How To Select Data From Table in SQL Server

SELECT Statement is used to retrieve data from database table(s). The retrieved data stored in a result table, called result set.

We can use the SELECT statement in the following ways.

SELECT all columns using *
SELECT by column names
SELECT DISTINCT rows
Filter with WHERE clause
SELECT rows by multiple conditions using AND / OR Operators
Filter data using Wild Cards
Sort selected rows using ORDER BY clause
SELECT TOP N or TOP N Percent rows

For demo, first we will create a table tblEmployee and insert some records and practice for SELECT Statement.

CREATE TABLE tblEmployee
(
	EmpID INT PRIMARY KEY NOT NULL,
	EmpFirstName NVARCHAR(20) NOT NULL,
	EmpLastName NVARCHAR(20) NOT NULL,
	EmpAge INT NULL,
	EmpGender NVARCHAR(10) NULL,
	EmpEmail NVARCHAR(50) NULL,
	EmpPhone NVARCHAR(20) NULL,
	EmpSalary INT NULL,
	EmpJoiningDate DATETIME NULL
)
GO

INSERT INTO tblEmployee
    (EmpID, EmpFirstName,EmpLastName,EmpAge,EmpGender,EmpEmail,EmpPhone,EmpSalary,EmpJoiningDate)
VALUES
    (1,'john','dave',26,'male','john@gmail.com','0923044554561',26000,'2010-02-01'),
    (2,'kamran','akmal',35,'male','kami@gmail.com','0923044545562',25000,'2011-01-01'),
    (3,'umar','akmal',36,'male','umar@gmail.com','0923044554563',38000,'2015-01-01'),
    (4,'thomas','hardy',36,'male','thomas@gmail.com','0923044554564',25000,'2016-05-05'),
    (5,'amar','sidhu',36,'male','amar@gmail.com','0923044554560',25000,'2016-05-05'),
    (6,'simran','sidhu',25,'female','simran@gmail.com','0923044554565',33000,'2018-01-01'),
    (7,'diyana','jeni',26,'female','diyana@gmail.com','0923044554566',50000,'2005-01-01'),
    (8,'maria','anders',27,'female','maria@gmail.com','0923044554567',70000,'2004-01-01'),
    (9,'anam','chaudhary',29,'female','anam@gmail.com','0923044554568',25000,'2020-01-01'),
    (10,'Amelia','sidhu',56,'female','amelia@gmail.com','0923044554569',50000,'2015-01-01')
    GO

SELECT All Columns Using *

We can retrieve data from a table without writing the names of each column using *. Following syntax is used to get all records using *.

SELECT * FROM tblEmployee

SELECT By Column Names

We can specify all columns or some specific columns in the SELECT query.

SELECT
	EmpFirstName,
	EmpLastName,
	EmpAge,
	EmpGender,
	EmpSalary
FROM    tblEmployee
GO

The result of the above select query with specific column names is shown below.

SELECT DISTINCT Rows

We can find the distinct EmpGender using the DISTINCT keyword.

SELECT
DISTINCT EmpGender AS Gender
FROM
tblEmployee
GO

The result of the above select query with DISTINCT gender names as shown below.

Filter with WHERE Clause

We can filter the rows using the WHERE clause in the SELECT Statement. Suppose we want all the employees whose EmpGender is male, we will write the query as…

SELECT * FROM tblEmployee
WHERE
          EmpGender='male'
GO

The result of the above select query with WHERE clause is shown below.

SELECT Rows by Multiple Conditions Using AND / OR Operator

In the SELECT Statement, we can filter the rows by multiple conditions in the where clause using AND / OR operator. In the following query, we find all the employees whose EmpGender is male and EmpSalary is greater than 25000.

SELECT
    EmpFirstName,
    EmpLastName,
     EmpPhone,
    EmpSalary
FROM
    tblEmployee
WHERE
    EmpGender='male'AND EmpSalary>25000
GO

The result of the above select query with multiple conditions is as follows.

Filter Rows using Wildcard

In the SELECT Statement, we can filter the rows by using Wildcards. Suppose we want to filter the rows whose EmpFirstName starts with ‘a’, then we use the Wildcard(% symbol after ‘a’ along with LIKE Operator.)

SELECT
    EmpFirstName,
    EmpLastName,
    EmpPhone,
    EmpGender
FROM
    tblEmployee
WHERE
    EmpFirstName LIKE 'a%'
GO

The result of the above select query with Wildcard character is as below.

Sort Selected Rows Using Order By

In the SELECT Statement, we can sort rows either in ascending (ASC) or descending (DESC) order using Order By clause. We can sort rows by one column or multiple columns.

First, We will see descending order on a single column.

SELECT
EmpFirstName,
EmpLastName,
EmpPhone,
EmpGender
FROM
tblEmployee
ORDER BY
EmpFirstName DESC
GO

The result of the above select query with Order By single column is shown below.

SELECT statement with Order By clause with multiple columns, First it will sort the result set first by first column and then sort by second column.

In the following example, we will order by EmpGender first and then order by EmpFirstName.

SELECT
EmpFirstName,
EmpLastName,
EmpPhone,
EmpGender
FROM
tblEmployee
ORDER BY
EmpGender, EmpFirstName DESC
GO

The result of the above select query with Order By multiple columns is shown below.

SELECT TOP N or TOP N Percent rows

We can SELECT TOP N or TOP N Percent Rows from a table, Where N is the number of rows and TOP N percent mean the rows in percentage from the total rows of the table.

SELECT TOP 5
EmpFirstName,
EmpLastName,
EmpPhone,
EmpGender
FROM
tblEmployee
GO

The result of the above select query with TOP 5 records is shown below.