CREATE TABLE PUBLISHER (
    NAME VARCHAR2(20) PRIMARY KEY,
    PHONE INTEGER,
    ADDRESS VARCHAR2(20)
);

CREATE TABLE BOOK (
    BOOK_ID INTEGER PRIMARY KEY,
    TITLE VARCHAR2(20),
    PUB_YEAR VARCHAR2(20),
    PUBLISHER_NAME REFERENCES PUBLISHER(NAME) ON DELETE CASCADE
);

CREATE TABLE BOOK_AUTHORS (
    AUTHOR_NAME VARCHAR2(20),
    BOOK_ID INTEGER REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,
    PRIMARY KEY (BOOK_ID, AUTHOR_NAME)
);

CREATE TABLE LIBRARY_BRANCH (
    BRANCH_ID INTEGER PRIMARY KEY,
    BRANCH_NAME VARCHAR2(50),
    ADDRESS VARCHAR2(50)
);

CREATE TABLE BOOK_COPIES (
    NO_OF_COPIES INTEGER,
    BOOK_ID INTEGER REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,
    BRANCH_ID INTEGER REFERENCES LIBRARY_BRANCH(BRANCH_ID) ON DELETE CASCADE,
    PRIMARY KEY (BOOK_ID, BRANCH_ID)
);

CREATE TABLE CARD (
    CARD_NO INTEGER PRIMARY KEY
);

CREATE TABLE BOOK_LENDING (
    DATE_OUT DATE,
    DUE_DATE DATE,
    BOOK_ID INTEGER REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,
    BRANCH_ID INTEGER REFERENCES LIBRARY_BRANCH(BRANCH_ID) ON DELETE CASCADE,
    CARD_NO INTEGER REFERENCES CARD(CARD_NO) ON DELETE CASCADE,
    PRIMARY KEY (BOOK_ID, BRANCH_ID, CARD_NO)
);

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

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 ('NAVATHE', 1);
INSERT INTO BOOK_AUTHORS VALUES ('NAVATHE', 2);
INSERT INTO BOOK_AUTHORS VALUES ('TANENBAUM', 3);
INSERT INTO BOOK_AUTHORS VALUES ('EDWARD ANGEL', 4);
INSERT INTO BOOK_AUTHORS VALUES ('GALVIN', 5);

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 (10, 1, 10);
INSERT INTO BOOK_COPIES VALUES (5, 1, 11);
INSERT INTO BOOK_COPIES VALUES (2, 2, 12);
INSERT INTO BOOK_COPIES VALUES (5, 2, 13);
INSERT INTO BOOK_COPIES VALUES (7, 3, 14);
INSERT INTO BOOK_COPIES VALUES (1, 5, 10);
INSERT INTO BOOK_COPIES VALUES (3, 4, 11);

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 ('01-JAN-17', '01-JUN-17', 1, 10, 101);
INSERT INTO BOOK_LENDING VALUES ('11-JAN-17', '11-MAR-17', 3, 14, 101);
INSERT INTO BOOK_LENDING VALUES ('21-FEB-17', '21-APR-17', 2, 13, 101);
INSERT INTO BOOK_LENDING VALUES ('15-MAR-17', '15-JUL-17', 4, 11, 101);
INSERT INTO BOOK_LENDING VALUES ('12-APR-17', '12-MAY-17', 1, 11, 104);


-- 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-2017' AND '01-JUL-2017'
GROUP BY CARD_NO
HAVING COUNT(*) > 3;

-- 3. Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation.
DELETE FROM BOOK
WHERE BOOK_ID = 3;

-- 4.Partition the BOOK table based on year of publication. Demonstrate its working with a
simple query.
CREATE VIEW V_PUBLICATION AS
SELECT PUB_YEAR
FROM BOOK;

-- 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
JOIN BOOK_COPIES C ON B.BOOK_ID = C.BOOK_ID;






2ND TEXT --





CREATE TABLE SALESMAN (
    SALESMAN_ID NUMBER(4) PRIMARY KEY,
    NAME VARCHAR(20),
    CITY VARCHAR(20),
    COMMISSION VARCHAR(20)
);

CREATE TABLE CUSTOMER (
    CUSTOMER_ID NUMBER(4) PRIMARY KEY,
    CUST_NAME VARCHAR(20),
    CITY VARCHAR(20),
    GRADE NUMBER(3),
    SALESMAN_ID NUMBER(4) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE SET NULL
);

CREATE TABLE ORDERS (
    ORD_NO NUMBER(5) PRIMARY KEY,
    PURCHASE_AMT NUMBER(10, 2),
    ORD_DATE DATE,
    CUSTOMER_ID NUMBER(4) REFERENCES CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE,
    SALESMAN_ID NUMBER(4) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE CASCADE
);

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', 'HYDERABAD', '15%');

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

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


1. Count the customers with grades above Bangalore’s average.
SELECT GRADE, COUNT (DISTINCT CUSTOMER_ID)
FROM CUSTOMER1
GROUP BY GRADE
HAVING GRADE > (SELECT AVG(GRADE)
FROM CUSTOMER1
WHERE CITY='BANGALORE');
18CSL58:DBMS Lab Manual 2022-23

2. Find the name and numbers of all salesmen who had more than one customer.
SELECT SALESMAN_ID, NAME
FROM SALESMAN A
WHERE 1 < (SELECT COUNT (*)
FROM CUSTOMER1
WHERE SALESMAN_ID=A.SALESMAN_ID);

3. List all salesmen and indicate those who have and don’t have customers in their
cities (Use UNION operation.)
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;

4. Create a view that finds the salesman who has the customer with the highest order
of a day.
CREATE VIEW ELITSALESMANAS
SELECT B.ORD_DATE, A.SALESMAN_ID, A.NAME
FROM SALESMAN A, ORDERS B
18CSL58:DBMS Lab Manual 2022-23
RNSIT, Bangalore Page 31
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);


5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders
must also be deleted.
Use ON DELETE CASCADE at the end of foreign key definitions while creating child table
orders and then execute the following:
Use ON DELETE SET NULL at the end of foreign key definitions while creating child table
customers and then executes the following:

DELETE FROM SALESMAN
WHERE SALESMAN_ID=1000;





3RD TEXT---





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


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 ‘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 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
18CSL58:DBMS Lab Manual 2022-23
RNSIT, Bangalore Page 38
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;

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’));




4TH TEXT---



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


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

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 STUDENT VALUES ('1RN14CS010','ABHAY','BENGALURU', 9900211201,'M');
INSERT INTO STUDENT VALUES ('1RN14CS032','BHASKAR','BENGALURU', 9923211099,'M');
INSERT INTO STUDENT VALUES ('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');
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');
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


1.List all the student details studying in fourth semester ‘C’ section.
SELECT S.*
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 SS.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';


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

-- Procedure definition
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;
    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;
    UPDATE IAMARKS SET FINALIA = C_AV WHERE CURRENT OF C_IAMARKS;
  END LOOP;
  CLOSE C_IAMARKS;
END;
/


SELECT * FROM IAMARKS;

Below SQL code is to invoke the PL/SQL stored procedure from the command line:

BEGIN
AVGMARKS; --IT INVOKES THE STORED PROCEDURE
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;




5TH TEXT---



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));
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,
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));
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);
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’);
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
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);
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 DISTINCT P.PNO
FROM PROJECT P, DEPARTMENT D, EMPLOYEE E
WHERE E.DNO=D.DNO
AND D.MGRSSN=E.SSN
AND E.LNAME=’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.LNAME=’SCOTT’);
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’;
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’;
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E
WHERE NOT EXISTS((SELECT PNO
FROM PROJECT
WHERE DNO=’5’)
 EXCEPT(SELECT PNO
 FROM WORKS_ON
 WHERE E.SSN=SSN));
Or
SELECT LNAME, FNAME, DNO
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
 FROM WORKS_ON B
 WHERE (B.PNO IN(SELECT P.PNO
 FROM PROJECT P
 WHERE P.DNO=’5’)
 AND
 NOT EXISTS (SELECT *
FROM WORKS_ON C
WHERE C.SSN=SSN
AND C.PNO=B.PNO)));
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)