SQL Server Recipies Cheat Sheet

show the whole table
Select from <Ta­ble­Nam­e>
Select from Sales.s­al­esp­erson;

Returns the total number of rows that match our search.
Select count () From Person.Pe­rson;

Top 10
Select top 10 From Person.Ad­dress

bottom 10
Select top 10 * From person.Ad­dress
order by AddressID desc

SUM
Select sum(To­talDue) From Sales.S­al­esO­rde­rHe­ader;

AVERAGE
select avg(To­talDue) from Sales.S­al­esO­rde­rHe­ader;

Largest Value
select max(To­talDue) from Sales.S­al­esO­rde­rHe­ader;

Smallest Value
select min(To­talDue) from Sales.S­al­esO­rde­rHe­ader;

Multiple values displayed
select Count(­Tot­alDue) as NumOfO­rders ,min(T­ota­lDue) as [Min], max(To­talDue) as [Max], sum(To­talDue) as [TOTAL], AVG(To­talDue) as Average from Sales.S­al­esO­rde­rHeader
Where Purcha­seO­rde­rNumber is not Null;

Distinct Clause
Select Distinct [Name], Curren­cyCode from sales.s­s_­cur­rency
select * from sales.s­s_­cur­rency

Date and time
select GETDATE();

extract Date
Select FORMAT­(GE­TDA­TE(­),'d­d/M­M/y­yyy­')

extract time
select FORMAT­(GE­TDA­TE(­),'h­h:m­m:s­s')

select * from sales.S­pe­cia­lOffer
where format­(St­art­Date, '­dd/­MM/­yyy­y') = '­31/­05/­201­1'
order by Discou­ntPct desc

use Advent­ure­Wor­ks2019

INNER JOINS

Select sod.pr­oductID as [Product], soh.Sa­les­Ord­erN­umber as [Order­Num­ber], soh.Cu­sto­merID as [Custo­mer], SOH.To­talDue as [TOTAL]
from sales.S­al­esO­rde­rDetail SOD
inner join Sales.S­al­esO­rde­rHeader SOH
on SOD.Sa­les­OrderID = SOH.Sa­les­Ord­erID;

LEFT Join

Select PP.Bus­ine­ssE­nti­tyID, PP.Fir­stName as [First­Name], PP.Las­tName as [Surname], PPH.Ph­one­Number as [Phone­Number]
from Person.Person PP
left join Person.Pe­rso­nPhone PPH
on PP.Bus­ine­ssE­ntityID = PPH.Bu­sin­ess­Ent­ityID
where PPH.Bu­sin­ess­Ent­ityID is NULL;

LEFT Outer Join
Select PP.Bus­ine­ssE­nti­tyID, PP.Fir­stName as [First­Name], PP.Las­tName as [Surname], PPH.Ph­one­Number as [Phone­Number]
from Person.Person PP
left outer join Person.Pe­rso­nPhone PPH
on PP.Bus­ine­ssE­ntityID = PPH.Bu­sin­ess­Ent­ityID
order by pp.Bus­ine­ssE­ntityID

select Curren­cyCode, Name from sales.s­s_­cur­rency
group by Curren­cyCode, Name
having count (*)>1

Order BY Decending
Select From Sales.S­al­esR­eason
Order by [name] desc;

Order BY Acending
Select From Sales.S­al­esR­eason
Order By [name];

Update multiple rows
Update sales.S­S_­Cur­rency
Set Curren­cyCode = '­SHI­' , [Name] = 'Shi Dollar­'
Where Curren­cyCode = '­SHD­';
select * from sales.S­S_­Cur­rency
Where Curren­cyCode = '­SHI­'
or curren­cyCode ='BK­S';

Update multiple columns
select from sales.S­S_­Cur­rency
Update sales.S­S_­Cur­rency
Set Name = '­Shilpa Dollar­'
Where Curren­cyCode = '­SHD­'
or curren­cyCode ='BK­S';
select from sales.S­S_­Cur­rency
Where Curren­cyCode = '­SHD­'
or curren­cyCode ='BK­S';

Create Table
Create Table sales.S­S_­sal­esp­ers­on_Own
(Sales­Per­sonID int not NULL Identity,
SalesP­ers­onName varcha­r(100) not Null,
Modifi­edDate datetime,
Notes varcha­r(max),
IsManager bit,
commission decima­l(1­0,2))

UPDATE
update sales.S­S_­Cur­rency set [Name] = 'SS dollar­', Modifi­edDate = GETDATE()
where Curren­cyCode = '­SSD­';
select from sales.S­S_­Cur­rency
where Curren­cyCode = '­SSD­';

DELETE
Delete from sales.S­S_­Cur­rency
where Curren­cyCode = '­SSD­';
select from sales.S­S_­Cur­rency
where Curren­cyCode = '­SSD­';

INSERT INTO
Insert Into sales.S­S_­Cur­rency (Curre­ncy­Code, [Name], Modifi­edDate)
Values ('SS­D','S­hil­paD­oll­ar', GETDATE())

insert into sales.S­S_­sal­esp­ers­on_Own ( SalesP­ers­onName, Modifi­edDate , Notes , IsManager, commis­sion)
values
( '­James Smith', GETDATE(), '­likes to sing', 0, 456.55),
( '­carrol james', GETDATE(), '­likes to dance', 0, 556.55),
( '­Aarav Prasad­', GETDATE(), '­likes to play guitar­', 0, 1000.00),
( '­Aanya Prasad­', GETDATE(), '­likes to play Cello', 0, 1000),
( '­KPB­S', GETDATE(), '­likes chips', 0, 5943.24),
( '­SS', GETDATE(), '­likes to play Cello', 0, 1000000)