SQL for non-programmers part 2 Flashcards
Which SQL clause is used to filter data, retrieving only the rows of data that fulfill a specific condition?
LIMIT
SELECT
WHERE
ORDER BY
WHERE allows you to specify conditions that must be met for a row to be included in your result set, essentially applying a filter over the data
What are the most common aggregate functions? Why use them?
COUNT
SUM
AVG
MIN
MAX
Helps us analyze data without having to extract data row by row from a table
What kind of syntax do aggregate functions need to follow?
The aggregate function needs to follow before the column name of the one you want to perform the function ona and the column name should have parentheses.
Why use alias
AS
To name a column something shorter when it has a long complicated name
Ex:
SELECT COUNT (DISTINCT cust_id)
FROM oliveoil.sales
change to
SELECT COUNT (DISTINCT cust_id) AS customer_count
FROM oliveoil.sales;
–this will return a result on the grid view with that given title and the number result
What does the SUM function do? What about AVG function?
It adds all numerical values in a column
The AVG will get an average of all numbers in the column selected
Ex:
SELECT SUM (order_total) AS sum_of_order
AVG (order_total) AS avg_order
FROM oliveoil.sales
This will return in the grid view, the numbers for both sum_of_order and avg_order
Use GROUP BY when
Grouping can be used to divide data into groups and then perform aggregate calculations on those groups. GROUP BY must be placed after the WHERE clause and before ORDER BY
Ex:
WHERE
GROUP BY
ORDER BY
What is the HAVING clause?
Used to filter the rest of a GROUP BY query based on specific conditions
It is placed after GROUP BY and before ORDER BY
Ex:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
Filters groups, dont confuse with WHERE that’s used to filter rows
How does HAVING clause differ from a WHERE clause?
HAVING clause is used to filter groups of data after they have been formed. Contrary to a WHERE clause which filters data rows before any groups have been applied.
What is an equijoin?
SELECT, FROM and WHERE
Joins two or more related data tables
It belongs to the broader INNER JOIN Family
INNER JOIN
A classic inner join is preferred in SQL community and is more robust, flexible option, useful as queries become more complex
What is the difference between joins?
The difference between joins lies in which rows you want to return from your selected data tables
INNER JOIN returns all of the rows that match the join conditions
What do you do if you wanted to return the rows that don’t match the join conditions?
Or all of the rows from one table and only those that match from another table?
LEFT JOIN returns all of the rows from the left table and only the matching rows from the right table
The left table is the table preceeding the JOIN keyword in your SQL code and the right table is the table immediately after the JOIN keyword
What does the RIGHT JOIN command do in SQL?
It does the opposite of LEFT JOIN. It returns all of the rows from the right table and only the matching rows from the left table
This is not supported in SQLite but it can be achieved by swapping the order of the tables listed on your JOIN clause
What is a CROSS JOIN?
It creates a cartesian product of rows from joined tables. A cartesian product means that it combines and returns all possible combinations of rows from both tables. You do not need to use ON condition because it includes everything regardless of a match.
