MS SQL Server Database Student Table Query

/************************************************
 1、 establishing a database 
************************************************/
USE master;
GO
CREATE DATABASE [StuData]
ON PRIMARY
(
NAME = N'StuData',
FILENAME = N'D:SQL workStuData.mdf',
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1MB
)
LOG ON 
(
NAME = Studlog,
FILENAME = 'D:SQL workStulog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20
);
GO
/************************************************
 2、 creating tables 
************************************************/
USE StuData;
GO
 --student table 
CREATE TABLE S
(
	Sno CHAR(9) PRIMARY KEY,
	Sname CHAR(20) NOT NULL,
	Ssex CHAR(2) CHECK (Ssex IN ('male','female')),
	Sage SMALLINT,
	Sdept CHAR(20)
);
 --course schedule 
CREATE TABLE C
(
	Cno CHAR(4) PRIMARY KEY,
	Cname CHAR(40) NOT NULL,
	Cpno CHAR(4),
	Ccredit SMALLINT,
	FOREIGN KEY (Cpno) REFERENCES C(Cno)
);
GO
 --course selection schedule 
CREATE TABLE SC
(
	Sno CHAR(9),
	Cno CHAR(4),
	Grade SMALLINT CHECK (Grade >= 0 AND Grade <= 100),
	PRIMARY KEY (Sno, Cno),
	FOREIGN KEY (Sno) REFERENCES S(Sno),
	FOREIGN KEY (Cno) REFERENCES C(Cno)
);
GO
 --join column 
--ALTER TABLE C ADD Cpno CHAR(4);
/************************************************
 3、 data updates 
************************************************/
 --modify S
INSERT INTO S(Sno, Sname, Ssex, Sage, Sdept) VALUES ('95001',	'li yong',	'male',	20,	'CS');
INSERT INTO S(Sno, Sname, Ssex, Sage, Sdept) VALUES ('95002',	'liu chen',	'female',	19,	'IS');
INSERT INTO S(Sno, Sname, Ssex, Sage, Sdept) VALUES ('95003',	'wang min',	'female',	18,	'MA');
INSERT INTO S(Sno, Sname, Ssex, Sage, Sdept) VALUES ('95004',	'tension',	'male',	19,	'IS');
 --modify C
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C2',	'higher mathematics',	2,	null);
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C6',	'data processing',	2,	null);
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C7',	'PASCAL language',4,	'c6');
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C4',	'operating system',	3,	'c6');
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C5',	'data structure',	4,	'c7');
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C1',	'database',	4,	'c5');
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C3',	'information system',	4,	'c1');
 --modify data 
/*
UPDATE C 
SET Cpno='C5'
WHERE Cno = 'C1';
*/
 --modify SC
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001',	'C1',	'92');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001',	'C2',	'65');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001',	'C3',	'88');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001',	'C4',	'88');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001',	'C5',	'88');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001',	'C6',	'88');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001',	'C7',	'88');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95002',	'C2',	'90');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95002',	'C5',	'73');
/************************************************
 4、 query 
************************************************/
 --list all tables (test) 
SELECT *
FROM S;
SELECT *
FROM C;
SELECT *
FROM SC;
 --search for the names, student ids, and genders of all students surnamed liu 
SELECT  Sname , Sno , Ssex
FROM  S
WHERE  Sname  LIKE  'liu %' ;
 --query elective courses c student id and name of all students with a score of 90 or above in 2 courses 
 --method 1 
SELECT S.Sno, Sname
FROM S, SC
WHERE S.Sno = SC.Sno AND SC.Cno = 'C2' AND SC.Grade >= 90;
 --method 2 
SELECT Sno,Sname
FROM S
WHERE Sno IN
	(SELECT Sno 
	FROM SC
	WHERE SC.Cno = 'C2' AND SC.Grade >= 90);
 --method three 
SELECT Sno,Sname 
FROM S
WHERE EXISTS
	(SELECT *
	FROM SC 
	WHERE Sno=S.Sno AND SC.Cno = 'C2' AND SC.Grade >= 90);
 --search for student ids and the number of courses with scores above 80 for two or more courses 
SELECT Sno, COUNT(*) NUM_OF_COURSE
FROM SC 
WHERE Grade >= 80 
GROUP BY Sno
	HAVING COUNT(*) >= 2;
 --search for elective courses named"database"student id and name 
 --law one 
SELECT Sno, Sname
FROM S
WHERE Sno IN
	(SELECT Sno 
	FROM SC
	WHERE Cno IN 
		(SELECT Cno 
		FROM C
		WHERE Cname = 'database')
	);
 --method 2 
SELECT Sno, Sname
FROM S
WHERE EXISTS
	(SELECT *
	FROM SC
	WHERE Sno = S.Sno AND EXISTS
		(SELECT *
		FROM C
		WHERE Cno = SC.Cno AND Cname = 'database')
	);
 --search for the names and ages of students in other departments who are younger than some students in the information department 
 --law one 
SELECT Sname, Sage
FROM S
WHERE Sage<ANY(SELECT Sage 
			  FROM S
			  WHERE Sdept = 'IS')
	AND Sdept <> 'IS';
 --method 2 
SELECT Sname, Sage
FROM S
WHERE Sage < (SELECT MAX(Sage)
			  FROM S
			  WHERE Sdept='IS')
		AND Sdept!='IS';
 --no elective courses found in the query c student names for course 1 
 --law one 
SELECT Sname
FROM S
WHERE Sno NOT IN
	(SELECT Sno 
	FROM SC
	WHERE Cno = 'C1');
 --method 2 
SELECT Sname 
FROM S
WHERE NOT EXISTS
	(SELECT *
	FROM SC
	WHERE Sno = S.Sno AND Cno = 'C1');
 --search for the names of students who have taken all elective courses 
 --method 1 (derived table) 
SELECT Sname
FROM S
WHERE Sno NOT IN
	(SELECT Sno 
	FROM (SELECT Sno,Cno
		FROM S,C
		EXCEPT
		SELECT Sno,Cno
		FROM SC) AS NEWT
	);
 --method 2 (existential quantifier) 
SELECT Sname
FROM S
WHERE NOT EXISTS
	(SELECT *
	FROM C
	WHERE NOT EXISTS
		(SELECT *
		FROM SC
		WHERE Cno = C.Cno AND Sno = S.Sno)
	);
 --search for student id numbers for all courses selected by at least 95002 elective students 
 --law one 
SELECT DISTINCT Sno 
FROM Sc AS S1
WHERE NOT EXISTS 
	(
	SELECT * 
	FROM Sc AS S2
	WHERE Sno='95002' AND NOT EXISTS 
		(
		SELECT *
		FROM Sc AS S3
		WHERE Sno=S1.SNO AND Cno=S2.Cno
		)	
	);
 --method 2 	
SELECT DISTINCT SNO
FROM SC AS S1
WHERE SNO NOT IN 
(
	SELECT SNO 
	FROM SC AS S2
	WHERE SNO = '95002' AND S2.CNO NOT IN 
	(
		SELECT CNO 
		FROM SC AS S3 
		WHERE SNO = S1.SNO AND S3.CNO = S2.CNO 
	)
);
 --three 
SELECT DISTINCT SNO 
FROM SC AS SCX
WHERE Sno IN 
(
	SELECT Sno  
	FROM SC AS SCY
	WHERE SNO = SCX.Sno
	EXCEPT
	(	
		SELECT CNO 
		FROM SC 
		WHERE SNO = SCX.Sno
		EXCEPT
		SELECT Cno 
		FROM SC 
		WHERE Sno = '95002'
	)
);