Activity: SQL Basics & Aggregates

(no submission)

---

Purpose:

• Be familiar with database queries
• Practice writing and interpreting SQL
• Get ready to work on homework assignment and course project

---

You may make a copy of a worksheet and complete this activity, or simply type your answers in any text editor.

You may work alone or with 3-4 students in this course (max size=5).

---

Given the following relations and schemas, write SQL queries to solve the problems.

Movies (title, year, length, genre, studioName, producerCert)
starsIn (movieTitle, movieYear, starName, salary)
MovieStar (name, address, gender, birthdate)
MovieExec (name, address, producerCert, netWorth)
Studio (name, address)   

Expand / Collapse all

1. Find the address of a studio named MGM
   [sample solution]

      SELECT  address AS Studio_Address
      FROM    Studio
      WHERE   name = "MGM";   

2. Find the number of movies that was not produced by "MGM"
   [sample solution]

      SELECT  COUNT(*) 
      FROM    Movies
      WHERE   studioName <> "MGM";   

3. Find all the stars that appeared either in a movie made in 2023 or a movie with the word "Love" in the title
   [sample solution]

      SELECT  starName
      FROM    StarsIn
      WHERE   movieYear = 2023 OR 
              movieTitle LIKE "%Love%";
   
   However, the above query will also return words that have the substring Love e.g. Lover. 
   Below query will only return movies that have title containing the word Love.   
   
      SELECT  starName
      FROM    StarsIn
      WHERE   movieYear = 2023 OR 
              movieTitle LIKE "Love %" OR 
              movieTitle LIKE "% Love %" OR 
              movieTitle LIKE "% Love" OR movieTitle = "Love";   

4. Find the names of all executives with net worth between $5,000,000 and $10,000,000
   [sample solution]

      SELECT  name 
      FROM    MovieExec
      WHERE   5000000 <= netWorth AND netWorth <= 10000000;    
   
   Another solution: 
      SELECT  name 
      FROM    MovieExec
      WHERE   netWorth BETWEEN 5000000 AND 10000000;   

5. Find all the stars who either are female or live in Malibu (i.e., having the string "Malibu" as a part of their addresses). You may assume that a star's gender is recorded as "F" for female and "M" for male.
   [sample solution]

      SELECT  name AS Star_Name
      FROM    movieStar
      WHERE   gender = "F" OR address LIKE '%Malibu%';         // '% Malibu %' ?

6. Find names of all males who star in a movie entitled Star Wars. You may assume that a star's gender is recorded as "F" for female and "M" for male.
   [sample solution]

      SELECT  M.name AS starName
      FROM    MovieStar M,
              StarsIn S
      WHERE   M.name = S.starName AND 
              S.movieTitle = "Star Wars" AND 
              M.gender = "M"; 

7. Which stars appeared in movies produced by MGM in 2023?
   [sample solution]

   Assuming the same movie title may be made in multiple years 
      SELECT  S.starName AS starName 
      FROM    Movies M, 
              starsIn S 
      WHERE   M.title = S.movieTitle AND 
              M.year = S.movieYear AND 
              M.year = 2023 AND 
              M.studioName = 'MGM'    

8. Find all the Malibu addresses shared by at least one star and at least one executive (i.e., having string "Malibu" as a part of their addresses). We do not want to see any duplicate addresses.
   [sample solution]

      SELECT DISTINCT MovieStar.address 
      FROM   MovieStar, MovieExec 
      WHERE  MovieStar.address LIKE "%Malibu%" AND 
             MovieStar.address = MovieExec.address
      
   Another possible solution: 
      (SELECT address
      FROM    MovieStar
      WHERE   address LIKE '%Malibu%')  
      INTERSECT
      (SELECT address
      FROM    MovieExec
      WHERE   address LIKE '%Malibu%')   

Expand / Collapse all

---

Copyright © 2024 Upsorn Praphamontripong

Released under the CC-BY-NC-SA 4.0 license.

Last updated 2024-02-13 18:47