ASSESSMENT2, Sql Server

ASSESSMENT2

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'
 CREATE TABLE STUDENT
(SNUM INT PRIMARY KEY,
SNAME VARCHAR(10), 
MAJOR VARCHAR(10), 
LEVEL VARCHAR(10),
AGE INTEGER);

INSERT INTO STUDENT VALUES(101,'ABHI','CSE','JR',19);
INSERT INTO STUDENT VALUES(102,'ANIL','ISE','JR',18);
INSERT INTO STUDENT VALUES(103,'BHAVYA','ISE','SR',20);
INSERT INTO STUDENT VALUES(104,'CHETHAN','CSE','JR',19);
 INSERT INTO STUDENT VALUES(105,'SURESH','MECH','JR',18);
INSERT INTO STUDENT VALUES(106,'JAYANTH','CSE','SR',20);
SELECT * FROM STUDENT;

CREATE TABLE FACULTY 
(FID INTEGER PRIMARY KEY, 
FNAME VARCHAR(10), 
DEPTID INTEGER);

INSERT INTO FACULTY VALUES(1001,'JAMES',41); INSERT INTO FACULTY VALUES(1002,'SUNIL',42);
INSERT INTO FACULTY VALUES(1003,'SUKRUTH',43); INSERT INTO FACULTY VALUES(1004,'SUKRUTH',43);
 INSERT INTO FACULTY VALUES(1005,'NARASIMHA',44);
INSERT INTO FACULTY VALUES(1006,'AFROZ',41);
INSERT INTO FACULTY VALUES(1007,'JOHN',42);
INSERT INTO FACULTY VALUES(1008,'AHMED',45); INSERT INTO FACULTY VALUES(1009,'SUNITHA',46); INSERT INTO FACULTY VALUES(10010,'SRIVINAY',42);

SELECT * FROM FACULTY;

CREATE TABLE CLASS 
(CNAME VARCHAR(10) PRIMARY KEY, 
MEETS_AT VARCHAR(10), ROOM VARCHAR(5), 
FID INTEGER, FOREIGN KEY (FID) REFERENCES FACULTY (FID) ON DELETE CASCADE);

INSERT INTO CLASS VALUES('4CSE1','9:00','NG01',1001);
INSERT INTO CLASS VALUES('4CSE2','10:00','NG02',1001);
INSERT INTO CLASS VALUES('4CSE3','11:15','NG03',1002);
 INSERT INTO CLASS VALUES('4CSE4','12:10','NG04',1002);
 INSERT INTO CLASS VALUES('4CSE5','1:05','NG05',1003);
 INSERT INTO CLASS VALUES('4CSE6','1:05','NG06',1004);
 INSERT INTO CLASS VALUES('4CSE7','2:05','KG01',1005);
 INSERT INTO CLASS VALUES('4CSE8','3:00','KG02',1006);
 INSERT INTO CLASS VALUES('4CSE9','9:00','KG03',1007);
 INSERT INTO CLASS VALUES('4CSE10','10:00','KG04',1008);
INSERT INTO CLASS VALUES('4CSE11','10:00','KF04',1009);
 INSERT INTO CLASS VALUES('4CSE12','10:00','KF03',1009);

SELECT * FROM CLASS;

CREATE TABLE ENROLLED 
(SNUM INTEGER, CNAME VARCHAR(10), 
PRIMARY KEY(SNUM, CNAME), 
FOREIGN KEY(SNUM) REFERENCES STUDENT (SNUM) ON DELETE CASCADE, FOREIGN KEY(CNAME) REFERENCES CLASS (CNAME) ON DELETE CASCADE);

INSERT INTO ENROLLED VALUES(101,'4CSE1');
INSERT INTO ENROLLED VALUES(102,'4CSE2');
INSERT INTO ENROLLED VALUES(103,'4CSE3');
INSERT INTO ENROLLED VALUES(101,'4CSE4');
INSERT INTO ENROLLED VALUES(104,'4CSE5');
INSERT INTO ENROLLED VALUES(105,'4CSE5');
INSERT INTO ENROLLED VALUES(106,'4CSE5');
INSERT INTO ENROLLED VALUES(101,'4CSE6');
INSERT INTO ENROLLED VALUES(102,'4CSE7');
INSERT INTO ENROLLED VALUES(103,'4CSE8');
INSERT INTO ENROLLED VALUES(104,'4CSE9');
INSERT INTO ENROLLED VALUES(105,'4CSE10');
INSERT INTO ENROLLED VALUES(106,'4CSE11');
INSERT INTO ENROLLED VALUES(106,'4CSE12');

SELECT * FROM ENROLLED;

SELECT DISTINCT S.SNAME
FROM STUDENT S,CLASS C,ENROLLED E,FACULTY 
F WHERE S.SNUM=E.SNUM AND E.CNAME=C.CNAME AND F.FID=C.FID AND F.FNAME='NARASIMHA' AND S.LEVEL='JR';

SELECT C.CNAME FROM CLASS C
WHERE C.ROOM='KG04' OR C.CNAME IN
  (SELECT E.CNAME FROM ENROLLED E GROUP BY E.CNAME HAVING COUNT(*)>=5);

SELECT DISTINCT S.* FROM STUDENT S WHERE S.SNUM IN (SELECT E1.SNUM
FROM ENROLLED E1, ENROLLED E2, CLASS C1, CLASS C2 WHERE E1.SNUM = E2.SNUM AND E1.CNAME <> E2.CNAME  AND E1.CNAME = C1.CNAME AND E2.CNAME = C2.CNAME
   AND C1.MEETS_AT = C2.MEETS_AT);

SELECT DISTINCT S.sname
    FROM Student S
    WHERE S.snum IN (SELECT E.snum
    FROM Enrolled E
    GROUP BY E.snum);

SELECT DISTINCT F.FNAME
    FROM FACULTY F
    WHERE F.FID IN
    (SELECT C.FID FROM CLASS C, ENROLLED E
    WHERE C.CNAME=E.CNAME
    GROUP BY C.FID
    HAVING COUNT(*)<5);


run \| edit \| history \| help	0