Government Engineering College

Sector-28 Gandhinagar
Sem:-III (Computer Engineering Department)
Subject: DATABASE MANAGEMENT SYSTEM Subject Code:-3130703

Lab Manual
Sr. No. Date Practical Aim Signature
1 To study DDL-create and DML-insert commands.

2 Create the below given table and insert the data accordingly.
3 To perform various data manipulation commands, aggregate
functions and sorting concept on all created tables.

4 To study Single-row functions.

1
Practical – 1: To study DDL-create and DML-insert commands.

(i) Create tables according to the following definition.

CREATE TABLE DEPOSIT (ACTNO VARCHAR2(5), CNAME VARCHAR2(18), BNAME
VARCHAR2(18), AMOUNT NUMBER(8,2), ADATE DATE);
CREATE TABLE BRANCH(BNAME VARCHAR2(18),CITY VARCHAR2(18));
CREATE TABLE CUSTOMERS(CNAME VARCHAR2(19) ,CITY VARCHAR2(18));
CREATE TABLE BORROW(LOANNO VARCHAR2(5), CNAME VARCHAR2(18), BNAME
VARCHAR2(18), AMOUNT NUMBER (8,2));

(ii) Insert the data as shown below.

DEPOSIT

ACTNO CNAME BNAME AMOUNT ADATE

100 ANIL VRCE 1000.00 1-MAR-95
101 SUNIL AJNI 5000.00 4-JAN-96
102 MEHUL KAROLBAGH 3500.00 17-NOV-95
104 MADHURI CHANDI 1200.00 17-DEC-95
105 PRMOD M.G.ROAD 3000.00 27-MAR-96
106 SANDIP ANDHERI 2000.00 31-MAR-96
107 SHIVANI VIRAR 1000.00 5-SEP-95
108 KRANTI NEHRU PLACE 5000.00 2-JUL-95
109 MINU POWAI 7000.00 10-AUG-95

BRANCH

BNAME CITY
VRCE NAGPUR
AJNI NAGPUR
KAROLBAGH DELHI
CHANDI DELHI
DHARAMPETH NAGPUR
M.G.ROAD BANGLORE
ANDHERI BOMBAY
VIRAR BOMBAY
NEHRU PLACE DELHI
POWAI BOMBAY

CUSTOMERS

CNAME CITY
ANIL CALCUTTA
SUNIL DELHI
MEHUL BARODA
MANDAR PATNA
MADHURI NAGPUR
PRAMOD NAGPUR
SANDIP SURAT
SHIVANI BOMBAY

2
KRANTI BOMBAY
NAREN BOMBAY

BORROW

LOANNO CNAME BNAME AMOUNT

201 ANIL VRCE 1000.00
206 MEHUL AJNI 5000.00
311 SUNIL DHARAMPETH 3000.00
321 MADHURI ANDHERI 2000.00
375 PRMOD VIRAR 8000.00
481 KRANTI NEHRU PLACE 3000.00

From the above given tables perform the following queries:

(1) Describe deposit, branch.
(2) Describe borrow, customers.
(3) List all data from table DEPOSIT.
(4) List all data from table BORROW.
(5) List all data from table CUSTOMERS.
(6) List all data from table BRANCH.
(7) Give account no and amount of depositors.
(8) Give name of depositors having amount greater than 4000.
(9) Give name of customers who opened account after date '1-12-96'.

3
Practical – 2: Create the below given table and insert the data accordingly.

Create Table Job (job_id, job_title, min_sal, max_sal)

COLUMN NAME DATA TYPE

job_id Varchar2(15)
job_title Varchar2(30)
min_sal Number(7,2)
max_sal Number(7,2)

Create table Employee (emp_no, emp_name, emp_sal, emp_comm, dept_no)

COLUMN NAME DATA TYPE

emp_no Number(3)
emp_name Varchar2(30)
emp_sal Number(8,2)
emp_comm Number(6,1)
dept_no Number(3)

Create table deposit(a_no,cname,bname,amount,a_date).

COLUMN NAME DATA TYPE

a_no Varchar2(5)
cname Varchar2(15)
bname Varchar2(10)
amount Number(7,2)
a_date Date

Create table borrow(loanno,cname,bname,amount).

COLUMN NAME DATA TYPE

loanno Varchar2(5)
cname Varchar2(15)
bname Varchar2(10)
amount Number(7,2)

Insert following values in the table Employee.

emp_no emp_name emp_sal emp_comm dept _no

101 Smith 800 20
102 Snehal 1600 300 25
103 Adama 1100 0 20
104 Aman 3000 15
105 Anita 5000 50,000 10
106 Sneha 2450 24,500 10
107 Anamika 2975 30

Insert following values in the table job.

job_id job_name min_sal max_sal

IT_PROG Programmer 4000 10000

4
MK_MGR Marketing manager 9000 15000
FI_MGR Finance manager 8200 12000
FI_ACC Account 4200 9000
LEC Lecturer 6000 17000
COMP_OP Computer Operator 1500 3000

Insert following values in the table deposit.

A_no cname Bname Amount date

101 Anil andheri 7000 01-jan-06
102 Sunil virar 5000 15-jul-06
103 Jay villeparle 6500 12-mar-06
104 Vijay andheri 8000 17-sep-06
105 Keyur dadar 7500 19-nov-06
106 Mayur borivali 5500 21-dec-06

Perform following queries

1) Retrieve all data from employee, jobs and deposit.
2) Give details of account no. and deposited rupees of customers having account opened
between dates 01-01-06 and 25-07-06.
3) Display all jobs with minimum salary is greater than 4000.
4) Display name and salary of employee whose department no is 20. Give alias name to name of
employee.
5) Display employee no, name and department details of those employee whose department lies
in(10,20)

To study various options of LIKE predicate

1) Display all employee whose name start with ‘A’ and third character is ‘‘a’.
2) Display name, number and salary of those employees whose name is 5 characters long and
First three characters are ‘Ani’.
3) Display the non-null values of employees and also employee name second character should
be ‘n’ and string should be 5 characters long.
4) Display the null values of employee and also employee name’s third character should be ‘a’.
5) What will be output if you are giving LIKE predicate as ‘%\_%’ ESCAPE ‘\’

5
Practical – 3: To perform various data manipulation commands, aggregate functions and
sorting concept on all created tables.

1) List total deposit from deposit.

2) List total loan from karolbagh branch
3) Give maximum loan from branch vrce.
4) Count total number of customers
5) Count total number of customer’s cities.
6) Create table supplier from employee with all the columns.
7) Create table sup1 from employee with first two columns.
8) Create table sup2 from employee with no data
9) Insert the data into sup2 from employee whose second character should be ‘n’ and string
should be 5 characters long in employee name field.
10) Delete all the rows from sup1.
11) Delete the detail of supplier whose sup_no is 103.
12) Rename the table sup2.
13) Destroy table sup1 with all the data.
14) Update the value dept_no to 10 where second character of emp. name is ‘m’.
15) Update the value of employee name whose employee number is 103.

6
Practical – 4: To study Single-row functions.

1) Write a query to display the current date. Label the column Date
2) For each employee, display the employee number, job, salary, and salary increased by 15%
and expressed as a whole number. Label the column New Salary
3) Modify your query no 4.(2) to add a column that subtracts the old salary from the new salary.
Label the column Increase
4) Write a query that displays the employee’s names with the first letter capitalized and all other
letters lowercase, and the length of the names, for all employees whose name starts with J, A,
or M. Give each column an appropriate label. Sort the results by the employees’ last names.
5) Write a query that produces the following for each employee: <employee last name> earns
<salary> monthly
6) Display the name, hire date, number of months employed and day of the week on which the
employee has started. Order the results by the day of the week starting with Monday.
7) Write a query to calculate the annual compensation of all employees (sal+comm.).

7
8