FROMclause are called table variables. They help with making the query more readable, and they rename relations within the
FROMclause when we have more than one instance of a relation.
INTERSECToperator, and the
FROMclause, and replace all the table names with just the table variable.
FROMclause. That is called adding a table variable, then where ever we used
Moviein our entire select statement, we can just replace with
M. We also did the same thing for
Review. We will call the first instance
R1and the second instance
R2. We also need to include
FROMclause to get the movie
R2to separate each instance. We are looking for the
Rating's of each movie. We specify
WHEREclause to remove duplicates. We then place a condition in the
WHEREclause to specify that we want to output movies with the same rating. We include the join condition
R1.mID = M.mIDto 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.mIDtells the computer that the
R1.mIDis 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
Ratingas itself, thus satisfying the condition. Likewise,
R1.uID <> R2.uIDmakes sure we don't get back two instances of a review by the same reviewer.
UNIONoperator 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
UNIONoperator, we can get elements from different tables listed into a single column together.
Name. If you wanted to specify a label for the column, you would use the
AS listin each select clause, and this tells SQL to name the column
INTERSECToperator takes away the necessity to specify a joint relation. It automatically knows that each select statement in the query is for the same movie.
mIDof less than 105, we could use the
mIDless than 105.
EXCEPToperator does exactly the opposite of the
mIDless than 105.
EXCEPToperator tells SQL to look for movies that were made before the year 2000, and then take away the movies with a
mIDless than 105. This then leaves us with a result of movies which were created before 2000, but have a
mIDgreater than or equal to 105.