数据库查询详细解答

--建立学生表
create table stud (
   stno char(3) primary key,
   name char(4),
   sex char(2),
   age int,
   classid char(5)
);

insert into stud values('108','曾华','男',19,'95033');
insert into stud values('105','匡明','男',20,'95031');
insert into stud values('107','王丽','女',20,'95033');
insert into stud values('101','李军','男',19,'95033');
insert into stud values('109','王芳','女',22,'95031');
insert into stud values('103','陆君','男',20,'95031');

--建立教师表
create table teacher(
   tcno char(3) primary key,
   name char(4),
   sex char(2),
   age int ,
   lev char(6),
   speciality char(8)
);

insert into teacher values('804','李成','男',42,'副教授','计算机系');
insert into teacher values('856','张旭','男',35,'讲师','电子工程');
insert into teacher values('825','王萍','女',28,'助教','计算机系');
insert into teacher values('831','刘冰','女',25,'助教','电子工程');

--建立课程表

create table course (
   cuid char(5) primary key,
   cuname char(10),
   tcno char(3),
   foreign key(tcno) references teacher(tcno)
);

insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');

--建立选课表
create table sc(
   stno char(3),
   cuid char(5),
   primary key(stno,cuid),
   score int,
   foreign key(stno) references stud(stno),
   foreign key(cuid) references course(cuid)
);

insert into sc values('103','3-245',86);
insert into sc values('105','3-245',75);
insert into sc values('109','3-245',68);
insert into sc values('103','3-105',92);
insert into sc values('105','3-105',88);
insert into sc values('109','3-105',76);
insert into sc values('101','3-105',64);
insert into sc values('107','3-105',91);
insert into sc values('108','3-105',78);
insert into sc values('101','6-166',85);
insert into sc values('107','6-166',79);
insert into sc values('108','6-166',81);

--查询操作

--1.查询所有内容
select * from stud;
select * from teacher;
select * from course;
select * from sc;

--2.查询选修课程'3-105'且成绩在60到80之间的所有记录
select * from sc where cuid = '3-105' and score between 60 and 80;

--3.查询成绩为85、86或88的记录
select * from sc where score in(85,86,88);

--4.查询'95031'班的学生人数
select count(*) as 学生人数 from stud where classid='95031';

--5.查询最低分大于70,且最高分小于90的学号列
select stno from sc group by stno having min(score)>70 and max(score)<90;

--6.查询平均分大于80分的学生的成绩表
select stno 学号,avg(score) 平均成绩 from sc group by stno having avg(score)>80;

--7.查询'95033'班每个学生所选课程的平均分
select * from stud where classid='95033'

select stno 学号,avg(score) 平均分 from sc 
       where stno in (select stno from stud where classid='95033') 
       group by stno;

--8.以选修 '3-105'为例,查询成绩高于'109'号同学的所有同学的记录。
select * from stud where stno in (
select stno from sc 
       where cuid='3-105' and score > (select score from sc where cuid='3-105' and stno='109'));

--9.查询与学号为'108'的同学同岁的所有学生的学号、姓名和年龄。
select stno,name,age from stud where age=(select age from stud where stno='108');

--10.查询'张旭'教师任课的课程号，选修其课程学生的学号和成绩
select stno,score from sc 
       where cuid in (select cuid from course where tcno = (select tcno from teacher where name='张旭'));

--11.查询选修其课程的学生人数多于5人的教师姓名
select cuid from sc group by cuid having count(cuid)>5;

select name from teacher where tcno in (
       select tcno from course where cuid in (select cuid from sc group by cuid having count(cuid)>5)
       );