UNIVERSITY OF VISVESVARAYA COLLEGE OF ENGINEERING

K.R. Circle - 560001

DATABASE MANAGEMENT SYSTEM LABORATORY

Index:

1.​ Library Database

2.​ Sales Order Database

3.​ Movie Database

4.​ College Database

5.​ Company Database

By:

Anish Sawhney- U25UV22T029005

5th Sem CSE(B1)

Signature:

Ms. Aarthi A​ Dr. H N Champa

A.​ 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
Jan2017 to Jun2017

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 simplequery.

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

ER Diagram:
Schema Diagram:

CREATE DATABASE
PR1; USE PR1;

CREATE TABLE PUBLISHER (

 NAME VARCHAR(20) PRIMARY KEY,
PHONE INT,
ADDRESS VARCHAR(20)
);

CREATE TABLE BOOK (

BOOK_ID INT 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 (

AUTHOR_NAME VARCHAR(20),
BOOK_ID INT,
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(50),
ADDRESS VARCHAR(50)
);

CREATE TABLE BOOK_COPIES (

NO_OF_COPIES INT,
BOOK_ID INT,
BRANCH_ID 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 (

DATE_OUT DATE,
DUE_DATE DATE,
BOOK_ID INT,
BRANCH_ID INT,
CARD_NO INT,
 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,
FOREIGN KEY (CARD_NO) REFERENCES CARD(CARD_NO) ON DELETE CASCADE
);
ALTER TABLE PUBLISHER MODIFY PHONE BIGINT;

INSERT INTO PUBLISHER VALUES

('MCGRAW-HILL', 9989076587,
'BANGALORE'), ('PEARSON', 9889076565,
'NEW DELHI'),
('RANDOM HOUSE', 7455679345, 'HYDERABAD'),
('HACHETTE LIVRE', 8970862340, 'CHENNAI'),
('GRUPO PLANETA', 7756120238, 'BANGALORE');

select * from publisher;

INSERT INTO BOOK VALUES

(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');

INSERT INTO BOOK_AUTHORS VALUES

('NAVATHE', 1),
('NAVATHE', 2),
('TANENBAUM', 3),
('EDWARD ANGEL', 4),
('GALVIN', 5);

INSERT INTO LIBRARY_BRANCH

VALUES (10, 'RR NAGAR', 'BANGALORE'),
(11, 'RNSIT', 'BANGALORE'),
(12, 'RAJAJI NAGAR',
'BANGALORE'), (13, 'NITTE',
'MANGALORE'),
(14, 'MANIPAL', 'UDUPI');

INSERT INTO BOOK_COPIES VALUES

(10, 1, 10),
(5, 1, 11),
(2, 2, 12),
(5, 2, 13),
(7, 3, 14),
(1, 5, 10),
(3, 4, 11);
INSERT INTO CARD VALUES
(100),
(101),
(102),
(103),
(104);

INSERT INTO BOOK_LENDING

VALUES ('2017-01-01', '2017-06-01', 1,
10, 101),
('2017-01-11', '2017-03-11', 3, 14, 101),
('2017-02-21', '2017-04-21', 2, 13, 101),
('2017-03-15', '2017-07-15', 4, 11, 101),
('2017-04-12', '2017-05-12', 1, 11, 104);

#query 1
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;

#query 2
SELECT CARD_NO
FROM
BOOK_LENDING
WHERE DATE_OUT BETWEEN '2017-01-01' AND '2017-07-01'
GROUP BY CARD_NO
HAVING COUNT(*) > 3;

#query 3
DELETE FROM BOOK
WHERE BOOK_ID=3;

select * from book;

#query 4
CREATE VIEW V_PUBLICATION AS SELECT
PUB_YEAR
FROM BOOK;
select * from V_PUBLICATION;

#query 5
CREATE VIEW V_BOOKS AS
SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
FROM BOOK B, BOOK_COPIES C, LIBRARY_BRANCH
L WHERE B.BOOK_ID=C.BOOK_ID
AND C.BRANCH_ID=L.BRANCH_ID;
Select * from v_books;
B.​ 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 UNIONoperation.)

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.

ER Diagram:

Schema Diagram:
CREATE DATABASE sales;
USE sales;

CREATE TABLE SALESMAN (

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

CREATE TABLE CUSTOMER1 (

CUSTOMER_ID INT PRIMARY
KEY, CUST_NAME VARCHAR(20),
CITY VARCHAR(20),
GRADE INT,
SALESMAN_ID INT,
FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID) ON
DELETE SET NULL
);

CREATE TABLE ORDERS (

ORD_NO INT PRIMARY
KEY,
PURCHASE_AMT DECIMAL(10, 2),
ORD_DATE DATE,
CUSTOMER_ID
INT, SALESMAN_ID
INT,
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER1(CUSTOMER_ID) ON
DELETE CASCADE,
FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID) ON
DELETE CASCADE
);

DESC SALESMAN;
DESC

CUSTOMER1;

DESC ORDERS;

INSERT INTO SALESMAN (SALESMAN_ID, NAME, CITY, COMMISSION)

VALUES (1000, 'JOHN', 'BANGALORE', '25%'),
(2000, 'RAVI', 'BANGALORE', '20%'),
(3000, 'KUMAR', 'MYSORE', '15%'),
(4000, 'SMITH', 'DELHI', '30%'),
(5000, 'HARSHA', 'HYDERABAD', '15%');

INSERT INTO CUSTOMER1 (CUSTOMER_ID, CUST_NAME, CITY, GRADE,

SALESMAN_ID)
VALUES (10, 'PREETHI', 'BANGALORE', 100, 1000),
(11, 'VIVEK', 'MANGALORE', 300, 1000),
(12, 'BHASKAR', 'CHENNAI', 400, 2000),
(13, 'CHETHAN', 'BANGALORE', 200, 2000),
(14, 'MAMATHA', 'BANGALORE', 400, 3000);

INSERT INTO ORDERS (ORD_NO, PURCHASE_AMT, ORD_DATE, CUSTOMER_ID,

SALESMAN_ID)

VALUES (50, 5000, '2017-05-04', 10, 1000),

(51, 450, '2017-01-20', 10, 2000),
(52, 1000, '2017-02-24', 13, 2000),
(53, 3500, '2017-04-13', 14, 3000),
(54, 550, '2017-03-09', 12, 2000);

SELECT * FROM SALESMAN;

SELECT * FROM ORDERS;

SELECT * FROM CUSTOMER1;

SHOW CREATE TABLE CUSTOMER1;

ALTER TABLE CUSTOMER1 MODIFY SALESMAN_ID INT NULL;

#query 1
SELECT GRADE, COUNT(DISTINCT CUSTOMER_ID)
FROM CUSTOMER1
GROUP BY GRADE
HAVING GRADE > (
SELECT AVG(GRADE)
FROM CUSTOMER1
WHERE CITY = 'BANGALORE'
);

#query 2
SELECT SALESMAN_ID, NAME FROM
SALESMAN A
WHERE 1 < (SELECT COUNT(*)
FROM CUSTOMER1
WHERE SALESMAN_ID=A.SALESMAN_ID);
#query 3
SELECT SALESMAN.SALESMAN_ID, NAME, CUST_NAME, COMMISSION
FROM SALESMAN, CUSTOMER1
WHERE SALESMAN.CITY = CUSTOMER1.CITY
UNION
SELECT SALESMAN_ID, NAME, 'NO MATCH', COMMISSION FROM
SALESMAN
WHERE NOT CITY = ANY
(SELECT CITY
FROM
CUSTOMER1)

ORDER BY 2 DESC;

#query 4
CREATE VIEW ELITSALESMAN AS
SELECT B.ORD_DATE, A.SALESMAN_ID, A.NAME FROM SALESMAN A, ORDERS B
WHERE A.SALESMAN_ID = B.SALESMAN_ID
AND B.PURCHASE_AMT=(SELECT MAX(PURCHASE_AMT)
FROM ORDERS C
WHERE C.ORD_DATE = B.ORD_DATE);
SHOW tables;

select * FROM ELITSALESMAN;

#query 5
DELETE FROM ORDERS WHERE SALESMAN_ID = 1000;
SELECT * FROM ORDERS;
C.​ 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 JOINoperation).

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.

ER Diagram:
Schema Diagram:

CREATE DATABASE
PR3; USE PR3;

CREATE TABLE ACTOR (

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

DESC ACTOR;

DROP TABLE DIRECTOR;

CREATE TABLE DIRECTOR
( DIR_ID INT,
DIR_NAME VARCHAR (20),
DIR_PHONE VARCHAR
(20), PRIMARY KEY
(DIR_ID));

ALTER TABLE DIRECTOR

MODIFY DIR_PHONE VARCHAR(15);

CREATE TABLE MOVIES (

MOV_ID INT,
MOV_TITLE VARCHAR
(25), 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,
MOV_ID INT,
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 VARCHAR (25),
PRIMARY KEY (MOV_ID),
FOREIGN KEY (MOV_ID) REFERENCES MOVIES (MOV_ID));

DESC MOVIES;

INSERT INTO ACTOR (ACT_ID, ACT_NAME,ACT_GENDER) VALUES

(301, 'ANUSHKA', 'F'),
(302, 'PRABHAS', 'M'),
(303, 'PUNITH', 'M'),
(304, 'JERMY', 'M');

SELECT * FROM ACTOR;

INSERT INTO DIRECTOR (DIR_ID, DIR_NAME, DIR_PHONE) VALUES

(60 , 'RAJAMOULI', 8751611001),
(61, 'HITCHCOCK', 7766138911),
(62, 'FARAN', 9986776531),
(63, 'STEVEN SPIELBERG', 8989776530);
SELECT * FROM DIRECTOR;

INSERT INTO RATING (MOV_ID, REV_STARS)

VALUES (1001, '4'),
(1002, '2'),
(1003, '5'),
(1004, '4');

INSERT INTO MOVIES (MOV_ID, MOV_TITLE, MOV_YEAR, MOV_LANG, DIR_ID)

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

select * from movies;

INSERT INTO MOVIE_CAST VALUES

(301, 1002, 'HEROINE'),
(301, 1001, 'HEROINE'),
(303, 1003, 'HERO'),
(303, 1002, 'GUEST'),
(304, 1004, 'HERO');

SELECT * FROM MOVIE_CAST;

DESC MOVIE_CAST;

#query1
SELECT
MOV_TITLE FROM
MOVIES
WHERE DIR_ID IN (SELECT DIR_ID
FROM DIRECTOR
WHERE DIR_NAME = 'HITCHCOCK');

#query2
SELECT 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)
GROUP BY MOV_TITLE HAVING COUNT(*)>1;

#query3
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;

#query4
SELECT MOV_TITLE,
MAX(REV_STARS) FROM MOVIES
INNER JOIN RATING USING (MOV_ID)
GROUP BY MOV_TITLE
HAVING MAX(REV_STARS)>0
ORDER BY MOV_TITLE;

#query 5
UPDATE
RATING
SET REV_STARS = '5'
WHERE MOV_ID IN (
SELECT
M.MOV_ID FROM
MOVIES M
JOIN DIRECTOR D ON M.DIR_ID = D.DIR_ID
WHERE D.DIR_NAME = 'STEVEN SPIELBERG'
);
D.​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.

ER Diagram:
Schema Diagram:

CREATE DATABASE
PR4; USE PR4;

CREATE TABLE STUDENT (

USN VARCHAR(10) PRIMARY KEY,
SNAME VARCHAR(25),
 ADDRESS VARCHAR(25),
PHONE_NUMBER VARCHAR(10),
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 (

SUBCODE VARCHAR(8) PRIMARY KEY,
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)
);

INSERT INTO STUDENT (USN, SNAME, ADDRESS, PHONE_NUMBER, GENDER)

VALUES
('1RN13CS020', 'AKSHAY', 'BELAGAVI', '8877881122', 'M'),
('1RN13CS062', 'SANDHYA', 'BENGALURU', '7722829912', 'F'),
('1RN13CS091', 'TEESHA', 'BENGALURU', '7712312312', 'F'),
('1RN13CS066', 'SUPRIYA', 'MANGALURU', '8877881122', 'F'),
('1RN14CS010', 'ABHAY', 'BENGALURU', '9900211201', 'M'),
('1RN14CS032', 'BHASKAR', 'BENGALURU', '9923211099', 'M'),
('1RN14CS025', 'ASMI', 'BENGALURU', '7894737377', 'F'),
('1RN15CS011', 'AJAY', 'TUMKUR', '9845091341', 'M'),
('1RN15CS029', 'CHITRA', 'DAVANGERE', '7696772121', 'F'),
('1RN15CS045', 'JEEVA', 'BELLARY', '9944850121', 'M'),
('1RN15CS091', 'SANTOSH', 'MANGALURU', '8812332201', 'M'),
('1RN16CS045', 'ISMAIL', 'KALBURGI', '9900232201', 'M'),
('1RN16CS088', 'SAMEERA', 'SHIMOGA', '9905542212', 'F'),
('1RN16CS122', 'VINAYAKA', 'CHIKAMAGALUR', '8800880011',
'M');

INSERT INTO SEMSEC (SSID, SEM, SEC) VALUES

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

INSERT INTO CLASS (USN, SSID) VALUES

('1BI15CS101', 'CSE8A'),
('1RN13CS020', 'CSE8A'),
('1RN13CS062', 'CSE8A'),
('1RN13CS066', 'CSE8B'),
('1RN13CS091', 'CSE8C'),
('1RN14CS010', 'CSE7A'),
('1RN14CS025', 'CSE7A'),
('1RN14CS032', 'CSE7A'),
('1RN15CS011', 'CSE4A'),
('1RN15CS029', 'CSE4A'),
('1RN15CS045', 'CSE4B'),
('1RN15CS091', 'CSE4C'),
('1RN16CS045', 'CSE3A'),
('1RN16CS088', 'CSE3B'),
('1RN16CS122', 'CSE3C');

INSERT INTO SUBJECT (SUBCODE, TITLE, SEM, CREDITS) VALUES

('10CS81', 'ACA', 8, 4),
('10CS82', 'SSM', 8, 4),
('10CS83', 'NM', 8, 4),
('10CS84', 'CC', 8, 4),
('10CS85', 'PW', 8, 4),
('10CS71', 'OOAD', 7, 4),
('10CS72', 'ECS', 7, 4),
('10CS73', 'PTW', 7, 4),
('10CS74', 'DWDM', 7, 4),
('10CS75', 'JAVA', 7, 4),
('10CS76', 'SAN', 7, 4),
('15CS51', 'ME', 5, 4),
('15CS52', 'CN', 5, 4),
('15CS53', 'DBMS', 5, 4),
('15CS54', 'ATC', 5, 4),
('15CS55', 'JAVA', 5, 3),
('15CS56', 'AI', 5, 3),
('15CS41', 'M4', 4, 4),
('15CS42', 'SE', 4, 4),
('15CS43', 'DAA', 4, 4),
('15CS44', 'MPMC', 4, 4),
('15CS45', 'OOC', 4, 3),
('15CS46', 'DC', 4, 3),
('15CS31', 'M3', 3, 4),
('15CS32', 'ADE', 3, 4),
('15CS33', 'DSA', 3, 4),
('15CS34', 'CO', 3, 4),
('15CS35', 'USP', 3, 3),
('15CS36', 'DMS', 3, 3);

INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
('1BI15CS101', '10CS71', 'CSE8C', 15, 19, 18),
('1RN13CS091', '10CS81', 'CSE8C', 15, 16, 18),

('1RN13CS091', '10CS82', 'CSE8C', 12, 19, 14),

('1RN13CS091', '10CS83', 'CSE8C', 19, 15, 20),
('1RN13CS091', '10CS84', 'CSE8C', 20, 16, 19),
('1RN13CS091', '10CS85', 'CSE8C', 15, 15, 12);

SELECT * FROM STUDENT;

#query1
SELECT S.*, SS.SEM, SS.SEC
FROM STUDENT S
JOIN CLASS C ON S.USN = C.USN
JOIN SEMSEC SS ON C.SSID = SS.SSID
WHERE SS.SEM = 4 AND SS.SEC = 'C';

#query2
SELECT SS.SEM, SS.SEC, S.GENDER, COUNT(S.GENDER) AS COUNT
FROM STUDENT S
JOIN CLASS C ON S.USN = C.USN
JOIN SEMSEC SS ON C.SSID = SS.SSID
GROUP BY SS.SEM, SS.SEC, S.GENDER
ORDER BY SS.SEM;

#query3
CREATE VIEW STU_TEST_MARKS_VIEW AS
SELECT SUBCODE, TEST1
FROM IAMARKS
WHERE USN = '1RN13CS091';

SELECT * FROM STU_TEST_MARKS_VIEW;

#query4
SET SQL_SAFE_UPDATES = 0;
UPDATE IAMARKS
SET FinalIA = (test1 + test2 + test3 - LEAST(test1, test2, test3)) / 2;
select * from iamarks;

#query5
SELECT S.USN, S.SNAME, S.ADDRESS, S.PHONE_NUMBER, 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
JOIN CLASS C ON S.USN = C.USN
JOIN SEMSEC SS ON SS.SSID = C.SSID
JOIN IAMARKS IA ON S.USN = IA.USN
JOIN SUBJECT SUB ON SUB.SUBCODE = IA.SUBCODE
WHERE SUB.SEM = 8
AND SS.SEC IN ('A', 'B', 'C');
E.​ 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). 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.

ER Diagram:
Schema Diagram:

create database p5;

use p5;

-- Create the database

CREATE DATABASE IF NOT EXISTS COMPANY;
USE COMPANY;

-- Create DEPARTMENT table

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

-- Create EMPLOYEE table

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

-- Add MGRSSN foreign key to DEPARTMENT

ALTER TABLE DEPARTMENT
ADD COLUMN MGRSSN VARCHAR(20),
ADD FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN);

-- Create DLOCATION table

CREATE TABLE DLOCATION (
DLOC VARCHAR(20),
DNO VARCHAR(20),
PRIMARY KEY (DNO, DLOC),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNO)
);

-- Create PROJECT table

CREATE TABLE PROJECT
(
PNO INT PRIMARY KEY,
PNAME VARCHAR(20),
PLOCATION VARCHAR(20),
DNO VARCHAR(20),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNO)
);

-- Create WORKS_ON table

CREATE TABLE WORKS_ON
(
HOURS
DECIMAL(5,2), SSN
VARCHAR(20), PNO
INT,
PRIMARY KEY (SSN, PNO),
FOREIGN KEY (SSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY (PNO) REFERENCES PROJECT(PNO)
);

-- DEPARTMENT (must go first)

INSERT INTO DEPARTMENT
VALUES
('D1','ACCOUNTS','2020-01-01',NULL)
,
('D2','IT','2020-02-01',NULL),
('D3','HR','2020-03-01',NULL),
('D4','SALES','2020-04-01',NULL),
('D5','ENGINEERING','2020-05-01',NULL);

-- EMPLOYEE (after DEPARTMENT)

INSERT INTO EMPLOYEE VALUES
('E1','JOHN','SMITH','NY','M',70000,NULL,'D1'),
('E2','JANE','DOE','LA','F',80000,'E1','D2'),
('E3','BOB','JOHNSON','CHI','M',90000,'E1','D3'),
('E4','ALICE','WILLIAMS','SF','F',85000,'E2','D4'),
('E5','MIKE','BROWN','BOS','M',95000,'E2','D5');

-- Update DEPARTMENT managers (after EMPLOYEE)

UPDATE DEPARTMENT SET MGRSSN='E1' WHERE
DNO='D1'; UPDATE DEPARTMENT SET MGRSSN='E2'
WHERE DNO='D2'; UPDATE DEPARTMENT SET
MGRSSN='E3' WHERE DNO='D3'; UPDATE DEPARTMENT
SET MGRSSN='E4' WHERE DNO='D4'; UPDATE
DEPARTMENT SET MGRSSN='E5' WHERE DNO='D5';

-- DLOCATION (after
DEPARTMENT) INSERT INTO
DLOCATION VALUES
('FLOOR1','D1'),
('FLOOR2','D2'),
('FLOOR3','D3'),
('FLOOR4','D4'),
('FLOOR5','D5');

-- PROJECT (after DEPARTMENT)

INSERT INTO PROJECT VALUES
(101,'ACCOUNTING','FLOOR1','D1'),
(102,'WEBSITE','FLOOR2','D2'),
(103,'RECRUITING','FLOOR3','D3'),
(104,'MARKETING','FLOOR4','D4'),
(105,'DEVELOPMENT','FLOOR5','D5');

-- WORKS_ON (last - after EMPLOYEE & PROJECT)

INSERT INTO WORKS_ON VALUES
(40.0,'E1',101),
(35.5,'E2',102),
(20.0,'E3',103),
(15.5,'E4',104),
(10.0,'E5',105);

#query1
(SELECT DISTINCT P.PNO
FROM PROJECT P
JOIN DEPARTMENT D ON P.DNO = D.DNO
JOIN EMPLOYEE E ON D.MGRSSN = E.SSN
WHERE E.LNAME = 'SCOTT')
UNION
(SELECT DISTINCT P1.PNO
FROM PROJECT P1
JOIN WORKS_ON W ON P1.PNO =
W.PNO JOIN EMPLOYEE E1 ON W.SSN =
E1.SSN WHERE E1.LNAME = SMITH);
#query2
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‘;

#query3
SELEC
T
SUM(E.SALARY) AS TOTAL_SALARY,
MAX(E.SALARY) AS MAX_SALARY,
MIN(E.SALARY) AS MIN_SALARY,
AVG(E.SALARY) AS AVG_SALARY
FROM
EMPLOYEE E
JOIN DEPARTMENT D ON E.DNO = D.DNO
WHERE
D.DNAME = 'ACCOUNTS';

#query4
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
)
);
#query5
SELEC
T
D.DNO,
COUNT(*) AS HIGH_EARNERS
FROM
DEPARTMENT D
JOIN EMPLOYEE E ON D.DNO =
E.DNO WHERE
E.SALARY > 600000
AND D.DNO IN (
SELECT E1.DNO
FROM EMPLOYEE E1
GROUP BY E1.DNO
HAVING COUNT(*) > 5
)
GROUP
BY
D.DNO;