数据库操作示例
通过以下SQL语言建立四个表：student,score,course和teacher表，完成以下功能：
CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5));
CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL);
CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL) ;
CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL);
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
2、 查询教师所有的单位即不重复的Depart列。
3、 查询Student表的所有记录。
4、 查询Score表中成绩在60到80之间的所有记录。
5、 查询Score表中成绩为85，86或88的记录。
6、 查询Student表中“95031”班或性别为“女”的同学记录。
7、 以Class降序查询Student表的所有记录。
8、 以Cno升序、Degree降序查询Score表的所有记录。
9、 查询“95031”班的学生人数。
10、查询Score表中的最高分的学生学号和课程号。
11、查询‘3-105’号课程的平均分。
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
13、查询最低分大于70，最高分小于90的Sno列。
14、查询所有学生的Sname、Cno和Degree列。
15、查询所有学生的Sno、Cname和Degree列。
16、查询所有学生的Sname、Cname和Degree列。
17、以班号和年龄从大到小的顺序查询Student表中的全部记录。
18、查询“男”教师及其所上的课程。
19、查询最高分同学的Sno、Cno和Degree列。
20、查询和“李军”同性别的所有同学的Sname.
21、查询和“李军”同性别并同班的同学Sname.
22、查询所有选修“计算机导论”课程的“男”同学的成绩表
1、 select Sname,Ssex,Class from Student;
2、 select distinct depart from teacher;
3、 select Sno as '学号',Sname as '姓名',Ssex as '性别',Sbirthday as'出生日期',Class as'班号'from student;
或
select Sno as 学号,Sname as 姓名,Ssex as 性别,Sbirthday as 出生日期,Class as 班号 from student;
4、 select * from score where degree between 60 and 80;
或select * from score where degree>=60 and degree<=80;
5、 select * from score where degree in (85,86,88);
6、 select * from student where class='95031'or Ssex='女';
7、 select * from student order by class desc;
8、 select * from score order by cno asc ,degree desc;
或select * from score order by cno ,degree desc;
9、 select count(*) as CNT from student where class='95031';
10、select Sno as '学号',cno as '课程号', degree as '最高分' from score
where degree=(select max(degree) from score)
11、select avg(degree)as 课程平均分 from score where cno='3-105';
12、select cno,avg(degree) from score where cno like'3%'group by cno having count(*) >5;
13、select Sno from score group by Sno having min(degree)>70 and max(degree)<90;
14、select student.Sname,score.Cno,score.degree from student,score where student.Sno=score.Sno;
15、select x.Sno,y.Cname,x.degree from score x,course y where x.Cno=y.Cno;
16、select x.Sname,y.Cname,z.degree from student x,course y,score z where x.Sno=z.Sno and z.Cno=y.Cno;
17、select class,sname,sbirthday from student order by class desc,sbirthday;
18、select x.tname,y.cname from teacher x,course y where x.tno=y.tno and x.tsex='男';
19、select * from score where degree=(select max(degree)from score);
20、select sname from student where ssex=(select ssex from student where sname='李军');
21、select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军');
22、select * from score where sno in(select sno from student where ssex='男') and cno=(select cno from course
where cname='计算机导论');
