mysql练习题1

drop table if exists student;
drop table if exists teacher;
drop table if exists course;
drop table if exists sc;

create table student(
sno varchar(10) primary key,
sname varchar(20),
sage int,
ssex varchar(5)
);

create table teacher(
tno varchar(10) primary key,
tname varchar(20)
);

create table course(
cno varchar(10),
cname varchar(20),
tno varchar(20),
constraint pk_course primary key (cno,tno)
);

create table sc(
sno varchar(10),
cno varchar(10),
score decimal(8,2),
constraint pk_sc primary key (sno,cno)
);

/*初始化学生表的数据/
insert into student values (‘s001’,‘张三’,23,‘男’);
insert into student values (‘s002’,‘李四’,23,‘男’);
insert into student values (‘s003’,‘吴鹏’,25,‘男’);
insert into student values (‘s004’,‘琴沁’,20,‘女’);
insert into student values (‘s005’,‘王丽’,20,‘女’);
insert into student values (‘s006’,‘李波’,21,‘男’);
insert into student values (‘s007’,‘刘玉’,21,‘男’);
insert into student values (‘s008’,‘萧蓉’,21,‘女’);
insert into student values (‘s009’,‘陈萧晓’,23,‘女’);
insert into student values (‘s010’,‘陈美’,22,‘女’);

/初始化教师表*****/
insert into teacher values (‘t001’, ‘刘阳’);
insert into teacher values (‘t002’, ‘谌燕’);
insert into teacher values (‘t003’, ‘胡明星’);

/初始化课程表*************/
insert into course values (‘c001’,‘J2SE’,‘t002’);
insert into course values (‘c002’,‘Java Web’,‘t002’);
insert into course values (‘c003’,‘SSH’,‘t001’);
insert into course values (‘c004’,‘Oracle’,‘t001’);
insert into course values (‘c005’,‘SQL SERVER 2005’,‘t003’);
insert into course values (‘c006’,‘C#’,‘t003’);
insert into course values (‘c007’,‘JavaScript’,‘t002’);
insert into course values (‘c008’,‘DIV+CSS’,‘t001’);
insert into course values (‘c009’,‘PHP’,‘t003’);
insert into course values (‘c010’,‘EJB3.0’,‘t002’);

/初始化成绩表********/
insert into sc values (‘s001’,‘c001’,78.9);
insert into sc values (‘s002’,‘c001’,80.9);
insert into sc values (‘s003’,‘c001’,81.9);
insert into sc values (‘s004’,‘c001’,60.9);
insert into sc values (‘s001’,‘c002’,82.9);
insert into sc values (‘s002’,‘c002’,72.9);
insert into sc values (‘s003’,‘c002’,81.9);
insert into sc values (‘s001’,‘c003’,59);
insert into sc values (‘s001’,‘c004’,78.9);
insert into sc values (‘s002’,‘c004’,80.9);
insert into sc values (‘s003’,‘c005’,81.9);
insert into sc values (‘s004’,‘c005’,60.9);
insert into sc values (‘s001’,‘c006’,82.9);
insert into sc values (‘s002’,‘c006’,72.9);
insert into sc values (‘s003’,‘c007’,81.9);
insert into sc values (‘s001’,‘c007’,59);
insert into sc values (‘s005’,‘c001’,80.9);
insert into sc values (‘s006’,‘c001’,81.9);
insert into sc values (‘s007’,‘c001’,90.9);
insert into sc values (‘s004’,‘c002’,75);
insert into sc values (‘s005’,‘c002’,71.2);
insert into sc values (‘s006’,‘c002’,85.9);
insert into sc values (‘s007’,‘c003’,59);
insert into sc values (‘s004’,‘c004’,77.9);
insert into sc values (‘s005’,‘c004’,84.9);
insert into sc values (‘s006’,‘c005’,82.9);
insert into sc values (‘s007’,‘c005’,67.9);
insert into sc values (‘s004’,‘c006’,62.9);
insert into sc values (‘s006’,‘c006’,92.9);
insert into sc values (‘s007’,‘c007’,35.9);
insert into sc values (‘s008’,‘c007’,69);
1.查询“c001”课程比“c002”课程成绩高的所有学生的学号；

select a.sno
FROM
(SELECT * from sc a where a.cno='c001' ) a,
(SELECT * from sc b where b.cno='c002' ) b
where a.sno=b.sno and a.score>b.score;

select sno
FROM sc a
where cno='c001' and EXISTS(SELECT sno from sc b where b.sno=a.sno and b.cno='c002' and a.score>b.score);

2.查询平均成绩大于60 分的同学的学号和平均成绩；

select sno,avg(score)
from sc
GROUP BY sno
HAVING AVG(score)>60;

3.查询所有同学的学号、姓名、选课数、总成绩；

-- 注意必须是student.sno sno是student绝对全有，sc不一定
select student.sno,sname,count(cno),sum(score)
from student left join sc on (sc.sno=student.sno)
GROUP BY student.sno;

4.查询姓“刘”的老师的个数；

select count(tname)
from teacher
where tname like '刘%';

5.查询没学过“谌燕”老师课的同学的学号、姓名；

select sno,sname 
from student
where sno not in (
select sno
from sc
where cno in(
select cno
from course
where tno in(
select tno
from teacher
where tname = '谌燕')));

6.查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

select sno,sname
from student
where sno in(
select sno
from sc
where cno='c001' and sno in(
select sno
from sc
where cno='c002'));

7.查询学过“谌燕”老师所教的所有课的同学的学号、姓名；

select DISTINCT(student.sno),sname 
from sc,student,teacher,course
where sc.sno = student.sno and sc.cno = course.cno and course.tno = teacher.tno AND tname = '谌燕';

select sno,sname 
from student
where sno in (
select sno
from sc
where cno in(
select cno
from course
where tno in(
select tno
from teacher
where tname = '谌燕')))