Database Systems Chapter 3 Labs
8 Questions
100 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

Play an AI-generated podcast conversation about this lesson

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?

<p>SELECT LessonSchedule.LessonDateTime, LessonSchedule.HorseID, Student.FirstName, Student.LastName FROM LessonSchedule INNER JOIN Student ON LessonSchedule.StudentID = Student.ID WHERE LessonSchedule.StudentID IS NOT NULL ORDER BY LessonSchedule.LessonDateTime ASC, LessonSchedule.HorseID ASC;</p> Signup and view all the answers

What SQL query selects lesson schedules with multiple joins?

<p>SELECT LessonDateTime, FirstName, LastName, RegisteredName FROM LessonSchedule LEFT JOIN Student ON LessonSchedule.StudentID = Student.ID LEFT JOIN Horse ON LessonSchedule.HorseID = Horse.ID WHERE DATE(LessonDateTime) = '2020-02-01' ORDER BY LessonDateTime ASC, RegisteredName ASC;</p> Signup and view all the answers

What SQL query selects tall horses with a subquery?

<p>SELECT RegisteredName, Height FROM Horse WHERE Height &gt; (SELECT AVG(Height) FROM Horse) ORDER BY Height ASC;</p> Signup and view all the answers

What SQL query performs multiple joins with aggregate functions in Sakila?

<p>SELECT a.last_name, a.first_name, ROUND(AVG(f.length), 0) AS average FROM actor a INNER JOIN film_actor fa ON a.actor_id = fa.actor_id INNER JOIN film f ON fa.film_id = f.film_id GROUP BY a.actor_id, a.last_name, a.first_name ORDER BY average DESC, a.last_name ASC;</p> Signup and view all the answers

What SQL query selects titles of films with nested aggregates in Sakila?

<p>SELECT title FROM film WHERE film_id IN (SELECT film_id FROM inventory GROUP BY film_id HAVING COUNT(film_id)=(SELECT MIN(count_film_id) FROM (SELECT COUNT(film_id) AS count_film_id FROM inventory GROUP BY film_id) AS temp_table));</p> 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

  • Select Movie Ratings with Left Join

    • Combines Movie and Rating tables
    • Uses LEFT JOIN to ensure all movies are displayed even if they lack a rating
    • Displays movie title, year, and rating description

  • Select Employees and Managers with Inner Join

    • Retrieves employee names alongside their managers
    • Uses INNER JOIN to match employees with their respective managers based on ManagerID

  • Select Lesson Schedule with Inner Join

    • Lists scheduled lessons for students
    • Joins LessonSchedule with Student
    • 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, and Horse tables using LEFT 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, and film 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.

Quiz Team

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!

More Like This

Use Quizgecko on...
Browser
Open
Browser
Browser