DBMS LAB Manual
User Manual:
Open the PDF directly: View PDF 
.
Page Count: 48
| Download | |
| Open PDF In Browser | View PDF |
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
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 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:
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Table Creation
CREATE TABLE ACTOR (
ACT_ID NUMBER (3),
ACT_NAME VARCHAR (20),
ACT_GENDER CHAR (1),
PRIMARY KEY (ACT_ID));
CREATE TABLE DIRECTOR (
DIR_ID NUMBER (3),
DIR_NAME VARCHAR (20),
DIR_PHONE NUMBER (10),
PRIMARY KEY (DIR_ID));
CREATE TABLE MOVIES (
MOV_ID NUMBER (4),
MOV_TITLE VARCHAR (25),
MOV_YEAR NUMBER (4),
MOV_LANG VARCHAR (12),
DIR_ID NUMBER (3),
PRIMARY KEY (MOV_ID),
FOREIGN KEY (DIR_ID) REFERENCES DIRECTOR (DIR_ID));
CREATE TABLE MOVIE_CAST (
ACT_ID NUMBER (3),
MOV_ID NUMBER (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 NUMBER (4),
REV_STARS VARCHAR (25),
PRIMARY KEY (MOV_ID),
FOREIGN KEY (MOV_ID) REFERENCES MOVIES (MOV_ID));
Table Descriptions
DESC ACTOR;
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
DESC DIRECTOR;
DESC DIRECTOR;
DESC MOVIES;
DESC MOVIE_CAST;
DESC RATING;
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Insertion of Values to 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, ‘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);
SELECT * FROM ACTOR;
SELECT * FROM DIRECTOR;
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
SELECT * FROM MOVIES;
SELECT * FROM MOVIE_CAST;
SELECT * FROM RATING;
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’);
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
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 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;
OR
SELECT A.ACT_NAME, A.ACT_NAME, C.MOV_TITLE, C.MOV_YEAR
FROM ACTOR A, MOVIE_CAST B, MOVIES C
WHERE A.ACT_ID=B.ACT_ID
AND B.MOV_ID=C.MOV_ID
AND C.MOV_YEAR NOT BETWEEN 2000 AND 2015;
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
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, MAX (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
KL
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’));
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
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.
Solution:
Entity - Relationship Diagram
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Schema Diagram
Table Creation
CREATE TABLE STUDENT (
USN VARCHAR (10) PRIMARY KEY,
SNAME VARCHAR (25),
ADDRESS VARCHAR (25),
PHONE NUMBER (10),
GENDER CHAR (1));
CREATE TABLE SEMSEC (
SSID VARCHAR (5) PRIMARY KEY,
SEM NUMBER (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));
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
CREATE TABLE SUBJECT (
SUBCODE VARCHAR (8),
TITLE VARCHAR (20),
SEM NUMBER (2),
CREDITS NUMBER (2),
PRIMARY KEY (SUBCODE));
CREATE TABLE IAMARKS (
USN VARCHAR (10),
SUBCODE VARCHAR (8),
SSID VARCHAR (5),
TEST1 NUMBER (2),
TEST2 NUMBER (2),
TEST3 NUMBER (2),
FINALIA NUMBER (2),
PRIMARY KEY (USN, SUBCODE, SSID),
FOREIGN KEY (USN) REFERENCES STUDENT (USN),
FOREIGN KEY (SUBCODE) REFERENCES SUBJECT (SUBCODE),
FOREIGN KEY (SSID) REFERENCES SEMSEC (SSID));
Table Descriptions
DESC STUDENT;
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Insertion of values to tables
INSERT INTO STUDENT VALUES ('1RN13CS020','AKSHAY','BELAGAVI', 8877881122,'M');
INSERT INTO STUDENT VALUES ('1RN13CS062','SANDHYA','BENGALURU',
7722829912,'F');
INSERT INTO STUDENT VALUES ('1RN13CS091','TEESHA','BENGALURU', 7712312312,'F');
INSERT INTO STUDENT VALUES ('1RN13CS066','SUPRIYA','MANGALURU',
8877881122,'F');
INSERT INTO STUDENTVALUES ('1RN14CS010','ABHAY','BENGALURU', 9900211201,'M');
INSERT INTO STUDENT VALUES ('1RN14CS032','BHASKAR','BENGALURU',
9923211099,'M');
INSERT INTO STUDENTVALUES ('1RN14CS025','ASMI','BENGALURU', 7894737377,'F');
INSERT INTO STUDENT VALUES ('1RN15CS011','AJAY','TUMKUR', 9845091341,'M');
INSERT INTO STUDENT VALUES ('1RN15CS029','CHITRA','DAVANGERE', 7696772121,'F');
INSERT INTO STUDENT VALUES ('1RN15CS045','JEEVA','BELLARY', 9944850121,'M');
INSERT INTO STUDENT VALUES ('1RN15CS091','SANTOSH','MANGALURU',
8812332201,'M');
INSERT INTO STUDENT VALUES ('1RN16CS045','ISMAIL','KALBURGI', 9900232201,'M');
INSERT INTO STUDENT VALUES ('1RN16CS088','SAMEERA','SHIMOGA', 9905542212,'F');
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
INSERT INTO STUDENT VALUES ('1RN16CS122','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 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');
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
INSERT INTO SEMSEC VALUES (‘CSE1B’, 1,’B’);
INSERT INTO SEMSEC VALUES ('CSE1C', 1,’C’);
INSERT INTO CLASS VALUES (‘1RN13CS020’,’CSE8A’);
INSERT INTO CLASS VALUES (‘1RN13CS062’,’CSE8A’);
INSERT INTO CLASS VALUES (‘1RN13CS066’,’CSE8B’);
INSERT INTO CLASS VALUES (‘1RN13CS091’,’CSE8C’);
INSERT INTO CLASS VALUES (‘1RN14CS010’,’CSE7A’);
INSERT INTO CLASS VALUES (‘1RN14CS025’,’CSE7A’);
INSERT INTO CLASS VALUES (‘1RN14CS032’,’CSE7A’);
INSERT INTO CLASS VALUES (‘1RN15CS011’,’CSE4A’);
INSERT INTO CLASS VALUES (‘1RN15CS029’,’CSE4A’);
INSERT INTO CLASS VALUES (‘1RN15CS045’,’CSE4B’);
INSERT INTO CLASS VALUES (‘1RN15CS091’,’CSE4C’);
INSERT INTO CLASS VALUES (‘1RN16CS045’,’CSE3A’);
INSERT INTO CLASS VALUES (‘1RN16CS088’,’CSE3B’);
INSERT INTO CLASS VALUES (‘1RN16CS122’,’CSE3C’);
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 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);
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
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 (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
('1RN13CS091','10CS81','CSE8C', 15, 16, 18);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
('1RN13CS091','10CS82','CSE8C', 12, 19, 14);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
('1RN13CS091','10CS83','CSE8C', 19, 15, 20);
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
('1RN13CS091','10CS84','CSE8C', 20, 16, 19);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
('1RN13CS091','10CS85','CSE8C', 15, 15, 12);
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
SQL> SELECT * FROM SUBJECT;
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Queries:
1. List all the student details studying in fourth semester ‘C’ section.
SELECT S.*, SS.SEM, SS.SEC
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
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
WHERES.USN = C.USN AND
SS.SSID = C.SSID
GROUP BY SS.SEM, SS.SEC, S.GENDER
ORDER BY SEM;
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 = '1RN13CS091';
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
4. Calculate the FinalIA (average of best two test marks) and update the corresponding table
for all students.
CREATE OR REPLACE PROCEDURE AVGMARKS
IS
CURSOR C_IAMARKS IS
SELECT GREATEST(TEST1,TEST2) AS A, GREATEST(TEST1,TEST3) AS B,
GREATEST(TEST3,TEST2) AS C
FROM IAMARKS
WHERE FINALIA IS NULL
FOR UPDATE;
C_A NUMBER;
C_B NUMBER;
C_C NUMBER;
C_SM NUMBER;
C_AV NUMBER;
BEGIN
OPEN C_IAMARKS;
LOOP
FETCH C_IAMARKS INTO C_A, C_B, C_C;
EXIT WHEN C_IAMARKS%NOTFOUND;
--DBMS_OUTPUT.PUT_LINE(C_A || ' ' || C_B || ' ' || C_C);
IF (C_A != C_B) THEN
C_SM:=C_A+C_B;
ELSE
C_SM:=C_A+C_C;
END IF;
C_AV:=C_SM/2;
--DBMS_OUTPUT.PUT_LINE('SUM = '||C_SM);
--DBMS_OUTPUT.PUT_LINE('AVERAGE = '||C_AV);
UPDATE IAMARKS SET FINALIA=C_AV WHERE CURRENT OF C_IAMARKS;
END LOOP;
CLOSE C_IAMARKS;
END;
/
Note: Before execution of PL/SQL procedure, IAMARKS table contents are:
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Below SQL code is to invoke the PL/SQL stored procedure from the command line:
BEGIN
AVGMARKS;
END;
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 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;
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
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.
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Table Creation
CREATE TABLE DEPARTMENT
(DNO VARCHAR2 (20) PRIMARY KEY,
DNAME VARCHAR2 (20),
MGRSTARTDATE DATE);
CREATE TABLE EMPLOYEE
(SSN VARCHAR2 (20) PRIMARY KEY,
FNAME VARCHAR2 (20),
LNAME VARCHAR2 (20),
ADDRESS VARCHAR2 (20),
SEX CHAR (1),
SALARY INTEGER,
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 REFERENCES EMPLOYEE (SSN);
CREATE TABLE DLOCATION
(DLOC VARCHAR2 (20),
DNO REFERENCES DEPARTMENT (DNO),
PRIMARY KEY (DNO, DLOC));
CREATE TABLE PROJECT
(PNO INTEGER PRIMARY KEY,
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
PNAME VARCHAR2 (20),
PLOCATION VARCHAR2 (20),
DNO REFERENCES DEPARTMENT (DNO));
CREATE TABLE WORKS_ON
(HOURS NUMBER (2),
SSN REFERENCES EMPLOYEE (SSN),
PNO REFERENCES PROJECT(PNO),
PRIMARY KEY (SSN, PNO));
Table Descriptions
DESC EMPLOYEE;
DESC DEPARTMENT;
DESC DLOCATION;
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
DESC PROJECT;
DESC WORKS_ON;
Insertion of values to tables
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSECE01’,’JOHN’,’SCOTT’,’BANGALORE’,’M’, 450000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE01’,’JAMES’,’SMITH’,’BANGALORE’,’M’, 500000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE02’,’HEARN’,’BAKER’,’BANGALORE’,’M’, 700000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE03’,’EDWARD’,’SCOTT’,’MYSORE’,’M’, 500000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE04’,’PAVAN’,’HEGDE’,’MANGALORE’,’M’, 650000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE05’,’GIRISH’,’MALYA’,’MYSORE’,’M’, 450000);
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);
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
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’);
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’);
Note: update entries of employee table to fill missing fields SUPERSSN and DNO
UPDATE EMPLOYEE SET
SUPERSSN=NULL, DNO=’3’
WHERE SSN=’RNSECE01’;
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
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
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’;
INSERT INTO DLOCATION VALUES (’BANGALORE’, ‘1’);
INSERT INTO DLOCATION VALUES (’BANGALORE’, ‘2’);
INSERT INTO DLOCATION VALUES (’BANGALORE’, ‘3’);
INSERT INTO DLOCATION VALUES (’MANGALORE’, ‘4’);
INSERT INTO DLOCATION VALUES (’MANGALORE’, ‘5’);
INSERT INTO PROJECT VALUES (100,’IOT’,’BANGALORE’,’5’);
INSERT INTO PROJECT VALUES (101,’CLOUD’,’BANGALORE’,’5’);
INSERT INTO PROJECT VALUES (102,’BIGDATA’,’BANGALORE’,’5’);
INSERT INTO PROJECT VALUES (103,’SENSORS’,’BANGALORE’,’3’);
INSERT INTO PROJECT VALUES (104,’BANK MANAGEMENT’,’BANGALORE’,’1’);
INSERT INTO PROJECT VALUES (105,’SALARY MANAGEMENT’,’BANGALORE’,’1’);
INSERT INTO PROJECT VALUES (106,’OPENSTACK’,’BANGALORE’,’4’);
INSERT INTO PROJECT VALUES (107,’SMART CITY’,’BANGALORE’,’2’);
INSERT INTO WORKS_ON VALUES (4, ‘RNSCSE01’, 100);
INSERT INTO WORKS_ON VALUES (6, ‘RNSCSE01’, 101);
INSERT INTO WORKS_ON VALUES (8, ‘RNSCSE01’, 102);
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
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);
SELECT * FROM EMPLOYEE;
SELECT * FROM DEPARTMENT;
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
SELECT * FROM DLOCATION;
SELECT * FROM PROJECT;
SELECT * FROM WORKS_ON;
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 E.DNO=D.DNO
AND D.MGRSSN=E.SSN
AND E.LNAME=’SCOTT’)
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
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.LNAME=’SCOTT’);
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’;
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
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’) MINUS (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 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;
Prepared by Prof. Guru Prasad and Prof. Suresh Chimkode
Source Exif Data:
File Type : PDF File Type Extension : pdf MIME Type : application/pdf PDF Version : 1.5 Linearized : No Page Count : 48 Language : en-US Tagged PDF : Yes Author : CGLAB Creator : Microsoft® Word 2016 Create Date : 2018:01:22 20:00:11+05:30 Modify Date : 2018:01:22 20:00:11+05:30 Producer : Microsoft® Word 2016EXIF Metadata provided by EXIF.tools