北京动力节点MySQL课程34道SQL经典题

| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
select * from salgrade;
+-------+-------+-------+
);
insert into sc(sno,cno,scgrade) values(1,1,30);
insert into sc(sno,cno,scgrade) values(1,2,50);
insert into sc(sno,cno,scgrade) values(1,3,80);
insert into sc(sno,cno,scgrade) values(1,4,90);
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)
13.有3个表S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1、找出没选过“黎明”老师的所有学生姓名。
2、列出2门以上（含2门）不及格学生姓名及平均成绩。
e.sal > t.avgsal;
+--------+-------+
| deptno | ename |
+--------+-------+
| 30 | ALLEN |
| 20 | JONES |
| 30 | BLAKE |
| 20 | SCOTT |
select sno from sc where cno = 2;
select
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);
select * from s where sno not in(select sno from sc where cno = (select cno from c where cteacher = '黎明'));
--列出2门以上（含2门）不及格学生姓名及平均成绩
t1
select
sc.sno,s.sname,count(*) as studentNum
insert into sc(sno,cno,scgrade) values(4,3,50);
insert into sc(sno,cno,scgrade) values(4,4,80);
/*
1、找出没选过“黎明”老师的所有学生姓名。
2、列出2门以上（含2门）不及格学生姓名及平均成绩。
3、即学过1号课程又学过2号课所有学生的姓名。
*/
--先找出选过黎明老师的学生编号 -> 黎明老师的授课的编号
select cno from c where cteacher = '黎明';
select sno from sc where cno = (select cno from c where cteacher = '黎明');
sc.sno,s.sname
having
studentNum >= 2) t1
join
(select
sc.sno,avg(sc.scgrade) as avgscgrade
cteacher varchar(32)
);
insert into c(cname,cteacher) values('Java','吴老师');
insert into c(cname,cteacher) values('C++','王老师');
insert into c(cname,cteacher) values('C##','张老师');
insert into c(cname,cteacher) values('MySQL','郭老师');
insert into c(cname,cteacher) values('Oracle','黎明');
create table sc(
sno int(4),
cno int(4),
insert into sc(sno,cno,scgrade) values(1,5,70);
insert into sc(sno,cno,scgrade) values(2,2,80);
insert into sc(sno,cno,scgrade) values(2,3,50);
insert into sc(sno,cno,scgrade) values(2,4,70);
from
emp e
group by
e.deptno;
将以下查询结果当成临时表t(deptno,avgsal)
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+---------+
将以上查询结果当成一个临时表t(deptno,maxsal)
select
e.deptno,e.ename,t.maxsal,e.sal
from
(select
e.deptno,max(e.sal) as maxsal
from
emp e
group by
e.deptno)t
join
emp e
on
t.deptno = e.deptno
where
t.maxsal = e.sal
order by
from ຫໍສະໝຸດ sc join s
on
sc.sno = s.sno
where
scgrade < 60
group by
insert into s(sname) values('wangwu');
insert into s(sname) values('zhaoliu');
create table c(
cno int(4) primary key auto_increment,
cname varchar(32),
from
sc
group by
sc.sno) t2
on
t1.sno = t2.sno;
--即学过1号课程又学过2号课所有学生的姓名
select sno from sc where cno = 1;
e.deptno;
+--------+-------+---------+---------+
| deptno | ename | maxsal | sal |
+--------+-------+---------+---------+
| 10 | KING | 5000.00 | 5000.00 |
insert into sc(sno,cno,scgrade) values(2,5,80);
insert into sc(sno,cno,scgrade) values(3,1,60);
insert into sc(sno,cno,scgrade) values(3,2,70);
insert into sc(sno,cno,scgrade) values(3,3,80);
| 20 | SCOTT | 3000.00 | 3000.00 |
| 20 | FORD | 3000.00 | 3000.00 |
| 30 | BLAKE | 2850.00 | 2850.00 |
+--------+-------+---------+---------+
3、即学过1号课程又学过2号课所有学生的姓名。
create table s(
sno int(4) primary key auto_increment,
sname varchar(32)
);
insert into s(sname) values('zhangsan');
insert into s(sname) values('lisi');
1.取得每个部门最高薪水的人员名称