Instruction
Great!
When joining tables, you'll sometimes find that they share a few column names. For example, it is common for tables to have an Id column. In this case, if two or more tables you're joining share some column names, you must always refer to those identical column names using the table name followed by a dot and the name of the column you want to select.
SELECT Employee.Id AS EmployeeId, Position.Id AS PositionId FROM Position JOIN Employee ON Position.Id = Employee.PositionId
In the SELECT clause we specify the columns as Employee.Id and Position.Id to avoid ambiguity. If we don't do that, the database won't know which Id column we are referring to.
We also aliased the columns to EmployeeId and PositionId, respectively, to make the result more readable.
Exercise
For each book, show its title, adaptation title, adaptation year, and publication year.
Consider only books with a rating lower than the rating of their corresponding adaptation. Additionally, show only those books for which an adaptation was released at most four years after the book was published.
Rename the Title column from the Book table to BookTitle, and the Title column from the Adaptation table to AdaptationTitle.
Stuck? Here's a hint!
You can filter your query by various conditions. Simply use the WHERE keyword for the first condition and the AND keyword before all others.
To select only adaptations that were created at most four years after the corresponding book was published, you will have to write:
Adaptation.ReleaseYear - Book.PublishYear <= 4
Type:
SELECT Book.Title AS BookTitle, Adaptation.Title AS AdaptationTitle, Book.PublishYear, Adaptation.ReleaseYear FROM Book JOIN Adaptation ON Book.Id = Adaptation.BookId WHERE Adaptation.ReleaseYear - Book.PublishYear <= 4 AND Book.Rating < Adaptation.Rating
