Ga naar inhoud

Header

Filtering with WHERE - Assignments

1. Retrieving data of actors with a specific first name

Complete the following query to obtain data from the actors table, where the first name (column first_name) is equal to 'Jennifer'.

  • Use the WHERE statement.
SELECT
  *
FROM
  actors
WHERE
  first_name = 'Jennifer';
id first_name last_name gender film_count
533710 Jennifer Allswang F 1
536750 Jennifer Aniston F 1
540103 Jennifer Aspen F 1
593617 Jennifer Darling F 1
633197 Jennifer Gimenez F 1 
Columns: 5
Rows: 5



2. Retrieving data of actors with a specific beginning of the first name

Complete the following query to obtain data from the actors table, where the first name (column first_name) begins with 'John'.

  • Use the WHERE statement.
  • Use the LIKE statement.
  • Use the % sign.
SELECT
  *
FROM
  actors
WHERE
  first_name LIKE 'John%';
id first_name last_name gender film_count
26018 John Bandemer M 1
36005 John Belushi M 1
43791 John (I) Bishop M 2
... ... ... ... ... 
Columns: 5
Rows: 72



3. Retrieving data of actors with the last name from a list

Complete the following query to obtain data from the actors table, where the last name (column last_name) appears in the list: 'Anderson', 'Douglas', 'Williams'.

  • Use the WHERE statement.
  • Use the IN() operator.
SELECT
  *
FROM
  actors
WHERE
  last_name IN('Anderson', 'Douglas', 'Williams');
id first_name last_name gender film_count
11489 Dion Anderson M 1
11547 George (IV) Anderson M 1
11590 J. Todd Anderson M 1
11865 Scott G. Anderson M 1
127373 Al Manuel Douglas M 1
... ... ... ... ... 
Columns: 5
Rows: 20



4. Retrieving data of actors with a specific number of films

Complete the following query to obtain data from the actors table, where the number of films (column film_count) is equal to 3.

  • Use the WHERE statement.
SELECT
  *
FROM
  actors
WHERE
  film_count = 3;
id first_name last_name gender film_count
65536 Steve Buscemi M 3
292028 Michael (I) Madsen M 3
366173 Bill Paxton M 3
376249 Brad Pitt M 3
378578 Stevo Polyi M 3
812916 Uma Thurman F 3 
Columns: 5
Rows: 6



5. Retrieving data of actors with the number of films greater than or equal to a value

Complete the following query to obtain data from the actors table, where the number of films (column film_count) is greater than or equal to 3.

  • Use the WHERE statement.
  • Use the >= operator.
SELECT
  *
FROM
  actors
WHERE
  film_count >= 3;
id first_name last_name gender film_count
22591 Kevin Bacon M 9
65536 Steve Buscemi M 3
292028 Michael (I) Madsen M 3
... ... ... ... ... 
Columns: 5
Rows: 7



6. Retrieving data of actors with the number of films in a range

Complete the following query to obtain data from the actors table, where the number of films (column film_count) is at least 2 and at most 4.

  • Use the WHERE statement.
  • Use the BETWEEN and AND operators.
SELECT
  *
FROM
  actors
WHERE
  film_count BETWEEN 2 AND 4;
id first_name last_name gender film_count
12508 David (I) Andrews M 2
36472 Lawrence Bender M 2
36641 Jay (I) Benedict M 2
... ... ... ... ... 
Columns: 5
Rows: 68



7. Retrieving data of actors with a specific gender and a specific number of films

Complete the following query to obtain data from the actors table, where:

  • The gender (column gender) is female ('F').
  • And the number of films (column film_count) is at least 2.


  • Use the WHERE statement.
  • Use the AND operator.
SELECT
  *
FROM
  actors
WHERE
  film_count >= 2
  AND gender = 'F';
id first_name last_name gender film_count
602370 Cameron Diaz F 2
623535 Vivica A. Fox F 2
635153 Jenette Goldstein F 2
... ... ... ... ... 
Columns: 5
Rows: 10



8. (Extra) Retrieving data of actors with requirements for the first name

Complete the following query to obtain data from the actors table, where:

  • The first name (column first_name) must begin with 'John'.
  • The length of the first name must be less than or equal to 9 characters.


  • Use the WHERE statement.
  • Use the LIKE operator.
  • Use the LENGTH() function.
SELECT
  *
FROM
  actors
WHERE
  first_name LIKE 'John%'
  AND LENGTH(first_name) <= 9;
id first_name last_name gender film_count
26018 John Bandemer M 1
36005 John Belushi M 1
43791 John (I) Bishop M 2
... ... ... ... ... 
Columns: 5
Rows: 65



9. (Extra) Retrieving data of actors with requirements for the first and last name (1)

Complete the following query to obtain data from the actors table, where:

  • The first name (column first_name) must begin with the letter 'j'.
    • It should not matter whether it is a lowercase or uppercase letter.
  • The second letter of the last name (column last_name) must be equal to the letter a.
    • It should not matter whether it is a lowercase or uppercase letter.
  • The length of the last name must be greater than or equal to 10 characters.


  • Use the WHERE statement.
  • Use the LIKE operator.
  • Use the LENGTH() function.
SELECT
  *
FROM
  actors
WHERE
  LOWER(first_name) LIKE 'j%'
  AND LOWER(last_name) LIKE '_a%'
  AND LENGTH(last_name) >= 10;
id first_name last_name gender film_count
267122 John Larroquette M 1
361482 Joe Pantoliano M 2
572929 Julia Carothers Hughes F 1 
Columns: 5
Rows: 3



10. (Extra) Retrieving data of actors with requirements for the first and last name (2)

Complete the following query to obtain data from the actors table, where:

  • The first name (column first_name) must begin with the letters 'je'.
    • It should not matter whether it is a lowercase or uppercase letter.
  • The last name (column last_name) must not appear in the list 'douglas', 'morrison'.
    • It should not matter whether it is a lowercase or uppercase letter.


SELECT
  *
FROM
  actors
WHERE
  LOWER(first_name) LIKE 'je%'
  AND LOWER(last_name) NOT IN('douglas', 'morrison');
id first_name last_name gender film_count
25293 Jeremy Ball M 1
113187 Jesse De Luna M 1
118282 Jeffrey DeMunn M 1
... ... ... ... ... 
Columns: 5
Rows: 37