Dee

SIRUCTURED QuCRY LANGGUAaE

DB- p8 is collec ion of datu infornat that can

be easy QcceSJed
Software ysed to ateess
applicqt"

manage DB

Typex
Orelahmd
bata soed in fable

SQ to w0Yk RDBMJ

Non- Relationgl CoSOL)

data not Se d n

x: mong oD B

SQL- programming lang

used to wH
Tela tonal dh.

T is used to perfom CROD operatrn

Create

Read
Updak
Delese

DB t ucty

DB
'Table 1
Taiez
pata Date
 Oy Frt Db

DATABASE db-nome Smail me bhi 1kh

CREATE saKI hai

DROP DA TA0sE dbnamne,

USe DB
USE db-nm e

frst table
USE dbngme

CREATG TABLE table name

Column name 1 daype Consbein

Column-namne2 datape? conrain,

Colu mn-nomn3 data typ Constaini

Studen+Da ta
CREATE DATABASE
USE stud eniDa ta,

CREATE TABLE Sud ent (

id INT PRIMARY kEY,
nme VARCHARCSo,
age INT NOT NLL

INSERT LTO Studen VALUES(l,"Kormne l', 2) / tuc

INSERT LNTO Student VALUE s(2, ryz, 2o))

SELECT * FROM Stud en t

/Table ko pint koge
|Co(Wn,
select and
 Dete

s@Ldatayp
signed & unsigned

toh
hamesha
bgaye
hote hai we will Uses UNSIGNED

+ve Q-Ve toe kara

But Ag d dono no.

likhno no
wet will

necesS ary

data hyfe ke iye

e sigo ed &

DoUBLE, TINY INT

hota hai INT, FLoAT

TINYLNT UNsIaNED(0 to 25S)

TINY INT (-182 to 127)

com mqno

Data defnihon Tenome, dop

Dqto Qu ey Languale Sel e

DML
Daa Manipu lo lan quage insest wpdak & delek

DCL
Dela corn bel langube 9ront&
to Ueo
 Tonochon Cont
Ianguage com mi{yll b ack

* DB QUeie
CREATE DATABASE dhnm e

CRGATG DATABAUC EXIS TS

DROP OATABASE db-nam e,

DROP DATABAJE IE db-nm e

SHow DATABAS ES;

SHOw TABLES

Table Yelaed Queies

Setet and ALL Colum ru

SeLECT ROM tablena me

SELECf shuden

TNSeRT LNTO table_nome VALUs (colt_vI, Co| 2-v)

oR
INSCRT TNTO tablenome (colnanme 1, col no me 2)
VALUC (col 1-vI, cd2, VID,
(cell_vei cel2.V3aK3
INGRT INTO Shuden (rellno, ame)
VALUCs
(lot, "komal') )

(162 ajun
 Constains
me data yre or conshain!
Creak toble

pedte ha

used to specity
mul es Lina tabie
Constainh

hove nuil
NOT NULLcol conne
difed
2 UNIQUG qll value co7unmn

PRIM ARY IceY mgle a Colunn unigu

FoREIGN KeY prrvent
achonthat wol d desbey

link betueen toblee

CREATE TABLE temp l

Cust_id Tnt

FoREIGN keY (cwt_id)ekrmes c4stome (rd)

defeutL volue colum

Solay INT DEFAULT 206

al lowed in
CaleerA.

CREATG TABLE templ

INT CHEC*( age 718PD city ='pune

Q) SAmple Examplei
CREATE eallegai
USe college

CREATG TABLE
 Yollno
INT PRIMARY kcy
nme VARCHAR (so),
INT NOT

grade VARCHAR (I:

VARCH AR (20)

INSERT s tudenA4CU
(rolln, name maTk grade Cihy )
VALU ES
101 qni)"78, C" Pune')
(I62,bhumika,93,'A,"Mu mbai),
(to3, Chetan 8S, B Hum bai"),
(1o4, dh" 94 DELHI):
(ios, emnue! ,2,E"Delhi);

Selec in do Detail.

Used to selecL
any dlata

SELECT col col2 FROM tablenme)

SELECT nme, mQk FRo M Sted en

seled Au Table

FRoM tablenqme

3 Aga7 Repeio nahy chig

toh

DISTINCT Ciby FRoy skudent;

clewe
To dehne Some condiin
 To dehne SOnme condiHonsM

SELECT Col1,Col2 FROM tablenme

WHERE Condiien

udenl wHGRG mqrk s > g0j

JCLECT ¥ FRoM S

in wHER E

4sing qperokr
Arithmehc operatorht-y A

Compoiion : =! =,>,><, <

AND, oR,NoT IN, BETWEEN, ALe

Logica
LIKE, ANY
AND, |(Bitwíse OR)

&X
wHGRE mqrks tIO>/00
SELECT KFRoM Styden

nrl 3)

chock for bo th
AND oPeal
WHRE malc, 86 AND City -"Mum boi
of conditen to be
OR checls for on e

.BETwLCN(select

IN (mgteh o any va lue n lisL

wH CRE crty IN ("Delhi,unmoi

Condiho )
WHeRe ciy NT IN(" Dethi' "'Mumbai"

* Limit clq4sa 9 LA

Rou dego
 ka da la
Agar Chiye toh

laga deg9

SELECT ¥ FRoM Skuden LIMIT 3

Orde by claue
To AsC Oy DESC oYder

SCLECT RoM stu den Asc

me
cily Acending.

A Aggse gale Funch ons

Aggoe gok pezfom calculahn se value
Valu e
single
CoUNT O SOM
MAX() AVaO
MIN(O
SCLECT max( mak) FR oM ctu dent lGet Mark

FROM student

hrep by clase
thet have vaty e in to

data
Colleb etordt Gm muliple YeCord and group
he more column

Ghengally
function.
 ¥ Coun No of studen h
cou n
in

Cname)
cach uy
RoM Studon .
ELECT ciky,

GRoUr By citd

rnunLaí
Jes

na4rn oojaye
banaya
shudenh har

opera h'urnperi
salce.,

OR

SELECT ciynam e , coun t (oll )

FROM Studon

GROUP B4 cihy, nam ej

Having clause
Simila7 to Jorne Condiion

used wph en want Cond,;en

to app
guping
er

SELECT Count Cneme), ci hy

FRoM

GROUr By ciky
HAVING max (mnk)90j
Oue: Count oof Shdens in
hmax mazks
eQeh cfy wh
CosJo.

General grder
 SELCCT Column ()
FRoM tablenome
wHCRE condihn
GRoup BY cotumn>

HAVNG Con diHon

ORDER B cold mn )Asc;

SELECT city
FRor ShedonE

where gade = 'A

GRoU By outft Delhi
ciby
MumbI
HAVING MAx Cmarh)>=93
ORDER By ciky
A sc

Table QUees Cupdete

updak (t erishng zow)

UPDATE table-nome
SET Col| Vail, Co1 2-Vo i2

wHCRC Condih on

UPDATE stud ent

wHCRG 9ade "A

(pelee
To
)
delee
DELETe FRoM table. no
UHGRCG condion,
 DCLETE FRoM stuod en

wHCRG marks <33

Y Reviihng Forgn key

2 tabte CreQe karege

INT PRINAR key

nme VARCHAR (so)

CREATE TABLE eache (

INT PRIMARY IEY
nam e VARCH AR Cso,
dert. id INT,
FoREIGaN kEy (dertid D REFERENCES deptCid)

Ke sah Connco

Dono table conn ced ha with help of

PK
ko table
dep ta ble paenL bol ege
Jo tab te Fk hai woh chil table hai

tu bio Pk hild tablo me

panent
ban JOk ha

(aseadng Foy foreign key

Agay eE Jagh. change hua toh dwx Jagk

Lhi hona chige
change

On Delele Caucade

puent tab dele

Agar kigq toh
 schemq design o6 Jabie >maans co Kone hal
kya dala ype hai, kya consan
hai woh

child teble bhi delee hona chiye.

On upda te casCa e

partn tabte me Kuch updak kiy

ta ble bhi
Chitd uçdaje hoga

uste baod
Jo teach ey ta ble banaya
foreign key line ke haad
ON UPDATE CASCADE
ON DELETCCACADE 2930

updok Quey kqrke pant tabk

me Pai Urko updak hai.
teachey b tablo me bhi updak hojayeg a
Lecauu of CASCA b|N,

* 1able Telakd

) to chane schomo
AD D colu mn
AITER TABLE table_nam
Query (alke)

ADD cold mn_name data tyre conshont

DRor column

AlTER TAB LE tabl e -neme

DRORcoLuMN Columnna me

Renoe table

ALTER TABLE tableomi

RENA ME To
hewtabename
 cHANGE column (enom)
ALtCR TABLE tablenome
old. nema new-ngme
CHANGaE COLUMN
new Cons hain t

MoDIEY Colu mo (medity daa ype /corvtaint)

ALTER to TABC tobleno me

MoDIEY colnenmo naudaaTyre nw Canshoint

elakd Query CTuncak )

T6 Jeee ble' date

TRUNCATGTABLE table_name

Drop ablo lco delele

Tun cele abl ka data delee Kate ha

Ham fir neo d a add katsakt!hai,.

JOINS
J6in i yd tocombihe TOWS of tuo OY

tabl es based on relaleJ colunn between

them
2 tobies ke JOUN Common Cotumn hona

chiye
FK bong Compulsay nahy has

* THees of Join
 has machuny Valeee
th at
Rewun Teeod
both f«ble

Inner Join B Dono tabte

anday
chiye hota hui toh

outey Joir

Left table

me
Jo OVe1aprins
Paxt hai woh

hai wo h
nahy mí'go,

(3) RIGHT Toin

Join

foy Inney Join

Synlax
SELEC T Column(s)

FR OM tabe A
IMNR JOIN teble B as b

tableA- colunme tabl oB -col-name,

a. cola nm =b.ce_neme

tea ble

LAHe name
adam I62
englih
Jo2
bob mat
coLSey |o3 Seien ca
 FRoM S tuden

INNGR JoIN CouJe

ON Studen!.stadernl íd- cam .d;

Resus (dnome Cou se

bob

/03 Seien

for Lert Joín

Synkx
SELECT COlumnG)
PRoM tuble A

LEFT Jot N to blo B

tableA. oneme tobleB. colnome

FRoM studon L

[Link]= ([Link]
ON a.d = C.d

fer Ri3h+ Jan

Sme lefl

foy Ful) Joín

synle
LEFT JoIN

UNIO
RIaHT JoiN

bich me UniorO
syntar
 e
gulejin bu ta bl
ioDd with iter f

FRoM tabie
JotN

oN Q. col_nome [Link] e,

UNION
C ornbine redurt - sel ofto Y more
SELECT stalenent
hives ecord.

shouid hove
evesy
Column. mwt have Simil4 data ty re

Colunmn. e ey SELECT should be in

SELECT co lumns) FROM tabie A

UNIo

SELECT Column) FROM tab leB

8x: SELECT hom FRoM ermpleyoa

UNIoV

PROM empluyee,
ReAunt me Jo employe nme haj fable me
ek baqr Hamn e
ke kiga
 Page N

likha toh
UNIO
Jagh
toh names zepeal hoke aayege

Thn ey
Nes ed

within ano h oy

TA involve 2 Selod

synlr
SELECT Columno)
FRON teble nome

wHGRC col_nqrne Opeg tor

Csubqy ey
ex. sd ent tobe
yollne name mark
ani!
|62 bhumt 93
|63 chetn
Joy dhuu
emqnu 92
|
e

66 farah &2

Get nome of qI shudenb

than cla qvg

Fnd avg of cles

Sep2 Gnd stu dernh wí

marks >avg
 SGLECT nome, man

FRoM Stud orn l

WHeRC manl >(sCLCCT AVG(manks ERoM S tud ent);

Thus 4able me
dyntmic
updak hua toh Avg bhi chene hok rahega

) oh oVel

And nam
Fnd
of

namo
ven
tedenh
S

oF
change

student
hoga

with roll numbey

Yol] no

SELECT
PRoy Stud ent

name
SELECT o||o bhymike |62
PRo S tudn dh
whex olln 2=o) Fora h

MysoL views
Vitua tabl e

Jo main,l tab1e hai U ska thoda

par
table

Ta ble ke
dalege

ke
Ce dif
kange
9uenes kage

CREATG vIew view As

SELECT FRoM Stu dernt,

SELECT FRoN show

hoj ayega,
 PaeNc.
Date

View1 table ka nam hai

Hamne Vies eble stud m

ollndala
"Teahb ko ka mqrk nahe

pala d h ega

bol