TBAihment - 01
Soceet- ity
Yclty-id wumeRK
SoccU_team Ghy VARcHAR(OS)
Paying-pesiion
8posithenid VARHAR{)
teamid NUMERC. utyid NUMeRIC
Positien dese VARCHAR2(1s) kom.qeup CAARALTOR(4)
mdh-playcd NUMERIC Secce- venwe
Won NUMERIC venue-d NutnERIC
dvow NVmeRC Yenenane VApHAR 230)
lost NVmERIC Cityid NwmER
playemast NUMERIC Qud capacty NymeRIC
player-id NuneRIC g°l againt NVmEeIe
teamid N¯MERK joal- digt lumeRC e7neemast
NUmERC Ponts NUméRC
matehast
VARHARY°) group-poshon NuneRC Coudry cd NumeRIc
Ymateh-ne NUmeRIC
Posito-play CAARRTER (2) play-stage CHARACTER(1)
dt--bi DATE Play dote DATE
age NumERC Soccet- Cowdry
Yesuts cHARACTER(5) Coachvast
-Peoutry-id AuwnERIC Coachid NUmeRIC
Paying-cub VaRCAHRZ() Couy-abb WRPRI(4) |decidedby CiARACTER (1)
Joal sceke CHARALTeg(5) Coach-nane VAReAR2(v)
Coutynae VARHRa
Tejeeid NUmtRIC
Playebeoked
audence NmtRic
-matho NUMERIC
Ply_-tratah NUMERIC teancache
teamd NumeAC Fayeinat Stop1- See NUMERIe
Plaeid NumERIc
Stopsec NUmERIC teamid NUnERLC
bocking-tin VARH ARZ(V°) teamid NUmERc
Seat 4 CHRACTER (4) Playe id UmtRc Coachid NUmEtiC
Payxedde CHARAL VER(2) inaut CHARACTeR(1) matchdalals
Puyha NUMERIC
mateh-no NumERie
|Play-schedule eRACTec() Playstage VARCHAPZ(0)
Playheu NUmnER\C
|atuhcoaplan teamid.NmERIC
winbe VARtAR2(1)
tam-id NWMee 9oal detouls Ipenalhy-shataut decidad by VARCHAR2(1)
|Playr captain nneR. Rtikid Nunekie Joolsue meRe
modeh no NUmeRIC mateh NUmER\C
Playelid NUmeec teamid NumERIC
penalhyg teamid NUMetIC playehid NUME eLC NUMeRIC
gotim NmERIC VARCUAR2()
matehno NumeRIC stve<eeemast
teanid wUMEc. goadStoqe kick-o NUMeRC jass_vel-id nNUmeE
sve}name VARCHAR2(40)
90alha NuMERL Coutry-id NUmERle
(4
Conside Hu given Sehema d Soccen Database and ite Sa£ queme.
tte
To Coun He
he nber o counthies eat patiipakd in
2021- ¬URO Cup
(a.) To find the nnbe o goals
Scfed stin nmal
laydg He
EURO up 2021. draus.
mber nathes eat endd ih
To tind He
shen e EUgO aup 2021 will
Fostbal EURo
(4) To ud But Bhoctot
mathes hat veultkd in a peralty
) To nd Hhe nwnten a
SE+EET EouNfDHSTtrtef Couudty As thunbertbnties
FperT FosreRtieipotior
owucantDe24EURO
1) SELECT coUNT( DISTUNCT teom id) AS Paticipahing Couties
Soccest_team
FROM
teamid IN(SeLECT DSTINCT teamid FRo m mateh detils
WHERE
WHERE ploy stage 'a)s
SELELT coUNT(oal id) As TotlGaonds
5
FROm qoal dotails
JoN mathmast ON gcal dtais, matehno = matehmast. math- no
WHERE Play-sigo - 'GAND goadtype = 'N;
SELECT coUNT (natehno) As DranMatehes
FRoM math nast
NHERE vesults = 'D' AND play stage = 'G ;
SeLECT MIN (play- date) As Tonament Stat Date
FRoM mateh mast
WHERE ply stage 'a
SELECT CoyNT(mathn As PenalthyShootoutMatehes
FROM matchmast
teRE decidelby 'P';
6) Conidn the fllouing tatdes
TABLE: EMPLOYEE
ECODE NAME DESIG SGRADe DoB
Vikvant Executwe So3 2003 -03-3 1980- 01-13
102 Ra Head-IT So2
ao10 - 0 - 12
1984-04-22
Jo09- o6 -4 1983- 02
Jshn Cena Receptionist SO3
00- 08- 11 (A84 6 -63
Azhas Anhai GM
l08 CEO 2004 -12-29 (982-l -9
TABLE: SALGRADE
tHRA
SGRAD£ SALARY
So S6o00
3 a00O 12000
%000
So3 24000
C) To dispay datid
Cio To diapay NAme and bEs I6 thate employes sRese sglade
ikey ESO ES03
cliüy To display NnE, DESIG,sGRADE hase empayee ho joined io
the yea late han 2000
(v) To diaplay all SGRADE, ANNUAL_SALARY
cv) To diplay unb emplayee wotkg each SALGRADE
cvi) o dplay NAME,DESIG, SALARY, HRA kon tables EMPLOYEE ad
SALGRADE we SALARY les than S0000
SELECT * FRON EMPLOYEE
oepER
ORDER BY
SELECT NANE, DESIG
FROM EmpLoYEE
HERE SGRADE 'So OR SGRADE ='So3;
SELECT NAMe, DeSLG, SGRADE
FROM EMpLOYEE
Wt1ERE YEAR (DoT) > 2000;;
SELECT SGRADE, ANNUALSALARY
FROM SALGRAOE )
() SELEcT SALGRADE, CoUNT (*) As ENPLOYEE_cOUNT
FRoM EmpLoYEE
GROUP BY SALGRADE ;
( ) SELECT E. NAME,E.DESIG, S.SALARY, S.HRA
FROm EMPLOYEE E
JotN SARGRADE S 0N E.SGRADE = S.ShRADE
NHERE S.SALAeY< 50 000
AmauntRid
Visis ID VisitName Gend Coingrorn
Kanpwr 2500
1 Suman
TI33
T33 Kucknow 3000
2000
3
Rachaa
4 Vitram Kanpu 4000
5 M Kanpu 3000
Rojesh
Alahabad 3600
Sunest
Diesh
Vannasi Soo0
Shitha
frem datails
() Wvite qvy to display Visitoi Name, Cemig Fre
female Visitßs sith A t Paid me than 3000.
(i) Waake a quey to display oll coming hem Location unigey
to insent te tolleunig valued - t, shilpa; F
3000
(iv) Nute a all
to duplay all datails o visitoss i
theia Amountad om higest to loutst.
9
cis SELEcT Visitss Nane, Comsiqtrem
Feom Visitor
NHERE hend = 'F No Amountfaid > 3000
() SeLecT distinet Coingron
FROM Visit
insent into visitoy alues (7, 'silpa', 'Fengoluu, Booo)
Select * kom visitoY
Order by Aanfaid desc
4) Fos he yelatien- Sehena :
- name, steet, city)
Eingtoyee
Wosks (emloyce- name Conpan-name ,solany
Cangony Cconmpany-rame,city)
onagnane)
Manags (anoye - nane
Da ER each
10 datatase sho
in tae
al employees
employee 'Snal! Bank Gaptation Ashune Hat a!
than erey
Joated in vereral F.
ities, Fud
Mau be
tswne hat the CouaA
Conpaes may
ihy in ashich 'Smal! Bank Capfahig'
all companies locatkd in eurey
Bocaed.
moie than he averae
find he ames all empleyees asho eas
at kas the Suallest payrotl.
Company
DOB
ER Diafanm: (hane')
rende
employee
N
CDog manages
1
at
| Compoy
Capaynane
() seLECT DISTINcT E.E. employee -name
FROM Empoyee E , Waks W
NHERE -name
Anp NOT EXISTS
SELECT
FROM Works W
wa. Connpay
WHERE wa. Conpang-name 'Sall Bank Cépration'
AnD W. Salaty <= W2. salaay
);
SeLET DISTINCT C.Company-name
FROM Comyony CC
WHERE NoT ExISTS(
S¬LECT *
FROM onpany C
wHERE Ca. cihy C. city
>
AND NOT EXsTS (
SELECT *
WteRE c3. Cempay Swall Bank yeaten
ANp C2.cihy - 3.tyY
)
2)
SELECT bISTINCT W. eangloape -
- name
FRoM Wks W
AHERE W. salby > (
seLecT AG (W. Saday
FRoM Woko w
SELECT W. Company-nawe
FROM WRk1 W
GeovP BY W. Cernpag
ORDER BY SUM (W. Salay)
5.) Coide te olloing elatienal Scana:
EMP (eid, enane,ap, Salosy
NORKS (cid, deptd, pet_time)
DEPT (Aeptid,'udgt, mqv-id)
aud Construct SeL ta each
Retieue he and age enployee uho astk in bot
13)
hade and Softwase depastent.
Foa each dupatment ith mie than 20 tull -tme emgees, punt H
deptid with mber ennployees that bodk n that dapastment.
adhate salay
enpoees ushate excead the
salay exced the budqet all the
depasteta.
Retriewe the nanes
the manago sto manage ony depateto
tan 100000.
No
Addhes Nan
Chocation)
Depattnest
Na Eneoyee
Genday Manage
Jokon
tours
(Name
Praject
to caton)
pepundt
lDependet
Dnan Cender Ralatinip
(14) SELECT DISTINcT
DIs TINCT è. &rame,e.oge
FROM EMPLOYEe e
Jo|N WDRKS W ON e.eid = Wei
NHERE W.degtid IN ('hasdne 'sagtuase)
GkoUP BY eeid, e ename,e. age
HAVING CoUNT (DISTINCT W. daptid) = 2;
seLeCT W.degtid, CouNT (*) As num enpleyes
FRO NoRKS
NHERE W.pcttine : 10
GROUP 8Y w.deptid
kAVING CoUNT (*) >20;
3 SELECT e.e. enme
FROM EMPLOyeE e
oEPARTMENT
NHeRE e, kaday > ALL(SELECT dibudget FRoM
4 SeLECT DiSTINCT e.enane
FROM EmPLOYCE e
DEPARTMENT d oN e.eid d.nqn-ic
WHERE J. budyt L00000
Anp Nor eISTS( SELECr FROM DE PARTnENT d
WHERE da,ma_id = tieid AND
d. budqet<= 400000)
EMP (eid, erame, age, salohy
NoRKS (eid,deçtid ,pt-in)
DEPr (daptid, budget, mgt-id)
Dras te ER diogan and Censtuct soL t each the oleg quvs
the dupantients wikn
9) ve the hames te so hanage ondy depatanto nith
budnti geaka tan 1o0000 but atteaut bne depastvent ith
budyet ae than So000.
id) budyet
age
Work Depatmant
CaEmployees
Wosktune
Paliuy
Dependenta
SELECT DISTINeT E. enase
FROM EMP E
TolN DEPT DON E. eid = D.rqs-id
NHERE Dbuyt =(Seer MAx (budqet) FROM DEPT);
SELECT DISTINCT E.ename
(16 FRON EMP E
JotN DEPr D ON E.cid - D. mqtd
AHeRE D. budqet > 100000
AND E,càd NoT IN (
SeLECT DISTIN CcT E1:ei
FROM EMP E1
DEPT Þ1 ON EL.eid = D1. mqB- id
JorN
< =5000
WHERE DL. budget
