Show Menu
Cheatography
  1. Home >
  2. Programming >

SQL Server Recipies Cheat Sheet (DRAFT) by

SQL Server Recipies - for update, delete, join etc

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Select Statement

Select Statements
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

Select Statement

Aggregate Functions
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;

Select Statement

other tips
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

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

Date and time

 
Date and time
select GETDATE();

extract Date
Select FORMAT­(GE­TDA­TE(­),'­dd/­MM/­yyyy')

extract time
select FORMAT­(GE­TDA­TE(­),'­hh:­mm:ss')

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

set Database to be used

USE
use Advent­ure­Wor­ks2019

Joins

Inner Joins - (inter­secting data)
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;

Joins

Left Joins
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;

Joins

Left Outer Join
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

Having Clause

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

Acending / Decending

 
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

Update Multiple Rows
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 ='BKS';

Update

 
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 ='BKS';
select * from sales.S­S_­Cur­rency
Where Curren­cyCode = 'SHD'
or curren­cyCode ='BKS';
  

Change Tables

COPY TABLE
Select * into new_table from old_table
Select * into sales.S­S_­Cur­rency from sales.C­ur­rency

DROP - delete contents of table
drop table sales.S­S_­sal­esp­ers­on_Own

Tables
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))

Changing Data

Update / Delete / Insert
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 ('SSD'­,'S­hil­paD­ollar', GETDATE())

Insert

Another Example of - Insert Into
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),
( 'KPBS', GETDATE(), 'likes chips', 0, 5943.24),
( 'SS', GETDATE(), 'likes to play Cello', 0, 1000000)