DATABASEAPPLI

CATI
ONSLABORATORY

1.Consi
dert
hef
oll
owi
ngschemaf
oraLi
brar
yDat
abase:
Book_
BOOK( id,
Tit
le,
Publ
i
sher
_Name,
Pub_
Year
)
BOOK_
AUTHORS(
Book_
id,
Aut
hor
_Name)
PUBLI Name,
SHER( Addr
ess,
Phone)
BOOK_
COPI Book_
ES( id,
Branch_
id,
No-
of_
Copi
es)
BOOK_
LENDI Book_
NG( id,
Branch_
id,
Car
d_No,
Dat
e_Out
,Due_
Dat
e)
LI
BRARY_ Br
BRANCH( anch_
id,
Branch_
Name,
Addr
ess)
Wr
it
eSQLquer
iest
o
1.Ret
ri
evedet
ail
sofal
lbooksi
nthel
i
brar
y–i
d,t
it
le,
nameofpubl
i
sher
,aut
hor
s,
numberofcopi
esi
neachbr
anch,
etc.
2.Gett
hepar
ti
cul
arsofbor
rower
swhohav
ebor
rowedmor
ethan3books,
butf
rom
Jan2017t
oJun2017
3.Del
eteabooki
nBOOKt
abl
e.Updat
ethecont
ent
sofot
hert
abl
est
oref
lectt
his
dat
amani
pul
ati
onoper
ati
on.
4.Par
ti
ti
ont
heBOOKt
abl
ebasedony
earofpubl
i
cat
ion.Demonst
rat
eit
swor
king
wi
thasi
mpl
equer
y.
5.Cr
eat
eav
iewofal
lbooksandi
tsnumberofcopi
est
hatar
ecur
rent
lyav
ail
abl
ein
t
heLi
brar
y.
Tabl
eCr
eat
ion:
CREATETABLEPUBLI
SHER
(
NAMEVARCHAR2(
20)PRI
MARYKEY,
PHONEI
NTEGER,
ADDRESSVARCHAR2(
20)
);

CREATETABLEBOOK
(
BOOK_
IDI
NTEGERPRI
MARYKEY,
TI
TLEVARCHAR2(
20)
,
PUB_
YEARVARCHAR2(
20)
,
PUBLI
SHER_
NAMEREFERENCESPUBLI
SHER(
NAME)ONDELETECASCADE)
;

CREATETABLEBOOK_
AUTHORS
(
AUTHOR_
NAMEVARCHAR2(
20)
,
BOOK_
IDREFERENCESBOOK(
BOOK_
ID)ONDELETECASCADE,
PRI
MARYKEY(
BOOK_
ID,
AUTHOR_
NAME)
);
CREATETABLELI
BRARY_
BRANCH
(
BRANCH_
IDI
NTEGERPRI
MARYKEY,
BRANCH_
NAMEVARCHAR2(
50)
,
ADDRESSVARCHAR2(
50)
);

CREATETABLEBOOK_
COPI
ES
(
NO_
OF_
COPI
ESI
NTEGER,
BOOK_
IDREFERENCESBOOK(
BOOK_
ID)ONDELETECASCADE,
BRANCH_
IDREFERENCESLI
BRARY_
BRANCH(
BRANCH_
ID)ONDELETECASCADE,
PRI
MARYKEY(
BOOK_
ID,
BRANCH_
ID)
);

CREATETABLEBORROWER
(
CARD_
NOI
NTEGERPRI
MARYKEY,
NAMEVARCHAR(
10)
,ADDRESS
VARCAHR(
20)
,PHONEI
NT)
;

CREATETABLEBOOK_
LENDI
NG
(
DATE_
OUTDATE,
DUE_
DATEDATE,
BOOK_
IDREFERENCESBOOK(
BOOK_
ID)ONDELETECASCADE,
BRANCH_
IDREFERENCESLI
BRARY_
BRANCH(
BRANCH_
ID)ONDELETECASCADE,
CARD_
NOREFERENCESBORROWER(
CARD_
NO)ONDELETECASCADE,
PRI
MARYKEY(
BOOK_
ID,
BRANCH_
ID,
CARD_
NO)
);

QUERI
ES:
1.Ret
ri
evedet
ail
sofal
lbooksi
nthel
i
brar
y–i
d,t
it
le,
nameofpubl
i
sher
,aut
hor
s,
numberofcopi
esi
neachbr
anch,
etc.

sel
ect
b.
book_
id,
b.t
it
le,
b.publ
i
sher
_name,
ba.
aut
hor
_name,
bc.
branch_
id,
bc.
num_
of_
copi
es
f
rom bookb,
book_
aut
hor
sba,
book_
copi
esbc
wher
eb.
bki
d=ba.
bki
dandb.
bki
d=bc.
bki
d;
2)Gett
hepar
ti
cul
arsofbor
rower
swhohav
ebor
rowedmor
ethan3books,
butf
rom
Jan2017t
oJun2017.
SELECTCARD_
NO
FROM BOOK_
LENDI
NG
WHEREDATE_
OUTBETWEEN’
01-
JAN-
2017’
AND’
01-
JUL-
2017’
GROUPBYCARD_
NO
HAVI
NGCOUNT(
*)>3;

3)Del
eteabooki
nBOOKt
abl
e.Updat
ethecont
ent
sofot
hert
abl
est
oref
lectt
his
dat
amani
pul
ati
onoper
ati
on.
DELETEFROM BOOK
WHEREBOOK_
ID=3;

4)Par
ti
ti
ont
heBOOKt
abl
ebasedony
earofpubl
i
cat
ion.Demonst
rat
eit
swor
king
wi
thasi
mpl
equer
y.
sel
ectbook_
id,
tit
le,
publ
i
sher
_name,
pub_
year
f
rom book
gr
oupbypub_
year
,book_
id,
tit
le,
pub_
name;
5)Cr
eat
eav
iewofal
lbooksandi
tsnumberofcopi
est
hatar
ecur
rent
lyav
ail
abl
ein
t
heLi
brar
y.
cr
eat
evi
ewLI
BRARY_
BOOKS_
DB
assel
ectb.
book_
id,
sum(
no_
of_
copi
es)
f
rom book_
copi
esb
gr
oupbybook_
id;
v
iewcr
eat
ed
Not
e:
Toseet
hecont
ent
sofv
iew,
use
Sel
ect*f
rom LI
BRARY_
BOOKS_
DB;
i
nser
tther
owonbook_
copi
est
abl
eforanybookandcheckwhet
hert
hesum of
copi
esf
ort
hatbooki
disupdat
edi
nthev
iewornot
.
2.Consi
dert
hef
oll
owi
ngschemaf
orOr
derDat
abase:
Sal
SALESMAN( esman_
id,
Name,
Cit
y,Commi
ssi
on)
Cust
CUSTOMER( omer
_id,
Cust
_Name,
Cit
y,Gr
ade,
Sal
esman_
id)
Or
ORDERS( d_
No,
Pur
chase_
Amt
,Or
d_Dat
e,Cust
omer
_id,
Sal
esman_
id)
Wr
it
eSQLquer
iest
o
1.Countt
hecust
omer
swi
thgr
adesabov
eBangal
ore’
sav
erage.
2.Fi
ndt
henameandnumber
sofal
lsal
esmenwhohadmor
ethanonecust
omer
.
3.Li
stal
lsal
esmenandi
ndi
cat
ethosewhohav
eanddon’
thav
ecust
omer
sint
hei
r
ci
ti
es(
UseUNI
ONoper
ati
on.
)
4.Cr
eat
eav
iewt
hatf
indst
hesal
esmanwhohast
hecust
omerwi
tht
hehi
ghest
or
derofaday
.
5.Demonst
rat
etheDELETEoper
ati
onbyr
emov
ingsal
esmanwi
thi
d1000.Al
lhi
s
or
der
smustal
sobedel
eted.

Tabl
eCr
eat
ion:
CREATETABLESALESMAN
(
SIDNUMBER(
4)PRI
MARYKEY,
NAMEVARCHAR2(
20)
,
CI
TYVARCHAR2(
20)
,
COMMI
SSI
ONVARCHAR2(
20)
);

CREATETABLECUSTOMER
(
CIDNUMBER(
4)PRI
MARYKEY,
CUST_
NAMEVARCHAR2(
20)
,
CI
TYVARCHAR2(
20)
,
GRADENUMBER(
3),
SI
DREFERENCESSALESMAN(
SID)ONDELETESETNULL)
;

CREATETABLEORDERS
(
ORD_
NONUMBER(
5)PRI
MARYKEY,
P_
AMTNUMBER(
10,
2),
ORD_
DATEDATE,
CI
DREFERENCESCUSTOMER1(
CID)ONDELETECASCADE,
SI
DREFERENCESSALESMAN(
SID)ONDELETECASCADE)
;

1.Countt
hecust
omer
swi
thgr
adesabov
eBangal
ore’
sav
erage.
sel
ectcount
(*)asNUMBER_
OF_
CUSTOMERS
f
rom cust
omer
wher
egr
ade>(sel
ectav
g(gr
ade)
f
rom cust
omer
wher
eci
ty='
bl
ore'
);

2.Fi
ndt
henameandnumber
sofal
lsal
esmenwhohadmor
ethanonecust
omer
.

sel
ectsal
esman.
sid,
sal
esman.
name
f
rom sal
esman,
order
s
wher
esal
esman.
sid=or
der
s.si
d
gr
oupbysal
esman.
sid
hav
ingcount
(ci
d)>1)
;
3.Li
stal
lsal
esmenandi
ndi
cat
ethosewhohav
eanddon’
thav
ecust
omer
sint
hei
r
ci
ti
es(
UseUNI
ONoper
ati
on.
)

(
sel
ects.
sid,
s.
cit
y,c.
cid,
c.
cit
y,
'HAVECUSTOMERI
NCI
TY'
f
rom sal
ess,
cust
omerc
wher
es.
sid=c.
sidandc.
cit
y=s.
cit
y)
UNI
ON
(
sel
ects.
sid,
s.
cit
y,c.
cid,
c.
cit
y,
'DONTHAVECUSTOMERI
NCI
TY'
f
rom sal
ess,
cust
omerc
wher
es.
sid=c.
sidandc.
cit
y!=s.
cit
y);

4.
Creat
eav
iewt
hatf
indst
hesal
esmanwhohast
hecust
omerwi
tht
hehi
ghestor
der
ofaday
.
cr
eat
evi
ewHI
GHEST_
ORDERSas
sel
ectsi
d,name
f
rom sal
esman
wher
esi
din(
sel
ectsi
d
f
rom or
der
s
wher
ep_
amount
=(sel
ectmax(
p_amount
)
f
rom or
der
swher
e
odat
e='
12-
aug-
17'
))
;
v
iewcr
eat
ed
sel
ect*f
rom HI
GHEST_
ORDERS;
(t
ovi
ewt
hecont
ent
soft
hev
iew)
5.Demonst
rat
etheDELETEoper
ati
onbyr
emov
ingsal
esmanwi
thi
d1000.Al
lhi
s
or
der
smustal
sobedel
eted.
del
etef
rom sal
es
wher
esi
d=1000;
3.
Consi
dert
heschemaf
orMov
ieDat
abase:
Act
ACTOR( _id,
Act
_Name,
Act
_Gender
)
DI Di
RECTOR( r
_id,
Dir
_Name,
Dir
_Phone)
MOVI Mov
ES( _id,
Mov
_Ti
tl
e,Mov
_Year
,Mov
_Lang,
Dir
_id)
MOVI
E_ Act
CAST( _id,
Mov
_id,
Rol
e)
RATI Mov
NG( _id,
Rev
_St
ars)
Wr
it
eSQLquer
iest
o
1.Li
stt
het
it
lesofal
lmov
iesdi
rect
edby‘
Hit
chcock’
.
2.Fi
ndt
hemov
ienameswher
eoneormor
eact
orsact
edi
ntwoormor
emov
ies.
3.Li
stal
lact
orswhoact
edi
namov
iebef
ore2000andal
soi
namov
ieaf
ter2015
(
useJOI
Noper
ati
on)
.
4.Fi
ndt
het
it
leofmov
iesandnumberofst
arsf
oreachmov
iet
hathasatl
eastone
r
ati
ngandf
indt
hehi
ghestnumberofst
arst
hatmov
ier
ecei
ved.Sor
tther
esul
tby
mov
iet
it
le.
5.Updat
erat
ingofal
lmov
iesdi
rect
edby‘
Stev
enSpi
elber
g’t
o5.
Tabl
eCr
eat
ion:
CREATETABLEACTOR(
ACT_
IDNUMBER(
3)PRI
MARYKEY,
ACT_
NAMEVARCHAR(
20)
,
ACT_
GENDERCHAR(
1))
;

CREATETABLEDI
RECTOR(
DI
R_I
DNUMBER(
3)PRI
MARYKEY,
DI
R_NAMEVARCHAR(
20)
,
DI
R_PHONENUMBER(
10)
);

CREATETABLEMOVI
ES(
MOV_
IDNUMBER(
4)PRI
MARYKEY,
MOV_
TITLEVARCHAR(
25)
,
MOV_
YEARNUMBER(
4),
MOV_
LANGVARCHAR(
12)
,
DI
R_I
DNUMBER(
3)REFERENCESDI
RECTOR(
DIR_
ID)
);

CREATETABLEMOVI
E_CAST(
ACT_
IDNUMBER(
3)REFERENCESACTOR(
ACT_
ID)
,
MOV_
IDNUMBER(
4)REFERENCESMOVI
ES(
MOV_
ID)
,
ROLEVARCHAR(
10)
,
PRI
MARYKEY(
ACT_
ID,
MOV_
ID)
);

CREATETABLERATI
NG(
MOV_
IDNUMBER(
4)REFERENCESMOVI
ES(
MOV_
ID)
,
REV_
STARSVARCHAR(
25)
,
PRI
MARYKEY(
MOV_
ID)
);

Quer
ies:
1.Li
stt
het
it
lesofal
lmov
iesdi
rect
edby‘
Hit
chcock’
.

SELECTMOV_
TITLE
FROM MOVI
ES
WHEREDI
R_I
DIN(
SELECTDI
R_I
D
FROM DI
RECTOR
WHEREDI
R_NAME=‘
HITCHCOCK’
);
2.Fi
ndt
hemov
ienameswher
eoneormor
eact
orsact
edi
ntwoormor
emov
ies.
SELECTMOV_
TITLE
FROM MOVI
ESM,
MOVI
E_CASTMV
WHEREM.
MOV_
ID=MV.
MOV_
IDANDACT_
IDI
N(SELECTACT_
ID
FROM MOVI
E_CAST
GROUPBYACT_
ID
HAVI
NGCOUNT(
ACT_
ID)
>1)
GROUPBYMOV_
TITLE
HAVI
NGCOUNT(
*)>1;
3.
Listal
lact
orswhoact
edi
namov
iebef
ore2000andal
soi
namov
ieaf
ter2015
(
useJOI
Noper
ati
on)
.
(
sel
ects.
sid,
s.
cit
y,c.
cid,
c.
cit
y,
'HAVECUSTOMERI
NCI
TY'
f
rom sal
ess,
cust
omerc
wher
es.
sid=c.
sidandc.
cit
y=s.
cit
y)
UNI
ON
(
sel
ects.
sid,
s.
cit
y,c.
cid,
c.
cit
y,
'DONTHAVECUSTOMERI
NCI
TY'
f
rom sal
ess,
cust
omerc
wher
es.
sid=c.
sidandc.
cit
y!=s.
cit
y);

4.Fi
ndt
het
it
leofmov
iesandnumberofst
arsf
oreachmov
iet
hathasatl
eastone
r
ati
ngandf
indt
hehi
ghestnumberofst
arst
hatmov
ier
ecei
ved.Sor
tther
esul
tby
mov
iet
it
le.
SELECTMOV_
TITLE,
MAX(
REV_
STARS)
FROM MOVI
ESM,
RATI
NGR
WHEREM.
MIV_
ID=R.
MOV_
ID
GROUPBYMOV_
TITLE
HAVI
NGMAX(
REV_
STARS)
>0
ORDERBYMOV_
TITLE;

5.Updat
erat
ingofal
lmov
iesdi
rect
edby‘
Stev
enSpi
elber
g’t
o5

UPDATERATI
NG
SETREV_
STARS=5
WHEREMOV_
IDI
N(SELECTMOV_
ID
FROM MOVI
ESM,
DIRECTORR
WHEREM.
DIR_
ID=D.
DIR_
ID
ANDD.
DIR_
NAME=‘
STEVENSPI
ELBERG’
);
4)
Consi
dert
heschemaf
orCol
l
egeDat
abase:
USN,
STUDENT( SName,
Addr
ess,
Phone,
Gender
)
SSI
SEMSEC( D,Sem,
Sec)
USN,
CLASS( SSI
D)
Subcode,
SUBJECT( Tit
le,
Sem,
Credi
ts)
I USN,
AMARKS( Subcode,
SSI
D,Test
1,Test
2,Test
3,Fi
nal
IA)
Wr
it
eSQLquer
iest
o
1.Li
stal
lthest
udentdet
ail
sst
udy
ingi
nfour
thsemest
er‘
C’sect
ion.
2.Comput
ethet
otal
numberofmal
eandf
emal
est
udent
sineachsemest
erandi
n
eachsect
ion.
3.Cr
eat
eav
iewofTest
1mar
ksofst
udentUSN‘
1BI
15CS101’
inal
lsubj
ect
s.
4.Cal
cul
atet
heFi
nal
IA(
aver
ageofbestt
wot
estmar
ks)andupdat
ethe
cor
respondi
ngt
abl
eforal
lst
udent
s.
5.Cat
egor
izest
udent
sbasedont
hef
oll
owi
ngcr
it
eri
on:

I
fFi
nal
IA=17t
o20t
henCAT=‘
Out
standi
ng’
I
fFi
nal
IA=12t
o16t
henCAT=‘
Aver
age’
I
fFi
nal
IA<12t
henCAT=‘
Weak’
Gi
vet
hesedet
ail
sonl
yfor8t
hsemest
erA,
B,andCsect
ionst
udent
s.
Tabl
eCr
eat
ion
CREATETABLESTUDENT(
USNVARCHAR(
10)PRI
MARYKEY,
SNAMEVARCHAR(
25)
,
ADDRESSVARCHAR(
25)
,
PHONENUMBER(
10)
,
GENDERCHAR(
1))
;

CREATETABLESEMSEC(
SSI
DVARCHAR(
5)PRI
MARYKEY,
SEM NUMBER(
2),
SECCHAR(
1))
;

CREATETABLECLASS(
USNVARCHAR(
10)
,
SSI
DVARCHAR(
5),
PRI
MARYKEY(
USN,
SSI
D),
FOREI
GNKEY(
USN)REFERENCESSTUDENT(
USN)
,
FOREI
GNKEY(
SSI
D)REFERENCESSEMSEC(
SSI
D))
;

CREATETABLESUBJECT(
SUBCODEVARCHAR(
8),
TI
TLEVARCHAR(
20)
,
SEM NUMBER(
2),
CREDI
TSNUMBER(
2),
PRI
MARYKEY(
SUBCODE)
);

CREATETABLEI
AMARKS(
USNVARCHAR(
10)
,
SUBCODEVARCHAR(
8),
SSI
DVARCHAR(
5),
TEST1NUMBER(
2),
TEST2NUMBER(
2),
TEST3NUMBER(
2),
FI
NALI
ANUMBER(
2),
PRI
MARYKEY(
USN,
SUBCODE,
SSI
D),
FOREI
GNKEY(
USN)REFERENCESSTUDENT(
USN)
,
FOREI
GNKEY(
SUBCODE)REFERENCESSUBJECT(
SUBCODE)
,
FOREI
GNKEY(
SSI
D)REFERENCESSEMSEC(
SSI
D))
;

Quer
ies:
1.Li
stal
lthest
udentdet
ail
sst
udy
ingi
nfour
thsemest
er‘
C’sect
ion.

SELECTS.
USN,
S.SNAME,
S.ADDRESS,
S.PHONE,
S.GENDER,
SS.
SEM,
SS.
SEC
FROM STUDENTS,
SEMSECSS,
CLASSC
WHERES.
USN=C.
USNAND
SS.
SSI
D=C.
SSI
DANDSS.
SEM =4ANDSS.
SEc=’
C’;
2.Comput
ethet
otal
numberofmal
eandf
emal
est
udent
sineachsemest
erandi
n
eachsect
ion.
SELECTSS.
SEM,
SS.
SEC,
S.GENDER,
COUNT(
S.GENDER)ASCOUNT
FROM STUDENTS,
SEMSECSS,
CLASSC
WHERES.
USN=C.
USNANDSS.
SSI
D=C.
SSI
D
GROUPBYSS.
SEM,
SS.
SEC,
S.GENDER
ORDERBYSEM;
3.Cr
eat
eav
iewofTest
1mar
ksofst
udentUSN‘
1BI
15CS101’
inal
lsubj
ect
s.
CREATEVI
EW STU_
TEST1_
MARKS_
VIEW
AS SELECTTEST1,
SUBCODE
FROM I
AMARKS
WHEREUSN='
1RN13CS091'
;

4.Cal
cul
atet
heFi
nal
IA(
aver
ageofbestt
wot
estmar
ks)andupdat
ethe
cor
respondi
ngt
abl
eforal
lst
udent
s.
CREATEORREPLACEPROCEDUREAVGMARKS
I
S
CURSORC_
IAMARKSI
S
SELECTGREATEST(
TEST1,
TEST2)ASA,
GREATEST(
TEST1,
TEST3)ASB,
GREATEST(
TEST3,
TEST2)ASC
FROM I
AMARKS
WHEREFI
NALI
AISNULL
FORUPDATE;
C_
ANUMBER;
C_
BNUMBER;
C_
CNUMBER;
C_
SM NUMBER;
C_
AVNUMBER;
BEGI
N
OPENC_
IAMARKS;
LOOP
FETCHC_
IAMARKSI
NTOC_
A,C_
B,C_
C;
EXI
TWHENC_
IAMARKS%NOTFOUND;
I
F(C_
A!=C_
B)THEN
C_
SM:
=C_
A+C_
B;
ELSE
C_
SM:
=C_
A+C_
C;
ENDI
F;
C_
AV:
=C_
SM/
2;
UPDATEI
AMARKSSETFI
NALI
A=C_
AVWHERECURRENTOFC_
IAMARKS;
ENDLOOP;
CLOSEC_
IAMARKS;
END;
/

Toexecut
eabov
epr
ocedur
e:
SQL>BEGIN
STUDENT_
AVG;
END;
/
Or
SQL>ExecSTUDENT_
AVG;
5.Cat
egor
izest
udent
sbasedont
hef
oll
owi
ngcr
it
eri
on:
I
fFi
nal
IA=17t
o20t
henCAT=‘
Out
standi
ng’
I
fFi
nal
IA=12t
o16t
henCAT=‘
Aver
age’
I
fFi
nal
IA<12t
henCAT=‘
Weak’
Gi
vet
hesedet
ail
sonl
yfor8t
hsemest
erA,
B,andCsect
ionst
udent
s.

SELECTS.
USN,
S.SNAME,
S.ADDRESS,
S.PHONE,
S.GENDER,
IA.
SUBCODE
(
CASE
WHENI
A.FI
NALI
ABETWEEN17AND20THEN'
OUTSTANDI
NG'
WHENI
A.FI
NALI
ABETWEEN12AND16THEN'
AVERAGE'
ELSE'
WEAK'
END)ASCAT
FROM STUDENTS,
SEMSECSS,
IAMARKSI
A
WHERES.
USN=I
A.USNANDSS.
SSI
D=I
A.SSI
DANDSS.
SEM =8;
E.Consi
dert
heschemaf
orCompanyDat
abase:

SSN,
EMPLOYEE( Name,
Addr
ess,
Sex,
Sal
ary
,Super
SSN,
DNo)
DNo,
DEPARTMENT( DName,
Mgr
SSN,
Mgr
Star
tDat
e)
DLOCATI DNo,
ON( DLoc)
PNo,
PROJECT( PName,
PLocat
ion,
DNo)
WORKS_ SSN,
ON( PNo,
Hour
s)
Wr
it
eSQLquer
iest
o
1.Makeal
i
stofal
lpr
ojectnumber
sforpr
oject
sthati
nvol
veanempl
oyeewhosel
ast
namei
s‘Scot
t’
,ei
therasawor
kerorasamanageroft
hedepar
tmentt
hatcont
rol
s
t
hepr
oject
.
2.Showt
her
esul
ti
ngsal
ari
esi
fev
eryempl
oyeewor
kingont
he‘
I
oT’
proj
ecti
sgi
ven
a10per
centr
aise.
3.Fi
ndt
hesum oft
hesal
ari
esofal
lempl
oyeesoft
he‘
Account
s’depar
tment
,aswel
l
ast
hemaxi
mum sal
ary
,themi
nimum sal
ary
,andt
heav
eragesal
aryi
nthi
s
depar
tment
4.Ret
ri
evet
henameofeachempl
oyeewhowor
ksonal
lthepr
oject
scont
rol
l
edby
depar
tmentnumber5(
useNOTEXI
STSoper
ator
).Foreachdepar
tmentt
hathas
mor
ethanf
iveempl
oyees,
ret
ri
evet
hedepar
tmentnumberandt
henumberofi
ts
empl
oyeeswhoar
emaki
ngmor
ethanRs.6,
00,
000.
Tabl
eCr
eat
ion
CREATETABLEDEPARTMENT
(
DNOVARCHAR2(
20)PRI
MARYKEY,
DNAMEVARCHAR2(
20)
,
MGRSTARTDATEDATE)
;

CREATETABLEEMPLOYEE
(
SSNVARCHAR2(
20)PRI
MARYKEY,
FNAMEVARCHAR2(
20)
,
LNAMEVARCHAR2(
20)
,
ADDRESSVARCHAR2(
20)
,
SEXCHAR(
1),
SALARYI
NTEGER,
SUPERSSNREFERENCESEMPLOYEE(
SSN)
,
DNOREFERENCESDEPARTMENT(
DNO)
);

NOTE:
OnceDEPARTMENTandEMPLOYEEt
abl
esar
ecr
eat
edwemustal
ter
depar
tmentt
abl
etoaddf
orei
gnconst
rai
ntMGRSSNusi
ngsql
command

ALTERTABLEDEPARTMENT
ADDMGRSSNREFERENCESEMPLOYEE(
SSN)
;

CREATETABLEDLOCATI
ON
(
DLOCVARCHAR2(
20)
,
DNOREFERENCESDEPARTMENT(
DNO)
,
PRI
MARYKEY(
DNO,
DLOC)
);

CREATETABLEPROJECT
(
PNOI
NTEGERPRI
MARYKEY,
PNAMEVARCHAR2(
20)
,
PLOCATI
ONVARCHAR2(
20)
,
DNOREFERENCESDEPARTMENT(
DNO)
);

CREATETABLEWORKS_
ON
(
HOURSNUMBER(
2),
SSNREFERENCESEMPLOYEE(
SSN)
,
PNOREFERENCESPROJECT(
PNO)
,
PRI
MARYKEY(
SSN,
PNO)
);

I
nser
ti
onofv
aluest
otabl
es
I
NSERTI
NTOEMPLOYEE(
SSN,
FNAME,
LNAME,
ADDRESS,
SEX,
SALARY)VALUES
(
‘RNSECE01’
,
’JOHN’
,
’SCOTT’
,
’BANGALORE’
,
’M’
,450000)
;
I
NSERTI
NTOEMPLOYEE(
SSN,
FNAME,
LNAME,
ADDRESS,
SEX,
SALARY)VALUES
(
‘RNSCSE01’
,
’JAMES’
,
’SMI
TH’
,
’BANGALORE’
,
’M’
,500000)
;
I
NSERTI
NTOEMPLOYEE(
SSN,
FNAME,
LNAME,
ADDRESS,
SEX,
SALARY)VALUES
(
‘RNSCSE02’
,
’HEARN’
,
’BAKER’
,
’BANGALORE’
,
’M’
,700000)
;
I
NSERTI
NTOEMPLOYEE(
SSN,
FNAME,
LNAME,
ADDRESS,
SEX,
SALARY)VALUES
(
‘RNSCSE03’
,
’EDWARD’
,
’SCOTT’
,
’MYSORE’
,
’M’
,500000)
;
I
NSERTI
NTOEMPLOYEE(
SSN,
FNAME,
LNAME,
ADDRESS,
SEX,
SALARY)VALUES
(
‘RNSCSE04’
,
’PAVAN’
,
’HEGDE’
,
’MANGALORE’
,
’M’
,650000)
;
I
NSERTI
NTOEMPLOYEE(
SSN,
FNAME,
LNAME,
ADDRESS,
SEX,
SALARY)VALUES
(
‘RNSCSE05’
,
’GI
RISH’
,
’MALYA’
,
’MYSORE’
,
’M’
,450000)
;

I
NSERTI
NTOEMPLOYEE(
SSN,
FNAME,
LNAME,
ADDRESS,
SEX,
SALARY)VALUES
(
‘RNSCSE06’
,
’NEHA’
,
’SN’
,
’BANGALORE’
,
’F’
,800000)
;
I
NSERTI
NTOEMPLOYEE(
SSN,
FNAME,
LNAME,
ADDRESS,
SEX,
SALARY)VALUES
(
‘RNSACC01’
,
’AHANA’
,
’K’
,
’MANGALORE’
,
’F’
,350000)
;
I
NSERTI
NTOEMPLOYEE(
SSN,
FNAME,
LNAME,
ADDRESS,
SEX,
SALARY)VALUES
(
‘RNSACC02’
,
’SANTHOSH’
,
’KUMAR’
,
’MANGALORE’
,
’M’
,300000)
;
I
NSERTI
NTOEMPLOYEE(
SSN,
FNAME,
LNAME,
ADDRESS,
SEX,
SALARY)VALUES
(
‘RNSI
SE01’
,
’VEENA’
,
’M’
,
’MYSORE’
,
’M’
,600000)
;
I
NSERTI
NTOEMPLOYEE(
SSN,
FNAME,
LNAME,
ADDRESS,
SEX,
SALARY)VALUES
(
‘RNSI
T01’
,
’NAGESH’
,
’HR’
,
’BANGALORE’
,
’M’
,500000)
;
I
NSERTI
NTODEPARTMENTVALUES(
‘1’
,
’ACCOUNTS’
,
’01-
JAN-
01’
,
’RNSACC02’
);
I
NSERTI
NTODEPARTMENTVALUES(
‘2’
,
’I
T’,
’
01-
AUG-
16’
,
’RNSI
T01’
);
I
NSERTI
NTODEPARTMENTVALUES(
‘3’
,
’ECE’
,
’01-
JUN-
08’
,
’RNSECE01’
);
I
NSERTI
NTODEPARTMENTVALUES(
‘4’
,
’I
SE’
,
’01-
AUG-
15’
,
’RNSI
SE01’
);
I
NSERTI
NTODEPARTMENTVALUES(
‘5’
,
’CSE’
,
’01-
JUN-
02’
,
’RNSCSE05’
);
Not
e:updat
eent
ri
esofempl
oyeet
abl
etof
il
lmi
ssi
ngf
iel
dsSUPERSSNandDNO
UPDATEEMPLOYEESET
SUPERSSN=NULL,
DNO=’
3’
WHERESSN=’
RNSECE01’
;
UPDATEEMPLOYEESET
SUPERSSN=’
RNSCSE02’
,DNO=’
5’
WHERESSN=’
RNSCSE01’
;
UPDATEEMPLOYEESET
SUPERSSN=’
RNSCSE03’
,DNO=’
5’
WHERESSN=’
RNSCSE02’
;
UPDATEEMPLOYEESET
SUPERSSN=’
RNSCSE04’
,DNO=’
5’
WHERESSN=’
RNSCSE03’
;
UPDATEEMPLOYEESET
DNO=’
5’
,SUPERSSN=’
RNSCSE05’
WHERESSN=’
RNSCSE04’
;DBMSLabManual
-2017
RNSI
T,Bangal
orePage58

UPDATEEMPLOYEESET
DNO=’
5’
,SUPERSSN=’
RNSCSE06’
WHERESSN=’
RNSCSE05’
;

UPDATEEMPLOYEESET
DNO=’
5’
,SUPERSSN=NULL
WHERESSN=’
RNSCSE06’
;

UPDATEEMPLOYEESET
DNO=’
1’
,SUPERSSN=’
RNSACC02’
WHERESSN=’
RNSACC01’
;

UPDATEEMPLOYEESET
DNO=’
1’
,SUPERSSN=NULL
WHERESSN=’
RNSACC02’
;

UPDATEEMPLOYEESET
DNO=’
4’
,SUPERSSN=NULL
WHERESSN=’
RNSI
SE01’
;

UPDATEEMPLOYEESET
DNO=’
2’
,SUPERSSN=NULL
WHERESSN=’
RNSI
T01’
;
I
NSERTI
NTODLOCATI
ONVALUES(
’BANGALORE’
,‘
1’
);
I
NSERTI
NTODLOCATI
ONVALUES(
’BANGALORE’
,‘
2’
);
I
NSERTI
NTODLOCATI
ONVALUES(
’BANGALORE’
,‘
3’
);
I
NSERTI
NTODLOCATI
ONVALUES(
’MANGALORE’
,‘
4’
);
I
NSERTI
NTODLOCATI
ONVALUES(
’MANGALORE’
,‘
5’
);
I
NSERTI
NTOPROJECTVALUES(
100,
’
IOT’
,
’BANGALORE’
,
’5’
);
I
NSERTI
NTOPROJECTVALUES(
101,
’
CLOUD’
,
’BANGALORE’
,
’5’
);
I
NSERTI
NTOPROJECTVALUES(
102,
’
BIGDATA’
,
’BANGALORE’
,
’5’
);
I
NSERTI
NTOPROJECTVALUES(
103,
’
SENSORS’
,
’BANGALORE’
,
’3’
);
I
NSERTI
NTOPROJECTVALUES(
104,
’
BANKMANAGEMENT’
,
’BANGALORE’
,
’1’
);
I
NSERTI
NTOPROJECTVALUES(
105,
’
SALARYMANAGEMENT’
,
’BANGALORE’
,
’1’
);
I
NSERTI
NTOPROJECTVALUES(
106,
’
OPENSTACK’
,
’BANGALORE’
,
’4’
);
I
NSERTI
NTOPROJECTVALUES(
107,
’
SMARTCI
TY’
,
’BANGALORE’
,
’2’
);

I
NSERTI
NTOWORKS_
ONVALUES(
4,‘
RNSCSE01’
,100)
;
I
NSERTI
NTOWORKS_
ONVALUES(
6,‘
RNSCSE01’
,101)
;
I
NSERTI
NTOWORKS_
ONVALUES(
8,‘
RNSCSE01’
,102)
;
I
NSERTI
NTOWORKS_
ONVALUES(
10,
‘RNSCSE02’
,100)
;
I
NSERTI
NTOWORKS_
ONVALUES(
3,‘
RNSCSE04’
,100)
;
I
NSERTI
NTOWORKS_
ONVALUES(
4,‘
RNSCSE05’
,101)
;
I
NSERTI
NTOWORKS_
ONVALUES(
5,‘
RNSCSE06’
,102)
;
I
NSERTI
NTOWORKS_
ONVALUES(
6,‘
RNSCSE03’
,102)
;
I
NSERTI
NTOWORKS_
ONVALUES(
7,‘
RNSECE01’
,103)
;
I
NSERTI
NTOWORKS_
ONVALUES(
5,‘
RNSACC01’
,104)
;
I
NSERTI
NTOWORKS_
ONVALUES(
6,‘
RNSACC02’
,105)
;
I
NSERTI
NTOWORKS_
ONVALUES(
4,‘
RNSI
SE01’
,106)
;
I
NSERTI
NTOWORKS_
ONVALUES(
10,
‘RNSI
T01’
,107)
;
QUERI
ES:
1.Makeal
i
stofal
lpr
ojectnumber
sforpr
oject
sthati
nvol
veanempl
oyeewhosel
ast
namei
s‘Scot
t’
,ei
therasawor
kerorasamanageroft
hedepar
tmentt
hatcont
rol
s
t
hepr
oject
.
(
SELECTDI
STI
NCTP.
PNO
FROM PROJECTP,
DEPARTMENTD,
EMPLOYEEE
WHEREE.
DNO=D.
DNO ANDD.
MGRSSN=E.
SSN ANDE.
LNAME=’
SCOTT’
)
UNI
ON
(
SELECTDI
STI
NCTP1.
PNO
FROM PROJECTP1,
WORKS_
ONW,
EMPLOYEEE1
WHEREP1.
PNO=W.
PNOANDE1.
SSN=W.
SSNANDE1.
LNAME=’
SCOTT’
);

2.Showt
her
esul
ti
ngsal
ari
esi
fev
eryempl
oyeewor
kingont
he‘
I
oT’
proj
ecti
sgi
ven
a10per
centr
aise.
SELECTE.
FNAME,
E.LNAME,
1.1*
E.SALARYASI
NCR_
SAL
FROM EMPLOYEEE,
WORKS_
ONW,
PROJECTP
WHEREE.
SSN=W.
SSN ANDW.
PNO=P.
PNOANDP.
PNAME=’
I
OT’
;

3.Fi
ndt
hesum oft
hesal
ari
esofal
lempl
oyeesoft
he‘
Account
s’depar
tment
,aswel
l
ast
hemaxi
mum sal
ary
,themi
nimum sal
ary
,andt
heav
eragesal
aryi
nthi
s
depar
tment
SELECTSUM (
E.SALARY)
,MAX(
E.SALARY)
,MI
N(E.
SALARY)
,AVG(
E.SALARY)
FROM EMPLOYEEE,
DEPARTMENTD
WHEREE.
DNO=D.
DNOANDD.
DNAME=’
ACCOUNTS’
;

4.Ret
ri
evet
henameofeachempl
oyeewhowor
ksonal
lthepr
oject
sCont
rol
l
edby
depar
tmentnumber5(
useNOTEXI
STSoper
ator
).
SELECTE.
FNAME,
E.LNAME
FROM EMPLOYEEE
WHERENOTEXI
STS(
(SELECTPNO
FROM PROJECT
 WHEREDNO=’
5’
)
MI
NUS
(
SELECTPNO
FROM WORKS_
ON
WHEREE.
SSN=SSN)
);

5.Foreachdepar
tmentt
hathasmor
ethanf
iveempl
oyees,
ret
ri
evet
hedepar
tment
numberandt
henumberofi
tsempl
oyeeswhoar
emaki
ngmor
ethanRs.6,
00,
000.

SELECTE.
DNO,
COUNT(
*)
FROM EMPLOYEEE
WHEREE.
SALARY>600000ANDE.DNOI
N(SELECTE1.
DNO
FROM EMPLOYEEE1
GROUPBYE1.
DNO
HAVI
NGCOUNT(
*)>5)
GROUPBYD.
DNO;

NOTE:
I
FANYMI
STAKES,
CORRECTI
TANDREAD
 WHI
LEWRI
TINGQUERI
ES,
USETHEAPPROPRI
ATE
ATTRI
BUTENAMESCORRESPONDI
NGTOCREATED
TABLES
I
NSERTTHEROWSPROPERLYSOTHATATLEASTONE
ROW I
SOBTAI
NEDASARESULTOFANYQUERY