Notes

CREATE TABLE Student
(
EnrollNo INT PRIMARY KEY,
Name VARCHAR(25) CHECK(ASCII(LEFT(Name,1))BETWEEN 60 AND 90),
Phone INT,
Gender CHAR(1),
DOB DATE
);

CREATE TABLE Branch
(
BranchName VARCHAR(25),
BranchCode VARCHAR(5) PRIMARY KEY,
HODName VARCHAR(25)
);

CREATE TABLE Course
(
SubjectCode VARCHAR(5) PRIMARY KEY,
SubjectName VARCHAR(25) NOT NULL,
Details VARCHAR(50),
BranchCode VARCHAR(5),
FOREIGN KEY (BranchCode) REFERENCES Branch(BranchCode)
);

CREATE TABLE Result
(
EnrollNo INT,
SubjectCode VARCHAR(5),
Marks INT,
Result CHAR(1) CHECK(Result IN('A','B','C','D','P','F')),
Rank INT CHECK(Rank IN(0,1,2,3)),
FOREIGN KEY(EnrollNo) REFERENCES Student(EnrollNo),
FOREIGN KEY(SubjectCode) REFERENCES Course(SubjectCode)
);

INSERT INTO Student VALUES
(1,'Rahul Dravid',8975150,'M','1994-02-22'),
(2,'Novak Djokovic',4478150,'M','1992-10-02'),
(3,'Michael Schumaker',6410210,'M','1993-07-01'),
(4,'Joseph John',9844550,'F','1990-09-15');

INSERT INTO Branch VALUES
('Computer Science','BRCSE','Alpha Singh'),
('Electronics and Communication','BRECE','Beta Kumari'),
('Civil','BRCVL','Gamma Kappa');

INSERT INTO Course VALUES
('10001','Network Security','Secure your network','BRCSE'),
('10002','Optical Communication','Learn Light','BRECE'),
('10003','Strength Of Materiasls','Smack Down for Materials','BRCVL');
('10004','Softwar Engineering','Design Your Software','BRCSE'),
('10005','VLSI','Integrate Your Life','BRECE');

INSERT INTO Result VALUES
(1,'10001',99,'A',0),
(2,'10002',71,'C',2),
(3,'10003',52,'D',3),
(4,'10001',93,'A',0),
(1,'10004',91,'A',0),
(4,'10005',33,'F',3);

******************************************** QUERIES **************************************************

1.
SELECT * FROM Student S WHERE
S.EnrollNo IN( SELECT R.EnrollNo FROM Result R
WHERE R.SubjectCode IN (SELECT C.SubjectCode FROM Course C
WHERE C.BranchCode = (SELECT B.BranchCode FROM Branch B
WHERE B.BranchName = 'Computer Science'))
GROUP BY R.EnrollNo HAVING SUM(CASE WHEN R.Result='A' THEN 1 ELSE 0 END) = SELECT COUNT(*) FROM Course C
WHERE C.BranchCode = (SELECT B.BranchCode FROM Branch B
WHERE B.BranchName='Computer Science')));