PART A

1. Create a table EMPLOYEE with the following columns:

Column Data type Constraint

ENO number Constraint NOT NULL and PRIMARY KEY

ENAME varchar2 Constraint NOT NULL

DEPT varchar2 Constraint NOT NULL

BASIC number Constraint Basic >=5000 and <=45000

Insert five records. Alter the table to add the columns DA, HRA, GrossPay, PF, PT and

Netpay and calculate the DA, HRA, GrossPay, PF, PT and Netpay as follows:

DA = 80% of BASIC if BASIC <20000 otherwise DA = 70% of BASIC

HRA= 10% of BASIC, GrossPay= BASIC+DA+HRA

PF = 12 % of Gross and PT=5% of Gross

Netpay = GrossPay – (PF+PT)

Write the SQL statements for the following:

a) Display the details of all employees.

b) Display the names of employees of Finance department.

c) Display the names of all employees in alphabetical order.

d) Display the name and Netpay of all employees in decreasing order of their Netpay.

e) Display the details of employees whose Netpay is between 15000 and 35000.

f) Display the name and basic of employees whose name contains KUMAR.

g) Display the name and basic of employees whose name ends with ‘th’.

h) Display the number of employees department-wise only if department includes more

than three employees.

i) Delete all the employees of Accounts department.

CREATE TABLE EMPLOYEE

(ENO NUMBER(5) NOT NULL PRIMARY KEY,

ENAME VARCHAR2(15)NOT NULL,

DEPT VARCHAR2(15)NOT NULL,

BASIC NUMBER(5)CHECK(BASIC BETWEEN 5000 AND 45000));

INSERT INTO EMPLOYEE VALUES(&ENO,'&ENAME','&DEPT',&BASIC);

ALTER TABLE EMPLOYEE

ADD(DA NUMBER(10,2),HRA NUMBER(10,2),GROSSPAY NUMBER(10,2),PF NUMBER(10,2),PT

NUMBER(10,2),NETPAY NUMBER(10,2));

UPDATE EMPLOYEE

SET DA=CASE

WHEN BASIC<=20000 THEN 0.8*BASIC

WHEN BASIC>20000 THEN 0.7*BASIC

END;

UPDATE EMPLOYEE

SET HRA=0.1*BASIC;

UPDATE EMPLOYEE

SET GROSSPAY=BASIC+DA+HRA;

UPDATE EMPLOYEE

SET PF=0.12*GROSSPAY;
UPDATE EMPLOYEE

SET PT=0.05*GROSSPAY;

UPDATE EMPLOYEE

SET NETPAY=GROSSPAY-(PF+PT);

a) Display the details of all employees.

SELECT * FROM EMPLOYEE;

b) Display the names of employees of Finance department.

SELECT ENAME FROM EMPLOYEE WHERE DEPT='FINANCE';

c) Display the names of all employees in alphabetical order.

SELECT ENAME FROM EMPLOYEE ORDER BY ENAME;

d) Display the name and Netpay of all employees in decreasing order of their Netpay.

SELECT ENAME,NETPAY FROM EMPLOYEE ORDER BY NETPAY DESC;

e) Display the details of employees whose Netpay is between 15000 and 35000.

SELECT * FROM EMPLOYEE WHERE NETPAY BETWEEN 15000 AND 35000;

f) Display the name and basic of employees whose name contains KUMAR.

SELECT ENAME,BASIC FROM EMPLOYEE WHERE ENAME LIKE'%KUMAR%';

g) Display the name and basic of employees whose name ends with ‘th’.

SELECT ENAME,BASIC FROM EMPLOYEE WHERE ENAME LIKE'%TH';

h) Display the number of employees department-wise only if department includes more

than three employees.

SELECT COUNT(ENO),DEPT FROM EMPLOYEE GROUP BY DEPT HAVING COUNT(DEPT)>3;

i) Delete all the employees of Accounts department.

DELETE FROM EMPLOYEE WHERE DEPT='ACCOUNTS';

PART A

2. Create a table CLIENT to store CLIENT_NO, NAME, ADDRESS, STATE, BAL_DUE.

CLIENT_NO must start with ‘C’. Apply suitable structure for the columns. Specify Primary Key

and NOT NULL constraints. Insert five records.

Write the following SQL queries:

a) From the table CLIENT, create a new table CLIENT1 that contains only CLIENT_NO

and NAME, BAL_DUE from specified STATE. Accept the state during run time.

b) Create a new table CLIENT2 that has the same structure as CLIENT but with no records.

Display the structure and records.

c) Add a new column by name PENALTY number (10, 2) to the CLIENT.

d) Assign a penalty of 10% of BAL_DUE for the clients C1002, C1003, C1005 and8% for

others. Display the records.

e) Change the name of CLIENT1 as NEW_CLIENT.

f) Delete the table CLIENT2.

CREATE TABLE CLIENT

(CLIENT_NO VARCHAR(5) CHECK(CLIENT_NO LIKE'C%')PRIMARY KEY,

NAME VARCHAR(15) NOT NULL,

ADDRESS VARCHAR(15),

STATE VARCHAR(15),

BAL_DUE NUMBER(10));
INSERT INTO CLIENT VALUES('&CLIENT_NO','&NAME','&ADDRESS','&STATE',&BAL_DUE);

a) From the table CLIENT, create a new table CLIENT1 that contains only CLIENT_NO

and NAME, BAL_DUE from specified STATE. Accept the state during run time.

CREATE TABLE CLIENT1(CLIENT_NO,NAME,BAL_DUE)

AS SELECT CLIENT_NO,NAME,BAL_DUE FROM CLIENT

WHERE STATE='&STATE';

b) Create a new table CLIENT2 that has the same structure as CLIENT but with no records.

Display the structure and records.

CREATE TABLE CLIENT2(CLIENT_NO,NAME,ADDRESS,STATE,BAL_DUE)

AS SELECT * FROM CLIENT

WHERE 1=2;

c) Add a new column by name PENALTY number (10, 2) to the CLIENT.

ALTER TABLE CLIENT

ADD(PENALTY NUMBER(10,2));

d) Assign a penalty of 10% of BAL_DUE for the clients C1002, C1003, C1005 and8% for

others. Display the records.

UPDATE CLIENT

SET PENALTY=CASE

WHEN CLIENT_NO IN('C1002','C1003','C1005')

THEN 0.1*BAL_DUE

WHEN CLIENT_NO NOT IN('C1002','C1003','C1005')

THEN 0.08*BAL_DUE

END;

e) Change the name of CLIENT1 as NEW_CLIENT.

RENAME CLIENT1 TO NEW_CLIENT;

f) Delete the table CLIENT2.

DROP TABLE CLIENT2;

PART A

3. Create a table BOOK using SQL command to store ACC_NO, TITLE, AUTHOR,

PUBLISHER, YEAR, PRICE. Apply suitable structure for the columns. Specify Primary Key

and NOT NULL constraints. Insert five records.

Write the following SQL queries:

a) List the details of publishers having ‘a’ as the second character in their names.

b) Display ACC_NO, TITLE, PUBLISHER and YEAR of the books published by the

specified author before 2010 in the descending order of YEAR. Accept AUTHOR during

run time.

c) Modify the size of TITLE to increase by five characters.

d) Display the details of all books other than Microsoft press publishers.

e) Remove the records of the books published before 1990.

CREATE TABLE BOOK

(ACC_NO NUMBER(5) PRIMARY KEY,

TITLE VARCHAR(25) NOT NULL,

AUTHOR VARCHAR(25),

PUBLISHER VARCHAR(25),

YEAR NUMBER(5),
PRICE NUMBER(10));

INSERT INTO BOOK VALUES(&ACC_NO,'&TITLE','&AUTHOR','&PUBLISHER',&YEAR,&PRICE);

a) List the details of publishers having ‘a’ as the second character in their names.

SELECT * FROM BOOK WHERE PUBLISHER LIKE'_A%';

b) Display ACC_NO, TITLE, PUBLISHER and YEAR of the books published by the

specified author before 2010 in the descending order of YEAR. Accept AUTHOR during

run time.

SELECT ACC_NO,TITLE,PUBLISHER,YEAR FROM BOOK WHERE YEAR<2010 AND AUTHOR='&AUTHOR'

ORDER BY YEAR DESC;

c) Modify the size of TITLE to increase by five characters.

ALTER TABLE BOOK

MODIFY(TITLE VARCHAR(30));

d) Display the details of all books other than Microsoft press publishers.

SELECT * FROM BOOK WHERE PUBLISHER NOT IN('MICROSOFT PRESS');

e) Remove the records of the books published before 1990.

DELETE FROM BOOK WHERE YEAR<1990;

PART A

4. Create a table SALES with columns SNO, SNAME, MANAGER_NO, JOIN_DATE,

DATE_BIRTH, SALARY, SALES_AMOUNT and COMMISSION. Minimum age for joining

the company must be 18 years. Default value for COMMISSION should be 0. Manager of

Manager can be NULL.

Apply suitable structure for the columns. Specify Primary Key and NOT NULL constraints.

Insert fiverecords with data except COMMISSION.

Write the following SQL queries:

a) Display the details of Sales Persons whose salaryis more than average salary in the
company.

b) Update commission as 20% of Sales Amount.

c) Display SNO, SNAME, MANAGER_NAME, SALARY, COMMISSION,

MANAGER_SALARY of the sales persons getting sum of salary and commission more

than salary ofmanager (self-join).

d) Display the records of employees who finished the service of 10years.

CREATE TABLE SALES

(SNO NUMBER(5) PRIMARY KEY,

SNAME VARCHAR(15) NOT NULL,

MNO NUMBER(5),

DOJ DATE NOT NULL,

DOB DATE NOT NULL,

SALARY NUMBER(10),

SAMOUNT NUMBER(10),

COMMISSION NUMBER(10) DEFAULT 0,

CONSTRAINT DOJ CHECK(MONTHS_BETWEEN(DOJ,DOB)/12>=18));

INSERT INTO SALES

VALUES(&SNO,'&SNAME',&MNO,'&DOJ','&DOB',&SALARY,&SAMOUNT,&COMMISSION);

a) Display the details of Sales Persons whose salaryis more than average salary in the

company.

SELECT * FROM SALES WHERE SALARY>(SELECT AVG(SALARY) FROM SALES);

b) Update commission as 20% of Sales Amount.

UPDATE SALES

SET COMMISSION=0.2*SAMOUNT;
c) Display SNO, SNAME, MANAGER_NAME, SALARY, COMMISSION,

MANAGER_SALARY of the sales persons getting sum of salary and commission more

than salary ofmanager (self-join).

SELECT A.SNO,A.SNAME,B.SNAME"MANAGER NAME",A.SALARY,A.COMMISSION,B.SALARY"MANAGER

SAL"

FROM SALES A ,SALES B

WHERE B.SNO=A.MNO AND (A.SALARY+A.COMMISSION)>B.SALARY;

d) Display the records of employees who finished the service of 10years.

SELECT * FROM SALES WHERE MONTHS_BETWEEN(SYSDATE,DOJ)/12>10;

PART A

5. Create a table SALES_DETAILS with the columns SNO, MONTH, TARGET and

QTY_SOLD to store the sales details of one year. Specify the composite primary key to be the

columns SNO and MONTH. TARGET and SALES must be positive numbers.

Write the following SQL queries:

a. Display the total sales by each sales person considering only those months sales
where target was reached.

b. If a commission of Rs.50 provided for each item after reaching target, calculate and

display the total commission for each sales person.

c. Display the SNO of those sales persons who never reached the target.

d. Display the SNO, MONTH and QTY_SOLD of the sales persons with SNO S0001 or

S0003.

CREATE TABLE SALES_DETAILS

(SNO VARCHAR(5),

MONTH VARCHAR(15),

TARGET NUMBER(5) CHECK(TARGET>0),

QTY_SOLD NUMBER(5) CHECK(QTY_SOLD>0),

PRIMARY KEY(SNO,MONTH));

INSERT INTO SALES_DETAILS VALUES('&SNO','&MONTH',&TARGET,&QTY_SOLD);

a. Display the total sales by each sales person considering only those months sales

where target was reached.

SELECT SNO,SUM(QTY_SOLD)"TOTAL SALES" FROM SALES_DETAILS WHERE TARGET<=QTY_SOLD GROUP

BY SNO;

b. If a commission of Rs.50 provided for each item after reaching target, calculate and

display the total commission for each sales person.

SELECT SNO,SUM(QTY_SOLD)*50"COMMISSION" FROM SALES_DETAILS WHERE TARGET<=QTY_SOLD

GROUP BY SNO;

c. Display the SNO of those sales persons who never reached the target.

SELECT SNO FROM SALES_DETAILS WHERE TARGET>QTY_SOLD;

d. Display the SNO, MONTH and QTY_SOLD of the sales persons with SNO S0001 or

S0003.
SELECT SNO,MONTH,QTY_SOLD FROM SALES_DETAILS WHERE SNO='S0001' OR SNO='S0003';

PART A

6.Create table STUDENT_PROFILE includes Rollno, name, class, ECCC(Extra-Co curricular

he belongs to such as SPORTs, NSS etc.) and another table MARKS_REPORT includes Rollno,

Internal_Test, Marks1, Marks2, Marks3 and ECCC_marks.

Constraints

• Internal _Test can be either 1 or 2.

• Each mark can be 0-100. Absence in the test can be entered as -1.

• Consider atleast 3 classes.

Apply suitable data type and constraints to each column.

Insert 5 students marks report in the both the tests.

Write the following SQL queries:

a) Find number of students failed class- wise.

b) Display the complete details of the students secured

distinction(Percentage>=70) in I BCA.

c) Display class and highest total marks in second internals in each class.

d) Display the student name with rollno and class of those who passed in I

internals and failed in II internals.(use SET operator

CREATE TABLE STUDENT_PROFILE

( ROLLNO NUMBER(5) PRIMARY KEY,

NAME VARCHAR(15),

CLASS VARCHAR(10)CHECK( CLASS IN('I BCA','II BCA','III BCA')),

ECCC VARCHAR(15));

INSERT INTO STUDENT_PROFILE VALUES(&ROLLNO,'&NAME','&CLASS','&ECCC');

CREATE TABLE MARKS_REPORT

(ROLL_NO NUMBER(5) REFERENCES STUDENT_PROFILE(ROLLNO),

INTERNAL_TEST NUMBER(2) CHECK(INTERNAL_TEST IN(1,2)),

M1 NUMBER(3) CHECK((M1 BETWEEN 0 AND 100)OR M1=-1),

M2 NUMBER(3)CHECK((M2 BETWEEN 0 AND 100)OR M2=-1),

M3 NUMBER(3)CHECK((M3 BETWEEN 0 AND 100)OR M3=-1),

ECCCM NUMBER(3)CHECK((ECCCM BETWEEN 0 AND 100)OR ECCCM=-1));

INSERT INTO MARKS_REPORT VALUES(&ROLL_NO,&INTERNAL_TEST,&M1,&M2,&M3,&ECCCM);

a) Find number of students failed class- wise.

SELECT CLASS,COUNT(DISTINCT(ROLLNO)) FROM STUDENT_PROFILE,MARKS_REPORT WHERE

ROLLNO=ROLL_NO AND (M1<35 OR M2<35 OR M3<35 OR ECCCM<35)GROUP BY CLASS;

b) Display the complete details of the students secured

distinction(Percentage>=70) in I BCA.

SELECT * FROM STUDENT_PROFILE,MARKS_REPORT WHERE ROLLNO=ROLL_NO AND (M1>=35 AND

M2>=35 AND M3>=35 AND ECCCM>=35) AND (((M1+M2+M3+ECCCM)/400)*100)>=70 AND CLASS='I
BCA';

c) Display class and highest total marks in second internals in each class.
SELECT CLASS,MAX(M1+M2+M3+ECCCM)"HIGHEST TOTAL" FROM STUDENT_PROFILE,MARKS_REPORT
WHERE ROLLNO=ROLL_NO AND (M1>=35 AND M2>=35 AND M3>=35 AND ECCCM>=35) AND
INTERNAL_TEST=2 GROUP BY CLASS;

d) Display the student name with rollno and class of those who passed in I

internals and failed in II internals.(use SET operator

SELECT NAME,ROLLNO,CLASS FROM STUDENT_PROFILE,MARKS_REPORT WHERE ROLLNO=ROLL_NO

AND (M1>=35 AND M2>=35 AND M3>=35 AND ECCCM>=35) AND INTERNAL_TEST=1

INTERSECT

SELECT NAME,ROLLNO,CLASS FROM STUDENT_PROFILE,MARKS_REPORT WHERE ROLLNO=ROLL_NO

AND (M1<35 AND M2<35 AND M3<35 AND ECCCM<35) AND INTERNAL_TEST=2;

PART A

7. Create a table BANK with columns ACNO, ACT_NAME, ACT_TYPE and BAL. Specify the

Primary Key. Initial BAL must be greater than 500.

Write a PL/SQL program to perform debit operation by providing acct_no and amount required.

The amount must be greater than 100 and less than 20000 for one transaction. If the account

exists and BAL>100 BANK table must be updated; otherwise “NO SUFFFICIENT BALANCE”

message should be displayed. If account number is not present then display “NO SUCH

ACCOUNT” message to the user.

CREATE TABLE BANK

(ACNO NUMBER(5) PRIMARY KEY,

ACNAME VARCHAR(15) NOT NULL,

ACTYPE VARCHAR(15),

BALANCE NUMBER(15));

INSERT INTO BANK VALUES(&ACNO,'&ACNAME','&ACTYPE',&BALANCE);

SET SERVEROUTPUT ON;

DECLARE

LACNO NUMBER:=&ACTNO;

LAMOUNT NUMBER:=&AMOUNT;

LBAL NUMBER(10,2);

BEGIN

IF(LAMOUNT>100 AND LAMOUNT<20000)THEN

SELECT BALANCE INTO LBAL FROM BANK WHERE ACNO=LACNO;

LBAL:=LBAL-LAMOUNT;

IF(LBAL<100) THEN

DBMS_OUTPUT.PUT_LINE('NO SUFFICIENT BALANCE');

ELSE

UPDATE BANK

SET BALANCE=LBAL WHERE ACNO=LACNO;

DBMS_OUTPUT.PUT_LINE('BALANCE UPDATE');

END IF;

ELSE

DBMS_OUTPUT.PUT_LINE('THE AMOUNT MUST BE >100 AND <20000');

COMMIT;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('NO SUCH ACCOUNT');

END;
/

PART A

8. Create a table STOCK_DETAIL with the columns PNO, PNAME and QTY_AVL to store

stock details of computer accessories. Specify Primary Key and NOTNULL constraints.

QTY_AVL should be a positive number.

Write a PL/SQL Program to define a user-defined exception named “LOW_STOCK” to validate

the transaction. The program facilitates the user to purchase the product by providing product

number and quantity required. It should display an error message “NO SUFFICIENT STOCK”

when the user tries to purchase a product with quantity more than QTY_AVL. Otherwise the

STOCK_DETAIL table should be updated for valid transaction.

CREATE TABLE STOCK_DETAILS

(PNO NUMBER(5)PRIMARY KEY,

PNAME VARCHAR(15)NOT NULL,

QTYAVAIL NUMBER(5) CHECK(QTYAVAIL>0));

INSERT INTO STOCK_DETAILS VALUES(&PNO,'&PNAME',&QTYAVAIL);

SET SERVEROUTPUT ON;

DECLARE
R_PNUM NUMBER:=&PRODUCT_NUMBER;

R_QREQ NUMBER:=&QUANTITY_REQUIRED;

R_QUANTITYAVAIL NUMBER;

LOW_STOCK EXCEPTION;

NON_ZERO EXCEPTION;

BEGIN

DBMS_OUTPUT.PUT_LINE('THE PRODUCT NUMBER YOU HAVE ENTERED IS:'||R_PNUM);

DBMS_OUTPUT.PUT_LINE('THE QUANTITY YOU HAVE ENTERED IS:'||R_QREQ);

BEGIN

SELECT QTYAVAIL INTO R_QUANTITYAVAIL FROM STOCK_DETAILS WHERE PNO=R_PNUM;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('PRODUCT NOT FOUND STOCK');

RETURN;

END;

IF R_QUANTITYAVAIL<R_QREQ THEN RAISE LOW_STOCK;

ELSE

DBMS_OUTPUT.PUT_LINE('THE QUANTITY VALUE BEFORE PURCHASE:'||R_QUANTITYAVAIL);

R_QUANTITYAVAIL:=R_QUANTITYAVAIL-R_QREQ;

IF R_QUANTITYAVAIL=0 THEN

RAISE NON_ZERO;

END IF;

UPDATE STOCK_DETAILS

SET QTYAVAIL=R_QUANTITYAVAIL WHERE PNO=R_PNUM;

DBMS_OUTPUT.PUT_LINE('THE QUANTITY VALUE BEFORE PURCHASE:'||R_QUANTITYAVAIL);

END IF;

EXCEPTION

WHEN LOW_STOCK THEN

DBMS_OUTPUT.PUT_LINE('NO SUFFICIENT STOCK');

WHEN NON_ZERO THEN

DBMS_OUTPUT.PUT_LINE('QUANTITY MUST NOT BE ZERO');

END ;

PART B

1. Create the following tables by identifying primary and foreign keys. Specify the NOT NULL

property for mandatory keys.

SUPPLIERS (Supplier_no, Sname, Saddress, Scity)

COMPUTER_ITEMS (Item_No, Supplier_no, Item_name, quantity)

Insert five records into both the tables and execute the following queries.

a) List item and supplier details.

b) List the names of the suppliers who are supplying the Keyboard.

c) Display the items supplied by Microtech.

d) List the items supplied by the suppliers Cats and Electrotech.

e) List the name of the suppliers and items whose quantity is more than 10.
CREATE TABLE SUPPLIERS

( SNO VARCHAR(5) PRIMARY KEY,

SNAME CHAR(15) NOT NULL,

SADD CHAR(15),

SCITY CHAR(15));

INSERT INTO SUPPLIERS VALUES('&SNO','&SNAME','&SADD','&SCITY');

CREATE TABLE COMPUTER_ITEMS

( INO NUMBER(5),

SUPNO VARCHAR(5) REFERENCES SUPPLIERS(SNO),

INAME CHAR(15),

QTY NUMBER(5));

INSERT INTO COMPUTER_ITEMS VALUES(&INO,'&SUPNO','&INAME',&QTY);

a) List item and supplier details.

SELECT SNO,SNAME,SADD,SCITY,INO,INAME,QTY FROM SUPPLIERS,COMPUTER_ITEMS WHERE

SNO=SUPNO;

b) List the names of the suppliers who are supplying the Keyboard.

SELECT SNAME FROM SUPPLIERS,COMPUTER_ITEMS WHERE SNO=SUPNO AND INAME='KEYBOARD';

c) Display the items supplied by Microtech.

SELECT INAME FROM SUPPLIERS,COMPUTER_ITEMS WHERE SNO=SUPNO AND SNAME='MICROTECH';

d) List the items supplied by the suppliers Cats and Electrotech.

SELECT INAME FROM SUPPLIERS,COMPUTER_ITEMS WHERE SNO=SUPNO AND SNAME

IN('CATS','ELECTROTECH');
e) List the name of the suppliers and items whose quantity is more than 10.

SELECT SNAME,INAME FROM SUPPLIERS,COMPUTER_ITEMS WHERE SNO=SUPNO AND QTY>10;

PART B

2. Create table DEPARTMENT with fields DeptnoVarchar2(3) must begin with D, Dname must

be Accounts, Purchase, Sales or Service. Impose Primary Key on Deptno.

Create another table EMP with fields Eno Number (5), EnameVarchar2(15) Not Null, Deptno

Varchar2(3) which must exist in the Department table, Salary Number (10,2). Impose Primary

Key on Eno.

Insert five records each to both the tables and execute the following queries.

a) Update the salary of employees by 20% in the department of Sales and Service.

b) Display the name of employees working in the departments of Accounts, Purchase and

Sales.

c) Display the name of employees working in the department of Purchase or Sales having

salary>20000.

d) Display the names of the departments with noemployees.

CREATE TABLE DEPARTMENT

( DEPTNO VARCHAR2(3) CHECK( DEPTNO LIKE 'D%') PRIMARY KEY,

DNAME VARCHAR(15) CHECK (DNAME IN('ACCOUNTS','PURCHASE','SALES','SERVICE')));

INSERT INTO DEPARTMENT VALUES('&DEPTNO','&DNAME');

CREATE TABLE EMP

( ENO NUMBER(5) PRIMARY KEY,

ENAME VARCHAR2(15) NOT NULL,

DEPNO VARCHAR2(3) REFERENCES DEPARTMENT(DEPTNO),

SALARY NUMBER(10,2));

INSERT INTO EMP VALUES(&ENO,'&ENAME','&DEPNO',&SALARY);

a) Update the salary of employees by 20% in the department of Sales and Service

UPDATE EMP

SET SALARY=SALARY+(0.2*SALARY) WHERE DEPNO IN( SELECT DEPTNO FROM DEPARTMENT WHERE
DNAME IN('SALES','SERVICE'));

b) Display the name of employees working in the departments of Accounts, Purchase and

Sales.

SELECT ENAME FROM EMP,DEPARTMENT WHERE DEPNO=DEPTNO AND DNAME

IN('ACCOUNTS','PURCHASE','SALES');

c) Display the name of employees working in the department of Purchase or Sales having

salary>20000.

SELECT ENAME FROM EMP,DEPARTMENT WHERE DEPNO=DEPTNO AND DNAME IN('PURCHASE','SALES')

AND SALARY>20000;

d) Display the names of the departments with noemployees.

SELECT DNAME FROM DEPARTMENT WHERE DEPTNO NOT IN( SELECT DEPNO FROM EMP);
PART B

3.Create Item_Master table with the fields ItemNo as Primary key, Item_Name (HDD,

MONITOR, CDROM, KEYBOARD, UPS), Stock defined as Not Null and Price which is

greater than 10000 and less than 30000. Create another table Item_Trans with the fields ItemNo

referencing the Item_Master table, Quantity greater than 25 and Trans_Date.

Insert 5 records to each of the tables and execute the following queries.

a) Display all the records from the Item_Master table.

b) List the Item_Name and the Stock where Price lies between 2500 and 25000.

c) Display the ItemNo, Item_Name and the Quantity where Quantity>=100

d) Display the item name and amount where amount=Price*Quantity with Amount as

new column heading.

e) Display the ItemNo, Item_Name, Quantity, Trans_date where the transaction date

greater than March 2008.

(Note: Minimum of five records must be inserted in each table. Any four queries to be

asked in the examination

CREATE TABLE ITEM_MASTER

( INO NUMBER(5) PRIMARY KEY,

INAME VARCHAR(15) CHECK (INAME IN('HDD','MONITOR','CDROM','KEYBOARD','UPS')),

STOCK NUMBER(5) NOT NULL,

PRICE NUMBER(5) CHECK(PRICE BETWEEN 10000 AND 30000));

INSERT INTO ITEM_MASTER VALUES(&INO,'&INAME',&STOCK,&PRICE);

CREATE TABLE ITEM_TRANS

(ITEMNO NUMBER(5) REFERENCES ITEM_MASTER(INO),

QTY NUMBER(5) CHECK(QTY>25),

TRANSDATE DATE);

INSERT INTO ITEM_TRANS VALUES(&ITEMNO,&QTY,'&TRANSDATE');

a) Display all the records from the Item_Master table.

SELECT * FROM ITEM_MASTER;

b) List the Item_Name and the Stock where Price lies between 2500 and 25000.

SELECT INAME,STOCK FROM ITEM_MASTER WHERE PRICE BETWEEN 2500 AND 25000;

c) Display the ItemNo, Item_Name and the Quantity where Quantity>=100

SELECT INO,INAME,QTY FROM ITEM_MASTER,ITEM_TRANS WHERE INO=ITEMNO AND QTY>=100;

d) Display the item name and amount where amount=Price*Quantity with Amount as

new column heading.

SELECT INAME,SUM(PRICE*QTY)"AMOUNT" FROM ITEM_MASTER,ITEM_TRANS WHERE INO=ITEMNO

GROUP BY INAME;

e) Display the ItemNo, Item_Name, Quantity, Trans_date where the transaction date

greater than March 2008.

SELECT INO,INAME,QTY,TRANSDATE FROM ITEM_MASTER,ITEM_TRANS WHERE INO=ITEMNO AND

TRANSDATE>'31-MAR-08';
4. Create the following tables by identifying primary and foreign keys, specify the not null

property for mandatory keys.

Write the following SQL queries:

a) Display total amount spent by C2.

b) Display the names of product for which either QtyAvailable is less than 30 or total

QtySold is less than 5(USE UNION).

c) Display the name of products and quantity purchased by C4.

d) How much Profit does the shopkeeper get on C1’s purchase?

e) How many ‘Pen Drives’ have been sold?

CREATE TABLE PRODUCT_DETAIL

( PRNO VARCHAR(8) PRIMARY KEY,

PNAME VARCHAR(15) NOT NULL,

QTYAVAIL NUMBER(5),

PRICE NUMBER(5),

PROFIT NUMBER(5));

INSERT INTO PRODUCT_DETAIL VALUES('&PRNO','&PNAME',&QTYAVAIL,&PRICE,&PROFIT);

CREATE TABLE PURCHASE_DETAILS

( CUSTNO VARCHAR(2) ,

PNO VARCHAR(8) REFERENCES PRODUCT_DETAIL(PRNO),

QTYSOLD NUMBER(5));

INSERT INTO PURCHASE_DETAILS VALUES('&CUSTNO','&PNO',&QTYSOLD);

a) Display total amount spent by C2.

SELECT SUM(PRICE*QTYSOLD)"TOTAL AMOUNT" FROM PRODUCT_DETAIL,PURCHASE_DETAILS WHERE

PRNO=PNO AND CUSTNO='C2';

b) Display the names of product for which either QtyAvailable is less than 30 or total

QtySold is less than 5(USE UNION).

SELECT PNAME FROM PRODUCT_DETAIL WHERE QTYAVAIL<30

UNION

SELECT PNAME FROM PRODUCT_DETAIL,PURCHASE_DETAILS WHERE PRNO=PNO AND QTYSOLD<5;

c) Display the name of products and quantity purchased by C4.

SELECT PNAME,QTYSOLD FROM PRODUCT_DETAIL,PURCHASE_DETAILS WHERE PRNO=PNO AND

CUSTNO='C4';

d) How much Profit does the shopkeeper get on C1’s purchase?

SELECT SUM((PRICE*QTYSOLD)*(PROFIT/100)) FROM PRODUCT_DETAIL,PURCHASE_DETAILS WHERE

PRNO=PNO AND CUSTNO='C1';

e) How many ‘Pen Drives’ have been sold?

SELECT SUM(QTYSOLD) FROM PRODUCT_DETAIL,PURCHASE_DETAILS WHERE PNO=PRNO AND

PNAME='PEN DRIVES';

PART B

5. Write a PL/SQL program to accept the students’ roll number, name and their marks in 3

subjects from a base table Student (having RollNo, Sname, Marks in three subjects) and declare

the result based on the following rules:

If student has scored below 40 in any subject, he/she is declared as FAIL. If the Total >=180

then declare the result as I CLASS. If the Total >=150 but <180 then declare the result as II

CLASS. If the Total <120 then declare the result as III CLASS.

Insert the records forfive students. Create the output which contains the roll number, name of the

student, marks in three subjects, total mark and result in the following format:

======================================================

ROLLNO NAME MARK1 MARK2 MARK3 TOTAL RESULT

======================================================

CREATE TABLE STUDENT

( RNO NUMBER(3),

NAME VARCHAR(15),

M1 NUMBER(3),

M2 NUMBER(3),

M3 NUMBER(3));
INSERT INTO STUDENT VALUES(&RNO,'&NAME',&M1,&M2,&M3);

SET SERVEROUTPUT ON;

DECLARE

MRNO STUDENT.RNO%TYPE;

MNAME VARCHAR(15);

MM1 STUDENT.M1%TYPE;

MM2 STUDENT.M2%TYPE;

MM3 STUDENT.M3%TYPE;

TOTAL NUMBER(5);

RESULTS VARCHAR(15);

CURSOR C1 IS SELECT * FROM STUDENT;

BEGIN

OPEN C1;

DBMS_OUTPUT.PUT_LINE('*************************************');

DBMS_OUTPUT.PUT_LINE('ROLL NO'||' '||'NAME'||' '||'M1'||' '||'M2'||' '||'M3'||' '||'TOTAL'||'

'||'RESULTS');

DBMS_OUTPUT.PUT_LINE('*************************************');

LOOP

FETCH C1 INTO MRNO,MNAME,MM1,MM2,MM3;

EXIT WHEN C1 % NOTFOUND;

TOTAL:=MM1+MM2+MM3;

IF

(MM1<40 OR MM2<40 OR MM3<40)

THEN RESULTS:='FAILS';

ELSIF(TOTAL>=180)

THEN RESULTS:='I CLASS';

ELSIF(TOTAL<180 AND TOTAL>=150)

THEN RESULTS:='II CLASS';

ELSIF(TOTAL<150)

THEN RESULTS:='III CLASS';

END IF;

DBMS_OUTPUT.PUT_LINE(MRNO||' '||MNAME||' '||MM1||' '||MM2||' '||MM3||' '||TOTAL||'

'||RESULTS);

END LOOP;

DBMS_OUTPUT.PUT_LINE('****************************************');

END;

6. Write a PL/SQL cursor program to calculate electricity bill of several domestic customers.

Accept Input RR No, name of the customer, previous meter reading, and current meter reading

from the table. The rates of electricity consumption are as follows -

For the first 30 units Rs. 2.5 per unit, for the next 70 units Rs. 3.5 per unit, for the next 100 units

Rs. 4.5 per unit, for the next 100 units Rs. 6 per unit and for units above 300 Rs. 8 per unit. A

fixed amount of Rs. 150 is also charged. 5% tax to be paid on the sum of bill amount & fixed

amount. Use Data validation to see that current reading is more than previous reading. Assume

the records of 5 customer details. Create the output which contains the RR number, name of the

Customer, previous meter reading, and current meter reading, Units Consumed, Total Bill in the

following format.

==================================================================

RR No. Customer Name Current Meter Previous Meter Units Total

Reading Reading Consumed Bill

=================================================================

CREATE TABLE EBILL

( RRNO NUMBER(5),

NAME VARCHAR(15),

CMR NUMBER(5),

PMR NUMBER(5));

INSERT INTO EBILL VALUES(&RRNO,'&NAME',&CMR,&PMR);

SET SERVEROUTPUT ON;

DECLARE

MRRNO EBILL.RRNO%TYPE;

MNAME VARCHAR(15);

MCMR EBILL.CMR%TYPE;

MPMR EBILL.PMR%TYPE;

UNIT NUMBER(10,2);

AMOUNT NUMBER(10,2);

BILL NUMBER(10,2);

CURSOR C1 IS SELECT * FROM EBILL;

BEGIN

OPEN C1;

DBMS_OUTPUT.PUT_LINE('**********************************************');

DBMS_OUTPUT.PUT_LINE('CUST NO'||' '||'NAME'||' '||'CMR'||' '||'PMR'||' '||'UNIT'||' '||'TOTAL

BILL');

DBMS_OUTPUT.PUT_LINE('**********************************************');

LOOP

FETCH C1 INTO MRRNO,MNAME,MCMR,MPMR;

EXIT WHEN C1 % NOTFOUND;

UNIT:=MCMR-MPMR;

IF(UNIT<=30)

THEN AMOUNT:=2.5*UNIT;

ELSIF(UNIT<=100)

THEN AMOUNT:=30*2.5+(UNIT-30)*3.5;

ELSIF(UNIT<=200)

THEN AMOUNT:=30*2.5+70*3.5+(UNIT-100)*4.5;

ELSIF(UNIT<=300)

THEN AMOUNT:=30*2.5+70*3.5+100*4.5+(UNIT-200)*6;

ELSE

AMOUNT:=30*2.5+70*3.5+100*4.5+100*6+(UNIT-300)*8;

END IF;

BILL:=AMOUNT+150;

BILL:=BILL+BILL*(5/100);

DBMS_OUTPUT.PUT_LINE(MRRNO||' '||MNAME||' '||MCMR||' '||MPMR||' '||UNIT||' '||BILL);

END LOOP;

DBMS_OUTPUT.PUT_LINE('**********************************************');

CLOSE C1;

END;

/
PART B

7.Write a PL/SQL program to display employee pay bill (using Cursor For loop) Use a

Procedure to receive basic pay and to compute DA, HRA, Tax, PF, Gross Pay and Net Pay(Use

OUT). Base table contains the following columns empnum, empname, basic pay.

Insert 3 records.

Allowances are computed as follows.

Basic Pay DA HRA

<=20000 35% of Basic 8% of Basic

>20000 &<=30000 38% 9%

>30000 &<=40000 40% 10%

>40000 45% 10%

Gross=Basic+DA+HRA

PF=12% of Gross or Rs. 2000 whichever is minimum.

PT=Rs. 100 upto Gross is 25,000 else Rs. 200.

Net=Gross-(PF+PT)

Print Pay slip as follows.

CREATE TABLE EMPBILL

(ENO NUMBER(5)PRIMARY KEY,

NAME VARCHAR(15),

BASIC NUMBER(10,2)NOT NULL);

INSERT INTO EMPBILL VALUES(&ENO,'&NAME',&BASIC);

CREATE OR REPLACE PROCEDURE PEMPN

(PBASICPAY EMPBILL.BASIC%TYPE,

PDA OUT EMPBILL.BASIC%TYPE,

PHRA OUT EMPBILL.BASIC%TYPE,

PGROSS OUT EMPBILL.BASIC%TYPE,

PPF OUT EMPBILL.BASIC%TYPE,

PPT OUT EMPBILL.BASIC%TYPE,

PNET OUT EMPBILL.BASIC%TYPE)

IS

BEGIN

IF(PBASICPAY<=20000)THEN

PDA:=0.35*PBASICPAY;

PHRA:=0.08*PBASICPAY;

ELSIF(PBASICPAY>20000 AND PBASICPAY<=30000)THEN

PDA:=0.38*PBASICPAY;

PHRA:=0.09*PBASICPAY;

ELSIF(PBASICPAY>30000 AND PBASICPAY<=40000)THEN

PDA:=0.40*PBASICPAY;

PHRA:=0.10*PBASICPAY;

ELSE

PDA:=0.45*PBASICPAY;

PHRA:=0.10*PBASICPAY;

END IF;

PGROSS:=PBASICPAY+PDA+PHRA;

PPF:=0.12*PGROSS;

IF(PPF<2000)THEN

PPF:=PPF;

ELSE

PPF:=2000;

END IF;

IF(PGROSS<=25000)THEN

PPT:=100;
ELSE

PPT:=200;

END IF;

PNET:=PGROSS-(PPF+PPF);

END;

SET SERVEROUTPUT ON;

DECLARE

ENO EMPBILL.ENO%TYPE;

NAME VARCHAR(15);

BASIC EMPBILL.BASIC%TYPE;

DA EMPBILL.BASIC%TYPE;

HRA EMPBILL.BASIC%TYPE;

GROSS EMPBILL.BASIC%TYPE;

PF EMPBILL.BASIC%TYPE;

PT EMPBILL.BASIC%TYPE;

NET EMPBILL.BASIC%TYPE;

CURSOR C IS SELECT ENO,NAME,BASIC FROM EMPBILL;

BEGIN

FOR I IN C

LOOP

PEMPN(I.BASIC,DA,HRA,GROSS,PF,PT,NET);

DBMS_OUTPUT.PUT_LINE('EMP NO:'||I.ENO||' '||'NAME:'||I.NAME);

DBMS_OUTPUT.PUT_LINE('BASIC PAY:'||I.BASIC||' '||'PF:'||PF);

DBMS_OUTPUT.PUT_LINE('DA:'||ENO||' '||'TAX:'||PT);

DBMS_OUTPUT.PUT_LINE('HRA:'||HRA||' '||'NET:'||NET);

DBMS_OUTPUT.PUT_LINE('GROSS:'||GROSS);
END LOOP;

END;

PART B

8. Create the following tables:

ITEM_MASTER (itemno, name, stock, unit_price). Apply the Primary key and check constraint

for stock and price as >0) [Insert five records].

ITEM_TRANS (itemno, quantity and trans_date)

Create a package PCK_ITEM includes a function CHK_ITEM and a procedure PROC_ITEM.

Function CHK_ITEM is used to check whether specified ITEM_NO exists in ITEM_MASTER

and should return 1 if exists. Otherwise, it should return 0, show appropriate message and

terminate the process.

Procedure PROC_ITEM (with arguments ITEM_NO and quantity) is used to perform the

following if item exists. If required quantity is not available, give appropriate message. If

available, insert a record of this transaction to ITEM_TRANS and modify the stock in

ITEM_MASTER.

Write a query to accept ITEM_NO and quantity of required item. Use Package to do the

transaction process (Transaction date can be current date).

CREATE TABLE ITEM_MASTER

(ITEMNO NUMBER(5)PRIMARY KEY,

NAME VARCHAR(15)NOT NULL,

STOCK NUMBER(5) CHECK(STOCK>0),

UNIT_PRICE NUMBER(5) CHECK(UNIT_PRICE>0));

INSERT INTO ITEM_MASTER VALUES(&ITEMNO,'&NAME',&STOCK,&UNIT_PRICE);

CREATE TABLE ITEM_TRANS

(ITNO NUMBER(5),

QTY NUMBER(5),

TRANDATE DATE DEFAULT SYSDATE);

CREATE OR REPLACE PACKAGE PCK_ITEM AS FUNCTION CHK_ITEM

(FITEMNO ITEM_MASTER.ITEMNO%TYPE)

RETURN NUMBER;

PROCEDURE PROC_ITEM

(PITEMNO ITEM_MASTER.ITEMNO%TYPE,

PQTY ITEM_MASTER.STOCK%TYPE);

END PCK_ITEM;

CREATE OR REPLACE PACKAGE BODY PCK_ITEM AS FUNCTION CHK_ITEM

(FITEMNO ITEM_MASTER.ITEMNO%TYPE) RETURN NUMBER IS DITEMNO ITEM_MASTER.ITEMNO%TYPE;

BEGIN

SELECT ITEMNO INTO DITEMNO FROM ITEM_MASTER WHERE ITEMNO=FITEMNO;

RETURN 1;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN 0;

END CHK_ITEM;
PROCEDURE PROC_ITEM

(PITEMNO ITEM_MASTER.ITEMNO%TYPE,

PQTY ITEM_MASTER.STOCK%TYPE) IS SITEMNO ITEM_MASTER.ITEMNO%TYPE;

SSTOCK ITEM_MASTER.STOCK%TYPE;

SPRICE ITEM_MASTER.UNIT_PRICE%TYPE;

SNAME ITEM_MASTER.NAME%TYPE;

SRATE NUMBER(10,2);

BEGIN

SELECT NAME,STOCK,UNIT_PRICE INTO SNAME,SSTOCK,SPRICE FROM ITEM_MASTER WHERE

ITEMNO=PITEMNO;

IF(SSTOCK<PQTY)THEN

DBMS_OUTPUT.PUT_LINE('NO SUFFICIENT STOCK');

ELSE

INSERT INTO ITEM_TRANS(ITNO,QTY)VALUES(PITEMNO,PQTY);

UPDATE ITEM_MASTER

SET STOCK=STOCK-PQTY WHERE ITEMNO=PITEMNO;

COMMIT;

SRATE:=(SPRICE*PQTY);

DBMS_OUTPUT.PUT_LINE('ITEM NO:'||' '||PITEMNO);

DBMS_OUTPUT.PUT_LINE('ITEM NAME:'||' '||SNAME);

DBMS_OUTPUT.PUT_LINE('QUANTITY:'||' '||PQTY);

DBMS_OUTPUT.PUT_LINE('PRICE:'||' '||SPRICE);

DBMS_OUTPUT.PUT_LINE('RATE:'||' '||SRATE);

END IF;

END PROC_ITEM;

END PCK_ITEM;

/
SET SERVEROUTPUT ON;

DECLARE

MITEMNO ITEM_MASTER.ITEMNO%TYPE;

MQTY ITEM_MASTER.STOCK%TYPE;

RET NUMBER(2);

BEGIN

MITEMNO:=&ITEMNO;

MQTY:=&QTY;

RET:=PCK_ITEM.CHK_ITEM(MITEMNO);

IF(RET=1) THEN

PCK_ITEM.PROC_ITEM(MITEMNO,MQTY);

ELSE

DBMS_OUTPUT.PUT_LINE('NO SUCH ITEM AVAILABLE');

END IF;

END;