Intermediate Flashcards
How can I return the number of RECORDS with a value in a FEILD
COUNT()
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
SELECT COUNT(birthdate) AS count_birthdates
FROM people;
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
SELECT COUNT(birthdate) AS count_birthdates
FROM people;
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
SELECT COUNT(birthdate) AS count_birthdates, COUNT(name) AS count)names
FROM people;
How can you count the number of RECORDS (rows) in a table
(all records from people table, use alias total_records)
SELECT COUNT(*) AS total_records
FROM people;
How can I select the unique values from a FIELD
DISTINCT
remove all duplicates in the FIELD; languages from the TABLE; films
SELECT DISTINCT language
FROM films;
how can you count the unique number of items in a field
combine; COUNT(DISTINCT____)
count the unique birthdays from the FIELD; birthday and use the alias; unique_birthdays
Table; people
SELECT COUNT(DISTINCT birthdays) AS unique_birthdays
FROM people;
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.
The number of records containing a film_id.
In what order does the code run in SQL
SELECT
FROM
LIMIT
FROM
SELECT
LIMIT
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
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
Get all details for all films released in 2016.
TABLE; films
FIELD; release year
Get the number of films released before 2000.
TABLE; films
FIELD; release year
Get the title and release year of films released after 2000.
Get all details for all French language films.
TABLE: films
FIELD: language
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
Get the number of Hindi language films.
TABLE: films
FIELD; language
Get all details for all films with an R certification
TABLE; films
FIELD; certification
How do you build up a WHERE query to have multiple conditions
use WHERE
AND, OR, BETWEEN, IN
Note that you need to specify the column name separately for every AND condition!!
Get the title and release year for all Spanish language films released before 2000.
TABLE; films
FIELDS, release_date , language
Get all details for Spanish language films released after 2000.
TABLE; films
FIELDS, release_date , language
Get all details for Spanish language films released after 2000, but before 2010.
TABLE; films
FIELDS, release_date , language
What does the OR operator do?
Display only rows that meet at least one of the specified conditions.
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
What does the BETWEEN keyword do?
Filter values in a specified range
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
What does the IN opperator do
specify multiple values in a WHERE clause, making it easier and quicker to specify multiple OR conditions
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
Get the title and language of all films which were in English, Spanish, or French.
(use IN)
TABLE; films
FIELDS; title, language
Get the title and certification of all films with an NC-17 or R certification.
TABLE; films
FIELDS; title, certification
When would you use an IS NULL or IS NOT NULL operator
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.
Get the names of people who are still alive, i.e. whose death date is missing.
TABLE; people
FIELDS; deathdate
Get the title of every film which doesn’t have a budget associated with it.
TABLE; films
FIELDS; budget, title
Get the number of films which don’t have a language associated with them.
TABLE; films
FIELDS; language
What are the two wild card characters you can use with the LIKE operator?
% 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’;
Get the names of all people whose names begin with ‘B’.
TABLE; people
FIELDS; name
Get the names of people whose names have ‘r’ as the second letter.
TABLE; people
FIELDS; name
Get the names of people whose names don’t start with A.
TABLE; people
FIELDS; name
