04-08-2023

DQL SELECT

QUERY
DDL CREATE, ALTER, DROP

MS SQL DML INSERT, UPDATE, DELETE

DCL GRANT, REVOKE

DATA TYPES

1
 04-08-2023

DATABASE

Database
CREATE DATABSE <DATA NAME>
Use <Data Names> DDL WITHOUT
DROP DATABSE <DATA NAME>
CONSTRAINTS

2
 04-08-2023

Create Table TRUNCATE TRUNCATE TABLE <TABLE NAME>

Without & DROP DROP TABLE<TABLE NAME>
Constraints Table

DDL WITH Create Table

With

CONSTRAINTS Constraints

3
 04-08-2023

Create Table
With
Constraints NOT NULL

Note: Not Null comes under domain specification of

column, so it cannot be put as constraint or key
in metadata.

UNIQUE Check
Constraint

4
 04-08-2023

create table fordefault

(
name varchar(10),

Default
roll int,
submission char default 'N'
)
INSERT
insert into fordefault (name, roll) values
COMMAND
('abc', 10)
select * from fordefault

INSERT DDL COMMANDS –

Statement
RENAME, ALTER,
DROP, UPDATE,
Note: BE CAREFUL WITH DEFAULT CONSTRAINT

5
 04-08-2023

sp_rename 'fr2.emp', 'fr2.empnew', 'column’; DDL alter table ABC

(Rename Column) add marks decimal(10,2)
DDL select *from fr2 commands:
command: sp_rename 'fr2', 'fr2s’; Add and
RENAME (Rename Table)
Drop
alter table One
drop column roll
Column

DDL
DDL alter table One alter table ABC
add constraint pk_roll primary key (roll); commands: alter column roll int
commands: -- only if its not null
Alter
Add and
Column (for ALTER TABLE fr2
Drop alter table One
alter column emp int not null
drop constraint PK1_roll domain,
Constraint
not null)

6
 04-08-2023

UPDATE
(RECORD)
FOREIGN KEY
Statement CONSTRAINT

alter table ABC

alter column roll int

create table Two

ALTER TABLE fr2
DDL ( alter column emp int not null
cid int primary key, DDL
commands: sroll int foreign key references
commands
One(sroll), sp_rename 'fr2.emp', 'fr2.empnew', 'column';
Foreign Key cname varchar(10)
select *from fr2

sp_rename 'fr2', 'fr2s';

)

7
 04-08-2023

1.ALTER TABLE Investors

alter table ABC
add marks decimal(10,2) 2.DROP COLUMN InvestorStartDate;

alter table fordefault

add constraint pk_roll primary key (roll); -- only 3.ALTER TABLE Investors
if its not null 4.DROP CONSTRAINT
DDL DDL UK_Investors_InvestorId_InvestorName;
commands alter table fr2
add constraint fk_fr2 foreign key (emp) references
commands
fordefault (roll); 5.DELETE FROM DUMMY WHERE c1 = 5;
6.DELETE FROM DUMMY or truncate table DUMMY;
ALTER TABLE fr2 7.DROP TABLE DUMMY;
ADD DEFAULT 'abc' FOR fname;

 Order of Execution
 From Select

1.SELECT *

DML COMMANDS
2.FROM Person.Password; --<Table Name>
DML :
Select 3.SELECT BusinessEntityID, FirstName, MiddleName, LastName --
Command <Col Name(s)>
4.FROM Person.Person; --<Table Name>;

5.SELECT 'FirstName’ -- Literal Select statement

6.FROM Person.Person;

8
 04-08-2023

1.SELECT JobTitle
2.FROM HumanResources.Employee;
1.SELECT NationalIDNumber, HireDate, VacationHours
3.SELECT DISTINCT JobTitle 2.FROM HumanResources.Employee;
4.FROM HumanResources.Employee;
Select with CASE : (when 3.SELECT NationalIDNumber, HireDate, VacationHours,

Distinct then else end) 4.CASE

1.SELECT DISTINCT Title, Suffix 5.WHEN VacationHours > 70 THEN 'Vacation hours over limit'
2.FROM Person.Person; 6.ELSE 'Vacation Hours within limit'
7.END AS VacationHourLimit
Note: Distinct does not ignore Null Values 8.FROM HumanResources.Employee;

SELECT NationalIDNumber, HireDate, VacationHours,

CASE : (when
CASE
WHEN VacationHours > 70 THEN 'Vacation hours over limit' DML COMMANDS
with WHERE
WHEN VacationHours BETWEEN 48 AND 61 THEN 'Vacation
then else end ) hours average'
ELSE 'Vacation Hours within limit'
END AS VacationHourLimit
FROM HumanResources.Employee;

9
 04-08-2023

 To filter the records on the condition/Query/Predicate

 Used with Select, Update and delete (Record)

Comparison
WHERE
1. SELECT BusinessEntityID, NationalIDNumber, BirthDate, Operators in
Condition MaritalStatus <col name(s)>
‘WHERE’
2. FROM HumanResources.Employee <table name>
3. WHERE MaritalStatus = 'S’; <condition>

Order of Execution
From Where Select

 SELECT *
1.SELECT StateProvinceID, CountryRegionCode  FROM Production.Product;
2.FROM Person. StateProvince
3.WHERE CountryRegionCode <> 'US’;  SELECT StandardCost, ListPrice, StandardCost +
Where with Where with ListPrice AS SumListPriceCost
 FROM Production.Product
Comparison  To compare two columns Arithmetic  WHERE SumListPriceCost > 0;
1.SELECT StateProvinceCode, CountryRegionCode
Operators 2.FROM Person. StateProvince
Operators
 SELECT StandardCost, ListPrice, StandardCost +
3.WHERE StateProvinceCode <> CountryRegionCode; ListPrice AS SumListPriceCost
 FROM Production.Product
 WHERE StandardCost + ListPrice > 0;

10
 04-08-2023

1.SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel

2.FROM Production.Product

3.WHERE ProductID < 600 AND StandardCost > 50 AND

SafetyStockLevel BETWEEN 500 AND 1200;

Logical Where with

Operators in Logical 4.SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel

Where Operators 5.FROM Production.Product

6.WHERE ProductID = 1 OR ProductID = 10 OR ProductID = 15 OR

ProductID = 20;

-- Better to use IN operator here

 AND is processes First than OR If no Brackets

1.SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel 1.SELECT ProductId, Name, Color
2.FROM Production.Product 2.FROM Production.Product

Where with 3.WHERE (ProductID = 800 OR ProductID < 600) AND StandardCost > 3.WHERE Color IS NULL;
50 AND SafetyStockLevel BETWEEN 500 AND 1200; IS NULL/
Logical
IS NOT NULL
Operators 4.SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel 4.SELECT ProductId, Name, Color
5.FROM Production.Product
5.FROM Production.Product
6.WHERE Color IS NOT NULL;
6.WHERE NOT ProductID = 4;

11
 04-08-2023

Start and End values are included in Between

1.SELECT PurchaseOrderId, ModifiedDate

2.FROM Purchasing.PurchaseOrderDetail
BETWEEN/ 3.WHERE ModifiedDate BETWEEN '2014-02-03'AND '2015-08-13’; 1.SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel

NOT IN OPERATOR 2.FROM Production.Product

3.WHERE ProductID IN (1,10,15,20);
4.SELECT PurchaseOrderId, ModifiedDate
BETWEEN 5.FROM Purchasing.PurchaseOrderDetail
6.WHERE ModifiedDate NOT BETWEEN '2014-02-03'AND '2015-08-
12'; 1.SELECT *
2.FROM Person.StateProvince
3.WHERE StateProvinceCode NOT IN ('AK','AZ','CO','ID','IA');

1.SELECT BusinessEntityId, JobTitle

2.FROM HumanResources.Employee
3.WHERE JobTitle LIKE ‘R%’; 1.SELECT *
2.FROM Person.StateProvince
3.WHERE StateProvinceCode LIKE 'A[BKL]’;
% Match zero or more characters
4.SELECT *
Pattern Match 1.SELECT * Pattern Match 5.FROM Production.Product
2.FROM Person.StateProvince 6.WHERE ProductNumber LIKE 'L[A-Z]-[0-9]%’
3.WHERE StateProvinceCode LIKE 'A[BKL]_’;
7.SELECT *
_ Single Character Match 8.FROM Production.Product
9.WHERE ProductNumber LIKE 'L[^I-N]-[135]%’;

12
 04-08-2023

BY DEFAULT ASC IS THE ORDER; Null are considered to be lowest; not necessary
that column on which order by clause is applied is there in select statement too.

 FROM  WHERE SELECTORDER BY

DML COMMANDS SORTING

1.SELECT AddressID, CONCAT(AddressLine1, ' ', AddressLine2, ' ',
2.City, ' ', StateProvinceID, ' ', PostalCode) AS PostalAddress

with ORDER BY
3.FROM Person.Address
DESC/ASC 4.ORDER BY AddressID;

1.SELECT ProductID, Name, StandardCost, Listprice

2.FROM Production.Product
3.WHERE StandardCost > 0 AND ListPrice > 0
4.ORDER BY StandardCost DESC, ListPrice DESC;

OFFSET IS MANDATORY AND FETCH IS OPTIONAL

1.SELECT BusinessEntityID, NationalIDNumber, HireDate

2.FROM HumanResources.Employee
3.ORDER BY HireDate ASC
1.SELECT AddressID, AddressLine1, LEN(AddressLine1) AS
LengthAddL1 SORTING 4.OFFSET 5 ROWS -- Skip first 5 Rows
SORTING 2.FROM Person.Address FETCH &
DESC/ASC 3.ORDER BY LEN(AddressLine1) ASC; 5.SELECT BusinessEntityID, NationalIDNumber, HireDate
OFFSET 6.FROM HumanResources.Employee
7.ORDER BY HireDate ASC
8.OFFSET 5 ROWS
9.FETCH NEXT 20 ROWS ONLY;

13
 04-08-2023

1.SELECT BusinessEntityID, NationalIDNumber, HireDate

2.FROM HumanResources.Employee
3.ORDER BY HireDate DESC
4.OFFSET 0 ROWS  SELECT TOP 3 SalesOrderID, SalesOrderDetailID, OrderQty
5.FETCH NEXT 5 ROWS ONLY;  FROM Sales.SalesOrderDetail
SORTING SORTING  ORDER BY OrderQty DESC;
6.SELECT TOP 5 BusinessEntityID, NationalIDNumber, HireDate
FETCH & FETCH &

7.FROM HumanResources.Employee  SELECT TOP 3 WITH TIES SalesOrderID,
8.ORDER BY HireDate DESC;
OFFSET OFFSET SalesOrderDetailID, OrderQty
 FROM Sales.SalesOrderDetail
9.SELECT TOP 10 PERCENT SalesOrderID, SalesOrderDetailID, OrderQty  ORDER BY OrderQty DESC;
10.FROM Sales.SalesOrderDetail
11.ORDER BY OrderQty DESC;

FROM  WHERE GROUP BY SELECTORDER BY

MOSTLY USED WITH AGGREGATE FUNCTIONS

DML COMMANDS GROUP BY

1.SELECT SalesOrderID, ORderQty
2.FROM Sales.SalesOrderDetail

with GROUP BY 3.SELECT SalesOrderID, sum(ORderQty)

4.FROM Sales.SalesOrderDetail
5.GROUP BY SalesOrderID;

14
 04-08-2023

Unless we use Aggregate function select col name should be same as

Group By Clause
1.SELECT *
2.FROM HumanResources.EmployeePayHistory;
1.SELECT *
2.FROM HumanResources.Department;
3.SELECT PayFrequency, SUM(Rate)

1.SELECT GroupName 4.FROM HumanResources.EmployeePayHistory;

GROUP BY 2.FROM HumanResources.Department
GROUP BY
3.GROUP BY GroupName; 5.SELECT PayFrequency, SUM(Rate) AS TotalRatePerPayFrequency
6.FROM HumanResources.EmployeePayHistory
1.SELECT GroupName, COUNT(GroupName) 7.GROUP BY PayFrequency;
2.FROM HumanResources.Department
3.GROUP BY GroupName;

1.SELECT PayFrequency, SUM(Rate) AS TotalRatePerPayFrequency

2.FROM HumanResources.EmployeePayHistory 1.SELECT ProductID, Shelf, Quantity
3.GROUP BY PayFrequency 2.FROM Production.ProductInventory
4.ORDER BY PayFrequency;
GROUP BY GROUP BY 3.SELECT ProductID, Shelf, SUM(Quantity)
4.FROM Production.ProductInventory
5.SELECT PayFrequency, SUM(Rate) AS TotalRatePerPayFrequency 5.GROUP BY ProductID, Shelf;
6.FROM HumanResources.EmployeePayHistory
7.GROUP BY PayFrequency
8.ORDER BY PayFrequency DESC;

15
 04-08-2023

1.SELECT max(StandardCost)
2.FROM Production.Product
3.HAVING max(standardCost) > 200; 1.SELECT DueDate,Sum(OrderQty) AS TotalOrderPerDueDate
2.FROM Purchasing.PurchaseOrderDetail
GROUP BY 1.SELECT DueDate, Sum(OrderQty) AS TotalOrderPerDueDate GROUP BY 3.WHERE YEAR(DueDate) > 2011 AND Month(DueDate) < 9
with HAVING 2.FROM Purchasing.PurchaseOrderDetail with HAVING 4.GROUP BY DueDate
3.WHERE YEAR(DueDate) > 2011 AND Month(DueDate) < 9 5.HAVING sum(OrderQty) < 6000
4.GROUP BY DueDate 6.ORDER BY DueDate DESC;
5.ORDER BY DueDate DESC;

1.SELECT PurchaseOrderId
2.FROM Purchasing.PurchaseOrderDetail
3.UNION

DML COMMANDS
4.SELECT PurchaseOrderID
5.FROM Purchasing.PurchaseOrderHeader;

with SET UNION 1.SELECT PurchaseOrderDetailID

2.FROM Purchasing.PurchaseOrderDetail

OPERATIONS 3.UNION
4.SELECT TaxAmt
5.FROM Purchasing.PurchaseOrderHeader
6.ORDER BY PurchaseOrderDetailID ASC;

16
 04-08-2023

1.SELECT PurchaseOrderId
2.FROM Purchasing.PurchaseOrderDetail
3.UNION
4.SELECT PurchaseOrderID
5.FROM Purchasing.PurchaseOrderHeader; 1.SELECT ProductSubCategoryID
2.FROM Production.Product
UNION 1.SELECT PurchaseOrderDetailID INTERSECT 3.INTERSECT

2.FROM Purchasing.PurchaseOrderDetail 4.SELECT ProductSubCategoryID

3.UNION 5.FROM Production.ProductSubcategory;
4.SELECT TaxAmt
5.FROM Purchasing.PurchaseOrderHeader
6.ORDER BY PurchaseOrderDetailID ASC;

DML COMMANDS
1.SELECT SalesOrderID
2.FROM Sales.SalesOrderDetail
EXCEPT 3.EXCEPT
4.SELECT CustomerID
5.FROM Sales.Customer;
JOINS

17
 04-08-2023

1.SELECT pod.PurchaseOrderID,
1.SELECT pod.PurchaseOrderID,
2.pod.PurchaseOrderDetailID,
2.pod.PurchaseOrderDetailID,
3.pod.OrderQty,
3.pod.OrderQty,
4.poh.OrderDate,
JOIN/ INNER
IMPLICIT JOIN 4.poh.OrderDate,
5.poh.ShipDate
5.poh.ShipDate JOIN 6.FROM Purchasing.PurchaseOrderHeader poh
6.FROM Purchasing.PurchaseOrderDetail pod,
7.INNER JOIN
Purchasing.PurchaseOrderHeader poh
8.Purchasing.PurchaseOrderDetail pod
7.WHERE pod.PurchaseOrderID = poh.PurchaseOrderID;
9.ON pod.PurchaseOrderID = poh.PurchaseOrderID

1.SELECT pod.PurchaseOrderID,
1.SELECT p.ProductID, p.Name, pc.ProductCategoryID, pc.Name,
2.pod.PurchaseOrderDetailID,
2.psc.ProductSubCategoryID
3.pod.OrderQty,
4.poh.OrderDate, INNER JOIN 3.FROM Production.Product P
4.INNER JOIN
JOIN/ INNER 5.poh.ShipDate ON MORE 5.Production.ProductSubcategory psc
JOIN + WHERE 6.FROM Purchasing.PurchaseOrderHeader poh THAN TWO 6.ON psc.ProductSubCategoryID = p.ProductSubCategoryID
7.INNER JOIN
TABLES 7.INNER JOIN
8.Purchasing.PurchaseOrderDetail pod
8.Production.ProductCategory pc
9.ON pod.PurchaseOrderID = poh.PurchaseOrderID
9.ON pc.ProductCategoryID = psc.ProductCategoryID;
10.WHERE year(poh.OrderDate) = 2014;

18
 04-08-2023

1.SELECT count(*)
2.FROM HumanResources.Employee;

1.SELECT count(*)

CROSS JOIN
VIEWS
2.FROM HumanResources.Department;

1.SELECT * FROM HumanResources.Employee, HumanResources.Department;

1.SELECT * FROM HumanResources.Employee CROSS JOIN

HumanResources.Department;

 SQL provides a mechanism for the nesting of subqueries. A subquery

1.CREATE VIEW vwMissingItemDetails AS
is a select-from-where expression that is nested within another query.
2.SELECT iss.itemId, iss.ItemQty, pd.ProductName, pd.ProductCode,
pd.ProductListPrice  The nesting can be done in the following SQL query

3.FROM retail.ItemStatus iss select A1, A2, ..., An

from r1, r2, ..., rm
Create View 4.INNER JOIN
SUB- Queries where P
5.retail.ProductDetails pd
6.ON iss.itemId = pd.ProductId as follows:
 Ai can be replaced be a subquery that generates a single value.
7.WHERE iss.ItemStatus IS NULL;
 ri can be replaced by any valid subquery
 P can be replaced with an expression of subquery

19
 04-08-2023

Those records Where Business Entity Id From

Those records Where Business Entity Id From
HumanResources.Employee Table must also be present in
HumanResources.Employee Table must also be present in HumanResources.EmployeeDepartmentHistory. Employee Table
HumanResources.EmployeeDepartmentHistory. Employee Table sort by Job Title

Sub Query in 1.SELECT NationalIDNumber, JobTitle, HireDate Sub Query in 1.SELECT BusinessEntityID,NationalIDNumber, JobTitle, HireDate

Where Clause 2.FROM HumanResources.Employee

Where Clause 2.FROM HumanResources.Employee
3.WHERE BusinessEntityID IN
3.WHERE BusinessEntityID IN
4.(SELECT BusinessEntityID 4.(SELECT BusinessEntityID
5.FROM HumanResources.EmployeeDepartmentHistory 5.FROM HumanResources.EmployeeDepartmentHistory

6.) 6.)
7.ORDER BY JobTitle;

Those records Where Business Entity Id From HumanResources.Employee

Table must also be present in
HumanResources.EmployeeDepartmentHistory. Employee Table sort by To Find Second Highest Value
Job Title and filter records in such a way that BusinessEntityID <= 100 in
HumanResources.EmployeeDepartmentHistory
1.SELECT Min(UnitPrice)
Sub Query in 2.FROM Purchasing.PurchaseOrderDetail
Sub Query in 1.SELECT BusinessEntityID,NationalIDNumber, JobTitle, HireDate
Where Clause 3.WHERE UnitPrice IN
2.FROM HumanResources.Employee
Where Clause 3.WHERE BusinessEntityID IN
– In the Same 4.(SELECT TOP 2 UnitPrice

4.(SELECT BusinessEntityID Table 5.FROM Purchasing.PurchaseOrderDetail

6.ORDER BY UnitPrice DESC
5.FROM HumanResources.EmployeeDepartmentHistory
7.);
6.WHERE BusinessEntityID <= 100
7.)
8.ORDER BY JobTitle;

20
 04-08-2023

To Find Third Highest Value (distinct) Find PurchaseOrderID, UnitPrice from PurchaseOrderDetail where
Unit Price is greater than Avg of List Price from Product Table

1.SELECT Min(UnitPrice)
Sub Query in 2.FROM Purchasing.PurchaseOrderDetail

Where Clause 3.WHERE UnitPrice IN

Sub Query in 1.SELECT PurchaseOrderID, UnitPrice
4.(SELECT TOP 3 UnitPrice 2.FROM Purchasing.PurchaseOrderDetail
– In the Same 5.FROM Purchasing.PurchaseOrderDetail
Where Clause 3.WHERE UnitPrice >
Table 6.GROUP BY UnitPrice 4.(SELECT Avg(ListPrice)
7.ORDER BY UnitPrice DESC 5.FROM Production.Product
8.); 6.);

Want to Check BusinessEntityId, JobTitle from Employee Table If

Department Id Exists in Department Table ; where Dept name is
Sales

Sub Query in The exists construct returns the value Sub Query in 1.SELECT BusinessEntityId, JobTitle
Where Clause true if the argument subquery is Where Clause 2.FROM HumanResources.Employee
– nonempty. – 3.WHERE EXISTS
Exists exists r  r  Ø Exists 4.(SELECT DepartmentID
5.FROM HumanResources.Department
6.WHERE Name = 'Sales'
7.);

21
 04-08-2023

Find out average of all the minimum of Unit Price

Sub Query as Column Expression-

1.SELECT PurchaseOrderID, min(UnitPrice) AS minUnitPrice

1.SELECT PurchaseOrderId, TaxAmt, 2.FROM Purchasing.PurchaseOrderDetail
2.(SELECT sum(OrderQty) 3.GROUP BY PurchaseOrderID

Subqueries in 3.FROM Purchasing.PurchaseOrderDetail pod

Subqueries in
4.WHERE pod.PurchaseOrderID = poh.PurchaseOrderID 4.SELECT avg(minUnitPrice)
the Select 5.GROUP BY pod.PurchaseOrderID
the From 5.FROM
Clause 6.) AS SumOrderQty
Clause 6.(SELECT PurchaseOrderID, min(UnitPrice) AS minUnitPrice
7.FROM Purchasing.PurchaseOrderDetail
7.FROM Purchasing.PurchaseOrderHeader poh
8.GROUP BY PurchaseOrderID
8.ORDER BY poh.PurchaseOrderID;
9.) AS avgMinUnitPricePerOrder

1. SELECT PurchaseOrderId, DueDate, UnitPrice

The ANY and ALL operators allow you to perform a 2. FROM Purchasing.PurchaseOrderDetail
comparison between a single column value and a range 3. WHERE DueDate =
of other values. 4. ANY (SELECT ORderDate
5. FROM Purchasing.PurchaseOrderHeader);

1.SELECT PurchaseOrderId, DueDate, UnitPrice

Any and ALL 2.FROM Purchasing.PurchaseOrderDetail
Any and ALL 1. SELECT PurchaseOrderId, DueDate, UnitPrice
2. FROM Purchasing.PurchaseOrderDetail
Operator 3.WHERE DueDate >= Operator
1. SELECT PurchaseOrderId, DueDate, UnitPrice
4.ANY (SELECT OrderDate
2. FROM Purchasing.PurchaseOrderDetail
5.FROM Purchasing.PurchaseOrderHeader); 3. WHERE DueDate = '2011-05-14 00:00:00.000';

1. SELECT PurchaseOrderId, OrderDate

 If due Date is greater than any of the order date then it returns
true Value 2. FROM Purchasing.PurchaseOrderHeader
3. WHERE OrderDate = '2011-05-14 00:00:00.000';

22
 04-08-2023

1.SELECT PurchaseOrderId, DueDate, UnitPrice

2.FROM Purchasing.PurchaseOrderDetail
3.WHERE DueDate <>
4.ALL (SELECT ORderDate
Any and ALL 5.FROM Purchasing.PurchaseOrderHeader);
Operator
1.SELECT PurchaseOrderId, DueDate, UnitPrice
2.FROM Purchasing.PurchaseOrderDetail
3.WHERE DueDate =
4.ANY (SELECT ORderDate
5.FROM Purchasing.PurchaseOrderHeader);

23