MCA 207: DBMS Lab

Lectures: 6 Periods/Week Sessional Marks: 30
University Exam: 3 Hours University Examination Marks: 70


Lab cycle
Cycle I:-
Aim:Marketing Company wishes to computerize their operations by using following tables.
Table Name: Client_Master
Description:This table stores the information about the clients.

Column Name Data Type Size Attribute
Client_no Varchar2 6 Primary Key and first letter should starts with 'C'
Name Varchar2 10 Not null
Address1 Varchar2 10
Address2 Varchar2 10
City Varchar2 10
State Varchar2 10
Pincode Number 6 Not null
Bal_due Number 10,2


Table Name:Product_master

Description:This table stores the information about products.

Column Name Data Type Size Attribute
Product_no Varchar2 6 Primary Key and first letter should starts with 'P'
Description Varchar2 10 Not null
Profit_percent Number 2,2 Not null
Unit_measure Varchar2 10
Qty_on_hand Number 8
Record_lvl Number 8
Sell_price Number 8,2 Not null, can't be 0
Cost_price Number 8,2 Not null, can't be 0


Table Name:salesman_master

Description:This table stores the salesmen working in the company

Column Name Data Type Size Attribute
Salesman_id Varchar2 6 Primary Key and first letter should starts with 'S'
Name Varchar2 10 Not null
Address1 Varchar2 10
Address2 Varchar2 10
City Varchar2 10
State Varchar2 10
Pincode Number 6 Not null
Sal_amt Number 8,2 Should not null and zero
Target_amt Number 6,2 Should not null and zero
Remarks Varchar2 10


Table Name:sales_order

Description:This table stores the information about orders

Column Name Data Type Size Attribute
S_order_no Varchar2 6 Primary Key and fisrt char is 'O'
S_order_date Date
Client_no Varchar2 6 Foreign key
Delve_address Varchar2 20
Salesman_no Varchar2 6 Foreign key
Delve_type Varchar2 1 Delivery: part(P)/Full(F) and default 'F'
Billed_yn Char 1
Delve_date Date Can't be less than the s_order_date
Order_status Varchar2 10 Values in 'IN PROCESS', FULFILLED', 'BACK ORDER, 'CANCELLED'
Remarks Varchar2 10


Table Name:sales_order_details

Description:This table stores the information about products ordered

Column Name Data Type Size Attribute
S_order_no Varchar2 6 Primary key, foreign key references sales_order table
Product_no Varchar2 6 Primary key, foreign key references product_master table
Qty_ordered Number 8
Qty_disp Number 8
Product_rate Number 10,2


Table Name:challan_master

Description:This table stores the information about challansmade for orders.

Column Name Data Type Size Attribute
Challan_no Varchar2 6 Primary key, first two letters must start with 'CH'
S_order_no Varchar2 6 Foreign key references sales_order
Challan_date Date
Billed_yn Char 1 Values in 'Y', 'N' default 'N'


Table Name:Challan_Details

Description:This table stores the information about challan details.

Column Name Data Type Size Attribute
Challan_no Varchar2 6 Primary key, foreign key references challan_master table
Product_no Varchar2 6 Primary key, foreign key references product_master table
Qty_disp Number 4,2 Not null


Solve the following queries by using above tables
  1. Retrieve the list of names and cities of all the clients.
  2. List the various products available from product_master.
  3. Find out the clients who stay in a city whose second letter is 'a'.
  4. Find the list of all clients who stay in the city 'CHENNAI' or 'DELHI'.
  5. List all the clients located at 'CHENNAI'.
  6. Print the information from sales order as the order the places in the month of January.
  7. Find the products with description as 'Floppy Drive' and 'Pen drive'.
  8. Find the products whose selling price is greater than 2000 and less than or equal to 5000.
  9. Find the products whose selling price is more than 1500 and also find the new selling price as original selling price *15.
  10. Find the products in the sorted order of their description.
  11. Divide the cost of product '540 HDD' by difference between its price and 100.
  12. List the product number, description, sell price of products whose description begin with letter 'M'.
  13. List all the orders that were cancelled in the month of March.
  14. Count the total number of orders.
  15. Calculate the average price of all the products.
  16. Determine the maximum and minimum product prices.
  17. Count the number of products having price grater than or equal to 1500.
  18. Find all the products whose quantity on hand is less than reorder level.
  19. Find out the challan details whose quantity dispatch is high.
  20. Find out the order status of the sales order, whose order delivery is maximum in the month of March.
  21. Find out the total salesmade by the each salesman.
  22. Find the total revenue gained by the each product sales in the period of Q1 and Q2 of year 2006.
  23. Print the description and total qty sold for each product..
  24. Find the value of each product sold.
  25. Calculate the average qty sold for each client that has a maximum order value of 1,50,000.
  26. List the products which has highest sales.
  27. Find out the products and their quantities that will have to deliver in the current month.
  28. Find the product number and descriptions of moving products.
  29. Find the names of clients who have purchased 'CD DRIVE'.
  30. List the product numbers and sales order numbers of customers having quantity ordered less than 5 fromthe order details for the product '1.44 Floppies'.
  31. Find the product numbers and descriptions of non-moving products.
  32. Find the customer names and address for the clients, who placed the order '019001'.
  33. Find the client names who have placed orders before the month of May, 2006.
  34. Find the names of clients who have placed orders worth of 10000 or more.
  35. Find out if the product is '1.44 drive' is ordered by any client and print the client number, name to whom it is sold.

Cycle II:-

Aim: A Manufacturing Company deals with various parts and various suppliers supply these parts. It consists of three tables to record its entire information. Those are as follows

S(SNO,SNAME,CITY,STATUS)
P(PNO,PNAME,COLOR,WEIGTH,CITY,COST)
SP(SNO,PNO,QTY)
J(JNO,JNAME,CITY)
SPJ(SNO,PNO,JNO,QTY)

  1. Get Suppliers Names for Suppliers who supply at least one red part.
  2. Get Suppliers Names for Suppliers who do not supply part 'P2'
  3. Using Group by with Having Clause, Get the part numbers for all the parts supplied by more than one supplier.
  4. Get supplier numbers for suppliers with status value less the current max status value.
  5. Get the total quantity of the part 'P2' supplied.
  6. Get the part color, supplied by the supplier 'S1'
  7. Get the names of the parts supplied by the supplier 'Smith' and "Black"
  8. Get the Project numbers, whose parts are not in Red Color, from London.
  9. Get the suppliers located from the same city.
  10. Get the suppliers, who does not supply any part.
  11. Find the pnames of parts supplied by London Supplier and by no one else.
  12. Find the sno's of suppliers who charge more for some part than the average cost of that part.
  13. Find the sid's of suppliers who supply only red parts.
  14. Find the sid's of suppliers who supply a red and a green part.
  15. Find the sid's of suppliers who supply a red or green part.
Cycle III:-

An Airline System would like to keep track their information by using the following relations.

Flights (flno: integer, from: string, to: string, distance: integer, Price: integer)
Aircraft (aid: integer, aname: string, cruising_range: integer)
Certified (eid: integer, aid: integer)
Employees (eid: integer, ename: string, salary: real)

Note that the employees relation describes pilots and other kinds of employees as well; every pilot is certified for aircraft and only pilots are certified to fly. Resolve the following queries:

  1. For each pilot who is certified formore than three aircraft, find the eid's and the maximum cruising range of the aircraft that he (or She) certified for.
  2. Find the names of pilots whose salary is less than the price of the cheapest route from Los Angeles to Honolulu.
  3. Find the name of the pilots certified fromsome Boeing aircraft.
  4. For all aircraft with cruising range over 1,000 miles, find the name of the aircraft and the average salary of all pilots certified for this aircraft.
  5. Find the aid's of all aircraft than can be used from Los Angels to Chicago.
  6. Print the enames of pilots who can operate planes with cruising range greater than 3,000 miles, but are not certified by Boeing aircraft.
  7. Find the total amount paid to employees as salaries.
  8. Find the eid's of employees who are certified for exactly three aircrafts.
  9. Find the eid's of employee who make second highest salary.
  10. Find the aid's of all than can be used on non-stop flights from Bonn to Chennai.
Cycle IV :Employee Database

Aim:An enterprise wishes to maintain a database to automate its operations. Enterprise divided into to certain departments and each department consists of employees. The following two tables describes the automation schemas

DEPT (DEPTNO, DNAME, LOC)
EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)

  1. Create a view, which contain employee names and their manager names working in sales department.
  2. Determine the names of employee, who earn more than there managers.
  3. Determine the names of employees, who take highest salary in their departments.
  4. Determine the employees, who located at the same place.
  5. Determine the employees, whose total salary is like the minimum salary of any department.
  6. Update the employee salary by 25%, whose experience is greater than 10 years.
  7. Delete the employees, who completed 32 years of service.
  8. Determine the minimumsalary of an employee and his details, who join on the same date.
  9. Determine the count of employees, who are taking commission and not taking commission.
  10. Determine the department does not contain any employees.
  11. Find out the details of top 5 earners of company. (Note: Employee Salaries should not be duplicate like 5k,4k,4k,3k,2k)
  12. Display thosemanagers name whose salary is more than an average salary of his employees.
  13. Display the names of the managers who is having maximum number of employees working under him?
  14. In which year didmost people join the company? Display the year and number of employees.
  15. Display ename, dname even if there no employees working in a particular department(use outer join).
PL/SQL PROGRAMS

  1. WRITE A PL/SQL PROGRAM TO CHECK THE GIVEN NUMBER IS STRONG OR NOT.
  2. WRITE A PL/SQL PROGRAM TO CHECK THE GIVEN STRING IS PALINDROME OR NOT.
  3. WRITE A PL/SQL PROGRAM TO SWAP TWO NUMBERS WITHOUT USING THIRD VARIABLE.
  4. WRITE A PL/SQL PROGRAM TO GENERATE MULTIPLICATION TABLES FOR 2, 4, 6
  5. WRITE A PL/SQL PROGRAM TO DISPLAY SUM OF EVEN NUMBERS AND SUM OF ODD NUMBERS IN THE GIVEN RANGE.
  6. WRITE A PL/SQL PROGRAM TO CHECK THE GIVEN NUMBER IS PALLINDROME OR NOT.
  7. THE HRD MANAGER HAS DECIDED TO RAISE THE EMPLOYEE SALARY BY 15%. WRITE A PL/SQL BLOCK TO ACCEPT THE EMPLOYEE NUMBER AND UPDATE THE SALARY OF THAT EMPLOYEE. DISPLAY APPROPRIATE MESSAGE BASED ON THE EXISTENCE OF THE RECORD IN EMP TABLE.
  8. WRITE A PL/SQL PROGRAM TO DISPLAY TOP 10 ROWS IN EMP TABLE BASED ON THEIR JOB AND SALARY.
  9. WRITE A PL/SQL PROGRAM TO RAISE THE EMPLOYEE SALARY BY 10%, FOR DEPARTMENT NUMBER 30 PEOPLE AND ALSO MAINTAIN THE RAISED DETAILS IN THE RAISE TABLE.
  10. WRITE A PROCEDURE TO UPDATE THE SALARY OF EMPLOYEE, WHO ARE NOT GETTING COMMISSION BY 10%.
  11. WRITE A PL/SQL PROCEDURE TO PREPARE AN ELECTRICITY BILL BY USING FOLLOWING TABLE TABLE USED: ELECT
    NAME NULL? TYPE
    MNO NOT NULL NUMBER(3)
    CNAME VARCHAR2(20)
    CUR_READ NUMBER(5)
    PREV_READ NUMBER(5)
    NO_UNITS NUMBER(5)
    AMOUNT NUMBER(8,2)
    SER_TAX NUMBER(8,2)
    NET_AMT NUMBER(9,2)


  12. WRITE A PL/SQL PROCEDURE TO PREPARE AN TELEPHONE BILL BY USING FOLLOWING TABLE. AND PRINT THE MOTHLY BILLS FOR EACH CUSTOMER TABLE USED : PHONE.


    NAME NULL? TYPE
    ---------------- -------- --------
    TEL_NO NOT NULL NUMBER(6)
    CITY VARCHAR2(10)
    PR_READ NUMBER(5)
    CUR_READ NUMBER(5)
    NET_UNITS NUMBER(5)
    TOT_AMT NUMBER(8,2)


  13. WRITE A PL/SQL PROGRAM TO RAISE THE EMPLOYEE SALARY BY 10%, WHO ARE COMPLETED THERE 25 YEARS OF SERVICE AND STORE THE DETAILS AT PPROPRIATE TABLES (DEFINE THE RETAIR_EMP TABLE).
  14. WRITE A PL/SQL PROCEDURE TO EVALUATE THE GRADE OF A STUDENT WITH FOLLOWING CONDITIONS:
    FOR PASS: ALL MARKS > 40
    FOR I CLASS: TOTAL%>59
    FOR II CLASS: TOTAL%BETWEEN >40 AND <60
    FOR III CLASS: TOTAL%=40
    AND ALSOMAINTAIN THE DETAILS IN ABSTRACT TABLE.

    TABLES USED
    1. TABLE STD

    NAME NULL? TYPE
    ---------------- -------- --------
    NO NOT NULL NUMBER
    NAME VARCHAR2(10)
    INTNO NUMBER
    CLASS NOT NULL VARCHAR2(10)
    M1 NUMBER
    M2 NUMBER
    M3 NUMBER
    M4 NUMBER
    M5 NUMBER


    2. TABLE ABSTRACT

    NAME NULL? TYPE
    ---------------- -------- --------
    STDNO NUMBER
    STDNAME VARCHAR2(10)
    CLASS VARCHAR2(10)
    MONTH VARCHAR2(10)
    INTNO (INTERNAL NUMBER) NUMBER
    TOT NUMBER
    GRADE VARCHAR2(10)
    PERCENT NUMBER
    DAT_ENTER DATE


  15. CREATE AN VARRAY, WHICH HOLDS THE EMPLOYEE PHONE NUMBERS (AT LEAST THREE NUMBERS).
  16. CREATE AN OBJECT TO DESCRIBE THE DETAILS OF ADDRESS TYPE DATA.
  17. WRITE A PL/SQL PROCEDURE TO READ THE DATA INTO THE TABLE AS PER THE FOLLOWING DESCRIPTION.
    AttributeName Data Type DETAILS
    EMPLOYEE NUMBER NUMBER
    EMPLOYEE NAME CHARACTER
    ADDRESS OBJECT STREET NUMBER
    STREET NAME
    TOWN
    DIST AND STATE
    QUALIFICATION CHARACTER
    PHONE NUMBER OBJECTVARRAY HOLDS THREE PHONE NUMBER