SQL

What is LIMIT?

A clause that lets you specify the maximum number of rows the result set will have.

What is ORDER BY?

A clause that sorts the result set alphabetically or numerically

What is the best definition of a foreign key?

A column that contains the primary key of another table in the database

What is a relational database?

A database that organizes information into one or more tables

What is LIKE?

A special operator that can be used with WHERE clause to search for a pattern.

What would you need to complete the associated UPDATE statement? UPDATE ________ SET height = 6 WHERE id = 1;

A table name

What is the best definition of a primary key?

A unique identifier for each row or record in a given table

What is a NULL value?

A value that represents missing or unknown data.

What code would you add to this query to order colors by name alphabetically (Z to A)? SELECT * FROM colors _________________;

ORDER BY name DESC

What is the correct query to select only the cities with temperatures less than 35?

SELECT * FROM cities WHERE temperature < 35;

How would you query all the unique genres from the books table?

SELECT DISTINCT genres FROM books;

How would you calculate the minimum number of stops from the train table?

SELECT MIN(stop) FROM train;

Which clause is used with the ALTER TABLE statement?

ADD COLUMN

Which operator would you use to query values that meet all conditions in a WHERE clause?

AND

Which keyword would you use to alias recipes.name and chefs.name in the following query'? SELECT recipes.name __ 'Recipe', chefs.name __ 'Chef' FROM recipes JOIN chefs ON recipes.chef_id = chefs.id;

AS

What would be correct syntax for a CREATE TABLE statement?

CREATE TABLE meals ( name TEXT, rating INTEGER );

Which of the following statements is correct and complete?

DELETE FROM icecream Where flavor IS NULL

Which kind of join is in the animation below?

INNER JOIN

What does the INSERT statement do?

Insert new rows into a table.

What does the following query do? SELECT neighborhood, AVG(price) FROM apartments GROUP BY neighborhood;

It calculates the average price of apartments in each neighborhood

What does the following query do? SELECT price, COUNT(*) FROM menu WHERE orders > 50 GROUP BY price;

It calculates the total number of menu items that have been ordered more than 50 times - for each price

Why is a CROSS JOIN not so useful?

It combines every row in one table with every row in another table.

What does the wildcard character % in the following SQL statement do? SELECT * FROM sports WHERE name LIKE '%ball';

It matches all sports that end with 'ball'

What does the following query do? SELECT genre, SUM(downloads) FROM kindle GROUP BY genre;

It returns the total amount of downloads - for each genre.

What is the purpose of the * character? SELECT * FROM celebs;

It selects every column in the table

Find the error in this code: SELECT COUNT(*) FROM songs HAVING plays > 100;

It should be WHERE instead of HAVING.

What is the difference between an INNER JOIN and a LEFT JOIN?

LEFT JOIN combines rows from two or more tables, but unlike INNER JOIN, it does not require the join condition to be met.

Find the error in this code: SELECT name, CASE WHEN imdb_rating > 8 THEN 'Oscar' WHEN imdb_rating > 7 THEN 'Good' WHEN imdb_rating > 6 THEN 'Fair' FROM movies;

Missing END statement

In a LEFT JOIN, if a join condition is not met, what will it use to fill columns on the right table?

NULL values

Which function takes a column and returns the total sum of the numeric values in that column?

SUM()

What does SQL stand for?

Structured Query Language

What does the ROUND function take as argument(s)?

The column name, and the number of decimal places to round the values in the column to.

What does the COUNT() function take as argument(s)?

The name of a column or *.

IS NULL condition returns true if the field is empty.

True

The WHERE clause filters rows, whereas the HAVING clause filter groups.

True

UNION allows us to stack one dataset on top of another.

True

You have two tables authors and books. Each book belongs to an author and references that author through a foreign key. If the primary key of the authors table is id, what would be the most sensical name for a foreign key in the books table that references the id column in authors?

author_id

Aggregate functions ...

compute a single result set from a set of values.

What are common data types in SQL?

integer, text, date, real

What is the correct syntax to query both the name and date columns from the database? SELECT __________ FROM album;

name, date

You have two tables teachers and students. Each student belongs to a teacher. Complete the query to join the tables on the teacher id. SELECT * FROM students JOIN teachers ON __________________;

students.teacher_id = teachers.id

Which of the following is NOT a comparison operator in SQL? < >= ~ !=

~ Comparison operators are =, !=, >, <, >=, <=.