INDIRA GANDHI DELHI TECHNICAL UNIVERSITY FOR WOMEN

KASHMERE GATE, DELHI-110006

DATABASE MANAGEMENT SYSTEM BIT-201

SUBMITTED BY:
Anshika Prasad
Batch: ECE AI (1)
Roll no: 01601182023

SUBMITTED TO: Mr. Alongbar Wary

 INDEX
S.NO EXPERIMENT DATE SIGN
1 Brief description about MYSQL and discuss few
keywords for the database and table
manipulation.
2 Create a database having two tables with specified
fields to computerize a library system for IGDTUW.
LibraryBooks (Accession number, Title, Author,
Department, PurchaseDate, Price) IssuedBooks
(Accession number, Borrower)
a. Identify primary and foreign keys.
3 Create a database having three tables to store the
details of students of Computer
Department in your college.
Personal_Information(Roll_No, Name, DOB, Address,
Marks (rounded to a whole
number) in percentage at 10 +2, phone_number)
Paper_Details(Paper code, Name of the Paper)
Student’s Academic and Attendance details (College
roll number, Paper code,
Attendance, Marks in home examination)
4 Create the following tables and answer the queries
given below:
Customer (CustID, email, Name, Phone, ReferrerID)
Bicycle (BicycleID, DatePurchased, Color, CustID,
ModelNo)
BicycleModel (ModelNo, Manufacturer, Style)
Service (StartDate, BicycleID, EndDate)
5 Create the two tables and implement cross join and
inner join.
6 Create the two tables and implement Outer joins.
7 Create the required tables, enter at least 5 records in
each table and perform the following operations
given below: a. Alter b. Update c. View
8 Create a table and perform the following operations
on it. a. Truncate b. Drop
9 Create the following tables, enter at least 5 records
in each table and answer the queries given below.
Employee ( Person_Name, Street, City ) Works (
Person_Name, Company_Name, Salary ) Company (
Company_Name, City ) Manages ( Person_Name,
Manager_Name )
10 Create the following tables, enter at least 5 records
in each table and answer the queries given below.
Employee ( Person_Name, Street, City ) Works (
Person_Name, Company_Name, Salary ) Company (
Company_Name, City ) Manages ( Person_Name,
Manager_Name )
EXPERIMENT 1
Ques:
Brief description about MYSQL and discuss few keywords for the database and table
manipulation.

Answer:
A database is a structure that organizes and stores data
electronically.The data is stored using a database
management system(DBMS) such as MySQL,Microsoft
Access or Microsoft SQL Server.Data is organized into rows
and columns.
MySQL follows the working of Client-Server Architecture. This
model is designed for the end-users called clients to access
the resources from a central computer known as a server
using network services. Here, the clients make requests
through a graphical user interface (GUI), and the server will
give the desired output as soon as the instructions are
matched. The process of MySQL environment is the same as
the client-server model. The core of the MySQL database is
the MySQL Server. This server is available as a separate
program and responsible for handling all the database
instructions, statements, or commands. The working of
MySQL database with MySQL Server are as follows: I. MySQL
creates a database that allows you to build many tables to
store and manipulate data and defining the relationship
between each table. II. Clients make requests through the
GUI screen or command prompt by using specific SQL
expressions on MySQL. III. Finally, the server application will
respond with the requested expressions and produce the
desired result on the client-side.

Database:A database is a collection of tables,with related data.

MySQL :MySQL is a relational database management

system (RDBMS) developed by Oracle that is based on
Structured Query Language(SQL).

MySQL version :The version of MySQL used in

the recent days and in the upcoming
experiments contains history from MySQL 8.0 to
MySQL 8.0.30

SQL – Structured Query Language is further categorized

on the basis of various commands and functionality types
done by a particular set of keywords and the overall
impact on a particular set of database objects. They are as
follows:
 ● DDL – Data Definition Language
● DML – Data Manipulation Language
● DCL – Data Control Language
● DQL – Data Query Language
● TCL – Transactional Control Language

DDL :The Data Definition Language is used to create,

modify or remove a particular database object. The
keywords included in DDL are ‘Create’, ‘Alter’ and ‘Drop’.
These keywords can be used against tables, views,
functions, stored procedures and other such objects.

MYSQL is
• a relational database management system.
• open-source.
• free.
• ideal for both small and large applications.
• very fast, reliable, scalable, and easy to use.
• cross-platform.
• compliant with the ANSI SQL standard.
• developed, distributed, and supported by Oracle Corporation.

1. PRIMARY KEY
A primary key is the column or columns that contain values that uniquely identify
each row in a table.

2. ALTER TABLE
The ALTER TABLE statement is used to add, delete, or modify columns in an existing
table.

3. AND
It is used to filter records based on more than one condition:
 The AND operator displays a record if all the conditions separated by AND are TRUE.

4. BETWEEN
It is used to select values within a given range.

5. CREATE
This CREATE keyword is used to create a database, table, views, and index.

6. DELETE
The DELETE statement is used to delete the existing records from the table.

7. DISTINCT
DISTINCT is used to select only different values from the table.

8. DROP
The DROP statement is used to delete a column in the mentioned table.

9. GROUP BY
The GROUP BY keyword is used to group the result set, and it is used with the
aggregate functions like COUNT, MAX, MIN, SUM, AVG etc.
10. INSERT
INSERT command is used to insert the data into the table.

11. JOIN
JOIN command is used to JOIN TABLES.
There are different JOINS available in SQL:
• INNER JOIN
• FULL OUTER JOIN
• SELF JOIN
• CROSS JOIN
• LEFT JOIN
• RIGHT JOIN

12. SELECT
SELECT statement is used to select the data from a database, and that data is
returned is stored in a table.

13. TRUNCATE
The TRUNCATE TABLE command deletes the data inside a table, but not the table
itself.
14. UPDATE
UPDATE command is used to update the existing records in a table.

15. FOREIGN KEY

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the
PRIMARY KEY in another table.
EXPERIMENT 2

Ques:
Create a database having two tables with specified fields to computerize a library
system for IGDTUW.
LibraryBooks (Accession number, Title, Author, Department, PurchaseDate, Price)
IssuedBooks (Accession number, Borrower) a. Identify primary and foreign keys.

a. Create the tables and insert records in each table

b. Delete the records of book titled “NCERT”.

C. Change the Department of the book titled “Science” to “ENG”

d.List all books that belongs to “Bio” department and are written by author “Ncert”.

e.List all books which have a price less than 400 or purchased between “10/08/2022” and
“15/08/2022”.
F List all books(Title = “maths”) that have been issued
EXPERIMENT 3

Ques:
Create a database having three tables to store the details of students of Computer
Department in your college.
Personal_Information(Roll_No, Name, DOB, Address, Marks (rounded to a whole
number) in percentage at 10 +2, phone_number)
Paper_Details(Paper code, Name of the Paper)
Student’s Academic and Attendance details (College roll number, Paper code,
Attendance, Marks in home examination)

1) Identify primary and foreign keys. Create the tables and insert at least 5 records in
each table.
2) 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.

3) List all students who live in “Delhi” and have marks greater than 60 in paper 1.
4) Find the total attendance and total marks obtained by each student.

5) List the name of student who has got the highest marks in paper 2.
EXPERIMENT 4

Ques:
Create the following tables and answer the queries given below:
Customer (CustID, email, Name, Phone, ReferrerID)
Bicycle (BicycleID, DatePurchased, Color, CustID, ModelNo)
BicycleModel (ModelNo, Manufacturer, Style) Service (StartDate, BicycleID, EndDate)

A)Identify primary and foreign keys. Create the tables and insert at least 5 records in each
table.
b. List all the customers who have the bicycles manufactured by manufacturer “Honda”.
c. List the bicycles purchased by the customers who have been referred by customer “C1”.

d. List the models of the bicycles given for service.

EXPERIMENT 5

Ques:
Create the two tables and implement cross join and inner join.

2 TABLES
INNER JOIN
CROSS JOIN
EXPERIMENT 6

Ques:
Create the two tables and implement Outer joins.

2 TABLES
LEFT OUTER JOIN
RIGHT OUTER JOIN
 EXPERIMENT 7

Ques:
Create the required tables, enter at least 5 records in each table and perform the
following operations given below:

a) ALTER

b) UPDATE

c) VIEW
EXPERIMENT 8

Ques:
Create a table and perform the following operations on it.
a. Truncate

b. Drop
EXPERIMENT 9

Ques:
Create the following tables, enter at least 5 records in each table and answer the queries
given below.
Employee ( Person_Name, Street, City )
Works ( Person_Name, Company_Name, Salary )
Company ( Company_Name, City )
Manages ( Person_Name, Manager_Name )
a. Identify primary and foreign keys.
b. Alter table employee, add a column “email” of type varchar(20).

c. Find the name of all managers who work for both Samba Bank and NCB Bank.
d. Find the names, street address and cities of residence and salary of all employees who
work for “Samba Bank” and earn more than $9500.
EXPERIMENT 10

Ques:
Create the following tables, enter at least 5 records in each table and answer the queries
given below.

Employee ( Person_Name, Street, City )

Works ( Person_Name, Company_Name, Salary )
Company ( Company_Name, City )
Manages ( Person_Name, Manager_Name )
a) Find the names of all employees who live in the same city as the company for which
they work.
b)Find the highest salary, lowest salary and average salary paid by each company.

c)Find the sum of salary and number of employees in each company.

d. Find the name of the company that pays highest salary