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 employee_id, position.id as position_id FROM position JOIN employee ON position.id = employee.position_id
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 employee_id and position_id, respectively, to make the result more readable.
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 book_title, and the title column from the adaptation table to adaptation_title.
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.release_year - book.publish_year <= 4
Type:
SELECT book.title AS book_title, adaptation.title AS adaptation_title, book.publish_year, adaptation.release_year FROM book JOIN adaptation ON book.id = adaptation.book_id WHERE adaptation.release_year - book.publish_year <= 4 AND book.rating < adaptation.rating
