13 Write a SQL statement that queries a table named Movies a

13. Write a SQL statement that queries a table named Movies and retrieves columns named Title and Year. The query should return only the rows where the Director column is equal to “Hitchcock”. The results should be sorted by the Year field, with the most recent movies listed first.

   14.   Suppose that a database contains two tables, named Movies and Directors. The Movies table has columns named Title, Year, and DirectorID. The Directors table has columns named DirectorID and DirectorName. Write a SQL statement that uses a join to return the title, year, and the name of the director for every movie in the database. Sort the rows by the directors’ names.

   15.   Write a SQL statement that changes the DirectorName column in the Directors table for DirectorID 37 to “Hitchcock”.

Solution

13)

SELECT m.Title, m.Year
FROM Movies m, Directors d
WHERE m.DirectorID = d.DirectorID
AND d.DirectorName = \'Hitchcock\'
ORDER BY m.Year DESC;

14)

SELECT m.Title, m.Year, d.DirectorName
FROM Movies m, Directors d
WHERE m.DirectorID = d.DirectorID
ORDER BY d.DirectorName;

15)

UPDATE Directors
SET DirectorName = \'Hitchcock\'
WHERE DirectorID = 37;