Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL > Intermediate > Flashcards

Intermediate Flashcards

1
Q

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

A

COUNT()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

SELECT COUNT(birthdate) AS count_birthdates
FROM people;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

SELECT COUNT(birthdate) AS count_birthdates
FROM people;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

(all records from people table, use alias total_records)

A

SELECT COUNT(*) AS total_records
FROM people;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How can I select the unique values from a FIELD

A

DISTINCT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

SELECT DISTINCT language
FROM films;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

combine; COUNT(DISTINCT____)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

SELECT COUNT(DISTINCT birthdays) AS unique_birthdays
FROM people;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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.

A

The number of records containing a film_id.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

In what order does the code run in SQL

SELECT
FROM
LIMIT

A

FROM
SELECT
LIMIT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

A
19
Q

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

A
20
Q

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

A
21
Q

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

A
22
Q

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

A
23
Q

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

A
24
Q

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

A
25
Q

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

A

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

26
Q

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

A
27
Q

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

A
28
Q

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

A
29
Q

What does the OR operator do?

A

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

30
Q

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

A
31
Q

What does the BETWEEN keyword do?

A

Filter values in a specified range

32
Q

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

A
33
Q

What does the IN opperator do

A

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

34
Q

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

A
35
Q

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

TABLE; films
FIELDS; title, language

A
36
Q

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

A
37
Q

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

A

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.

38
Q

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

TABLE; people
FIELDS; deathdate

A
39
Q

Get the title of every film which doesn’t have a budget associated with it.

TABLE; films
FIELDS; budget, title

A
40
Q

Get the number of films which don’t have a language associated with them.

TABLE; films
FIELDS; language

A
41
Q

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

A

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

42
Q

Get the names of all people whose names begin with ‘B’.

TABLE; people
FIELDS; name

A
43
Q

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

A
44
Q

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

A

SQL (4 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions