[实验一]
create database test;
//（1） 分别利用命令行方式将下面各表建立到教学管理数据库中。表的创建存在先后顺序，在实际操作中要注意体会。
create table department
(dept_no char (2) unique,
dept_name char(10) not null
);
create table class
(class_no char(5) unique,
class_name char(10) not null,
dept_no char(2)
foreign key(dept_no) references department(dept_no)
);
create table student
(s_no char(6) unique,
s_name char(10) not null,
s_sex char(2) check(s_sex='男' or s_sex='女'),
s_birthday datetime,
s_score numeric(5,1),
s_addf numeric(3,1),
class_no char(5),
foreign key (class_no) references class(class_no)
);
create table course
(course_no char(5) unique,
course_name char(20) not null
);
create table teacher
(t_no char(6) unique,
t_name char(10) not null,
t_sex char(2) check(t_sex='男' or t_sex='女'),
t_birthday datetime,
tech_title char(10)
);
create table choice
(s_no char(6),
course_no char(5),
score numeric(5,1)
primary key(s_no,course_no),
foreign key(s_no) references student(s_no),
foreign key(course_no) references course(course_no)
);
create table teaching
(t_no char(6),
course_no char(5),
primary key (t_no,course_no),
foreign key(t_no) references teacher(t_no),
foreign key(course_no) references course(course_no)
);
//（2）利用命令行方式查看这些基本表的各种信息。
select*from student;
select*from class;
select*from department;
select*from course;
select*from teacher;
select*from choice;
select*from teaching;
//（3）为某基本表创建索引，查看其信息，然后删除它。
create index Teat_no on teacher(t_no);
drop index Teat_no;
//（4）向教师表中添加数据
insert into teacher(t_no,t_name,t_sex,t_birthday,tech_title) values ('2095','我','男','1992-08-01','老子');
insert into teacher(t_no,t_name,t_sex,t_birthday,tech_title) values ('2096','爱','女','1995-02-04','庄子');
insert into teacher(t_no,t_name,t_sex,t_birthday,tech_title) values ('2097','九','男','1988-05-07','孔子');
insert into teacher(t_no,t_name,t_sex,t_birthday,tech_title) values ('2098','五','女','1999-06-08','墨子');
insert into teacher(t_no,t_name,t_sex,t_birthday,tech_title) values ('2099','寝','男','1999-03-09','荀子');
//（5）修改学生表（student），增加联系电话（phone）属性
alter table student add s_phone char(8);
//（6）为学生表（student）按出生日期字段建立索引，并练习删除索引
create index Stubirthday on student(s_birthday);
drop index Stubirthday;
[实验二]
//（1） 向教学管理系统的每个表中录入5条记录。录入时注意体会外键约束。
insert into department values('1','信息系统');
insert into department values('2','工商管理');
insert into department values('3','市场营销');
insert in
to department values('4','会计');
insert into department values('5','财务管理');
insert into class values('9501','信息','1');
insert into class values('9502','工商','2');
insert into class values('9503','营销','3');
insert into class values('9504','会计','4');
insert into class values('9505','财务','5');
insert into student values('95011','严二姣','男','1991-04-04','555.5',NULL,'9501','04313887');
insert into student values('95012','严傻姣','男','1995-06-07','666.6',NULL,'9501','04313889');
insert into student values('95013','严二傻','男','1992-11-19','777.7',NULL,'9501','04313886');
insert into student values('95014','严傻傻','男','1993-10-05','888.8',NULL,'9501','04313888');
insert into student values('95015','傻瓜严','女','1990-12-15','999.9',NULL,'9501','04313885');
insert into course values('2091','孔子');
insert into course values('2092','老子');
insert into course values('2093','庄子');
insert into course values('2094','墨子');
insert into course values('2090','荀子');
insert into choice values('95011','2091','100');
insert into choice values('95012','2092','19');
insert into choice values('95013','2093','59');
insert into choice values('95014','2094','58');
insert into choice values('95015','2090','57');
select * from choice;
insert into teaching values('2095','2091');
insert into teaching values('2096','2092');
insert into teaching values('2097','2093');
insert into teaching values('2098','2094');
insert into teaching values('2095','2090');
//(2） 创建教师表2(teacher2)，向表中录入5条不同的记录，注意与教师表中数据必须不同，主要体现在主键上。
create table teacher2
(t_no char(6) unique,
t_name char(8) not null,
t_sex char(2) check(t_sex='男' or t_sex='女'),
t_birthday datetime,
tech_title char(10),
);
select*from teacher2;
insert into teacher2 values('2100','驴大傻','男','1990-02-02','鉴宝');
insert into teacher2 values('2101','大傻驴','男','1995-03-03','考古');
insert into teacher2 values('2102','崴脚驴','男','1992-08-08','哲学');
insert into teacher2 values('2103','瘸驴','男','1998-08-09','人类起源');
insert into teacher2 values('2104','面板驴','女','1994-09-30','君主论');
select*from teacher2;
//（3）用多行数据插入的方法将教师表2中数据录入到教师表。
insert into teacher select * from teacher2;*****!!!!!
select *from teacher;
//（4）利用SELECT INTO方法创建教师表3(teacher3)，查看教师表3的内容，体会表间数据复制的含义。
select * into teacher3 from teacher2;
select * from teacher3;
//（5）删除教师表3中所有数据，删除教师表3。@@@@@!!!
delete teacher3;
drop table teacher3;
//（6）删除教师表2中数据，删除教师表2。
delete teacher2;
drop table teacher2;
select*from teacher2;检查
//（7）显示所有学生情况。
select*from student;
//（9）显
示与某个学生某门课程成绩相同的选修该门课程的所有学生的学号、姓名。
select t_no,t_name,t_birthday from teacher;
//（9）显示与某个学生某门课程成绩相同的选修该门课程的所有学生的学号、姓名。(自连接)
select bb.s_no,s_name
from student,choice aa,choice bb
where aa.s_no='95011'
and aa.course_no='2091'
and bb.course_no=aa.course_no
and bb.score=aa.score
and bb.s_no=student.s_no;
//（10）显示成绩在60分以上的学生姓名、教师姓名和课程名称。
select student.s_name ,t_name,course_name
from student,choice,teacher,course,teaching
where score>='60'
and choice.s_no=student.s_no
and choice.course_no=course.course_no
and choice.course_no=teaching.course_no
and teaching.t_no=teacher.t_no
select*from choice;
// （11）将某个学生所学课程成绩低于60分的课程列出来。
select course_name
from choice,course
where score
and choice.s_no='95012'
and choice.course_no=course.course_no;
//（12）将某门课程成绩低于60分的学生的成绩改为60分。
老师讲的：
updata choice
set score=60
where sno in(select sno
from student
where sname='李明')
and course_no in(select course _no
from course
where course_name='物理');
[实验三]
//（1）在课程选修表中录入所有学生的物理学习成绩
insert into course values('2001','物理');
select*from course;
select*from student;
insert into student values('95016','严三姣','男','1991-01-04','555.5',NULL,'9501','04313887');
insert into student values('95017','严四姣','男','1991-08-04','555.5',NULL,'9501','04313887');
insert into student values('95018','严五姣','男','1991-08-04','555.5',NULL,'9501','04313817');
insert into choice values('95011','2001','100');
insert into choice values('95012','2001','90');
insert into choice values('95013','2001','80');
insert into choice values('95014','2001','85');
//（2）按学生的物理成绩排序，显示学生表的所有属性信息及物理成绩
老师讲：
SELECT student.*,score
from student,choice,course
where student.s_no=choice.s_no
and course.course_no='2001'
and course.course_no=choice.course_no
order by score;
SELECTstudent.*,score
from student left join choice
on student.s_no=choice.s_no
where course_no='2001'
order by score;
我的：
select student.*,choice.score
from student left join choice
on choice.s_no=student.s_no
where course_no='2001'
order by score asc;
//（3）查询所有物理成绩在80分与90分之间的学生个数及平均分
我的：
select COUNT (*) sumstudent,AVG(score) grade
from choice
where course_no='2001'
and score>='80'
and score
老师讲：
select count(*),avg(score)
from choice,course
where course_name='物理'
and score between 80 and90 (between 包含左右边界)
and choice.course_no=c
ourse.course_no;
//（4）查询每个班级学生的物理最高成绩，显示出班级名称与最高成绩@@@@！！！！
我的：
select class.class_no ,class.class.name,max(score)
from class,choice,student
where course_no='2001'
and class.class_no=student.class_no
and student.s_no=choice.s_no
group by class.class_no,class.class.name;
//（5）使用嵌套子查询的方式，查询所有物理成绩在80分与90分之间的学生
我的：
select choice.s_no ,s_name ,score
from choice,student
where choice.s_no in(select s_no
from choice
where score>'80'
and score
and course_no='2001';
老师讲：
select*
from student
where s_no in (select s_no
from choice
where score between 80 and 90
and course_no =(select course_no
from course
where course_no='2001'))
//（6）查询所有学生总数在30人以上的班级信息
老师：
select student.class_no
from class,student
where class.class_no=student.class_no
group by student.class_no
having count(*)>=30 ;
我的：
select student.class_no
from class,student
where class.class_no=student.class_no
group by student.class_no
having count(*)>=30 ;
//(7)查询所有没有选择物理课的同学的学号和姓名
我：
select s_no,s_name
from student
where not exists
(select*
from choice
where s_no=student.s_no
and course_no='2001');
老师：
select *
from student
where s_no Ot in(select s_no
from choice
where course_no='2001')
// （8）对以上的数据查询语句(子查询那个)进行优化
select student.s_no,s_name
from student,choice,course
where student.s_no = choice.s_no
and course.course_no=choice.course_no
and course_name!=' 物理';
//（9）将所有物理成绩低于60的学生信息删除
我：
select s_no
into #F
from choice
where course_no='2001'
and score
delete
from choice
where course_no='2001'
and score
delete student
from student,#F
where student.s_no=#F.s_no;
老师：
select s_no
into #table1
from choice
where course_no='2001'
and score
selce *
frome #table1
delete
from choice
where s_no in (select s_no
from #table1);
delete
from student
where s_no in(select S_no
from #table1);
[实验四]
//1、查询既教物理课也教化学课的老师姓名
方法一：
select t_name
from teaching ,course,teacher
where course_name='物理'
and teaching.course_no=course.course_no
and teaching.t_no in(select t_no
from teaching,course
where course_name='孔子'
and teaching.course_no=course.course_no)
and teaching.t_no=teacher.t_no;
方法二：
select t_name
from teacher,teaching taa,teaching tbb,course
coua,course coub
where coua.course_name='物理'
and taa.course_no=coua.course_no
and coub.course_name='孔子'
and tbb.course_no=coub.course_no
and taa.t_no=teacher.t_no;
//2、创建显示学生成绩的视图，显示结果包含学生学号、学生姓名、课程号、课程名称、教师编号、教师姓名、成绩。
create view student_score
as select student.s_no,s_name,course.course_no,course_name,teacher.t_no,t_name,choice.score
from student,course,teacher,choice,teaching
where student.s_no=choice.s_no
and choice.course_no=course.course_no
and choice.course_no=teaching.course_no
and teaching.t_no=teacher.t_no
select *
from student_score
//3、修改该视图使其只查询出成绩不及格的相关信息。
drop view student_score;
create view student_score
as select student.s_no,s_name,course.course_no,course_name,teacher.t_no,t_name,choice.score
from student,course,teacher,choice,teaching
where student.s_no=choice.s_no
and choice.course_no=course.course_no
and choice.course_no=teaching.course_no
and teaching.t_no=teacher.t_no
and choice.score
//4、查询选修了A老师教授所有课程的学生
select s_name
from student
where not exists
(select*
from teaching
where t_no=2095
and not exists
(select *
from choice
where s_no=student.s_no
and choice.course_no=teaching.course_no));
//5、查询所有物理课成绩最高的同学学号（SNO）和姓名（SNAME）
select student.s_no,s_name,score
from student,choice,course
where course_name='物理'
and course.course_no=choice.course_no
and choice.s_no=student.s_no
and score>=(select max(score)
from choice,course
where course_name='物理'
and course.course_no=choice.course_no);
//6查询所有物理成绩及格但化学成绩不及格的同学学号（SNO）和姓名（SNAME）。
方法一：
select student.s_no,s_name
from student,choice cha,choice chb,course coua,course coub
where coua.course_name='物理'
and coua.course_no=cha.course_no
and cha.score>='60'
and coub.course_name='庄子'
and coub.course_no=chb.course_no
and chb.score
方法二：
select student.s_no,s_name
from student,choice ,course
where student.s_no=choice.s_no
and course_name='物理'
and course.course_no=choice.course_no
and choice.s_no in (select student.s_no
from student,choice,course
where student.s_no=choice.s_no
and course_name='庄子'
and course.course_no=choice.course_no);
//7查询所有选课门数大于6门的同学，并按选课门数降序排列。
select s_no
from choice
group by s_no
having count(*)>2
order by count(*) desc;
//8为课程表加入学分字段，查询所有选课学分大于
20分、选课门数大于9门，并且最多有一门成绩低于60分的同学。
alter table course add xf char(4);
update course set xf='2'
where course_no='2091';
update course set xf='5'
where course_no='2092';
update course set xf='3'
where course_no='2093';
update course set xf='7'
where course_no='2094';
update course set xf='1'
where course_no='2090';
update course set xf='9'
where course_no='2001';
select student.s_no
from student,choice
where score
and choice.s_no=student.s_no
and exists (select choice.s_no
from course,choice
where choice.course_no=course.course_no
and exists (select s_no
from choice
group by s_no
having COUNT(*)>3)
group by choice.s_no
having COUNT(xf)>2)
group by student.s_no
having COUNT(*)
//10、找出所选课程平均分最高的同学
select s_no
from choice
group by s_no
having AVG(score)>=(select AVG(score)
from choice
group by s_no)
[实验一]
create database test;
//（1） 分别利用命令行方式将下面各表建立到教学管理数据库中。表的创建存在先后顺序，在实际操作中要注意体会。
create table department
(dept_no char (2) unique,
dept_name char(10) not null
);
create table class
(class_no char(5) unique,
class_name char(10) not null,
dept_no char(2)
foreign key(dept_no) references department(dept_no)
);
create table student
(s_no char(6) unique,
s_name char(10) not null,
s_sex char(2) check(s_sex='男' or s_sex='女'),
s_birthday datetime,
s_score numeric(5,1),
s_addf numeric(3,1),
class_no char(5),
foreign key (class_no) references class(class_no)
);
create table course
(course_no char(5) unique,
course_name char(20) not null
);
create table teacher
(t_no char(6) unique,
t_name char(10) not null,
t_sex char(2) check(t_sex='男' or t_sex='女'),
t_birthday datetime,
tech_title char(10)
);
create table choice
(s_no char(6),
course_no char(5),
score numeric(5,1)
primary key(s_no,course_no),
foreign key(s_no) references student(s_no),
foreign key(course_no) references course(course_no)
);
create table teaching
(t_no char(6),
course_no char(5),
primary key (t_no,course_no),
foreign key(t_no) references teacher(t_no),
foreign key(course_no) references course(course_no)
);
//（2）利用命令行方式查看这些基本表的各种信息。
select*from student;
select*from class;
select*from department;
select*from course;
select*from teacher;
select*from choice;
select*from teaching;
//（3）为某基本表创建索引，查看其信息，然后删除它。
create index Teat_no on teacher(t_no);
drop index Teat_no;
//（4）向教师表中添加数据
insert into teacher(t_no,t_name,t_sex,t_birthday,tech_title) values ('2095','我','男','1992-08-01','老子');
insert into teacher(t_no,t_name,t_sex,t_birthday,tech_title) values ('2096','爱','女','1995-02-04','庄子');
insert into teacher(t_no,t_name,t_sex,t_birthday,tech_title) values ('2097','九','男','1988-05-07','孔子');
insert into teacher(t_no,t_name,t_sex,t_birthday,tech_title) values ('2098','五','女','1999-06-08','墨子');
insert into teacher(t_no,t_name,t_sex,t_birthday,tech_title) values ('2099','寝','男','1999-03-09','荀子');
//（5）修改学生表（student），增加联系电话（phone）属性
alter table student add s_phone char(8);
//（6）为学生表（student）按出生日期字段建立索引，并练习删除索引
create index Stubirthday on student(s_birthday);
drop index Stubirthday;
[实验二]
//（1） 向教学管理系统的每个表中录入5条记录。录入时注意体会外键约束。
insert into department values('1','信息系统');
insert into department values('2','工商管理');
insert into department values('3','市场营销');
insert in
to department values('4','会计');
insert into department values('5','财务管理');
insert into class values('9501','信息','1');
insert into class values('9502','工商','2');
insert into class values('9503','营销','3');
insert into class values('9504','会计','4');
insert into class values('9505','财务','5');
insert into student values('95011','严二姣','男','1991-04-04','555.5',NULL,'9501','04313887');
insert into student values('95012','严傻姣','男','1995-06-07','666.6',NULL,'9501','04313889');
insert into student values('95013','严二傻','男','1992-11-19','777.7',NULL,'9501','04313886');
insert into student values('95014','严傻傻','男','1993-10-05','888.8',NULL,'9501','04313888');
insert into student values('95015','傻瓜严','女','1990-12-15','999.9',NULL,'9501','04313885');
insert into course values('2091','孔子');
insert into course values('2092','老子');
insert into course values('2093','庄子');
insert into course values('2094','墨子');
insert into course values('2090','荀子');
insert into choice values('95011','2091','100');
insert into choice values('95012','2092','19');
insert into choice values('95013','2093','59');
insert into choice values('95014','2094','58');
insert into choice values('95015','2090','57');
select * from choice;
insert into teaching values('2095','2091');
insert into teaching values('2096','2092');
insert into teaching values('2097','2093');
insert into teaching values('2098','2094');
insert into teaching values('2095','2090');
//(2） 创建教师表2(teacher2)，向表中录入5条不同的记录，注意与教师表中数据必须不同，主要体现在主键上。
create table teacher2
(t_no char(6) unique,
t_name char(8) not null,
t_sex char(2) check(t_sex='男' or t_sex='女'),
t_birthday datetime,
tech_title char(10),
);
select*from teacher2;
insert into teacher2 values('2100','驴大傻','男','1990-02-02','鉴宝');
insert into teacher2 values('2101','大傻驴','男','1995-03-03','考古');
insert into teacher2 values('2102','崴脚驴','男','1992-08-08','哲学');
insert into teacher2 values('2103','瘸驴','男','1998-08-09','人类起源');
insert into teacher2 values('2104','面板驴','女','1994-09-30','君主论');
select*from teacher2;
//（3）用多行数据插入的方法将教师表2中数据录入到教师表。
insert into teacher select * from teacher2;*****!!!!!
select *from teacher;
//（4）利用SELECT INTO方法创建教师表3(teacher3)，查看教师表3的内容，体会表间数据复制的含义。
select * into teacher3 from teacher2;
select * from teacher3;
//（5）删除教师表3中所有数据，删除教师表3。@@@@@!!!
delete teacher3;
drop table teacher3;
//（6）删除教师表2中数据，删除教师表2。
delete teacher2;
drop table teacher2;
select*from teacher2;检查
//（7）显示所有学生情况。
select*from student;
//（9）显
示与某个学生某门课程成绩相同的选修该门课程的所有学生的学号、姓名。
select t_no,t_name,t_birthday from teacher;
//（9）显示与某个学生某门课程成绩相同的选修该门课程的所有学生的学号、姓名。(自连接)
select bb.s_no,s_name
from student,choice aa,choice bb
where aa.s_no='95011'
and aa.course_no='2091'
and bb.course_no=aa.course_no
and bb.score=aa.score
and bb.s_no=student.s_no;
//（10）显示成绩在60分以上的学生姓名、教师姓名和课程名称。
select student.s_name ,t_name,course_name
from student,choice,teacher,course,teaching
where score>='60'
and choice.s_no=student.s_no
and choice.course_no=course.course_no
and choice.course_no=teaching.course_no
and teaching.t_no=teacher.t_no
select*from choice;
// （11）将某个学生所学课程成绩低于60分的课程列出来。
select course_name
from choice,course
where score
and choice.s_no='95012'
and choice.course_no=course.course_no;
//（12）将某门课程成绩低于60分的学生的成绩改为60分。
老师讲的：
updata choice
set score=60
where sno in(select sno
from student
where sname='李明')
and course_no in(select course _no
from course
where course_name='物理');
[实验三]
//（1）在课程选修表中录入所有学生的物理学习成绩
insert into course values('2001','物理');
select*from course;
select*from student;
insert into student values('95016','严三姣','男','1991-01-04','555.5',NULL,'9501','04313887');
insert into student values('95017','严四姣','男','1991-08-04','555.5',NULL,'9501','04313887');
insert into student values('95018','严五姣','男','1991-08-04','555.5',NULL,'9501','04313817');
insert into choice values('95011','2001','100');
insert into choice values('95012','2001','90');
insert into choice values('95013','2001','80');
insert into choice values('95014','2001','85');
//（2）按学生的物理成绩排序，显示学生表的所有属性信息及物理成绩
老师讲：
SELECT student.*,score
from student,choice,course
where student.s_no=choice.s_no
and course.course_no='2001'
and course.course_no=choice.course_no
order by score;
SELECTstudent.*,score
from student left join choice
on student.s_no=choice.s_no
where course_no='2001'
order by score;
我的：
select student.*,choice.score
from student left join choice
on choice.s_no=student.s_no
where course_no='2001'
order by score asc;
//（3）查询所有物理成绩在80分与90分之间的学生个数及平均分
我的：
select COUNT (*) sumstudent,AVG(score) grade
from choice
where course_no='2001'
and score>='80'
and score
老师讲：
select count(*),avg(score)
from choice,course
where course_name='物理'
and score between 80 and90 (between 包含左右边界)
and choice.course_no=c
ourse.course_no;
//（4）查询每个班级学生的物理最高成绩，显示出班级名称与最高成绩@@@@！！！！
我的：
select class.class_no ,class.class.name,max(score)
from class,choice,student
where course_no='2001'
and class.class_no=student.class_no
and student.s_no=choice.s_no
group by class.class_no,class.class.name;
//（5）使用嵌套子查询的方式，查询所有物理成绩在80分与90分之间的学生
我的：
select choice.s_no ,s_name ,score
from choice,student
where choice.s_no in(select s_no
from choice
where score>'80'
and score
and course_no='2001';
老师讲：
select*
from student
where s_no in (select s_no
from choice
where score between 80 and 90
and course_no =(select course_no
from course
where course_no='2001'))
//（6）查询所有学生总数在30人以上的班级信息
老师：
select student.class_no
from class,student
where class.class_no=student.class_no
group by student.class_no
having count(*)>=30 ;
我的：
select student.class_no
from class,student
where class.class_no=student.class_no
group by student.class_no
having count(*)>=30 ;
//(7)查询所有没有选择物理课的同学的学号和姓名
我：
select s_no,s_name
from student
where not exists
(select*
from choice
where s_no=student.s_no
and course_no='2001');
老师：
select *
from student
where s_no Ot in(select s_no
from choice
where course_no='2001')
// （8）对以上的数据查询语句(子查询那个)进行优化
select student.s_no,s_name
from student,choice,course
where student.s_no = choice.s_no
and course.course_no=choice.course_no
and course_name!=' 物理';
//（9）将所有物理成绩低于60的学生信息删除
我：
select s_no
into #F
from choice
where course_no='2001'
and score
delete
from choice
where course_no='2001'
and score
delete student
from student,#F
where student.s_no=#F.s_no;
老师：
select s_no
into #table1
from choice
where course_no='2001'
and score
selce *
frome #table1
delete
from choice
where s_no in (select s_no
from #table1);
delete
from student
where s_no in(select S_no
from #table1);
[实验四]
//1、查询既教物理课也教化学课的老师姓名
方法一：
select t_name
from teaching ,course,teacher
where course_name='物理'
and teaching.course_no=course.course_no
and teaching.t_no in(select t_no
from teaching,course
where course_name='孔子'
and teaching.course_no=course.course_no)
and teaching.t_no=teacher.t_no;
方法二：
select t_name
from teacher,teaching taa,teaching tbb,course
coua,course coub
where coua.course_name='物理'
and taa.course_no=coua.course_no
and coub.course_name='孔子'
and tbb.course_no=coub.course_no
and taa.t_no=teacher.t_no;
//2、创建显示学生成绩的视图，显示结果包含学生学号、学生姓名、课程号、课程名称、教师编号、教师姓名、成绩。
create view student_score
as select student.s_no,s_name,course.course_no,course_name,teacher.t_no,t_name,choice.score
from student,course,teacher,choice,teaching
where student.s_no=choice.s_no
and choice.course_no=course.course_no
and choice.course_no=teaching.course_no
and teaching.t_no=teacher.t_no
select *
from student_score
//3、修改该视图使其只查询出成绩不及格的相关信息。
drop view student_score;
create view student_score
as select student.s_no,s_name,course.course_no,course_name,teacher.t_no,t_name,choice.score
from student,course,teacher,choice,teaching
where student.s_no=choice.s_no
and choice.course_no=course.course_no
and choice.course_no=teaching.course_no
and teaching.t_no=teacher.t_no
and choice.score
//4、查询选修了A老师教授所有课程的学生
select s_name
from student
where not exists
(select*
from teaching
where t_no=2095
and not exists
(select *
from choice
where s_no=student.s_no
and choice.course_no=teaching.course_no));
//5、查询所有物理课成绩最高的同学学号（SNO）和姓名（SNAME）
select student.s_no,s_name,score
from student,choice,course
where course_name='物理'
and course.course_no=choice.course_no
and choice.s_no=student.s_no
and score>=(select max(score)
from choice,course
where course_name='物理'
and course.course_no=choice.course_no);
//6查询所有物理成绩及格但化学成绩不及格的同学学号（SNO）和姓名（SNAME）。
方法一：
select student.s_no,s_name
from student,choice cha,choice chb,course coua,course coub
where coua.course_name='物理'
and coua.course_no=cha.course_no
and cha.score>='60'
and coub.course_name='庄子'
and coub.course_no=chb.course_no
and chb.score
方法二：
select student.s_no,s_name
from student,choice ,course
where student.s_no=choice.s_no
and course_name='物理'
and course.course_no=choice.course_no
and choice.s_no in (select student.s_no
from student,choice,course
where student.s_no=choice.s_no
and course_name='庄子'
and course.course_no=choice.course_no);
//7查询所有选课门数大于6门的同学，并按选课门数降序排列。
select s_no
from choice
group by s_no
having count(*)>2
order by count(*) desc;
//8为课程表加入学分字段，查询所有选课学分大于
20分、选课门数大于9门，并且最多有一门成绩低于60分的同学。
alter table course add xf char(4);
update course set xf='2'
where course_no='2091';
update course set xf='5'
where course_no='2092';
update course set xf='3'
where course_no='2093';
update course set xf='7'
where course_no='2094';
update course set xf='1'
where course_no='2090';
update course set xf='9'
where course_no='2001';
select student.s_no
from student,choice
where score
and choice.s_no=student.s_no
and exists (select choice.s_no
from course,choice
where choice.course_no=course.course_no
and exists (select s_no
from choice
group by s_no
having COUNT(*)>3)
group by choice.s_no
having COUNT(xf)>2)
group by student.s_no
having COUNT(*)
//10、找出所选课程平均分最高的同学
select s_no
from choice
group by s_no
having AVG(score)>=(select AVG(score)
from choice
group by s_no)