ICA #4 - SQL Part 2
docx
keyboard_arrow_up
School
Temple University *
*We aren’t endorsed by this school
Course
MIS 2101
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
3
Uploaded by BarristerPencil4766
In-class Activity #4: Working with SQL, Part 2
Getting Information out of a Database
Submission Instructions
Submit your solutions for Part 2
of this exercise as a word or pdf file through Canvas>Assignments>To-
Do.
In this exercise, you will be working with a movie rental database. The schema for this database is provided below. All of the tables are in a schema called simply “
moviedb
” (not m0moviedb, m1moviedb,
etc.). You can’t write to any of the tables – you can only use SELECT statements to read from them (so don’t worry about causing any damage).
Spend some time looking at the schema carefully. The field names are self-explanatory. You can see that a film has a title, description, rating, and length (among other things). You can also see that an actor has a first name and a last name. The film_actor table implements the many-to-many relationship between actor and film (i.e., a film can have more than one actor, and an actor can be in
more than one film). You’ll also notice that data types are listed for each field, but they should be pretty obvious – for example, first_name is a VARCHAR because it is a string value.
Write queries on your own
Create the SQL SELECT query to answer each of the questions below. Some of the questions can be answered by querying one table; others will require joining multiple tables to get the answer. For each question, you’ll need to write down (1)
the SQL query
(which you can copy and paste from SQL Workbench) and (2) the answer you get as a result of the query (which you can copy and paste from the results). Once you are done, save this file and submit it to Canvas.
Question
SQL Query
Results/Answer from MySQL Workbench
1. How many movies are in French?
Display: number of films
2. What is the postal code of a customer named “Ruth Martinez”?
Display: postal code
3. How many inventories are there for each film? Return the value for all films including those
that have no inventory.
Display: film id and number of inventories
(only show first 5 rows)
4. What is the number of films with the highest value of length?
Display the length and the number of films. (HINT: Find the number of films for each value of
length.)
Display: length and number of films
5. In which films did Sandra Peck star (only return the first five)?
Display: title
6. Provide a list of all the unique languages used in PG-rated movies available at store 1. (Hint: Store 1 means store_id =1)
Display: language name
7. How many rentals has the customer Adam Gooch made? Page 2
(HINT: You need to use the customer table and the rental table.)
Display: number of rentals
8. What are the two most popular first names of customers
who live in postal code greater than 40000?
Display: first name and number of names
9. What are the top-3 longest films rented by Diana Alexander?
Display films’ names and their lengths.
(HINT: You would need to connect customer and film tables.)
Display: title and length
10. Find the number of rentals for each film with Penelope Guiness and list the three most popular films. (HINT: You would need to connect actor and rental tables.)
Display: title and number of rentals
Page 3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help