Code Examples

Question 1

SELECT COUNT(*)
FROM table_name;

Answer 1

This counts all the rows in the Table.

Question 2

SELECT SUM(downloads)
FROM table_name;

Answer 2

This adds all values in the downloads column of the Table.

Question 3

SELECT MAX(downloads)
FROM table_name;

Answer 3

This returns the largest value in the downloads column of the Table.

Question 4

SELECT MIN(downloads)
FROM table_name;

Answer 4

This returns the smallest value in the downloads column of the Table.

Question 5

SELECT AVG(downloads)
FROM table_name;

Answer 5

This returns the average number of downloads column of the Table.

Question 6

SELECT ROUND(price, 0)
FROM table_name;

Answer 6

This rounds the values in the price column Table to 0 decimal places.

Question 7

SELECT year,
AVG(imdb_rating)
FROM movies
GROUP BY year
ORDER BY year;

Answer 7

This returns the the year and average imdb_rating values that are grouped and ordered by the year.

Question 8

SELECT ROUND(imdb_rating),
COUNT(name)
FROM movies
GROUP BY 1
GROUP BY 1;

Answer 8

This rounds the imdb_rating values and counts all the columns in name from the Table movies and the values are grouped by the first column.

Question 9

SELECT year,
genre,
COUNT(name)
FROM movies
GROUP BY 1, 2
HAVING COUNT(name) > 10;

Answer 9

This selects the year, genre, and counts the name column from the movies Table, Grouping them by column 1 and column 2 but only counting the name values that are greater than 10.

Question 10

SELECT orders.order_id,
customers.customer_name
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id

Answer 10

INNER JOIN

Question 11

SELECT *
FROM table1
LEFT JOIN table2
ON table1.c2 = table2.c2;

Answer 11

LEFT JOIN

Question 12

SELECT shirts.shirts_color,
pants.pants_color
FROM shirts
CROSS JOIN pants;

Answer 12

CROSS JOIN

Question 13

SELECT *
FROM table1
UNION
SELECT *
FROM table2;

Answer 13

UNION

Question 14

WITH previous_results AS (
SELECT …..
)
SELECT *
FROM previous_results
JOIN customers
ON _____ = ______;

Answer 14

WITH

Question 15

What is a PRIMARY KEY?

Answer 15

Special columns

Question 16

What are the requirements of a PRIMARY KEY?

Answer 16

None of the value can be NULL
Each value must be unique (can’t have duplicate values)
A Table can not have more than one PRIMARY KEY column

Question 17

What is a FOREIGN KEY?

Answer 17

A PRIMARY KEY that appears in a different Table.

Question 18

What is the difference between PRIMARY KEYs and FOREIGN KEYs?

Answer 18

PRIMARY KEYs are unique and FOREIGN KEYs have descriptive names.

Question 19

The most common types of JOIN do what with PRIMARY and FOREIGN KEYs?

Answer 19

The most common types of JOINs, will be joining a FOREIGN KEY from one Table with the PRIMARY KEY of another Table.

Question 20

SELECT column1, column2
FROM table_name;

SELECT *
FROM table_name;

Answer 20

SELECT query

Question 21

SELECT name AS ‘Titles’
FROM movies;

Answer 21

AS - renames column or Table

Question 22

SELECT DISTINCT tools
FROM inventory;

Answer 22

DISTINCT - removes duplicate values

Question 23

SELECT *
FROM movies
WHERE imdb_rating>8;

Answer 23

WHERE filters the result set to include rows where the condition is true

Question 24

SELECT *
FROM movies
WHERE name LIKE ‘Se_en’

Answer 24

LIKE is used with the WHERE clause to search for a specific pattern.

Question 25

SELECT *
FROM movies
WHERE name LIKE ‘A%’;

SELECT *
FROM movies
WHERE name LIKE ‘%A’;

SELECT *
FROM movies
WHERE name LIKE ‘%A%’;

Answer 25

The wildcard character, %, matches zero or missing letters in a patter.

Example one matches movies that begin with A
Example two matches movies that end with A
Example three matches movies that contain A in it

Question 26

SELECT name
FROM movies
WHERE imdb_rating IS NOT NULL;

Answer 26

Unknown values are indicated by NULL

IS NOT NULL
IS NULL

Will be used as comparison operators for WHERE clause and not = or !=

Question 27

SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999;

Answer 27

The result set will include movie with years from 1990 up to and including 1999

Question 28

SELECT *
FROM movies
WHERE name BETWEEN ‘A’ AND ‘J’;

Answer 28

The result set will include movies with the names that begin with the letter ‘A’ up to, but not including the letter ‘J’.

Question 29

SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999
AND genre = ‘romance’;

Answer 29

With AND, both conditions must be true for the row to be included in the result

Question 30

SELECT *
FROM movies
WHERE year > 2014
OR genre = ‘action’;

Answer 30

With OR, if any of the conditions are true then the row is added to the result.

Question 31

SELECT *
FROM movies
WHERE imdb_rating > 8
ORDER BY year DESC;

Answer 31

ORDER BY sorts the result set by a particular column.
Always goes after WHERE clause if WHERE is present

Question 32

SELECT *
FROM movies
LIMIT 10;

Answer 32

LIMIT specifies the maximum number of rows the result will have
Always goes at the very end of the query

Question 33

SELECT name,
CASE
WHEN imdb_rating > 8 THEN ‘Fantastic’
WHEN imdb_rating > 6 THEN ‘Poorly Received’
ELSE ‘Avoid at All costs’
END AS ‘Review’
FROM movies;

Answer 33

CASE statement creates different outputs

-usually in the SELECT statement
- WHEN tests a condition
- THEN gives us the string
- CASE must end with END