1
CS2258 Database Management Systems Lab (SET I) 1. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% &ro#'ct (&ro#!# &ro#esc &r!ce Stoc$) &'rcase (&'r!# &ro!# ty s'"name) Sales (Sale!# &ro!# ty c'stname) a. *"#ate te Stoc$ of all "ro#'cts by 1+.
Query: update product set stock=stock+10; b. D!s"lay "ro#'cts wose stoc$ !s ,15 an# or#er !t by stoc$.
Query: select prodesc from product where stock<15 order by stock; c. D!s"lay l!st of "ro#'ct #eta!ls (&ro#!# &ro#esc &r!ce) s'""l!e# by a "art!c'lar s'""l!er(-/C0).
Query: select prodid,prodesc,price from product where prodid inselect proid from purchase where supname=!"#$!%; #. &ro#'ct !#s an# S'm of 'ant!ty "'rcase#
Query: select sum&ty%,proid from purchase 'roup by proid; e. &ro#'ct #eta!ls (&ro#!# &ro#esc &r!ce) of "ro#'cts w!c are "'rcase# as well as sol#.
Query: select prodid,prodesc,price from product where prodid in select proid from purchase intersect select proid from sales%; f. Create a "roce#'re w!c acce"ts a"ro#!# an# #!s"lays all te sales an# "'rcase recor#s of !t.
Query: $()"*) ( ()-"$) ($)./() dispp. sales2roid3type% 4 6 $/(( emp7cur is 8 elect 9 from sales where roid = p.; 5 emp7rec emp7cur3rowtype; #) ( emp7rec in emp7cur > -
4
? dbms7output2put7lineemp7rec2aleid @@A A@@ emp7rec2roid @@B C@@ emp7rec2&ty @@B C@@ emp7rec2custname%; 10 ). -; 11 ).; 14 D Query: set serEeroutput on Query: )F)$/*) disp4%;
2. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% &ro#'ct (&ro#!# &ro#esc &r!ce Stoc$) &'rcase (&'r!# &ro!# ty) a. ## a col'mn c'stomer name !n &'rcase table.
Query: alter table purchase add columncustname Earchar15%; b. &ro#'ct!#s w!c are not at all "'rcase#. L!st of "ro#'ct #eta!ls (&ro#!# &ro#esc &r!ce) "'rcase# by a "art!c'lar c'stomer. (-MTE0).
Query: select prodid, prodesc, price from product where prodid not in select proid from purchase where custname=!G"*H)I!%; c. Te n'mber of c'stomers wo ma#e te "'rcases
Query: select distinctcustname% from purchase; #. L!st of te "ro#'ct!#s an# total 'ant!ty "'rcase#
Query: select sum&ty% ,proid from purchase 'roup by proid; e. &ro#'ct !#s of te "ro#'cts w!c are "'rcase# more tan 5 t!mes
Query: select countproid%,proid from purchase 'roup by proid haEin' countproid%J4; f. Create a "roce#'re to "r!nt te &ro#!# an#ty wen te &'r!# !s g!3en as te "arameter.
Query: $()"*) ( ()-"$) ($)./() dispp. purchase2roid3type% 4 6 $/(( emp7cur is 8 elect roid, &ty from purchase where roid = p.; 5 emp7rec emp7cur3rowtype;
6
#) ( emp7rec in emp7cur > - ? dbms7output2put7lineemp7rec2proid @@A A@@ emp7rec2&ty%; 10 ). -; 11 ).; 14 D Query: set serEeroutput on Query: )F)$/*) disp4%;
4. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% C'stomer (C'st!# C'stname ge "no) Loan (Loan!# mo'nt C'st!# Em!) a. C'stomer!#s wo a3e not ta$en Loan
Query: select custid from customer where custid not in select custid from loan%; b. Loan #eta!ls (Loan!# mo'nt C'st!# ) of Loan ta$en by a "art!c'lar c'stomer(-670)
Query: select loanid, amount, custid from loan where custid in select custid from customer where custname=!KH!%; c. Te Total n'mber of Loans a3!a!le#
Query: select count9% from loan; #. L!st of te c'stomer!#s an# total Loan amo'nt ta$en
Query: select sumamount%,custid from loan 'roup by custid; e. C'stomer !#s wo a3e ta$en less tan 2 Loans
Query: select custid, countcustid% from loan 'roup by custid haEnin' countcustid%J4; f. Create a "roce#'re to "r!nt te mo'nt an# C'st!# wen te Loan!# !s g!3en as te "arameter.
Query: $()"*) ( ()-"$) ($)./() dispp. -oan2-oanid3type% 4 6 $/(( emp7cur is
8
8 elect custid, amount from -oan where -oanid = p.; 5 emp7rec emp7cur3rowtype; #) ( emp7rec in emp7cur > - ? dbms7output2put7lineemp7rec2custid @@A A@@ emp7rec2amount%; 10 ). -; 11 ).; 14 D Query: set serEeroutput on Query: )F)$/*) disp4%;
. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% C'stomer (C'st!# C'stname ge "no) Loan (Loan!# mo'nt C'st!#) 9Loan (9Loan!# mo'nt C'st!#) a. *"#ate te Loan amo'nt by re#'c!ng 5+++ r'"ees w!c !s a s"ec!al #!sco'nt o:er.
Query: update H-oan set amount=amountL5000; b. 7'mber of 9Loan ta$en by a "art!c'lar c'stomer !#.
Query: select count9% from M-oan 'roup by custid haEin' custid=4; c. C'stomer #eta!ls (C'st!# C'stname ge "no) wo a3e ta$en Loan
Query: select $ustid, $ustname, "'e, phno from customer where custid in select custid from H-oan%; #. L!st of te c'stomer!#s an# total 9Loan amo'nt ta$en
Query: select sumamount%, custid from M-oan 'roup by custid; e. C'stomer #eta!ls (C'st!# C'stname ge "no) wo a3e ta$en bot loan an# 9Loan
5
Query: select $ustid, $ustname, "'e, phno from customer where custid in select custid from H-oan intersect select custid from M-oan%; f. Create a "roce#'re to "r!nt te mo'nt an# Loan!# wen te C'st!# !s g!3en as te "arameter.
Query: $()"*) ( ()-"$) ($)./() dispp. H-oan2custid3type% 4 6 $/(( emp7cur is 8 elect H-oanid, amount from H-oan where custid = p.; 5 emp7rec emp7cur3rowtype; #) ( emp7rec in emp7cur > - ? dbms7output2put7lineemp7rec2H-oanid @@A A@@ emp7rec2amount%; 10 ). -; 11 ).; 14 D Query: set serEeroutput on Query: )F)$/*) disp4%;
5. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% C'stomer (C'st!# C'stname ##r "no"anno) Loan (Loan!# mo'nt InterestC'st!#) cco'nt (cc# ccbal C'st!#) a. ## a col'mn C*SD6/ !n c'stomer table.
Query: alter table customer add columncustdob date%; b. Select te c'stomers wose name en#sw!t ;SI7<= an# or#er by c'st!#
Query: select custname, custid from customer where custname like C3HB order by custid;
c. D!s"lay te c'stomer!#s an# s'm of !s acco'nt balances
Query: select custid, sumaccbal% from account 'roup by custid; #. D!s"lay te acco'nts of c'stomer!#s ;C+1==C+2==C+4=
Query: select 9 from account where custid inC$01B,B$04B,B$06B%; e. Select te c'stomername!# of c'stomers wose n'mber of acco'nts !s greater tan
Query: select custid, custname from customer where custid in select custid from account 'roup by account haEin' countcustid%J5%; f. Create a "roce#'re to "r!nt te Loan #eta!ls wen C'stomer name !s g!3en as te "arameter.
Query: $()"*) ( ()-"$) ($)./() dispp. customer2custname3type% 4 6 $/(( emp7cur is 8 elect 9 from -oan where custid in select custid from customer where custname= p.%; 5 emp7rec emp7cur3rowtype; #) ( emp7rec in emp7cur > - ? dbms7output2put7lineemp7rec2-oanid @@A A@@ emp7rec2amount @@B C@@ emp7rec2interest%; 10 ). -; 11 ).; 14 D Query: set serEeroutput on Query: )F)$/*) disp4%;
>. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% &ro#'ct (&ro#!# &ro#esc &r!ce Stoc$) &'rcase (&'r!# &ro!# ty s'"name)
Sales (Sale!# &ro!# ty c'stname) a. D!s"lay s'"name wen a "art!c'lar"ro#'ct !# !s g!3en (-DL240).
Query: select supname from purchase where proid=!."-46!; b. L!st of names wo are bot s'""l!er as well as c'stomer.
Query: select supname from purchase intersect select custname from sales; c. Create a Tr!gger w!c re#'ces te stoc$ of&ro#'ct tat !s been !nserte# !n sales an# '"#ate te stoc$ wen "'rcase !s ma#e ?'ery%
$reate tri''er t after insert on sales for each row be'in update product set stock=stock+1; end; D #. Create a 3!ew w!c #!s"lays &ro#'ct !#s an# s'm of 'ant!ty !n sales. Query: $reate Eiew E as select sum&ty%, proid from sales 'roup by proid;
@. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% &ro#'ct (&ro#!# &ro#esc &r!ce Stoc$Aeor#) Sales (Sales!# &ro!# ty) a. D!s"lay &ro#esc of "ro#'cts of"art!c'lar Sales(-S240).
Query: select prodesc from product where prodid in select proid from sales where salesid=!"468!%; b. D!s"lay te amo'nt ("r!ce B ty)of &ro#'cts !n eac Sales.
Query: select amount as p2price9s2&ty from product p, sales s where p2prodid=s2proid; c. Create a Tr!gger w!c re#'ces te stoc$ of&ro#'ct tat !s been !nserte# !n sales an# "r!nt !f !t !s o't of stoc$ (stoc$ ,Aeor#) ?'ery% create tr!gger t before !nsert on sales
>
/eg!n *"#ate "ro#'ct set stoc$stoc$ 1+ En# F #. Create a 3!ew w!c #!s"lays &ro!# &ro#esc an# s'm of 'ant!ty !n sales.
Query: create Eiew E as select sum&ty%,proid from sales 'roup by proid;
8. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% C'stomer (C'st!# C'stname ge "no) Loan (Loan!# mo'nt C'st!# Em!) a. D!s"lay te total Em! tat nee# to be"a!# by c'stomer w!t a "art!c'lar C'stname(-LEE70)
Query: select custid, custname from customer where custname=!-))"! and custid in select sumemi%,custid from loan 'roup by custid%; b. ## a col'mn nol an# '"#ate te col'mn3al'es w!t s'm of Loans ta$en by !m
Query: alter table customer add columnnol number5%%; c. Create a Tr!gger w!c '"#ates te nol 3al'e on !nsert!on an# #elet!on of Loan for a c'stomer.
Query: $reate tri''er u after insert on loan or each row #e'in /pdate customer set nol=nol+500; )nd; D #. Create a 9!ew w!t C'stname an# s'm of Loan mo'nt
Query: create Eiew w as select sumamount%,custid from loan 'roup by custid;
G. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts%
?
C'stomer (C'st!# C'stname ge "no) Loan (Loan!# mo'nt C'st!#) 9Loan (9Loan!# mo'nt C'st!#)
a. D!s"lay C'stname name w!t bot Loan an# 9Loan
Query: select custname from customer where custid in select custid from H-oan intersect select custid from M-oan%; b. L!st of te c'stomer!#s an# total 9Loan amo'nt ta$en
Query: select sumamount% from M-oan 'roup by custid; c. Create a Tr!gger w!c #!s"lays te Loan #eta!ls wen 3al'es !n Loan!s !nserte# #o te same for 9Loan also.
Query: $reate tri''er t after insert on H-oan referencin' new row asnrow or each row #e'in dbms7output2put7linenrow2H-oanid @@A A@@ nrow2amount @@B C@@ nrow2custid%; end; D #. Create a 3!ew w!t C'st!# an# C'stname w!t s'm of Loan mo'nt(bot Loan an# 9Loan)
Query: create Eiew s as select sumamount% from H-oan 'roup by custid haEin' custid=select custid from H-oan intersect select custid from M-oan%;
1+. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% C'stomer (C'st!# C'stname ##r "no"anno) Loan (Loan!# mo'nt Interest C'st!#) cco'nt (cc# ccbal C'st!#) a. D!s"lay te s'm of ccbal of a &art!c'lar C'stname(-LEE70)
Query: select custid, custname from customer where custname=!-))"! and custid in select sumaccbal% from account 'roup by custid%;
10
b. *"#ate te !nterest w!t 1H wen ccbal of te C'st!# 5+H of Loan mo'nt
Query: update account set accbal=accbal+accbal 9 0201% where accbal J select amount 9 025% from loan%;
c. Create a Tr!gger w!c cec$s weter te ccbal !s 25H of amo'nt tobe !nserte# !n Loan
Query: $reate tri''er f after insert on loan #e'in dbms7output2put7lineaccount2accbal%; end; D #. Create a 9!ew w!t ccbal an# Loan mo'nt of all C'tomers
Query: create Eiew d as select l2amount, a2accbal from customer c, account a, loan l where c2custid=a2custid and c2custid=l2custid;
11. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% C'stomer (C'st!# C'stname ##r "no"anno) Loan (Loan!# mo'nt Interest C'st!#) cco'nt (cc# ccbal C'st!#) a. D!s"lay te C'stname a3!ng bot Loan an# cco'nt
Query: select custname from customer where custid in select custid from loan intersect select custid from account%; b. Create an 'ser #eJne# f'nct!on -get/al0 w!c ret'rns te S'm of ccbal of a "art!c'lar C'st!# "asse# as "arameter
Q-J$()"*) ( ()-"$) /$* em7dtl7func ()*/( account2accbal3type acc7bal account2accbal3type;
11
#) )-)$* sumaccbal% * acc7bal (G account IH)() custid = 4; ()*/( acc7bal; ).; D Q-J select em7dtl7func from dual; c. Create a "roce#'re w!c "r!nts te C'stomer #eta!ls wose s'm of ccbal 1++++ 's!ng -get/al0 f'nct!on create# by 'ser abo3e (!n b)
Query: $()"*) ( ()-"$) ($)./() disp $/(( emp7cur is elect sumaccbal%, custid from account 'roup by custid haEin' sumaccbal%J10000; emp7rec emp7cur3rowtype; #) ( emp7rec in emp7cur - dbms7output2put7lineemp7rec2custid%; ). -; ).; D Query: set serEeroutput on Query: )F)$/*) disp4%;
12. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% C'stomer (C'st!# C'stname ##r "no"anno) Loan (Loan!# mo'nt Interest C'st!# &r!nc!"al&a!#) cco'nt (cc# ccbal C'st!#) a. D!s"lay te C'stname #oesn=t ol#any cco'nt nor ta$en any Loan
Query: select custname from customer where custid not in select custid from loan union custid from account%;
14
b. Create an 'ser #eJne# f'nct!on -get/alance0 w!c ret'rns te /alance &r!nc!"al Loan mo'nt &en#!ng for a &art!c'lar C'st!# "asse# as "arameter
Query: "nswer 11b% c. Create a "roce#'re w!c "r!nts te C'stomer #eta!ls wose a3e /alance &r!nc!"al Loan mo'nt &en#!ng + 's!ng -get/alance0 f'nct!on create# by 'ser abo3e (!n b) Query: "nswer 11c%
14. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% C'stomer (C'st!# C'stname ##r "no"anno) Loan (Loan!# mo'nt Interest C'st!# LoanDate) a. D!s"lay n'mber of Loans te s'm of Loan mo'nt of a &art!c'lar C'stname(-LEE70)
Query: select custid, custname from customer where custname=!-))"! and custid in select sumamount% loan 'roup by custid%; b. Create a Ae"ort ('s!ng Crystal Ae"ortsFny re"ort generat!on tool) w!c #!s"lays Loan Ta$en between Krom an# To Dates
1. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% &ro#'ct (&ro#!# &ro#esc &r!ce Stoc$ Aeor#) Sales (Sales!# &ro!# ty) a. D!s"lay te total amo'nt ("r!ce B ty) of Sales ma#e so far
Query: select totalamount as p2price9s2&ty from product p, sales s where p2prodid=s2proid; b. Create a Ae"ort ('s!ng Crystal Ae"ortsF ny re"ort generat!on tool) wen
15. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% C'stomer (C'st!# C'stname ##r "no"anno)
16
Loan (Loan!# mo'nt Interest C'st!# LoanDate) a. D!s"lay n'mber of Total Loan mo'nt ta$en on LoanDates an# or#er by Loan Date ten by Loan mo'nt
Query: select sumamount% from loan 'roup by -oan.ate order by -oan.ate,amount; b. Create a Korm to ma$e a Loan entry ('s!ng 9/F 9CFa3a). c. Loan can be entere# only for a3a!lable c'stomer so #!s"lay a3a!lable C'st!# !n te Korm to Coose one among tem for Loan Entry
roNect for b O c 1>. Create Tables as follows by coos!ng a""ro"r!ate #ata ty"e an# set te necessary "r!mary an# fore!gn $ey constra!nts% &ro#'ct (&ro#!# &ro#esc &r!ce Stoc$ Aeor#) Sales (Sales!# &ro!# ty) a. Do'ble te Aeor# 3al'e of &ro#'cts w!t &r!ce 5++ an# Aeor# ,5+
Query: update product set reord=reord94 where priceJ500 and reord<50; b. Create a Korm to ma$e a Sale entry ('s!ng 9/F 9CFa3a). c. Sale can be entere# only for a3a!lable "ro#'cts so #!s"lay a3a!lable &ro#!# !n te Korm to Coose one among tem for Sale Entry
roNect for b O c
