数据库项目练习

花了有一个小时才把今天的作业写完了
题目如下：
练习：执行以下SQL语句创建Students, courses, enrollment三个表，并插入测试数据。
CREATE TABLE Students
(
Sno CHAR (10 ) PRIMARY KEY,
Sname CHAR ( 8 ) NOT NULL,
Ssex CHAR (1) NOT NULL CHECK (Ssex = ‘F’ OR Ssex = ‘M’),
Sage INT NULL, Sdept CHAR (20 ) DEFAULT ‘Computer’
);
CREATE TABLE Courses
(
Cno CHAR (6 ) PRIMARY KEY,
Cname CHAR ( 20 ) NOT NULL,
PreCno CHAR (6) ,
Credits INT
);
CREATE TABLE Enrollment
(
Sno CHAR(10) NOT NULL,
Cno CHAR(6) NOT NULL,
Grade INT,
CONSTRAINT EPK PRIMARY KEY ( Sno, Cno ),
CONSTRAINT ESlink FOREIGN KEY (Sno) REFERENCES Students ( Sno ),
CONSTRAINT EClink FOREIGN KEY (Cno ) REFERENCES Courses ( Cno )
);
INSERT INTO Students VALUES (‘20010101’, ‘Jone’, ‘M’, 19, ‘computer’);
INSERT INTO Students VALUES (‘20010102’, ‘Sue’, ‘F’, 20, ‘Computer’);
INSERT INTO Students VALUES (‘20010103’, ‘Smith’, ‘M’, 19, ‘Math’);
INSERT INTO Students VALUES (‘20030101’, ‘Allen’, ‘M’, 18, ‘Automation’);
INSERT INTO Students VALUES (‘20030102’, ‘Deepa’, ‘F’, 21, ‘Art’);

INSERT INTO Courses VALUES (‘c1’, ‘English’, ‘’, 4);
INSERT INTO Courses VALUES (‘c2’, ‘Math’, ‘c5’,2);
INSERT INTO Courses VALUES (‘c3’, ‘Database’,‘c2’,2);

INSERT INTO Enrollment VALUES (‘20010101’,‘c1’,90);
INSERT INTO Enrollment VALUES (‘20010102’,‘c1’,88);
INSERT INTO Enrollment VALUES (‘20010102’,‘c2’,94);
INSERT INTO Enrollment VALUES (‘20010102’,‘c3’,62);

利用以上三个表，编写SQL语句完成以下简单查询练习：
1.查询全体学生的学号与姓名
select sno,sname from Students;
2.查询全体学生的学号、姓名、性别、年龄、所在系。
select sno,sname,ssex,sage,sdept
from Students;
3.查询全体学生的选课情况，即学号、课程号、成绩，成绩值都加5
select sno,cno,grade+5 grade
from Enrollment;
4.显示所有选课学生的学号。
select sno
from Enrollment;
5.显示所有选课学生的学号，并去掉重复行。
select distinct sno
from Enrollment;
6.查询学生选课成绩大于80分的学生学号、课程号、成绩。
select sno,cno,grade
from Enrollment
where grade>80;
7.查询数学系全体学生的学号、姓名。
select sno,sname
from Students
where sdept = ‘Math’;
8.查询学生选课成绩在80～90分之间的学生学号、课程号、成绩。
select sno,cno,grade
from Enrollment
where grade>=80 and grade<=90;
9.查询数学系、计算机系、艺术系学生的学号、姓名。
select sno,sname
from Students
where sdept=‘Math’ or sdept='Computer’or sdept=‘Art’;
10.查询既不是数学系、计算机系，也不是艺术系学生的学号、姓名。
select sno,sname
from Students
where sdept !='Math’or sdept !='Computer’or sdept !=‘Art’;
11.查找姓名以S开头的所有学生的学号、姓名。
select sno,sname
from Students
where sname like ‘S%’;
12.查询无考试成绩(即成绩为空值)的学生的学号和相应的课程号。
select sno,cno
from Enrollment
where grade is null;
13.查询有考试成绩(即成绩不为空值)的学生的学号、课程号。
select sno,cno
from Enrollment
where grade is not null;
14.查询计算机系年龄在18岁以上的学生学号、姓名。
select sno,sname
from Students
where sdept = ‘Math’ and sage>18;
15.求选修了c1课程或c2课程的学生学号、成绩。
select sno,grade
from Enrollment
where cno=‘c1’ or cno=‘c2’;
16.求学生的总人数
select count(sno) 总人数
from Students ;
17.求选修了c1课程的学生的平均成绩。
select avg(grade) 平均成绩
from Enrollment;
18.求20010102号学生的考试总成绩之和。
select sum(grade) 总成绩之和
from Enrollment
where sno=‘20010102’;
19.选修了c1课程的学生的最高分和最低分
select max(grade) 最高分,min(grade) 最低分
from Enrollment
where cno =‘c1’;
20.求选修每门课程的学生人数。
select cno, count(cno) 选课人数
from Enrollment group by cno;
21.求每个学生的学号和各门课程的总成绩。
select sno 学号，sum(grade) 总成绩
from Enrollment
group by Sno;
22.求选修课程超过2门课的学生的学号、平均成绩，选修的门数。
select sno,avg(grade) 平均成绩,count(cno) 选修的门数
from Enrollment
group by sno having count(cno)>2;
23.查询所有学生的行，并按学生的年龄值从小到大排序。
select *from Students
order by sage ;
24.查询选修了c1课程的学生的学号和成绩，查询结果按成绩降序排列。
select sno ,grade
from Enrollment
where cno = ‘c1’
order by grade desc;
25.查询全体学生信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。
select * from Students
order by sdept,sage desc;
26.求选修课程超过2门课的学生的学号、平均成绩和选课门数，并按平均成绩降序排列。
select sno ,avg(grade) 平均成绩,count(cno) 选课门数
from Enrollment
group by sno
having count(cno)>=2
order by avg(grade) desc;