CS4750 - Database Systems

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

Find the address of a studio named MGM

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

Find the number of movies that was not produced by "MGM"

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

Find all the stars that appeared either in a movie made in 2023 or a movie with the word "Love" in the title

   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";

Find the names of all executives with net worth between $5,000,000 and $10,000,000

   SELECT  name 
   FROM    MovieExec
   WHERE   5000000 <= netWorth AND netWorth <= 10000000;    

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

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.
```
   SELECT  name AS Star_Name
   FROM    movieStar
   WHERE   gender = "F" OR address LIKE '%Malibu%';         // '% Malibu %' ?
```

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.

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

Which stars appeared in movies produced by MGM in 2023?

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'

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.

   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

CC-BY-NC-SA 4.0 license.
Last updated 2025-09-21 12:28