Using the select statement
As the name Structured Query Language suggests, queries are what SQL is all about. Thus, the select statement is the most important of the SQL statements. A select statement extracts data from one or more tables in a database and creates a result set containing the selected rows and columns.
In a select statement, you list the table or tables from which you want to retrieve the data, the specific table columns you want retrieved (you might not be interested in everything that's in the table), and other clauses that indicate which specific rows should be retrieved, what order the rows should be presented in, and so on.
Here's a simple select statement that retrieves data from a table named movie, which contains information about your favorite movies:
select title, year
from movie
order by year
Now, take this statement apart piece by piece:
- select title, year names the columns you want included in the query result (title and year).
- from movie names the table you want the rows retrieved from (movie).
- order by year indicates that the result should be sorted into sequence by the year column so the oldest movie appears first.
In other words, this select statement retrieves the title and date for all the rows in the movie table and sorts them into year sequence.
If you want the query to retrieve all the columns in each row, you can use an asterisk instead of naming the individual columns:
select * from movie order by year;
Both examples so far include an order by clause. In a SQL database, the rows stored in a table are not assumed to be in any particular sequence. As a result, if you want to display the results of a query in sequence, you must include an order by clause in the select statement.
Suppose you want to find information about one particular video title. To select certain rows from a table, use the where clause in a select statement. For example:
select title, year from movie
where year <= 1980
order by year
Here, the select statement selects all rows in which the year column is less than or equal to 1980. The results are ordered by the year column.
Perhaps you want to retrieve all rows except those that match certain criteria. For example, here's a query that ignores movies made in the 1970s:
select title, year from movie
where year < 1970 or year > 1979
order by year;
There is, of course, much more you can do with select statements. But this tutorial isn't about SQL itself; it's about installing and using SQL Server. So get ready to move on to the good stuff.