ms SQL server数据库学生表查询

/************************************************
一、建立数据库
************************************************/

USE master;
GO

CREATE DATABASE [StuData]
ON PRIMARY
(
    NAME = N'StuData',
    FILENAME = N'D:\SQL work\StuData.mdf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1MB
)

LOG ON 
(
    NAME = Studlog,
    FILENAME = 'D:\SQL work\Stulog1.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20
);

GO

/************************************************
二、建表
************************************************/

USE StuData;
GO

--学生表
CREATE TABLE S
(
	Sno CHAR(9) PRIMARY KEY,
	Sname CHAR(20) NOT NULL,
	Ssex CHAR(2) CHECK (Ssex IN ('男','女')),
	Sage SMALLINT,
	Sdept CHAR(20)
);

--课程表
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

--选课表
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

--加入列
--ALTER TABLE C ADD Cpno CHAR(4);

/************************************************
三、数据更新
************************************************/

--修改S
INSERT INTO S(Sno, Sname, Ssex, Sage, Sdept) VALUES ('95001',	'李勇',	'男',	20,	'CS');
INSERT INTO S(Sno, Sname, Ssex, Sage, Sdept) VALUES ('95002',	'刘晨',	'女',	19,	'IS');
INSERT INTO S(Sno, Sname, Ssex, Sage, Sdept) VALUES ('95003',	'王敏',	'女',	18,	'MA');
INSERT INTO S(Sno, Sname, Ssex, Sage, Sdept) VALUES ('95004',	'张力',	'男',	19,	'IS');

--修改C
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C2',	'高等数学',	2,	null);
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C6',	'数据处理',	2,	null);
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C7',	'PASCAL语言',4,	'c6');
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C4',	'操作系统',	3,	'c6');
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C5',	'数据结构',	4,	'c7');
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C1',	'数据库',	4,	'c5');
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C3',	'信息系统',	4,	'c1');

--修改数据
/*
UPDATE C 
SET Cpno='C5'
WHERE Cno = 'C1';
*/

--修改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');

/************************************************
四、查询
************************************************/

--列出所有表(测试）
SELECT *
FROM S;
SELECT *
FROM C;
SELECT *
FROM SC;

--查询所有姓刘学生的姓名、学号和性别
SELECT  Sname , Sno , Ssex
FROM  S
WHERE  Sname  LIKE  '刘%' ;

--查询选修c2课程且成绩在90分及以上的所有学生的学号、姓名
--方法一
SELECT S.Sno, Sname
FROM S, SC
WHERE S.Sno = SC.Sno AND SC.Cno = 'C2' AND SC.Grade >= 90;

--方法二
SELECT Sno,Sname
FROM S
WHERE Sno IN
	(SELECT Sno 
	FROM SC
	WHERE SC.Cno = 'C2' AND SC.Grade >= 90);

--方法三
SELECT Sno,Sname 
FROM S
WHERE EXISTS
	(SELECT *
	FROM SC 
	WHERE Sno=S.Sno AND SC.Cno = 'C2' AND SC.Grade >= 90);

--查询有2门以上课程是80分以上的学生学号及其(80分以上的)课程数
SELECT Sno, COUNT(*) NUM_OF_COURSE
FROM SC 
WHERE Grade >= 80 
GROUP BY Sno
	HAVING COUNT(*) >= 2;

--查询选修了课程名为“数据库”的学生学号和姓名
--法一
SELECT Sno, Sname
FROM S
WHERE Sno IN
	(SELECT Sno 
	FROM SC
	WHERE Cno IN 
		(SELECT Cno 
		FROM C
		WHERE Cname = '数据库')
	);

--法二
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 = '数据库')
	);

--查询其他系中比信息系某些学生年龄小的学生姓名和年龄
--法一
SELECT Sname, Sage
FROM S
WHERE Sage<ANY(SELECT Sage 
			  FROM S
			  WHERE Sdept = 'IS')
	AND Sdept <> 'IS';

--法二
SELECT Sname, Sage
FROM S
WHERE Sage < (SELECT MAX(Sage)
			  FROM S
			  WHERE Sdept='IS')
		AND Sdept!='IS';

-- 查询没有选修c1号课程的学生姓名
--法一
SELECT Sname
FROM S
WHERE Sno NOT IN
	(SELECT Sno 
	FROM SC
	WHERE Cno = 'C1');

--法二
SELECT Sname 
FROM S
WHERE NOT EXISTS
	(SELECT *
	FROM SC
	WHERE Sno = S.Sno AND Cno = 'C1');

--查询选修了全部课程的学生姓名
--法一(派生表）
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
	);

--法二（存在量词）
SELECT Sname
FROM S
WHERE NOT EXISTS
	(SELECT *
	FROM C
	WHERE NOT EXISTS
		(SELECT *
		FROM SC
		WHERE Cno = C.Cno AND Sno = S.Sno)
	);

--查询至少选修学生95002所选全部课程的学生学号
--法一
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
		)	
	);

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

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