Sign up for Hasura Newsletter

SELECT

SELECT is a T-SQL command of the type Data Query Language (DQL). A SELECT query is used to retrieve rows from a table.

Prerequisite

Download sample database

Download the sample database here: AdventureWorks2019.bak.

Select the OLTP data that is used for most typical online transaction processing workloads.

Move the .bak file to your SQL Server backup location. The default location for a default instance of SQL Server 2019 is: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup.

Restore to SQL Server

Next, you can use the .bak file to restore the sample database into your SQL Server.

Before you begin

  1. In the Object Explorer, click to expand AdventureWorks2019 > Tables.
  2. Select HumanResources.Employee, and then click New Query from the top ribbon bar. where,
  • HumanResources is the schema name.
  • Employee is a table in the above schema.
  1. Run the below statement to use the 'AdventureWorks2019' sample database.
USE AdventureWorks2019;
GO

SELECT T SQL statements

The statement is used to retrieve all of the columns or some columns based on certain clauses.

Syntax

SELECT [Arguments]
FROM <table_name>
WHERE <search_condition>
GROUP BY <group_by_expression>
HAVING <search_condition>
ORDER BY <order_expression> [ASC|DESC]

The Arguments may be any of the following:

  • ALL: The result set returns all the rows and columns, and may include duplicate values. 'ALL' is the default.
  • DISTINCT: Returns unique rows.
  • column_list: A single column name, or a list of comma-separated column names.
  • *: The result set returns all columns and rows from a table/view, as specified in the 'FROM' clause.

Some of the common select query clauses are:

Select to retrieve all rows and columns

SELECT * FROM HumanResources.Employee;

All rows

Get the count of all rows in a table

SELECT COUNT(*) FROM HumanResources.Employee;

Count of rows

Select certain columns

SELECT NationalIDNumber,JobTitle FROM HumanResources.Employee;

only2cols

Select columns and print an alias column name

--First column is renamed as 'SSN'
SELECT NationalIDNumber AS 'SSN', HireDate
FROM HumanResources.Employee;

Alias column name

Derived column

A derived column is based on the calculation results on another column(s).

SELECT ( VacationHours + SickLeaveHours ) AS 'TOTAL LEAVES' FROM HumanResources.Employee;

Derived column

Select distinct rows

DISTINCT keyword removes any duplicate row(s).

The query results in 290 rows

SELECT JobTitle FROM HumanResources.Employee;

The query results in 67 rows

SELECT DISTINCT JobTitle FROM HumanResources.Employee;

Limit the rows with the WHERE clause

  1. Equal to = operator
--List all the employees with a specific job title
SELECT * FROM HumanResources.Employee
WHERE JobTitle='Application Specialist';

single-jobTitle

  1. LIKE

The LIKE clause retrieves only the rows that match the search pattern (regular expression).

--Search with a regular expression - 'Assistant' anywhere in the text
SELECT DISTINCT JobTitle FROM HumanResources.Employee
WHERE JobTitle LIKE '%Assistant%';

regex1

--Job Title that begins with 'Assistant' followed by any text
SELECT DISTINCT JobTitle FROM HumanResources.Employee
WHERE JobTitle LIKE 'Assistant%';

regex2

--Job Title that ends with 'Assistant'
SELECT DISTINCT JobTitle FROM HumanResources.Employee
WHERE JobTitle LIKE '%Assistant';

regex3

--Job Title that begins with 'Human' and ends with 'Assistant'
SELECT DISTINCT JobTitle FROM HumanResources.Employee
WHERE JobTitle LIKE 'Human%Assistant';

regex4

  1. NOT LIKE
-- Retrieve all the roles, where 'Job Title' is not like the given string.
SELECT DISTINCT JobTitle FROM HumanResources.Employee
WHERE JobTitle NOT LIKE '%Supervisor%';
  1. LOGICAL AND
--Select the rows where both the `Where` conditions are true
SELECT * FROM HumanResources.Employee
WHERE OrganizationLevel=2 AND JobTitle LIKE '%Assistant%';

logical-and

  1. LOGICAL OR
--Show the rows where any one of the conditions is true
SELECT * FROM HumanResources.Employee
WHERE OrganizationLevel=2 OR JobTitle LIKE '%Assistant%';

Logical-or

  1. IN
SELECT * FROM HumanResources.Employee
WHERE OrganizationLevel IN (1,3);

in-condition

  1. BETWEEN
--Rows with OrganizationLevel that include 2,3, AND 4.
SELECT DISTINCT OrganizationLevel FROM HumanResources.Employee
WHERE OrganizationLevel BETWEEN 2 and 4;

between-range

  1. NOT
--Rows with OrganizationLevel that do not include 2,3, AND 4.
SELECT DISTINCT OrganizationLevel FROM HumanResources.Employee
WHERE OrganizationLevel NOT BETWEEN 2 and 4;

not-between

Group BY

The GROUP BY clause is used to categorize the result set based on a single or multiple columns.

  • The clause can be applied only on the column(s) that are listed in the SELECT query
  • All non-aggregate columns listed in the SELECT query must be included in the GROUP BY clause. Or, the column on which you apply the GROUP BY clause must be a non-aggregate column.
--Get the count of employees in each department.
SELECT COUNT(NationalIDNumber) AS 'Count of employees', JobTitle
FROM HumanResources.Employee
GROUP BY JobTitle;

group-by

HAVING

Is used along with the GROUP BY clause to limit the result set obtained by the Group by clause. It specifies a search condition on the Groups.

--List the department that has more than 25 employees
SELECT COUNT(NationalIDNumber) AS 'Count of employees', JobTitle
FROM HumanResources.Employee
GROUP BY JobTitle
HAVING COUNT(NationalIDNumber) > 20;

having

ORDER BY

The result set can be sorted by any column's value in ascending (ASC) or descending (DESC) order. By default, the rows are sorted in ascending order. To change the order in descending order run the following statement:

--Rows ordered in descending order of BusinessEntityID
SELECT * FROM HumanResources.Employee
ORDER BY BusinessEntityID DESC;

order-by

SELECT INTO

Use the SELECT INTO statement to create another (backup) table from an existing table.

USE AdventureWorks2019;
GO
SELECT *
INTO PRODUCTION.PRODUCT_BACKUP
FROM PRODUCTION.PRODUCT;
Did you find this page helpful?
Start with GraphQL on Hasura for Free
  • ArrowBuild apps and APIs 10x faster
  • ArrowBuilt-in authorization and caching
  • Arrow8x more performant than hand-rolled APIs
Promo
footer illustration
Brand logo
© 2021 Hasura Inc. All rights reserved
Github
Titter
Discord
Facebook
Instagram
Youtube
Linkedin