lOMoARcPSD|64140312
SQL NOTE - Comprehensive SQL lecture notes
SQL NOTE - Comprehensive SQL lecture notes
Database systems implementation (University of Cambridge)
Database systems implementation (University of Cambridge)
messages.pdf_cover_qr_code_label
messages.pdf_cover_qr_code_label
messages.studocu_not_sponsored_or_endorsed_by_college
messages.studocu_not_sponsored_or_endorsed_by_college
messages.downloaded_by
lOMoARcPSD|64140312
SQL
" whot is sQL 2
SQL (Structured Queny Language) is a programming
language desiged for managing dota in relati onal
database. SqL has a Variety o 4anctions that
allow ds useng to read, manipulate, and chang e
data. Though sqL is commonly used by engineers
in seft ware devolopment, its also popula uilh
data analysts for afeaw reason :
" H% semantiolly eagy to understand md let.
Becauge it Can be used to access lange
amounts of data dineetly uhene tts slored,
aralysts don't have to copy data into olhen
aplicat'ons.
Compared to speread shet tools, ata
analysis done in SQL is easy to audit ond
Peplicate. or omaly etg,this means
looking for the aell euith he typo in the Brmula.
meanS no more
" SELECT* Example.
Ihe lolouing sqL statement seleete all !he
Column.s from the alestable:
EXample - SELECT * FROM Sales;
messages.downloaded_by
lOMoARcPSD|64140312
" select columhs uise
Example -
SELECI year,
month
west.
FROM Sales
Rename Qolumns
Example
SELECT cuest AS "west Region"
FRoM Sales
" LIMIT Clause
Ihe LIMIT elause is used to spect'y the number
o records to petun
Example- SELECT*
FROM Sales
LIMIT 100
WHERE Clause
The wHERE clause is used to ken reconds.
If is uged to extraet only those reconds lhat
fulf:ll a specifted eonattion.
Example- SELECT *
FROM Sales
WHERE Country
messages.downloaded_by
" Canoda;
lOMoARcPSD|64140312
" Comppison Operatong on numepical dota
The most basie any toftlter data te uting
Companison. operators. lhe easiect eway to
bndenstond them is lo stant by loking at a ltst
of them:
Equal to
Not equal to
Ghreater than
Less than
Greaten thon or equal to
Less than on equal tO to
Exemple- . SELECT *
FROM Sales
WHERE City = kolkata";
SELECT *
FROM Sales
WHERE city != "kolkata" ;
SELECT *
FROM Saleg
WHERE Month >"Jonuany";
SELECT *
FROM Soles
WHERE Saleomount < 5 0000
messages.downloaded_by
lOMoARcPSD|64140312
Jou can. perlorm anithmetie in salusing the Some
operators you. cuould in Exeel: + -)*,
ou ever
in saL you Can ony perform anithmetie acogg
Columons on values in agiven row. To clanify you
Con only add values in multiple columns Arorn the
Same row together asing +-iyou want to od ad
Values acrogs multiple rocus, you'll need to use
9Pegate Panetins.
Exemple - SELECT yeor
months
west
South,
wegt + south As [Link]
FROM Sales ;
Example.
SELECT Jear,
months
twest s
South,
west + south- 4year AS [Link]
FROM Sales;
Example - SELECT year,
month
west
Southy
(west +sout) /2 AS Soulh uest-avg
FROM Sales j
messages.downloaded_by
lOMoARcPSD|64140312
CREATE TABLE
The cREATE TABLE statement is uged to cneate
anew toble in [Link].
Example- CREATE TABLE person.l
Person ID ints
LastName Vanchar (255),
FristNome varchar (255),
Aldness varchan (255)
city varchor (255)
" INSERT INTO
The INSERT INTO SBatement is used to insert
neau peconds in atable.
4. Speciky both the colum names and the values to
be insented :
INSERT INTO table-name (Colunnd, colam2, eolumns,..)
VALUES (value 1, value2, Value3, ...);
2. Ifyo ane adding volues kr all the colams oP e tetl
you do not need to Spectty the eoumn names in he
3aL query"
INSERT INTO table_hame
VALUES (value1, \alue 2, value3,...);
messages.downloaded_by
lOMoARcPSD|64140312
What is a NULL Value ?
A Reld aith a NULL Value is a keld uth
value.
IP afeld in atable is ogtional d ie possible to
in cept a new recond op update a record
uilhoat adding a value to this Reld. Then. lhe
ield uill be "Saved uith a NULl value.
no
" Hou to Test tor NULL Values?
It is not possible to test for NylL walaes tuth
Companison operators, Sueh as =yk, op<>.
We udll have to use the IS NULL and IS NOT NUL
operatong inslead.
IS NULL Cperator
he
lhe TS NULL operator is used to test fop empty
yalues CNULL values).
Example-,
SELECT CustomerNome , corttactNome, Adtres
FROM Sales
WHERE Ad dress IS NULL 3
The IS NoT NULL Operator
The TS NOT NDÊL ope rator is used to test foo
non-empty yalues (NOT NULL Values).
Example -
SELECT cuskomenName, Catact Name , Addresg
FROM Saleg
WHERE Add ress IS NoT NULL;
messages.downloaded_by
" UPDATE Statement
lOMoARcPSD|64140312
The UPDATE stotement is used to madik he
existing peconds in a tobel.
Example
UPDATE Sales
SET ContactNane =" Alan", city=" Goa"
WAERE Customer ID= 1;
clausge that detepmines how many
UPDATE Maltiple Records
Hig the WHERE
pecopds enill be updated.
Example
UPDATE Sales
SET Postal Code = 00000
WHERE Cotry = India" ;
Notesi
Be Carelall eken. upateing recopds. T you omit
the WHERE clause, ALL Peconds cuill be updated!
DELETE State me nt
Te DELETE statemernt is used to delete exisling
pecords in a toble.
Example pEETE ROM Sales WHERE CustomerNarme =" Bob':
messages.downloaded_by
lOMoARcPSD|64140312
Delete Al Recopdg
I is possible to delete all roue in atable uthout
deleteing the table. This meang that the table
StnacBure , attribates , and inderes adl be itact:
Exmgle -
DELETE FROM toble_pame
" Altases
name.
Aliases are used togve atabe, on a colun in
a table, atempoary
Aliases are ol en ased to make coumn. Tames
more peadable.
An alias ony exists Kor the duration olat queny.
An aliag is created tuth the hs keyuOPd.
Al(cid:13)as Calumn Fxampe
SELECT Column_name AS alias _hame
FROM
table name ;
" Alias Table Fxample.
SELECT [Link] (s)
FROM table_name AS alias-name ;
messages.downloaded_by
lOMoARcPSD|64140312
" SQL Logical Operators
Logical operotons allow you to use muiple
Comporison openaton8 in One queny
Each logical openaton is o gpecial Snowtake,
s0 uel go through them indi vidualy in. the
follouing lessons.
"LIKE allow3, youto mateh simiar values,
instead oF exaet vaues.
" IN allows, you to specky a kat o
youwd like to include.
" BETWEEN allows you to select onl
wtthin a centain range.
" IS NULL allous you to seleet rous that
Contain. no data in a given eolumn.
" AND allowe you to select only rous that
Batis?y tao condtlions.
" OR alloue you to select rout that satict
either o tuo Conditions.
" NOT allous you to select powehat do not
lues
a let
match a centain conditicn.
"LTKE Operator
SELECT *
FROM Sales
WHERE gPoup" LIKE 'New%;
messages.downloaded_by
lOMoARcPSD|64140312
"IN Operatop
SELECT *
FROM Songs
WHERE artist IN (Taylor Suif.'Usher)3
" BETWEEN Operator
SELECT*
FROM Songs
WHERE year_Pank BETWEEN 5 AND 10
" AND Operator
SELECT *
FROM Songs
WHERE year = 2012 AND yean Pank <= 10;
OR OPeratop
SELECT *
FROM Sorngs
WHERE yeaR_Pank = 5 oR artist ="Soru" ;
" NOT OPerator
SELECT*
FROM Sales
WHERE NOT Country ="Jopan'
messages.downloaded_by
lOMoARcPSD|64140312
" Combining AND,OR and NOT
SELECT FROM Saleg
WHERE Counny-'Tapan' AND (CiBy- Go' oR cdy='ruri)
" QRDER By
SELECT
FROM Sales
ORDER By Country, Customer Name j
SELECT* FROM Sales
ORDER By coun:try Ase, Customen Name DESC
Usigq Comments CHow to use Commente)
"SELECT
-- This is select command
FROM Sales
WHERE year = 2020;
" /" Here's a Comment So long and deseniptive that
it could only ft on mulliple lines. fortanately
it, too, will not aleet how thig code runs. */
SELECT *
FROM Sales
WHERE year = 2015 ;
messages.downloaded_by
lOMoARcPSD|64140312
SQL Aagnegate Funclton
sQL is excellent at aggregating data the way you
might in. a pivet table in Fxcel. You will ase oggrgate
functions all the time,s0 ts impoptant toget
Comfontable utth [Link] functions themsaves
ane the Same ones you euill 4ind in Exeel or any
other analyties rogram.
" COUNT Counts how many row3 ane in a
Particulan column.
" SUM aslds togelhen all the values in a parlieular
Colun.
" MIN and MAX peturn the louest and hi¡hest
Values in a partieular colanm, respetively :
" AVG Calculates the average of a group of selected
yalues.
Example
SELECT COUNT)
FROM Saleg;
Examgle-
SELECT COUNT (Column hame)
FROM [Link]
WHERE Codition;
Example '-
S£LECT SUM (column
FROM toble name
WAERE Condition ;
messages.downloaded_by
name)
lOMoARcPSD|64140312
Example - SELECT MIN CColumn -name)
FROM toble_name
WHERE Condition;
Example -
S£LECT MAx Ccolunnnome)
FROM [Link]
WHERE Condition
Example :-
SELECT Ava Ccolumn-hame)
FROM [Link]
WHERE [Link] ;
The SQL GiROUP BY clause
GROUP 8S ollows you to Separate aata into groaps.,
chich can be aggregated independently of one anodher.
SELECT year,
COUNT (*) AS Count
FROM Sales
GAOUP BS year 3
Multile colamn
SELECT year,
month,
COUNT () As Count
FROM Saleg
GROUP 8y year, mondh ;
messages.downloaded_by
lOMoARcPSD|64140312
GROUP BY Column numbens
SELECT year,
morth,
COUNTO AS count
FROM Sales
GiROUP By 1,2
Using GhROUP BS wth ORDER BY
SELECT yeap,
month,
COUNT)AS Count
FROM &ales
GiROUP BN yean, month
ORDER BY month, Jear i
Using GROUP BY audth
IMTT
SELECT Column hames
FROM [Link]
WHERE Condetien
GhROUP By column hame
LIMIT umber ;
HAVING Clause
The HAVING clause was added to SQL becuse
the WHERE keyuord cannot be used aith aggregate
funetions.
messages.downloaded_by
Example :- SELECT Columnhame (3)
lOMoARcPSD|64140312
FROM able name
WHERE Condition
GROUP By [Link] (s)
HAVING ondtion
ORDER By column-name () ;
" SELECT year
month,
MAX (high) As month high
FROM Sales
GROUP AY yean, momih
HAVING MAX (high) > 4o0
ORDER BY year, month j
o
he SQl CASE statement
The aASE statement is sal's way o handling il/
then logic. The CASE state ment is Rolowed by at
least one pair o wAEN and THEN statements-sal's
equivalent oP IF/ THEN in Excel. Because o his
pairing . you might be tempted to call this sQL
CASE WHEN (cid:30) but CAsE is the aceepBed term.
Every CAs£ sBatement mugt end with he END
statement. The ElSE satement ie optional, an
provides a way to captune values not &pectfed tn
in
the wHEN /TH¾N Statement. cnsE is easiest to
understand in the conteyt of an example.
messages.downloaded_by
lOMoARcPSD|64140312
Syntax
CASE
WHEN Cohdition1 THEN pesult 1
WHEN Condition 2 THEN pesult2
WHEN Condition N THEN pesultN
ELSE resul!
END ;
Example SELECT OrderID, Quondty
CASE
WHEN Quantity > 30 THEN'he quontity is greato ba
WAEN quantity= 30 THEN he quartityie 30"
ELSE "The quantity is under 20
END AS Quantity Text
FROM 8oe s;
SQL DISTINCT
Jou'll occasionally want to look at nly the tunigue
values ina particaar colunn. You Can do this using
SELECT DISTINCT yntax.
Exomple:-
SELECT DIS TINCT month
FROM Sales ;
"SELECT DISTINCT year, month
FROM Sales ;
messages.downloaded_by
lOMoARcPSD|64140312
Using DISTINCT in agqreqations.
SELECT COUNT (DISTINCT month) AS Ungue-months
FROM Sales;
MySQL JOINS
A TOIN clause ts used to combine rows rom
two or more tables, based on a nelated column
on
between them.
Fxampe:
SELECT *
FROM [Link]- lotbal Playens playens
JOIN benm. college. Poolball teams team3
ON teamg. 3ehool_namne = plasyers .sehol
My SQL
Iypes o ToTNS in
Suppocted
Tges
"INNER JOIN : Retunns records that have matching
Values in. both tables.
"LEFT JOIN: Retuns all necopds hom the lefl }oble.
ond the matched recopds rom he night table.
" RIGHT JOIN : Returns all neconds rom the ight
table, and the matched peopde from he le4 table.
" CROSS JoIN: Returns all records Brom bolh tables.
INNER
JOIN
(ables
table2
table2
LEFT
JOTN
RIGHT
JOIN
CROSS
51Etno.e
J0IN
messages.downloaded_by
lOMoARcPSD|64140312
INNER J0IN
The INNER JOIN keyuond selects reconds lhat
have matchirg values in both tables.
INNER JOIN
(tabdlei tabe2)
Example
SELECT column _hame (6)
FROM table1
INNER JOIN table2
ON table 'i. eolumn_name = [Link];
LEFT J0TN
The LEFTJoIN keyaword peturns all nenond3 ro
rom
(i!
lable 1), ond lhe malehing reconds (:f
the lefl toble
ony) rom the ight table (lable 2).
LEFT JOIN
table2
Example
SELEC T Column_name (s)
FROM table 1
LEFT JOIN table 2
ON [Link]
messages.downloaded_by
toble2. Colmn hame ;
lOMoARcPSD|64140312
RIGHT JOIN
Ihe RIGHT JOIN keyuond petunns all peconds from
the night iable (Aables), and !he nalehig necras ti?
any )rom the lelf table Cloble 1).
RIGHT JOIN
(tablei
Example:
SELECT Column_name (s)
FROM Aable 1
RTGHT JOIN table 2
ON table1. Colunnname = toble [Link] ;
CROSS JOIN
The CROSs JOTN kejuord betarns all necovds rom
both tales Ctable 1 ond table 2).
CROSS JOIN
SELECT colum_name (9)
FROM table 1
CROSS JOTIN toble 2;
messages.downloaded_by
lOMoARcPSD|64140312
SELF JOTN
A sel! Join is a reaular join, bat the table is
Joined uith dsel.
Exomple :
SELECTaol umn-name (s)
FROM table1 T1 , table1.T2
WHERE Cendition
UNTON Operator
sQL joins allow yo¡ to cornoine tu catageta Side-by.
side, but UNTON allQwS you to staek one dataset on top
oPthe other. Put differenlly, UNION allowsyou to
aurite two, Separate sELECT State ments , and to have
the nesults o ene statemerd digplay in the Same table
as fhe results hrom the other staBement.
Example i
C SELECT Qolumn-name (s) FROM table 1
UNION
[Link] ($) FROM table 2;
" SELECT olumm_name (s) FROM Lable 1
UNTON ALL
SELECT coumn_nane Cs)
FROM table 2;
IN Operator
The IN openaBor allous youto SpeciBy meltiple
Values in a wHERE ause.
The TN openaAor is a shorthond 4or mulliple oA
Condetions.
messages.downloaded_by
lOMoARcPSD|64140312
Example :
SELECT FROM Sales
WHERE Count ny IN ("lndia', "Nepal "UK);
SELECT* FROM Sales
WHERE COuntny NOT TN("India, "Nepal; "Ux):
SELECT FROM Sales
WNERE Country TN (SELECT ountry FROM Suqplien) ;
EXISTS Opera-or
The EXISTS openator is used to test for the evistence
ol any necord in a Sub(cid:5)uery.
The EXISTS operator nelumg TRUE
Subqueny retuns
ne on more records.
he
Exanple:
SELECT column na me (8)
FBOM table-name
WHERE EXISTS
(SELECTeo [Link] [Link] WHERE Condetion);
ANY and ALL Operator
The ANY and ALl eperator allou you to perform
Comparison betueen Single colamn value and
Pange of olhen vaucs.
messages.downloaded_by
lOMoARcPSD|64140312
ANY OpemtoR
"I petuns a boolean value as a regult.
"It relupns TRUE P ANy o the Subquery valucs met
the condition.
ANS means tat the condtion ul be te ?he
operotion is true lor any of the values n the nonge.
Example:
SELECT Pro ductName FROM Sales
WHERE Product ID = ANY
(SELECT ProduetID FROM OrderDelails
WAERE Quantity > 99) 3
ALL Operator
"I reurns a boolean Vale as a pesult.
"I retuons TRUE ? ALL ?he sabguery values tmeet
the condition.
R is used with SElECT, WHERE ad HAVING statements.
ALL means that the condition will be true cly ? Jhe
Öperation is true fon all values in the ronge.
Example -
SELECT ALL Prodact Name
FROM Sales
WHERE TRUE ;
messages.downloaded_by
lOMoARcPSD|64140312
SELECT ArodctNayme FROM Sales
WHERE ProductID = ALL
(SELECT ProsucBID FROM OrderDetails
WHERE Quantity = 10)
NTO SELECT
INSERT
Ihe INSERT INTO SELECT sBatemert copies data ro
one table and inscrs tt into anadhentable.
The INSERT INTO SELEÇT staBement reguires thal the
dota types in source and Bargel tables matches.
lhe existing Pecords in the tayget tabe are unalfected.
Exampe:-
INSERT INTO table 2
"
SELECT FRDM toble 1
WHERE Condction ;
" INSERTINTO tble2 Ccolanl, column2, Qolam3, .)
SELECT Column 1, Colunn2, Colurn3 , ...
FROM toble1
WHERE cendition
INSERT INTO SBatement
The INSERT INIO statement is used o insert rew
recopd s ën a toble.
IH is posible to write the INSERT INTO staement
in two uoys.
messages.downloaded_by
lOMoARcPSD|64140312
" Specrty bl, tre colun rames and the valaes lobe
inserted.
INSERT INTOtabe. nane (oskomnls Colunn2, aumng,..)
VALUES (VAluei, Value2, value3, ...)3
"II you ape odding values forall he cdlums oPhe table
you do not need to speriky the colurm vamgs in the saL
quey. However , make Sure the orden o the values js in
the Same orden as the columns inhe table. tlene, the
INSERT INTO Syntax would be as folous.
INSERT INTO [Link]
VALUES (Valuei , value2, Value 3,...);
IFNULLO FuncBton.
IFNULLO.4nction lets you relurn on alternatire vaue !
an expression is NULL.
TIhe example below relurns 0 ?he value is NULL.
SELECT Cartactnane,
IFNULL (bizphone , homephone) As phone
FROM contacts
" SELECT mame
IFNULL CORtcephone , mbilephone) AS contact
FROM employee j
messages.downloaded_by