Intermediate

Question 1

How can I return the number of RECORDS with a value in a FEILD

Answer 1

COUNT()

Question 2

Table: people
Field: birthdate

write code that can count the number of birthdates in the birthdate field and return it with an alias; count_birthdates

Answer 2

SELECT COUNT(birthdate) AS count_birthdates
FROM people;

Question 3

Table: people
Field: birthdate

write code that can count the number of birthdates in the birthdate field and return it with an alias; count_birthdates

Answer 3

SELECT COUNT(birthdate) AS count_birthdates
FROM people;

Question 4

count the number of names (FIELD name) and the number of birthdates (FIELD birthdate) from the table; people

use; count_names and count_birthdates as aliases

Answer 4

SELECT COUNT(birthdate) AS count_birthdates, COUNT(name) AS count)names
FROM people;

Question 5

How can you count the number of RECORDS (rows) in a table

(all records from people table, use alias total_records)

Answer 5

SELECT COUNT(*) AS total_records
FROM people;

Question 6

How can I select the unique values from a FIELD

Answer 6

DISTINCT

Question 7

remove all duplicates in the FIELD; languages from the TABLE; films

Answer 7

SELECT DISTINCT language
FROM films;

Question 8

how can you count the unique number of items in a field

Answer 8

combine; COUNT(DISTINCT____)

Question 9

count the unique birthdays from the FIELD; birthday and use the alias; unique_birthdays
Table; people

Answer 9

SELECT COUNT(DISTINCT birthdays) AS unique_birthdays
FROM people;

Question 10

Here is a query counting film_id. Select the answer below that correctly describes what the query will return.

SELECT COUNT(film_id) AS count_film_id
FROM reviews;

The number of unique films in the reviews table.

The number of records containing a film_id.

The total number of records in the reviews table.

The sum of the film_id field.

Answer 10

The number of records containing a film_id.

Question 11

Answer 11

Question 12

Answer 12

Question 13

Answer 13

Question 14

Answer 14

Question 15

Answer 15

Question 16

In what order does the code run in SQL

SELECT
FROM
LIMIT

Answer 16

FROM
SELECT
LIMIT

Question 17

What is the KEYWORD to filter text and numbers and where does it go?

what are the comparison opperators for
equal
not equal
less than
greater than
less than or equal to
greater than or equal to

Answer 17

keyword; WHERE
WHERE always comes after FROM statment.

= equal
<> not equal
< less than
> greater than
<= less than or equal to
>= greater than or equal to

Question 18

Get all details for all films released in 2016.
TABLE; films
FIELD; release year

Answer 18

Question 19

Get the number of films released before 2000.
TABLE; films
FIELD; release year

Answer 19

Question 20

Get the title and release year of films released after 2000.

Answer 20

Question 21

Get all details for all French language films.
TABLE: films
FIELD: language

Answer 21

Question 22

Get the name and birth date of the person born on November 11th, 1974. Remember to use ISO date format (‘1974-11-11’)!
TABLE: people
FIELD: birthdate

Answer 22

Question 23

Get the number of Hindi language films.
TABLE: films
FIELD; language

Answer 23

Question 24

Get all details for all films with an R certification
TABLE; films
FIELD; certification

Answer 24

Question 25

How do you build up a WHERE query to have multiple conditions

Answer 25

use WHERE AND, OR, BETWEEN, IN Note that you need to specify the column name separately for every AND condition!!

Question 26

Get the title and release year for all Spanish language films released before 2000. TABLE; films FIELDS, release_date , language

Answer 26

Question 27

Get all details for Spanish language films released after 2000. TABLE; films FIELDS, release_date , language

Answer 27

Question 28

Get all details for Spanish language films released after 2000, but before 2010. TABLE; films FIELDS, release_date , language

Answer 28

Question 29

What does the OR operator do?

Answer 29

Display only rows that meet at least one of the specified conditions.

Question 30

write a query to get the title and release year of films released in the 90s which were in French or Spanish and which took in more than $2M gross. TABLE; films FIELDS, language, release_year, gross

Answer 30

Question 31

What does the BETWEEN keyword do?

Answer 31

Filter values in a specified range

Question 32

get the title and release year of all Spanish language films released between 1990 and 2000 (inclusive) with budgets over $100 million. (use a BETWEEN keywork) TABLE; films FIELDS; language, budget, release_year

Answer 32

Question 33

What does the IN opperator do

Answer 33

specify multiple values in a WHERE clause, making it easier and quicker to specify multiple OR conditions

Question 34

Get the title and release year of all films released in 1990 or 2000 that were longer than two hours. Remember, duration is in minutes! (use IN) TABLE; films FIELDS; release_year, duration, title

Answer 34

Question 35

Get the title and language of all films which were in English, Spanish, or French. (use IN) TABLE; films FIELDS; title, language

Answer 35

Question 36

Get the title and certification of all films with an NC-17 or R certification. TABLE; films FIELDS; title, certification

Answer 36

Question 37

When would you use an IS NULL or IS NOT NULL operator

Answer 37

IS NULL is useful when combined with WHERE to figure out what data you're missing. Sometimes, you'll want to filter out missing values so you only get results which are not NULL. To do this, you can use the IS NOT NULL operator.

Question 38

Get the names of people who are still alive, i.e. whose death date is missing. TABLE; people FIELDS; deathdate

Answer 38

Question 39

Get the title of every film which doesn't have a budget associated with it. TABLE; films FIELDS; budget, title

Answer 39

Question 40

Get the number of films which don't have a language associated with them. TABLE; films FIELDS; language

Answer 40

Question 41

What are the two wild card characters you can use with the LIKE operator?

Answer 41

% wildcard will match zero, one, or many characters in text. For example, the following query matches companies like 'Data', 'DataC' 'DataCamp', 'DataMind', and so on: SELECT name FROM companies WHERE name LIKE 'Data%'; _ wildcard will match a single character. For example, the following query matches companies like 'DataCamp', 'DataComp', and so on: SELECT name FROM companies WHERE name LIKE 'DataC_mp';

Question 42

Get the names of all people whose names begin with 'B'. TABLE; people FIELDS; name

Answer 42

Question 43

Get the names of people whose names have 'r' as the second letter. TABLE; people FIELDS; name

Answer 43

Question 44

Get the names of people whose names don't start with A. TABLE; people FIELDS; name

Answer 44