Course

Programme BCA
Semester 2
Subject Relational Database Management System

Subject Code BCA1203

Credit 4 Lecture 4 Practical / 0
Tutorial
Teaching Scheme 4 Lecture 4 Practical / 0
Hrs / Week Tutorial
Examination 100 50 50
Scheme CIE SEE
Marks

Lab Plan

Sr. List of Experiments

No.
1. [A] Create a table Employee_detail which include these fields, Insert records in the table then perform given
queries:

[B] Data for Employee_detail table:

 [C] Exercise on retrieving records from a table:
1. Retrieve Firstname and lastname of the Employees.
2. Find out the phone number of the employee whose ID is 104.
3. List all employees’ salary is below 10000.
4. Change First name of the employee as “ Girija” whose birthdate is 21 september 1989.
5. Display details of the employee whose salary is 17000.

2. Create a table Client_Master1 which include these fields, Insert records in the table then perform given queries:
[A] Create the tables described below:
Table Name: CLIENT_MASTER
Description: Used to store client information.

[B] Data for CLIENT_MASTER1 tables:

[C] Exercise on retrieving records from a table:

1. Find out the names of all the clients.
2. Retrieve the entire contents of the Client_Master1 table.
3. Retrieve the list of names, city and the state of all the clients.
4. List all the clients who are located in Mumbai.
5. Change the city of Client_No ‘C00005’ to ‘Bangalore’.
6. Change the BalDue of Client_No ‘C00001’ to Rs. 1000.
7. Delete from Client_Master1 where the column state holds the value ‘Tamil Nadu’.
8. Add a column called ‘Telephone’ of data type ‘number’ and size = ‘10’ to the Client_Master1 table.
9. Destroy the table Client_Master1 along with its data.
10. Change the Pincode of Madras 780001 to 560006.
11. Change the size of BalDue column in Client_master to 10,2.
12. List the name of all clients having ‘a’ as the second letter in their names.
13. List the clients who stay in a city whose first letter is ‘M’.
14. List all clients who stay in ‘Bangalore’ or ‘Mangalore’.
15. List all clients whose Baldue is greater than value 10000.
 16. List the name, city and state of clients who are not in the state of ‘Maharashtra’.

3 Create a table Product_Master which include these fields, insert records in the table then perform a given queries:
[A] Create the tables described below:
Table Name: PRODUCT_MASTER
Description: Used to store product information.

[B] Data for PRODUCT_MASTER tables:

[C] Exercise on retrieving records from a table

1. List various products available from the Product_Master1 table.
2. Change the cost price of ‘Trousers’ to Rs. 950.00.
3. Delete all products from Product_Master1 where the quantity on hand is equal to100.
4. Change the size of SellPrice column in Product_Master1 to 10,2.
5. List products whose selling price is greater than 500 and less than or equal to 750.
6. List products whose selling price is more than 500. Calculate a new selling price as original selling price *
15. Rename the new column in the output of the above query as New_Price.
7. Calculate the average price of all the products.
8. Determine the maximum and minimum product prices. Rename the output as Max_Price and Min_Price
respectively.
9. Count the number of products having price less than or equal to 500.
10. List all the products whose QtyOnHand is less than reorder level.
4. Create a table Salesman_Master which include these fields, Insert records in the table then perform a given
queries:
[A] Create the tables described below:
Table Name: SALESMAN_MASTER
Description: Used to store salesman information working for the company.

[B] Data for SALESMAN_MASTER tables:

Create a table Sales_Order which include these fields, Insert records in the table then perform a given queries:
5.
[A] Create the tables described below:
Table Name: SALES_ORDER
Description: Used to store client’s orders.
 [B] Data for SALES_ORDER tables:

[C] Exercise on retrieving records from a table

1. List all information from the Sales_Order table for orders place in the month of June.
2. Count the total number of orders.
3. List the order information for ClientNo ‘C00001’ and ‘C00002’.
4. List the order no and day on which client placed their order.
5. List the month (in alphabets) and date when the orders must be delivered.
6. List the OrderDate in the format ‘DD – Month – YY’ e.g. 12 – February - 02
7. List the date, 15 days after today date.

6 Create a table Sales_Order_Details which include these fields, insert records in the table then perform a given
queries:
[A] Create the tables described below:
Table Name: SALES_ORDER_DETAILS
Description: Used to store client’s orders with details of each product ordered.

[B] Data for SALES_ORDER_DETAILS tables:

 7. Sketch out ER Diagram of above Experiment.
8. Implement SQL queries using Group by, Having and Order by clause.
9. Retrieve data spread across various tables or the same table using various Joins.
10. Implement SQL queries using Date functions like add-months, months-between, round, nextday, truncate etc

11. Implement SQL queries using Numeric functions like abs, ceil,power, mod, round, trunc, sqrt etc. and Character
Functions like initcap, lower, upper, ltrim, rtrim, replace, substring, instr etc.

12. Database name: Movie_database

1. Create Movies, Actor , Acts , Director table as follows with given fields.

2. write a query to fetch details of Fargo movies from all the tables using joins.
3. Perform right join.
4. Perform left join
5. Perform inner join.
6. Perform outer join.
7. perform subqueries on tables.
13. Implement SQL queries using Conversion Functions like to-char, to-date, to-number and Group functions like
Avg, Min, Max, Sum, Count, Decode etc.

14.
Design and Develop the following database to demonstrate using of SQL commands and aggregate functions.
create a table: "Employee".
1) Insert the following data using both insert queries.
 2) Fetch all employee data who are from HR department
3) Fetch all employee whose name is Ved and who is from ahemdabad
4) Update the name of Ved with harsh whose id is 6
5) Update the city of Bhavya replace ahemdabad with Baroda
6) Update whole record and add your record at id 9
7) Fetch the count of total records. (use count function).
8) Fetch total salary of all employee. (use sum function)
9) Fetch minimum salary of employee. (use min function)
10)Fetch maximum salary of employee. (use max function)
11) Fetch unique department of company. (use distinct keyword)
12) Fetch all data in ascending order. (use name field for ascending order)
13) Fetch all data in descending order. (use name field for descending)
14) Find average salary of employee. (use avg function)
15) Create groups for all department. (use groupby clause)
16) Fetch group with its member. (use groupby and having clause)
17) Delete a record whose id is 7.
18) Delete a records who name is Ved.
19) Delete your record with your name.
20) Delete all records.
14. Retrieve data from multiple tables using Subqueries.
15. Implement SQL queries using simple Case Operations and using Group functions and Case operations for getting
summary data.
16. Write a PL/SQL program to add number using function.
17. Write a PL/SQL program to fetch users from table using function.

[Note: Insert data using procedure]

178 Write a PL/SQL program to find factorial number using function.

19. Write a Pl/SQL statement to create procedure called p1 which accept a number & print multiply by 2 on the
screen.
20. Develop a PL/SQL program to perform updatation of above experiment using Cursor. [use implicit cursor].
21. Create following table.
Table name: customers

Now retrieve this data using explicit cursor in PL/SQL.

22 Consider above table customers.

[Link] a trigger to check salary using trigger.
[Link] create a procedure to check difference between salary using procedure.
23. Perform queries to Create synonyms, sequence and index.
24. Create user and give them appropriate grant to object and revoke that grant on object.
25. Create a trigger called tr1 which considers the record of client master table. The given trigger is defined for delete
and update operation only and stored both the values in the client1 table.
26. Write a PL/SQL block to create an object called address which has city & pincode as attributes and used it in to
query.