Syntax Flashcards
SELECT
The SELECT statement is used to select data from a database.
SELECT column1, column2, …
FROM table_name;
Here, column1, column2, … are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:
How can you select all data in a table?
SELECT * FROM …
How can I return only unique values from a column?
SELECT DISTINCT column FROM table
or
SELECT DISTINCT(column) FROM table
What does COUNT do?
The COUNT function returns the number of input rows that match a specific condition of a query.
We can apply COUNT on a specific column or just pass COUNT(*)
Ex: SELECT COUNT(*) FROM table;
Is the following a valid query:
SELECT COUNT DISTINCT amount FROM payment
No, COUNT needs to be followed up by parentheses: SELECT COUNT(DISTINCT amount) FROM payment;
What does WHERE do?
The WHERE statement allows us to specify conditions on columns for the rows to be returned.
The WHERE clause appears immediately after the FROM clause of the SELECT statement.
Is the following query correct:
SELECT * FROM film
WHERE rental_rate > 4 AND WHERE length > 100
No, WHERE cannot be repeated.
The correct query would be:
SELECT * FROM film
WHERE rental_rate > 4 AND length > 100
Is the following query correct:
SELECT title FROM film
WHERE rental_rate > 4 AND replacement_cost >= 19.99
AND rating=’R’;
Yes, you can add as many logical statements as you want
What does ORDER BY do?
ORDER BY orders results.
How does ORDER BY order if left blank? (no following statement)
ORDER BY will order by ascending if left blank.
How can you use ORDER BY with multiple columns?
ORDER BY company,sales
How can I specify to ORDER BY ascending for the first statement and descending for the second statement?
ORDER BY store_id ASC,first_name DESC
What does the LIMIT command allow us to do?
The LIMIT command allows us to lmit the number of rows returned for a query.
Useful for not wanting to return every single row in table, but only view the top few rows to get an idea of the table layout.
Where do ORDER BY and LIMIT belong inside a query?
ORDER BY and LIMIT belong at the very end of a query.
What does the BETWEEN operator do?
The BETWEEN operator can b e used to match a value against a range of values:
value BETWEEN low AND high
The BETWEEN operator is the same as:
value >= low AND value <= high
value BETWEEN low AND high
Can also combine BETWEEN with the NOT logical operator:
value NOT BETWEEN low AND high
In what format does a date need to be to be used with the BETWEEN operator?
ISO 8601 standard format:
YYYY-MM-DD
example:
date BETWEEN ‘2007-01-01’ AND ‘2007-02-01’
What does the IN operator do?
The IN operator can be used as a replacement to query multiple values.
We can use the IN operator to create a condition that checks to see if a value in included in a list of multiple options.
Example:
SELECT color FROM table
WHERE color IN(‘red,’blue’);
Considering the row you want to query is of a numeric datatype, how would you use the IN operator in that case?
WHERE amount IN (0.99,1.98,1.99)
as opposed to putting the values in quotation marks
What does the LIKE operator do?
The LIKE operator allows us to perform pattern matching against string data with the use of wildcard characters:
Percent %: Matches any sequence of characters
Underscore _: Matches any single character
What does the ILIKE operator do?
The ILIKE operator is a case-sensitive version of the LIKE operator.
What are aggregate functions?
An aggregate function performs a calculation on a set of values, and returns a single value.
Except for COUNT(*), aggregate functions ignore null values.
Aggregate functions are often used with the GROUP BY clause of the SELECT statement.
What does the AVG function do?
AVG returns a floating point value many decimal places (e.g. 2.342418…)
What does the MAX and MIN functions do
MAX and MIN are aggregate functions that return either the highest or lowest value inside a given row. Example: SELECT MAX(replacement\_cost) FROM film
What does the MAX and MIN functions do
MAX and MIN are aggregate functions that return either the highest or lowest value inside a given row. Example: SELECT MAX(replacement\_cost) FROM film
How can I use both MAX and MIN inside a single query to get multiple results?
SELECT MAX(replacement_cost),MIN(replacement_cost) FROM film;
What does the AVG aggregate function do?
The AVG() function returns the average value of an expression.
How can I round the result of AVG to 2 decimal places?
SELECT ROUND(AVG(replacement_cost),2) FROM film;
How can I add up all values inside a row?
SELECT SUM(replacement_cost) FROM film;
What does the GROUP BY statement do?
The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has same values in different rows then it will arrange these rows in a group.
Important Points:
GROUP BY clause is used with the SELECT statement.
In the query, GROUP BY clause is placed after the WHERE clause.
In the query, GROUP BY clause is placed before ORDER BY clause if used any.
SELECT column1, column2
FROM table_name
WHERE [conditions]
GROUP BY column1, column2
Given the following table layout:
payment_id, customer_id, staff_id, rental_id, amount, payment_date
I want to figure out which 10 customers made the most purchases, how would I do that?
SELECT customer_id, COUNT(*) // these are the 2 rows that I care about, I also need the COUNT of all rows
FROM payment
GROUP BY customer_id // I want all rows that have the same customer_id to be grouped together
ORDER BY count(*) DESC // Order the results by descending to see which customers made the most purchases\
LIMIT 10 // limit it to only the top 10 results
What does the HAVING clause do?
The HAVING Clause enables you to specify conditions that filter which group results appear in the results.
The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.
Syntax:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used.
Can you use the HAVING clause to filter out results without using aggregate function?
Yes you can, however in that case it will just function like a normal WHERE clause.
Would this be a valid query? SELECT sum(amount), customer\_id from payment
No, the query needs to be either all aggregate functions or only selecting rows.
If I wanted to group aggregate results and rows together I would need to use the GROUP BY clause
Would this be a valid query?
SELECT amount, customer_id from payment
GROUP BY customer_id
No, GROUP BY needs all non-aggregate arguments to work.
The correct way would be:
SELECT amount, customer_id from payment
GROUP BY customer_id, amount
Would this be a valid query?
SELECT sum(amount), customer_id from payment
GROUP BY customer_id
Yes, GROUP BY doesn’t accept aggregate functions as an argument.
Given the following table named payment:
payment_id, customer_id, staff_id, rental_id, amount, payment_date
How can I get the customer_ids of customers who have spent more than $100 in payment transaction with our staff_id member 2?
SELECT customer_id, SUM(amount)
FROM payment
WHERE staff_id = 2
GROUP BY customer_id
HAVING SUM(amount) > 100
What does the AS statement do?
The AS statement lets us give the selected table a new alias.
Example SELECT id AS customer_id
FROM table;
Will output the id as customer_id
The AS operator gets executed at the very end of a query. Meaning that we cannot use the ALIAS inside a WHERE operator
Is this a valid query?
SELECT customer_id AS ID from customer
WHERE ID = 123
No, because the AS statement doesn’t get executed until the end of a query, therefor it cannot be used inside a WHERE clause, or HAVING clause
What does the INNER JOIN statement do?
The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate.
The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate.
When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.
Syntax:
SELECT table1.column1, table2.column2…
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
What does a FULL OUTTER JOIN do?
In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause.
What does a LEFT OUTER JOIN do?
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.
Syntax:
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.col_match = TableB.com_match
WHERE TableB.id IS null
What does a RIGHT OUTER JOIN do?
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.
I could just use LEFT OUTER JOIN (LEFT JOIN) and switch out the right side with the left side and have the same results.
Given the following diagram, which JOIN would you choose if you had only needed data that exists in both tables?
INNER JOIN
Given the following diagram, which JOIN would you choose if you wanted all data inside both tables including data that is shared between the two?
FULL OUTER JOIN (FULL JOIN)
Can you use the JOIN statements on more than 1 table?
For example if you want to add results of a third table to the data
Yes, you can use multiple JOIN statements.
Example:
SELECT * FROM table
INNER JOIN othertable
ON table.id = othertable.customer_id
INNER JOIN thirdtable
ON table.id = thirdtable.customer_id
How can I check for columns that are null? (have no value)
WHERE col IS NULL
What does the UNION operator do?
- The UNION operator is used to combine the result-set of two or more SELECT statements.
- It basically services to directly concatenate two results together, essentially “pasting” them together.
