Lab Programs

CREATE TABLE PUBLISHER (NAME VARCHAR(18) PRIMARY KEY, ADDRESS VARCHAR(10), PHONE VARCHAR(10));
CREATE TABLE BOOK (BOOK_ID INTEGER PRIMARY KEY, TITLE VARCHAR(20), PUBLISHER_NAME VARCHAR(20) REFERENCES PUBLISHER(NAME), PUB_YEAR NUMERIC);
CREATE TABLE BOOK_AUTHORS(BOOK_ID INTEGER , AUTHOR_NAME VARCHAR(20), PRIMARY KEY(BOOK_ID), FOREIGN KEY (BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE);
CREATE TABLE LIBRARY_BRANCH(BRANCH_ID INTEGER PRIMARY KEY, BRANCH_NAME VARCHAR(18), ADDRESS VARCHAR(15));
CREATE TABLE BOOK_COPIES(BOOK_ID INTEGER, BRANCH_ID INTEGER, NO_OF_COPIES INTEGER, 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 BOOK_LENDING(BOOK_ID INTEGER, BRANCH_ID INTEGER, CARD_NO INTEGER, 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);

INSERT INTO PUBLISHER VALUES( "PEARSON", "BANGALORE", "987546230"); 
INSERT INTO PUBLISHER VALUES( "MCGRAW", "NEWDELHI", "7865456787");
INSERT INTO PUBLISHER VALUES( "SAPNA", "BANGALORE", "8475647365");

INSERT INTO BOOK VALUES( 1111, "SE", "PEARSON",2005); 
INSERT INTO BOOK VALUES( 2222, "DBMS", "MCGRAW", 2004);
INSERT INTO BOOK VALUES( 3333, "ANOTOMY", "PEARSON", 2010);
INSERT INTO BOOK VALUES( 4444, "ENCYCLOPEDIA","SAPNA",2010);

INSERT INTO BOOK_AUTHORS VALUES(1111, "SOMMERVILLE");
INSERT INTO BOOK_AUTHORS VALUES(2222, "NAVATHE");
INSERT INTO BOOK_AUTHORS VALUES(3333, "HENRY GRAY");
INSERT INTO BOOK_AUTHORS VALUES(4444, "THOMAS");

INSERT INTO LIBRARY_BRANCH VALUES(11, "CENTRAL TECHNICAL", "MG ROAD");
INSERT INTO LIBRARY_BRANCH VALUES(22, "MEDICAL", "BH ROAD");
INSERT INTO LIBRARY_BRANCH VALUES(33, "CHILDREN", "SS PURAM");
INSERT INTO LIBRARY_BRANCH VALUES(44, "SECRETARIAT", "SIRAGATE");
INSERT INTO LIBRARY_BRANCH VALUES(55, "GENERAL", "JAYANAGAR");

INSERT INTO BOOK_COPIES VALUES(1111,11,5);
INSERT INTO BOOK_COPIES VALUES(3333,22,6);
INSERT INTO BOOK_COPIES VALUES(4444,33,10);
INSERT INTO BOOK_COPIES VALUES(2222,11,12);
INSERT INTO BOOK_COPIES VALUES(4444,55,3);

INSERT INTO BOOK_LENDING VALUES(2222,11,1,"2017-01-10","2017-08-20");
INSERT INTO BOOK_LENDING VALUES(3333,22,2,"2017-07-09","2017-08-12");
INSERT INTO BOOK_LENDING VALUES(4444,55,1,"2017-04-11","2017-08-09");
INSERT INTO BOOK_LENDING VALUES(2222,11,5,"2017-08-09","2017-08-19");
INSERT INTO BOOK_LENDING VALUES(4444,33,1,"2017-06-10","2017-08-15");
INSERT INTO BOOK_LENDING VALUES(1111,11,1,"2017-05-12","2017-06-10");
INSERT INTO BOOK_LENDING VALUES(3333,22,1,"2017-01-10","2017-07-15");

SELECT LB.BRANCH_NAME, B.BOOK_ID, TITLE, PUBLISHER_NAME,AUTHOR_NAME,
NO_OF_COPIES FROM BOOK B, BOOK_AUTHORS BA, BOOK_COPIES BC, LIBRARY_BRANCH LB
WHERE B.BOOK_ID = BA.BOOK_ID AND BA.BOOK_ID=BC.BOOK_ID AND BC.BRANCH_ID =
LB.BRANCH_ID;
                  
SELECT CARD_NO, count(*)
FROM BOOK_LENDING
WHERE DATE_OUT BETWEEN '2017-01-01' AND '2017-06-30'
GROUP BY CARD_NO
HAVING COUNT(*) > 3;
SELECT *FROM BOOK_LENDING;

DELETE FROM BOOK
WHERE BOOK_ID = '3333';

SELECT *, count(*) FROM BOOK
GROUP BY pub_year;

create view available_books As select b.book_id, b.title, lb.branch_name,bc.no_of_copies from book b, book_copies bc, library_branch lb where b.BOOK_ID=bc.BOOK_ID and bc.BRANCH_ID= lb.BRANCH_ID;
select * from available_books;

PROGRAM 1

CREATE TABLE SALESMAN(
SALESMAN_ID INTEGER PRIMARY KEY,
NAME VARCHAR(20),
CITY VARCHAR(20),
COMMISSION VARCHAR(20));
CREATE TABLE CUSTOMER(
CUSTOMER_ID INTEGER PRIMARY KEY,
CUST_NAME VARCHAR(20),
CITY VARCHAR(20),
GRADE INTEGER,
SALESMAN_ID INTEGER,
FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE SET NULL);
CREATE TABLE ORDERS(
ORDER_NO INTEGER PRIMARY KEY,
PURCHASE_AMOUNT DECIMAL(10,2),
ORDER_DATE DATE,
CUSTOMER_ID INTEGER,
SALESMAN_ID INTEGER,
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID)ON DELETE CASCADE,
FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE CASCADE);

INSERT INTO SALESMAN VALUES(1000,'RAHUL','BANGALORE','20%');
INSERT INTO SALESMAN VALUES(2000,'ANKITA','BANGALORE','25%');
INSERT INTO SALESMAN VALUES(3000,'SHARMA','MYSORE','30%');
INSERT INTO SALESMAN VALUES(4000,'ANJALI','DELHI','15%');
INSERT INTO SALESMAN VALUES(5000,'RAJ','HYDERABAD','15%');

SELECT * FROM SALESMAN;

INSERT INTO CUSTOMER VALUES(1,'ADYA','BANGALORE',100,1000);
INSERT INTO CUSTOMER VALUES(2,'BANU','MANGALORE',300,1000);
INSERT INTO CUSTOMER VALUES(3,'CHETHAN','CHENNAI',400,2000);
INSERT INTO CUSTOMER VALUES(4,'DANISH','BANGALORE',200,2000);
INSERT INTO CUSTOMER VALUES(5,'ESHA','BANGALORE',400,3000);

SELECT * FROM CUSTOMER;

INSERT INTO ORDERS VALUES(201,5000,'2020-06-02',1,1000);
INSERT INTO ORDERS VALUES(202,450,'2020-04-09',1,2000);
INSERT INTO ORDERS VALUES(203,1000,'2020-03-15',3,2000);
INSERT INTO ORDERS VALUES(204,3500,'2020-07-09',4,3000);
INSERT INTO ORDERS VALUES(205,550,'2020-05-05',2,2000);

SELECT * FROM ORDERS;

SELECT SALESMAN_ID, NAME
FROM SALESMAN S
WHERE (SELECT COUNT(*)
FROM CUSTOMER C
WHERE C.SALESMAN_ID=S.SALESMAN_ID) > 1;

SELECT S.SALESMAN_ID, S.NAME, C.CUST_NAME, S.COMMISSION
FROM SALESMAN S, CUSTOMER C
WHERE S.CITY=C.CITY
UNION
SELECT S.SALESMAN_ID,S.NAME,'NO MATCH',S.COMMISSION
FROM SALESMAN S
WHERE CITY NOT IN 
(SELECT CITY
FROM CUSTOMER)
ORDER BY 1 ASC;

CREATE VIEW V_SALESMAN AS
SELECT O.ORDER_DATE, S.SALESMAN_ID, S.NAME
FROM SALESMAN S,ORDERS O
WHERE S.SALESMAN_ID = O.SALESMAN_ID
AND O.PURCHASE_AMOUNT= (SELECT MAX(PURCHASE_AMOUNT)
FROM ORDERS C
WHERE C.ORDER_DATE=O.ORDER_DATE);

SELECT * FROM V_SALESMAN;

DELETE FROM SALESMAN
WHERE SALESMAN_ID=1000;

SELECT * FROM SALESMAN;

SELECT * FROM ORDERS;

PROGRAM 2

CREATE TABLE ACTOR (ACT_ID INT(5) PRIMARY KEY, ACT_NAME VARCHAR(15) NOT NULL, ACT_GENDER VARCHAR(2) not null);
CREATE TABLE DIRECTOR (DIR_ID int PRIMARY KEY, DIR_NAME varchar(18) NOT NULL, DIR_PHONE VARCHAR(10) NOT NULL);
create table MOVIES (MOV_ID INT primary key, MOV_TITLE VARCHAR(10) NOT null, MOV_YEAR INT NOT NULL ,MOV_LANG varchar(10) NOT NULL ,DIR_ID INT NOT NULL, FOREIGN KEY(DIR_ID) REFERENCES DIRECTOR(DIR_ID) ON DELETE CASCADE);
create table MOVIE_CAST( ACT_ID INT NOT NULL, MOV_ID INT NOT NULL, ROLE VARCHAR (10) NOT NULL, primary key(ACT_ID,MOV_ID),foreign key(ACT_ID) references ACTOR (ACT_ID) ON delete cascade, foreign key(MOV_ID) references MOVIES(MOV_ID) ON delete cascade);
CREATE TABLE RATING( MOV_ID INT NOT NULL,REV_STARS INT NOT NULL,primary key(MOV_ID), foreign key(MOV_ID) references MOVIES (MOV_ID) ON delete cascade);
INSERT into ACTOR values(111,'DEEPA SANNIDHI','F');
INSERT into ACTOR values(222,'SUDEEP','M');
INSERT into ACTOR values(333,'PUNETH','M');
INSERT into ACTOR values(444,'DIGHANTH','M');
INSERT into ACTOR values(555,'ANGELA','F');
insert into DIRECTOR values(101,'HITCHCOOK','112267809');
insert into DIRECTOR values(102,'RAJMAOLI','152358709');
insert into DIRECTOR values(103,'YOGA RAJ','272337808');
insert into DIRECTOR values(104,'STEVEN SPIELBERG','363445678');
insert into DIRECTOR values(105,'PAVAN KUMAR','385456809');
insert INTO MOVIES values(1111,'LAST WORD',2009,'ENGLISH',104);
insert INTO MOVIES values(2222,'EEGA',2010,'TELUGU',102);
insert INTO MOVIES values(3333,'PARAMATHMA',2006,'KANNADA',103);
insert INTO MOVIES values(4444,'MALE' ,2005,'KANNADA',103);
insert INTO MOVIES values(5555,'MANASARE',2010,'KANNADA',103);
insert INTO MOVIES values(6666,'REARWINDOW',1954,'ENGLISH',101);
insert INTO MOVIES values(7777,'NOTORIOUS',1946,'ENGLISH',101);
insert INTO MOVIE_CAST VALUES (111,4444,'HEROIN');
insert INTO MOVIE_CAST VALUES (222,2222,'VILLAN');
insert INTO MOVIE_CAST VALUES (333,4444,'HERO');
insert INTO MOVIE_CAST VALUES (444,3333,'GEUST');
insert INTO MOVIE_CAST VALUES (444,5555,'HERO');
insert INTO MOVIE_CAST VALUES (555,7777,'MOTHER');
insert INTO RATING values(1111,3);
insert INTO RATING values(2222,4);

insert INTO RATING values(4444,5);
insert INTO RATING values(5555,4);
select*from RATING;
          
SELECT MOV_TITLE FROM MOVIES M,director D WHERE D.DIR_ID = M.DIR_ID AND DIR_NAME = 'HITCHCOCK';

SELECT MOV_TITLE FROM MOVIES M,MOVIE_CAST MC WHERE M.MOV_ID = MC.MOV_ID AND MC.ACT_ID IN (select ACT_ID FROM movie_cast group by ACT_ID having count(MOV_ID)>=2);

select ACT_NAME FROM ACTOR A JOIN MOVIE_CAST C ON A.ACT_ID JOIN MOVIES M ON C.MOV_ID= M.MOV_ID WHERE M.MOV_YEAR > 2000 AND ACT_NAME IN (SELECT ACT_NAME 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 > 2015);

SELECT MOV_TITLE, REV_STARS FROM MOVIES M,RATING R WHERE M.MOV_ID=R.MOV_ID AND REV_STARS>=1 order by MOV_TITLE;

UPDATE RATING SET REV_STARS=5 WHERE MOV_ID IN(SELECT MOV_ID FROM MOVIES M, DIRECTOR D WHERE M.DIR_ID=D.DIR_ID AND DIR_NAME='STEVEN SPIELBERG');

PROGRAM 3

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 TINYINT,SEC CHAR(1));
CREATE TABLE CLASS(USN VARCHAR(10),SSID VARCHAR(5),PRIMARY KEY(USN,SSID),FOREIGN KEY(USN) REFERENCES STUDENT(USN) ON DELETE CASCADE,FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID) ON DELETE CASCADE);
CREATE TABLE SUBJECT(SUBCODE VARCHAR(8) PRIMARY KEY,TITLE VARCHAR(20),SEM TINYINT,CREDITS TINYINT);
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) ON DELETE CASCADE, FOREIGN KEY (SUBCODE) REFERENCES SUBJECT(SUBCODE) ON DELETE CASCADE,FOREIGN KEY (SSID) REFERENCES SEMSEC(SSID) ON DELETE CASCADE);

INSERT INTO STUDENT VALUES('1CG15CS001','ABHI','TUMKUR','9875698410','M');
INSERT INTO STUDENT VALUES('1CG15CS002','AMULYA','GUBBI',8896557410,'F');
INSERT INTO STUDENT VALUES('1CG15CS063','CHETHAN','NITTUR',7894759522,'M');
INSERT INTO STUDENT VALUES('1CG15CS055','RAGHAVI','SSPURAM','9485675521','M');
INSERT INTO STUDENT VALUES('1CG15CS065','SANJAY','BANGLORE','9538444404','M');

INSERT INTO SEMSEC VALUES('5A',5,'A');
INSERT INTO SEMSEC VALUES('3B',3,'B');
INSERT INTO SEMSEC VALUES('7A',7,'A');
INSERT INTO SEMSEC VALUES('2C',2,'C');
INSERT INTO SEMSEC VALUES('4B',4,'B');
INSERT INTO SEMSEC VALUES('4C',4,'C');

INSERT INTO CLASS VALUES('1CG15CS001','5A');
INSERT INTO CLASS VALUES('1CG15CS002','5A');
INSERT INTO CLASS VALUES('1CG15CS063','3B');
INSERT INTO CLASS VALUES('1CG15CS055','7A');
INSERT INTO CLASS VALUES('1CG15CS065','3B');
INSERT INTO CLASS VALUES('1CG15CS002','4C');
SELECT *FROM CLASS;

INSERT INTO SUBJECT VALUES('10CS81','ACA',8,4);
INSERT INTO SUBJECT VALUES('10CS53','DBMS',5,4);
INSERT INTO SUBJECT VALUES('10CS33','DS',3,4);
INSERT INTO SUBJECT VALUES('10CS34','CO',3,4);
INSERT INTO SUBJECT VALUES('10CSL58','DBA',5,2);
INSERT INTO SUBJECT VALUES('10CS71','OOMD',7,4);

SELECT *FROM SUBJECT;

INSERT INTO IAMARKS VALUES('1CG15CS001','10CS81','5A',17,18,19,NULL);
INSERT INTO IAMARKS VALUES('1CG15CS063','10CS33','3B',10,11,19,NULL);
INSERT INTO IAMARKS VALUES('1CG15CS055','10CS34','7A',19,20,21,NULL);
INSERT INTO IAMARKS VALUES('1CG15CS001','10CS71','4C',15,16,14NULL);
INSERT INTO IAMARKS VALUES('1CG15CS002','10CS81','4C',17,11,12,NULL);
SELECT *FROM IAMARKS;
                        
// queries
   1   select s.usn,sname, address, phone,gender from students, class c, semsec ss where sem=4 and sec='c' and ss.ssid=c.ssid and c.usn=s.usn;

2  SELECT SEM,SEC, GENDER,COUNT(*) FROM STUDENTS, SEMSEC SS, CLASS C WHERE S.USN=C.USN AND C.SSID=SS.SSID GROUP BY SEM,SEC,GENDER ORDER BY SEM;

3   CREATE VIEW TEST1 AS
SELECT SUBCODE, TEST1
FROM IAMARKS
WHERE USN='1cg15ee065';
SELECT * FROM TEST1;

4   UPDATE IAMARKS SET FINALIA (TEST1+TEST2+TEST3-LEAST(TEST1,TEST2TEST3))/2:

5   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=7;

PROGRAM 4

CREATE TABLE DEPARTMENT(DNO tinyint PRIMARY KEY,DNAME VARCHAR(15) NOT NULL, MGRSSN CHAR(10),MGRSTARTDATE DATE);

CREATE TABLE EMPLOYEE(SSN CHAR(10) PRIMARY KEY, NAME VARCHAR(18) NOT NULL,ADDRESS VARCHAR(18),SEX VARCHAR(3),SALARY REAL,SUPER_SSN CHAR(10),DNO tinyint,FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));

CREATE TABLE DLOCATION (DLOC VARCHAR(20),DNO tinyint,PRIMARY KEY(DNO,DLOC),foreign key(DNO)REFERENCES DEPARTMENT(DNO));

ALTER TABLE DEPARTMENT ADD CONSTRAINT FOREIGN KEY(MGRSSN)REFERENCES EMPLOYEE(SSN);

CREATE TABLE PROJECT(PNO INTEGER PRIMARY KEY,PNAME VARCHAR (20),PLOCATION VARCHAR(20),DNO tinyint,foreign key(DNO)REFERENCES DEPARTMENT(DNO));

CREATE TABLE WORKS_ON(SSN CHAR(10),PNO INTEGER,HOURS tinyint,foreign key(SSN)REFERENCES EMPLOYEE(SSN),foreign key(PNO)REFERENCES PROJECT(PNO),PRIMARY KEY(SSN,PNO));     
 
 SET FOREIGN_KEY_CHECKS=0;
 
INSERT INTO EMPLOYEE VALUES('111111','RAJ','BENGALURU','M',700000,null,1);
INSERT INTO EMPLOYEE VALUES('222222','RASHMI','MYSORE','F',400000,'111111',2);
INSERT INTO EMPLOYEE VALUES('333333','RAGAVI','TUMKUR','F',800000,null,3);
INSERT INTO EMPLOYEE VALUES('444444','RAJESH','TUMKUR','M',650000,'333333',3);
INSERT INTO EMPLOYEE VALUES('555555','RAVEESH','BENGALURU','M',500000,'333333',3);
INSERT INTO EMPLOYEE VALUES('666666','SCOTT','ENGLAND','M',700000,'444444',5);
INSERT INTO EMPLOYEE VALUES('777777','NIGANTH','GUBBI','M',200000,'222222',2);
INSERT INTO EMPLOYEE VALUES('888888','RAMYA','GUBBI','F',400000,'222222',3);
INSERT INTO EMPLOYEE VALUES('999999','VIDYA','TUMKUR','F',650000,'333333',3);
INSERT INTO EMPLOYEE VALUES('100000','GEETHA','TUMKUR','F',800000,'',3);
SELECT * FROM EMPLOYEE;

INSERT INTO DEPARTMENT VALUES(1,'RESEARCH','111111','2012-08-10');
INSERT INTO DEPARTMENT VALUES(2,'ACCOUNTS','222222','2010-08-10');
INSERT INTO DEPARTMENT VALUES(3,'AI','333333','2015-04-12');
INSERT INTO DEPARTMENT VALUES(4,'NETWORK','111111','2018-05-14');
INSERT INTO DEPARTMENT VALUES(5,'BIGDATA','666666','2021-01-10');
SELECT * FROM DEPARTMENT;

INSERT INTO PROJECT VALUES(111,'IOT','GUBBI',3);
INSERT INTO PROJECT VALUES(222,'TEXTSPEECH','GUBBI',3);
INSERT INTO PROJECT VALUES(333,'IPSECURITY','DELHI',4);
INSERT INTO PROJECT VALUES(444,'TRAFICANAL','BENGALURU',5);
INSERT INTO PROJECT VALUES(555,'CLOUDSEC','DELHI',1);
SELECT * FROM PROJECT;

INSERT INTO DLOCATION VALUES('MYSORE',1);
INSERT INTO DLOCATION VALUES('TUMKUR',1);
INSERT INTO DLOCATION VALUES('BENGALURU',2);
INSERT INTO DLOCATION VALUES('GUBBI',3);
INSERT INTO DLOCATION VALUES('DELHI',4);
INSERT INTO DLOCATION VALUES('BENGALURU',5);
SELECT * FROM DLOCATION;

INSERT INTO WORKS_ON VALUES('666666',333,4);
INSERT INTO WORKS_ON VALUES('666666',111,2);
INSERT INTO WORKS_ON VALUES('111111',222,3);
INSERT INTO WORKS_ON VALUES('555555',222,2);
INSERT INTO WORKS_ON VALUES('333333',111,4);
INSERT INTO WORKS_ON VALUES('444444',111,6);
INSERT INTO WORKS_ON VALUES('222222',111,2);
SELECT * FROM WORKS_ON;

(SELECT DISTINCT PNO FROM PROJECT P,DEPARTMENT D,EMPLOYEE E WHERE P.DNO=D.DNO AND SSN=MGRSSN AND NAME='SCOTT') UNION (SELECT DISTINCT P.PNO FROM PROJECT P,WORKS_ON W,EMPLOYEE E WHERE P.PNO=W.PNO AND W.SSN=E.SSN AND NAME='SCOTT');

SELECT E.SSN, NAME,1.1*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(SALARY),MAX(SALARY),MIN(SALARY),AVG(SALARY) FROM EMPLOYEE E,DEPARTMENT D WHERE DNAME='ACCOUNTS' AND D.DNO=E.DNO;

SELECT NAME AS EMPLOYEE_OF_DEPT5 FROM EMPLOYEE WHERE DNO=5 AND NOT EXISTS (SELECT DNO FROM PROJECT WHERE DNO=NULL);

SELECT DNO,COUNT(SSN) FROM EMPLOYEE WHERE SALARY>600000 AND DNO IN (SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING COUNT(SSN)>5) GROUP BY DNO;

PROGRAM 5

Would you like to upload Programs?