Unit 4 Module 4 - SQL and Databses Flashcards
What do you call an organized collection of information or data?
A Database
What do you call a structed database containing tables that are related to eachother?
Relational Database
What do you call a column where every row has a unique entry?
Primary Key
What do you call a table that is a primary key in another table?
Foreign Key
What is a programming language used to create, interect with, and request information from a database?
SQL (Structured Query Language)
What do you call a request for data from a database table or a combination of tables?
Query
What do you call a record of events that occur within an organizations system?
Log
What is the command to access SQL from Linux?
sqlite3
What 3 ways do Linux and SQL filter data differently?
Structure, Joining Tables, Best uses
Structure - SQL offers more structure and tidyness rather than Linux
Joining Tables - SQL allows analysts to join multiple tables together when returning data, Linux does not.
Best uses - Sometimes looking for certain files that aren’t text SQL can’t format it.
In SQL, what command indicates which columns to return?
SELECT
In SQL, what command indicates which table to query?
FROM
In SQL, what command sorts a specified keyword in a column?
ORDER BY
Ex) ORDER BY city;
In SQL, what command sorts a specified keyword in a column by descending order?
DESC
Ex) ORDER BY city DESC;
What will this SQL command do to the columns?
SELECT customerid, city, country
FROM customers
ORDER BY country, city;
SQL then sorts the output by country, and for rows with the same country, it sorts them based on city.
What do you call selecting data that matches a certain condition?
Filtering
What do you call a symbol or keyword that represents an operation?
Operator
What command in SQL indicates there condition for a filter?
WHERE
SELECT firstname, lastname, title, email
FROM employees
WHERE title = ‘IT Staff’;
Ex) Want to filter by country?
WHERE country = ‘USA’
What command in SQL uses WHERE to search for a pattern in a column?
LIKE
When filtering in SQL where should you put the ; ?
At the end of a query
What do you call a special character that can be substituted with any other character? Also, what are they?
Wildcard
% and _
In order to use the wildcard command ( %, _ ) in SQL , what other command do you need?
LIKE
What do you call data consisting of numbers?
Numeric Data
What do you call data representing a date and/or time?
Date and time data
What operator filters for numbers or dates within a range?
BETWEEN
SELECT firstname, lastname, hiredate
FROM employees
WHERE hiredate BETWEEN ‘2002-01-01’ AND ‘2003-01-01’;
What are the 6 different operator symbols? What do they do?
< - less than
> - Greater than
= - Equal to
<= - Less than or equal to
>= - Greater than or equal to
<> - Not equal to
What command specifies that both condition must be met simultaneously?
AND
SELECT firstname, lastname, email, country, supportrepid
FROM customers
WHERE supportrepid = 5 AND country = ‘USA’;
What command specifies that either condition can be met?
OR
SELECT firstname, lastname, email, country
FROM customers
WHERE country = ‘Canada’ OR country = ‘USA’;
What command negates a condition?
NOT
SELECT firstname, lastname, email, country
FROM customers
WHERE NOT country = ‘USA’;
What command returns rows matching on a specified column that exists in more than one table ?
INNER JOIN
What command returns all of the records of the first table, but only returns rows of the second table that match on a specified column?
LEFT JOIN
What command returns all of the records of the second table, but only returns rows from the first table that match on a specified column?
RIGHT JOIN
What command returns all records from both tables?
FULL OUTER JOIN
What do you call the functions that perform a calculation over multiple data points and return the result of the calculation? What are 3 of them?
Aggregate functions
1) COUNT - returns a single number that represents the number of rows returned from your query.
2) AVG - Returns a single number that represents the average of the numerical data in a column.
3) SUM - returns a single number that represents the sum of the numerical data in a column.
