Selenium WebDriver Java Framework Course Limited Time Offer for $20

Selenium WebDriver Java Framework Course Limited Time Offer for $20

 

Print

Using Select to Retrieve rows and columns

Select statement is used to retrieve rows and columns from a table, view or joined tables. Using different arguments, select statement query will return different results.  

 

All these examples below uses AdventureWorks sample database provided by Microsoft

(1) Return all rows and columns from a table

This query below returns all rows and columns from Department table and Product table. 

USE AdventureWorks2008
Go
--return all rows from Department table
Select * from HumanResources.Department
--return all rows from Product table
Select * from Production.Product

(2) Return all rows and subset of the columns

 This query below returns all rows and subset columns from the Department and Product table

USE AdventureWorks2008
Go
--return all rows and subset of the columns (DepartmentID, Name, GroupName) from Department table
Select DepartmentID,Name,GroupName from HumanResources.Department
--return all rows and subset of the columns(ProductID, Name, ProductNumber) from Product table
Select ProductID,Name,ProductNumber from Production.Product

(3) Return rows with conditional statement

This query below returns only the rows from Department table where DepartmentID greater than 5 and returns only the rows from the Produc table where ProductID between 300 and 400

 
USE AdventureWorks2008
Go
--return all rows from the Department table where DepartmentID>=5
Select * from HumanResources.Department 
where DepartmentID>=5
--return all rows from the Product table where ProductID between 10 and 20
Select ProductID,Name,ProductNumber from Production.Product
where ProductID>300 and ProductID<=400
--alternative ways
Select ProductID,Name,ProductNumber from Production.Product
where ProductID between 300 and 400

(4) Return rows with column headings and calculations

 This query below returns all rows from Product table. For ListPrice, we add 20% and flag it as "Markup Price". ProductID, Name, and ListPrice are labeled as ID, Product Name and List Price. 

USE AdventureWorks2008;
GO
-- Add 20% to the ListPrice and flag it as Markup Price
select 
ProductID as ID, 
Name as 'Product Name', 
ListPrice as 'List Price',
ListPrice+ListPrice*0.20 as 'Markup Price'
 from Production.Product
 where ListPrice<>0

(5) Return DISTINCT rows with Select statement

 This query below returns only DISTINCT Group Name from Department Table  to prevent the retrieval of duplicate group name. 

USE AdventureWorks2008
Go
--returns all GroupName from the Department
select GroupName from HumanResources.Department

--return DISTINCT GroupName from the Department
select DISTINCT GroupName from HumanResources.Department

Result from all

GroupName
--------------------------------------------------
Research and Development
Research and Development
Sales and Marketing
Sales and Marketing
Inventory Management
Research and Development
Manufacturing
Manufacturing
Executive General and Administration
Executive General and Administration
Executive General and Administration
Quality Assurance
Quality Assurance
Executive General and Administration
Inventory Management
Executive General and Administration

(16 row(s) affected)

Result from DISTINCT

GroupName
--------------------------------------------------
Executive General and Administration
Inventory Management
Manufacturing
Quality Assurance
Research and Development
Sales and Marketing

(6 row(s) affected)

(6) Creating tables with SELECT INTO statement

 In some testing, we use temporary table to store records and then use it to validate data to avoid repeated access to the database. This query below creates a temporary Product table in the tempdb database. Generally, we put (#) sign for temp table. We retrieve only the rows from that Product table where ProductID between 300 and 350 and store it in temp table #Product. Temporary tables can be deleted by using drop statement. 

use tempdb
go

use AdventureWorks2008
go
--create a temporary table #Product
select * into #Product from Production.Product
where ProductID>300 and ProductID<=350
--retrieve data from the temporary table #Product
select * from #Product
--delete temporary table 
drop table #Product

(7)Using subqueries in Select Statement

This query below returns only the rows from Product table where ProductModelId is from the top 10 ProductModelID records from ProductModel table. 

use AdventureWorks2008
go
-- retrieve rows from Product where ProductModelID in
-- top 10 ProductModelID in ProductModel table
select * from Production.Product
where ProductModelID in(
select top 10 ProductModelID from Production.ProductModel)

(8) Using GROUP BY in Select Statement

 This query below finds the total number of employees who have the Job Title "Janitor" in Employee table. 

use AdventureWorks2008
go

select JobTitle,COUNT(*) as 'Number of Employees' 
from HumanResources.Employee
where JobTitle like 'Janitor'
group by JobTitle

Query Result

JobTitle                                           Number of Employees
-------------------------------------------------- -------------------
Janitor                                            4

(1 row(s) affected)

(9)Using GROUP BY with multiple groups

 This query below returns the average unit price discount and the sum of year-to-date sales, grouped by ProductID and Special Offer ID. 

Use AdventureWorks2008
go

SELECT ProductID, SpecialOfferID, 
AVG(UnitPriceDiscount) AS 'Average Unit Price Discount', 
    SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID, SpecialOfferID
ORDER BY ProductID
GO

(10) Using GROUP BY and WHERE

 This query below finds the total number of employees who have the Job Title "Manager" in Employee table. 

use AdventureWorks2008
go

select JobTitle,COUNT(*) as 'Number of Employees' 
from HumanResources.Employee
where JobTitle like '%Manager%'
group by JobTitle

Query Result

JobTitle                                           Number of Employees
-------------------------------------------------- -------------------
Accounts Manager                                   1
Document Control Manager                           1
Engineering Manager                                1
European Sales Manager                             1
Facilities Manager                                 1
Finance Manager                                    1
Human Resources Manager                            1
Information Services Manager                       1
Marketing Manager                                  1
Network Manager                                    1
North American Sales Manager                       1
Pacific Sales Manager                              1
Production Control Manager                         1
Purchasing Manager                                 1
Quality Assurance Manager                          1
Research and Development Manager                   2

(16 row(s) affected)

(11) Using GROUP BY with expression

 With expression, we can do some calculation to group data by evaluated results. This query below returns number of employees by ages. 

Use AdventureWorks2008
go

select DATEDIFF(YEAR,BirthDate,GETDATE()) as Age,
COUNT(*) as 'Number of Employees' from HumanResources.Employee
group by DATEDIFF(YEAR,BirthDate,GETDATE())

(12) Using GROUP BY with order by

 This query below returns number of employees by ages and order by age descending.

Use AdventureWorks2008
go

select DATEDIFF(YEAR,BirthDate,GETDATE()) as Age,
COUNT(*) as 'Number of Employees' from HumanResources.Employee
group by DATEDIFF(YEAR,BirthDate,GETDATE())
order by DATEDIFF(YEAR,BirthDate,GETDATE()) desc

(13) Using the HAVING Clause

 Having clause is used to filter the result of an aggregate function. This query below filters employees who is over 50 years old. 

Use AdventureWorks2008
go

select DATEDIFF(YEAR,BirthDate,GETDATE()) as Age,
COUNT(*) as 'Number of Employees' from HumanResources.Employee
group by DATEDIFF(YEAR,BirthDate,GETDATE())
having DATEDIFF(YEAR,BirthDate,GETDATE())>=50
order by DATEDIFF(YEAR,BirthDate,GETDATE()) desc

(14) Using HAVING and GROUP BY

 Having clause is used to filter the result of an aggregate function. This query below filters employees who is over 50 years old. 

Use AdventureWorks2008
go

select DATEDIFF(YEAR,BirthDate,GETDATE()) as Age,
COUNT(*) as 'Number of Employees' from HumanResources.Employee
group by DATEDIFF(YEAR,BirthDate,GETDATE())
having DATEDIFF(YEAR,BirthDate,GETDATE())>=50
order by DATEDIFF(YEAR,BirthDate,GETDATE()) desc

(15) Using HAVING with SUM and AVG

 This query below groups the SalesOrderDetail table by ProductID and returns only those group of products that have average order quantities are more than 5 and order totaling more than $50000.00

USE AdventureWorks2008;
GO
SELECT ProductID, AVG(OrderQty) AS AverageQuantity, 
SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $50000.00
AND AVG(OrderQty) >5;
GO

Query Result

ProductID   AverageQuantity Total
----------- --------------- ---------------------------------------
863         9               69943.214246
864         6               156398.067950

(2 row(s) affected)

(16) Calculating group totals by using COMPUTE BY

COMPUTE BY is used to evaluate aggregation for each group. This query below calculates the sum of orders, for products prices less than $3.00 and greater than $2.00, for each type of product. 

USE AdventureWorks2008;
GO
SELECT ProductID, UnitPrice,LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice > $2.00 and UnitPrice<$3.0
ORDER BY ProductID,UnitPrice, LineTotal
COMPUTE SUM(LineTotal) BY ProductID;
GO

Query Result