FROM
clause are called table variables. They help with making the query more readable, and they rename relations within the FROM
clause when we have more than one instance of a relation.UNION
operator, the INTERSECT
operator, and the EXCEPT
operator.FROM
clause, and replace all the table names with just the table variable.M
after Movie
inside the FROM
clause. That is called adding a table variable, then where ever we used Movie
in our entire select statement, we can just replace with M
. We also did the same thing for User U
and Review R
.Review
. We will call the first instance R1
and the second instance R2
. We also need to include Movie
in the FROM
clause to get the movie Title
.R1
and R2
to separate each instance. We are looking for the mID
's, Title
's, and Rating
's of each movie. We specify DISTINCT
in the WHERE
clause to remove duplicates. We then place a condition in the WHERE
clause to specify that we want to output movies with the same rating. We include the join condition R1.mID = M.mID
to make sure the movies are the same in each relation. We then specify two final clauses: and R1.mID <> R2.mID and R1.uID <> R2.uID;
. R1.mID <> R2.mID
tells the computer that the R1.mID
is different from R2.mID
, or that movie 1 needs to be different from movie 2. If we didn't specify this clause, we would get an output of movies that equal themselves, because movie 1 would have the same Rating
as itself, thus satisfying the condition. Likewise, R1.uID <> R2.uID
makes sure we don't get back two instances of a review by the same reviewer.
UNION
operator allows us to create queries that will output a list of elements that come from multiple tables. Previously we could only separate these elements into different columns. However, by using the UNION
operator, we can get elements from different tables listed into a single column together.Title
or Name
. If you wanted to specify a label for the column, you would use the AS
operator.AS list
in each select clause, and this tells SQL to name the column list
.INTERSECT
operator takes away the necessity to specify a joint relation. It automatically knows that each select statement in the query is for the same movie.mID
of less than 105, we could use the INTERSECT
operator.mID
less than 105.EXCEPT
operator does exactly the opposite of the INTERSECT
operator.mID
less than 105.EXCEPT
operator tells SQL to look for movies that were made before the year 2000, and then take away the movies with a mID
less than 105. This then leaves us with a result of movies which were created before 2000, but have a mID
greater than or equal to 105.