MEERUT INSTITUTE OF TECHNOLOGY

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

LAB MANUAL

COURSE : B.Tech (CSE, AIML, DS)

SEMESTER : V

SUBJECT : Database Management System

SUBJECT CODE : BCS-551

PREPARED BY

Faculty Name: Dr. Suneet Shukla Prof. (Dr.) MIH Ansari

Faculty Name: Mr. Amit Girdharwal H.O.D. (CSE)
 MEERUT INSTITUTE OF TECHNOLOGY
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

LIST OF EXPERIMENTS

Course: B.Tech Semester: V Session: 2024-25

Branch: CSE, AIML, DS Subject Code & Name: BCS-551 DBMS Lab

The queries to be implemented on DBMS by using SQL

1. Write the queries for data definition and data manipulation language.
2. Write SQL queries using logical operators (=, <, >, etc.)
3. Write SQL queries using SQL operators (between,end, IN(List), Like, IS
NULL and also with negating expressions.
4. Write SQL query using character, number and date data types.
5. Write SQL queries using group by and having clause.
6. Write SQL queries for UNION, INTERSECT and MINUS.
7. Write SQL queries for extracting data from more than one table (EQUI
JOIN, NON EQUI JOIN, Outer Join).
8. Write SQL queries for sub queries, nested queries.

PREPARED BY

Faculty Name: Dr. Suneet Shukla Prof. (Dr.) MIH Ansari

Faculty Name: Mr. Amit Girdharwal H.O.D. (CSE)
Experiment No. 1
Write the queries for DDL and DML.
To create a table:
Create table emp
(emp_name varchar2(20),
emp_no number(5),
emp_dept varchar2(10),
salary number(10,2));

To insert data into tables:

Insert into emp values
(‘deepak’, 13, ‘cs’, 10000.87);

To view data in a table:

All rows and all columns:
select * from emp;

Selected columns and all rows

Select emp_name, emp_no from emp;

Selected rows and all columns:

Select * from emp
Where emp_no > 10;

Selected rows and selected columns:

Select emp_name, salary
From emp
Where salary > 10000;
Experiment No. 2
Write SQL queries using logical operators (=, <, >, etc.)
Create table emp
(emp_name varchar2(20),
 emp_no number(5),
emp_dept varchar2(10),
salary number(10,2));

To insert data into tables:

Insert into emp values
(‘deepak’, 13, ‘cs’, 10000.87);

To retrieve data from the table:

Select * from emp

Where salary < 5000;

Select * from emp

Where emp_no > 20;

Select * from emp

Where emp_name = ‘Rakesh’;

Select emp_name, salary

Where salary >= 10000;
Experiment No. 3
Write SQL queries using SQL operators (between..end, IN(List),
Like, IS NULL and also with negating expressions.
Create table emp
(emp_name varchar2(20),
emp_no number(5),
emp_dept varchar2(10),
salary number(10,2));

To insert data into tables:

Insert into emp values
(‘deepak’, 13, ‘cs’, 10000.87);

Select * from emp

Where salary between 9000 and 11000;

Select * from emp

Where emp_name IN (‘deepak’, ‘ravi, ‘rakesh’, ‘ranjeet’);

Select * from emp

Where emp_name Like ‘_a%’ or emp_name Like ‘d%’

Select * from emp

Where emp_dept = ‘CS’ and salary < 10000;

Select * from emp

Where not emp_dept = ‘cs’
Experiment No. 4
Write SQL query using character, number and date data types.
Create table emp
(EID char(10),
ENAME char(25),
DOJ date,
SALARY numeric(9, 2));

Insert the following data:

E1 JYOTI 01/08/2002 25000
E2 IBA 01/08/2003 26000
E3 NITIN 01/09/2003 27000
E4 AMIT 10/09/2003 28000
E5 AJEET 01/08/2004 29000
E6 DEB 01/08/2005 25000
E7 DHARAM 01/08/2006 26000
E8 MUNISH 01/08/2007 27000
E9 ANCHAL 01/08/2008 28000
E10 ANJALI 01/08/2010 29000

Select all employees who joined after 01 January 2005.

Select * from emp
Where DOJ > ’01-JAN-2005’;

Select all employees who joined after DEB

Select S.ENAME, S.DOJ
From emp S, emp T
Where T.ENAME = ‘DEB’
And S.DOJ > T.DOJ;
Experiment No. 5
Write SQL queries using group by and having clause.
Create account table:

Create table account

(Acc_No Char(5),
BrName Char(20),
Balance Numeric (10, 2));

Insert data into account table:

A1 STROAD 15000
A2 MANDICHOWK 16000
A3 KATGHAR 18000
A4 CIVILLINES 21000
. . .
. . .
. . .
. . .
. . .
. . .
. . .
A20 STROAD 17000

Select BrName, avg(Balance)

From account
Group by BrName;

Select BrName, avg(Balance)

From account
Where BrName IN (‘StRoad’, ‘Katghar’)
Group by BrName;

Select BrName, avg(Balance)

From account
Where BrName IN (‘StRoad’, ‘Katghar’)
Group by BrName
Having avg(Balance) > 15000;
Experiment No. 6
Write SQL queries for UNION, INTERSECT and MINUS.
The number of columns and the data types of respective columns
being selected must be identical.

Create table cust_mstr

(cust_no varchar2(5),
Name varchar2(20));

Create table addr_dtls

(code_no varchar2(5),
City varchar2(20));

Create table emp_mstr

(emp_no varchar2(5),
Name varchar2(20));

Insert into cust_mstr values

( insert some values );

Similarly insert into addr_dtls and emp_mstr.

Select cust_no, name

From cust_mstr, addr_dtls
Where cust_mstr.cust_no = addr_dtls.code_no
And addr_dtls.city = ‘Mumbai’
And addr)_dtls.code_no LIKE ‘c%’
UNION
Select emp_no, name
From emp_mstr, addr_dtls
Where emp_mstr.emp_no = addr_dtls.code_no
And addr_dtls.city = ‘Mumbai’
And addr_dtls.code_no LIKE ‘E%’;

Create table acct_fd_cust_dtls

 (Cust_no varchar2(5),
Acct_fd_no varchar2(5));

Insert into acct_fd_cust_dtls values

(‘&cust_no’, &acct_fd_no’);

Select distinctcust_no
From acct_fd_cust_dtls
Where acct_fd_no LIKE ‘CA%’
Or acct_fd_no LIKE ‘SB%’
INTERSECT
Select distinct cust_no
From acct_fd_cust_dtls
Where acct_fd_no LIKE ‘FS%’;

Replace INTERSECT with MINUS.

Experiment No. 7
Write SQL queries for extracting data from more than one table
(EQUI JOIN, NON EQUI JOIN, Outer Join).
reate table emp_mstr
(emp_no number(5),
Emp_name varchar2(20),
Dept varchar2(10),
Design varchar2(10),
Branch_no number(5));

Insert into emp_mstr values

(-------------------);

Create table branch_mstr

(name varchar2(20),
Branch_no number(5));

Insert into branch_mstr values

(-------------------);

Select E.emp_no, E.emp_name, B.name, E.dept, E.desig

From emp_mstr E INNER JOIN branch_mstr B
ON B.branch_no = E.branch_no;

Select E.emp_no, E.emp_name, B.name, E.dept, E.desig

From emp_mstr E, branch_mstr B
Where B.branch_no = E.branch_no;

Create table cntc_dtls

(code_no number(5),
Cntc_type varchar2(5),
Cntc_data varchar2(20));

Insert into cntc_dtls values

(--------------------------)
Select E.emp_name, E.dept, C.cntc_type, C.cntc_data
From emp_mstr e LEFT JOIN cntc_dtls C
ON E.emp_no = C.code_no;

Experiment No. 8
Write SQL queries for sub queries, nested queries.
Find out all customers having same names as the employees.

Create table cust_mstr

 (Fname varchar2(15),
Lname varchar2(15));

Insert into cust_mstr values

(-----------------------);

Create table emp_mstr

(Fname varchar2(15),
Lname varchar2(15));

Insert into emp_mstr values

(-----------------------);

Select fname, lname

From cust_mstr
Where (fname, lname) IN
(select fname, lname from emp_mstr);

List accounts alongwith the current balance, the branch to which it

belongs and the average balance of the branch, having a balance
more than the average balance of the branch, to which the count
belongs.

Create table acct_mstr

(acct_no varchar2(5),
Curbal number(12,2),
Branch_no varchar2(5));

Insert into acct_mstr values

(--------------------------------)

Select A.acct_no, A.curbal, A.branch_no, B.avgbal

From acct_mstr A, (Select branch_no, avg(curbal) avgbal from acct_mstr
Group by branch_no) B
Where A.branch_no = B.branch_no
And A.curval > B.avgbal