UTTARANCHAL UNIVERSITY

(Established vide Uttaranchal University Act, 2012, Uttarakhand Act No. 11 of 2013)
`Premnagar-248007, Dehradun, Uttarakhand, INDIA

Programme Name BCA Programme Code 09

Course Code BCA-C211 Credit 1
Year/Semester 2/3 L-T-P 0-0-2
Course Name Database Management Systems Lab
Course Objective:
The course objective is to provide hands-on session on the theoretical concepts in database
management. The student learns Implementation using queries for DDL, DML, TCL etc. using
SQL queries. The students learn to perform various operations on database including: creation,
updation, deletion of database objects. The lab sessions enhances the student’s practical
Implementation skills.
List of Practical: (30 Hrs)

Practical No. 1. Create a table named uscs_employee having attributes such as: Employee id,
Employee name, Employee’s department number, Employee’s date of joining, Employee’s
salary, Employee’s email_id and Employee’s contact number.
Consider the following table:
Attribute Datatype Size

employee_id Varchar2 10

employee_name Char 25

employee_department_no Number 03

employee_date_of_joining Date -

employee_salary Number 8,2

employee_email_id Varchar2 30

employee_contact_no Number 12

Note: Insert department number values as 111, 222, 333, 444, ……..etc.
Note: Insert employee id values as UU1001, UU1002, UU1003, UU1004, ……..etc.
Write SQL queries to:
i) Insert at least 10 tuples in the table.
ii) Display employee’s complete details including employee_id, employee_name,
employee_department_no, employee_date_of_joining, employee_salary,
employee_email_idandemployee_contact_no.
iii) Display employee’s complete details including employee_id, employee_name,
employee_department_no, employee_date_of_joining, employee_salary,
employee_email_idandemployee_contact_nowho work in department number 444.

Uttaranchal University-Syllabus for Bachelor of Computer Applications (BCA) w.e.f. 2024-25 4.74
 UTTARANCHAL UNIVERSITY
(Established vide Uttaranchal University Act, 2012, Uttarakhand Act No. 11 of 2013)
`Premnagar-248007, Dehradun, Uttarakhand, INDIA
iv) Display employee_id, employee_name and employee_date_of_joining who work in
work in department number 333.
v) Delete the employee’s details having employee_idas UU1003.
vi) Update employee_contact_no to 9592929295 having employee_idas UU1007.

Practical No. 2. Implement DDL and DML on the uim_employee table.

Consider the following employee table:

Attribute Datatype Size

employee_id Varchar2 10

employee_name Char 25

employee_department_no Number 03

employee_date_of_joining Date -

employee_salary Number 8,2

employee_email_id Varchar2 30

employee_contact_no Number 12

i) Add a new column named employee_addresshaving data type as varchar2, size 30 in the
uim_employee table.
ii) Update the addresses of existing uim_employees in the table.
iii) Drop the column named employee_date_of_joining from the table.
iv) Modify the size of the column named employee_contact_no to 14.
v) Rename the table to employee_details from the table name uim_employee.
vi) Truncate as the records from the employee_details table.
vii) Drop the table named employee_details.
Practical No. 3. Implementation of keys and constraints concept. Create a table named
uim_student having attributes such as: student’s roll number, student’s name, student’s date of
birth, student’s course, student’s house address, student’s contact number, student’s aadhaar
number. The attribute named: student_roll_no has a PRIMARY KEY constraint, student_name
has NOT NULL constraint, student_ aadhaar _no as UNIQUE constraint.
Consider the following table:
Attribute Datatype Size Constraint

student_roll_no Number 3 PRIMARY KEY

student_name Char 25 NOT NULL

Uttaranchal University-Syllabus for Bachelor of Computer Applications (BCA) w.e.f. 2024-25 4.75
 UTTARANCHAL UNIVERSITY
(Established vide Uttaranchal University Act, 2012, Uttarakhand Act No. 11 of 2013)
`Premnagar-248007, Dehradun, Uttarakhand, INDIA
student_date_of_birth Date - -

student_course Varchar 15 -

student_address Varchar2 30 -

student_contact_no Number 10 -

student_aadhaar_no Number 12 UNIQUE

i) Describe the structure of uim_student table.

ii) Insert few tuples in the table.
iii) Examine the error message by inserting same student_roll_no values for two rows. Write
the error message and reason.
iv) Examine the error message by NOT inserting student_name value in a row in the table.
Write the error message and reason.
v) Examine the error message by inserting same student_ aadhaar_no values for two rows.
Write the error message and reason.
Practical No. 4. Implementation of Foreign key concept using two tables named:
uscs_employee and uscs_department. The employee table has employee’s id, employee’s name
and employee’s department number. The department table has department number, department
name and department location.
Consider the following two tables:
Table name: uim_employee Table name: uim_department
Attribute Data type Size Constrain Attribute Data Siz Constrain
t type e t

employee_id Char 8 - department_no Number 3 PRIMAR

Y KEY

employee_name Varchar2 20 - department_name Char 15 -

employee_departmen Number 3 FOREIG department_locatio Varchar 20 -

t_no N KEY n 2

i) Display the structure of uscs_employee table

ii) Display the structure of uscs_department table
iii) Insert at least three department details in the uscs_department table.
iv) Display the data of uscs_department table.
v) Insert employee’s details working in the corresponding departments as in the
uscs_department table.
vi) Display the data of uscs_department table.
vii) Examine the error message by inserting a value in employee_department_no which is
NOT there in uscs_department table’s department_no. Write the error message and
reason.
Uttaranchal University-Syllabus for Bachelor of Computer Applications (BCA) w.e.f. 2024-25 4.76
 UTTARANCHAL UNIVERSITY
(Established vide Uttaranchal University Act, 2012, Uttarakhand Act No. 11 of 2013)
`Premnagar-248007, Dehradun, Uttarakhand, INDIA
viii) Delete any department number from the uim_department table and examine its effects
in uim_employee table.
Practical No. 5. Create a table named employee_contact_details from employee table by
taking the attribute named: employee_id.
Consider the table below:
Table name: employee_contact_details

Attribute Datatype Size

employee_id employee_id from employee table

i) Display the contents of employee_contact_detailstable

ii) Add a new column employee_contact_no having data type as Number and size as 12 in
employee_contact_detailstable
iii) Display the contents of employee_contact_details table.
iv) Update the contact details of existing employees
v) Display the updated contents of employee_contact_details table
Practical No. 6. Create a table named uim_book having the attributes related to book id, book
name and book theme. The book_theme attribute can have only two values: BCA or MCA.
Apply CHECK constraint on the attribute named book_theme.
Consider the table below:
Table name: book

Attribute Datatype Size Constraint

Book_id Number 10 PRIMARY KEY

book_name Varchar2 25 NOT NULL

book_theme Char 4 CHECK

i) Describe the structure of uscs_book table.

ii) Insert few tuples in the uscs_book table.
iii) Display the contents of uscs_book table.
iv) Examine the error message by inserting a value other than IT/MGT in column named
book_theme. Write the error message and reason.
Practical No. 7. Extract the data from both the tables by performing join. Given two tables
named: uscs_employee and uscs_department. The employee table has attributes related to
employee’s id, employee’s name and employee’s department number. The department table has
attributes related to department number, department name and department location.
Consider the following two tables:
Table name: uim_employee Table name: uim_department

Uttaranchal University-Syllabus for Bachelor of Computer Applications (BCA) w.e.f. 2024-25 4.77
 UTTARANCHAL UNIVERSITY
(Established vide Uttaranchal University Act, 2012, Uttarakhand Act No. 11 of 2013)
`Premnagar-248007, Dehradun, Uttarakhand, INDIA
Attribute Data Siz Constra Attribute Data Siz Constra
type e int type e int

employee_id Char 8 - department_no Numb 3 PRIMA

er RY
KEY

employee_name Varchar2 20 - department_na Char 15 -

me

employee_depart Number 3 FOREI department_lo Varch 20 -

ment_no GN cation ar2
KEY

i) Display the structure of uscs_employee table

ii) Display the structure of uscs_department table
iii) Insert at least three department details in the department table.
iv) Display the data of uscs_department table.
v) Insert employee’s details in uim_employee table who workin the corresponding
departments as in the uscs_department table.
vi) Display the data of uscs_employee table.
vii) Display employee_id, employee_name, department_no and department_name of
employees from both the tables uim_employee and uim_department by performing join.
viii) Display employee_id, department_no, department_nameand department_location of
employees from both the tables uim_employee and uim_department by performing join.
Practical No. 8. The data in the table can be grouped based on certain attributes. Consider the
book table having attributes as book_id, book_name, book_theme and book_price.
Consider the table below:
Table name: uim_book

Attribute Datatype Size Constraint

Book_id Number 10 PRIMARY KEY

book_name Varchar2 25 NOT NULL

book_theme Char 4 CHECK

Note: the book_theme can either be 'cs' or 'it' only.

i) Add a new column named book_price having data type as Number and size as 7,2 in the
exisiting table named uscs_book.
ii) Display the contents of uscs_book table.
iii) Update the book prices of available books in the uscs_book table.
iv) Display the contents after updation in uscs_book table.
v) Find the sum of all the book price based on the book theme.
Uttaranchal University-Syllabus for Bachelor of Computer Applications (BCA) w.e.f. 2024-25 4.78
 UTTARANCHAL UNIVERSITY
(Established vide Uttaranchal University Act, 2012, Uttarakhand Act No. 11 of 2013)
`Premnagar-248007, Dehradun, Uttarakhand, INDIA
vi) Display the maximum book_price in each group of book_theme.
Practical No. 9. There are numerous aggregate functions that can be performed on table(s).
Consider the attributes in the employee table as: employee’s id, employee’s name, employee’s
department number, employee’s Designation, employee’s date of joining, employee’s salary,
employee’s email id, employee’s contact no and employee’s aadhaar number.
Consider the following uu_employee table:

Attribute Datatype Size Constraint

employee_id Varchar2 10 PRIMARY

KEY

employee_name Char 25 NOT NULL

employee_department_no Number 03 NOT NULL

employee_Designation Varchar2 15 NOT NULL

employee_date_of_joining Date - NOT NULL

employee_salary Number 8,2 NOT NULL

employee_email_id Varchar2 30 NOT NULL

employee_contact_no Number 12 NOT NULL

employee_aadhaar_no Number 12 UNIQUE

i) Insert few tuples in the uu_employee table.

ii) Display the contents of uu_employee table
iii) Calculate the sum of salaries of all the employees.
iv) Calculate the sum of salaries of employees working in department number 222.
Practical No. 10.Demonstrate the following based on the uu_employee table.
i) Count the number of employees in the uu_employee table.
ii) Display the maximum salary of employees having employee_Designation as “Assistant
Professor”.
iii) Display the minimum salary of employees having employee_Designationas “Professor”.
iv) Calculate the average of salaries of all the employees.
Practical No. 11.Implement the single-row character function on the customer table. The
customer table as attributes related to customer’s id, customer’s first name, customer’s last
name, customer’s contact number, customer’s house number, customer’s street of address,
customer’s city of address, customer’s state of address and customer’s email id.
Consider the following customer table:
Table name: uim_customer

Uttaranchal University-Syllabus for Bachelor of Computer Applications (BCA) w.e.f. 2024-25 4.79
 UTTARANCHAL UNIVERSITY
(Established vide Uttaranchal University Act, 2012, Uttarakhand Act No. 11 of 2013)
`Premnagar-248007, Dehradun, Uttarakhand, INDIA
Attribute Datatype Size Constraint

customer _id Varchar2 10 PRIMARY KEY

customer _first_name Char 25 NOT NULL

customer _last_name Char 25 -

customer _contact_no Number 03 NOT NULL

customer _house_no Number 03 -

customer _home_street Varchar2 15 -

customer _home_city Varchar2 15 -

customer _home_state Varchar2 15 -

customer _ email_id Date - -

i) Insert few tuples in the uscs_customer table

ii) Concatenate customer_first_name and customer_last_name in display.
iii)Display the customer_first_name in upper case.
iv) Display the customer_ home_city in with first letter as capital and remaining in lower case
(e.g. Dehradun).
Practical No. 12.Implement the date functions on uu_employee table. Consider the attributes
in the employee table as: employee’s id, employee’s name, employee’s department number,
employee’s Designation, employee’s date of joining, employee’s salary, employee’s email id,
employee’s contact no, employee’s aadhaar number and employee’s date of resigning.
Consider the following employee table:
Attribute Datatype Size Constraint
employee_id Varchar2 10 PRIMARY KEY
employee_name Char 25 NOT NULL
employee_department_no Number 03 NOT NULL
employee_Designation Varchar2 15 NOT NULL
employee_date_of_joining Date - NOT NULL
employee_salary Number 8,2 NOT NULL
employee_email_id Varchar2 30 NOT NULL
employee_contact_no Number 12 NOT NULL

Uttaranchal University-Syllabus for Bachelor of Computer Applications (BCA) w.e.f. 2024-25 4.80
 UTTARANCHAL UNIVERSITY
(Established vide Uttaranchal University Act, 2012, Uttarakhand Act No. 11 of 2013)
`Premnagar-248007, Dehradun, Uttarakhand, INDIA
employee_aadhaar_no Number 12 UNIQUE
employee_date_of_resigning Date -
i) Describe the structure of uu_employee table.
ii) Add a new column named employee_date_of_resigning having date data type in
uim_employee table
iii) Update the data in uu_employee table those who have resigned (wherever applicable)
iv) Display the number of months between employee’s joining and resignation. (Note: There
might be few employees who have resigned).
v) Display the last day of the month in which employees have resigned.
vi) Calculate the working employee’s experience in the current organization. (Note: Use
sysdate)
Course Outcomes(CO):
Students completing the course would be able to:
CO1 Demonstrate the concepts of creation of tables, views and indexes.
CO2 Demonstrate the concept of keys and constraints on the tables.
CO3 Illustrate the concept of data and structure manipulation.
CO4 Demonstrate the use of various aggregate functions in SQL on data.
CO5 Illustrate the data segregation using various operators in SQL.

Examination Scheme:

Components Internal ETE Total

Weightage (%) 40% 60% 100%

CO-PO Articulation Matrix

CO-PO PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8

CO1 - - 2 - 2 - - -
CO2 - 2 2 - 2 - 3 -
CO3 - - 2 - - - 3 -
CO4 - - 2 - - - 3 -
CO5 - - 2 - - - 3 -
Articulated Average - 2 2 - 2 - 3 -

Uttaranchal University-Syllabus for Bachelor of Computer Applications (BCA) w.e.f. 2024-25 4.81