Database Systems Chapter 3 Labs

SELECT Year, COUNT(*) AS Total_Movies FROM Movie GROUP BY Year;

SELECT Movie.Title, Movie.Year, Rating.Description FROM Movie LEFT JOIN Rating ON Movie.RatingCode = Rating.Code;

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;

<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

<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

<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

<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

<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