Select Statement

The select command is the crux of all DML and is used extensively to query and retrieve data from databases. The select command has the following basic syntax; optional clauses are in square brackets:

select [distinct] <comma_separated_column(s)>
from <table>
[ where <condition1> [and/or] <condition2> ...]
[ order by <comma_separated_column(s) [asc/desc]> ]

A simple SELECT query for the books database and results follow:

select subjectdesc, categoryid from subject

Select Query Results

As you can see, the query just returned the columns subjectdesc and categoryid from the subject table. Columns are returned in the exact order specified.

Now that we have the basic SELECT query under our belts, let us try something new. When we need all the columns in a table in the same order they appear within it, there is really no need to specify each column's name in the SQL command. We may use the asterisk '*' symbol instead. This symbol stands for 'all columns' when used in this context.

select * from category

Select All Columns Results

If we want a list of customers and their addresses in alphabetical order, we could use the ORDER BY clause after the SELECT statement

SELECT lastname, firstname, address
from customer ORDER BY lastname, firstname

Select with Order by - Partial Results

Specifying the letters 'ASC' or nothing at all next to the column name sorts the column in ascending order while specifying 'DESC' sorts the column in descending order. Sometimes, there is a need to leave out duplicates when we get an answer to a question. For example, let us say that we need to know which subjects are represented in the list of books. Obviously, the book table has as many entries for a subject as there are books on the subject. Ten books may be classified under subject 1, five under subject 2 and so on, in which case 1 will appear ten times and 2 will appear five times. We only want a list of the subjects represented; we don't want to see duplicates.

select distinct subjectid from book
order by subjectid

Select with Distinct Results

We can clearly see that all 15 subjects are represented. Run the same query without using the distinct keyword and see what results you will get.

Say you have a special promotion on books that cost under $30 and you want to send flyers containing just these books. How would you get a list of books that cost under $30? SQL offers the 'WHERE' clause to help you do this.

SELECT title, price from book WHERE price <= 30
order by title

Simple Where Clause

If your query returns a large result set that you only need a part of, you may use the TOP keyword to obtain partial results. The following command returns the title of the first 10% (24 rows) of the total number of books in the book table:

SELECT TOP (10) PERCENT title from book

Basically, the 'where' clause made the command go to the database and grab all the rows of information where the column price contained a value less than or equal to 30. The select clause may also contain arithmetic expressions, function results and so on. The following command returns the price of discounted order items after the application of discount against the orderid. The 'AS' keyword allows us to specify an alias for the computed column. The result of the computation is displayed under the header that appears after the AS keyword.

select orderid, unitprice*(100-discount)/100 AS FINALPRICE
from orderdetails
where discount is not null

Expression Usage

Several operators and functions may be used for the appropriate datatypes while performing arithmetic operations after the select clause, the where clause and so on. Following are some important SQL Server built-in functions.