Syntax

Question 1

SELECT

Answer 1

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:

Question 2

How can you select all data in a table?

Answer 2

SELECT * FROM …

Question 3

How can I return only unique values from a column?

Answer 3

SELECT DISTINCT column FROM table
or
SELECT DISTINCT(column) FROM table

Question 4

What does COUNT do?

Answer 4

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;

Question 5

Is the following a valid query:
SELECT COUNT DISTINCT amount FROM payment

Answer 5

No, COUNT needs to be followed up by parentheses:
SELECT COUNT(DISTINCT amount) FROM payment;

Question 6

What does WHERE do?

Answer 6

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.

Question 7

Is the following query correct:
SELECT * FROM film
WHERE rental_rate > 4 AND WHERE length > 100

Answer 7

No, WHERE cannot be repeated.
The correct query would be:
SELECT * FROM film
WHERE rental_rate > 4 AND length > 100

Question 8

Is the following query correct:
SELECT title FROM film
WHERE rental_rate > 4 AND replacement_cost >= 19.99
AND rating=’R’;

Answer 8

Yes, you can add as many logical statements as you want

Question 9

What does ORDER BY do?

Answer 9

ORDER BY orders results.

Question 10

How does ORDER BY order if left blank? (no following statement)

Answer 10

ORDER BY will order by ascending if left blank.

Question 11

How can you use ORDER BY with multiple columns?

Answer 11

ORDER BY company,sales

Question 12

How can I specify to ORDER BY ascending for the first statement and descending for the second statement?

Answer 12

ORDER BY store_id ASC,first_name DESC

Question 13

What does the LIMIT command allow us to do?

Answer 13

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.

Question 14

Where do ORDER BY and LIMIT belong inside a query?

Answer 14

ORDER BY and LIMIT belong at the very end of a query.

Question 15

What does the BETWEEN operator do?

Answer 15

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

Question 16

In what format does a date need to be to be used with the BETWEEN operator?

Answer 16

ISO 8601 standard format:
YYYY-MM-DD
example:

date BETWEEN ‘2007-01-01’ AND ‘2007-02-01’

Question 17

What does the IN operator do?

Answer 17

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’);

Question 18

Considering the row you want to query is of a numeric datatype, how would you use the IN operator in that case?

Answer 18

WHERE amount IN (0.99,1.98,1.99)
as opposed to putting the values in quotation marks

Question 19

What does the LIKE operator do?

Answer 19

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

Question 20

What does the ILIKE operator do?

Answer 20

The ILIKE operator is a case-sensitive version of the LIKE operator.

Question 21

What are aggregate functions?

Answer 21

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.

Question 22

What does the AVG function do?

Answer 22

AVG returns a floating point value many decimal places (e.g. 2.342418…)

Question 23

What does the MAX and MIN functions do

Answer 23

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

Question 24

What does the MAX and MIN functions do

Answer 24

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

Question 25

How can I use both MAX and MIN inside a single query to get multiple results?

Answer 25

SELECT MAX(replacement_cost),MIN(replacement_cost) FROM film;

Question 26

What does the AVG aggregate function do?

Answer 26

The AVG() function returns the average value of an expression.

Question 27

How can I round the result of AVG to 2 decimal places?

Answer 27

SELECT ROUND(AVG(replacement_cost),2) FROM film;

Question 28

How can I add up all values inside a row?

Answer 28

SELECT SUM(replacement_cost) FROM film;

Question 29

What does the GROUP BY statement do?

Answer 29

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

Question 30

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?

Answer 30

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

Question 31

What does the HAVING clause do?

Answer 31

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.

Question 32

Can you use the HAVING clause to filter out results without using aggregate function?

Answer 32

Yes you can, however in that case it will just function like a normal WHERE clause.

Question 33

Would this be a valid query?
SELECT sum(amount), customer\_id from payment

Answer 33

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

Question 34

Would this be a valid query?
SELECT amount, customer_id from payment
GROUP BY customer_id

Answer 34

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

Question 35

Would this be a valid query?
SELECT sum(amount), customer_id from payment
GROUP BY customer_id

Answer 35

Yes, GROUP BY doesn’t accept aggregate functions as an argument.

Question 36

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?

Answer 36

SELECT customer_id, SUM(amount)
FROM payment
WHERE staff_id = 2
GROUP BY customer_id
HAVING SUM(amount) > 100

Question 37

What does the AS statement do?

Answer 37

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

Question 38

Is this a valid query?
SELECT customer_id AS ID from customer
WHERE ID = 123

Answer 38

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

Question 39

What does the INNER JOIN statement do?

Answer 39

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;

Question 40

What does a FULL OUTTER JOIN do?

Answer 40

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.

Question 41

What does a LEFT OUTER JOIN do?

Answer 41

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

Question 42

What does a RIGHT OUTER JOIN do?

Answer 42

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.

Question 43

Given the following diagram, which JOIN would you choose if you had only needed data that exists in both tables?

Answer 43

INNER JOIN

Question 44

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?

Answer 44

FULL OUTER JOIN (FULL JOIN)

Question 45

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

Answer 45

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

Question 46

How can I check for columns that are null? (have no value)

Answer 46

WHERE col IS NULL

Question 47

What does the UNION operator do?

Answer 47

• 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.