实验四(3)

CREATE DATABASE CSTP

USE CSTP

/*1:建立学生表*/

CREATE TABLE STUDENT( SNO char(6) primary key, SNAME char(6), SEX char(2), AGE int,

CLASS char(4) );

insert into student values('980101','李华','男',19,'9801'); insert into student values('980102','张军','男',18,'9801'); insert into student values('980103','王红','女',19,'9801'); insert into student values('980301','黄华','女',17,'9803'); insert into student values('980302','大卫','男',16,'9803'); insert into student values('980303','赵峰','男',20,'9803'); insert into student values('980304','孙娟','女',21,'9803');

/*2:建立成绩表*/ create table SC( SNO char(6), CNO char(4),

primary key(SNO,CNO), GRADE int, );

insert into sc values('980101','8104',67); insert into sc values('980101','8105',86); insert into sc values('980102','8244',96); insert into sc values('980102','8245',76); insert into sc values('980103','8104',86); insert into sc values('980103','8105',56); insert into sc values('980301','8244',76); insert into sc values('980301','8245',96); insert into sc values('980302','8104',45); insert into sc values('980302','8105',85); insert into sc values('980303','8244',76); insert into sc values('980303','8245',79); insert into sc values('980304','8104',86); insert into sc values('980304','8105',95);

/*3:建立教师表*/ create table teacher(

TNO char(3) primary key,

1

TNAME char(6), SEX char(2), AGE int ,

PROF char(6), DEPT char(8) );

insert into teacher values('801','李新','男',38,'副教授','计算机系'); insert into teacher values('802','钱军','男',45,'教授','计算机系'); insert into teacher values('803','王立','女',35,'副教授','食品系'); insert into teacher values('804','李丹','女',22,'讲师','食品系');

/*4:建立课程表*/

create table COURSE (

CNO char(4) primary key, CNAME char(10), TNO char(3), );

insert into course values('8104','计算机导论','801'); insert into course values('8105','C语言','802');

insert into course values('8244','数据库系统','803'); insert into course values('8245','数据结构','804');

/*5:所有表内容*/ select * from student; select * from course; select * from teacher; select * from sc;

2

3

设有表4-1~表4-4的4个基本表（表结构与表内容是假设的），请先创建数据库及根据表内容创建表结构，并添加表记录，写出实现一下个体功能的SQL语句： ①查询选修课程“8105”且成绩在80到90之间的所有记录； select * from sc

where cno='8105' and grade between 80 and 90; 或者 select * from sc

where cno='8105' and grade>80 and grade<90;

②查询成绩为79、89或99的记录； select * from sc

where GRADE=79 or GRADE=89 or GRADE=99; 或者 select * from sc

where GRADE in(79,89,99);

③查询“9803”班的学生人数； select count(sno) as 学生人数 from student

where class='9803';

④查询至少有20名学生选修的并且课程号以8开头的课程的平均成绩； select avg(grade) as 平均成绩 from sc

where cno like '8%' group by cno

having count(cno) >= 20;

换成2名学生选修

4

⑤查询最低分大于80，最高分小于95的SNO与平均分； select sno,min(grade)as 最低分,max(grade)as 最高分 from sc

group by sno having min(grade)>80 and max(grade) < 95;

⑥查询“9803”班学生所选各课程的课程号及其平均成绩； select sno,avg(grade) as 平均成绩 from sc

where sno in

(select sno from student where class = '9803') group by sno;

⑦查询选修“8105”课程的成绩高于“980302”号同学成绩的所有同学的记录； select *

from student where sno in (select sno from sc

where cno='8105' and grade > (select grade from sc

where sno='980302' and cno='8105'));

⑧查询与学号为“980103”的同学同岁的所有学生的SNO,SNAME和AGE； 法1:select sno,sname,age from student

where age = (select age from student where sno='980103'); 法2:select sno,sname,age from student

where age in (select age from student where sno='980103');

5

⑨查询“钱军”教师任课的课程号，以及选修其课程学生的学号和成绩； 法1:select cno from course

where tno=(select tno from teacher

where tname='钱军'); select sno,grade from sc

where cno in(select cno from course where tno in (select tno from teacher

where tname='钱军'));

法2:select teacher.tname as 教师姓名,course.cno as课程号,student.sname as 学生姓名,student.sno as学号,sc.grade as成绩

from teacher inner join (course inner join (sc inner join student on student.sno=sc.sno) on course.sno=sc.sno) on course.tno=teacher.tno where teacher.tname='钱军';（出错）

⑩查询选修某课程的学生人数多于20人的教师姓名； 法1:select tname as 教师姓名 from teacher where tno in (select tno from course where cno in (select cno from sc

group by cno having count(*) > 20));

6

法2:select tname as 教师姓名

from teacher inner join (course inner join sc on course.cno=sc.cno) on teacher.tno=course.tno group by sc.cno having count(*) > 20;（出错）

⑾查询选修编号为“8105”课程且成绩至少高于其选修编号为“8245”课程的同学的SNO及“8105”课程成绩，并按成绩从高到低依次排列； select * from sc

where cno='8105'

and grade > any (select grade from sc

where cno='8245') order by grade desc;

⑿查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩的同学的 CNO,SNO,GRADE； select * from sc

where cno='8105'

and grade > all (select grade from sc

where cno='8245') order by grade desc;

⒀列出所有教师和同学的姓名、SEX、AGE；

select student.sname as 学生姓名,student.sex as 学生性别,student.age as 学生年龄, teacher.tname as 教师姓名,teacher.sex as 教师性别,teacher.age as 教师年龄

from student inner join (sc inner join (course inner join teacher on course.tno=teacher.tno) on sc.cno=course.cno) on student.sno=sc.sno;

7

⒁查询成绩比该课平均成绩高的学生的成绩表； select * from sc

where grade > any (select avg(grade) from sc group by cno) order by cno asc;

⒂列出所有任课教师的TNAME和DEPT；

select teacher.tname as 教师姓名, teacher.dept as专业 from teacher where tno in (select tno from course where cno in (select cno from sc

8

group by cno));

⒃列出所有未讲课教师的TNAME和DEPT；

select teacher.tname as 教师姓名, teacher.dept as 专业 from teacher where tno not in (select tno from course

where cno in (select cno from sc group by cno));

⒄列出至少有4名男生的班号； select class as 班号 from student where sex='男'

group by class having count(*) >= 4;

⒅查询不姓“张”的学生记录； 法1:select * from student

where sname not like '张%'; 法2:select * from student

where sname not in (select sname from student

where sname like '张%');

⒆查询每门课最高分的学生的SNO、CNO、GRADE； select *

9

from sc

where grade in (select max(grade) from sc

group by cno);

⒇查询与“李华”同性别并同班的同学SNAME；

select sname from student

where sex=(select sex from student

where sname='李华') and class=(select class from student

where sname='李华');

（21）查询“女”教师及其所上的课程； select tname as 教师名,cname as 课程

from teacher inner join course on teacher.tno=course.tno where sex='女';

（22）查询选修“数据库系统”课程的“男”同学的成绩表； 法1:select * from sc

where cno=(select cno from course

where cname='数据库系统') and sno in (select sno from student

10

where sex='男');

法2:select sc.sno,student.sname as 学生姓名,

student.sex as 学生性别,sc.cno,sc.grade,course.cname

from student inner join (sc inner join course on sc.cno=course.cno) on student.sno=sc.sno where sex='男' and cname='数据库系统';

（23）查询所有比刘涛年龄大的教师姓名、年龄和刘涛的年龄； Select tname,age From teacher

Where age>(select age From teacher

Where tname='刘涛') Union

Select tname,age From teacher

Where tname='刘涛';

/*Select teacher.tname as 教师姓名，teacher.age as 年龄，*/

（24）查询不讲授“8104”号课程的教师姓名。 select teacher.tname from teacher where not exists ( select *

from course

where cno='8104' and tno=teacher.tno);

11

where sex='男');

法2:select sc.sno,student.sname as 学生姓名,

student.sex as 学生性别,sc.cno,sc.grade,course.cname

from student inner join (sc inner join course on sc.cno=course.cno) on student.sno=sc.sno where sex='男' and cname='数据库系统';

（23）查询所有比刘涛年龄大的教师姓名、年龄和刘涛的年龄； Select tname,age From teacher

Where age>(select age From teacher

Where tname='刘涛') Union

Select tname,age From teacher

Where tname='刘涛';

/*Select teacher.tname as 教师姓名，teacher.age as 年龄，*/

（24）查询不讲授“8104”号课程的教师姓名。 select teacher.tname from teacher where not exists ( select *

from course

where cno='8104' and tno=teacher.tno);

11

本文来源：https://www.bwwdw.com/article/hmf6.html