Mysql练习题二

有三个表s(学生表), C(课程表) , SC(学生选课表)

• S(SNO, SNAME)代表 (学号，姓名)
• C(CNO, CNAME, CTEACHER) 代表(课号， 课名， 教师)
• SC(SNO, CNO, SCGRADE) 代表(学号， 课号， 成绩)

create table s(
    sno  int(4) primary key auto_increment,
    sname varchar(32)
);
crate table c(
    con int(4) primary key auto_increment,
    cname varchar(32),
    cteacher varchar(32)
);

create table sc(
    sno int (4),
    cno int(4),
    scgrade double(3, 1),
    constraint sc_sno_cno_pk primary key(sno, cno),
    constraint sc_sno_fk foreign key(sno) references s(sno), //需要使用其他表的字段，所以在此处增加一个外键的约束
    constraint sc_cno_fk foreign key(cno) references c(cno)
);

 1.  找出“黎明”老师教过的课程编号

select 
	cno
from
	c
where
	cteacher = "黎明"

2.  找出所有选过”黎明”老师的学生编号

select
	distinct sno
from
	sc
where 
 	cno 
in
	(select cno from c where cteacher = "黎明")；

3. 列出2门以上(含2门)不及格学生姓名及平均成绩

第一步:先找出不及格门数大于2的学生编号和姓名

select
	sc.sno, s.name, count（*） as studentNum
from
	sc
join
	s
on 
	sc.sno = s.sno
where
	sc.scgrade < 60
group by
	sc.sno, s.name
having
	studentNum >= 2;

作为临时表t1

第二步:找平均值，作为临时表t2

select
	sc.sno, avg(sc.scgrade) as avgscgradge
from
	sc
group by
	sc.sno

第三步;将t1表和t2表进行连接

select
	t1.sname, t2.avgscgradge
from
	t1
join
	t2
on
	t1.sno = t2.sno

找出即学过1号课程又学过2号课程所有学生的姓名

select 
    sno, s.sname
from 
    sc 
join
    s
on 
    sc.sno = s.sno
where 
    cno = 1
and 
    sc.sno 
in
    (select sno from sc where cno = 2);