Using the SELECT Statement – SQL Class

This is a short extract from a SQL class on using the SELECT Statement to retrieve data. This text is taken from our SQL Query Writing and Fundamentals course. In our full training, we also offer full screen Demonstrations and Simulations so that you can practice and visualise what you are learning in an interactive environment.

Using the SELECT Statement SQL Class

In the simplest form, the SELECT statement retrieves all rows and columns from the specified table.

The syntax of the SELECT statement to retrieve all the data from a table is:
SELECT * FROM

Consider the query:

 SELECT * FROM Employees

This statement retrieves all rows and columns from the Employees table.

Executing a Query Using the SQL Server Management Studio

You can execute queries using the SQL Server Management Studio.

Note:This topic includes a demo. To view this demo, please refer to the online course.

Retrieving Data from Specific Columns in a Table – SQL Class

The SELECT statement can also be used to retrieve data from specific columns of a table.

To retrieve data from specific columns of a table, the syntax is:
SELECT <column1,column2, …,columnN> FROM <table_name>

Consider the query:

SELECT TitleOfCourtesy, FirstName, LastName, Title
FROM Employees

This SELECT statement retrieves data from the columns TitleOfCourtesy, FirstName, LastName, and Title for all rows of the table Employees.

Filtering Rows with the WHERE Clause

You may want to view those rows that meet specific conditions. In such situations, the WHERE clause is used with the SELECT statement.

The syntax of a SELECT query that uses the WHERE clause is:
SELECT <column1,column2, …,columnN>
FROM <table_name>
WHERE <column> <operator> <value1>

Consider the query:

SELECT TitleOfCourtesy, FirstName, LastName, Title
FROM Employees
WHERE Title='Sales Representative'

This statement retrieves data only from those rows where the Title column has the value Sales Representative.

Using Operators to Create Conditions

An operator is a character or a reserved word that is used to specify a condition, or combine two or more conditions. The operators are used with the WHERE clause in the SELECT statement to set the filter criteria for data.

There are four types of operators:

The syntax for using an operator in a query is:

SELECT <column1,column2, …,columnN>
FROM <table_name>
WHERE <expression1> <operator> <expression2>

We shall look at each category of operators in detail in the topics to follow.

What are Comparison Operators?

The comparison operators shown in the table below are used to compare two expressions.

Consider the query:

 SELECT ProductName,UnitsInStock,UnitsOnOrder
 FROM Products
 WHERE UnitsOnOrder >=70

This query retrieves data only from those rows of the Products table where the value ofUnitsInStock is greater than or equal to 70.

What are Logical Operators?

Logical operators are used to test if a specified condition is true or not. They are also used to combine multiple conditions.

Some of the commonly used logical operators are:

Consider the following query:

SELECT CompanyName,City
FROM Customers
WHERE City = 'London' OR City = 'Madrid'

This query retrieves data only from those rows of the Products table where the City is London or Madrid.

What are List Operators?

The IN list operator checks if the result of the expression meets one of the specified values.

The syntax for using a list operator in a query is:
SELECT <column1,column2, …,columnN>
FROM <table_name>
WHERE <column> IN ('value1','value2', …,'valueN')

Note: Use NOT IN to retrieve rows that do not meet the specified criteria.

Consider the query:
SELECT CompanyName,City
FROM Customers
WHERE City IN ('London','Madrid','Paris')

This query retrieves data only from those rows of the Products table where the City is London,Madrid and Paris.

What is the NULL Keyword?

A NULL value in a column means that there is no data in the column. You can retrieve rows which contain NULL values using the IS NULL keyword with the WHERE clause.

The syntax for retrieving records with NULL values is:
SELECT <column1,column2, …,columnN>
FROM <table_name>
WHERE <column1> IS NULL

Consider the query:

SELECT CompanyName,City,Region,Country
FROM Customers
WHERE REGION IS NULL

This query retrieves data only from those rows of the Customers table where the Region is NULL.

Sorting the Result Set with the ORDER BY Clause

The ORDER BY clause sorts the retrieved results in the ascending or descending order of the values in the specified columns. The ASC and DESC keywords specify the sort order. If the sort order is not specified, the results are sorted in the ascending order.

The syntax for using the ORDER BY clause is:
SELECT <column1,column2, …,columnN>
FROM <table_name>
ORDER BY <column1 [ASC|DESC],column2 [ASC|DESC], …,columnN [ASC|DESC]>

Consider the query:

 SELECT * FROM [Order Details] ORDER BY Quantity ASC

This query retrieves all the rows and columns from the [Order Details] table and sorts the rows in the ascending order by the column Quantity.

Using the DISTINCT keyword to Suppress Duplicate Rows

Queries may return result sets with duplicate rows. The DISTINCT keyword eliminates redundant rows from a result set.

The syntax for limiting result sets with the DISTINCT keyword is:
SELECT DISTINCT <column1,column2,…,columnN>
FROM <table_name>

Consider the query:

SELECT DISTINCT OrderID,Quantity
FROM [Order Details]

This query retrieves distinct values of the OrderID and the Quantity columns from the Order Details table.

Viewing Top Rows in a Large Result Set

When accessing a large database, you can set a limit to the number of records you want to retrieve using the TOP keyword.

The syntax for retrieving the top N (Number) rows is:
SELECT TOP <N> <column1,column2, …,columnN>
FROM <table_name>

You can also specify the percentage of rows to be retrieved. The syntax for retrieving the top N percentage of rows is:
SELECT TOP <N> PERCENT <column1,column2, …,columnN>
FROM <table_name>

Consider the query:

 SELECT TOP 30 ProductName FROM Products

This query retrieves the top 30 rows from the Products table.

We hope this mini SQL Class has given you some tips on using the SELECT statement. Good Luck with your queries!