INDIRA GANDHI DELHI TECHNICAL

UNIVERSITY FOR WOMEN

ISO 9001-2015 certified university
Kashmere Gate, Delhi-110006

BATCH: B.TECH AI-ML’27

DATABASE MANAGEMENT SYSTEMS(BAM

201)
LAB PRACTICAL FILE

Submitted to :Nidhi Submittted

Submitted by :to :
Nitisha
Arora Submitted
Sharma by
04501192023
 LAB 1
1. The Create Table Command:

2. Modifying the structure of tables.

3. Dropping a column from a table.

4. Modifying existing columns.

5. Renaming the tables

6. Truncating the tables.

7. Destroying tables.
8. Inserting Data into Tables:

Ex 1: Insert into skc

(sname,rollno,class,dob,fee_paid)
values(‘sri’,’104B’,’cse’,’27-feb-
05’,10000.00);

Ex 2: insert into skc values(&sname,&roll

no,&class); enter sname:’sri’ enter roll
no:’104B’ enter class:’cse’ 1 row created.
9. Delete operations.
a) remove all rows

b) removal of a specified row/s

10. Updating the contents of a table.

a) updating all rows
b) updating seleted records.

11. Viewing data in the tables: -

a) all rows and all columns
13. Filtering table data: -
a) Selected columns and all rows:

b) selected rows and all columns:

c) selected columns and selected rows

14. Sorting data in a table.
 LAB 2

1) To list all the fields from the table Customer.

2) To list the first name, last name.

 3) To list the first name and last name of
persons in Karnataka.

4) To list all the columns for invalid

persons
 5) To list the names of active customers.

6)To list the name and address using

concatenation

7) To select records where the pin code

has not been entered.
 8) To select the single occurrence of any
state from the table

9) To select rows of valid customers from

Karnataka.
10) To select rows of customers from
Karnataka or Kerala.

11) To sort the customer data in the

alphabetic order of state.

12) To sort in the descending order.

13) To sort the customer data, state wise
and within state by the last name.

14) To retrieve records of Karnataka cus-

tomers who are valid.
15) To retrieve records of Karnataka/Ker-
ala customers.

16) To retrieve records of Karnataka/Ker-

ala customers who are active.

17) To retrieve records of Karnataka cus-

tomers with pin code 576101.
18) To retrieve rows where the state
name begins with K and followed by any
other character.

19) To retrieve rows where the first name

contains the word RAJ embedded in it.
20) To retrieve rows where the address2
is either the word UDUPI or UDIPI in which
the 3rd character may be anything.

21) To retrieve rows where the cust_no

has data representing any value between
1003 and 1005, both numbers included.
22) To retrieve rows of persons born after
9-JAN-70 and before 1-AUG-96.

23) To retrieve rows where the city has

data which is equal to UDP or MNG or BNG
or PJM or MAR.
 LAB 3

1) To list all the branch names from the

table Loan.
2) To list rows after eliminating
duplicates.

3) To explicitly list rows, including

duplicates.
 4) To list fields after applying arithmetic
operations.

5) Find all loan numbers for loans made

at the Perryridge branch with loan amounts
greater than Rs1200.
 6)Find all loan numbers for loans with loan
amounts between Rs90,000 and Rs100,000.

7)Find all loan numbers for loans with loan

amounts not between Rs90,000 and
Rs100,000.
8) For all customers who have a loan from
the bank, find their names, loan numbers and
loan amounts.

9) Find the customer names, loan numbers

and loan amounts for all loans at the
Perryridge branch.
10) Find the names of all branches that have
assets greater than atleast one branch
located in Brooklyn.

11) Find the names of all customers whose

street address includes the substring ‘Main’.
12) To list in alphabetic order all customers
who have a loan at the Perryridge branch.

13) To list the entire loan info in descending

order of amont.

14) To find all customers having a loan, an

account or both at the bank, without
duplicates.
15) To find all customers having a loan, an
account or both at the bank, with duplicates.

16) To find all customers having both a loan

and an account at the bank, without
duplicates.
17) To find all customers having a loan, an
account or both at the bank, with duplicates.

18) To find all customers who have an

account but no loan at the bank, without
duplicates.

19) To find all customers who have an

account but no loan at the bank, with
duplicates.
20) Find the average account balance at the
Perryridge branch

21) Find the average account balance at the

each branch SELECT AVG(balance) FROM
Account GROUP BY branch_name;
22) Find the number of depositors for each
branch .

23) Find the number of depositors for each

branch where average account balance is
more than Rs 1200.

24) Find the average balance for all accounts.

25) Find the number of tuples in the
customer relation.

26) Find the average balance for each

customer who lives in Harrision and has at
least three accounts.

27) Find all the loan number that appear in

loan relation with null amount values.

28) Find all customers who have both a loan

and an account at the bank.
29) Find all customers who have both an
account and a loan at the Perryridge branch

30) Find all customers who do not have a

loan at the bank, but do not have an account
the bank.

31) Find the names of customers who do

have a loan at the bank, and whose names
are neither Smith nor Jones.
32) Find the names of all branches that have
assets greater than those of at least one
branch located in Brooklyn.

33) Find the names of all branches that have

assets greater than that of each branch
located in Brooklyn.

34) Find all customers who have an account

at all the branches located in Brooklyn.

35) Find all customers who have at most one

account at the Perryridge branch.
36)Find all customers who have at least 2
accounts at Perryridge branch

37) Find the average account balance of

those branches where the average account
balance is greater than 1200.

38) Find the maximum across all branches of

the total balance at each branch.
39) Find the all customers who have an
account but no loan at the bank.

40) Find the all customers who have either an account or a loan (but not
both) at the bank.
 LAB 4
TABLE EMPL

Q1 To sum the salary of all employees.

Q2 To sum the salary of each employee

and sort it on the sum of basic.
Q3 To sum the salary of each employee
and sort it in descending order on the
sum of basic.

Q4 To sum the salary of each employee

and sort it in descending order on the
sum of basic. Display name also.
Q5 To group the data by average salary
of each employee.

Q6 To group the basic by month.

Q7 To group the data by average salary
of each employee and display where av-
erage basic is more than 2000.

Q8 To list the employees who earn less

than the average salary.
Q9 To list the employees whose deduc-
tion is 150.

Q10 To list the names of employees and

salary details, whose basic is less than
the average salary.
 LAB 5

Q1 Create a table use name Software with

fields and insert the value
a)Display the details of software developed
by software

b) Display the details of the packages whose

software cost exceeds 2000
c) Display the details of the software
developed in c++

d) What is price of costliest software

developed in c

e) Display the details of programmer whose

language used is same as Suresh
Q2 Create a table use name Employee with
fields and insert the value
a)Display the names of employees whose
salary is greater than 10000
b)Display the details of the employees in
ascending order according to employee code

c) Display the total salary of the employees

whose grade is A

d) Display the details of the employee

earning the highest salary
e) Display the names of Employees who earn
more than RAVI

Q3 Create a table use name Student with

fields and insert the value
a)Calculate the average percentage of the
students

b)Display the names of students whose

percentage is greater than 80

C)Display the details of the student who got

the highest percentage
d)Display the details of students whose
percentage is between 50 and 70

e)Display the details of the students whose

percentage is greater than the percentage of
roll no 12CA01
 LAB 6
Q1 List of all DBMS software.
Ans 1
 MySQL
 SQlite
 IBM db2
 Oracle Database
 PostgreSQL
 MariaDB
 Microsoft SQL server
 CockroachDB
 MemSQL
  Google Spanner
 NuoDB

Q2 List down the main differences

between a File Processing System
and a Database Management
System (DBMS) in a table.
Ans 2

Attribute File Database

processin managem
g system ent
system
Data Access Centralize
Access through d data for
 custom the query
code languages
like sql
Data Basic or High
security no security
security, with
dependent control to
on file access
Data Complex Can be
Sharing an limited shared
to an easily
extent
Data Frequent Relational
duplicacy structures
avoid
duplicatio
n
Data Difficult Supports
consisten for data
cy multiple consistenc
files y
Data Less or no Supports
integrity data integrity
constraint through
s primary
key,
reference
key
Cost Low cost High cost
initially initially
but high but lower
maintenan maintenan
ce ce

Q3 Define the Terms:

a. Data Dictionary
b. DBMS Catalog (System Catalog)
c. Data Definition Language (DDL)
d. Data Manipulation Language (DML)
e. Query Language
f. Metadata
g. Components of DBMS
Ans 3
a) Data dictionary – It consists of the
details of tables, columns, data types ,
relationships and constraints .

b) DBMS catalog(System catalog) – It

consists of the meta data which includes
definition, table structures , indexes.

c) Data definition language(DDL) – These

are the set of commands that are used to
define and modify structure of database .
They include –
Create(for creation of table )
Drop(To delete a table)
Alter(To modify data types of
columns etc)

d)Data Manipulation language(DML) -

These set of commands are used to
manipulate the data in the database . These
includes –
Select(for selection of columns)
 Insert(for insertion of values in
table)
Update(modify existing data)
Delete(to delete records)

e)Query language – A language which is used

to manipulate and retrieve the data from
databases . For example SQL which is
structured query language .

f)Meta data – It means data about data , it

describes structure , properties . Like table
name, column name , data type
g)Components of DBMS –
1)Application programs – It includes
Programs developed to perform operations
like insert, update, delete etc .
2)Users – It includes users who provides
queries to get information.
3)DBA – It consists of database
schema(view,logical,physical)
4)DML processor – It handles data
manipulation commands like delete, update ,
insert etc. It processes thee commands and
generates project object code .
5)Project object code – It includes compiled
version of code which has DML processed
statements .
6)Query processor – It processes the queries
raised by users.
7)Database manager – It is responsible for
executing DML commands while interacting
with file .
8)DDL compiler – It handles data definition
commands such as create, alter etc.
9) Dictionary Manager – It consists of
information like data types, data
constraints(meta data) etc.
10)Access methods – These are techniques
and algorithms that are used to retrieve data
efficiently .
11)System buffers – These are temporary
storage areas.
12)File manager – It manages storage of data
at physical level by checking for sufficient
space in disk .
13)Database and system catalog – It is actual
physical storage where data is stored along
with catalog having meta data .

Q4 Elaborate on various types of database

users and the role of database administrator.
Ans 4 Database users are –
End users – These are the people who
work with the database eg engineers,
scientists, organizations etc.
 System Analysts – They work on the
requirements and analyze them ,
mainly work on the design eg table
creators etc .
Application programmers - They
create applications to connect with the
database , eg programmers, software
engineers etc
Database Designers – They specifically
design the structure of database , they
take into considerations rows, columns
and constraints.
Database Administrator – They
manage the database of the overall
system .
Role –
1) Design and maintenance –
Handling the structure
2) Security management – Control of
accessibility for different users
3) Back up and recovery – Prevent
data loss
4) Upgradation – Updates the version

Q5 Difference between Physical and

Logical Data Independence.
SN Physical data Logical data
O independenc independence
e
1 It focusses It focusses on
on physical logical schema
data storage like relations
without
affecting
logical
schema
2 Does not Does not affect
affect logical on users
schema
3 Changing Ex adding a new
storage column ,
format, performing
indexing etc joins, etc
4 This layer is More difficult to
hidden from close ties
users

Q6 Write a short note on Three-

Schema Architecture.

Ans 6 Three schema architecture

consists of
1)External/view level schema -
 It is basically how data is
presented to the viewers.
 It can vary based on needs of
users
2)Logical/conceptual schema –
 It is the middle schema .
 It includes columns, data types
and relation between data
elements .
3)Physical/Internal schema –
 It is the physical storage foe the
data .
 This schema is hidden from the
user.
Q7 Why Mapping Between Schema
Levels is Needed?

 Program and data are

independent of each other .
 That is any change in logical
schema would not change
anything in physical schema and
vice versa.
 This is because of mapping since
when changes in logical schema
is made , it is mapped accordingly
 thus maintaining the
independency .

Q8 How Different Schema Definition

Languages Support the Architecture?
1. Storage definition language (SDL) -
This defines how data is stored
physically such as structures, indexes
etc.
2. Data definition language(DDL) –
This is for defining conceptual data
like entities, relations, constraints,
data types .
3. View definition language(VDL) –
This is for the users who might look
for different views of data .
 LAB 7
Table Department

Table Employee
Q1 List name and designation of all
employees from department 4

Q2 Display employee names which

have 4 characters or more in their
names

Q3 Execute a query which will

display result in the form: Axar
holds the position of senior
technician in department 2 and he
is getting Rs. 8000
Q4 List the names of all employee
having even employee number

Q5 Display the date of completion of

probation period (one year from
date of joining) for all employees
with their names. HINT: Explore
adddate() function.
 Q6 Display the working experience
of each employee in terms of:
a) Number of days
b) Number of months
c) Number of years
Hint: use datediff()
function
Q7 List out all departments where more
than 2 assistant professors are working.
Display the count as well.

LAB 8
Q1 Execute a query that does
cross join on both the tables
Q2 List department details of all
employees. What type of join is
this ?

This is an example of left join, wherein all columns of relation Employee are
kept in view regardless of them matching with those in Dept table .

Q3 List employee details of all

departments. What type of join is
this ?
This is right join , wherein all
columns of Dept relation are
taken regardless of the matching
with primary key of Employee
relation

Q4 Execute a left join that selects

all departments and any
employees the department may
have.

Q5 Execute a full outer join on

both tables. How the results are
different from query 1.
In this query only those records
are prent which had same value
of attribute(primary key) for both
relations while in query 1 , there
was no such matching or
intersection , it was a cartesian
product .

Q6 Execute a query that returns

the names and deptt IDs of all
employees who have the same
department ID ordered by
department ID. What type of join
is this ?
This is a self join

Q7 Execute a query that returns

the names, deptt IDs and
department name of all
employees who have the same
department ID ordered by
department ID.

Q8 Execute a query which returns

department name of that
department that gives highest
salary summed over their
employees.
 LAB 9
Q1.Draw ER model for an University database
application where i) a university has many
departments ii) Each department has
multiple instructor; one among them is the
head of the department iii) an instructor
belongs to only one department iv) each
department offers multiple courses each of
which is taught by a single instructor v) a
student may enroll for many courses by
different department
 Dep_name HOD

DEPARTMENT INSTRUCTOR
CONSISTS
DEPARTMENT

COURSE

CONSISTS
OFFERS STUDENT

COURSES Taugh
INSTRUCTOR
t

ENROLLS Name

Dep_name
ID

STUDENT
Q2. Construct an ER diagram for a
hospital with a set of patients and a
set of medical doctors. Associated
with each patient, there is a log of
the various tests and examinations
conducted.
 SPECIALI Name Patient_n
SATION o

DOCTOR

Examine

TREATS
Prescription

Patient_n
o PATIENT TESTED

NAME

Type TEST

Descriptio
n
Q3. Design ER diagram for keeping track of
performance of your favourite sports team.
You should store the matches played, the
scores in each match, the player in each
match and individual player statistics for
each match.
 Match
Score

TEAM

Time

Date PART
OF CAPTAIN

Place

PLAYER
Q4. Information about a bank is
about customers and their
account. Customer has a name,
address which consists of house
number, area and city and one or
more phone numbers. Account
has number, type and balance. We
need to record jointly. Clearly
indicate attributes, keys and the
cardinality ratios and
participation constraints.
 House_no Area City
Q5.A database is to be constructed to keep
track of the team. A team has a number of
players,
Name not all of then participate Phone_n
ADDRESS ino each
game. It is desired to keep track of the
players. Create ER diagram.
CUSTOMER

1:N

HAS AN

1:1

ACCOUNT

Account_no
Balance

Type
 Name id

TEAM

Belongs
Part to

Plays

GAME
PLAYER

Location
P_name

Time P_num
Q6 Construct ER diagram for a
car-insurance company whose
customers own one or more cars
each. Each car has associated
with it, zero to any number of
recorded accidents.
 Liscense_ Phone_n
ADDRESS
no o

Name
CUSTOMER

OWNS Has an

CAR
Meet
s
ACCIDENT

Car_id
Location
C_id

number
Model color