CREATE TABLE ACTOR (
ACT_ID INT(3),
ACT_NAME VARCHAR(20),
ACT_GENDER CHAR(1),
PRIMARY KEY (ACT_ID)
);
CREATE TABLE DIRECTOR (
DIR_ID INT(3),
DIR_NAME VARCHAR(20),
DIR_PHONE BIGINT(10),
PRIMARY KEY (DIR_ID)
);
CREATE TABLE MOVIES (
MOV_ID INT(4),
MOV_TITLE VARCHAR(25),
MOV_YEAR INT(4),
MOV_LANG VARCHAR(12),
DIR_ID INT(3),
PRIMARY KEY (MOV_ID),
FOREIGN KEY (DIR_ID) REFERENCES DIRECTOR(DIR_ID)
);
CREATE TABLE MOVIE_CAST (
ACT_ID INT(3),
MOV_ID INT(4),
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(4),
REV_STARS INT,
PRIMARY KEY (MOV_ID),
FOREIGN KEY (MOV_ID) REFERENCES MOVIES(MOV_ID)
);
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, 'TELAGU', 60);
INSERT INTO MOVIES VALUES (1002,'BAHUBALI-1', 2015, 'TELAGU', 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');
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);
-- 1. List the titles of all movies directed by 'RAJAMOULI'.
SELECT MOV_TITLE
FROM MOVIES
WHERE DIR_ID IN (SELECT DIR_ID
FROM DIRECTOR
WHERE DIR_NAME = 'RAJAMOULI');
-- 2. Find the movie names where one or more actors acted in two or more movies.
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;
-- 3. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN operation).
SELECT A.ACT_NAME, C.MOV_TITLE, C.MOV_YEAR
FROM ACTOR A JOIN MOVIE_CAST B
ON A.ACT_ID=B.ACT_ID JOIN MOVIES C
ON B.MOV_ID=C.MOV_ID
WHERE C.MOV_YEAR < 2000 OR C.MOV_YEAR > 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 M.MOV_TITLE, MAX(R.REV_STARS) AS MAX_STARS
FROM MOVIES M
JOIN RATING R ON M.MOV_ID = R.MOV_ID
GROUP BY M.MOV_TITLE
HAVING MAX(R.REV_STARS) IS NOT NULL
ORDER BY M.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'
)
);
select * from rating;
CREATE TABLE STUDENT (
USN VARCHAR(10) PRIMARY KEY,
SNAME VARCHAR(25),
ADDRESS VARCHAR(25),
PHONE VARCHAR(10),
GENDER CHAR(1)
);
CREATE TABLE SEMSEC (
SSID VARCHAR(5) PRIMARY KEY,
SEM INT(2),
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),
TITLE VARCHAR(20),
SEM INT(2),
CREDITS INT(2),
PRIMARY KEY (SUBCODE)
);
CREATE TABLE IAMARKS (
USN VARCHAR(10),
SUBCODE VARCHAR(8),
SSID VARCHAR(5),
TEST1 INT(2),
TEST2 INT(2),
TEST3 INT(2),
FINALIA INT(2),
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 VALUES ('1JS13CS020','AKSHAY','BELAGAVI','8877881122','M');
INSERT INTO STUDENT VALUES ('1JS13CS062','SANDHYA','BENGALURU','7722829912','F');
INSERT INTO STUDENT VALUES ('1JS13CS091','TEESHA','BENGALURU','7712312312','F');
INSERT INTO STUDENT VALUES ('1JS13CS066','SUPRIYA','MANGALURU','8877881123','F');
INSERT INTO STUDENT VALUES ('1JS14CS010','ABHAY','BENGALURU','9900211201','M');
INSERT INTO STUDENT VALUES ('1JS14CS032','BHASKAR','BENGALURU','9923211099','M');
INSERT INTO STUDENT VALUES ('1JS14CS025','ASMI','BENGALURU','7894737377','F');
INSERT INTO STUDENT VALUES ('1JS15CS011','AJAY','TUMKUR','9845091341','M');
INSERT INTO STUDENT VALUES ('1JS15CS029','CHITRA','DAVANGERE','7696772121','F');
INSERT INTO STUDENT VALUES ('1JS15CS045','JEEVA','BELLARY','9944850121','M');
INSERT INTO STUDENT VALUES ('1JS15CS091','SANTOSH','MANGALURU','8812332201','M');
INSERT INTO STUDENT VALUES ('1JS16CS045','ISMAIL','KALBURGI','9900232201','M');
INSERT INTO STUDENT VALUES ('1JS16CS088','SAMEERA','SHIMOGA','9905542212','F');
INSERT INTO STUDENT VALUES ('1JS16CS122','VINAYAKA','CHIKAMAGALUR','8800880011','M');
INSERT INTO SEMSEC VALUES ('CSE8A',8,'A');
INSERT INTO SEMSEC VALUES ('CSE8B',8,'B');
INSERT INTO SEMSEC VALUES ('CSE8C',8,'C');
INSERT INTO CLASS VALUES ('1JS13CS020','CSE8A');
INSERT INTO CLASS VALUES ('1JS13CS062','CSE8A');
INSERT INTO CLASS VALUES ('1JS13CS066','CSE8B');
INSERT INTO CLASS VALUES ('1JS13CS091','CSE8C');
INSERT INTO SUBJECT VALUES ('10CS81','ACA',8,4);
INSERT INTO SUBJECT VALUES ('10CS82','SSM',8,4);
INSERT INTO SUBJECT VALUES ('10CS83','NM',8,4);
INSERT INTO SUBJECT VALUES ('10CS84','CC',8,4);
INSERT INTO SUBJECT VALUES ('10CS85','PW',8,4);
INSERT INTO IAMARKS VALUES ('1JS13CS091','10CS81','CSE8C',15,16,18,NULL);
INSERT INTO IAMARKS VALUES ('1JS13CS091','10CS82','CSE8C',12,19,14,NULL);
INSERT INTO IAMARKS VALUES ('1JS13CS091','10CS83','CSE8C',19,15,20,NULL);
INSERT INTO IAMARKS VALUES ('1JS13CS091','10CS84','CSE8C',20,16,19,NULL);
INSERT INTO IAMARKS VALUES ('1JS13CS091','10CS85','CSE8C',15,15,12,NULL);
-- 1. 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';
-- 2. 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, SEC;
-- 3. Create a view of Test1 marks of student USN '1BI15CS101' in all subjects.
CREATE VIEW STU_TEST1_MARKS_VIEW AS
SELECT TEST1, SUBCODE
FROM IAMARKS
WHERE USN = '1JS13CS091';
-- 4. Calculate the FinalIA (average of best two test marks) and update the corresponding table for all students.
UPDATE IAMARKS
SET FINALIA =
CASE
WHEN TEST1 < TEST2 AND TEST1 < TEST3 THEN ROUND((TEST2 + TEST3) / 2)
WHEN TEST2 < TEST3 THEN ROUND((TEST1 + TEST3) / 2)
ELSE ROUND((TEST1 + TEST2) / 2)
END
WHERE FINALIA IS NULL;
-- 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.
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 CATEGORY
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;
-- Create Table DEPARTMENT with PRIMARY KEY as DNO
CREATE TABLE DEPARTMENT (
DNO VARCHAR(20) PRIMARY KEY,
DNAME VARCHAR(20),
MGR_SSN VARCHAR(20),
MGR_START_DATE DATE
);
DESC DEPARTMENT;
-- Create Table EMPLOYEE with PRIMARY KEY as SSN
CREATE TABLE EMPLOYEE (
SSN VARCHAR(20) PRIMARY KEY,
NAME VARCHAR(20),
ADDRESS VARCHAR(20),
SEX CHAR(1),
SALARY INTEGER,
SUPERSSN VARCHAR(20),
DNO VARCHAR(20),
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE (SSN),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNO)
);
DESC EMPLOYEE;
-- ADD FOREIGN KEY Constraint to DEPARTMENT table
ALTER TABLE DEPARTMENT
ADD FOREIGN KEY (MGR_SSN) REFERENCES EMPLOYEE(SSN);
-- Create Table DLOCATION with PRIMARY KEY as DNO and DLOC
-- and FOREIGN KEY DNO referring DEPARTMENT table
CREATE TABLE DLOCATION (
DLOC VARCHAR(20),
DNO VARCHAR(20),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNO),
PRIMARY KEY (DNO, DLOC)
);
DESC DLOCATION;
-- Create Table PROJECT with PRIMARY KEY as PNO
-- and FOREIGN KEY DNO referring DEPARTMENT table
CREATE TABLE PROJECT (
PNO INTEGER PRIMARY KEY,
PNAME VARCHAR(20),
PLOCATION VARCHAR(20),
DNO VARCHAR(20),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNO)
);
DESC PROJECT;
-- Create Table WORKS_ON with PRIMARY KEY as PNO and SSN
-- and FOREIGN KEY SSN and PNO referring EMPLOYEE and PROJECT table
CREATE TABLE WORKS_ON (
HOURS INTEGER,
SSN VARCHAR(20),
PNO INTEGER,
FOREIGN KEY (SSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY (PNO) REFERENCES PROJECT(PNO),
PRIMARY KEY (SSN, PNO)
);
DESC WORKS_ON;
-- Inserting records into EMPLOYEE table
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES ('ABC01','BEN SCOTT','BANGALORE','M', 450000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES ('ABC02','HARRY SMITH','BANGALORE','M', 500000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES ('ABC03','LEAN BAKER','BANGALORE','M', 700000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES ('ABC04','MARTIN SCOTT','MYSORE','M', 500000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES ('ABC05','RAVAN HEGDE','MANGALORE','M', 650000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES ('ABC06','GIRISH HOSUR','MYSORE','M', 450000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES ('ABC07','NEELA SHARMA','BANGALORE','F', 800000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES ('ABC08','ADYA KOLAR','MANGALORE','F', 350000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES ('ABC09','PRASANNA KUMAR','MANGALORE','M', 300000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES ('ABC10','VEENA KUMARI','MYSORE','M', 600000);
INSERT INTO EMPLOYEE (SSN, NAME, ADDRESS, SEX, SALARY) VALUES ('ABC11','DEEPAK RAJ','BANGALORE','M', 500000);
SELECT * FROM EMPLOYEE;
-- Inserting records into DEPARTMENT table
INSERT INTO DEPARTMENT VALUES ('1','ACCOUNTS','ABC09', '2016-01-03');
INSERT INTO DEPARTMENT VALUES ('2','IT','ABC11', '2017-02-04');
INSERT INTO DEPARTMENT VALUES ('3','HR','ABC01', '2016-04-05');
INSERT INTO DEPARTMENT VALUES ('4','HELPDESK', 'ABC10', '2017-06-03');
INSERT INTO DEPARTMENT VALUES ('5','SALES','ABC06', '2017-01-08');
SELECT * FROM DEPARTMENT;
-- Updating EMPLOYEE records
UPDATE EMPLOYEE SET SUPERSSN=NULL, DNO='3' WHERE SSN='ABC01';
UPDATE EMPLOYEE SET SUPERSSN='ABC03', DNO='5' WHERE SSN='ABC02';
UPDATE EMPLOYEE SET SUPERSSN='ABC04', DNO='5' WHERE SSN='ABC03';
UPDATE EMPLOYEE SET SUPERSSN='ABC06', DNO='5' WHERE SSN='ABC04';
UPDATE EMPLOYEE SET DNO='5', SUPERSSN='ABC06' WHERE SSN='ABC05';
UPDATE EMPLOYEE SET DNO='5', SUPERSSN='ABC07' WHERE SSN='ABC06';
UPDATE EMPLOYEE SET DNO='5', SUPERSSN=NULL WHERE SSN='ABC07';
UPDATE EMPLOYEE SET DNO='1', SUPERSSN='ABC09' WHERE SSN='ABC08';
UPDATE EMPLOYEE SET DNO='1', SUPERSSN=NULL WHERE SSN='ABC09';
UPDATE EMPLOYEE SET DNO='4', SUPERSSN=NULL WHERE SSN='ABC10';
UPDATE EMPLOYEE SET DNO='2', SUPERSSN=NULL WHERE SSN='ABC11';
SELECT * FROM EMPLOYEE;
-- Inserting records into DLOCATION table
INSERT INTO DLOCATION VALUES ('BENGALURU', '1');
INSERT INTO DLOCATION VALUES ('BENGALURU', '2');
INSERT INTO DLOCATION VALUES ('BENGALURU', '3');
INSERT INTO DLOCATION VALUES ('MYSORE', '4');
INSERT INTO DLOCATION VALUES ('MYSORE', '5');
SELECT * FROM DLOCATION;
-- Inserting records into PROJECT table
INSERT INTO PROJECT VALUES (1000,'IOT','BENGALURU','5');
INSERT INTO PROJECT VALUES (1001,'CLOUD','BENGALURU','5');
INSERT INTO PROJECT VALUES (1002,'BIGDATA','BENGALURU','5');
INSERT INTO PROJECT VALUES (1003,'SENSORS','BENGALURU','3');
INSERT INTO PROJECT VALUES (1004,'BANK MANAGEMENT','BENGALURU','1');
INSERT INTO PROJECT VALUES (1005,'SALARY MANAGEMENT','BANGALORE','1');
INSERT INTO PROJECT VALUES (1006,'OPENSTACK','BENGALURU','4');
INSERT INTO PROJECT VALUES (1007,'SMART CITY','BENGALURU','2');
SELECT * FROM PROJECT;
-- Inserting records into WORKS_ON table
INSERT INTO WORKS_ON VALUES (4, 'ABC02', 1000);
INSERT INTO WORKS_ON VALUES (6, 'ABC02', 1001);
INSERT INTO WORKS_ON VALUES (8, 'ABC02', 1002);
INSERT INTO WORKS_ON VALUES (10,'ABC03', 1000);
INSERT INTO WORKS_ON VALUES (3, 'ABC05', 1000);
INSERT INTO WORKS_ON VALUES (4, 'ABC06', 1001);
INSERT INTO WORKS_ON VALUES (5, 'ABC07', 1002);
INSERT INTO WORKS_ON VALUES (6, 'ABC04', 1002);
INSERT INTO WORKS_ON VALUES (7, 'ABC01', 1003);
INSERT INTO WORKS_ON VALUES (5, 'ABC08', 1004);
INSERT INTO WORKS_ON VALUES (6, 'ABC09', 1005);
INSERT INTO WORKS_ON VALUES (4, 'ABC10', 1006);
INSERT INTO WORKS_ON VALUES (10,'ABC11', 1007);
SELECT * FROM WORKS_ON;
— Queries
-- 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 E.DNO=D.DNO
AND D.MGR_SSN=E.SSN
AND E.NAME LIKE '%SCOTT'
UNION
SELECT DISTINCT P1.PNO
FROM PROJECT P1, WORKS_ON W, EMPLOYEE E1
WHERE P1.PNO=W.PNO
AND E1.SSN=W.SSN
AND E1.NAME LIKE '%SCOTT';
-- Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent raise.
SELECT E.NAME, 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';
-- 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';
-- Retrieve the name of each employee who works on all the projects controlled by department number 5 (use NOT EXISTS operator).
SELECT E.NAME
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
)
);
-- 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 D.DNO, COUNT(*)
FROM DEPARTMENT D, EMPLOYEE E
WHERE D.DNO=E.DNO
AND E.SALARY > 600000
AND D.DNO IN (
SELECT E1.DNO
FROM EMPLOYEE E1
GROUP BY E1.DNO
HAVING COUNT(*) > 5
)
GROUP BY D.DNO;