# Basic Select Statements
\
### The Three Clauses:
The select statement has three clauses: the `FROM` clause, the `WHERE` clause, and the `SELECT` clause. The basic concept is that the `FROM` clause identifies the relation that you want to query over, the 'WHERE' condition is used to combine the relations and filter the relations, and the `SELECT` tells you what to return.
The syntax looks like this:
```sql
SELECT A1, A2, . . . , A(n)
FROM R1, R2, . . . , R(m)
WHERE ;
```
Since relational query languages are compositional, when you run a query over relations, you get a relation as a result. Thus, the result of the above select statement is a relation, but it doesn’t have a name. The schema of that relation is the set of attributes that are returned.
### A Basic Query:
Assume we have a database with three tables:
1. A `MOVIE` table that has four columns labeled `mID` (movie ID), `Title`, `Year`, and `Director`.
2. A `User` table that has two columns labeled `uID` (User ID), and `Name`.
3. Lastly, a `Review` table that has four columns labeled `uID`, `mID`, `Rating`, and `ratingDate`.
*(This sample database can be viewed in the Introduction chapter of this book.)*
***
We’re going to do a basic query that finds the `Title`, and `Year` of movies that were created after the year 2000. The `SELECT` tells us what we want to get out of the query, the `FROM` tells us our table name, and the `WHERE` gives us the filtering condition.
The query would look like this:
```sql
SELECT Title, Year
FROM Movie
WHERE Year > 2000;
```
We would then get a table back that would have two columns labeled `Title`, and `Year`. It would then display all the movies that were created after the year 2000.
The resulting movies would include: *Gravity*, *Harry Potter*, *Cast Away*, and *Spiderman*.
### Combing Two Relations:
Now let’s create a query that combines two relations, such as finding movie titles, mID's and the rating that the movie received. We’re now involving the `Movie` table, and the `Review` table.
Combining relations looks like this:
```sql
SELECT Movie.mID, Title, Rating
FROM Movie, Review
WHERE Movie.mID = Review.mID;
```
The condition above is called a **join** condition and is saying that we want to combine movies with review statistics that have the same `mID`. We would then get a table as a result with three columns labeled `mID`, `Title`, and `Rating`. It would then display all the movies with their `mID` and their `Rating`.
### Combing Two Relations w/ a Condition:
The next query is going to find the `Title`, `mID` and `Rating` of movies that were created before the year 2000, and `Rating` is greater than 2.
It would look like this:
```sql
SELECT Movie.mID, Title, Rating
FROM Movie, Review
WHERE Movie.mID = Review.mID
and Rating > 2 and Year < 2000;
```
So in this case, we are looking for `mID`, `Title`, and `Rating`. We are looking inside the `Movie` and `Review` tables, and we have a join condition making sure that the query knows the `mID` in the `Movie` table is the same `mID` in the `Review` table. We are filtering the results based on the year the movie was created, and the rating it received. 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.
The resulting movies would be: *Top Gun*, *Titanic*, *The Lion King*, and *The Godfather*.
### Combining Three Relations:
This time we are going to combine all three relations, and we’re going to get a table with the results of every `mID`, `Title`, `Year`, User `Name`, and `Rating`.
It would look like this:
```sql
SELECT Movie.mID, Title, Year, Name, User.uID, Rating
FROM Movie, User, Review
WHERE Movie.mID = Review.mID and User.uID = Review.uID;
```
Notice how in the `SELECT` and `WHERE` statements, we specify which table we want to pull some of the attributes out of. Since there is more than one table with `mID` and `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.
### Sorting Table Results:
SQL by default does not order table results in any particular order. However, if we specify a specific order that we want, we can get results sorted by a specific attribute, or set of attributes. Say we want to sort all of our movies by descending `Rating`. In order to do this, we need to add an additional clause called the `ORDER BY` clause. If we want to get a descending order, we write what we want to search for and then use the keyword `DESC`.
It would look like this:
```sql
SELECT Movie.mID, Title, Year, Name, User.uID, Rating
FROM Movie, User, Review
WHERE Movie.mID = Review.mID and User.uID = Review.uID
ORDER BY Rating DESC;
```
If we wanted to have it sort by additional attributes, we would just put a comma after `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.
### Doing Arithmetic within Select Statements:
While doing a `SELECT` statement, 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.
The query would look like this:
```sql
SELECT Movie.mID, Title, Rating, Director, Rating + 10
FROM Movie, Review;
```
We would then get a table with all of the above attributes, and then an additional column that shows the movie's `Rating` after being scaled by 10. However, we will get a column labeled `Rating + 10`, but we want to change it to a different particular label.
We would just use the AS clause like so:
```sql
SELECT Movie.mID, Title, Rating, Director, Rating + 10 AS ScaledRating
FROM Movie, Review;
```
We would then have a column labeled `ScaledRating` instead of `Rating + 10`.
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://hunter-ducharme.gitbook.io/sql-basics/basic_select_statements.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.