SQL 50题

//SQL 50 题 练习

select * from fiftysql.student;
select * from fiftysql.sc;
select * from fiftysql.teacher;
select * from fiftysql.course;


//Question 1 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select sc.sid, sc.score, sc.cid, course.cname from fiftysql.sc
inner join fiftysql.course
on sc.cid = course.cid;

select sc.sid, sc.cid, sc.score from fiftysql.sc where sc.cid = '01';   // choose the 
select sc.sid, sc.cid, sc.score from fiftysql.sc where sc.cid = '02';

select a1.sid,a1.cid,a1.score, course.cname from
(select sc.sid, sc.cid, sc.score from fiftysql.sc where sc.cid = '01') as a1
inner join fiftysql.course  
on a1.cid = course.cid;

select 
a.sname,
a.sage,
a.ssex,
b1.cname as course_1_name,
b1.score as course_1_score,
c1.cname as course_2_name,
c1.score as course_2_score
from 
(
  select a1.sid,a1.cid,a1.score, course.cname from
  (
    select sc.sid, sc.cid, sc.score 
    from fiftysql.sc where sc.cid = '01'
  ) as a1
  inner join fiftysql.course  
  on a1.cid = course.cid
) as b1

inner join
(
  select a2.sid,a2.cid,a2.score, course.cname from
  (
    select sc.sid, sc.cid, sc.score 
    from fiftysql.sc 
    where sc.cid = '02'
  ) as a2
  inner join fiftysql.course  
  on a2.cid = course.cid
) as c1
on b1.sid = c1.sid and b1.score > c1.score
inner join 
fiftysql.student as a
on b1.sid = a.sid;


// Question 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
select a.sid, a.cid, b.cid from 
(select sid, cid from fiftysql.sc where cid = '01') as a
inner join (select sid, cid from fiftysql.sc where cid = '02') as b
on a.sid = b.sid;


//1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select a.sid, a.cid, b.cid from 
(select sid, cid from fiftysql.sc where cid = '01') as a
left join (select sid, cid from fiftysql.sc where cid = '02') as b
on a.sid = b.sid;

//1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
select a.sid, a.cid, b.cid from 
(select sid, cid from fiftysql.sc where cid = '02') as a
left join (select sid, cid from fiftysql.sc where cid = '01') as b
on a.sid = b.sid;

//2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select sid, sum(sc.score) as total_score from fiftysql.sc group by sid;

select sid, avg(sc.score) as avg_score from fiftysql.sc group by sid;

select a.sid, a.avg_score, b.sname 
from
(select sid, avg(sc.score) as avg_score from fiftysql.sc group by sid) as a
inner join fiftysql.student as b
on b.sid = a.sid
where a.avg_score > 60;


//3.查询在 SC 表存在成绩的学生信息
select distinct student.* from fiftysql.student, fiftysql.sc where student.sid=sc.sid;  


//4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select 
b.sid,
b.sname,
a.sum_score, 
a.course_nums
from 
fiftysql.student as b
left join
(select sid, sum(score) as sum_score, count(cid) as course_nums from fiftysql.sc group by sid) as a
on b.sid = a.sid;

//4.1 查有成绩的学生信息
select 
b.sid,
b.sname,
a.sum_score, 
a.course_nums
from 
fiftysql.student as b
right join
(select sid, sum(score) as sum_score, count(cid) as course_nums from fiftysql.sc group by sid) as a
on b.sid = a.sid;

//5.查询「李」姓老师的数量
select 
count(a.name) as nums from
(select tname as name from fiftysql.teacher where tname like '李%') as a;

select count(tname) as numbers from fiftysql.teacher where tname like '李%';
select count(sname) as numbers from fiftysql.student where sname like '李%';

select * from fiftysql.teacher;

//6.查询学过「张三」老师授课的同学的信息
select student.sid, student.sname,student.sage,student.ssex from fiftysql.student
inner join 
(select sid from fiftysql.sc,
(select cid from fiftysql.course as b
inner join
(select tid from fiftysql.teacher where tname = '张三') as a
on a.tid = b.tid) as c
where sc.cid = c.cid) as d
on student.sid = d.sid;

select count(cid) from fiftysql.sc where cid = '02';

//7.查询没有学全所有课程的同学的信息

select sid,sname,sage,ssex from fiftysql.student    // 不在集合里使用not in
where student.sid not in
(select b.sid from
(select count(cid) as courses_amt_course from fiftysql.course) as a,
(select sid, count(cid) as courses_amt from fiftysql.sc group by sid) as b
where a.courses_amt_course = b.courses_amt)
order by student.sid;


select c.stu_l_a from 
(select b.sid as stu_l_a, b.courses_Amt from
(select count(cid) as courses_amt_course from fiftysql.course) as a,
(select sid, count(cid) as courses_amt from fiftysql.sc group by sid) as b
where a.courses_amt_course = b.courses_amt) as c;

//8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
/*****
1. 选出01 同学所学的所有课程
2. 找出选相同课程的学生
3. 打印出选相同课程学生的信息
******/ 
select sid, cid from fiftysql.sc where sid = '01'; 

select sname, student.sid, sage,ssex from
(select distinct sc.sid as sid from fiftysql.sc,
(select sid, cid from fiftysql.sc where sid = '01') as a
where sc.cid = a.cid and sc.sid != a.sid) as b
left join 
fiftysql.student
on student.sid = b.sid;