Maharaja Education Trust (R), Mysuru

Maharaja Institute of Technology Mysore

Belawadi, Sriranga Pattana Taluk, Mandya – 571 477

Approved by AICTE, New Delhi,

Affiliated to VTU, Belagavi & Recognized by Government of Karnataka

LAB MANUAL
DBMS LABORATORY WITH MINI
PROJECT (17CSL58)

Prepared by
PUNEETH P
Assistant professor

Department of Information Science and

Engineering
 Maharaja Education Trust (R), Mysuru
Maharaja Institute of Technology Mysore
Belawadi, Sriranga Pattana Taluk, Mandya – 571 477

Vision/ ಆಶಯ

“To be recognized as a premier technical and management institution promoting extensive

education fostering research, innovation and entrepreneurial attitude"
,

Mission/ ಯ

 To empower students with indispensable knowledge through dedicated teaching and

collaborative learning.

 To advance extensive research in science, engineering and management disciplines.

,

 To facilitate entrepreneurial skills through effective institute - industry collaboration and

interaction with alumni.
,

 To instill the need to uphold ethics in every aspect.

.

 To mould holistic individuals capable of contributing to the advancement of the society.

.
 Maharaja Institute of Technology Mysore
Department of Information Science and Engineering

VISION OF THE DEPARTMENT

To be recognized as the best centre for technical education and research in the field of
information science and engineering.

MISSION OF THE DEPARTMENT

 To facilitate adequate transformation in students through a proficient teaching

learning process with the guidance of mentors and all-inclusive professional activities.
 To infuse students with professional, ethical and leadership attributes through industry
collaboration and alumni affiliation.
 To enhance research and entrepreneurship in associated domains and to facilitate real
time problem solving.

PROGRAM EDUCATIONAL OBJECTIVES:

 Proficiency in being an IT professional, capable of providing genuine solutions to

information science problems.
 Capable of using basic concepts and skills of science and IT disciplines to pursue
greater competencies through higher education.
 Exhibit relevant professional skills and learned involvement to match the
requirements of technological trends.
PROGRAM SPECIFIC OUTCOME:

Student will be able to

 PSO1: Apply the principles of theoretical foundations, data Organizations,

networking concepts and data analytical methods in the evolving technologies.

 PSO2:Analyse proficient algorithms to develop software and hardware

competence in both professional and industrial areas
 Maharaja Institute of Technology Mysore
Department of Information Science and Engineering

Program Outcomes

1. Engineering knowledge: Apply the knowledge of mathematics, science, engineering

fundamentals, and an engineering specialization to the solution of complex engineering
problems.
2. Problem analysis: Identify, formulate, review research literature, and analyze complex
engineering problems reaching substantiated conclusions using first principles of
mathematics, natural sciences, and engineering sciences.
3. Design/development of solutions: Design solutions for complex engineering problems
and design system components or processes that meet the specified needs with appropriate
consideration for the public health and safety, and the cultural, societal, and environmental
considerations.
4. Conduct investigations of complex problems: Use research-based knowledge and
research methods including design of experiments, analysis and interpretation of data, and
synthesis of the information to provide valid conclusions.
5. Modern tool usage: Create, select, and apply appropriate techniques, resources, and
modern engineering and IT tools including prediction and modeling to complex engineering
activities with an understanding of the limitations.
6. The engineer and society: Apply reasoning informed by the contextual knowledge to
assess societal, health, safety, legal and cultural issues and the consequent responsibilities
relevant to the professional engineering practice.
7. Environment and sustainability: Understand the impact of the professional engineering
solutions in societal and environmental contexts, and demonstrate the knowledge of, and need
for sustainable development.
8. Ethics: Apply ethical principles and commit to professional ethics and responsibilities and
norms of the engineering practice.
9. Individual and team work: Function effectively as an individual, and as a member or
leader in diverse teams, and in multidisciplinary settings.
10. Communication: Communicate effectively on complex engineering activities with the
engineering community and with society at large, such as, being able to comprehend and
write effective reports and design documentation, make effective presentations, and give and
receive clear instructions.
11. Project management and finance: Demonstrate knowledge and understanding of the
engineering and management principles and apply these to one’s own work, as a member and
leader in a team, to manage projects and in multidisciplinary environments.
12. Life-long learning: Recognize the need for, and have the preparation and ability to
engage in independent and life-long learning in the broadest context of technological change.
 Maharaja Institute of Technology Mysore
Department of Information Science and Engineering

Course Overview
SUBJECT: DBMS LABORATORY WITH MINI PROJECT SUBJECT CODE: 17CSL58

A database management system (DBMS) is computer application software that provides a

way to manage data. The requirement of modern days is to have an automated system that
manages, modifies and updates data accurately. This is achieved by a DBMS in robust,
correct and non-redundant way. Structured Database Management Systems (DBMS) based
on relational and other models have long formed the basis for such databases. Consequently,
Oracle, Microsoft SQL Server, Sybase etc. have emerged as leading commercial systems
while MySQL, PostgreSQL etc. lead in open source and free domain .

The Course allows students to apply the conceptual design model to construct the real
world requirement. Course gives familiarity of Database Concepts were students can analyze
the various constraints to populate the database and examine different working concepts of
DBMS to infer the most suitable pattern of documentation.

DBMS lab with mini project aims at practicing and achieving this aim by using MySQL.
While also gain capability to design database and its hierarchical structure for given real
world application.

Course Objectives

1. Foundation knowledge in database concepts, technology and practice to groom.

2. Strong practice in SQL programming through a variety of database problems.
3. Apply the conceptual design model to analyse the real world requirement.
4. Develop database applications using front-end tools and back-end DBMS.
Course Outcomes
CO’s DESCRIPTION OF THE OUTCOMES
17CSL58.1 Apply the conceptual design model to construct the real world requirement.
17CSL58.2 Analyze the various constraints to populate the database.
17CSL58.3 Examine different working concepts of DBMS to infer the most suitable pattern of
documentation.
17CSL58.4 Design database and its hierarchical structure for given real world application.
 Maharaja Institute of Technology Mysore
Department of Information Science and Engineering
Syllabus
SUBJECT: DBMS LABORATORY WITH MINI PROJECT SUBJECT CODE: 17CSL58

LABORATORY EXPERIMENTS
PART- A
SQL Programming (Max. Exam Mks. 50)
Design, develop, and implement the specified queries for the following problems using
Oracle, MySQL, MS SQL Server, or any other DBMS under LINUX/Windows
environment.
Create Schema and insert at least 5 records for each table. Add appropriate database
constraints.

PROGRAM 1) Consider the following schema for a Library Database:

BOOK(Book_id, Title, Publisher_Name, Pub_Year)
BOOK_AUTHORS(Book_id, Author_Name)
PUBLISHER(Name, Address, Phone)
BOOK_COPIES(Book_id, Branch_id, No-of_Copies)
BOOK_LENDING(Book_id, Branch_id, Card_No, Date_Out, Due_Date)
LIBRARY_BRANCH(Branch_id, Branch_Name, Address)
Write SQL queries to
1. Retrieve details of all books in the library – id, title, name of publisher, authors, number of
copies in each branch, etc.
2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017
to Jun 2017.
3. Delete a book in BOOK table. Update the contents of other tables to reflect this data
manipulation operation.
4. Partition the BOOK table based on year of publication. Demonstrate its working with a
simple query.
5. Create a view of all books and its number of copies that are currently available in the
Library.

PROGARM 2) Consider the following schema for Order Database:

SALESMAN(Salesman_id, Name, City, Commission)
CUSTOMER(Customer_id, Cust_Name, City, Grade, Salesman_id)
ORDERS(Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id)
Write SQL queries to
1. Count the customers with grades above Bangalore’s average.
2. Find the name and numbers of all salesman who had more than one customer.
3. List all the salesman and indicate those who have and don’t have customers in their cities
(Use UNION operation.)
4. Create a view that finds the salesman who has the customer with the highest order of a day.
5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders
must also be deleted.

PROGRAM 3) Consider the schema for Movie Database:

ACTOR(Act_id, Act_Name, Act_Gender)
DIRECTOR(Dir_id, Dir_Name, Dir_Phone)
MOVIES(Mov_id, Mov_Title, Mov_Year, Mov_Lang, Dir_id)
MOVIE_CAST(Act_id, Mov_id, Role)
RATING(Mov_id, Rev_Stars)
Write SQL queries to
1. List the titles of all movies directed by ‘Hitchcock’.
2. Find the movie names where one or more actors acted in two or more movies.
3. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN
operation).
4. Find the title of movies and number of stars for each movie that has at least one rating and
find the highest number of stars that movie received. Sort the result by movie title.
5. Update rating of all movies directed by ‘Steven Spielberg’ to 5.

PROGRAM 4) Consider the schema for College Database:

STUDENT(USN, SName, Address, Phone, Gender)
SEMSEC(SSID, Sem, Sec)
CLASS(USN, SSID)
SUBJECT(Subcode, Title, Sem, Credits)
IAMARKS(USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)
Write SQL queries to
1. List all the student details studying in fourth semester ‘C’ section.
2. Compute the total number of male and female students in each semester and in each
section.
3. Create a view of Test1 marks of student USN ‘1BI15CS101’ in all subjects.
4. Calculate the FinalIA (average of best two test marks) and update the corresponding table
for all students.
5. Categorize students based on the following criterion:
If FinalIA = 17 to 20 then CAT = ‘Outstanding’
If FinalIA = 12 to 16 then CAT = ‘Average’
If FinalIA< 12 then CAT = ‘Weak’
Give these details only for 8th semester A, B, and C section students.

PROGRAM 5) Consider the schema for Company Database:

EMPLOYEE(SSN, Name, Address, Sex, Salary, SuperSSN, DNo)
DEPARTMENT(DNo, DName, MgrSSN, MgrStartDate)
DLOCATION(DNo,DLoc)
PROJECT(PNo, PName, PLocation, DNo)
WORKS_ON(SSN, PNo, Hours)
Write SQL queries to
1. Make a list of all project numbers for projects that involve an employee whose last name is
‘Scott’, either as a worker or as a manager of the department that controls the project.
2. Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10
percent raise.
3. Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as the
maximum salary, the minimum salary, and the average salary in this department
4. Retrieve the name of each employee who works on all the projects controlled by
department number 5 (use NOT EXISTS operator).
5. For each department that has more than five employees, retrieve the department number
and the number of its employees who are making more than Rs. 6,00,000.

PART-B: Mini Project (Max. Exam Marks. 30)

Use Java, C#, PHP, Python, or any other similar front-end tool. All
applications must be demonstrated on desktop/laptop as a stand-alone or web
Based application (Mobile apps on Android/IOS are not permitted.)
 Maharaja Institute of Technology Mysore
Department of Information Science and Engineering

Index

SUBJECT: DBMS LABORATORY WITH MINI PROJECT SUBJECT CODE: 17CSL58

PROGRAMS Pg no
1) Library Database. 2
2) Order Database: 13

3) Movie Database: 22
4) College Database: 32
5) Company Database 43
VIVA QUESTION 55
Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

PART-A:

SQL programming (Max. Exam Marks, 50)

 Design, develop, and implement the specified queries for the following problems using
Oracle, My SQL, MY SQL Server, or any other DBMS under LINUX/Windows
environment.
 Create Schema and insert at least 5 records for each table. Add appropriate database
constraints.

PART-B:

Mini project (Max. Exam Marks, 30)

 Use Java. C#, PHP, Python, or any other similar front-end tool.
 All applications must be demonstrated on desktop/laptop as a stand-alone or web based
application (Mobile apps on Android/IOS are not permitted).

PART A: SQL PROGRAMMING

1. Consider the following schema for a Library Database:

BOOK (Book_ id, Title, Publisher_ Name, Pub_ Year)

BOOK_AUTHORS (Book_ id, Author_ Name)

PUBLISHER (Name, Address, Phone)

BOOK_COPIES (Book_ id, Branch_ id, No_ of_ Copies)

BOOK_LENDING (Book_ id, Branch_ id ,Card_ No, Date_ Out, Due_ Date)

LIBRARY_BRANCH (Branch_ id, Branch_ Name, Address)

Write SQL queries to

A. Retrieve details of all books in the library – id, title, name of publisher, authors, number of
copies in each branch, etc.

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 1

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

B. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to
Jun 2017.
C. Delete a book in BOOK table. Update the currents of other tables of reflect this data
manipulation Operation.
D. Partition the BOOK table based on year of publication. Demonstrate its working with a
simple query.
E. Create a view of all books and its number of copies that are currently available in the Library.

Solution:

ERDigram:

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 2

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

Schema diagram:

BOOK

Book_ id title Publisher_ Name Publisher_ year

BOOK_AUTHOR

Book_ id Author_ name

PUBLISHER

Name address Phone

BOOK_COPIES

Book_ id Branch_ id No_ of_ copies

BOOK_LENDING

Book_ id Branch_ id Card_ no Date_ out Due_ date

LIBRARY_BRANCH

Branch_ id Branch_ name Address

Table Creation

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 3

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

CREATE TABLE PUBLISHER(NAME VARCHAR(20) PRIMARY KEY,ADDRESS

VARCHAR(20),PHONE REAL);

CREATE TABLE BOOK(BOOK_ID INT PRIMARY KEY,TITLE

CHAR(10),PUBLISHER_NAME CHAR(15),PUBLISHER_YEAR INT,FOREIGN
KEY(PUBLISHER_NAME) REFERENCES PUBLISHER(NAME) ON DELETE CASCADE);

CREATE TABLE LIBRARY_BRANCH(BRANCH_ID INT PRIMARY

KEY,BRANCH_NAME CHAR(10),ADDRESS VARCHAR(15));

CREATE TABLE BOOK_AUTHORS(BOOK_ID INT,AUTHOR_NAME

CHAR(10),FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE
CASCADE);

CREATE TABLE BOOK_LENDING(BOOK_ID INT,BRANCH_ID INT,CARD_NO

INT,DATE_OUT DATE,DUE_DATE DATE,PRIMARY
KEY(BOOK_ID,BRANCH_ID,CARD_NO),FOREIGN KEY(BOOK_ID) REFERENCES
BOOK(BOOK_ID) ON DELETE CASCADE,FOREIGN KEY(BRANCH_ID) REFERENCES
LIBRARY_BRANCH(BRANCH_ID) ON DELETE CASCADE);

CREATE TABLE BOOK_COPIES(BOOK_ID INT,BRANCH_ID INT,NO_OF_COPIES

INT,FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE
CASCADE,FOREIGN KEY(BRANCH_ID) REFERENCES
LIBRARY_BRANCH(BRANCH_ID) ON DELETE CASCADE,PRIMARY
KEY(BOOK_ID,BRANCH_ID));

Table description

DESC PUBLISHER;

DESC BOOK;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 4

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

DESC LIBRARY_BRANCH;

DESC BOOK_AUTHORS;

DESC BOOK_LENDING;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 5

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

DESC BOOK_COPIES;

Insertion of values to tables:

INSERTINTO PUBLISHER
VALUES(“ANUAMMUBOOKS”,”SRIRAMRKNAGAR”,”8797546021”);

INSERT INTO PUBLISHER

VALUES(“BHAVANISTORE”,”KARNATAKABAR”,”9900778821”);

INSERT INTO PUBLISHER

VALUES(“RAJBOOKSTALL”,”SATALLITE@12”,”9252459821”);

INSERT INTO PUBLISHER VALUES(“SNEHABANDHU”,”MANDYA123”,”9380577730”);

INSERT INTO PUBLISHER

VALUES(“SPOO@SHBOOKS”,”NEARBEMLARCH”,”8969361282”);

INSERT INTO PUBLISHER VALUES(“SRI

SAI”,”DEVEGOWDANAHUNDI”,”7090454640”);

INSERT INTO BOOK VALUES(“100”,”OOMD”,”SRI SAI”,”1996”);

INSERT INTO BOOK VALUES(“101”,”CMPTRNTWK”,”SNEHABANDHU”,”1992”);

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 6
Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

INSERT INTO BOOK VALUES(“102”,”AUTOMATA”,”RAJBOOKSTALL”,”2000”);

INSERTB INTO BOOK VALUES(“103”,”CSHARP”,”SPOO@ASHBOOKS”,”2012”);

INSERT INTO BOOK VALUES(“104”,”MANAGEMENT”,”SRI SAI”,”1996”);

INSERT INTO BOOK VALUES(“105”,”DATABASEMS”,”BHAVANISTORE”,”1899”);

INSERT INTO LIBRARY_BRANCH VALUES(“1000”,”BRANCH1”,”GROUNDFLOAR”);

INSERT INTO LIBRARAY_BRANCH VALUES(“1001”,”BRANCH2”,”GFROWF”);

INSERT INTO LIBRARY_BRANCH VALUES(“1002”,”BRANCH1”,”LFTSIDEROW7”);

INSERT INTO LIBRARY_BRANCH VALUES(“1003”,”BRANCH3”,”RGHTSIDEROW7);

INSERT INTO LIBRARY_BRANCH VALUES(“1004”,”BRANCH4”,”OPPOSITEFLOAR”);

INSERT INTO LIBRARY_BRANCH VALUES(“1005”,”BRANCH5”,”ECEFLOAR”);

INSERT INTO BOOK_AUTHORS VALUES(“100”,”NAWATHI”);

INSERT INTO BOOK_AUTHORS VALUES(“100”,”NAWATHI”);

INSERT INTO BOOK_AUTHORS VALUES(“101”,”THRIPATI”);

INSERT INTO BOOK_AUTHORS VALUES(“102”,”PADMAREDDY”);

INSERT INTO BOOK_AUTHORS VALUES(“103”,”KRISHNA”);

INSERT INTO BOOK_LENDING VALUES(“100”,”1000”,”50”,”2017/01/03”,”2017/06/26”);

INSERT INTO BOOK_LENDING VALUES(“101”,”1001”,”50”,”2017/01/26”,”2017/06/26”);

INSERT INTO BOOK_LENDING VALUES(“102”,”1002”,”50”,”2017/01/15”,”2017/06/26”);

INSERT INTO BOOK_LENDING VALUES(“103”,”1003”,”50”,”2018/01/26”,”2018/06/03”);

INSERT INTO BOOK_LENDING VALUES(“104”,”1004”,”54”,”2017/01/03”,”2017/06/03”);

INSERT INTO BOOK_COPIES VALUES(“101”,”1000”,”3”);

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 7

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

INSERT INTO BOOK_COPIES VALUES(“100”,”1004”,”4”);

INSERT INTO BOOK_COPIES VALUES(“101”,”1006”,”2”);

INSERT INTO BOOK_COPIES VALUES(“103”,”1003”,”5”);

INSERT INTO BOOK_COPIES VALUES(“104”,”1002”,”5”);

SELECT * FROM PUBLISHER;

SELECT * FROM BOOK;

SELECT * FR0M LIBRARY_BRANCH;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 8

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

SELECT * FR0M BOOK_AUTHORS;

SELECT * FROM BOOK_LENDING;

SELECT * FROM BOOK_COPIES;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 9

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

Queries:

1. Retrieve details of all books in the library_ id, title, name of publisher, authors,
number of copies in each branch, etc.

SELECTPUBLISHER_NAME,BRANCH_ID,TITLE,AUTHOR_NAME,NO_OF_COPI
ES FROM BOOK b, BOOK_COPIES c, BOOK_AUTHOR a WHERE b. BOOK_ID=a.
BOOK_ID AND b. BOOK_ID=c. BOOK_ID;

2. Get the particulars of borrowers who have borrowed more than 3 books, but from
Jan 2017 to Jun 2017.

SELECT CARD_NO FROM BOOK_LENDING WHERE DATE_OUT BETWEEN

“2017/01/03” AND “2017/06/26” GROUP BY CARD_NO HAVING COUNT(*)>=3;

3. Delete a book in BOOK table, Update the contents of other tables to reflect this data
manipulation Operation.

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 10

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

DELETE FROM BOOK WHERE BOOK_ID=100;

SELECT * FROM BOOK;

SELECT * FROM BOOK_LENDING;

SELECT * FROM BOOK_COPIES;

4. Partition the BOOK table based on year of publication. Demonstrate its working
with a simple query.

CREATE VIEW P_YEAR AS (SELECT PUBLISHER_YEAR FROM BOOK GROUP

BY PUBLISHER_YEAR);

SELECT * FROM PUBLISHER_YEAR;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 11

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

5. Create a view of all books and its number of copies that are currently available in
the library.

CRAETE VIEW CUR_AVAI AS (SELECT b. BOOK_ID, NO_OF_COPIES, TITLE

FROM BOOK_COPIES b, LIBRARY_BRANCH l, BOOK c WHERE b.
BRANCH_ID=l. BRANCH_ID AND c. BOOK_ID=b. BOOK_ID);

SELECT * FROM CUR_AVAI;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 12

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

2. Consider the following schema foe Order Database:

SALESMAN(Salesman_ id, Name, City, Commission)

CUSTOMER(Customer_ id, Customer_ name, Grade, Salesman_ id)
ORDERS(Order_ no, Purchase_ amount, Order_ date, Customer_ id, Salesman_ id)

Write SQL queries to

1. Count the customers with grades above Banaglore‟s average.

2. Find the name and numbers of all salesman who had more than one customer.
3. List all salesman and indicate those have and don‟t have customers in their cities(use
UNION operation).
4. Create a view that finds the salesman who has the customer with the highest order of a
day.
5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders
must be deleted.

Solution:

ER – Diagram:

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 13

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

Schema Digram:

SALESMAN

Salesman_ id name city commission

CUSTOMER

Customer_ id Customer_ name city grade Salesman_ id

ORDERS

Order_ no Purchase_ Order_ date Customer_ id Salesman_ id

amount

Table creation:

CREATE TABLE SALESMAN(SALESMAN_ID INT PRIMARY KEY,NAME

CHAR(20),CITY CHAR(20),COMMISSION REAL);

CREATE TABLE CUSTOMER(CUSTOMER_ID INT PRIMARY KEY,CUSTOMER_NAME

CHAR(20),CITY CHAR(20),GRADE INT,SALESMAN_ID INT,FOREIGN
KEY(SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE SET
NULL);

CREATE TABLE ORDERS(ORDER_NO INT PRIMARY KEY,PURCHASE_AMOUNT

REAL,ORDER_DATE DATE,CUSTOMER_ID INT,FOREIGN KEY(CUSTOMER_ID)
REFERENCES CUSTOMER(CUSTOMER_ID),SALESMAN_ID INT,FOREIGN
KEY(SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE
CASCADE);

Table description;

DESC SALESMAN;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 14

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

DESC CUSTOMER;

DESC ORDERS;

Insertion of values to tables:

INSERT INTO SALESMAN VALUES(“1000”,”BHUVANESH K”,”BANGLORE”,”12000”);

INSERT INTO SALESMAN VALUES(“1001”,”PRAGATHI”,”BANGLORE”,”15000”);

INSERT INTO SALESMAN

VALUES(“1002”,”VASUDEVAPRASAD”,”MANGLORE”,”10000”);

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 15

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

INSERT INTO SALESMAN

VALUES(“1003”,”GURUPRASADKONDI”,”MYSORE@123”,”19000”);

INSERT INTO SALESMAN VALUES(“1004”,”SRISAI SALERS”,”MANDYA”,”25000”);

INSERT INTO SALESMAN

VALUES(“1005”,”BOOKTRIBUTERS”,”HANUMANTHNAGAR”,”16200”);

INSERT INTO SALESMAN VALUES(“SHOWROOMGALAXY”,”BIDAR”,”14900”);

INSERT INTO CUSTOMER VALUES(“100”,”SPOORTHIASHWIN”,”BIDAR”,”10”,”1000”);

INSERT INTO CUSTOMER

VALUES(“100”,”AMRUTHANUKASHYAP”,”MANGLORE”,”8”,”1000”);

INSERT INTO CUSTOMER VALUES(“102”,”RAJESHWARI”,”MANGLORE”,”5”,”1002”);

INSERT INTO CUSTOMER

VALUES(“103”,“NISHAKGOWDA”,”MANGLORE”,”9”,”1001”);

INSERT INTO CUSTOMER VALUES(“104”,“SATHWIK”,”MANDYA”,”10”,”1004”);

INSERT INTO CUSTOMER VALUES(“105”,”GURUDEVANAYAK”,”BIDAR”,”7”,”1002”);

INSERT INTO CUSTOMER

VALUES(“106”,”VASUMURTHI”,”MYSORE@123”,”9”,”1003”);

INSERT INTO ORDERS VALUES(“10”,”23000”,”2019/09/17”,”100”,”1000);

INSERT INTO ORDERS VALUES(“11”,”26455”,”2019/06/03”,”102”,”1001‟);

INSERT INTO ORDERS VALUES(“12”,”30002”,”2019/01/03”,”103”,”1002”);

INSERT INTO ORDERS VALUES(“13”,”17003”,”2019/07/17”,”105”,”1003”);

INSERT INTO ORDERS VALUES(“14”,“45255”,”2019/05/13”,”106”,”1004”);

INSERT INTO ORDERS VALUES(“15”,”32000”,”2019/07/23”,”101”,”1000”);

INSERT INTO ORDERS VALUES(“16”,”27000”,”2019/05/23”,”104”,”1002”);

INSERT INTO ORDERS VALUES(“17”,”26255”,”2019/09/17”,”104”,”1000”);

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 16

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

SELECT * FROM SALESMAN;

SELECT * FROM CUSTOMER;

SELECT * FROM ORDERS;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 17

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

1. Count the customers with grades above banglore‟s average.

SELECT COUNT(CUSTOMER_ID),GRADE FROM CUSTOMER WHERE

GRADE>=(SELECT AVG(GRADE) FROM CUSTOMER WHERE
CITY=”BANGLORE”)GROUP BY GRADE;

2. Find the name and numbers of all salesman who has more than one customer.

SELECT SALESMAN_ID,NAME FROM SALESMAN WHERE SALESMAN_ID

IN(SELECT SALESMAN_ID FROM CUSTOMER GROUP BY SALESMAN_ID
HAVING COUNT(CUSTOMER_ID)>1);

3. List all the salesman and indicate those who have and don‟t have customers in their
cities (Use UNION Operation).

(SELECT SALESMAN_ID, NAME FROM CUSTOMER c, SALESMAN s

WHERE s. SALESMAN_ID=c. SALESMAN_ID AND s. CITY=c. CITY)
UNION
(SELECT SALESMAN_ID, NAME FROM CUSTOMER c, SALESMAN s WHERE s.
SALESMAN_ID=c, SALESMAN_ID AND s. CITY!=c. CITY));

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 18

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

4. Create a view that finds the salesman who has the customer within the highest order
of day.

CREATE VIEW SAL_MAX AS(SELECT s. SALESMAN_ID, NAME ,c.

ORDER_DATE FROM SALESMAN s, ORDERS c WHERE c. SALESMAN_ID=s.
SALESMAN_ID AND PURCAHSE_AMOUNT=(SELECT
MAX(PURCHASE_AMOUNT) FROM ORDERS o WHERE o. ORDER_DATE=c.
OREDER_DATE;

SELECT * FROM SAL_MAX;

5. Demonstrate the DELETE operation by removing salesman with id 1000,but all his
orders must be deleted.

DELETE FROM SALESMAN WHERE SALESMAN_ID=1000;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 19

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 20

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

3. Consider the schema for Movie Database

ACTOR(Act_ id, Act_ name, Act_ gender)
DIRECTOR(Dir_ id, Dir_ name, Dir_ phone)
MOVIES(Mov_ id, Mov_ title, Mov_ year, Mov_ lang, dir- id)
MOVIE_CAST(Act_ id, Mov_ id, Role)
RATING(Mov_ id, Rev_ stars)

Write SQL queries to

1. List the titles of all movies directed by „Hitchcock‟.

2. Find the movie names where one or more actors acted in two or more movies.
3. List all actors who acted in movie before 2000 and also in a movie after 2015 (use JOIN
operation).
4. Find the title of movies and number of stars for each movie that has at least one rating
and find the highest number of stars that movie received. Sort the result by movie title.
5. Update rating of all movies directed by “Steven Spielberg‟ to 5.

Solution:

ER –Diagram:

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 21

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 22

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

Schema diagram:

ACTOR

Act_ id Act_ name gender

DIRECTOR

Dir_ id Dir_ name Dir_phone

MOVIES

Mov_ id Mov_ tit Mov_ year Mov_ lang Dir_ id

MOVIE_CAST

Act_ id Mov_ id Role

RATING

Mov_ id Rev_ stars

Table Creation:

CREATE TABLE ACTOR(ACT_ID INT PRIMARY KEY,ACT_NAME CHAR(20), GENDER

CHAR(6));

CRAETE TABLE DIRECTOR(DIR_ID INT PRIMARY KEY,DIR_NAME

CHAR(20),DIR_PHONE REAL);

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 23

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

CREATE TABLE MOVIES(MOV_ID INT PRIMARY KEY,MOV_TITLE

VARCAHR(20),MOV_YEAR REAL,MOV_LANG CHAR(10),DIR_ID INT,FOREIGN
KEY(DIR_ID) REFERENCES DIRECTOR(DIR_ID));

CRAETE TABLE MOVIE_CAST(ACT_ID INT,MOV_ID INT,ROLE CHAR(15),PRIMARY

KEY(ACT_ID,MOV_ID),FOREIGN KEY(ACT_ID) REFERENCES
ACTOR(ACT_ID),FOREIGN KEY(MOV_ID) REFERENCES MOVIES(MOV_ID));

CREATE TABLE RATING(MOV_ID INT,REV_STARS INT,FOREIGN KEY(MOV_ID)

REFERENCES MOVIES(MOV_ID));

Insertion of values to tables:

INSERT INTO ACTOR VSLUES(“100”,”PUNITHRAJKUMAR”,”MALE”);

INSERT INTO ACTOR VALUES(“101”,”RAKSHITHAPREM”,”FEMALE”);

INSERT INTO ACTOR VALUES(“102”,”CHIKKANNA”,”MALE‟);

INSERT INTO ACTOR VALUES(“103”,”SHARAN”,”MALE”);

INSERT INTO ACTOR VALUES(“104”,”SADHUKOKILA”,”MALE”);

INSERT INTO ACTOR VALUES(“105”,”MANJULA”,”FEMALE”);

INSERT INTO DIRECTOR VALUES(“10”,”HITCHCOCK”,”9933221144”);

INSERT INTO DIRECTOR VALUES(“11”,”STEVEN SPIELBERG”,”9876541234”);

INSERT INTO DIRECTOR VALUES(“12”,”PAVANVODEYAR”,”9632587410”);

INSERT INTO DIRECTOR VALUES(“13”,”YOGRAJBHAT”,”7412589630”);

INSERT INT0 DIRECTOR VALUES(“14”,”S NARAYAN”,”8521479630”);

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 24

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

INSERT INTO DIRECTOR VALUES(“15”,”UPENDRA”,”9631478520”);

INSERT INTO MOVIES VALUES(“50”,”RAJAKUMARA”,”2016”,”KANNADA”,”12‟);

INSERT INTO MOVIES VALUES(“51”,”50SHADESOFGREY”,”2018”,”ENGLISH”,”10”);

INSERT INTO MOVIES VALUES(“52”,”KANJURING”,”1999”,”ENGLISH”,”11”);

INSERT INTO MOVIES VALUES(“53”,”HEARTATTACK”,”1999”,”TELAGU”,”10”);

INSERT INTO MOVIES VALUES(“54”,”ILUVU”,”2019”,”KANNADA”,”15”);

INSERT INTO MOVIES(“55”,”HALUJENU”,”1998”,”KANNADA”,”14”);

INSERT INTO MOVIE_CAST VALUES(“101”,”52”,”HEROIN”);

INSERT INTO MOVIE_CAST VALUES(“102”,”50”,”COMEDIAN”);

INSERT INTO MOVIE_CAST VALUES(“102”,”51”,”SIDEACTOR”);

INSERT INTO MOVIE_CAST VALUES(“102”,”54”,”COMEDIAN”);

INSERT INTO MOVIE_CAST VALUES(“103”,”50”,”GUESTROLE”);

INSERT INTO MOVIE_CAST VALUES(“103”,”53”,”HERO”);

INSERT INTO MOVIE_CAST VALUES(“104”,”50”,”COMEDIAN”);

INSERT INTO MOVIE_CAST VALUES(“104”,”55”,”COMEDIAN”);

INSERT INTO RATING VALUES(“50”,”5”);

INSERT INTO RATING VALUES(“50”,”5”);

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 25

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

INSERT INTO RATING VALUES(“51”,”5”);

INSERT INTO RATING VALUES(“55”,”5”);

INSERT INTO RATING VALUES(“52”,”4”);

INSERT INTO RATING VALUES(“53”,”5”);

INSERT INTO RATING VALUES(“54”,”3”);

INSERT INTO RATING VALUES(“50”,”4”);

INSERT INTO RATING VALUES(“50”,”2”);

SELECT * FROM ACTOR;

SELECT * FROM DIRECTOR;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 26

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

SELECT * FROM MOVIES;

SELECT * FROM MOVIE_CAST;

SELECT * FROM RATING;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 27

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

Queries:

1. List the titles of all the movies directed by Hitchcock.

SELECT MOVIE_TITLE FROM DIRECTOR d, MOVIES m WHERE d. DIR_ID=m.

DIR_ID AND DIR_NAME=”HITCHCOCK”;

2. Find the movie names where one or more actors acted in two or more movies.

SELECT MOV-TITLE FROM MOVIES m, MOVIE_CAST v WHERE m. MOV_ID=v.

MOV_ID AND ACT_ID IN(SELECT ACT_ID FROM MOVIE_CAST GROUP BY
ACT_ID HAVING COUNT(*)>1)GROUP BY MOV_TITLE HAVING COUNT(*)>1;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 28

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

3. List all actors who are acted in movie before 2000 and also in a movie after 2015 (use
JOIN operation).

SELECT a.ACT_ ID FROM ACTORS a, MOVIES m, MOVIE_CAST c WHERE

a.ACT_ ID=c.ACT_ID AND m. MOV_ID=c.MOV_ID AND MOV_YEAR<2000 AND
a.ACT_ID IN(SELECT b.ACT_ID FROM ACTORS b,MOVIES n,MOVIE_CAST d
WHERE b.ACT_ID=d.ACT_ID AND n.MOV_ID=d.MOV_ID MOV_YEAR>2015);

4. Find the title of movies and number of stars for each movies that as at least one
rating, and find the highest number of stars that movie receives, sort the movie
title by the name.

SELECT MOV_TITLE,MAX(REV_STARS) FROM MOVIES m, RATING r WHERE

m.MOV_ ID=r.MOV_ ID GROUP BY MOV_TITLE HAVING MAX(REV_STARS)>0
ORDER BY MOV_TITLE;

5. Update the rating of all the movies directed by Steven Spielberg to 5.

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 29

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

UPDATE RATING SET REV_STARS=5 WHERE MOV_ID IN(SELECT MOV_ID

FROM MOVIES WHERE DIR_ID IN(SELECT DIR_ID FROM DIRECTOR WHERE
DIR_NAME=”STEVEN SPIELBERG”));

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 30

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

4. Consider the schema for college Database:

STUDENT(USN, Sname, Address, Phone, Gender)
SEMSEC(SSID, Sem, Sec)
CLASS(USN, SSID)
SUBJECT(Subcode, Title, Sem, Credits)
IA_MARKS(USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)

Write SQL queries to

1. List all the students details studying in fourth semester „c‟ section.
2. Compute the total number of male and female students in each semester and in each
section.
3. Create a view of Test1 marks of student USN „1BI15CS101‟ in all subjects.
4. Calculate the FinalIA (average of best two test marks) and update the corresponding table
for all students.
5. Categorize students based on the following criterion: If FinalIA=17 to 20 then
CAT=‟Outstanding‟. If FinalIA=12 to 16 then CAT=‟Average‟. If FinalIA<12 then
CAT=‟Weak‟.
Give these details only for 8th semester A, B and C section students.

Solution:

ER – Diagram:

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 31

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 32

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

Schema diagram;

STUDENT

USN S name Address Phone Gender

SEM_SEC

SSID Sem Sec

CLASS

USN SSID

SUBJECT

Sub code title Sem credits

IA_MARKS

USN Sub code SSID Test1 Test2 Test3 Final ia

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 33

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

TABLE CREATION

CREATE TABLE STUDENT(USN VARCHAR(15) PRIMARY KEY,SNAME

CHAR(20),ADDRESS VARCHAR(20),PHONE REAL,GENDER CHAR(6));

CREATE TABLE SEM_SEC(SSID INT PRIMARY KEY,SEM INT,SEC CHAR(4));

CREATE TABLE CLASS(USN VARCHAR(15),SSID INT,PRIMARY

KEY(USN,SSID),FOREIGN KEY(USN) REFERENCES STUDENT(USN),FOREIGN
KEY(SSID) REFERENCES SEM_SEC(SSID));

CREATE TABLE SUBJECT(SUBCODE VARCHAR(10) PRIMARY KEY,TITLE

VARCHAR(10),SEM INT,CREDITS INT);

CREATE TABLE IA_MARKS(USN VARCHAR(15),SUBCODE VARCHAR(10),SSID

INT,PRIMARY KEY(USN,SUBCODE,SSID),TEST1 FLOAT,TEST2 FLOAT,TEST3
FLOAT,FINALIA FLOAT,FOREIGN KEY(USN) REFERENCES
STUDENT(USN),FOREIGN KEY(SUBCODE) REFERENCES
SUBJECT(SUBCODE),FOREIGN KEY(SSID) REFERENCES SEM_SEC(SSID));

Table description:

DESC STUDENT;

DESC SEM_SEC;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 34

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

DESC CLASS;

DESC SUBJECT;

DESC IA_MARKS;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 35

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

Insertion of values to tables:

INSERT INTO STUDENT

VALUES(“1BI15CS101”,”SINISTAR”,”MALLESHWARAM”,”9988776655”,”MALE”);

INSERT INTO STUDENT

VALUES(“1BI15ME121”,”DRACCU”,”MYSORE”,”8523697413”,”MALE”);

INSERT INTO STUDENT

VALUES(“4GS17CS031”,”SUSHMITHA”,”MYSORE@123”,”9632587410”,”FEMALE”);

INSER INTO STUDENT

VALUES(“4MH15IS006”,”DRUSHYA”,”MADDUR”,”7090454640”,”FEMALE”);

INSERT INTO STUDENT VALUES(“4MH17IS053”,”NISHA

GOWDA”,”MANDYA”,”9380577730”,”FEMALE”);

INSERT INTO STUDENT VALUES(“5BI15EC108”,”SMILE

STAR”,”KODIYALA”,”7412589630”,”FEMALE”);

INSERT INTO SEM_SEC VALUES(“100”,”4”,”C”);

INSERT INTO SEM_SEC VALUES(“101”,”4”,”A”);

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 36
Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

INSERT INTO SEM_SEC VALUES(“102”,”8”,‟C”);

INSERT INTO SEM_SEC VALUES(“103”,”8”,”B”);

INSERT INTO SEM_SEC VALUES(“104”,”4”,”C”);

INSERT INTO SEM_SEC VALUES(“105”,”6”,”A”);

INSERT INTO SEM_SEC VALUES(“106”,”6”,”B”);

INSERT INTO SEM_SEC VALUES(“107”,”6”,”C”);

INSERT INTO SEM_SEC VALUES(“108”,”8”,”B”);

INSERT INTO SEM_SEC VALUES(“109”,”4”,”B”);

INSERT INTO CLASS VALUES(“1BI15CS101”,”100”);

INSERT INTO CLASS VALUES(“4MH17IS053”,”102”);

INSERT INTO CLASS VALUES(“1BI15ME121”,”103”);

INSERT INTO CLASS VALUES(“4GS17CS031”,”104”);

INSERT INTO CLASS VALUES(“5BI15EC108”,”105”);

INSERT INTO CLASS VALUES(“4MH15IS006”,”107”);

INSERT INTO SUBJECT VALUES(“17CS51”,”M&EFORITI”,”6”,”32”);

INSERT INTO SUBJECT VALUES(“17CS52”,”CPTRNTWRKS”,”4”,”34”);

INSERT INTO SUBJECT VALUES(“17CS53”,”DBMS”,”8”,”38”);

INSERT INTO SUBJECT VALUES(“17CS54”,”ATC”,”6”,”30”);

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 37

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

INSERT INTO SUBJECT VALUES(“17CS551”,”OOMD”,”6”,”28”);

INSERT INTO SUBJECT VALUES(“17CS552”,”CSHARP”,”4”,”30”);

INSERT INTO IA_MARKS

VALUES(“1BI15CS101”,”17CS51”,”100”,”10”,”18”,”19‟,”NULL”);

INSERT INTO IA_MARKS

VALUES(“1BI15CS101”,”17CS551”,”108”,”18”,”18”,”18”,”NULL”);

INSERT INTO IA_MARKS

VALUES(“1BI15ME121”,”17CS552”,”107”,”15”,”12‟,”12”,”NULL”);

INSERT INTO IA_MARKS

VALUES(“4GS17CS031”,”17CS53”,”105”,”12‟,”8”,”5”,”NULL”);

INSERT INTO IA_MARKS VALUES(“4MH15IS006”,”17CS54”,”102”,”15”,”20”,”18”);

INSERT INTO IA_MARKS

VALUES(“4MH17IS053”,”17CS53”,”101”,”20”,”18‟,”19”,”NULL”);

INSERT INTO IA_MARKS

VALUES(“4MH17IS053”,”17CS53”,”103”,”18”,”18”,”18‟,”NULL”);

SELECT * FROM STUDENT;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 38

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

SELECT * FROM SEM_SEC;

SELECT * FROM CLASS;

SELECT * FROM SUBJECT;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 39

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

SELECT * FROM IA_MARKS;

Queries:s

1. List all the student details studying in 4th sem „C‟ section.

SELECT s. USN, SNAME, ADDRESS FROM STUDENT s, SEM_ SEC m, CLASS C

WHERE s. USN=c. USN AND m. SSID=c. SSID AND SEM=”4” AND SEC=”C”;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 40

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

2. Compute the total number of male and female students in each semester and in each
section.

SELECT m. SEC, m. SEC, s. GENDER, COUNT(GENDER) FROM STUDENT s,

SEM_ SEC m, CLASS c WHERE s. USN=c. USN AND m. SSID=c. SSID GROUP BY
m. SEM, m. SEC, s. GENDER ORDER BY SEM;

3. Create a view of tset1 marks of student USN,1bi15cs101 in all subjects.

CREATE VIEW S1 AS(SELECT TEST1,SUBCODE FROM IA_MARKS WHERE

USN=”1BI15CS101”);

SELECT * FROM S1;

4. Calculate the FinalIA(average of best two test marks) and update corresponding
table for all students.

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 41

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

UPDATE IA_MARKS SET

FINALIA=GREATEST(TEST1+TEST2,TEST2+TEST3,TEST1+TEST3)/2;
OR
UPDATE IA_MARKS SET FINALIA=((TEST1+TEST2+TEST3)-
LEAST(TEST1,TEST2,TEST3))/2;

5. Catagarize the students based on the following criterian : If Final IA=17 to 20 then
CAT=‟Outstanding‟
If Final IA=12 to 16 the CAT=‟Average‟
If Final IA<12 the CAT=‟Weak‟.
Give the details only for 8th sem „A‟,‟B‟ and „C‟ sec.

SELECT s. USN, SNAME,ADDRESS,GENDER,(CASE WHEN FINALIA BETWEEN

17 AND 20 THEN “OUTSTANDING” WHEN FINALIA BETWEEN 12 AND 16
THEN “AVERAGE” ELSE “WEAK” END) AS CAT FROM STUDENT s, IA_MARKS
I,SUBJECT b, SEM_SEC m WHERE s. USN=i. USN AND m. SSID=i. SSID AND b.
SUBCODE=i. SUBCODE AND m. SEM=”8” AND m. SEC IN(“A”,”B”,”C”);

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 42

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

5 Consider the schema for Company Database:

EMPLOYEE(SSN, Name, Address, Sex, Salary, SuperSSN, Dno)

DEPARTMENT(Dno, Dname, MgrSSN, MgrstartDate)
DLOACTION(Dno, Dloc)
PROJECT(Pno, Pname, Ploaction, Dno)
WORKS_ON(SSN, Pno, Hours)

Write SQL queries to

1. Make a list of all project numbers for projects that involve an employee whose last name
is „Scott‟, either as a worker or as number of the department that controls the project.
2. Show the resulting salaries if every employee working on the „IOT‟ project is given a 10
percent raise.
3. Find the sum of the salaries of all employees of the „accounts‟ department, as well as the
maximum salary, the minimum salary, and the average salary in this department.
4. Retrieve the name of each employee who works on all the operation Projects controlled
by department number 5 (use NOT EXITS operation).
5. For each department that has more than five employees. Retrieve the department number
and the number of its employees who are making more than Rs 6,00,000

Solution:

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 43

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

Schema diagram:

EMPLOYEE

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 44

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

SSN fname lname address sex salary superSSn dno

DEPARTMENT

dno dname mgrSSN mgrstartdate

DLOCATION

dno dloc

PROJECT

pno pname plocation dno

WORKSON

SSN pno hours

TABLE CREATION

CREATE TABLE EMPLOYEE(SSN INT PRIMARY KEY,FNAME CHAR(10),LNAME

CHAR(10),ADDRESS VARCHAR(15),SEX CHAR(6),SALARY REAL,SUPERSSN
INT,DNO INT);

CREATE TABLE DEPARTMENT(DNO INT PRIMARY KEY,DNAME CHAR(15),MGRSSN

INT,MGERSTART DATE);

CREATE TABLE DLOACTION(DNO INT,DLOC CHAR(10),PRIMARY

KEY(DNO,DLOC),FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 45

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

CREATE TABLE PROJECT(PNO INT PRIMARY KEY,PNAME CHAR(15),PLOACTION

CHAR(10),DNO INT,FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));

CREATE TABLE WORKS_ON(SSN INT,PNO INT,PRIMARY KEY(SSN,PNO),FOREIGN

KEY(SSN) REFERENCES EMPLOYEE(SSN),FOREIGN KEY(PNO) REFERENCES
PROJECT(PNO),HOURS INT);

Table description:

DESC EMPLOYEE;

DESC DEPARTMENT;

DESC DLOCATION;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 46

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

DESC PROJECT;

DESC WORKS_ON;

ALTERING COMMANDS

ALTER TABLE EMPLOYEE ADD CONSTRAINT S FOREIGN KEY(DNO) REFERENCES

DEPARTMENT(DNO);

ALTER TABLE DEPARTMENT ADD CONSTRAINT R FOREIGN KEY(MGRSSN)

REFERENCES EMPLOYEE(SSN);

DESC EMPLOYEE;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 47

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

DESC DEPARTMENT;

INSERT ION OF VALUES TO TABLES

INSERT INTO EMPLOYEE

VALUES(“100”,”WATTSON”,”SCOTT”,”MYSORE12”,”MALE”,”750000”,”100”,”1”);

INSERT INTO EMPLOYEE

VALUES(“101”,”MARRY”,”STEVE”,”MYSORE123”,”FEMALE”,”850000”,”100”,”1”);

INSERT INTO EMPLOYEE

VALUES(“102”,”STEVE”,”JOBS”,”BANGLORE123”,”MALE”,”800000”,”101”,”5”);

INSERT INTO EMPLOYEE

VALUES(“103”,”RED”,”KANE”,”BANGLORE13”,”MALE”,”900000”,”5”);

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 48

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

INSERT INTO EMPLOYEE

VALUES(“104”,”JOHN”,”CENA”,”BANGLORE25”,”MALE”,”950000”,”100”,”1”);

INSERT INTO EMPLOYEE

VALUES(“105”,”BLOCK”,”LUSTER”,”BANGLORE15”,”FEMALE”,”750000”,”100”,”1”);

INSERT INTO EMPLOYEE

VALUES(“106”,”KALI”,”SHOW”,”INDIA”,”MALE”,”850000”,”102”,”1”);

INSERT INTO EMPLOYEE

VALUES(“107”,”RUSEV”,”LENSER”,”RUSSIA”,”MALE”,”800000”,”100”,”1”);

INSERT INTO EMPLOYEE

VALUES(“108”,”WATTSON”,”SCOTT”,”MYSORE12”,”MALE”,”750000”,”100”,”1”);

IINSERT INTO DEPARTMENT VALUES(“1”,”ACCOUNTS”,”100”,”1999/01/03”);

INSERT INTO DEPARTMENT VAULES(“2”,”HOLDER”,”102”,”1989/02/24”);

INSERT INTO DEPARTMENT VALUES(“3”,”BALANCE”,”101”,”1982/07/17”);

INSERT INTO DEPARTMENT VALUES(“4”,”ACNTMAKR”,”100”,”1991/12/24”);

INSERT INTO DEPARTMENT VALUES(“5”,”SCHLORSHP”,”101”,”2000/07/17”);

INSERT INTO DLOACTION VALUES(“1”,”GRNDFLR”);

INSERT INTO DLOCATION VALUES(“1”,”TOPFLR”);

INSERT INTO DLOCATION VALUES(“3”,”HYRIGE”);

INSERT INTO DLOCATION VALUES(“4”,MYSORE”);

INSERT INTO DLOCATION VALUES(“5”,”MANDYA”);

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 49

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

INSERT INTO PROJECT VALUES(“1001”,”IOT”,”MYS”,”1”);

INSERT INTO PROJECT VALUES(“1002”,”L&T”,”BAN”,”2”);

INSERT INTO PROJECT VALUES(“1003”,”MED”,”MAN”,”1”);

INSERT INTO PROJECT VALUES(“1004”,”CPG”,”SOUTH”,”5”);

INSERT INTO PROJECT VALUES(“1005”,”VIP”,”NORTH”,”5”);

INSERT INTO PROJECT VALUES(“1006”,”INF”,”WEST”,”3”);

INSERT INTO WORKSON VALUES(“100”,”1001”,”2”);

INSERT INTO WORKSON VALUES(“100”,”1002”,”3”);

INSERT INTO WORKSON VALUES(“101”,”1003”,”3”);

INSERT INTO WORKSON VALUES(“107”,”1004”,”1”);

INSERT INTO WORKSON VALUES(“107”,”1005”,”4”);

INSERT INTO WORKSON VALUES(“107”,”1006”,”3”);

SELECT * FROM EMPLOYEE;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 50

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

SELECT * FROM DEPARTMENT;

SELECT * FROM DLOCATION;

SELECT * FROM PROJECT;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 51

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

SELECT * FROM WORKSON;

Queries:

1. make a list of all projects numbers for projects that involve an employee whose last name
is „scott‟, either as a worker or as a manager of the department that controller project.

(SELECT PNO FROM EMPLOYEE e, WORKSON w WHERE e.SSN=w.SSN AND

LNAME=”SCOTT”) UNION (SELECT PNO FROM DEPARTMENT d, PROJECT p,
EMPLOYEE e WHERE SSN=MGRSSN AND d.DNO=p.DNO AND LNAME=”SCOTT”);

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 52

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

2. Show the resulting salaries if every employee working on the „IOT‟ project is given a 10
percent raise.

SELECT SALARY*1.1 FROM EMPLOYEE e, PROJECT p, WORKSON w WHERE

e.SSN=w.SSN AND p.PNO=w.PNO AND PNAME=”IOT”;

3. Find the sum of the salaries of all employees of the „Accounts‟ department, as well as the
maximum salary, the minimum salary, and the average salary in this department.

SELECT SUM(SALARY),MAX(SALARY),MIN(SALARY),AVG(SALARY) FROM

EMPLOYEE e, DEPARTMENT d WHERE e.DNO=d.DNO AND DNAME=”ACCOUNTS”;

4. Retrieve the name of each employee who workers on all the projects controlled by
department number 5 (use NOT EXIST operator).

SELECT FNAME, LNAME FROM EMPLOYEE e WHERE NOT EXISTS(SELECT PNO

FROM PROJECT WHERE DNO=5 AND PNO NOT IN(SELECT PNO FROM WORKSON
WHERE e. SSN=SSN));

5. For each department that has more than five employees, retrieve the department
number and the number of its employees who are making more than Rs.6,00,000.

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 53

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

SELECT d. DNO, COUNT(*) FROM DEPARTMENT d, EMPLOYEE e WHERE d. DNO=e.

DNO AND SALARY=6,00,000 AND d. DNO IN(SELECT e1.DNO FROM EMPLOYEE e1
GROUP BY e1.DNO HAVING COUNT(*)>5) GROUP BY d.DNO;

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 54

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

Viva Questions:
1. What is SQL?
Structured Query Language.
2. What is database?
A database is a logically coherent collection of data with some inherent meaning,
representing some aspects of real world and which is designed, built and populated with
data for a specific purpose.
3. What is DBMS?
It is a collection of programs that enables the users to create and maintain a database. In
other words it is general-purpose software that provides the users with the processes of
defining, constructing and manipulating the database for various applications.
4. What is database system?
The database and DBMS software together is called as Database system.
5. Advantages of DBMS?
 Redundancy is controlled.
 Unauthorized access is restricted.
 Providing multiple user interfaces.
 Enforcing integrity constraints.
 Providing backup and recovery.
6. Disadvantages in file Processing System?
 Data redundancy and inconsistency.
 Difficult in accessing data.
 Data integrity.
 Concurrent access is not possible.
 Security problems.
7. Describe the three levels of data abstraction?
There are three levels of abstraction:
 Physical level: the lowest level of abstraction describes how data are stored.
 Logical level: the next higher level of abstraction, describes what data are stored
in database and what relationship among those data.
 View level: the highest level of abstraction describes only part of entire database.

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 55

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

8. Define the “Integrity” rules.

There are two integrity rules:
 Entity Integrity: States that- primary key cannot be NULL value.
 Referential Integrity: States that- Foreign key can be either a NULL value or
should be Primary key value of other relation.
9. What is extension and intension?
Extension – It is the number of tuples present in a table at any instance. This is a time
dependent.
Intension – It is a constant value that gives the name, Structure of the table and the
constraints laid on it.
10. What is data independence?
Data independence means that – the application is independent of the storage structure
and access strategy of data. In other words, the ability to modify the schema definition in
one level should not affect the schema definition in the next higher level.
Two types of Data Independence.
 Physical Data Independence: Modification in physical level should not affect the
logical level.
 Logical Data Independence: Modification in logical level should affect the view
level.
NOTE: Logical Data Independence is more difficult to achieve.
11. What is a view? How it is related to data independence?
A view may be thought of as a virtual table, that is, a table that does not really exist in its
own right but is instead derived from one or more underlying base table. In other words,
there is no stored file that direct represents the view instead a definition of view is stored
in data dictionary.
Growth and restructuring of base tables in not reflected in views. Thus the view can
insulate users from the effects of restructuring and growth in the database. Hence
accounts for logical data independence.
12. What is Data model?
A collection of conceptual tools for describing data, data relationships data semantic and
constraints.

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 56

Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

13. What is E-R model?

This data model is based on real world that consists of basic objects called entities and of
relationship among these objects. Entities are described in a database by a set of
attributes.
14. What is object oriented model?
This model is based on collection of objects. An object contains values stored in instance
variables within the object. An object also contains bodies of code that operate on the
object. These bodies of code are called methods. Objects that contain same types of
values and the same methods are grouped together into classes.
15. What is an Entity?
It is an „object‟ in the real world with an independent existence.
16. What is an Entity type?
It is a collection (set) of entities that have same attributes.
17. What is an Entity set?
It is a collection of all entities of particular entity type in the database.
18. What is an Extension of entity type?
The collection of entities of a particular entity type are grouped together into an entity set.
19. What is an attribute?
It is a particular property, which describes the entity.
20. What is a Relation Schema and a Relation?
A Relation Schema denoted by R (A1, A2, …..., An) is made up of the relation name R
and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let R
be the relation which contains set tuples ( t1, t2, t3, ….., tn). Each tuple is an
ordered list of n-values t=(v1, v2, ……, vn).
21. What is degree of a relation?
It is an number of attribute of its relation schema.
22. What is Relationship?
It is an association among two or more entities.
23. What is Relationship set?
The collection (or set) of similar relationship.
24. What is Relationship type?
Relationship type defines a set of associations or a relationship set among a given set of
DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 57
Maharaja Institute of Technology, Mysore Department of Information Science and Engineering

entity types.
25. What is degree of Relationship type?
It is the number of entity type participating.
26. What is DDL (Data Definition Language)?
A data base schema is specified by a set of definitions expressed by a special language
called DDL.
27. What is VDL (View Definition Language)?
It specifies user views and their mapping to the conceptual schema.
28. What is SDL (Storage Definition Language)?
This language is to specify the internal schema. This language may specify the mapping
between two schemas.
29. What is Data storage- Definition Language?
The storage structures and access methods used by database system are specified by a set
of definition in a special type of DDL called Data storage – definition language.

DBMS LABORATORY WITH MINI PROJECT (17CSL58) Page 58