DBMS

for more pl/sql program visit "http://avmathews.tripod.com/id18.html".....

PL/SQL(all programs)

Practical No. # 4

Q1: Write a PL/SQL code block to find sum and average of three numbers.

Solution:

declare

a number:=&a;

b number:=&b;

c number:=&c;

sm number;

av number;

begin

sm:=a+b+c;

av:=sm/3;

dbms_output.put_line('Sum = '||sm);

dbms_output.put_line('Average = '||av);

end;

Output:

Enter value for a: 12

old 2: a number:=&a;

new 2: a number:=12;

Enter value for b: 4

old 3: b number:=&b;

new 3: b number:=4;

Enter value for c: 21

old 4: c number:=&c;

new 4: c number:=21;

Sum = 37

Average = 12.33333333333333333333333333333333333333

PL/SQL procedure successfully completed.

Q2: Write a PL/SQL code block to find Simple Interest.

Solution:

declare

p number(9,2);

r number(9,2);

t number(9,2);

si number(9,2);

begin

p:=&p;

r:=&r;

t:=&t;

si:=(p*r*t)/100;

dbms_output.put_line('Simple Interest = '||si);

end;

Output:

Enter value for p: 150

old 7: p:=&p;

new 7: p:=150;

Enter value for r: 4

old 8: r:=&r;

new 8: r:=4;

Enter value for t: 3

old 9: t:=&t;

new 9: t:=3;

Simple Interest = 18

PL/SQL procedure successfully completed.

Q3: Write a PL/SQL code block to find area of circles with radius greater than 3 and less than equal to 7 and store the result in a table with attributes radius and area.

Solution:

declare

area number(5,2);

radius number(1):=3;

pi constant number(3,2):=3.14;

begin

while radius<=7

loop

area:=pi*radius*radius;

insert into areas values (radius,area);

radius:=radius+1;

end loop;

end;

Output:

PL/SQL procedure successfully completed.

SQL> select * from areas;

RADIUS AREA

---------- ----------

3 28.26

4 50.24

5 78.5

6 113.04

7 153.86

Q4: Write a PL/SQL code block to find factorial of a number.

Solution:

declare

n number;

i number;

f number:=1;

begin

n:=&n;

for i in 1..n

loop

f:=f*i;

end loop;

dbms_output.put_line(n||'! = '||f);

end;

Output:

Enter value for n: 5

old 6: n:=&n;

new 6: n:=5;

5! = 120

PL/SQL procedure successfully completed.

Q5: Write a PL/SQL code block to find reverse of a number.

Solution:

declare

N number;

S NUMBER := 0;

R NUMBER;

K number;

begin

N := &N;

K := N;

loop

exit WHEN N = 0;

S := S * 10;

R := MOD(N,10);

S := S + R;

N := TRUNC(N/10);

end loop;

dbms_output.put_line('THE REVERSED DIGITS OF '||K||' = '||S);

end;

Output:

Enter value for n: 4567

old 7: N := &N;

new 7: N := 4567;

THE REVERSED DIGITS OF 4567 = 7654

PL/SQL procedure successfully completed.

Q6: Write a PL/SQL code block to find greatest of three numbers.

Solution:

declare

a number := &a;

b number := &b;

c number := &c;

begin

if a>b and a>c then

dbms_output.put_line(a||' is greatest.');

elsif b>a and b>c then

dbms_output.put_line(b||' is greatest.');

else

dbms_output.put_line(c||' is greatest.');

end if;

end;

Output:

Enter value for a: 6

old 2: a number := &a;

new 2: a number := 6;

Enter value for b: 3

old 3: b number := &b;

new 3: b number := 3;

Enter value for c: 4

old 4: c number := &c;

new 4: c number := 4;

6 is greatest.

PL/SQL procedure successfully completed.

Q7: Write a PL/SQL code block to generate Fibonacci series.

Solution:

declare

a number:= 0 ;

b number:= 1;

c number;

begin

dbms_output.put(a||' '||b||' ');

for i in 3..10 loop

c := a + b;

dbms_output.put(c||' ');

a := b;

b := c;

end loop;

dbms_output.put_line(' ');

end;

Output:

0 1 1 2 3 5 8 13 21 34

PL/SQL procedure successfully completed.

Q8: Write a PL/SQL code block to find sum of digits of a number.

Solution:

declare

N number ;

S number:=0;

R number;

begin

N:=&N;

WHILE N<>0 LOOP

R := MOD(N,10);

S := S + R;

N := TRUNC(N/10);

end loop;

dbms_output.put_line('THE SUM OF THE DIGITS = '||S);

end;

Output:

Enter value for n: 535

old 6: N:=&N;

new 6: N:=535;

THE SUM OF THE DIGITS = 13

PL/SQL procedure successfully completed.

Salesman Master

Practical No. # 3

Create the Salesman_Master table described below:

Column Name	Datatype	Size	Constraints
Salesman_No	Char	6	Primary Key
SalesmanName	Varchar2	20	Not Null
Address1	Varchar2	30	Not Null
Address2	Varchar2	30
City	Varchar2	20
PinCode	Number	6
State	Varchar2	20
SalAmt	Number	8,2	Not Null
TgtToGet	Number	6,2	Not Null
YtdSales	Number	6,2	Not Null
Remarks	Varchar2	60

Solution:

CREATE TABLE SALESMAN_MASTER(

SALESMAN_NO CHAR(6) PRIMARY KEY,

SALESMANNAME VARCHAR2(20) NOT NULL,

ADDRESS1 VARCHAR2(30) NOT NULL,

ADDRESS2 VARCHAR2(30),

CITY VARCHAR2(20),

PINCODE NUMBER(6),

STATE VARCHAR2(20),

SALAMT NUMBER(8,2) NOT NULL,

TGTTOGET NUMBER(6,2) NOT NULL,

YTDSALES NUMBER(6,2) NOT NULL,

REMARKS VARCHAR2(60));

Insert the following data into the table:

SALESMAN_NO	SALESMANNAME	ADDRESS1	ADDRESS2	CITY	PINCODE	STATE	SALAMT	TGTTOGET	YTDSALES	REMARKS
S00001	Aman	A/14	Worli	Mumbai	400002	Maharashtra	3000	100	50	Good
S00002	Omkar	65	Nariman	Mumbai	400001	Maharashtra	3000	200	100	Good
S00003	Raj	P-7	Bandra	Mumbai	400032	Maharashtra	3000	200	100	Good
S00004	Ashish	A/5	Juhu	Mumbai	400044	Maharashtra	3500	200	150	Good

Create the Sales_Order table described below:

Column Name	Datatype	Size	Default	Constraints
Order_No	Char	6		Primary Key
Client_No	Char	6		Foreign Key References Client_No of Client_Master Table
OrderDate	Date			Not Null
Salesman_No	Char	6		Foreign Key References Salesman_No of Salesman_Master Table
DelivType	Char	1	F	Delivery: part(P)/full(F)
BillYN	Char	1
DelivDate	Date			Cannot be less than OrderDate
OrderStatus	Varchar2	10		Values(‘In process’, ‘Fulfilled’, ‘Backorder’, ‘Cancelled’)

Solution:

CREATE TABLE SALES_ORDER(

ORDER_NO CHAR(6) PRIMARY KEY,

CLIENT_NO CHAR(6) REFERENCES CLIENT_MASTER,

ORDERDATE DATE,

SALESMAN_NO CHAR(6) REFERENCES SALESMAN_MASTER,

DELIVTYPE CHAR(1) DEFAULT ‘F’,

BILLYN CHAR(1),

DELIVDATE DATE,

ORDERSTATUS VARCHAR2(10),

CONSTRAINT CK_DELIVTYPE CHECK(DELIVTYPE IN(‘P’,’F’)),

CONSTRAINT CK_DELIVDATE CHECK(DELIVDATE > ORDERDATE),

CONSTRAINT CK_ORDERSTATUS CHECK(ORDERSTATUS IN(‘In Process’,‘Fulfilled’,‘Backorder’,‘Cancelled’)));

Insert the following data into the table:

ORDER_NO	CLIENT_NO	ORDERDATE	SALESMAN_NO	DELIVTYPE	BILLYN	DELIVDATE	ORDERSTATUS
O19001	C00001	12-JUN-02	S00001	F	N	20-JUL-02	In Process
O19002	C00002	02-JUN-02	S00002	P	N	27-JUN-02	Cancelled
O19003	C00003	18-FEB-02	S00003	F	Y	20-FEB-02	Fulfilled
O19004	C00004	03-APR-02	S00001	F	Y	07-APR-02	Fulfilled
O19005	C00005	20-MAY-02	S00002	P	N	22-MAY-02	Cancelled
O19006	C00006	24-MAY-02	S00004	F	N	26-JUL-02	In Process

Create the Sales_Order_Details table described below:

Column Name	Datatype	Size	Constraints
Order_No	Char	6	Foreign Key References Order_No of Sales_Order Table
Product_No	Char	6	Foreign Key References Product_No of Product_Master Table
QtyOrdered	Number	8
QtyDisp	Number	8
ProductRate	Number	10,2

Solution:

CREATE TABLE SALES_ORDER_DETAILS(

ORDER_NO CHAR(6) REFERENCES SALES_ORDER,

PRODUCT_NO CHAR(6) REFERENCES PRODUCT_MASTER,

QTYORDERED NUMBER(8),

QTYDISP NUMBER(8),

PRODUCTRATE NUMBER(10,2));

Insert the following data into the table:

ORDER_NO	PRODUCT_NO	QTYORDERED	QTYDISP	PRODUCTRATE
O19001	P00001	4	4	525
O19001	P00006	2	1	5250
O19002	P00001	10	0	525
O19003	P00005	3	3	3150
O19003	P00006	3	1	5250
O19003	P00001	10	10	525
O19003	P00002	4	4	1050
O19004	P00002	2	2	1050
O19004	P00003	1	1	12000
O19006	P00001	10	5	525

Perform the following queries on these tables:

Q1: List all the information from the Sales_Order table for orders placed in the month of June.

Solution:

SELECT * FROM SALES_ORDER WHERE TO_CHAR(ORDERDATE,‘MON’) = ‘JUN’;

Output:

ORDER_NO	CLIENT_NO	ORDERDATE	SALESMAN_NO	DELIVTYPE	BILLYN	DELIVDATE	ORDERSTATUS
O19001	C00001	12-JUN-02	S00001	F	N	20-JUL-02	In Process
O19002	C00002	02-JUN-02	S00002	P	N	27-JUN-02	Cancelled

Q2: Count the total number of orders.

Solution:

SELECT COUNT(*) “TOTAL ORDERS” FROM SALES_ORDER;

Output:

TOTAL ORDERS

6

Q3: List the order number and day on which clients placed their order.

Solution:

SELECT ORDER_NO, TO_CHAR(ORDERDATE,‘DAY’) “ORDER DAY” FROM SALES_ORDER;

Output:

ORDER_NO	ORDER DAY
O19001	WEDNESDAY
O19002	SUNDAY
O19003	MONDAY
O19004	WEDNESDAY
O19005	MONDAY
O19006	FRIDAY

Q4: List the month (in alphabets) and date when the orders must be delivered.

Solution:

SELECT ORDER_NO, TO_CHAR(DELIVDATE,‘MONTH’) “MONTH”, DELIVDATE FROM SALES_ORDER;

Output:

ORDER_NO	MONTH	DELIVDATE
O19001	JULY	20-JUL-02
O19002	JUNE	27-JUN-02
O19003	FEBRUARY	20-FEB-02
O19004	APRIL	07-APR-02
O19005	MAY	22-MAY-02
O19006	JULY	26-JUL-02

Q5: List the order date in the format ‘DD-MONTH-YY’.

Solution:

SELECT TO_CHAR(ORDERDATE,‘DD-MONTH-YY’) “ORDERDATE” FROM SALES_ORDER;

Output:

ORDERDATE

12-JUNE -02

02-JUNE -02

18-FEBRUARY -02

03-APRIL -02

20-MAY -02

24-MAY -02

Q6: List the date, 15 days after today’s date.

Solution:

SELECT SYSDATE + 15 FROM DUAL;

Output:

SYSDATE+15

07-SEP-09

Q7: Print the description and total qty sold for each product.

Solution:

SELECT DESCRIPTION, SUM(QTYDISP) FROM PRODUCT_MASTER PM, SALES_ORDER_DETAILS SOD WHERE PM.PRODUCT_NO = SOD.PRODUCT_NO GROUP BY DESCRIPTION;

Output:

DESCRIPTION	SUM(QTYDISP)
1.44 Floppies	19
540 HDD	2
Mouse	1
Keyboard	3
Monitors	6

Q8: Find the value of each product sold.

Solution:

SELECT SOD.PRODUCT_NO, PM.DESCRIPTION, SUM(SOD.QTYDISP * SOD.PRODUCTRATE) SALESPERPRODUCT FROM SALES_ORDER_DETAILS SOD, PRODUCT_MASTER PM WHERE PM.PRODUCT_NO = SOD.PRODUCT_NO GROUP BY PM.PRODUCT_NO, SOD.DESCRIPTION;

Output:

PRODUCT_NO	DESCRIPTION	SALESPERPRODUCT
P00002	Monitors	6300
P00001	1.44 Floppies	9975
P00003	Mouse	12000
P00005	Keyboard	9450
P00006	540 HDD	10500

Q9: Calculate the average quantity sold for each client that has a maximum order value of 15000.00.

Solution:

SELECT CM.CLIENT_NO, AVG(SOD.QTYDISP) AVGSALES FROM SALES_ORDER_DETAILS SOD, CLIENT_MASTER CM WHERE CM.CLIEN_NO = SO.CLIENT_NO AND SO.ORDER_NO = SOD.ORDER_NO GROUP BY CM.CLIENT_NO HAVING MAX(SOD.QTYORDERED * SOD.PRODUCTRATE) > 15000;

Output:

CLIENT_NO	AVGSALES
C00003	4.5

Q10: Find out the total of all the billed orders for the month of June.

Solution:

SELECT SO.ORDER_NO, SO.ORDERDATE, SUM(SOD.QTYORDERED * SOD.PRODUCTRATE) ORDERBILLED FROM SALES_ORDER SO, SALES_ORDER_DETAILS SOD WHERE SOD.ORDER_NO = SO.ORDER_NO AND SO.BILLYN = ‘Y’ AND TO_CHAR(ORDERDATE,‘MON’) = ‘JUN’ GROUP BY SO.ORDER_NO, SO.ORDERDATE;

Output:

no rows selected

Q11: Find out the products, which have been sold to ‘Ivan Bayross’.

Solution:

SELECT SOD.PRODUCT_NO, PM.DESCRIPTION FROM SALES_ORDER_DETAILS SOD, PRODUCT_MASTER PM, SALES_ORDER SO, CLIENT_MASTER CM WHERE PM.PRODUCT_NO = SOD.PRODUCT_NO AND SO.ORDER_NO = SOD.ORDER_NO AND CM.CLIENT_NO = SO.CLIENT_NO AND CM.NAME = ‘Ivan Bayross’;

Output:

PRODUCT_NO	DESCRIPTION
P00001	1.44 Floppies
P00006	540 HDD

Q12: Find out the products and their quantities that will have to be delivered in the current month.

Solution:

SELECT SOD.PRODUCT_NO, PM.DESCRIPTION, SUM(SOD.QTYORDERED) FROM SALES_ORDER_DETAILS SOD, SALES_ORDER SO, PRODUCT_MASTER PM WHERE PM.PRODUCT_NO = SOD.PRODUCT_NO AND SO.ORDER_NO = SOD.ORDER_NO AND TO_CHAR(DELIVDATE, 'MON-YY') = TO_CHAR(SYSDATE,'MON-YY') GROUP BY SOD.PRODUCT_NO, PM.DESCRIPTION;

Output:

no rows selected

Q13: List the product number and description of constantly sold (i.e. rapidly moving) products.

Solution:

SELECT DISTINCT PRODUCT_MASTER.PRODUCT_NO, DESCRIPTION FROM SALES_ORDER_DETAILS, PRODUCT_MASTER WHERE PRODUCT_MASTER.PRODUCT_NO = SALES_ORDER_DETAILS.PRODUCT_NO;

Output:

PRODUCT_NO	DESCRIPTION
P00002	Monitors
P00001	1.44 Floppies
P00003	Mouse
P00005	Keyboard
P00006	540 HDD

Q14: Find the names of clients who have purchased ‘1.22 floppies’.

Solution:

SELECT DISTINCT SO.CLIENT_NO, CM.NAME FROM SALES_ORDER_DETAILS SOD, SALES_ORDER SO, PRODUCT_MASTER PM, CLIENT_MASTER CM WHERE PM.PRODUCT_NO = SOD.PRODUCT_NO AND SO.ORDER_NO = SOD.ORDER_NO AND CM.CLIENT_NO = SO.CLIENT_NO AND DESCRIPTION = '1.22 Floppies';

Output:

no rows selected

Q15: List the products and orders from customers who have ordered less than 5 units of ‘keyboards’.

Solution:

SELECT SOD.PRODUCT_NO, SOD.ORDER_NO FROM SALES_ORDER_DETAILS SOD, SALES_ORDER SO, PRODUCT_MASTER PM WHERE SO.ORDER_NO = SOD.ORDER_NO AND PM.PRODUCT_NO = SOD.PRODUCT_NO AND SOD.QTYORDERED < description =" ‘Keyboard’;

Output:

PRODUCT_NO	ORDER_NO
P00005	O19003

Q16: Find the products and their quantities for the orders placed by ‘Ivan Bayross’ and ‘Mamta Muzumdar’.

Solution:

SELECT SOD.PRODUCT_NO, PM.DESCRIPTION, SUM(QTYORDERED) UNITSORDERED FROM SALES_ORDER_DETAILS SOD, SALES_ORDER SO, PRODUCT_MASTER PM, CLIENT_MASTER CM WHERE SO.ORDER_NO = SOD.ORDER_NO AND PM.PRODUCT_NO = SOD.PRODUCT_NO AND CM.CLIENT_NO = SO.CLIENT_NO AND (CM.NAME = 'Ivan Bayross' OR CM.NAME = 'Mamta Muzumdar') GROUP BY SOD.PRODUCT_NO, PM.DESCRIPTION;

Output:

PRODUCT_NO	DESCRIPTION	UNITSORDERED
P00001	1.44 Floppies	4
P00006	540 HDD	2

Q17: Find the products and their quantities for the orders placed by Client_No ‘C00001’ and ‘C00002’.

Solution:

SELECT SO.CLIENT_NO, SOD.PRODUCT_NO, PM.DESCRIPTION, SUM(QTYORDERED) UNITSORDERED FROM SALES_ORDER SO, SALES_ORDER_DETAILS SOD, PRODUCT_MASTER PM, CLIENT_MASTER CM WHERE SO.ORDER_NO = SOD.ORDER_NO AND SOD.PRODUCT_NO = PM.PRODUCT_NO AND SO.CLIENT_NO = CM.CLIENT_NO GROUP BY SO.CLIENT_NO, SOD.PRODUCT_NO, PM.DESCRIPTION HAVING SO.CLIENT_NO = 'C00001' OR SO.CLIENT_NO = 'C00002';

Output:

CLIENT_NO	PRODUCT_NO	DESCRIPTION	UNITSORDERED
C00002	P00001	1.44 Floppies	10
C00001	P00006	540 HDD	2
C00001	P00001	1.44 Floppies	4

Q18: Find the Product_No and Description of non-moving products.

Solution:

SELECT PRODUCT_NO, DESCRIPTION FROM PRODUCT_MASTER WHERE PRODUCT_NO NOT IN(SELECT PRODUCT_NO FROM SALES_ORDER_DETAILS);

Output:

PRODUCT_NO	DESCRIPTION
P00004	1.22 Floppies

Q19: List the customer name, address, city and pincode for the client who has placed Order_No ‘O19001’.

Solution:

SELECT NAME, ADDRESS, CITY, STATE, PINCODE FROM CLIENT_MASTER WHERE CLIENT_NO IN(SELECT CLIENT_NO FROM SALES_ORDER WHERE ORDER_NO = 'O19001');

Output:

NAME	ADDRESS	CITY	STATE	PINCODE
Ivan Bayross	F-111	Bombay	Maharashtra	400054

Q20: List the client names that have placed orders before the month of May’02.

Solution:

SELECT CLIENT_NO, NAME FROM CLIENT_MASTER WHERE CLIENT_NO IN(SELECT CLIENT_NO FROM SALES_ORDER WHERE TO_CHAR(ORDERDATE, 'MON,YY') < 'MAY,02');

Output:

CLIENT_NO	NAME
C00001	Ivan Bayross
C00002	Vandana
C00003	Praveen
C00004	Basu

Q21: List if the product ‘mouse’ has been ordered by any client and print the Client_No, name to whom it was sold.

Solution:

SELECT CLIENT_NO, NAME FROM CLIENT_MASTER WHERE CLIENT_NO IN(SELECT CLIENT_NO FROM SALES_ORDER WHERE ORDER_NO IN(SELECT ORDER_NO FROM SALES_ORDER_DETAILS WHERE PRODUCT_NO IN(SELECT PRODUCT_NO FROM PRODUCT_MASTER WHERE DESCRIPTION = 'Mouse')));

Output:

CLIENT_NO	NAME
C00004	Basu

Q22: List the names of clients who have placed orders worth Rs. 10000 or more.

Solution:

SELECT NAME FROM CLIENT_MASTER WHERE CLIENT_NO IN(SELECT CLIENT_NO FROM SALES_ORDER WHERE ORDER_NO IN(SELECT ORDER_NO FROM SALES_ORDER_DETAILS WHERE(QTYORDERED * PRODUCTRATE) >= 10000));

Output:

NAME

Ivan Bayross

Praveen

Basu