Retrieving Data

Selecting data using SELECT statement

The SELECT statement is used to retrieve data from one or more tables from the database.

Basic SELECT Syntax

The structure of an SQL statement.

SELECT select_list 
FROM table_list 
WHERE predicates 
GROUP BY columns
HAVING condition
ORDER BY sort_key_columns

Select all products from the products tablespoon

SELECT *
FROM products

Use the wildcard character *, we can retrieve all the rows and all the columns from the products tables.

Select a single column

SELECT product_name
FROM products

By only choosing one column, we will retrieve only rows with the specified column from the database.

Retrieving multiple columns

SELECT prod_id, prod_name, prod_price
FROM products

Whitespace is ignored when writing sql statements. We could have puts the whole sql statement in one line and would have worked the same. Also the sql statements are case insensitive, SELECT and select are treated the same.

Retrieving distinct rows

We can use the DISTINCT keyword to remove duplicates from the returned rows.

SELECT DISTINCT vend_id
FROM products

The query will return distinct vendor ids from the the products table;

Note

The DISTINCT keyword applies to all rows not only the one column it precedes. If you specify two columns, the DISTINCT will apply to both columns not to an individual column, so all rows will be returned.

Limiting results

THE LIMIT keyword is used to limit results returned from the database. We can specify a number or a percentage on the number of rows to return. The statement below will return the first 5 rows from the products table.

SELECT TOP 5 * 
FROM products

We can also specify a percentage to return, e.g. to return the top 10 % from the products table we can use the following sql statement.

SELECT TOP 20%
FROM products

Comments

Comments are used to document your sql statements. These statements are ignored and not processed, they are mainly for yourself.

Single line comment

The single line comment starts with –, two dashes, anything after the dashes is ignored.

-- This is a single line comment, any statements after the two -- are ignored
SELECT * 
FROM products

Multiline comment

The multiline comment starts with with /* and ends with */ characters. Anything in between the two character combination will be ignored.

/*
This is a multiple line comment
anything between here
and the last character will be ignored.
*/
SELECT * 
FROM products