Dbms Lab Manual Final

Download as pdf or txt
Download as pdf or txt
You are on page 1of 62

DEPARTMENT OF C O M P U T E R S C I E N C E A N D

ENGINEERING

LABORATORY MANUAL

DBMS LABORATORY WITH MINI PROJECT

Semester: V

Prepared By

Ms.Anjana Kulkarni
VTU, Belagavi.
Program Educational Objectives (PEOs)

Graduates will be able to:


PEO1: Utilize the strong foundation in mathematics, programming, scientific and
engineering fundamentals necessary to formulate, analyze and solve IT related
engineering problems, and endeavour themselves for higher learning.

PEO2: Demonstrate an ability to analyze the requirements and technical specifications


of software to articulate novel engineering solutions for an efficient product
design.

PEO3: Adapt to emerging technologies, and work as teams on multidisciplinary projects


meeting the requirements of Indian and multinational companies.

PEO4: Pursue a professional career adopting work values with a social concern to
bridge the digital divide and develop effective communication skills and
leadership qualities, and

PEO5: Understand the efficacy of life-long learning, professional ethics and practices,
so that they may emerge as global leaders.
Program Outcomes:
PO Number Title
PO 1 Engineering knowledge

PO 2 Problem analysis

PO 3 Design/development of solutions

PO 4 Conduct investigations of complex problems

PO 5 Modern tool usage

PO 6 The engineer and society

PO 7 Environment and sustainability

PO 8 Ethics

PO 9 Individual and team work

PO 10 Communication

PO 11 Project management and finance

PO 12 Life-long learning

Course Outcomes:

CO Description

CO1 Dbms Laboratory With Mini Project

CO2 Demonstrate the working of different concepts of DBMS

CO3 Implement, analyze and evaluate the project developed for an application.
MySql 8.1
SOFTWARE REQUIREMENT

Software Installation Procedure


• After downloading the setup, unzip it anywhere and double click the MSI installer .exe file
• In the next wizard, choose the Setup Type. There are several types available, and you need to choose the
appropriate option to install MySQL product and features. Here, we are going to select the Full option and
click on the Next button.
• In the next wizard, choose the Setup Type. There are several types available, and you need to choose the
appropriate option to install MySQL product and features. Here, we are going to select the Full option and
click on the Next button.
• In the next wizard, choose the Setup Type. There are several types available, and you need to choose the
appropriate option to install MySQL product and features. Here, we are going to select the Full option and
click on the Next button.
• In the next wizard, choose the Setup Type. There are several types available, and you need to choose the
appropriate option to install MySQL product and features. Here, we are going to select the Full option and
click on the Next button.
• In the next wizard, choose the Setup Type. There are several types available, and you need to choose the
appropriate option to install MySQL product and features. Here, we are going to select the Full option and
click on the Next button.
• In the next wizard, choose the Setup Type. There are several types available, and you need to choose the
appropriate option to install MySQL product and features. Here, we are going to select the Full option and
click on the Next button.
• In the next wizard, choose the Setup Type. There are several types available, and you need to choose the
appropriate option to install MySQL product and features. Here, we are going to select the Full option and
click on the Next button.
• Now, select the Authentication Method and click on Next. Here, I am going to select the first option.
• Now, select the Authentication Method and click on Next. Here, I am going to select the first option.
• Now, select the Authentication Method and click on Next. Here, I am going to select the first option.
• In the next wizard, the system will ask you to apply the Server Configuration. If you agree with this
configuration, click on the Execute button.
• Once the configuration has completed, you will get the screen below. Now, click on the Finish button to
continue.
• In the next screen, you can see that the Product Configuration is completed. Keep the default setting and
click on the Next-> Finish button to complete the MySQL package installation.
• In the next wizard, we can choose to configure the Router. So click on Next->Finish and then click the Next
butto
• In the next wizard, we will see the Connect to Server option. Here, we have to mention the root password,
which we had set in the previous steps.
• In the next wizard, we will see the Connect to Server option. Here, we have to mention the root password,
which we had set in the previous steps. After completing the above step, we will get the following screen.
Here, click on the Finish button. Now, the MySQL installation is complete. Click on the Finish button.
TABLE OF CONTENT

Sl.no PART- A
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,
Programme_id, No-of_Copies)
BOOK_LENDING (Book_id, Programme_id, Card_No, Date_Out, Due_Date)
LIBRARY_BRANCH ((Programme_id, Branch_Name, Address)
Write SQL queries to
Retrieve details of all books in the library – id, title, name of publisher, authors, number of
copies in each branch, etc.
Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017
to Jun 2017
Delete a book in BOOK table. Update the contents of other tables to reflect this data
manipulation operation.
Partition the BOOK table based on year of publication. Demonstrate its working with a
simple query.
Create a view of all books and its number of copies that are currently available in the
Library.

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 salesmen who had more than one customer.
3. List all salesmen 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.

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.

4 STUDENT (USN, SName, Address, Phone, Gender)


SEMSEC (SSID, Sem, Sec)
CLASS (USN, SSID)
COURSE (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
Courses.
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.

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 controlledby
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
 For any problem selected
 Make sure that the application should have five or more tables
 Indicative areas include; health care
EXPERIMENT-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,
Programme_id, No-of_Copies)
BOOK_LENDING (Book_id, Programme_id, Card_No, Date_Out, Due_Date)
LIBRARY_BRANCH
((Programme_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 copiesin each branch, etc.
2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan
2017 to Jun2017
3. Delete a book in BOOK table. Update the contents of other tables to
reflect this datamanipulation operation.
4. Partition the BOOK table based on year of publication. Demonstrate its working
with a simplequery.
5. Create a view of all books and its number of copies that are currently available in the Library.

Solution:
Entity-Relationship Diagram
SCHEMA DIAGRAM
Book

TABLE CREATION

CREATE TABLE PUBLISHER


(NAME VARCHAR (20)
PRIMARY KEY,
ADDRESS VARCHAR (20),
PHONE BIGINT);
CREATE TABLE BOOK
(BOOK_ID INT (4) PRIMARY KEY,
TITLE VARCHAR (20),
PUB_YEAR VARCHAR (20),
PUBLISHER_NAME VARCHAR (20), FOREIGN KEY (PUBLISHER_NAME) REFERENCES
PUBLISHER (NAME) ON DELETE CASCADE);

CREATE TABLE BOOK_AUTHORS


(BOOK_ID INT,
AUTHOR_NAME VARCHAR (20),
PRIMARY KEY (BOOK_ID, AUTHOR_NAME),
FOREIGN KEY (BOOK_ID) REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE);

CREATE TABLE LIBRARY_BRANCH


(BRANCH_ID INT PRIMARY KEY,
BRANCH_NAME VARCHAR (20),
ADDRESS VARCHAR (30));

CREATE TABLE BOOK_COPIES


(BOOK_ID INT,
BRANCH_ID INT,
NO_OF_COPIES INT,
PRIMARY KEY (BOOK_ID, BRANCH_ID),
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 CARD


(CARD_NO INT PRIMARY KEY);

CREATE TABLE BOOK_LENDING


(BOOK_ID INT,
BRANCH_ID INT,
CARD_NO INT NOT NULL,
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);

DESC COMMANDS

DESC PUBLISHER;
DESC BOOK;
DESC LIBRARY_BRANCH;
DESC BOOK_COPIES;
DESC CARD;
DESC BOOK_LENDING;

INSERTION OF VALUES TO TABLES


INSERT INTO PUBLISHER VALUES ('MCGRAW-HILL', 'BANGALORE', 9989076587);
INSERT INTO PUBLISHER VALUES ('PEARSON', 'NEWDELHI', 9889076565);
INSERT INTO PUBLISHER VALUES ('RANDOM HOUSE', 'HYDERABAD', 7455679345);
INSERT INTO PUBLISHER VALUES ('HACHETTE LIVRE', 'CHENNAI', 8970862340);
INSERT INTO PUBLISHER VALUES ('GRUPO PLANETA', 'BANGALORE', 7756120238);

INSERT INTO BOOK VALUES (1,'DBMS','JAN-2017', 'MCGRAW-HILL');


INSERT INTO BOOK VALUES (2,'ADBMS','JUN-2016', 'MCGRAW-HILL');
INSERT INTO BOOK VALUES (3,'CN','SEP-2016', 'PEARSON');
INSERT INTO BOOK VALUES (4,'CG','SEP-2015', 'GRUPO PLANETA');
INSERT INTO BOOK VALUES (5,'OS','MAY-2016', 'PEARSON');

INSERT INTO BOOK_AUTHORS VALUES (1, 'NAVATHE');


INSERT INTO BOOK_AUTHORS VALUES (2, 'NAVATHE');
INSERT INTO BOOK_AUTHORS VALUES (3, 'TANENBAUM');
INSERT INTO BOOK_AUTHORS VALUES (4, 'EDWARD ANGEL');
INSERT INTO BOOK_AUTHORS VALUES (5, 'GALVIN');

INSERT INTO LIBRARY_BRANCH VALUES (10,'RR NAGAR','BANGALORE');


INSERT INTO LIBRARY_BRANCH VALUES (11,'RNSIT','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES (12,'RAJAJI NAGAR', 'BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES (13,'NITTE','MANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES (14,'MANIPAL','UDUPI');

INSERT INTO BOOK_COPIES VALUES (1, 10, 10);


INSERT INTO BOOK_COPIES VALUES (1, 11, 5);
INSERT INTO BOOK_COPIES VALUES (2, 12, 2);
INSERT INTO BOOK_COPIES VALUES (2, 13, 5);
INSERT INTO BOOK_COPIES VALUES (3, 14, 7);
INSERT INTO BOOK_COPIES VALUES (5, 10, 1);
INSERT INTO BOOK_COPIES VALUES (4, 11, 3);

INSERT INTO CARD VALUES (100);


INSERT INTO CARD VALUES (101);
INSERT INTO CARD VALUES (102);
INSERT INTO CARD VALUES (103);
INSERT INTO CARD VALUES (104);

INSERT INTO BOOK_LENDING VALUES (1, 10, 101, '2017-01-22', '2017-06-01');


INSERT INTO BOOK_LENDING VALUES (3, 14, 101, '2017-01-11', '2017-03-11');
INSERT INTO BOOK_LENDING VALUES (2, 13, 101, '2017-02-21', '2017-04-21');
INSERT INTO BOOK_LENDING VALUES (4, 11, 101, '2017-03-15', '2017-07-11');
INSERT INTO BOOK_LENDING VALUES (1, 11, 104, '2017-04-12', '2017-05-12');
SELECT COMMANDS
SELECT *FROM PUBLISHER;
SELECT *FROM BOOK;
SELECT *FROM BOOK_AUTHORS;
SELECT *FROM LIBRARY_BRANCH;
SELECT *FROM BOOK_COPIES;
SELECT *FROM CARD;
SELECT *FROM BOOK_LENDING;

QUERIES:

/*1. Retrieve details of all books in the library – id, title, name of publisher, authors, number
of copies in each branch, etc. */
SELECT B.BOOK_ID, B.TITLE, B.PUBLISHER_NAME, A.AUTHOR_NAME,
C.NO_OF_COPIES, L.BRANCH_ID
FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH L
WHERE B.BOOK_ID=A.BOOK_ID AND B.BOOK_ID=C.BOOK_ID AND
L.BRANCH_ID=C.BRANCH_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 '01-JAN-17'
and '01- JUL17' group by(CARD_NO) having count(CARD_NO)>3 ;

/* 3. Delete a book in BOOK table. Update the contents of other tables to reflect this data
Manipulation operation. */
DELETE FROM BOOK WHERE BOOK_ID=3;
SELECT * FROM BOOK;
/* 4. Partition the BOOK table based on year of publication. Demonstrate its working with a
simple query. */
CREATE VIEW YEAR AS SELECT PUB_YEAR FROM BOOK;
SELECT * FROM YEAR;

/* 5. Create a view of all books and its number of copies that are currently available in the
Library. */
CREATE VIEW V_BOOKS AS SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
FROM BOOK B, BOOK_COPIES C WHERE B.BOOK_ID=C.BOOK_ID;
SELECT * FROM V_BOOKS;

OUTPUT

mysql> SELECT *FROMPUBLISHER;


+ + + +
| NAME | ADDRESS | PHONE |
+ + + +
| GRUPO PLANETA | BANGALORE | 7756120238 |
| HACHETTE LIVRE | CHENNAI | 8970862340 |
| MCGRAW-HILL | BANGALORE | 9989076587 |
| PEARSON | NEWDELHI | 9889076565 |
| RANDOM HOUSE | HYDERABAD | 7455679345 |
+ + + +
5 rows in set (0.00 sec)

mysql> SELECT *FROM BOOK;


+ + + + +
| BOOK_ID | TITLE | PUB_YEAR | PUBLISHER_NAME |
+ + + + +
| 1 | DBMS | JAN-2017 | MCGRAW-HILL |
| 2 | ADBMS | JUN-2016 | MCGRAW-HILL |
| 3 | CN | SEP-2016 | PEARSON |
| 4 | CG | SEP-2015 | GRUPO PLANETA |
| 5 | OS | MAY-2016 | PEARSON |
+ + + + +
5 rows in set (0.00 sec)

mysql> SELECT *FROM BOOK_AUTHORS;


+ + +
| BOOK_ID | AUTHOR_NAME |
+ + +
| 1 | NAVATHE |
| 2 | NAVATHE |
| 3 | TANENBAUM |

| 4 | EDWARD ANGEL |
| 5 | GALVIN |
+ + +
5 rows in set (0.00 sec)

mysql> SELECT *FROM LIBRARY_BRANCH;


+ + + +
| BRANCH_ID | BRANCH_NAME | ADDRESS |
+ + + +
| 10 | RR NAGAR | BANGALORE |
| 11 | RNSIT | BANGALORE |
| 12 | RAJAJI NAGAR | BANGALORE |
| 13 | NITTE | MANGALORE |
| 14 | MANIPAL | UDUPI |
+ + + +
5 rows in set (0.00 sec)
mysql> SELECT *FROM BOOK_COPIES;
+ + + +
| BOOK_ID | BRANCH_ID | NO_OF_COPIES |
+ + + +
| 1| 10 | 10 |
| 1| 11 | 5|
| 2| 12 | 2|
| 2| 13 | 5|
| 3| 14 | 7|
| 4| 11 | 3|
| 5| 10 | 1|
+ + + +
7 rows in set (0.00 sec)
mysql> SELECT *FROM CARD;
+ +
| CARD_NO |
+ +
| 100 |
| 101 |
| 102 |
| 103 |
| 104 |
+ +
5 rows in set (0.00 sec)

mysql> SELECT *FROM BOOK_LENDING;


+ + + + + +
| BOOK_ID | BRANCH_ID | CARD_NO | DATE_OUT | DUE_DATE |
+ + + + + +
| 1| 10 | 101 | 2017-01-22 | 2017-06-01 |
| 1| 11 | 104 | 2017-04-12 | 2017-05-12 |
| 2| 13 | 101 | 2017-02-21 | 2017-04-21 |
| 3| 14 | 101 | 2017-01-11 | 2017-03-11 |
| 4| 11 | 101 | 2017-03-15 | 2017-07-11 |
+ + + + + +
5 rows in set (0.00 sec)
-
Queries(output)
1.1.Retrieve details of all books in the library – id, title, name of publisher, authors,
number of copies in each branch, etc.
1- Queries
mysql> SELECT B.BOOK_ID, B.TITLE, B.PUBLISHER_NAME, A.AUTHOR_NAME,
C.NO_OF_COPIES, L.BRANCH_ID
FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH L
WHERE B.BOOK_ID=A.BOOK_ID AND B.BOOK_ID=C.BOOK_ID AND
L.BRANCH_ID=C.BRANCH_ID;
+ + + + + + +
| BOOK_ID | TITLE | PUBLISHER_NAME | AUTHOR_NAME | NO_OF_COPIES |
BRANCH_ID |
+ + + + + + +
| 1 | DBMS | MCGRAW-HILL | NAVATHE | 10 | 10 |
| 1 | DBMS | MCGRAW-HILL | NAVATHE | 5| 11 |
| 2 | ADBMS | MCGRAW-HILL | NAVATHE | 2| 12 |
| 2 | ADBMS | MCGRAW-HILL | NAVATHE | 5| 13 |
| 3 | CN | PEARSON | TANENBAUM | 7| 14 |
| 4 | CG | GRUPO PLANETA | EDWARD ANGEL | 3| 11 |
| 5 | OS | PEARSON | GALVIN | 1| 10 |
+ + + + + + +
7 rows in set (0.00 sec)

2- Queries

mysql> mysql> select CARD_NO from book_lending where DATE_OUT between '17-01-01'
and '1707-01' group by(CARD_NO) having count(CARD_NO)>3 ;
Empty set, 10 warnings (0.03 sec)

3- Queries
mysql> DELETE FROM BOOK WHERE BOOK_ID=3;
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM BOOK;


+ + + + +
| BOOK_ID | TITLE | PUB_YEAR | PUBLISHER_NAME |
+ + + + +
| 1 | DBMS | JAN-2017 | MCGRAW-HILL |
| 2 | ADBMS | JUN-2016 | MCGRAW-HILL |
| 4 | CG | SEP-2015 | GRUPO PLANETA |
| 5 | OS | MAY-2016 | PEARSON |
+ + + + +
4 rows in set (0.00 sec)
4- Queries
mysql> CREATE VIEW YEAR AS SELECT PUB_YEAR FROM BOOK;
Query OK, 0 rows affected (0.05 sec)

mysql> mysql> SELECT * FROM


YEAR;
+ +
| PUB_YEAR |
+ +
| JAN-2017 |
| JUN-2016 |
| SEP-2015 |
| MAY-2016 |
+ +
4 rows in set (0.02 sec)

5- Queries
mysql> CREATE VIEW V_BOOKS AS SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
-> FROM BOOK B, BOOK_COPIES C WHERE B.BOOK_ID=C.BOOK_ID;
Query OK, 0 rows affected (0.04 sec)
mysql> mysql> SELECT * FROM V_BOOKS;
+ + + +
| BOOK_ID | TITLE | NO_OF_COPIES |
+ + + +
| 1 | DBMS | 10 |
| 1 | DBMS | 5|
| 2 | ADBMS | 2|
| 2 | ADBMS | 5|
| 4 | CG | 3|
| 5 | OS | 1|
+ + + +
6 rows in set (0.00 sec)
LAB EXPERIMENTS-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 salesmen who had more than one customer.
3. List all salesmen 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.
TABLE CREATION

CREATE TABLE SALESMAN


(SALESMAN_ID INT,
NAME VARCHAR (20),
CITY VARCHAR (20),
COMMISSION VARCHAR (20),
PRIMARY KEY (SALESMAN_ID));

CREATE TABLE CUSTOMER


(CUSTOMER_ID INT,
CUST_NAME VARCHAR (20),
CITY VARCHAR(20),
GRADE INT (3) NOT NULL CHECK(GRADE <= 10),
SALESMAN_ID INT,
PRIMARY KEY (CUSTOMER_ID),
FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN (SALESMAN_ID) ON DELETE
CASCADE);

CREATE TABLE ORDERS


(ORD_NO INT,
PURCHASE_AMT INT NOT NULL,
ORD_DATE DATE NOT NULL,
SALESMAN_ID INT,
CUSTOMER_ID INT,
PRIMARY KEY (ORD_NO),
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE
CASCADE,
FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN (SALESMAN_ID) ON DELETE
CASCADE);

Description of Tables
DESC SALESMAN;
DESC CUSTOMER;
DESC ORDERS;

INSERT INTO TABLES

INSERT INTO SALESMAN VALUES (1000, 'JOHN','BANGALORE','25 %');


INSERT INTO SALESMAN VALUES (2000, 'RAVI','BANGALORE','20 %');
INSERT INTO SALESMAN VALUES (3000, 'KUMAR','MYSORE','15 %');
INSERT INTO SALESMAN VALUES (4000, 'SMITH','DELHI','30 %');
INSERT INTO SALESMAN VALUES (5000, 'HARSHA','HYDRABAD','15 %');

INSERT INTO CUSTOMER VALUES (10, 'PREETHI','BANGALORE', 8.5, 4000);


INSERT INTO CUSTOMER VALUES (11, 'VIVEK','MANGALORE', 4.5, 1000);
INSERT INTO CUSTOMER VALUES (12, 'BHASKAR','CHENNAI', 7.5, 2000);
INSERT INTO CUSTOMER VALUES (13, 'CHETHAN','BANGALORE', 6, 2000);
INSERT INTO CUSTOMER VALUES (14, 'MAMATHA','BANGALORE', 2.4, 3000);

INSERT INTO ORDERS VALUES (50, 5000, '2017-5-1', 1000, 10);


INSERT INTO ORDERS VALUES (51, 450, '2017-01-27', 2000, 10);
INSERT INTO ORDERS VALUES (52, 1000, '2017-02-24', 2000, 13);
INSERT INTO ORDERS VALUES (53, 3500, '2017-04-23', 3000, 14);
INSERT INTO ORDERS VALUES (54, 550, '2017-03-13', 2000, 12);

Queries
1. Count the customers with grades above Bangalore‟s average.

SELECT COUNT(CUSTOMER_ID) FROM CUSTOMER WHERE GRADE > (SELECT


AVG(GRADE) FROM CUSTOMER WHERE CITY='BANGALORE');

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

SELECT SALESMAN_ID,NAME FROM SALESMAN A WHERE (SELECT COUNT(*)


FROM CUSTOMER C WHERE C.SALESMAN_ID=A.SALESMAN_ID) > 1;

3. List all the salesman and indicate those who have and don‟t have customers in their cities (Use
UNION operation.)
DBMS Lab with Mini Project

SELECT SALESMAN.SALESMAN_ID, NAME,CUST_NAME,COMMISSION FROM


SALESMAN,CUSTOMER WHERE SALESMAN.CITY=CUSTOMER.CITY UNION
SELECT SALESMAN_ID,NAME,'NO MATCH',COMMISSION FROM SALESMAN
WHERE NOT SALESMAN.CITY IN (SELECT CUSTOMER.CITY FROM CUSTOMER)
ORDER by SALESMAN_ID;

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

CREATE VIEW HighestOrder AS SELECT s.SALESMAN_ID, s.NAME, o.ORD_DATE,


o.PURCHASE_AMT FROM SALESMAN s, ORDERS o WHERE (o.SALESMAN_ID =
s.SALESMAN_ID) and o.PURCHASE_AMT = (SELECT MAX(PURCHASE_AMT) FROM
ORDERS);
SELECT * FROM HighestOrder;

5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must
also be deleted.
DELETE FROM SALESMAN WHERE SALESMAN_ID=1000;
SELECT * FROM SALESMAN;
SELECT * FROM ORDERS;

OUTPUT
Q-1
mysql> SELECT COUNT(CUSTOMER_ID) FROM CUSTOMER WHERE GRADE >
(SELECT AVG(GRADE) FROM CUSTOMER WHERE CITY='BANGALORE');
+ +
| COUNT(CUSTOMER_ID) |
+ +
| 3|
+ +
1 row in set (0.04 sec)

Q-2
SELECT SALESMAN_ID,NAME FROM SALESMAN A WHERE (SELECT COUNT(*)
FROM CUSTOMER C WHERE C.SALESMAN_ID=A.SALESMAN_ID) > 1;
+ + +
| SALESMAN_ID | NAME |
+ + +
| 2000 | RAVI |
+ + +
1 row in set (0.03 sec)

Q-3

16
DBMS Lab with Mini Project

SELECT SALESMAN.SALESMAN_ID, NAME,CUST_NAME,COMMISSION FROM

17
DBMS Lab with Mini Project

SALESMAN,CUSTOMER WHERE SALESMAN.CITY=CUSTOMER.CITY UNION SELECT


SALESMAN_ID,NAME,'NO MATCH',COMMISSION FROM SALESMAN WHERE NOT
SALESMAN.CITY IN (SELECT CUSTOMER.CITY FROM CUSTOMER)
-> ORDER by SALESMAN_ID;
+ + + + +
| SALESMAN_ID | NAME | CUST_NAME | COMMISSION |
+ + + + +
| 2000 | RAVI | PREETHI | 20 % |
| 2000 | RAVI | CHETHAN | 20 % |
| 2000 | RAVI | MAMATHA | 20 % |
| 3000 | KUMAR | NO MATCH | 15 % |
| 4000 | SMITH | NO MATCH | 30 % |
| 5000 | HARSHA | NO MATCH | 15 % |
+ + + + +
6 rows in set (0.06 sec)

Q-4
CREATE VIEW HighestOrder2 AS SELECT s.SALESMAN_ID, s.NAME, o.ORD_DATE,
o.PURCHASE_AMT FROM SALESMAN s, ORDERS o WHERE (o.SALESMAN_ID =
s.SALESMAN_ID) and o.PURCHASE_AMT = (SELECT MAX(PURCHASE_AMT) FROM
ORDERS);
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT * FROM HighestOrder2;


+ + + + +
| SALESMAN_ID | NAME | ORD_DATE | PURCHASE_AMT |
+ + + + +
| 3000 | KUMAR | 2017-04-23 | 3500 |
+ + + + +
1 row in set (0.00 sec)
Q-5
DELETE FROM SALESMAN WHERE SALESMAN_ID=1000;
Query OK, 1 row affected (0.16 sec)
mysql>
mysql> SELECT * FROM SALESMAN;
+ + + + +
| SALESMAN_ID | NAME | CITY | COMMISSION |
+ + + + +
| 2000 | RAVI | BANGALORE | 20 % |
| 3000 | KUMAR | MYSORE | 15 % |
| 4000 | SMITH | DELHI | 30 % |
| 5000 | HARSHA | HYDRABAD | 15 % |
+ + + + +
DBMS Lab with Mini Project

4 rows in set (0.00 sec)


DBMS Lab with Mini Project

mysql> SELECT * FROM ORDERS;


+ + + + + +
| ORD_NO | PURCHASE_AMT | ORD_DATE | SALESMAN_ID | CUSTOMER_ID |
+ + + + + +
| 51 | 450 | 2017-01-27 | 2000 | 10 |
| 52 | 1000 | 2017-02-24 | 2000 | 13 |
| 53 | 3500 | 2017-04-23 | 3000 | 14 |
| 54 | 550 | 2017-03-13 | 2000 | 12 |
+ + + + + +
4 rows in set (0.00 sec)
DBMS Lab with Mini Project

EXPERIMENT - 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.
DBMS Lab with Mini Project

CREATE TABLE
CREATE TABLE ACTOR (
ACT_ID INT,
ACT_NAME VARCHAR (20),
ACT_GENDER CHAR (1),
PRIMARY KEY (ACT_ID));

CREATE TABLE DIRECTOR (


DIR_ID INT,
DIR_NAME VARCHAR (20),
DIR_PHONE BIGINT,
PRIMARY KEY (DIR_ID));

CREATE TABLE MOVIES (


MOV_ID INT,
MOV_TITLE VARCHAR (20),
MOV_YEAR INT,
MOV_LANG VARCHAR (12),
DIR_ID INT,
PRIMARY KEY (MOV_ID),
FOREIGN KEY (DIR_ID) REFERENCES DIRECTOR (DIR_ID));
CREATE TABLE MOVIE_CAST (
ACT_ID INT,
DBMS Lab with Mini Project

MOV_ID INT,
DBMS Lab with Mini Project

ROLE VARCHAR (10),


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 INTEGER,
PRIMARY KEY (MOV_ID),
FOREIGN KEY (MOV_ID) REFERENCES MOVIES (MOV_ID));

DESC COMMAND
DESC ACTOR;
DESC DIRECTOR;
DESC MOVIES;
DESC MOVIE_CAST;
DESC RATING;

INSERT VALUES INTO TABLES


INSERT INTO ACTOR VALUES (301,'ANUSHKA','F');
INSERT INTO ACTOR VALUES (302,'PRABHAS','M');
INSERT INTO ACTOR VALUES (303,'PUNITH','M');
INSERT INTO ACTOR VALUES (304,'JERMY','M');

INSERT INTO DIRECTOR VALUES (60,'RAJAMOULI', 8751611001);


INSERT INTO DIRECTOR VALUES (61,'HITCHCOCK', 7766138911);
INSERT INTO DIRECTOR VALUES (62,'FARAN', 9986776531);
INSERT INTO DIRECTOR VALUES (63,'STEVEN SPIELBERG', 8989776530);

INSERT INTO MOVIES VALUES (1001,'BAHUBALI-2', 2017, 'TELUGU', 60);


INSERT INTO MOVIES VALUES (1002,'BAHUBALI-1', 2015, 'TELUGU', 60);
INSERT INTO MOVIES VALUES (1003,'AKASH', 2008, 'KANNADA', 61);
INSERT INTO MOVIES VALUES (1004,'WAR HORSE', 2011, 'ENGLISH', 63);

INSERT INTO MOVIE_CAST VALUES (301, 1002, 'HEROINE');


INSERT INTO MOVIE_CAST VALUES (301, 1001, 'HEROINE');
INSERT INTO MOVIE_CAST VALUES (303, 1003, 'HERO');
DBMS Lab with Mini Project

INSERT INTO MOVIE_CAST VALUES (303, 1002, 'GUEST');


INSERT INTO MOVIE_CAST VALUES (304, 1004, 'HERO');

INSERT INTO RATING VALUES (1001, 4);


INSERT INTO RATING VALUES (1002, 2);
INSERT INTO RATING VALUES (1003, 5);
INSERT INTO RATING VALUES (1004, 4);

QUERIES:
1. List the titles of all movies directed by „Hitchcock‟
SELECT MOV_TITLE FROM MOVIES WHERE DIR_ID IN(SELECT DIR_ID FROM
DIRECTOR WHERE DIR_NAME = 'HITCHCOCK');

2. Find the movie names where one or more actors acted in two or more movies.
SELECT distinct MOV_TITLE FROM MOVIES M, MOVIE_CAST MV WHERE
M.MOV_ID=MV.MOV_ID AND ACT_ID IN(SELECT ACT_ID FROM MOVIE_CAST
GROUP BY ACT_ID HAVING COUNT(ACT_ID) > 1);

3. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN
operation)
SELECT ACT_NAME, MOV_TITLE, MOV_YEAR FROM ACTOR A JOIN MOVIE_CAST
C ON A.ACT_ID=C.ACT_ID JOIN MOVIES M ON C.MOV_ID=M.MOV_ID WHERE
M.MOV_YEAR NOT BETWEEN 2000 AND 2015;
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.
SELECT MOV_TITLE, COUNT(REV_STARS) FROM MOVIES INNER JOIN RATING
USING (MOV_ID) GROUP BY MOV_TITLE HAVING MAX(REV_STARS)>0 ORDER BY
MOV_TITLE;
5. Update rating of all movies directed by „Steven Spielberg‟ to 5.
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 Lab with Mini Project

SELECT * FROM RATING;


DBMS Lab with Mini Project

OUTPUT
Q1.

SELECT MOV_TITLE FROM MOVIES WHERE DIR_ID IN(SELECT DIR_ID FROMDIRECTOR


WHERE DIR_NAME = 'HITCHCOCK');
+ +
| MOV_TITLE |
+ +
| AKASH |
+ +

Q2.
SELECT distinct MOV_TITLE FROM MOVIES M, MOVIE_CAST MV WHERE
M.MOV_ID=MV.MOV_ID AND ACT_ID IN(SELECT ACT_ID FROM MOVIE_CAST GROUP BY
ACT_ID HAVING COUNT(ACT_ID) > 1);
+ +
| MOV_TITLE |
+ +
| BAHUBALI-2 |
| BAHUBALI-1 |
| AKASH |
+ +

Q3.
SELECT ACT_NAME, MOV_TITLE, MOV_YEAR FROM ACTOR A JOIN MOVIE_CAST CON
A.ACT_ID=C.ACT_ID JOIN MOVIES M ON C.MOV_ID=M.MOV_ID WHERE M.MOV_YEAR
NOT BETWEEN 2000 AND 2015;
+ + + +
| ACT_NAME | MOV_TITLE | MOV_YEAR |
+ + + +
| ANUSHKA | BAHUBALI-2 | 2017 |
+ + + +
DBMS Lab with Mini Project

Q4.
SELECT MOV_TITLE, COUNT(REV_STARS) FROM MOVIES INNER JOIN RATING USING
(MOV_ID) GROUP BY MOV_TITLE HAVING MAX(REV_STARS)>0 ORDER BY MOV_TITLE;
+ + +
| MOV_TITLE | COUNT(REV_STARS) |
+ + +
| AKASH | 1|
| BAHUBALI-1 | 1|
| BAHUBALI-2 | 1|
| WAR HORSE | 1|
+ + +

Q5. 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'));SELECT * FROM RATING;
+ + +
| MOV_ID | REV_STARS |
+ + +
| 1001 | 4|
| 1002 | 2|
| 1003 | 5|
| 1004 | 5|
+ + +
DBMS Lab with Mini Project

EXPERIMENT – 4
Consider the schema for College Database:
STUDENT (USN, SName, Address, Phone, Gender)
SEMSEC (SSID, Sem, Sec)
CLASS (USN, SSID)
COURSE (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 Courses.
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.
DBMS Lab with Mini Project

TABLE CREATION
CREATE TABLE STUDENT (
USN VARCHAR (10) PRIMARY KEY,
SNAME VARCHAR (20),
ADDRESS VARCHAR (20),
PHONE BIGINT,
GENDER CHAR (1));

CREATE TABLE SEMSEC (


SSID VARCHAR (5) PRIMARY KEY,
SEM INT,
SEC CHAR (1));

CREATE TABLE CLASS (


USN VARCHAR (10),
SSID VARCHAR (5),
PRIMARY KEY (USN, SSID),
FOREIGN KEY (USN) REFERENCES STUDENT (USN),
FOREIGN KEY (SSID) REFERENCES SEMSEC (SSID));

CREATE TABLE SUBJECT (


DBMS Lab with Mini Project

SUBCODE VARCHAR (8) PRIMARY KEY,


DBMS Lab with Mini Project

TITLE VARCHAR (20),


SEM INT,
CREDITS INT);

CREATE TABLE IAMARKS (


USN VARCHAR (10),
SUBCODE VARCHAR (8),
SSID VARCHAR (5),
TEST1 INT,
TEST2 INT,
TEST3 INT,
FINALIA INT,
PRIMARY KEY (USN, SUBCODE, SSID),
FOREIGN KEY (USN) REFERENCES STUDENT (USN),
FOREIGN KEY (SUBCODE) REFERENCES SUBJECT (SUBCODE), FOREIGN
KEY (SSID) REFERENCES SEMSEC (SSID));

DESC COMMANDS DESC


STUDENT;
DESC SEMSEC;
DESC CLASS;
DESC SUBJECT;
DESC IAMARKS;

INSERTION OF VALUES TO TABLES

INSERT INTO STUDENT VALUES (2AG13CS020','AKSHAY','BELAGAVI', 8877881122,'M');


INSERT INTO STUDENT VALUES (2AG13CS062','SANDHYA','BENGALURU',
7722829912,'F');
INSERT INTO STUDENT VALUES (2AG13CS091','TEESHA','BENGALURU', 7712312312,'F');
INSERT INTO STUDENT VALUES (2AG13CS066','SUPRIYA','MANGALURU', 8877881122,'F');
INSERT INTO STUDENT VALUES (2AG14CS010','ABHAY','BENGALURU', 9900211201,'M');
INSERT INTO STUDENT VALUES (2AG14CS032','BHASKAR','BENGALURU',
9923211099,'M');
INSERT INTO STUDENT VALUES (2AG14CS025','ASMI','BENGALURU', 7894737377,'F');
INSERT INTO STUDENT VALUES (2AG15CS011','AJAY','TUMKUR', 9845091341,'M');
INSERT INTO STUDENT VALUES (2AG15CS029','CHITRA','DAVANGERE', 7696772121,'F');
INSERT INTO STUDENT VALUES (2AG15CS045','JEEVA','BELLARY', 9944850121,'M');
INSERT INTO STUDENT VALUES (2AG15CS091','SANTOSH','MANGALURU',
8812332201,'M');
INSERT INTO STUDENT VALUES (2AG16CS045','ISMAIL','KALBURGI', 9900232201,'M');
INSERT INTO STUDENT VALUES (2AG16CS088','SAMEERA','SHIMOGA', 9905542212,'F');
INSERT INTO STUDENT VALUES
DBMS Lab with Mini Project

(2AG16CS122','VINAYAKA','CHIKAMAGALUR',8800880011,'M');
DBMS Lab with Mini Project

INSERT INTO SEMSEC VALUES ('CSE8A', 8,'A');


INSERT INTO SEMSEC VALUES ('CSE8B', 8,'B');
INSERT INTO SEMSEC VALUES ('CSE8C', 8,'C');
INSERT INTO SEMSEC VALUES ('CSE7A', 7,'A');
INSERT INTO SEMSEC VALUES ('CSE7B', 7,'B');
INSERT INTO SEMSEC VALUES ('CSE7C', 7,'C');
INSERT INTO SEMSEC VALUES ('CSE6A', 6,'A');
INSERT INTO SEMSEC VALUES ('CSE6B', 6,'B');
INSERT INTO SEMSEC VALUES ('CSE6C', 6,'C');
INSERT INTO SEMSEC VALUES ('CSE5A', 5,'A');
INSERT INTO SEMSEC VALUES ('CSE5B', 5,'B');
INSERT INTO SEMSEC VALUES ('CSE5C', 5,'C');
INSERT INTO SEMSEC VALUES ('CSE4A', 4,'A');
INSERT INTO SEMSEC VALUES ('CSE4B', 4,'B');
INSERT INTO SEMSEC VALUES ('CSE4C', 4,'C');
INSERT INTO SEMSEC VALUES ('CSE3A', 3,'A');
INSERT INTO SEMSEC VALUES ('CSE3B', 3,'B');
INSERT INTO SEMSEC VALUES ('CSE3C', 3,'C');
INSERT INTO SEMSEC VALUES ('CSE2A', 2,'A');
INSERT INTO SEMSEC VALUES ('CSE2B', 2,'B');
INSERT INTO SEMSEC VALUES ('CSE2C', 2,'C');
INSERT INTO SEMSEC VALUES ('CSE1A', 1,'A');
INSERT INTO SEMSEC VALUES ('CSE1B', 1,'B');
INSERT INTO SEMSEC VALUES ('CSE1C', 1,'C');

INSERT INTO CLASS VALUES ('2AG13CS020','CSE8A');


INSERT INTO CLASS VALUES ('2AG13CS062','CSE8A');
INSERT INTO CLASS VALUES ('2AG13CS066','CSE8B');
INSERT INTO CLASS VALUES ('2AG13CS091','CSE8C');
INSERT INTO CLASS VALUES ('2AG14CS010','CSE7A');
INSERT INTO CLASS VALUES ('2AG14CS025','CSE7A');
INSERT INTO CLASS VALUES ('2AG14CS032','CSE7A');
INSERT INTO CLASS VALUES ('2AG15CS011','CSE4A');
INSERT INTO CLASS VALUES ('2AG15CS029','CSE4A');
INSERT INTO CLASS VALUES ('2AG15CS045','CSE4B');
INSERT INTO CLASS VALUES ('2AG15CS091','CSE4C');
INSERT INTO CLASS VALUES ('2AG16CS045','CSE3A');
INSERT INTO CLASS VALUES ('2AG16CS088','CSE3B');
INSERT INTO CLASS VALUES ('2AG16CS122','CSE3C');

INSERT INTO SUBJECT VALUES ('10CS81','ACA', 8, 4);


INSERT INTO SUBJECT VALUES ('10CS82','SSM', 8, 4);
DBMS Lab with Mini Project

INSERT INTO SUBJECT VALUES ('10CS83','NM', 8, 4);


INSERT INTO SUBJECT VALUES ('10CS84','CC', 8, 4);
DBMS Lab with Mini Project

INSERT INTO SUBJECT VALUES ('10CS85','PW', 8, 4);


INSERT INTO SUBJECT VALUES ('10CS71','OOAD', 7, 4);
INSERT INTO SUBJECT VALUES ('10CS72','ECS', 7, 4);
INSERT INTO SUBJECT VALUES ('10CS73','PTW', 7, 4);
INSERT INTO SUBJECT VALUES ('10CS74','DWDM', 7, 4);
INSERT INTO SUBJECT VALUES ('10CS75','JAVA', 7, 4);
INSERT INTO SUBJECT VALUES ('10CS76','SAN', 7, 4);
INSERT INTO SUBJECT VALUES ('15CS51', 'ME', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS52','CN', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS53','DBMS', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS54','ATC', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS55','JAVA', 5, 3);
INSERT INTO SUBJECT VALUES ('15CS56','AI', 5, 3);
INSERT INTO SUBJECT VALUES ('15CS41','M4', 4, 4);
INSERT INTO SUBJECT VALUES ('15CS42','SE', 4, 4);
INSERT INTO SUBJECT VALUES ('15CS43','DAA', 4, 4);
INSERT INTO SUBJECT VALUES ('15CS44','MPMC', 4, 4);
INSERT INTO SUBJECT VALUES ('15CS45','OOC', 4, 3);
INSERT INTO SUBJECT VALUES ('15CS46','DC', 4, 3); INSERT INTO SUBJECT VALUES
('15CS31','M3', 3, 4);
INSERT INTO SUBJECT VALUES ('15CS32','ADE', 3, 4);
INSERT INTO SUBJECT VALUES ('15CS33','DSA', 3, 4);
INSERT INTO SUBJECT VALUES ('15CS34','CO', 3, 4);
INSERT INTO SUBJECT VALUES ('15CS35','USP', 3, 3);
INSERT INTO SUBJECT VALUES ('15CS36','DMS', 3, 3);

INSERT INTO IAMARKS VALUES ('2AG13CS091','10CS81','CSE8C', 15, 16, 0, 0);


INSERT INTO IAMARKS VALUES ('2AG13CS091','10CS82','CSE8C', 12, 19, 14, 0);
INSERT INTO IAMARKS VALUES ('2AG13CS091','10CS83','CSE8C', 19, 15, 20, 0);
INSERT INTO IAMARKS VALUES ('2AG13CS091','10CS84','CSE8C', 20, 16, 19, 0);
INSERT INTO IAMARKS VALUES ('2AG13CS091','10CS85','CSE8C', 15, 15, 12, 0);

QUERIES
Q1-List all the student details studying in fourth semester „C‟ section.
SELECT S.*, SS.SEM, SS.SEC FROM STUDENT S, SEMSEC SS, CLASS C WHERE S.USN
= C.USN AND SS.SSID = C.SSID AND SS.SEM = 4 AND SS.SEc='C';

Q2-Compute the total number of male and female students in each semester and in each section.
SELECT SS.SEM, SS.SEC, S.GENDER, COUNT(S.GENDER) AS COUNT FROM STUDENT S,
SEMSEC SS, CLASS C WHERE S.USN = C.USN AND SS.SSID = C.SSID GROUP BY SS.SEM,
SS.SEC, S.GENDER ORDER BY SEM;
DBMS Lab with Mini Project

Q3- Create a view of Test1 marks of student USN „1BI15CS101‟ in all Courses.
DBMS Lab with Mini Project

CREATE VIEW STU_TEST1_MARKS_VIEW AS SELECT TEST1, SUBCODE FROM


IAMARKS WHERE USN = '2AG13CS091';

SELECT * from STU_TEST1_MARKS_VIEW;


Q4- Calculate the FinalIA (average of best two test marks) and update the corresponding table for all
students
DELIMITER #
CREATE PROCEDURE CALFA2()
BEGIN
UPDATE IAMARKS
SET FINALIA = (CASE WHEN GREATEST(TEST1,TEST2)!=GREATEST(TEST1,TEST3)
THEN GREATEST(TEST1,TEST2)+GREATEST(TEST1,TEST3)
ELSE
GREATEST(TEST1,TEST2)+GREATEST(TEST2,TEST3)
END)/2
WHERE FINALIA IS NOT NULL;
END#

TO RUN
MYSQL> DELIMITER ;
MYSQL> CALL CALFA1();

Q5- 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.
SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER,
(CASE
WHEN IA.FINALIA BETWEEN 17 AND 20 THEN 'OUTSTANDING'
WHEN IA.FINALIA BETWEEN 12 AND 16 THEN 'AVERAGE'
ELSE 'WEAK'
DBMS Lab with Mini Project

END) AS CAT
DBMS Lab with Mini Project

FROM STUDENT S, SEMSEC SS, IAMARKS IA, SUBJECT SUB


WHERE S.USN = IA.USN AND
SS.SSID = IA.SSID AND
SUB.SUBCODE = IA.SUBCODE AND
SUB.SEM = 8;
OUTPUT
Q1.
SELECT S.*, SS.SEM, SS.SEC FROM STUDENT S, SEMSEC SS, CLASS C WHERE S.USN
= C.USN AND SS.SSID = C.SSID AND SS.SEM = 4 AND SS.SEc='C';
+ + + + + + + +
| USN | SNAME | ADDRESS | PHONE | GENDER | SEM | SEC |
+ + + + + + + +
| 1RN15CS091 | SANTOSH | MANGALURU | 8812332201 | M | 4 |C |
+ + + + + + + +

Q2.
SELECT SS.SEM, SS.SEC, S.GENDER, COUNT(S.GENDER) AS COUNT FROM
STUDENT S, SEMSEC SS, CLASS C WHERE S.USN = C.USN AND SS.SSID = C.SSID
GROUP BY SS.SEM, SS.SEC, S.GENDER ORDER BY SEM;
+ + + + +
| SEM | SEC | GENDER | COUNT |
+ + + + +
| 3 |A |M | 1|
| 3|B |F | 1|
| 3 |C |M | 1|
| 4 |A |F | 1|
| 4 |A |M | 1|
| 4|B |M | 1|
| 4 |C |M | 1|
| 7 |A |F | 1|
| 7 |A |M | 2|
DBMS Lab with Mini Project

| 8|A |F | 1|
| 8|A |M | 1|
DBMS Lab with Mini Project

| 8|B |F | 1|
| 8 |C |F | 1|
+ + + + +
Q3.
mysql> CREATE VIEW STU_TEST1_MARKS_VIEW AS SELECT TEST1, SUBCODE
FROM IAMARKS WHERE USN = '1RN13CS091';
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * from STU_TEST1_MARKS_VIEW;


+ + +
| TEST1 | SUBCODE |
+ + +
| 15 | 10CS81 |
| 12 | 10CS82 |
| 19 | 10CS83 |
| 20 | 10CS84 |
| 15 | 10CS85 |
+ + +

Q4.
delimiter # create
procedure calfa1() begin
update IAMARKS
SET FINALIA = (CASE WHEN GREATEST(TEST1,TEST2)!=GREATEST(TEST1,TEST3)
THEN GREATEST(TEST1,TEST2)+GREATEST(TEST1,TEST3)
ELSE
GREATEST(TEST1,TEST2)+GREATEST(TEST2,TEST3)
END)/2
WHERE FINALIA IS NOT NULL;
END#
DBMS Lab with Mini Project

TO RUN
mysql> DELIMITER ;
mysql> CALL CALFA1();
Query OK, 5 rows affected (0.13 sec)

mysql> SELECT * FROM IAMARKS;


+ + + + + + + +
| USN | SUBCODE | SSID | TEST1 | TEST2 | TEST3 | FINALIA |
+ + + + + + + +
| 1RN13CS091 | 10CS81 | CSE8C | 15 | 16 | 0| 16 |
| 1RN13CS091 | 10CS82 | CSE8C | 12 | 19 | 14 | 17 |
| 1RN13CS091 | 10CS83 | CSE8C | 19 | 15 | 20 | 20 |
| 1RN13CS091 | 10CS84 | CSE8C | 20 | 16 | 19 | 20 |
| 1RN13CS091 | 10CS85 | CSE8C | 15 | 15 | 12 | 15 |
+ + + + + + + +
5 rows in set (0.00 sec)

Q5.
SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER,
-> (CASE
-> WHEN IA.FINALIA BETWEEN 17 AND 20 THEN 'OUTSTANDING'
-> WHEN IA.FINALIA BETWEEN 12 AND 16 THEN 'AVERAGE'
-> ELSE 'WEAK'
-> END) AS CAT
-> FROM STUDENT S, SEMSEC SS, IAMARKS IA, SUBJECT SUB
-> WHERE S.USN = IA.USN AND
-> SS.SSID = IA.SSID AND
-> SUB.SUBCODE = IA.SUBCODE AND
-> SUB.SEM = 8;
DBMS Lab with Mini Project

+ + + + + + +
| USN | SNAME | ADDRESS | PHONE | GENDER | CAT |
+ + + + + + +
| 1RN13CS091 | TEESHA | BENGALURU | 7712312312 | F | WEAK |
| 1RN13CS091 | TEESHA | BENGALURU | 7712312312 | F | WEAK |
| 1RN13CS091 | TEESHA | BENGALURU | 7712312312 | F | WEAK |
| 1RN13CS091 | TEESHA | BENGALURU | 7712312312 | F | WEAK |
| 1RN13CS091 | TEESHA | BENGALURU | 7712312312 | F | WEAK |
+ + + + + + +
DBMS Lab with Mini Project

EXPERIMENT – 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 controlledby 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.
DBMS Lab with Mini Project

TABLE CREATION

CREATE TABLE DEPARTMENT


(DNO VARCHAR(20) PRIMARY KEY,
DNAME VARCHAR(20),
MGRSTARTDATE DATE);

CREATE TABLE EMPLOYEE


(SSN VARCHAR(20) PRIMARY KEY,
FNAME VARCHAR(20),
LNAME VARCHAR(20),
ADDRESS VARCHAR(20),
SEX CHAR (1), SALARY INT,
SUPERSSN REFERENCES EMPLOYEE (SSN), DNO REFERENCES DEPARTMENT (DNO));

NOTE: Once DEPARTMENT and EMPLOYEE tables are created we must alter department table to
add foreign constraint MGRSSN using sql command

ALTER TABLE DEPARTMENT


ADD MGRSSN VARCHAR(10) REFERENCES EMPLOYEE (SSN);
DBMS Lab with Mini Project

CREATE TABLE DLOCATION (DLOC VARCHAR2 (20),


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

CREATE TABLE PROJECT


(PNO INTEGER PRIMARY KEY,
PNAME VARCHAR(20),
PLOCATION VARCHAR(20),
DNO REFERENCES DEPARTMENT (DNO));

CREATE TABLE WORKS_ON


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

INSERT OF VALUES TO TABLE

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSCSE06‘,‘NEHA‘,‘SN‘,‘BANGALORE‘,‘F‘, 800000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSACC01‘,‘AHANA‘,‘K‘,‘MANGALORE‘,‘F‘, 350000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSACC02‘,‘SANTHOSH‘,‘KUMAR‘,‘MANGALORE‘,‘M‘, 300000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSISE01‘,‘VEENA‘,‘M‘,‘MYSORE‘,‘M‘, 600000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSIT01‘,‘NAGESH‘,‘HR‘,‘BANGALORE‘,‘M‘, 500000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSCSE06‘,‘NEHA‘,‘SN‘,‘BANGALORE‘,‘F‘, 800000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSACC01‘,‘AHANA‘,‘K‘,‘MANGALORE‘,‘F‘, 350000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSACC02‘,‘SANTHOSH‘,‘KUMAR‘,‘MANGALORE‘,‘M‘, 300000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSISE01‘,‘VEENA‘,‘M‘,‘MYSORE‘,‘M‘, 600000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSIT01‘,‘NAGESH‘,‘HR‘,‘BANGALORE‘,‘M‘, 500000);

INSERT INTO DEPARTMENT VALUES (‗1‘,‘ACCOUNTS‘,‘01-JAN-01‘,‘RNSACC02‘);


INSERT INTO DEPARTMENT VALUES (‗2‘,‘IT‘,‘01-AUG-16‘,‘RNSIT01‘);
DBMS Lab with Mini Project

INSERT INTO DEPARTMENT VALUES (‗3‘,‘ECE‘,‘01-JUN-08‘,‘RNSECE01‘);


INSERT INTO DEPARTMENT VALUES (‗4‘,‘ISE‘,‘01-AUG-15‘,‘RNSISE01‘); INSERT
INTO DEPARTMENT VALUES (‗5‘,‘CSE‘,‘01-JUN-02‘,‘RNSCSE05‘);
DBMS Lab with Mini Project

UPDATE COMMAND

UPDATE EMPLOYEE SET


SUPERSSN='RNSCSE02',DNO=5
WHERE SSN='RNSCSE01';

UPDATE EMPLOYEE SET


SUPERSSN='RNSCSE03',DNO='5'
WHERE SSN='RNSCSE02';

UPDATE EMPLOYEE SET


SUPERSSN='RNSCSE04', DNO='5'
WHERE SSN='RNSCSE03';

UPDATE EMPLOYEE SET


DNO='5', SUPERSSN='RNSCSE05'
WHERE SSN='RNSCSE04';

UPDATE EMPLOYEE SET


DNO='5', SUPERSSN='RNSCSE06'
WHERE SSN='RNSCSE05';

UPDATE EMPLOYEE SET


DNO='5', SUPERSSN=NULL
WHERE SSN='RNSCSE06';

UPDATE EMPLOYEE SET


DNO='1', SUPERSSN='RNSACC02'
WHERE SSN='RNSACC01';

UPDATE EMPLOYEE SET


DNO='1', SUPERSSN=NULL
WHERE SSN='RNSACC02';

UPDATE EMPLOYEE SET


DNO='4', SUPERSSN=NULL
WHERE SSN='RNSISE01';

UPDATE EMPLOYEE SET


DNO='2', SUPERSSN=NULL
WHERE SSN='RNSIT01';
DBMS Lab with Mini Project 18CSL58

INSERT INTO DLOCATION VALUES ('50',


'BANGALORE');
DBMS Lab with Mini Project

INSERT INTO DLOCATION VALUES ('2', 'BANGALORE');


INSERT INTO DLOCATION VALUES ('3', 'BANGALORE');
INSERT INTO DLOCATION VALUES ('4', 'MANGALORE');
INSERT INTO DLOCATION VALUES ('5', 'MANGALORE');

INSERT INTO PROJECT VALUES (100,'5', 'IOT','BANGALORE');


INSERT INTO PROJECT VALUES (101,'5','CLOUD','BANGALORE');
INSERT INTO PROJECT VALUES (102,'5', 'BIGDATA','BANGALORE');
INSERT INTO PROJECT VALUES (103,'3','SENSORS','BANGALORE');
INSERT INTO PROJECT VALUES (104,'1','BANK MANAGEMENT','BANGALORE');
INSERT INTO PROJECT VALUES (105,'1','SALARY MANAGEMENT','BANGALORE');
INSERT INTO PROJECT VALUES (106,'4','OPENSTACK','BANGALORE');
INSERT INTO PROJECT VALUES (107,'2','SMART CITY','BANGALORE');

INSERT INTO WORKS_ON VALUES (4, 'RNSCSE01', 100);


INSERT INTO WORKS_ON VALUES (6, 'RNSCSE01', 101);
INSERT INTO WORKS_ON VALUES (8, 'RNSCSE01', 102);
INSERT INTO WORKS_ON VALUES (10, 'RNSCSE02', 100);
INSERT INTO WORKS_ON VALUES (3, 'RNSCSE04', 100);
INSERT INTO WORKS_ON VALUES (4, 'RNSCSE05', 101);
INSERT INTO WORKS_ON VALUES (5, 'RNSCSE06', 102);
INSERT INTO WORKS_ON VALUES (6, 'RNSCSE03', 102);
INSERT INTO WORKS_ON VALUES (7, 'RNSECE01', 103);
INSERT INTO WORKS_ON VALUES (5, 'RNSACC01', 104);
INSERT INTO WORKS_ON VALUES (6, 'RNSACC02', 105);
INSERT INTO WORKS_ON VALUES (4, 'RNSISE01', 106);
INSERT INTO WORKS_ON VALUES (10, 'RNSIT01', 107);

QUERIES:

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.
SELECT DISTINCT P.PNO
FROM PROJECT P,DEPARTMENT D,EMPLOYEE E
WHERE P.DNO=D.DNO AND
D.MGRSSN=E.SSN AND
E.LNAME='SCOTT'
UNION
SELECT DISTINCT W.PNO
FROM WORKS_ON W,EMPLOYEE E1
WHERE E1.SSN=W.SSN AND
E1.LNAME='SCOTT';
DBMS Lab with Mini Project

2. Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent raise.

SELECT E.FNAME,E.LNAME, 1.1*E.SALARY AS INCR_SAL


FROM EMPLOYEE E,WORKS_ON W, PROJECT P
WHERE E.SSN=W.SSN AND
W.PNO=P.PNO AND
P.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(E.SALARY), MAX(E.SALARY),


MIN(E.SALARY),AVG(E.SALARY)
FROM EMPLOYEE E,DEPARTMENT D
WHERE E.DNO=D.DNO AND
D.DNAME='ACCOUNTS';

4. Retrieve the name of each employee who works on all the projects Controlled by department
number 5 (use NOT EXISTS operator).

SELECT E.FNAME,E.LNAME
FROM EMPLOYEE E
WHERE NOT EXISTS(SELECT PNO FROM PROJECT WHERE DNO=5
AND PNO NOT IN (SELECT PNO FROM WORKS_ON 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.

SELECT E.DNO,COUNT(E.SSN) as "No. oF Employees"


FROM EMPLOYEE E
WHERE E.SALARY >600000 AND
E.DNO IN (SELECT E1.DNO
FROM EMPLOYEE E1
GROUP BY E1.DNO
HAVING COUNT(E1.DNO) >5 )
GROUP BY E.DNO;
DBMS Lab with Mini Project

OUTPUT

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. + ---- +
| PNO |
+ +
| 103 |
| 102 |
+-----+
2.Show the resulting salaries if every employee working on the „IoT‟ project is given a 10
percent raise
+ + + +
| FNAME | LNAME | INCR_SAL |
+ + + +
| JAMES | SMITH | 550000.0 |
| HEARN | BAKER | 770000.0 |
| PAVAN | HEGDE | 715000.0 |
+ + + +

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
+ + + + +
| SUM(E.SALARY) | MAX(E.SALARY) | MIN(E.SALARY) | AVG(E.SALARY) |
+ + + + +
| 650000 | 350000 | 300000 | 325000.0000 | +---------------+---------------+------------
+ +

4. Retrieve the name of each employee who works on all the projects controlledby
department number 5 (use NOT EXISTS operator).
+ + +
| FNAME | LNAME |
+ + +
| JAMES | SMITH |
+ + +

For each department that has more than five employees, retrieve the department numberand the
number of its employees who are making more than Rs. 6,00,000.
+ + +
| DNO | No. oF Employees |
+ + +
|5 | 3|
+ + +

You might also like