master of computer applications semester-ii subject … from:[ ] page 1 gujarat technological...
TRANSCRIPT
Download From:[ http://gtu.dizworld.com ] Page 1
Gujarat Technological University
Master of Computer Applications
Semester-II
Subject Name: Software Lab (DBMS: SQL & PL/SQL)
Subject Code: 620006
---------------------------------------------------------------------------------------------------------------------
PL/SQL Practical List:
Question 1:
Competition (Comp_code, Comp_name (Dancing, Painting, GK, etc.))
Create table competition
(
comp_code varchar2(3),
comp_name varchar2(10) NOT NULL,
CONSTRAINT comp_code_pk PRIMARY KEY (comp_code),
CONSTRAINT comp_code_ck CHECK (comp_code LIKE 'C%')
)
insert into competition values('C01','Dancing')
insert into competition values('C02','Painting')
insert into competition values('C03','GK')
insert into competition values('C04','Quiz')
insert into competition values('C05','Drawing')
insert into competition values('C06','Runing')
SQL> select * from competition;
Download From:[ http://gtu.dizworld.com ] Page 2
COM COMP_NAME
--- ----------
C01 Dancing
C02 Painting
C03 G.K.
C04 Quiz
C05 Running
C06 Writing
6 rows selected.
Participants (Part_no, Part_name, DOB, Address, EmailID, Contact_number)
Create table participants_5
(
Part_no varchar2(3),
Part_name varchar2(20),
DOB date,
Address varchar2(30) NOT NULL,
EmailID varchar2(15) NOT NULL,
contact_number number(10) NOT NULL,
CONSTRAINT part_no_pky PRIMARY KEY (Part_no),
CONSTRAINT part_no_chk CHECK (Part_no LIKE 'P%')
);
SQL> select * from participants_5;
PAR PART_NAME DOB ADDRESS EMAILID CONTACT_NUMBER
--- -------------------- --------- ------------------------------ --------------- ---------------------------
P01 hafva 06-JUN-86 asdasdf hgh_2 8989555521
P02 LILABEN 05-JUN-86 DLF RD [email protected] 9654545454
PO3 Kamleshbhai 23-JAN-82 adarsh comp [email protected] 9454444123
Scorecard (Part_no, Comp_code, Judge_no [1, 2, 3], Marks)
Create table scorecard
(
Part_no varchar2(3),
Download From:[ http://gtu.dizworld.com ] Page 3
Comp_code varchar2(3),
Jude_no number(1),
Marks number(5,2),
CONSTRAINT Part_no_fk FOREIGN KEY (Part_no)
REFERENCES Participants(Part_no),
CONSTRAINT Comp_code_fk FOREIGN KEY (Comp_code)
REFERENCES Competition(Comp_code),
CONSTRAINT Jude_no_ck CHECK (Jude_no IN (1,2,3))
)
SQL> select * from scorecard;
PAR COM JUDGE_NO MARKS
--- --- ---------- ----------
P01 C01 1 40
P02 C02 2 45
Implement the following:
A) SQL Queries
1.
Find those participants who have registered both for ‘Dancing’ and ‘Painting’
(Note:Use set operator).
SQL> select part_name from participants_5
2 where part_no
3 IN
4 (select s.part_no from competition c,scorecard s
5 where c.comp_code = s.comp_code
6 and c.comp_name IN ('Dancing','Painting')
7 INTERSECT
8 select p.part_no from participants_5 p,scorecard s
9 where p.part_no = s.part_no)
10 ;
PART_NAME
--------------------
hafva
LILABEN
2.
Download From:[ http://gtu.dizworld.com ] Page 4
Find the average score, scored in each competition event.
select c.comp_code,c.comp_name,avg(s.marks)
from scorecard s,competition c
where c.comp_code = s.comp_code
group by s.comp_code,c.comp_code,c.comp_name
order by c.comp_code;
B) PL/SQL Blocks :
B)
Create a PL/ SQL block to prepare report in following format.
Display the score card in the following format, for the Participant whose
ID/ Name should be provided by the user.
Talent Winner 2011: <Participant’s Name>
Competition name Judge1 Judge2 Judge3
--------------------------------------------------------------------------------------------
1. Painting
2. Dancing
--------------------------------------------------------------------------------------------
Total Marks: _______
--------------------------------------------------------------------------------------------
Declare
cursor c1
is
select c.Comp_code,c.comp_name,p.part_name,s.Judge_no,s.marks
from scorecard s,competition c,participants_5 p
where
s.comp_code=c.comp_code and
s.part_no=p.part_no and
Download From:[ http://gtu.dizworld.com ] Page 5
s.part_no='&Part_no';
var_part c1%Rowtype;
Begin
dbms_output.put_line(' ');
dbms_output.put_line(' ');
dbms_output.put_line(' ');
dbms_output.put_line('Talent Winner 2011 :::'||var_part.part_name);
dbms_output.put_line('---------------------------------------------------------');
dbms_output.put_line('Competition Name Judge1 Judge2 Judge3');
dbms_output.put_line('---------------------------------------------------------');
Open c1;
loop
Fetch c1 into var_part;
Exit when c1%notfound;
if (var_part.judge_no = 1)
then
dbms_output.put_line(var_part.comp_name||' '||var_part.marks);
elsif (var_part.judge_no = 2)
then
dbms_output.put_line(var_part.comp_name||' '||var_part.marks);
elsif (var_part.judge_no = 3)
then
dbms_output.put_line(var_part.comp_name||' '||var_part.marks);
end if;
End Loop;
dbms_output.put_line('------------------------------------------------------------');
dbms_output.put_line(' Total Marks:');
dbms_output.put_line('------------------------------------------------------------');
Close c1;
End;
Question 2:
Customer (Cust_Id, Cust_Name, Cust_Addr, Cust_City, EmailID,Contact_No)
Create table Customer
(
Cust_Id Varchar2(3),
Cust_Name varchar2(20),
Cust_Addr Varchar2(30),
Download From:[ http://gtu.dizworld.com ] Page 6
Cust_City Varchar2(15),
EmailID Varchar2(20) NOT NULL,
Contact_No Number(10),
CONSTRAINTS cust_id_pk PRIMARY KEY (cust_id),
CONSTRAINTS cust_id_ck CHECK (cust_id LIKE 'C%')
);
insert into Customer values (
'C01','Nikunj Patel','Tithal Road Vasad','Valsad','[email protected]', 9974729414)
insert into Customer values (
'C02','Hiren Patel','Tithal Vasad','Valsad','[email protected]', 8876887700)
insert into Customer values (
'C03','Ganesh H Mishra','M.G.Road','Surat','[email protected]', 8933890099)
insert into Customer values (
'C04','Tanvir Rana','Sayajigunj','Baroda','[email protected]', 4455005399)
insert into Customer values ( 'C05','Sachin Tendulkar','Juhu','Bandra Mumbai','[email protected]',
8877893300)
Magazine (Mag_Id, Mag_Name, Unit_Rate, Type_of_subsciption[weekly, monthly, etc.])
create table Magazine
(
Mag_Id Varchar2(3),
Mag_Name Varchar2(15),
Unit_Rate Number(5,2),
Type_of_sub Varchar2(10),
CONSTRAINTS mag_id_pk PRIMARY KEY (mag_id),
CONSTRAINTS mag_id_ck CHECK (Mag_Id LIKE 'M%')
)
insert into Magazine values('M01','Bit Today',200,'weekly')
insert into Magazine values('M02','Byte World',300,'Mothly')
Download From:[ http://gtu.dizworld.com ] Page 7
insert into Magazine values('M03','Zero Bit Flag',500,'Yearly')
insert into Magazine values('M04','Bit Bit Flag',250,'Weekly')
insert into Magazine values('M05','Byte World',350,'Monthly')
Subscription (Cust_Id, Mag_Id, start_date, end_date)
Create table Subscription
(
Cust_Id Varchar2(3),
Mag_Id Varchar2(3),
start_date date,
end_date date,
CONSTRAINT cust_id_fk FOREIGN KEY (Cust_Id)
REFERENCES customer (cust_id),
CONSTRAINT Mag_id_fk FOREIGN KEY (Mag_Id)
REFERENCES Magazine (Mag_id)
)
insert into Subscription values ('C01','M01','23-Mar-2010','23-Jun-2011')
insert into Subscription values ('C01','M02','25-May-2010','23-Jun-2011')
insert into Subscription values ('C02','M01','17-Dec-2011','23-Jun-2012')
insert into Subscription values ('C03','M03','01-Sept-2010','01-Feb-2011')
insert into Subscription values ('C04','M01','01-Sep-2010','01-Feb-2011')
insert into Subscription values('C05','M05','10-Jun-2010','01-Feb-2011')
Implement the following:
A) SQL Queries
1.
Create a View that displays Customer name, Magazine name along with its rate
which was subscribed during 01-Sept-2010 to 01-Feb-2011.
create view Cust_Mag_view
as
select c.Cust_Name,m.Mag_name,m.Unit_Rate from
Subscription s, Magazine m , Customer c
where
c.cust_id=s.cust_id
Download From:[ http://gtu.dizworld.com ] Page 8
and
m.mag_id=s.mag_id
and
Start_date='01-Sep-2010' and end_date='01-Feb-2011'
2
Find top three magazines having the highest sale during last one month of time.
B) PL/SQL Blocks :
1.
Create a function to return No. of customers in city Gandhinagar who have subscribed
the magazine ‘Outlook’ after August 2010. If no such customer exists, throw a user
defined exception with appropriate message.
=========================EXAMPLE===============================
CREATE OR REPLACE FUNCTION F1
(i_emono emp.empno%TYPE)
RETURN varchar2
IS
v_empname varchar2(20);
BEGIN
select ename into v_empname from emp
where empno=&empno;
RETURN v_empname;
END F1;
===========================================================
Create or Replace Function Fun_Cust_detail
(v_mag_name magazine.mag_name%TYPE,
v_cust_City Customer.Cust_city%TYPE)
Return Varchar2
IS
v_cust_id varchar2(3);
Begin
Select c.cust_id into v_cust_id
from Customer c, Magazine m , Subscription s
where
c.cust_id=s.cust_id
and
m.mag_id=s.mag_id
and
Download From:[ http://gtu.dizworld.com ] Page 9
mag_name= v_mag_name
AND
Cust_city IN (v_cust_City)
AND
Start_date < to_date('01-Aug-2010','DD-MM-YYYY');
Return v_cust_id;
End Fun_Cust_detail;
How to Run Function ?
select Fun_Cust_detail('Bit Today','Valsad') from dual
2.
Create a trigger that is fired after an INSERT statement is executed for the Customer table. The trigger writes
the new customer’s code, name and the sysdate in a table called Customer_Log.(create the table
Customer_Log)
create table Customer_log
(
Cust_id varchar2(3),
Cust_name varchar2(20)
)
CREATE or REPLACE TRIGGER Cust_Tigger
AFTER INSERT
ON Customer
FOR EACH ROW
BEGIN
insert into Customer_Log (Cust_Id,Cust_name)
Values
Download From:[ http://gtu.dizworld.com ] Page 10
(:new.Cust_id,
:new.Cust_name
);
Dbms_output.put_line('Record Susscessfully Inserted into Customer_Log');
END;
Question: 3
Account (ac_no, ac_name, act_type)
Create table Account
(
ac_no Varchar2(5),
ac_name Varchar2(15),
act_type Varchar2(1),
CONSTRAINTS ac_no_pk PRIMARY KEY (ac_no),
CONSTRAINTS ac_no_ck CHECK (ac_no LIKE 'A%')
)
insert into Account Values ('A0001','Saving','S');
insert into Account Values ('A0002','Current','C');
insert into Account Values ('A0003','Deposite','D');
insert into Account Values ('A0004','Withdrawal','W');
Transaction (ac_no, trans_date, tran_type, tran_amount, balance)
Create table Transaction
(
ac_no Varchar2(5),
Download From:[ http://gtu.dizworld.com ] Page 11
trans_date date,
tran_type Varchar2(1),
tran_amount Number(10),
balance number(10,4),
CONSTRAINTS ac_no_fk FOREIGN KEY (ac_no)
REFERENCES Account (ac_no)
)
insert into Transaction Values ('A0001','10-Jan-2011','T',60000,10000)
insert into Transaction Values ('A0002','20-Jan-2011','T',70000,30000)
insert into Transaction Values ('A0003','15-Jan-2011','T',70000,30000)
insert into Transaction Values ('A0003','23-Mar-2011','T',70000,30000)
insert into Transaction Values ('A0004','10-Oct-2010','T',34000,2300)
Note:
Act_type may be ‘S’ for saving or ‘C’ for current and tran_type may be ‘D’ for deposit
or ‘W’ for withdrawal.
Implement the following:
A) SQL Queries
1.
Find out those saving transactions that took place between 10th January 2011 and
20th January 2011 and have withdrawn an amount greater than Rs. 50,000.
Select * from Account
where
ac_no
IN
(Select ac_no from
Transaction
where trans_date between '10-Jan-2011' and '20-Jan-2011'
and tran_amount > 50000)
and
Act_Type LIKE 'S'
2.
Create a Sequence that can be used to enter new account number into the account
table. Add a new record into Account table using the created sequence.
Download From:[ http://gtu.dizworld.com ] Page 12
B) PL/SQL Blocks :
1.
Create a trigger not allowing insertion, deletion or updation on Saturday and before
8:00 AM & after 6:00 PM on Account table.
2.
Create a package for the following :
Create a function to return the current balance for a given account number.
Create or Replace Function acc_tran
(v_ac_no account.ac_no%TYPE)
return Number
as
v_balance Transaction.balance%TYPE;
Begin
select sum(balance) into v_balance
from Transaction
where
ac_no=v_ac_no;
return v_balance;
End;
Download From:[ http://gtu.dizworld.com ] Page 13
-----------------------------------------------------------------------------------------------
begin
dbms_output.put_line(acc_tran('A0003'));
end
;
Create or Replace Package acc_trans_balance
As
Function acc_tran
(v_ac_no in account.ac_no%TYPE)
Return Number;
End acc_trans_balance;
Create or Replace Package Body acc_trans_balance
as
Function acc_tran
(v_ac_no account.ac_no%TYPE)
return Number
as
v_balance Transaction.balance%TYPE;
Begin
select sum(balance) into v_balance
from Transaction
where
ac_no=v_ac_no;
return v_balance;
End acc_tran;
End acc_trans_balance;
begin
dbms_output.put_line('Available Balance is='||acc_trans_balance.acc_tran('A0001'));
end;
Download From:[ http://gtu.dizworld.com ] Page 14
Question: 4
Supplier (sid, sname, contactnum)
Create table Supplier
(
sid Varchar2(3),
sname Varchar2(30) Not Null,
contactnum Number(10) Not Null,
CONSTRAINTS sid_pk Primary Key (sid),
CONSTRAINTS sid_ck CHECK (sid LIKE 'S%')
)
Insert into Supplier Values('S01','Nikunj Patel',9974729414);
Insert into Supplier Values('S02','Hiren Patel',8889098878);
Insert into Supplier Values('S03','Brijesh Desai',998390983);
Insert into Supplier Values('S04','Kapil Shah',8700989003);
Insert into Supplier Values('S05','Anuj Desai',9855005508)
Parts (pid, pname, color, unit rate)
Create Table Parts
(
pid Varchar2(3),
pname Varchar2(20) Not Null,
color Varchar2(20),
unit_rate Number(10,4),
Constraints pk_pid Primary key (pid),
Constraints ck_pid CHECK (pid LIKE 'P%')
)
insert into Parts Values('P01','Screw','Red',200);
insert into Parts Values('P02','Battery','Black',3300);
insert into Parts Values('P03','Cap','Green',4300);
insert into Parts Values('P04','Button','White',10);
insert into Parts Values('P05','Key','Black',100);
Download From:[ http://gtu.dizworld.com ] Page 15
Catalog (sid, pid, qty)
Create Table Catalog
(
sid Varchar2(3),
pid Varchar2(3),
qty Number(5),
Constraints fk_sid Foreign Key (sid)
references Supplier(sid),
Constraints fk_pid Foreign Key (pid)
references Parts(pid)
)
insert into catalog values('S01','P01',20);
insert into catalog values('S02','P01',40);
insert into catalog values('S03','P04',50);
insert into catalog values('S04','P03',100);
insert into catalog values('S05','P02',300);
insert into catalog values('S04','P05',20);
Implement the following:
A) SQL Queries:
1.
Find the top three Parts been ordered and have the highest sale till date.
select * from parts
where unit_rate > (
select max(unit_rate) from parts
where
unit_rate < (
select max(unit_rate) from parts
where
unit_rate < (
select max(unit_rate) from parts
where
unit_rate < (
select max(unit_rate) from parts ))))
order by unit_rate desc
Download From:[ http://gtu.dizworld.com ] Page 16
2.
Find those suppliers who charge more for some part that the average cost of that part.
select s.sname,count(*),sum(p.unit_rate) from supplier s, catalog c, parts p
where s.sid=c.sid
and p.pid=c.pid
group by s.sid,s.sname
having
sum(p.unit_rate)>avg(p.unit_rate)
Download From:[ http://gtu.dizworld.com ] Page 17
B)
PL/SQL Blocks :
Create a PL/ SQL block to prepare invoice in following format.
Display the invoice in the following format. Use parameterized cursor.
Invoice ::: <Supplier’s Name>
Part Id Part Name Quantity Unit Price Total Price
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
Total: _______
----------------------------------------------------------------------------------------
Declare
Cursor c1(s_id in Varchar2)
is
select s.sname,p.pid,p.pname,c.qty,p.unit_rate,sum(p.unit_rate)
from parts p, catalog c, supplier s
where
s.sid=c.sid
and
p.pid=c.pid
and
s.sid=s_id
group by s.sname,p.pid,p.pname,c.qty,p.unit_rate;
depvar c1%rowtype;
tot number :=0 ;
Begin
dbms_output.put_line('-----------------------------------REPORT---------------------------');
dbms_output.put_line('=====================================================');
dbms_output.put_line('Part Id '||'Part Name '||'Quantity '||'Unit Price'||'Total Price');
open c1('S04');
loop
fetch c1 into depvar;
exit when c1%notfound;
dbms_output.put_line(depvar.pid||' '||depvar.pname||' '||depvar.qty||'
'||depvar.unit_rate||' '||depvar.unit_rate);
tot := tot + depvar.unit_rate;
end loop;
dbms_output.put_line('-------------------------------------------------------------------');
dbms_output.put_line(' TOTAL='||tot);
close c1;
End;
Download From:[ http://gtu.dizworld.com ] Page 18
Question 5:
Sailor (sid, sname, rating (0-10), DOB)
Create table Sailor1
(
sid Varchar2(3),
sname Varchar2(20) Not null,
rating number(2),
DOB date,
Constraints s_pk Primary Key (sid),
Constraints ck_rate CHECK (rating between 1 and 10),
Constraints ck_sid CHECK (sid LIKE 'S%')
)
insert into Sailor1 Values('S01','Vijay Tandel',7,'23-dec-1980');
insert into Sailor1 Values('S02','Bhavin Patel',8,'11-Mar-1982');
insert into Sailor1 Values('S03','Anuj Desai',5,'3-Apr-1984');
insert into Sailor1 Values('S04','Mihir Mishra',6,'11-Jun-1985');
insert into Sailor1 Values('S05','Ravat Bhavesh',10,'20-Dec-1981');
Boat (bid, bname, color)
Create table Boat1
(
bid Varchar2(3),
bname Varchar2(20) Not Null,
color Varchar2(10),
Constraints pk_bid Primary Key (bid),
Constraints ck_bid Check (bid Like 'B%')
)
insert into Boat1 Values('B01','IND100','White');
insert into Boat1 Values('B02','IND200','Black');
insert into Boat1 Values('B03','IND400','Blue');
insert into Boat1 Values('B04','IND500','Red');
insert into Boat1 Values('B05','IND600','Green');
insert into Boat1 Values('B06','IND777','Green');
Download From:[ http://gtu.dizworld.com ] Page 19
Reserve (sid, bid, rev_date)
Create table Reserve1
(
sid Varchar2(3),
bid Varchar2(3),
rev_date date,
Constraints fk_sid_2 Foreign Key (sid) References Sailor(sid),
Constraints fk_bid_2 Foreign Key (bid) References Boat(bid)
)
insert into Reserve1 values('S01','B01','23-Dec-2011');
insert into Reserve1 values('S02','B02','22-Mar-2011');
insert into Reserve1 values('S03','B03','14-Feb-2011');
insert into Reserve1 values('S04','B04','23-Dec-2011');
insert into Reserve1 values('S05','B05','1-Jan-2011');
Implement the following:
A) SQL Queries:
1.
Find the sailor(s) whose birthday fall in a leap year.
select 'Leap Year' from dual
where 28 < to_char(last_day(to_date('19990201','YYYYMMDD')),'DD')
2.
Find the name of the sailor who has reserved either the red or green colored boat.
select sname from sailor where sid
IN ( select sid from reserve where bid
IN ( select b.bid from boat b, reserve r where r.bid=b.bid
And b.color='Red'
Or b.color='Green' group by b.bid ))
Download From:[ http://gtu.dizworld.com ] Page 20
B) PL/SQL Blocks :
1.
Create a parameterized cursor to display the sailor details who have reserved any
boat after November 2010. If no record found, throw an user defined exception with
appropriate message.
Declare
Cursor c1(s_id in Varchar2)
is
select s.sid,s.sname,r.rev_date from Sailor s,Reserve r
where
s.sid=s_id
and
s.sid=r.sid;
temp_month varchar2(10);
temp_year number(4);
depvar c1%rowtype;
Begin
open c1('S01');
loop
fetch c1 into depvar;
exit when c1%notfound;
dbms_output.put_line(depvar.sid||' '||depvar.sname||' '||depvar.rev_date);
temp_month:=to_char(depvar.rev_date,'MONTH');
temp_year:=to_char(depvar.rev_date,'YYYY');
dbms_output.put_line(temp_month);
dbms_output.put_line(temp_year);
if temp_year=2010 then
dbms_output.put_line(depvar.sname);
end if;
end loop;
close c1;
End;
Download From:[ http://gtu.dizworld.com ] Page 21
2.
Create a function that get the Boat code from the user. Display the sailor_code who
have reserved this boat code. Raise an exception if no information for boat/sailor
exists.
Create or Replace Function f1(boat_id in Varchar2)
Return Varchar2
as
v_sid Sailor.sid%TYPE;
Begin
select s.sid into v_sid from Sailor s,Boat b,Reserve r
where s.sid=r.sid and b.bid=r.bid
and b.bid=boat_id;
Return v_sid;
End f1;
How to execute f1() function ?
begin
dbms_output.put_line(f1('B01'));
end;
Download From:[ http://gtu.dizworld.com ] Page 22
Question 6:
Movie (movie_id, movie_name, date_of_release)
Create Table Movie
(
movie_id Varchar2(3),
movie_name Varchar2(30),
date_of_release date,
Constraints pk_movie_id Primary Key (movie_id),
Constraints ck_movie_id Check ( movie_id LIKE 'M%')
)
insert into Movie Values ('M01','Singham','12-Dec-2011');
insert into Movie Values ('M02','RockStar','11-Nov-2011')
insert into Movie Values ('M03','Ra-One','10-Oct-2011')
insert into Movie Values ('M04','Ra-One','10-Oct-2011')
insert into Movie Values ('M05','Don2','12-Jan-2011')
insert into Movie Values ('M06','Meri Bhai Ki Dulhan','23-Mar-2011')
Screen (screen_id, location, max_capacity)
Create Table Screen
(
screen_id Varchar2(3),
location Varchar2(20),
max_capacity Number(3),
Constraints pk_screen_id Primary Key (screen_id),
Constraints ck_screen_id CHECK (screen_id LIKE 'S%'),
Constraints ck_location CHECK (location IN ('FF','SF','TF')),
Constraints ck_max_capacity CHECK (max_capacity > 0)
);
insert into Screen Values('S01','FF',100);
insert into Screen Values('S02','SF',500);
insert into Screen Values('S03','TF',300);
insert into Screen Values('S04','SF',400);
insert into Screen Values('S05','FF',20);
Download From:[ http://gtu.dizworld.com ] Page 23
Current (movie_id,screen_id, date_of_arrival, date_of_closure)
Note:
Value of screen_id must with letter ‘S’.
Screen location can by any one of ‘FF’, ‘SF’, and ‘TF’.
Date_of_arrival must be less than Date_of_closure.
Max_capacity attribute should have a value greater than 0.
Create Table Current_Details
(
movie_id Varchar2(3),
screen_id Varchar2(3),
date_of_arrival date,
date_of_closure date,
Constraints fk_movie_id Foreign Key (movie_id)
References Movie (movie_id),
Constraints fk_screen_id Foreign Key (screen_id)
References Screen (screen_id),
Constraints ck_date Check ( date_of_closure > date_of_arrival)
)
Insert into Current_Details Values ('M01','S02','20-Jun-2011','30-Aug-2011')
Insert into Current_Details Values ('M02','S04','21-Mar-2011','1-Apr-2011')
Insert into Current_Details Values ('M03','S01','20-Feb-2011','1-Apr-2011')
Insert into Current_Details Values ('M04','S04','21-Feb-2011','2-Apr-2011')
Insert into Current_Details Values ('M05','S02','20-Dec-2011','2-Apr-2012')
insert into current_details values ('M01','S03','20-JUN-11','30-AUG-11');
insert into current_details values ('M01','S04','21-MAR-11','01-APR-11')
insert into current_details values ('M03','S04','20-FEB-11','01-APR-11')
Download From:[ http://gtu.dizworld.com ] Page 24
Implement the following:
A) SQL Queries :
1.
Find the top three movies which have the highest screened record.
2.
Create a View which displays the movie details along with the information about the
screen on which it is currently screened.
Create view view_movie
as
select m.movie_id,m.movie_name,s.screen_id,s.location
from
movie m,screen s,current_details cd
where m.movie_id=cd.movie_id
and s.screen_id=cd.screen_id
and cd.date_of_arrival > sysdate
Download From:[ http://gtu.dizworld.com ] Page 25
B) PL/SQL Blocks:
1.
Create a trigger that is fired after an INSERT statement is executed for the Movie
table. The trigger writes the new movie’s code, movie name and the sysdate in a table
called Movie_Log.(create the table Movie_Log)
create table Movie_Log as Select Movie_id,Movie_name,Date_of_release from Movie where 1=2;
CREATE or REPLACE TRIGGER Movie_Tigger
AFTER INSERT
ON Movie
FOR EACH ROW
BEGIN
insert into Movie_Log (Movie_Id,Movie_name,Date_of_release)
Values
(:new.Movie_id,
:new.Movie_name,
:new.Date_of_release
);
Dbms_output.put_line('Record Susscessfully Inserted into Movie_Log');
END;
SQL> select * from Movie_log;
MOV MOVIE_NAME DATE_OF_R
--- ------------------------------ ---------
M07 TINTIN 23-NOV-11
SQL> insert into movie values('M08','GLADIETOR',sysdate);
Record Susscessfully Inserted into Movie_Log
1 row created.
Download From:[ http://gtu.dizworld.com ] Page 26
2.
Create a function that get the Screen Code from the user and displays the movie name
currently screened on it. If the given screen code does not exist, throw a user defined
exception with appropriate message.
CREATE OR REPLACE FUNCTION fun_f1(p1 in Varchar2)
RETURN Varchar2
is
v1 movie.movie_name%TYPE;
e1 Exception;
BEGIN
Select distinct m.movie_name into v1 from movie m,screen s,current_details cd
where
m.movie_id=cd.movie_id
and
s.screen_id=cd.screen_id
and
m.Date_of_Release >= sysdate
and
s.screen_id=p1;
if v1 IS NULL then
Raise e1;
else
Return v1;
end if;
Exception
WHEN e1 THEN
Raise_application_error(-20001,'No Movie...');
When NO_data_found then
dbms_output.put_line('No Movie');
END fun_f1;
begin
dbms_output.put_line(fun_f1('S01'));
end;
Download From:[ http://gtu.dizworld.com ] Page 27
Question 7:
Department_master(Dept_Code ,Dept_Name)
Create Table Department_master
(
Dept_Code Varchar2(3),
Dept_Name Varchar2(20),
Constraints PK_Dept_Code Primary Key (Dept_Code)
)
insert into Department_master Values('D01','Markating');
insert into Department_master Values('D02','IT');
insert into Department_master Values('D03','Human Resource');
insert into Department_master Values('D04','Inventory');
insert into Department_master Values('D05','Purchase');
Employee_master(EmpCode , Emp_Name , Dept_Id, Emp_Address , DOB , Basic_Salary)
Create table Employee_master
(
EmpCode Varchar2(3),
Emp_Name Varchar2(20),
Dept_Id Varchar2(3) Not Null,
Emp_Address Varchar2(30),
DOB Date,
Basic_Salary Number(10,3),
Constraints PK_EmpCode Primary Key (EmpCode),
Constraints FK_Dept_Id Foreign Key (Dept_Id)
References Department_master(Dept_code)
)
insert into Employee_master Values('E01','Hiren Patel','D01','Tithal Road Surat','10-Dec-1985',20000);
insert into Employee_master Values('E02','Nikunj Desai','D02','Valsad','23-Mar-1984',18000);
insert into Employee_master Values('E03','Kapil Tandel','D03','Vapi','22-Feb-1980',17000);
insert into Employee_master Values('E04','Mihir Mishra','D04','Ahemadabad','1-Jan-1981',15500);
insert into Employee_master Values('E05','Dhrmesh Shah','D05','Banglore','3-Jun-1985',17500);
insert into Employee_master Values('E06','Deven','D04','Banglore','2-Jun-1985',13500);
Download From:[ http://gtu.dizworld.com ] Page 28
Implement the following:
A) SQL Queries:
1.
Create a View that displays some Employee details such as Employee code,
Employee name, Department Name and their Basic Salary.
Create View emp_dept
as
select Empcode,Emp_name, Dept_Code,Dept_Name,Basic_Salary
from
Employee_master e, Department_master d
where
e.Dept_Id=d.Dept_Code
2.
Find those employees who do not belong to Department D102 or D105. (Note: Use
set operator)
select Dept_Id from employee_master
Minus
select Dept_Code from Department_master
where Dept_code='D01' OR Dept_code='D05'
Download From:[ http://gtu.dizworld.com ] Page 29
B) PL/SQL Blocks :
1.
Create a PL/ SQL block to prepare report in the following format:
Display the salary slip for the employee in the following format, whose Employee
Code is provided by the user.
Salary Slip for the month January 2011
Employee Code: <E102> Employee Name: <John Smith>
Department Name: <Finance>
----------------------------------------------------------------------------------------
Basic Salary DA HRA Medical P.F.
___________________________________________________________
Deductions:
----------------------------------------------------------------------------------------
Total Salary : _____________
Note:
HRA is 15% of basic salary
DA is 30% of basic salary
Medical is 1% of basic salary
P.F. is 10% of basic salary
Declare
v_EmpCode Employee_master.EmpCode%TYPE;
v_Emp_Name Employee_master.Emp_Name%TYPE;
v_Dept_Name Department_master.Dept_Name%TYPE;
v_Basic_Salary Employee_master.Basic_Salary%TYPE;
v_DA Number(10);
v_HRA Number(10);
v_Medical Number(10);
v_PF Number(10);
v_Total Number(10);
Download From:[ http://gtu.dizworld.com ] Page 30
Begin
Select e.EmpCode,e.Emp_Name,d.Dept_Name,
e.Basic_Salary,e.Basic_Salary*30/100"DA",
e.Basic_Salary*15/100"HRA",
e.Basic_Salary*1/100"Medical",
e.Basic_Salary*10/100"PF",
e.Basic_Salary+
e.Basic_Salary*30/100+
e.Basic_Salary*15/100+
e.Basic_Salary*1/100+
e.Basic_Salary*10/100"Total"
into v_EmpCode,v_Emp_Name,v_Dept_Name,v_Basic_Salary,
v_DA,v_HRA,v_Medical,v_PF,v_Total
From
Employee_Master e,Department_Master d
Where
e.Dept_Id=d.Dept_Code
AND
e.EmpCode='E01';
dbms_output.put_line('- Salary Slip for the month January 2011.');
dbms_output.put_line('----------------------------------------------------------------');
dbms_output.put_line('Employee Code:'||v_EmpCode||' Employee Name:'||v_Emp_Name);
dbms_output.put_line('----------------------------------------------------------------');
dbms_output.put_line('Department Name:'||v_Dept_Name);
dbms_output.put_line('----------------------------------------------------------------');
dbms_output.put_line('Basic Salary '||'DA '||'HRA '||'Medical '||'P.F. ');
dbms_output.put_line(' '||v_Basic_Salary||' '||v_DA||' '||v_HRA||' '||v_Medical||'
'||v_PF);
dbms_output.put_line('----------------------------------------------------------------');
dbms_output.put_line('Deductions:');
dbms_output.put_line('----------------------------------------------------------------');
dbms_output.put_line('- Total Salary'||v_Total);
dbms_output.put_line('----------------------------------------------------------------');
End;
Question: 10.
Account10(ac_no, ac_name, act_type)
Create table Account10(
Download From:[ http://gtu.dizworld.com ] Page 31
ac_no varchar2(3),
ac_name Varchar2(20),
act_type Varchar2(1),
CONSTRAINTS pk_ac_no Primary key (ac_no),
CONSTRAINTS ck_ac_no CHECK (ac_no LIKE 'A%'),
CONSTRAINTS ck_act_type CHECK (act_type IN ('S','C'))
)
insert into Account10 values('A01','Saving','S');
insert into Account10 values('A02','Current','C');
insert into account10 values('A03','Saving','S');
Transaction10 (ac_no, trans_date, tran_type, tran_amount, balance)
Create Table Transaction10(
ac_no varchar2(3),
trans_date date,
tran_type Varchar2(1),
tran_amount number,
balance number,
CONSTRAINTS fk_ac_no Foreign Key (ac_no)
References Account10(ac_no),
CONSTRAINTS ck_tran_type CHECK (tran_type IN ('D','W')),
CONSTRAINTS ck_tran_amount CHECK (tran_amount > 0),
CONSTRAINTS ck_balance CHECK (balance > 0)
)
insert into Transaction10 values('A01','10-Jan-2011','W',60000,200000);
insert into Transaction10 values('A02','20-Jan-2011','W',80000,550000);
insert into Transaction10 values('A03','15-Jan-2011','W',90000,1000);
Note:
Act_type may be ‘S’ for saving or ‘C’ for current and tran_type may be ‘D’ for
deposit or ‘W’ for withdrawal.
Implement the following:
A) SQL Queries :
1.
Find out those saving transactions that took place between 10th January 2011 and
20th January 2011 and have withdrawn an amount greater than Rs. 50,000.
Download From:[ http://gtu.dizworld.com ] Page 32
Answer:
select * from
Transaction10 t,Account10 a
where
t.trans_date between to_date('10-January-2011','DD-MON-YYYY')
and
to_date('20-January-2011','DD-MON-YYYY')
and
t.tran_type LIKE ('W')
and
t.tran_amount > 50000
and
t.ac_no=a.ac_no
and
a.act_type='S'
2.
Create a View that display the account information having a balance greater than
Rs. 1,00,000.
Answer:
Create View Acc_Tran_View
As
select * from Transaction10
where
BALANCE > 100000
B) PL/SQL Blocks :
select to_char(sysdate,'HH:MI AM') from dual
where
(to_char(sysdate,'HH:MI AM') >= to_char('08:00 AM')
or
to_char(sysdate,'HH:MI AM') <= to_char('06:00 PM'))
AND
Download From:[ http://gtu.dizworld.com ] Page 33
to_char(sysdate,'DAY')=to_char('WEDNESDAY')
---------------------------------------------------------------------------------------------
1.
Create a trigger not allowing insertion, deletion or updation on Saturday and before
8:00 AM & after 6:00 PM on Account table.
Create or Replace Trigger Account_t
BEFORE INSERT
ON
Account10
Declare
v varchar2(10);
Download From:[ http://gtu.dizworld.com ] Page 34
v1 varchar2(10);
v3 varchar2(10);
Cursor c1 is
select to_char(sysdate,'DAY') as
from dual;
Cursor c2 is
select to_char(sysdate,'HH:MI AM') from dual;
Cursor c3 is
select * from account10;
BEGIN
open c1;
fetch c1 into v;
close c1;
open c2;
fetch c2 into v1;
close c2;
if (v1 >= to_char('08:00 AM') AND v1 <= to_char('06:00 PM')) AND
v=to_char('WEDNESDAY') THEN
for v3 in c3 loop
insert into account10(ac_no,ac_name,act_type) values (v3.ac_no,v3.ac_name,v3.act_type);
end loop;
else
DBMS_OUTPUT.PUT_LINE('No');
END IF;
End Account_t;
2.
After every 6 months all the customers are given 5% interest. So for current date, give
interest of 6% to all the customers whose balance are greater than or equal to 2000
and interest of, on their balance.
Question: 17
Item_master(Item_Cd, Item_Name, Item_Price)
Item_received( Item_Cd, Month, Year, Day, Rec_Qty)
Download From:[ http://gtu.dizworld.com ] Page 35
Item_stock(Item_Cd, Month,Year, Open_Stock, Rec_Qty, Close_Stock)
Create table Item_master(
Item_Cd Varchar2(3),
Item_Name Varchar2(20),
Item_Price number,
)
Create table Item_received(
Item_Cd Varchar2(3),
Month Varchar2(10),
Year number,
Day Varchar2(10),
Rec_Qty Number,
)
Create table Item_stock(
Item_Cd Varchar2(3),
Month Varhcar2(10),
Year Number,
Open_Stock Number,
Rec_Qty Number,
Close_Stock Number,
)
Implement the following:
A) SQL Queries :
1.
Create a sequence that can be used to enter new items into item table.
2.
List items whose range lies between Rs.250 and Rs. 500
Download From:[ http://gtu.dizworld.com ] Page 36
B) PL/SQL Blocks :
1.
Write triggers that affect Item_stock table for the insert, update and delete on
Item_received table.
2.
Write a procedure to accept Item Name as input if it exists display the Item Price
otherwise display the proper message through the use of exception.
Question: 18
Team Master (Team_Id, Team_Name)
Player Master (Team_Id, Player_Id, Player_Name, Bt_dt)
Bowler (Team_Id, Bowler_Id, Over, Maiden, Run, Wicket)
Batsman (Team_Id, Player_Id, Score, Out_type, Baller_Id, Bteam_Id)
Extra_run(Team_Id, Wide_Run, No_run, Bye_Run, Legbye_Run)
Team Master (Team_Id, Team_Name)
Download From:[ http://gtu.dizworld.com ] Page 37
Create table Team_Master (
Team_Id Varchar2(3),
Team_Name Varchar2(20),
Constraints pk_team_Master_team_id Primary Key (Team_Id),
Constraints ck_team_Master_team_id Check (Team_Id LIKE 'T%')
);
insert into Team_Master Values('T01','India');
insert into Team_Master Values('T02','Australia');
insert into Team_Master Values('T03','South Africa');
insert into Team_Master Values('T04','New Zeland');
insert into Team_Master Values('T05','Shri Lanka');
insert into Team_Master Values('T06','Kenya');
insert into Team_Master Values('T07','England');
insert into Team_Master Values('T08','Pakistan');
insert into Team_Master Values('T09','Bangladesh');
insert into Team_Master Values('T10','Zimbabwe');
insert into Team_Master Values('T11','Barmuda');
insert into Team_Master Values('T12','Canada');
Player Master (Team_Id, Player_Id, Player_Name, Bt_dt)
Create table Player_Master (
Team_Id Varchar2(3),
Player_Id Varchar2(3),
Player_Name Varchar2(30),
Bt_dt date,
Constraints pk_player_master_team_id Primary Key (Team_Id,Player_Id),
Constraints ck_player_master_team_id Check (Team_Id LIKE 'T%'),
Constraints ck_player_master_player_id Check (Player_Id LIKE 'P%')
Download From:[ http://gtu.dizworld.com ] Page 38
);
insert into Player_Master Values('T01','P01','Sachin Tendlukar','24-APR-1973');
insert into Player_Master Values('T02','P01','Michel Beven','20-Jan-1978');
insert into Player_Master Values('T03','P01','Jack Kallish','10-Feb-1978');
insert into Player_Master Values('T04','P01','Krish Krians','20-Dec-1979');
insert into Player_Master Values('T05','P01','Arvinda De Silva','22-May-1979');
insert into Player_Master Values('T06','P01','Tikolo','12-Sep-1976');
insert into Player_Master Values('T07','P01','Flontoff','14-Oct-1980');
insert into Player_Master Values('T08','P01','Said Anvar','31-Dec-1988');
insert into Player_Master Values('T09','P01','Razzak','11-Jan-1979');
Bowler (Team_Id, Bowler_Id, Over, Maiden, Run, Wicket)
Create table Bowler (
Team_Id Varchar2(3),
Bowler_Id Varchar2(3),
Over Number,
Maiden Number,
Run Number,
Wicket Number,
Constraints pk_bowler_team_id Primary Key (Team_Id,Bowler_Id),
Constraints ck_team_id Check (Team_Id LIKE 'T%'),
Constraints ck_bowler_id Check (Bowler_Id LIKE 'B%')
);
insert into Bowler values('T01','B01',10,2,45,2);
insert into Bowler values('T02','B01',10,4,30,4);
insert into Bowler values('T03','B01',10,2,34,3);
insert into Bowler values('T04','B01',10,5,20,6);
insert into Bowler values('T05','B01',10,3,40,4);
Batsman (Team_Id, Player_Id, Score, Out_type, Baller_Id, Bteam_Id)
Create table Batsman (
Team_Id Varchar2(3),
Player_Id Varchar2(3),
Score Number,
Out_type Varchar2(20),
Baller_Id Varchar2(3),
Bteam_Id Varchar2(3),
Constraints fk_batsman_team_player_id Foreign Key (Team_Id,Player_Id)
Download From:[ http://gtu.dizworld.com ] Page 39
References Player_Master (Team_Id,Player_Id),
Constraints fk_batsman_team_bowler_id Foreign Key (Bteam_Id,Baller_Id)
References Bowler (Team_Id,Bowler_Id),
Constraints ck_bat_team_id Check (Team_Id LIKE 'T%'),
Constraints ck_bat_player_id Check (Player_Id LIKE 'P%'),
Constraints ck_bat_baller_id Check (Baller_Id LIKE 'B%'),
Constraints ck_bat_Bteam_Id Check (Bteam_Id LIKE 'T%')
);
insert into Batsman values('T01','P01',100,'Catch','B01','T02');
insert into Batsman values('T02','P01',99,'Bowld','B01','T03');
insert into Batsman values('T03','P01',45,'Run Out','B01','T04');
insert into Batsman values('T04','P01',140,'LBW','B01','T05');
insert into Batsman values('T05','P01',00,'Stump','B01','T06');
insert into Batsman values('T06','P01',140,'Run Out','B01','T07');
Extra_run(Team_Id, Wide_Run, No_run, Bye_Run, Legbye_Run)
Create table Extra_run(
Team_Id Varchar2(3),
Wide_Run Number,
No_run Number,
Bye_Run Number,
Legbye_Run Number,
Constraints fk_team_extra_run Foreign Key (Team_Id)
References Team_Master (Team_Id),
Constraints ck_extra_run Check (Team_Id LIKE 'T%')
);
insert into Extra_run values('T01',10,20,12,10); insert into Extra_run values('T02',11,22,13,14);
insert into Extra_run values('T03',9,11,12,30);insert into Extra_run values('T04',30,20,11,10);
Implement the following:
A) SQL Queries :
1.
Display the detail of player who has highest score.
2.
Display the age of each player in ‘India’ Team.
B) PL/SQL Blocks :
Download From:[ http://gtu.dizworld.com ] Page 40
Write a procedure to display score board of the given team name in proper format.
Question 21:
Empmaster (Emp_No, Emp_Name, Basic)
Create table Empmaster (
Emp_No Varchar2(3),
Emp_Name Varchar2(20),
Download From:[ http://gtu.dizworld.com ] Page 41
Basic Number,
Constraints pk_emp_no Primary Key (Emp_No),
Constraints ck_emp_no Check (Emp_No LIKE 'E%'),
Constraints ck_Basic Check (Basic > 0)
)
Holidays (Month, Year, No. of Weekly Off, No. of Holidays)
Create table Holidays (
Month Varchar2(3),
Year Number,
No_Weekly_Off Number,
No_Holidays Number
);
EmpTran ( Emp_No, Month, Year, Presence Days, Loan Amount)
Create table EmpTran (
Emp_No Varchar2(3),
Month Varchar2(3),
Year Number,
Presence_Days Number,
Loan_Amount Number,
Constraints fk_emp_no Foreign Key (Emp_No)
References Empmaster (Emp_No)
);
Note:
1. HRA is 20% of basic salary
2. DA is 45% of basic salary
2. Medical is 5% of basic salary
3. P.F. is 4% of basic salary
4. Salary is given for (Attendance + Holidays + weekly off) days
Implement the following:
A) SQL Queries :
Download From:[ http://gtu.dizworld.com ] Page 42
1.
Add a column Emp_Address to the Empmaster table with the not null constraint.
Answer:
Alter table Empmaster add Emp_Address Varchar2(20) NOT NULL
2.
Delete the records of last two years from the current date.
B) PL/SQL Blocks :
An organization want to print the pay slips in following format for given Employee
Name, Month & Year.
Month : Issue Date:
Year : Days in Month:
Employee No: Employee Name:
Download From:[ http://gtu.dizworld.com ] Page 43
Presence : Holidays : Absence :
Salary Days:
Earnings Deductions
======= ========
Basic : P.F. :
Medical : Loan :
H.R.A. : Prof. Tax : 20 Rs.
D.A. :
Total Earning: Total Deduction:
Total Amount to pay: _____________
Question: 22.
Student (Stud_Id, Stud_Name, Address, Date of Birth)
Create table Student (
Stud_Id Varchar2(3),
Stud_Name Varchar2(20),
Address Varchar2(30),
Download From:[ http://gtu.dizworld.com ] Page 44
Date_of_Birth Date,
Constraints pk_Stud_Id Primary Key (Stud_Id),
Constraints ck_Stud_Id Check (Stud_Id LIKE 'S%')
);
insert into Student Values('S01','Nikunj K Patel','Anand Apt Anand','23-Mar-1984');
insert into Student Values('S02','Binti N Patel','Anand Apt Anand','28-Mar-1985');
insert into Student Values('S03','Sachin Tendulkar','Mumbai','10-Dec-1971');
insert into Student Values('S04','Lay G Desai','Ahemadabad','20-Mar-1990');
insert into Student Values('S05','Dhami P Shah','Cikhali Apt Mayur','30-Dec-1991');
Stud_Edu (Stud_Id, Degree Name, Year of Passing, Percentage, Grade)
Create table Stud_Edu (
Stud_Id Varchar2(3),
Degree_Name Varchar2(20),
Year_of_Passing Number,
Percentage Number,
Grade Varchar(2),
Constraints fk_Stud_Id Foreign Key (Stud_Id)
References Student(stud_Id)
);
insert into Stud_Edu Values('S01','MCA',2008,60,'A');
insert into Stud_Edu Values('S02','Msc Biology',2009,84,'AA');
insert into Stud_Edu Values('S03','BA',2007,70,'AB');
insert into Stud_Edu Values('S04','Diploma',2005,80,'A+');
insert into Stud_Edu Values('S05','BE',2009,80,'A+');
Implement the following:
A) SQL Queries :
1.
Display the students whose age is more than 24 years.
Answer:
select * from Student
where
round(round(sysdate-Date_of_Birth)/365) >= 24
Download From:[ http://gtu.dizworld.com ] Page 45
2.
Display the data of top 3 students in MCA , 2010.
B) PL/SQL Blocks :
1.
Write a PL/SQL block to display the detail of students who have done MCA.
2.
Write a procedure to accept stud-id as input and handle user-defined exception
when no data found.
Question: 23.
Weather (City_Id, Name of city, Temperature, Humidity)
Create table Weather (
City_Id Varchar2(3),
Name_of_city Varchar2(20),
Temperature Number,
Download From:[ http://gtu.dizworld.com ] Page 46
Humidity Number,
Constraints pk_City_Id Primary Key (City_Id),
Constraints ck_City_Id Check (City_Id LIKE 'C%')
)
insert into Weather Values('C01','Mehsana',34.56,45);
insert into Weather Values('C02','Ahemadabad',37.57,50);
insert into Weather Values('C03','Mumbai',40.57,35);
insert into Weather Values('C04','Madhubandh',57.57,60);
insert into Weather Values('C05','Tapi',56.57,51);
Implement the following:
A) SQL Queries :
1.
Create a sequence that can be used to enter new city into weather table.
2.
Display the detail of city whose name starts with ‘M’.
Answer:
select * from Weather
where
Name_of_city LIKE ('M%')
B) PL/SQL Blocks :
1.
Write a function which accepts the name of city & returns the Temperature &
Humidify. Also handle an exception if name of city does not exist.
2.
Write a trigger before update on weather for each row if new temperature >
50 then give the message otherwise update the value.
Download From:[ http://gtu.dizworld.com ] Page 47
Question: 24.
Item Master (Item Code, Item Name, Price, Unit)
Create table Item_Master (
Item_Code Varchar2(3),
Item_Name Varchar2(15),
Price Number,
Download From:[ http://gtu.dizworld.com ] Page 48
Unit Number,
Constraints pk_item_code Primary Key (Item_Code),
Constraints ck_item_code Check (Item_Code LIKE 'I%')
);
insert into Item_Master Values('I01','Computer',30000,20);
insert into Item_Master Values('I02','Compact Disk',30,20000);
insert into Item_Master Values('I03','Monitio',6000,30);
insert into Item_Master Values('I04','TV',50000,50);
insert into Item_Master Values('I05','Network Cable',500,100);
Stock (Item Code, Purchased Qty, Sold Qty)
Create table Stock (
Item_Code Varchar2(3),
Purchased_Qty Number,
Sold_Qty Number,
Constraints fk_item_code Foreign Key (Item_Code)
References Item_Master(Item_Code)
);
insert into Stock Values('I01',20,30);
insert into Stock Values('I02',25,31);
insert into Stock Values('I03',55,34);
insert into Stock Values('I04',45,65);
insert into Stock Values('I05',46,75);
Implement the following:
A) SQL Queries :
1.
Update the Price of Item Code “I3” from Rs.500 to Rs.550.
Answer:
update item_master set price=550
where
Download From:[ http://gtu.dizworld.com ] Page 49
item_code='I03'
2.
Display the top 3 costly items.
Answer:
select item_master.price from item_master
where item_master.price >= (
select max(item_master.price) from item_master
where item_master.price <(
select max(item_master.price) from item_master
where item_master.price <(select max(item_master.price) from item_master)))
order by item_master.price desc
B) PL/SQL Blocks :
1.
Write a trigger before update for each row not allowing to update if Sold
Qty > Purchased Qty.
2.
Write a procedure to insert the new record in Item Master table. If Item is already
exist than raise the exception.
Question:25.
Employee_Master25 (Emp_Code, Emp_Name, Birth_Date)
Create table Employee_Master25 (
Emp_Code Varchar2(3),
Emp_Name Varchar2(20),
Birth_Date date,
Constraints pk25_emp_code Primary Key(Emp_Code),
Download From:[ http://gtu.dizworld.com ] Page 50
Constraints ck25_emp_code Check(Emp_Code LIKE 'E%')
);
insert into Employee_Master25 values('E01','Nikunj Patel','23-Mar-1984');
insert into Employee_Master25 values('E02','Smith Desai','21-Dec-1985');
insert into Employee_Master25 values('E03','Steve Jobes','23-Nov-1986');
insert into Employee_Master25 values('E04','Mark Helly','21-Jul-1984');
insert into Employee_Master25 values('E05','Kapil Shah','03-Apr-1984');
Department_Master25 (Dept_Code, Dept_Name, Budget)
Create table Department_Master25 (
Dept_Code Varchar2(3),
Dept_Name Varchar2(3),
Budget Number,
Constraints pk25_dept_code Primary Key(Dept_Code),
Constraints ck25_dept_code Check(Dept_Code LIKE 'D%')
);
insert into Department_Master25 values('D01','IT',100000);
insert into Department_Master25 values('D02','Marketing',330000);
insert into Department_Master25 values('D03','HR',700000);
insert into Department_Master25 values('D04','Purchase',110000);
insert into Department_Master25 values('D05','Sale',3440000);
Salary25 (Dept_Code, Emp_Code, Salary)
Create table Salary25 (
Dept_Code Varchar2(3),
Emp_Code Varchar2(3),
Salary Number,
Constraints fk25_dept_code Foreign Key (Dept_Code)
References Department_Master25(Dept_Code),
Constraints fk25_emp_code Foreign Key (Emp_Code)
Download From:[ http://gtu.dizworld.com ] Page 51
References Employee_Master25(Emp_Code)
);
insert into Salary25 values('D01','E01',2100000);
insert into Salary25 Values('D02','E02',23000);
insert into Salary25 Values('D03','E03',24000);
insert into Salary25 Values('D04','E04',26000);
insert into Salary25 Values('D05','E05',21600);
insert into Salary25 values('D01','E01',2100000);
insert into Salary25 values('D01','E02',2100000);
insert into Salary25 Values('D02','E01',23000);
insert into Salary25 Values('D03','E04',24000);
insert into Salary25 Values('D04','E03',26000);
insert into Salary25 Values('D05','E01',21600);
insert into Salary25 Values('D03','E04',24000);
insert into Salary25 Values('D03','E03',26000);
insert into Salary25 Values('D03','E01',21600);
Implement the following:
A) SQL Queries :
1.
Count the number of employee in each department.
select Dept_code,count(emp_code) from salary25
group by Dept_code
order by
Dept_code
Download From:[ http://gtu.dizworld.com ] Page 52
2.
Create a view to display employee name & its salary.
select s.emp_code,sum(s.salary),e.emp_name from salary25 s,Employee_Master25 e group by
e.emp_code,s.emp_code,e.emp_name
having count(s.emp_code) IN (select count(s.emp_code) from
salary25 s group by s.emp_code)
AND
s.emp_code=e.emp_code
order by s.emp_code
B) PL/SQL Blocks :
1.
Write a trigger before insert new row into salary table for constraint “Total salary for
department is not exceeding the budget.”
2.
Write a function which accept the Employee Name as an argument and return
the salary of that employee. If employee name does not exists than raise the
exception.
Reference Books:
1. “Oracle 9i PL/SQL”, Oracle Press
2. Ivan Bayross, “SQL, PL/SQL – The Programming Language Oracle