BCA III SEM, DBMS LAB MANUAL

DBMS Lab Manual
University of Mysore SEP BCA III SEM

Overview

• Semester: III
• Course Code: CAM32P
• Course Title: DBMS Lab
• Course Credits: 02 (0-0-2)
• Hours/Week: 04
• Total Contact Hours: 60
• Formative Assessment Marks: 10
• Exam Marks: 40
• Exam Duration: 03

Course Outcomes (COs)

• CO1: Execute single-line SQL queries and apply group functions effectively.
• CO2: Perform database operations using DDL, DML, DCL, and TCL commands.
• CO3: Implement advanced SQL concepts like nested queries and join operations.
• CO4: Create views and apply table-level locking mechanisms for data control.

Laboratory Program List

Part A

Activity 1: Database: Student (DDL, DML Statements)

Table: Student

Name	Reg. No	Class	Major
Smith	17	1	CS
Brown	8	2	CS

Table: Course

Course Name	Course Number	Credit Hours	Department
Introduction to Computer Science	CS1310	4	CS
Data Structure	CS3320	4	CS
Discrete Mathematics	MATH2410	3	MATH
Database Management System	CS3380	3	CS

Table: Section

Section Identifier	Course Number	Year	Instructor
85	MATH2410	98	King
92	CS1310	98	Andreson
102	CS3320	99	Knuth
112	MATH2410	99	Chang
119	CS1310	99	Andreson
135	CS3380	99	Stone

Page 11 of 50

Table: Grade_Report

Reg. No	Section Identifier	Grade
17	112	B
17	119	C
8	85	A
8	92	A
8	102	B
8	135	A

Queries

1. Create Table Using create statement.
2. Insert rows into individual Tables using insert statement.
3. Alter table section add new field section and update the records
4. Delete brown’s grade report.
5. Drop the table section.

Page 12 of 50

Activity 2: (Select clause, Arithmetic Operators)

Database: Employee - Create the following tables and insert tuples with suitable constraints.

Table: EMPLOYEE

EMPID	FIRSTNAME	LASTNAME	Hire Date	ADDRESS	CITY
1001	George	Smith	11-May-06	83 First Street	Paris
1002	Mary	Jones	25-Feb-08	842 Vine Ave	Losantiville
1012	Sam	Tones	12-Sep-05	33 Elm St.	Paris
1015	Peter	Thompson	19-Dec-06	11 Red Road	Paris
1016	Sarath	Sharma	22-Aug-07	440 MG Road	New Delhi
1020	Monika	Gupta	07-Jun-08	9 Bandra	Mumbai

Table: EMPSALARY

EMPID	SALARY	BENEFITS	DESIGNATION
1001	10000	3000	Manager
1002	8000	1200	Salesman
1012	20000	5000	Director
1015	6500	1300	Clerk
1016	6000	1000	Clerk
1020	8000	1200	Salesman

Queries

6. Display FIRSTNAME, LASTNAME, ADDRESS, and CITY of all employees living in PARIS
7. Display the content of the employee table in descending order of FIRSTNAME
8. Select FIRSTNAME and SALARY of salesmen
9. Display the FIRSTNAME, LASTNAME, and TOTAL SALARY of all employees where TOTAL SALARY = SALARY + BENEFITS
10. Count the number of distinct DESIGNATIONs from EMPSALARY
11. List the employees whose names have exactly 6 characters
12. Add a new column PHONE_NO to the EMPLOYEE table and update the records
13. List employee names who have joined before 15-Jun-08 and after 16-Jun-07
14. Generate salary slip with Name, Salary, Benefits, HRA=50%, DA=30%, PF=12%, and calculate gross salary. Order by gross salary in descending order.

Page 13 of 50

Activity 3: (Logical, Relational Operators)

Database: Library - Create the following tables and insert tuples with suitable constraints.

Table: Books

Book_Id	Book_Name	Author_Name	Publishers	Price	Type	Quantity
C0001	The Klone and I	Lata Kappor	EPP	355	Novel	5
F0001	The Tears	William Hopkins	First Publ	650	Fiction	20
T0001	My First C++	Brain & Brooke	First Publ	350	Text	10
T0002	C++ Brainwork's	A.W. Rossaine	TDH	350	Text	15
F0002	Thunderbolts	Ana Roberts	First Publ	750	Fiction	50

Table: Issued

Book_Id	Quantity_Issued
T0001	4
C0001	5
F0001	2
T0002	5
F0002	8

Queries

15. To show Book name, Author name and price of books of First Publ. publisher.
16. Display Book id, Book name and publisher of books having quantity more than 8 and price less than 500.
17. Select Book id, book name, author name of books which is published by other than ERP publishers and price between 300 to 700.
18. Generate a Bill with Book_id, Book_name, Publisher, Price, Quantity, 4% of VAT “Total”.
19. Display book details with book id's C0001, F0001, T0002, F0002 (Hint: use IN operator).
20. Display Book list other than, type Novel and Fiction.
21. Display book details with author name starts with letter “A”.
22. Display book details with author name starts with letter “T” and ends with “S”.
23. Select Book_Id, Book_Name, Author Name , Quantity Issued where Books.Books_Id = Issued.Book_Id.
24. List the book_name, Author_name, Price. In ascending order of Book_name and then on descending order of price.

Page 14 of 50

Activity 4: (Date Functions)

Database: Lab - Create Following table and insert tuples with suitable constraints.

Table: Equipment Details

No.	ItemName	Cost PerItem	Quantity	Date ofPurchase	Warranty	Operational
1	Computer	30000	9	21/5/07	2	7
2	Printer	5000	3	21/5/06	4	2
3	Scanner	8000	1	29/8/08	3	1
4	Camera	7000	2	13/6/05	1	2
5	UPS	15000	5	21/5/08	1	4
6	Hub	8000	1	31/10/08	2	1
7	Plotter	25000	2	11/1/09	2	2

Queries

25. To select the ItemName purchase before 31/10/07.
26. Extend the warranty of each item by 6 months.
27. Display ItemName , Dateof purchase and number of months between purchase date and present date.
28. To list the ItemName in ascending order of the date of purchase where quantity is more than 3.
29. To count the number, average of costperitem of items purchased before 1/1/08.
30. To display the minimum warranty , maximum warranty period.
31. To Display the day of the date , month , year of purchase in characters.
32. To round of the warranty period to month and year format.
33. To display the next Sunday from the date “07-JUN-96”.
34. To list the ItemName, which are within the warranty period till present date.

Page 15 of 50

Part B

Activity 5: (Numeric, character functions)

Use Functions for the following operations.

35. Find the mod of 165,16.
36. Find Square Root of 5000.
37. Truncate the value 128.3285 to 2 and -1 decimal places
38. Round the value 92.7683 to 2 and -1 decimal places.
39. Convert the string ‘Department’ to uppercase and lowercase.
40. Display your address convert the first character of each word to uppercase and rest are in lowercase.
41. Combine your first name and last name under the title Full name.
42. A) Take a string length maximum of 15 displays your name to the left. The remaining space should be filled with '*'.
43. Take a string length maximum of 20 displays your name to the right. The remaining space should be filled with '#'.
44. Find the length of the string ‘JSS College, Mysore’.
45. Display substring ‘BASE’ from ‘DATABASE’.
46. Display the position of the first occurrence of character ‘o’ in Position and Length.
47. Replace string Database with Data type.
48. Display the ASCII value of ‘ ’ (Space).
49. Display the Character equivalent of 42.

Page 16 of 50

Activity 6: Database: subject

Create the following table and insert tuples with suitable constraints.

Table: Physics

Regno	Name	Year	Combination
AJ00325	Ashwin	First	PCM
AJ00225	Swaroop	Second	PMCs
AJ00385	Sarika	Third	PME
AJ00388	Hamsa	First	PMCs

Table: Computer Science

Regno	Name	Year	Combination
AJ00225	Swaroop	Second	PMCs
AJ00296	Tejas	Second	BCA
AJ00112	Geetha	First	BCA
AJ00388	Hamsa	First	PMCs

Queries

50. Select all students from Physics and Computer Science.
51. Select student common in Physics and Computer Science.
52. Display all student details who are studying in the second year.
53. Display students who are studying both Physics and Computer Science in the second year.
54. Display the students studying only Physics.
55. Display the students studying only Computer Science.
56. Select all students having PMCs combination.
57. Select all students having BCA combination.
58. Select all students studying in the third year.
59. Rename table Computer Science to CS.

Page 17 of 50

Activity 7: (views)

Database: Railway Reservation System. Create the following table and insert tuples with suitable constraints.

Table: Train Details

Train_No	Train_Name	Start_Place	Destination
RJD16	Rajdhani Express	Bangalore	Mumbai
UDE04	Udhyan Express	Chennai	Hyderabad
KKE55	Karnataka Express	Bangalore	Chennai
CSE3	Shivaji Express	Coimbatore	Bangalore
JNS8	Janashatabdi	Bangalore	Salem

Table: Availability

Train_No	Class	Start_Place	Destination	No_of_seats
RJD16	Sleeper Class	Bangalore	Mumbai	15
UDE04	First Class	Chennai	Hyderabad	22
KKE55	First Class AC	Bangalore	Chennai	15
CSE3	Second Class	Coimbatore	Bangalore	8
JNS8	Sleeper Class	Bangalore	Salem	18

Queries

60. Create a view sleeper to display train number, start place, destination which have sleeper class and perform the following:
    • Insert new record.
    • Update destination='Manglore' where train no='RJD16'.
    • Delete a record which has train no='KKE55'
61. Create a view detail to display train number, train name, and class.
62. Create a view total_seats to display train number, start place, use COUNT function on No_of_seats, group by start place and perform the following:
    • Insert new record.
    • Update start place='Hubli' where train no='JNS8'.
    • Delete the last row of the view.
63. Rename view sleeper to class.
64. Delete view details.

Page 18 of 50

Activity 8: (group by, having clause)

Database: Bank system - Create the following table and insert tuples with suitable constraints.

Table: Account

Account_No	Cust_Name	Branch_ID
AE0012856	Reena	SB002
AE1185698	Akhil	SB001
AE1203996	Daniel	SB004
AE1225889	Roy	SB002
AE8532166	Sowparnika	SB003
AE8552266	Anil	SB003
AE1003996	Saathwik	SB004
AE1100996	Swarna	SB002

Table: Branch

Branch_ID	Branch_Name	Branch_City
SB001	Malleshwaram	Bangalore
SB002	MG Road	Bangalore
SB003	MG Road	Mysore
SB004	Jayanagar	Mysore

Table: Depositor

Account_No	Branch_ID	Balance
AE0012856	SB002	12000
AE1203996	SB004	58900
AE8532166	SB003	40000
AE1225889	SB002	150000

Table: Loan

Account_No	Branch_ID	Balance
AE1185698	SB001	102000
AE8552266	SB003	40000
AE1003996	SB004	15000
AE1100996	SB002	100000

Page 19 of 50

Queries

65. Display the total number of accounts present in each branch.
66. Display the total loan amount in each branch.
67. Display the total deposited amount in each branch in descending order.
68. Display the maximum and minimum loan amount present in each city.
69. Display the average amount deposited in each branch for each city.
70. Display the maximum loan amount in each branch where the balance is more than 25000.
71. Display the total number of accounts present in each city.
72. Display all customer details in ascending order of branch ID.
73. Update the balance to 26000 where Account_No = AE1003996.
74. Display customer names with their branch name.

Evaluation Scheme

The laboratory program is structured around a comprehensive Evaluation Scheme for Lab Examination specified by the University of Mysore guidelines for the SEP BCA III SEM Curriculum.

Resources & Downloads

Access the complete high-quality lab manual PDF containing precise execution scripts and configurations.

Download Full Lab Manual

Prepared by: Yashavanth K

Founder and CEO of STUDY WITH YASHVANTH

© STUDY WITH YASHVANTH - YK UNIVERSE STUDIOS all rights reserved