SQL Server SELECT Query Examples based on single table

In the previous post you learnt about different clauses used in select statement. In this post you will see different examples of select statement. We will use simple examples first and then move to more complex examples. A select query can be very complex if multiple tables are used and joined them to get the result set. In this post will focus on select query using only one table.

You should focus on the columns expressions and functions that can be used in a clause of the select statement. There are different operators that can be used with an expression; understanding of these operators is important.

In this post we will create Employee table by running SQL script. Use the following script to create Employees table. Paste the script in SSMS Query Editor and press F5 or CTRL+E to run the script.

USE [Appliedk]
GO
IF OBJECT_ID('dbo.tbl_employees','U') IS NOT NULL
	DROP TABLE tbl_employees;
GO
CREATE TABLE  dbo.tbl_Employees (
	 Emp_ID  int ,
	 First_Name  nvarchar(30) NULL,
	 Last_Name  nvarchar(30) NULL,
	 Gender  nvarchar(30) NULL,
	 Date_of_Birth  datetime NULL,
	 Date_of_Joining  datetime NULL,
	 Salary  int NULL,
	 Department_ID  int NULL
) 
GO
INSERT dbo.tbl_Employees VALUES 
  (1, 'Nisha', 'Gupta', 'F', '3/29/1981', '11/24/2003', 20000, 2)
, (2, 'Mira', 'Gupta', 'F', '7/31/1970', '7/27/2008', 70000, 3)
, (3, 'Ajeet', '''''', 'M', '6/16/1963', '8/3/2016', 60000, 4)
, (4, 'Gita', 'Singh', 'F', '12/4/1977', '4/16/1999', 15000, 1)
, (5, 'Mahip', NULL, 'M', '4/17/1977', '7/25/2013', 25000, 2)
, (6, 'Rakesh', 'Roy', 'M', '9/27/1982', '7/22/2005', NULL, 3)
, (7, 'Rupa', 'Singh', 'F', '6/13/1995', '9/14/2016', 45000, 4)
, (8, 'Chitra', 'Roy', 'F', '6/30/1958', '1/28/1983', 55000, 1)
, (9, 'Jaya', 'Gupta', 'F', '6/9/1959', '4/27/1994', NULL, 2)
, (10, 'Rina', 'Singh', 'F', '11/13/1987', '2/17/2014', 75000, 3)
GO

Find all the rows and columns of a table.

To find all the columns of a table we use asterisk symbol after select clause.

SELECT *
FROM tbl_Employees

In the production environment we should specify all the column names after select clause instead of asterisk symbol.

SELECT Emp_ID
      ,First_Name
      ,Last_Name
      ,Gender
      ,Date_of_Birth
      ,Date_of_Joining
      ,Salary
     ,Department_Id
FROM tbl_Employees

Select some columns of a table

SELECT Emp_ID
      ,First_Name
      ,Last_Name
      ,Gender
      ,Salary
FROM tbl_Employees

Select all columns of a table plus create a calculated column

SELECT *, salary*1.1 as [Increased Salary]
FROM tbl_Employees

Filter records of a table using WHERE clause

SELECT *
FROM tbl_Employees
WHERE Gender='F'

Sort records of a table using ORDER BY clause on a column

SELECT *
FROM tbl_Employees
WHERE Gender='F'
ORDER BY Salary

Sort records of a table using ORDER BY clause on a column in descending order

SELECT *
FROM tbl_Employees
WHERE Gender='F'
ORDER BY Salary DESC

Sort records of a table by a column that is not in the select list

SELECT Emp_ID, First_Name, Last_Name, Gender
FROM tbl_Employees
WHERE Gender='F'
ORDER BY Salary DESC

Sort records of a table by an expression based on a column of table

SELECT *
FROM tbl_Employees
ORDER BY LEN(First_Name)

Sort records of a table by a column using its ordinal position

SELECT *
FROM tbl_Employees
ORDER BY 3

Multi level Sorting- Sort records of a table using ORDER BY clause on multiple columns

SELECT *
FROM tbl_Employees
ORDER BY Gender, Salary

Multi level Sorting- Sort records of a table using ORDER BY clause on multiple columns in different sorting orders

SELECT *
FROM tbl_Employees
ORDER BY Gender DESC, Salary ASC

Multi level Sorting- Sort records of a table by multiple columns using their ordinal positions

SELECT *
FROM tbl_Employees
ORDER BY 2, 3

Group records of a table using GROUP BY clause on a column

SELECT Gender, Sum(Salary), count(emp_id)
FROM tbl_Employees
GROUP BY Gender

Group records of a table using GROUP BY clause on a column and count each group

SELECT Gender, count(*)
FROM tbl_Employees
GROUP BY Gender

Group records of a table using GROUP BY clause on a grouping column and get average of a numeric column

SELECT gender, AVG(Salary) as [average salary]
FROM tbl_Employees
group by gender

Filter grouped records of a table using HAVING clause

SELECT county, count(gender) as Count Sex, SUM(Salary)
FROM tbl_Employees
GROUP BY county
HAVING count(gender) > 2

Select distinct items for a column

SELECT distinct county
FROM tbl_Employees

Comparison Operators

• IN
• BETWEEN AND
• AND
• OR
• LIKE

The above comparison operators are used in a comparison expression. The expression returns a Boolean value. If the value is true for a row then that row is included in the result set.

IS NULL Example

SELECT *
FROM tbl_Employees
WHERE Salary is null

Use OFFSET clause to skip some records of a table. The OFFSET keyword is followed by an integer/expression to skip that many records of the table. FETCH NEXT is an optional clause which may be used after OFFSET.

• OFFSET n ROWS
• FETCH NEXT m ROWS ONLY

SELECT *
FROM tbl_Employees
ORDER BY Emp_ID
OFFSET 15 ROWS -- offset is after the order by and order by is must

Must use the OFFSET and FETCH clauses with the ORDER BY clause. Otherwise, there will be an error. The OFFSET and FETCH clauses have been available since SQL Server 2012 (11.x) and later and Azure SQL Database.

Get 3rd largest salary

SELECT *
FROM tbl_Employees
ORDER BY salary desc
OFFSET 2 ROWS 
FETCH NEXT 1 ROWS ONLY

Get first three largest salary

SELECT *
FROM tbl_Employees
ORDER BY salary desc
OFFSET 0 ROWS 
FETCH NEXT 3 ROWS ONLY

Get first three largest salary

SELECT top 3 *
FROM tbl_Employees
ORDER BY salary desc

Get first 10 percent of largest salary

SELECT top 10 percent *
FROM tbl_Employees
ORDER BY salary desc

Get top 9 employees with highest salary

SELECT top 9 with ties 
*
FROM tbl_Employees
ORDER BY salary desc

find duplicate names

SELECT First_Name ,Last_Name, count(*) as counts
  FROM tbl_Employees
  group by  First_Name ,Last_Name
 having count(*) > 1

find records without duplicate names

f
 

comparison operators

SELECT *
FROM tbl_Employees
WHERE Salary > 70000

comparison operators

SELECT *
FROM tbl_Employees
WHERE Salary > 70000 and Salary < 80000

comparison operators

SELECT *
FROM tbl_Employees
WHERE Salary between 70000 and 80000

comparison operators

SELECT *
FROM tbl_Employees
WHERE Salary not between 70000 and 80000

comparison operators

SELECT *
FROM tbl_Employees
WHERE Salary > 70000 and Gender= 'F'

comparison operators

SELECT *
FROM tbl_Employees
WHERE Salary is null
  

comparison operators

SELECT *
FROM tbl_Employees
WHERE First_Name ='Diana' or Last_Name = 'Murphy'
order by First_Name

comparison operators

SELECT *
FROM tbl_Employees
WHERE salary in (55000,65000,75000)
order by Salary

comparison operators

SELECT count(*)
FROM tbl_Employees
WHERE salary in (55000,65000,75000)

comparison operators

SELECT *
FROM tbl_Employees
WHERE E_Mail like '%hot%'

comparison operators

SELECT *
FROM tbl_Employees
WHERE E_Mail like 'd%'

comparison operators

SELECT *
FROM tbl_Employees
WHERE E_Mail like '%uk'

correct email based on firstname and lastname

SELECT *,  LOWER(First_Name) +'.'+ LOWER(Last_Name) + SUBSTRING(E_mail, CHARINDEX('@',0), Len(E_Mail)-CHARINDEX('@',0)) As Email
FROM tbl_Employees

create a new table

SELECT *,  LOWER(First_Name) +'.'+ LOWER(Last_Name) + SUBSTRING(E_mail, CHARINDEX('@',0), Len(E_Mail)-CHARINDEX('@',0)) As Email
into tblNew
FROM tbl_Employees

create a new table

SELECT Emp_ID
      ,First_Name
      ,Last_Name
      ,Gender
      ,Salary
  INTO tblEmpNew
  FROM tbl_Employees

In this post we have learnt how to query a single table. In the forthcoming posts, we will learn querying multiple Tables by joining them. Also we will learn different kind of subqueries and the lookup table concept.