Course Objective
The objective of this laboratory is to provide hands-on experience in designing,
implementing, and manipulating relational databases using SQL and PL/SQL.
Students will learn database schema design, query processing, views, triggers,
procedures, joins, and transaction control statements.
System Configuration
- Processor: Intel Core i3 / higher
- RAM: 8 GB
- Storage: 256 GB SSD
- Operating System: Windows 10 / Linux
Required Software
- Oracle Database / MySQL
- SQL Developer / MySQL Workbench
- Command Line SQL Client
List of Experiments
Experiment 1: Library Database
Consider the following schema:
BOOK (Book_id, Title, Publisher_Name, Pub_Year)
BOOK_AUTHORS (Book_id, Author_Name)
PUBLISHER (Name, Address, Phone)
BOOK_COPIES (Book_id, Programme_id, No_of_Copies)
BOOK_LENDING (Book_id, Programme_id, Card_No, Date_Out, Due_Date)
LIBRARY_PROGRAMME (Programme_id, Programme_Name, Address)
Tasks:
- Retrieve details of all books in the library including title, publisher, authors and number of copies.
- Get the borrowers who borrowed more than 3 books between Jan 2025 and Jun 2025.
- Delete a book from BOOK table and update related tables.
- Partition BOOK table based on publication year.
- Create a view showing available books and number of copies.
Experiment 2: College Database
STUDENT (REGNO, SName, Address, Phone, Gender)
SEMSEC (SSID, Sem, Sec)
CLASS (REGNO, SSID)
COURSE (Subcode, Title, Sem, Credits)
IAMARKS (REGNO, Subcode, SSID, Test1, Test2, Test3, FinalIA)
- List student details studying in 4th semester C section.
- Compute number of male and female students in each semester and section.
- Create a view for Test1 marks of student REGNO ‘1BP22CS101’.
- Calculate FinalIA as average of best two tests.
- Categorize students as Outstanding, Average, and Weak based on FinalIA.
Experiment 3: Order Database
SALESMAN (Salesman_id, Name, City, Commission)
CUSTOMER (Customer_id, Cust_Name, City, Grade, Salesman_id)
ORDERS (Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id)
- Count customers with grades above Bangalore’s average.
- Find salesman with more than one customer.
- List salesman with and without customers using UNION.
- Create a view showing salesman with highest order of the day.
- Delete salesman with id 1000 and remove all related orders.
Experiment 4: Privileges and Transactions
Tables:
Departments (dept_no, dept_name, dept_location)
Employees (emp_id, emp_name, emp_salary, dept_no)
- Grant all privileges on employees table.
- Grant selective privileges on employees table.
- Revoke all privileges.
- Revoke selective privileges.
- Implement SAVEPOINT in transactions.
Experiment 5: SQL Joins
- Display employee and department details using INNER JOIN.
- Display employee name and department name using LEFT OUTER JOIN.
- Display employee name and department name using RIGHT OUTER JOIN.
- Display employees with salary greater than average salary.
Experiment 6: Election Commission Database
CONSTITUENCY (cons_id, csname, csstate, no_of_voters)
PARTY (pid, pname, psymbol)
CANDIDATES (cand_id, name, age, state, phone_no)
VOTER (vid, vname, vage, vaddr)
- Find candidates contesting in more than one constituency in different states.
- Display the state having maximum constituencies.
- Create stored procedure to insert voter if age ≥ 18.
- Create stored procedure to display number of voters in a constituency.
- Create trigger to update voter count after voter insertion.
Experiment 7: Airline Flight Database
FLIGHTS (flno, distance, departs, arrives)
AIRCRAFT (aid, aname, cruisingrange)
CERTIFIED (eid, aid)
EMPLOYEES (eid, ename, salary)
- Find pilots certified for Boeing aircraft.
- Find aircraft that can fly non-stop from Delhi to Mumbai.
- Find pilots certified for aircraft with range greater than 3000 miles.
- Find employees with highest and second highest salary.
- Find pilots certified for largest number of aircraft.
- Find total salary paid to employees.
Experiment 8: Cursor Implementation
Employee (E_id, E_name, Age, Salary)
- Create a cursor to extract employee records.
- Declare variables, open cursor, fetch values, and close cursor.
Experiment 9: Stored Procedure – Student Grading
Tables:
Stud_Marks (Name, Total_Marks)
Result (Roll, Name, Class)
- Create stored procedure proc_Grade to categorize students based on marks.
- Use PL/SQL block to call the procedure.
- Classify students as Distinction, First Class, and Higher Second Class.
Experiment 10: PL/SQL Fine Calculation
Borrower (Rollin, Name, Date_of_Issue, Name_of_Book, Status)
Fine (Roll_no, Date, Amt)
- Accept Roll number and book name from user.
- Calculate fine based on number of days after issue.
- Update book status after submission.
- Store fine details if applicable.
- Use control structures and exception handling.
