CREATE DATABASE 学生课表
GO
USE 学生课表
CREATE TABLE 学生(SNUM CHAR(4) NOT NULL UNIQUE,
Sname VARCHAR(8) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT DEFAULT 21,
Sphone VARCHAR(20),
Dname VARCHAR(20),
PRIMARY KEY(Snum),
CONSTRAINT C1 CHECK(Ssex IN('男','女')));
USE 学生课表
CREATE TABLE 课程(Cnum CHAR(2) PRIMARY KEY,
Cname VARCHAR(20) NOT NULL,
Cfreq NUMERIC(2,1)
CONSTRAINT C2 CHECK(Cfreq BETWEEN 0 AND 10));
USE 学生课表
CREATE TABLE 选课(Snum CHAR(4),
Cnum CHAR(2),
Score SMALLINT,
PRIMARY KEY(Snum,Cnum),
CONSTRAINT C3 CHECK(Score BETWEEN 0 AND 100),
CONSTRAINT C4 FOREIGN KEY(Snum) REFERENCES 学生(Snum),
CONSTRAINT C5 FOREIGN KEY(Cnum) REFERENCES 课程(Cnum));

INSERT INTO 学生(Snum,Sname,Ssex,Sage,Sphone,Dname)
VALUES('S001','王明','男','19','86824571','数学系');
INSERT INTO 学生(Snum,Sname,Ssex,Sage,Sphone,Dname)
VALUES('S002','李勇','男','23','89454321','物理系');
INSERT INTO 学生(Snum,Sname,Ssex,Sage,Sphone,Dname)
VALUES('S003','刘燕','女','21','','计算机系');
INSERT INTO 学生(Snum,Sname,Ssex,Sage,Sphone,Dname)
VALUES('S004','王萍','女','23','','电子系');
INSERT INTO 学生(Snum,Sname,Ssex,Sage,Sphone,Dname)
VALUES('S005','王佳','男','24','13098765892','计算机系');
INSERT INTO 学生(Snum,Sname,Ssex,Sage,Sphone,Dname)
VALUES('S006','赵婷','女','20','','物理系');
INSERT INTO 课程(Cnum,Cname,Cfreq)
VALUES('C1','数据库系统原理','4');
INSERT INTO 课程(Cnum,Cname,Cfreq)
VALUES('C2','C程序设计','4');
INSERT INTO 课程(Cnum,Cname,Cfreq)
VALUES('C3','计算机体系结构','3');
INSERT INTO 课程(Cnum,Cname,Cfreq)
VALUES('C4','自动控制原理','2');
INSERT INTO 课程(Cnum,Cname,Cfreq)
VALUES('C5','数据结构','4');
INSERT INTO 选课(Snum,Cnum,Score)
VALUES('S001','C1','83');
INSERT INTO 选课(Snum,Cnum,Score)
VALUES('S001','C2','89');
INSERT INTO 选课(Snum,Cnum,Score)
VALUES('S001','C3','0');
INSERT INTO 选课(Snum,Cnum,Score)
VALUES('S001','C4','85');
INSERT INTO 选课(Snum,Cnum,Score)
VALUES('S001','C5','69');
INSERT INTO 选课(Snum,Cnum,Score)
VALUES('S002','C3','78');
INSERT INTO 选课(Snum,Cnum,Score)
VALUES('S002','C4','75');
INSERT INTO 选课(Snum,Cnum,Score)
VALUES('S003','C1','85');
INSERT INTO 选课(Snum,Cnum,Score)
VALUES('S004','C2','0');
INSERT INTO 选课(Snum,Cnum,Score)
VALUES('S005','C1','92');
INSERT INTO 选课(Snum,Cnum,Score)
VALUES('S005','C3','76');

(1)SELECT Snum,Sname
FROM 学生
WHERE Dname='数学系';
(3)SELECT Snum,Cnum
FROM 选课
WHERE Cnum='C1'
ORDER BY Score DESC,Snum ASC;
(4)SELECT Snum,Score*0.8 AS NewScore
FROM 选课
WHERE Cnum ='C1' AND ( Score >=80 AND Score <=90);
(5)SELECT *
FROM 学生
WHERE (Dname='数学系' OR Dname='计算机系')AND Sname like '王%';
(6)SELECT Snum,Cnum
FROM 选课
WHERE Score is NULL;
(7)SELECT DISTINCT Snum
FROM 选课;

(1)SELECT 学生.Snum,Sname,Ssex,Sage,Sphone,Dname,课程.Cname
FROM 学生,选课,课程
WHERE 学生.Snum=选课.Snum AND 选课.Cnum=课程.Cnum;
(2)SELECT 学生.Snum,Sname,课程.Cname,选课.Score
FROM 学生,选课,课程
WHERE 学生.Snum=选课.Snum AND 选课.Cnum=课程.Cnum;
(3)SELECT 学生.Snum,Sname,选课.Score
FROM 学生,选课
WHERE 学生.Snum=选课.Snum AND 选课.Cnum='C1' AND 选课.Score>=90;

SELECT 选课.Snum,Score
FROM 选课,学生
where Cnum='C1'
and 选课.Snum=学生.Snum
and Score>all
(select Score from 选课,学生
where 选课.Snum=学生.Snum
and Sname='刘燕')
select*
from 学生
where Dname!='物理系'
and Sage<any
(select max(Sage)
from 学生
where Dname='物理系')
select*
from 学生
where Dname!='计算机系'
and Sage<all(
select min(Sage)
from 学生
where Dname='计算机系')
select Sname
from 学生
where Snum in
(select 学生.Snum
from 选课,学生
where 选课.Snum=学生.Snum
and Cnum='c2')
select count(distinct Snum)
from 选课
select Cnum,count(Snum)
from 选课
group by Cnum;
select Snum
from 选课
group by Snum
having COUNT(*)>3;
select*
from 学生
where Dname='计算机系'
UNION
(SELECT*
FROM 学生
where sage<=19);

http://pan.baidu.com/mbox/homepage?short=sladgD3