Tip of the Day : SQL Server Tutorial - SELECT Statement 101

Welcome to SQL Server Helper !!!

This site is intended for those who are beginning to use SQL Server as part of their day-to-day activities.  You will find in this site a collection of useful functions, triggers, stored procedures and tips and tricks related to SQL Server.

Should you have any comments or questions regarding this site or if you want to ask SQL Server-related questions, e-mail us here.

We hope we are able to help and we are glad to help!!!

SQL Server Tip of the Day - November 12, 2018

SQL Server Tutorial - SELECT Statement 101

The SELECT statement is used to retrieve rows from the database and enables the selection of one or many rows and one or many columns from one or many tables.  

The full syntax of the SELECT statement is complex but it can be broken down into the following clauses or elements:

  • WITH <common_table_expression>
  • SELECT Clause
  • INTO Clause
  • FROM Clause
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • UNION Clause
  • EXCEPT and INTERSECT Clause
  • ORDER BY Clause
  • FOR Clause
  • OPTION Clause

Here are a few examples of how to use the SELECT statement:

Using SELECT to Retrieve All Rows and All Columns from a Table

SELECT * FROM [dbo].[Sales]

Using SELECT to Retrieve All Columns From a Table But Only Some Rows Based on a Condition

SELECT * FROM [dbo].[Sales]
WHERE [TransactionAmount] > 1000.00

Using SELECT to Return Rows from a Table in Ascending Order

SELECT * FROM [dbo].[Customer]
ORDER BY [CustomerName] ASC

Using SELECT to Return Rows from a Table in Descending Order

SELECT * FROM [dbo].[Sales]
ORDER BY [SalesAmount] DESC

Using SELECT to Return DISTINCT or Unique Rows

SELECT DISTINCT [ProductName], [UnitPrice], [Quantity]
FROM [dbo].[Product]

Using SELECT to Return Specified Number of Rows

SELECT TOP 100 [ProductName], [UnitPrice], [Quantity]
FROM [dbo].[Product]

Using SELECT to Return Literal Values Instead of From a Table

SELECT 'USD' AS [CurrencyCode], 'US Dollar' AS [CurrencyName]
UNION ALL
SELECT 'CAD' AS [CurrencyCode], 'Canadian Dollar' AS [CurrencyName]
UNION ALL
SELECT 'EUR' AS [CurrencyCode], 'Euro' AS [CurrencyName]

Using SELECT to Assign Literal Values to Local Variables

DECLARE @Index        INT
DECLARE @CountryCode  CHAR(2)

SELECT @Index = 100, @CountryCode = 'US'

Using SELECT to Assign Table Values to Local Variables

DECLARE @FirstName      VARCHAR(50)
DECLARE @LastName       VARCHAR(50)

SELECT @FirstName = [FirstName], @LastName = [LastName]
FROM [dbo].[User]
WHERE [UserName] = 'sqlserver'

Using SELECT to Retrieve Certain Number of Rows that Satisfy a Condition in a Specified Order

SELECT TOP 100 [EmployeeNumber], [FirstName], [LastName]
FROM [dbo].[Employees]
WHERE [HiringDate] >= '2012/01/01'
ORDER BY [HiringDate] DESC, [LastName] ASC, [FirstName] ASC

SQL Server 2012

SQL Server 2008

User-Defined Functions

Date Functions

A collection of useful user-defined functions that deal with dates.

String Functions

A collection of useful user-defined functions that deal with strings (varchar/char/nvarchar/nchar).

Tree Functions

A collection of useful user-defined functions that deal with tree or hierarchical design structures.

Table-Valued Functions

A collection of useful table-valued user-defined functions that can be used to join with other tables.

SQL Server Built-in Functions

A reference to all built-in functions available within SQL Server grouped into categories.

Tips and Tricks

A collection of useful SQL Server-related tips and tricks:

SQL Server Error Messages

A list of SQL Server error messages and for certain error messages, discusses ways on how to solve the error or work around them:

Frequently Asked Questions