SQL经典50题找找手感

前10题瞎写

/*
select * from Student S,
(select s_id ,s_score from Score where c_id = '01') as S1,
(select s_id ,s_score from Score where c_id = '02') as S2
where S1.s_id = S2.s_id and S.s_id=S2.s_id and S1.s_score > S2.s_score;
*/

/*
select * from Student S  join 
	(select A.s_id,class1,class2 from 
	(select s_id ,s_score as class1 from Score Sc where Sc.c_id = '01') as A,
	(select s_id ,s_score as class2 from Score Sc where Sc.c_id = '02') as B 
	where class1 > class2 and A.s_id = B.s_id ) SD 
on S.s_id = SD.s_id; 
*/


#平均成绩大于60分
/*select * from Student S right join 
(select Sc.s_id,AVG(Sc.s_score) a from Score Sc group by Sc.s_id having a>60) SD on S.s_id=SD.s_id;
*/

#查询成绩不存在的学生信息
/*select * from Student S right join 
(select SC.s_id from Score SC where s_score = 0) SD
on S.s_id = SD.s_id;
*/

#查询所有同学的信息 选课总数 成绩总和
/*
select S.s_id,S.s_name,SD.class,SD.sumscore from Student S join 
(select s_id,count(*) as class , sum(s_score) as sumscore from Score SC group by Sc.s_id) as SD on SD.s_id=S.s_id;
*/

#查询所有同学的信息 选课总数 成绩总和 没有成绩和没有选课的也列出来
/*select S.s_id,S.s_name,SD.class,SD.sumscore from Student S left join 
(select s_id,count(*) as class , sum(s_score) as sumscore from Score SC group by Sc.s_id) as SD on SD.s_id=S.s_id;
*/

#查询名为 “一”的老师
/*
select count(*) from Teacher where t_name like '%一%';
*/

#查询学过张三老师授课的同学
/*
select * from Score ,(select c_id from Course where t_id=(select T.t_id from Teacher T where T.t_name like '教一')) as SD where Score.c_id=SD.c_id;
*/

#利用多表查询
/*
select S.* from  Student S,Score SC,Course C,Teacher T
	where	T.t_id = C.t_id
	AND C.c_id=SC.c_id
	AND SC.s_id=S.s_id
	AND T.t_name = '教一';
*/

#查询没有学全的学生信息
/*
select S.* from Student S where s_id not in 
	(select s_id from Score group by s_id 
		having count(c_id)=(select count(*) from Course));
*/

#查询至少有一么课程与01号学员相同
/*
select Distinct s_id from Score where c_id
in (select c_id from Score where s_id='1001');
*/

#查询没学过教三老师的学生
/*
select * from Student where s_id not in (
select SC.s_id from Teacher T , Course C , Score SC where 
	SC.c_id = C.c_id
	AND T.t_id = C.t_id
	AND T.t_name = '教三');
*/

#查询两门及其以上不合格的学生信息
#select s_id from Score where s_score<'60' GROUP BY s_id having count(*) > 1;