SQL 50题刷题记录

上诉为数据表

答:
1
select * from student right join (
select t1.sid,class1,class2 from
(select sid,score as class1 from sc where cid=‘01’)as t1,
(select sid,score as class2 from sc where cid=‘02’)as t2
where t1.sid=t2.sid and t1.class1>t2.class2
)r
on student.sid=r.sid;

学习到的知识：
1.连接，外连接，左右连接，左右外连接
2.如何用查询建立表

1.1
select * from (select * from sc where sc.cid=‘01’)t1,
(select * from sc where sc.cid=‘02’)t2
where t1.sid=t2.sid;
1.2
明显要外连接，由题意是左外连接
select * from （select * from sc where sc.cid='01）t1 left join
(select * from sc where sc.cid=‘02’)t2
on t1.sid=t2.sid;

1.3
select * from sc where sid not in（
select sid from sc where cid=‘01’）;

2
select student.sid,sname,avgscore from student,(
select sid,avg(score) as avgscore from sc group by sid)t1
where student.sid=t1.sid and avgscore>60;

select student.sid,sname,avgscore from student,(
select sid,avg(score) as avgscore
from sc group by sid
having avg(score)>60)t1
where student.sid=t1.sid;
学习知识点：
having 语句的筛选

select * from student where sid in(select distinct sid from sc where score is not null);
在sc表中，score非主属性，可能为空

明显需要外连接
select student.sid,sname,num,totals from student left join
(select sid,sum(score) as totals,count (sid) as num from sc
group by sid)t1
on student.sid=t1.sid;
学习知识点：
count 对元组个数的统计

4.1
select * from student where sid in(select distinct sid from sc );

select * from student where exists（select * from sc where sc.sid=student.sid）;
学习知识点：
exist 和 in 的效率 ，exist的返回值只是真假

5
select count（tname）from teacher where tname like ‘李%’;

select * from student where student.sid in(
select sc.sid from sc where sc.cid=(select course.cid from course,teacher where course.tid=teacher.cid and tname=‘张三’)
);
多表联合查询
select student.* from student,course，teacher，sc
where student.sid =sc.sid and sc.cid =course.cid
and course.tid=teacher.cid and tname=‘张三’;
学习知识点：
多表联合查询的应用

答：
7.
select * from student where student.sid not in(
select sc.sid from sc group by sc.sid having count (sc.sid)=(
select count() from course));
8.
select distinct student. from student,sc where student.sid=sc.sid and cid in(
select cid from sc where sid=‘01’)
);

select * from student where sid in(
select sid from sc where cid in(select cid from sc where sid=‘01’) and sid !=‘01’ group by sid having count(cid) = (select count(cid) from sc where sid = ‘01’ group by sid) )
);
cid在01中且课程数相同
学习知识点：
怎么间接表示=all

select sname from student where sid not in （
select sid from sc，course，teacher where sc.cid=course.cid and course.id=teacher.tid and tname=‘张三’
）；
11.
select student.sid,sanme,avgs from student,(
select sid,avg(score) as avgs from sc where score<60 group by sid having by count(score)>1)r
where student.sid=r.sid;
这里平均成绩不清楚的是是不及格课程的平均成绩和全部课程的平均成绩，如果是全部利用选出sid再求平均
学习知识点：
明白了where，和having的区别：where是分组前的筛选，having是分组后的筛选
12.
select student.* from student，sc
where student.sid=sc.sid and cid=‘01’ and score<60
order by score desc;
13.
select * from student left join
(select sid,score as class1 from sc where cid=‘01’) t1 on student.sid=t1.sid
left join (select sid,score as class1 from sc where cid=‘02’) t2 on
student.sid=t2.sid
left join (select sid,score as class1 from sc where cid=‘03’) t3 on
student.sid=t3.sid
left join (select sid,avg（score）as avgs from sc group by sid)r on
student.sid=r.sid
order by avgs desc;
学习知识点：
如何进行多表左连接

未完待续