SQL 50题刷题记录

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;

select * from (select * from sc where sc.cid=‘01’)t1,
(select * from sc where sc.cid=‘02’)t2
where t1.sid=t2.sid;

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;

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

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;

select * from student where sid in(select distinct sid from sc where score is not null);

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;

select * from student where sid in(select distinct sid from sc );
select * from student where exists（select * from sc where sc.sid=student.sid）;

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=‘张三’;

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));

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) )
);

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=‘张三’
）；

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;

select student.* from student，sc
where student.sid=sc.sid and cid=‘01’ and score<60
order by score desc;

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;