RDBMS Lab Exercises

This page provides detailed RDBMS Lab exercises covering SQL DDL, DML, and TCL commands, advanced queries using joins and constraints, aggregate functions, set operations, and hands-on PL/SQL programming with cursors, triggers, functions, and stored procedures.

RDBMS Lab Exercises

DDL Commands

Consider the following Schema Supplier(SID, Sname, branch, city, phone) Part(PID, Pname, color, price) Supplies(SID, PID, qty, date_supplied)

• Create the above tables
• Add a new attribute state in supplier table
• Remove attribute city from supplier table
• Modify the data type of phone attribute
• Change the name of attribute city to address
• Change a table’s name, supplier to sup
• Use truncate to delete the contents of supplies table
• Remove the part table from database

DML Commands

1. Insert at least 10 records in tables supplier, part and supplies
2. Show the contents in tables supplier, part and supplies
3. Find the name and city of all suppliers
4. Find the name and phoneno of all suppliers who stay in ‘Delhi’
5. Find all distinct branches of suppliers
6. Delete the record of the supplier whose SID is 204001
7. Delete all records of supplier table
8. Delete all records of suppliers whose city starts with capital A.
9. Find the supplier names which have ‘lk’ in any position
10. Find the supplier name where ‘R’ is in the second position
11. Find the name of supplier whose name starts with ‘V’ and ends with ‘A’
12. Change the city of all suppliers to ‘BOMBAY’
13. Change the city of supplier ‘Vandana’ to ‘Goa’

Queries with Constraints

1. Create the supplier table with Primary Key Constraint
2. Create supplies table with Foreign key Constraint
3. Create a part table with UNIQUE Constraint
4. Create supplier Table with Check Constraints
5. Create Supplier table with Default Constraint

Queries on TCL

1. Create Savepoints
2. Rollback to SavePoints
3. Use Commit to save on Aggregate Functions:
4. Find the minimum, maximum, average and sum of costs of parts
5. Count the total number of parts present
6. Retrieve the average cost of all parts supplied by ‘Mike’ Queries on GROUP BY

HAVING AND ORDER BY Clauses

1. Display total price of parts of each color
2. Find the branch and the number of suppliers in that branch for branches which have more then 2 suppliers
3. Find all parts sorted by pname in ascending order and cost in descending order
4. Find the branch and the number of suppliers in that branch Queries on Analytical

Queries on Operators

1. Find the pname, phoneno and cost of parts which have cost equal to or greater than 200 and less than or equal to 600.
2. Find the sname , SID and branch of suppliers who are in ‘local’ branch or ‘global’ branch
3. Find the pname, phoneno and cost of parts for which cost is between 200 and 600
4. Find the pname and color of parts, which has the word ‘NET’ anywhere in its pname.
5. Find the PID and pname of parts with pname either ‘NUT’ or ‘BOLT’
6. List the suppliers who supplied parts on ‘1st may2000’, ‘12 JAN 2021’ ,’17 dec 2000’,’10 Jan 2021’
7. Find all the distinct costs of parts

Join Operators

1. Perform Inner join on two tables
2. Perform Natural Join on two tables
3. Perform Left Outer Join on tables
4. Perform Right Outer join on tables
5. Perform Full Outer Join on tables Set Theory Operators
6. Show the use of UNION operator with union compatibility
7. Show the use of intersect operator with union compatibility
8. Show the use of minus operator with union compatibility
9. Find the Cartesian product of two tables

Demonstration on PL/SQL block, cursor, trigger, functions and stored procedure.

SOME MORE:  

  JOIN NOW