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)
- Find the address of a studio named MGM
- Find the number of movies that was not produced by "MGM"
- Find all the stars that appeared either in a movie made in 2023 or a movie with the word "Love" in the title
- Find the names of all executives with net worth between $5,000,000 and $10,000,000
- Find all the stars who either are female or live in Malibu (i.e., having 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.
- 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.
- Which stars appeared in movies produced by MGM in 2023?
- 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.
Copyright © 2024 Upsorn Praphamontripong
Released under the
CC-BY-NC-SA 4.0 license.