Questions and Answers
What SQL query selects the number of movies grouped by year?
SELECT Year, COUNT(*) AS Total_Movies FROM Movie GROUP BY Year;
What SQL query selects movie ratings with a left join?
SELECT Movie.Title, Movie.Year, Rating.Description FROM Movie LEFT JOIN Rating ON Movie.RatingCode = Rating.Code;
What SQL query selects employees and managers with an inner join?
SELECT E.FirstName AS Employee, M.FirstName AS Manager FROM Employee E INNER JOIN Employee M ON E.ManagerID = M.ID ORDER BY E.FirstName;
What SQL query selects the lesson schedule with an inner join?
Signup and view all the answers
What SQL query selects lesson schedules with multiple joins?
Signup and view all the answers
What SQL query selects tall horses with a subquery?
Signup and view all the answers
What SQL query performs multiple joins with aggregate functions in Sakila?
Signup and view all the answers
What SQL query selects titles of films with nested aggregates in Sakila?
Signup and view all the answers
Study Notes
SQL Query Techniques
-
Select Number of Movies Grouped by Year
- Utilizes the
COUNT(*)
function - Groups results by
Year
to display total movies per year
- Utilizes the
-
Select Movie Ratings with Left Join
- Combines
Movie
andRating
tables - Uses
LEFT JOIN
to ensure all movies are displayed even if they lack a rating - Displays movie title, year, and rating description
- Combines
-
Select Employees and Managers with Inner Join
- Retrieves employee names alongside their managers
- Uses
INNER JOIN
to match employees with their respective managers based onManagerID
-
Select Lesson Schedule with Inner Join
- Lists scheduled lessons for students
- Joins
LessonSchedule
withStudent
- Results are ordered by lesson date and horse ID
-
Select Lesson Schedule with Multiple Joins
- Retrieves lesson schedule data including student names and horse info
- Combines
LessonSchedule
,Student
, andHorse
tables usingLEFT JOIN
- Filters results for a specific date ('2020-02-01')
-
Select Tall Horses with Subquery
- Identifies horses taller than the average height
- Uses a subquery to calculate the average height from the
Horse
table
Aggregate Functions and Nested Queries
-
Multiple Joins with Aggregate (Sakila)
- Averages film lengths for each actor
- Joins
actor
,film_actor
, andfilm
tables - Groups and orders results by average length and actor's last name
-
Nested Aggregates (Sakila)
- Retrieves titles of films that have the minimum count in inventory
- Utilizes nested select statements for aggregate calculations
- Specifically looks for films with the lowest inventory counts
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Description
Test your knowledge of SQL queries through practical lab exercises from Chapter 3 of Database Systems. This quiz focuses on grouping, joining, and selecting data from tables. Perfect for mastering important SQL concepts!