DBMS Lab Assignment
Instructions:
1. Every student has to make a practical file (spiral binding).
2. Write questions followed by answer in SQL commands then the output.
3. This assignment is for the final practical exam of DBMS.
4. Get your file signed by me till 10th Jul 2023.
Using the SQL Commands complete the exercises given below:
Exercise 1. Create a table called Employee with the following structure.
Name Type
Empno Number
Ename Varchar2(10)
Job Varchar2(10)
Mgr Number
Sal Number
a. Add a column commission with domain to the Employee table.
b. Insert any five records into the table.
c. Update the column details of job.
d. Rename the column of Employ table using alter command.
e. Delete the employee whose Empnois105.
Exercise 2. Create department table with the following structure.
Name Type
Deptno Number
Deptname Varchar2(10)
Location Varchar2(10)
a. Add column designation to the department table.
b. Insert values into the table.
c. List the records of dept table grouped by deptno.
d. Update the record where deptnois9.
e. Delete any column data from the table.
Excercise 3. Create a table EMPLOYEE with following schema:
(Emp_no, E_name, E_address, E_ph_no, Dept_no, Dept_name,Job_id , Salary)
1. Insert aleast 5 rows in the table.
2. Display all the information of EMP table.
3. Display the record of each employee who works in department D10.
4. Update the city of Emp_no-12 with current city as Nagpur.
5. Display the details of Employee who works in department MECH.
6. Delete the email_id of employee James.
7. Display the complete record of employees working in SALES Department.
Excercise 4. Library Management System:
Create the following schema, enter at least 5 records in each table and answer the queries given below.
LibraryBooks (Accession number, Title, Author, Department, PurchaseDate, Price)
IssuedBooks (Accession number, Borrower)
a) Identify primary and foreign keys. Create the tables and insert at least 5 records in each table.
b) Delete the record of book titled “Database System Concepts”.
c) Change the Department of the book titled “Discrete Mathematics” to “CSE”.
d) List all books that belong to “CSE” department.
e) List all books that belong to “CSE” department and are written by author “Navathe”.
f) List all computer (Department=”CSE”) that have been issued.
g) List all books which have a price less than 500 or purchased between “01/01/2015” and
“01/01/2019”.
Excercise 5. Student Management System:
Create the following schema, enter at least 5 records in each table and answer the queries given below.
Student (College roll number, Name of student, Date of birth, Address, Marks (rounded off to whole
number) in percentage at 10 + 2, Phone number) Paper Details (Paper code, Name of the Paper)
Academic details (College roll number, Paper code, Attendance, Marks in home examination)
a) Identify primary and foreign keys. Create the tables and insert at least 5 records in each table.
b) Design a query that will return the records (from the second table) along with the name of student
from the first table, related to students who have more than 75% attendance and more than 60% marks
in paper 2.
c) List all students who live in “Lucknow” and have marks greater than 60 in paper 1.
d) Find the total attendance and total marks obtained by each student.
e) List the name of student who has got the highest marks in paper 2.
