WHEREclause, and the
SELECTclause. The basic concept is that the
FROMclause identifies the relation that you want to query over, the 'WHERE' condition is used to combine the relations and filter the relations, and the
SELECTtells you what to return.
MOVIEtable that has four columns labeled
Usertable that has two columns labeled
uID(User ID), and
Reviewtable that has four columns labeled
Yearof movies that were created after the year 2000. The
SELECTtells us what we want to get out of the query, the
FROMtells us our table name, and the
WHEREgives us the filtering condition.
Year. It would then display all the movies that were created after the year 2000.
Movietable, and the
mID. We would then get a table as a result with three columns labeled
Rating. It would then display all the movies with their
Ratingof movies that were created before the year 2000, and
Ratingis greater than 2.
Rating. We are looking inside the
Reviewtables, and we have a join condition making sure that the query knows the
Movietable is the same
Reviewtable. We are filtering the results based on the year the movie was created, and the rating it recieved. We would then get a table with the results of the query. The results would include all movies that were created before the year 2000, with a rating greater than 2.
WHEREstatements, we specify which table we want to pull some of the attributes out of. Since there is more than one table with
uID, we need to specify which table we want to pull it out of. It doesn’t matter which table we specify, but if we don’t specify we will get an error because the computer doesn't know which table to pull from.
Rating. In order to do this, we need to add an additional clause called the
ORDER BYclause. If we want to get a descending order, we write what we want to search for and then use the keyword
DESC, and add another attribute. However, SQL defaults to ascending order, so you need to specify which way you prefer for any additional attributes that you add.
SELECTstatement, SQL allows for doing arithmetic operations. Say we want to find all the movie's attributes, but add to it a scaled
Rating. Where we are going to scale the rating by 10 to get ratings that are in the teens.
Ratingafter being scaled by 10. However, we will get a column labeled
Rating + 10, but we want to change it to a different particular label.
Rating + 10.