UNIVERSITY OF ENGINEERING AND MANAGEMENT

Institute of Engineering & Management, Salt Lake Campus

Institute of Engineering & Management, New Town Campus
University of Engineering & Management, Jaipur

Department of Electronics & Communication Engineering

DATABASE MANAGEMENT SYSTEM

​
 Department of Electronics & Communication Engineering

Database Management System Lab (PCCEC 594)

o​NAME:

o​ENROLLMENT NO.:

o​CLASS ROLL NO.:

o​SEMESTER:
Course: Database Management System Lab (PCCEC 594)
PROGRAMME: ELECTRONICS & COMMUNICATION DEGREE: B. TECH
ENGINEERING
COURSE:​ DATABASE MANAGEMENT SYSTEM SEMESTER: 5​ CREDITS: 2

COURSECODE: PCCEC 594 COURSE TYPE: Laboratory

COURSE AREA/DOMAIN: ELECTRONICS & CONTACT HOURS: 3(weekly)
COMMUNICATION ENGINEERING
CORRESPONDING LAB COURSE CODE (IFANY):NA LABCOURSE NAME: DATABASE
MANAGEMENT SYSTEM LAB

Course Objective:
1.​ To explain basic database concepts, applications, data models, schemas and instances.
2.​ To demonstrate the use of constraints and relational algebra operations.
3.​ Describe the basics of SQL and construct queries using SQL.
4.​ To emphasize the importance of normalization in databases.
5.​ To facilitate students in Database design.
6.​ To familiarize issues of concurrency control and transaction management.

Course Outcomes:
Sl. No. Description Blooms Level

Upon completion of the course, the

students will be able to:
CO1 Apply the basic concepts of Database Apply (L3)
Systems and Structured Query Language
to create table and database.
CO2 Handle Table and Records within the Apply (L3)
table.
CO3 Retrieve​ Data​ from​ a​ Database​ Apply (L3)
and manage the database.
CO4 Design​ &​ ​ implement​ a​ Apply (L3 & L6)
commercial relational​ ​ database​
system​ (Oracle,
MySQL) by writing SQL using the system.
 Index
Exp. Experiment Name Date of Date of Signature
No. Experiment Submission
1 ●​ Create and manipulate relational Employee
database tables in SQL containing following
parameters- EMPNO NUMBER, ENAME,
JOB, DEPTNO, SAL.
●​ Allow NULL for all columns except ename
and job
●​ Add a column “experience” to the emp table.
“experience” numeric null allowed.
●​ Modify the column width of the job field of
emp table.
●​ Create the emp1 table with ename and empno,
add constraints to check the empno value
while entering (i.e) empno > 100
●​ Finally use an AI agent (LangChain-based) to
read, interpret, and validate employee data
stored in CSV format.
2 ●​ Create a table called EMPLOYEE table
with name EMP with the fields like
EMPNO, ENAME, JOB, DEPTNO,​SAL
and insert the values.
●​ Write query to select all the columns of
emp table.
●​ Write query to select only Empname,
Ename and Job also select unique Jobs.
●​ Write query to select only those
employees who are salesman.
●​ Select employee name, grade and salary ,
in the order of their salary.
●​ Mgmt is considering a pay raise, however
they want to find out, if they give a flat
200$ increment to all, then what % each
person is getting. Now display the
ename, salary and pctincr. Express work
experience of each of the employee by
using sysdate and hiredate in terms of no
of years.
Execute SQL queries for data analysis, and
use an AI agent (LangChain) to
automatically query and interpret employee
data.
Exp. Experiment Name Date of Date of Signature
No. Experimen Submissio
t n
3 ●​ Create a table called EMPLOYEE table with name
EMP with the fields like EMPNO, ENAME, JOB,
DEPTNO,SAL and insert the values.
●​ List down number of employees, minimum salary,
maximum salary for each department, Update
Email_id, if department id is < 1000 update the
EMAIL field by appending @[Link]
●​ If department id is < 5000 update the EMAIL
field by appending @[Link] c. Else update it
as [Link]
●​ Apart from ‘Delete’ a ‘Truncate’ statement can
also be used for deleting the rows. Comment on
their difference.
●​ Display a department id wise count of employees
-
Getting salary more than 5000
●​ Apart from the above condition, select only those
departments which has an average salary in excess
of 6500
●​ Explain how two levels of filtering is happening
based on firstly where clause secondly having
clause based on this particular scenario
●​ You want to add a new row in the employees table
with employee id 10000, First Name = ‘Scott’ ,
Last Name = ‘Tiger’, Email = Stiger, Hire Date ,
01/02/2014, Job id PR_Prsdnt ( Title ‘Company
President’ ) Department_id 280 (
Department_Name ‘Database’ ) Salary
50000,Issue necessary insert statements.
●​ Display day, month and year of the hire date of
the employees.
Exp. Experiment Name Date of Date of Signatur
No. Experimen Submissio e
t n
4 ●​ Create a table called EMPLOYEE table with name
EMP with the fields like EMPNO, ENAME, JOB,
DEPTNO,SAL,DEPTNAME,LOCATION,
PHONE_NUMBER and insert the values.
●​ Display name of employees, department name and
job name for each employee.
Display the department name along with no. of
employees and average salary of that department.
For each department, find out no. of jobs the
employees are assigned to. Check for correctness
of the above queries in terms of count, if you want
to bring in all entries, how would you achieve the
same? Group by the employees based on the first
character of employee firstname.
●​ Display the results in alphabetic order
(descending) of first character.
5 ●​ Create a table called EMPLOYEE table with name
EMP with the fields like EMPNO, ENAME, JOB,
DEPTNO,SAL,DEPTNAME and insert the values.
●​ To display NAME, LOCATION,
PHONE_NUMBER of an employee from
EMPLOYEE table whose belongs to specific
department.
●​ Create an "Orders" table with attributes
ORDERID, CUSTOMERID and
ORDERDATE & create a “Customers" table with
attributes CUSTOMERID, CUSTOMERNAME,
CONTACTNAME
and COUNTRY. Create the SQL statement that
selects records that have matching values in both
tables.
●​ Write down an SQL query to create a view that
shows all customers from a specific location.
●​ Write down an SQL query to create two aliases,
one for the CustomerID column and one for the
CustomerName column
●​ Compare GRANT & REVOKE command with
proper examples.
Exp. Experiment Name Date of Date of Signature
No. Experimen Submissio
t n
6 Execute the following tasks using PL-SQL:
●​ Display your name five times using FOR Loop.
●​ Write a PL/SQL block of code to convert a
number 12345 to 54321.
●​ Write a function to find the Factorial of a
number.
●​ Write a function to find Maximum Number
between two numbers.
 Assignment 1

Objective: Handwritten

Theory: Handwritten

SQL Query: Print

Output: Print

Conclusion: Handwritten
 ​

Assignment 2

Objective: Handwritten

Theory: Handwritten

SQL Query: Print

Output: Print

Conclusion: Handwritten
 Assignment 3

Objective: Handwritten

Theory: Handwritten

SQL Query: Print

Output: Print

Conclusion: Handwritten
 Assignment 4

Objective: Handwritten

Theory: Handwritten

SQL Query: Print

Output: Print

Conclusion: Handwritten
 Assignment 5

Objective: Handwritten

Theory: Handwritten

SQL Query: Print

Output: Print

Conclusion: Handwritten
 Assignment 6

Objective: Handwritten

Theory: Handwritten

SQL Query: Print

Output: Print

Conclusion: Handwritten