作業(yè)需要數據表SQL語句已給

?1. 查詢" 01 "課程比" 02 "課程成績高的學生的信息及課程分數

select * from Student RIGHT JOIN (
    select t1.SId, class1, class2 from
          (select SId, score as class1 from sc where sc.CId = '01')as t1, 
          (select SId, score as class2 from sc where sc.CId = '02')as t2
    where t1.SId = t2.SId AND t1.class1 > t2.class2
)r on Student.SId = r.SId;

?1.1 查詢同時存在" 01 "課程和" 02 "課程的情況

select * from 
    (select * from sc where sc.CId = '01') as t1, 
    (select * from sc where sc.CId = '02') as t2
where t1.SId = t2.SId;

1.2 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null )

select * from 
(select * from sc where sc.CId = '01') as t1
left join 
(select * from sc where sc.CId = '02') as t2
on t1.SId = t2.SId;

1.3 查詢不存在" 01 "課程但存在" 02 "課程的情況

select * from sc
where sc.SId not in (
    select SId from sc 
    where sc.CId = '01'
) and sc.CId= '02';

2.查詢平均成績大于等于 60 分的同學的學生編號和學生姓名和平均成績

select student.SId,sname,ss from student,(
    select SId, AVG(score) as ss from sc  
    group by SId 
    having AVG(score)> 60
    )r where student.sid = r.sid;

3.查詢在 SC 表存在成績的學生信息

select distinct student.*
from student,sc
where student.SId=sc.SId

4.查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績(沒成績的顯示為 null )

select s.sid, s.sname,r.coursenumber,r.scoresum
from (
    (select student.sid,student.sname from student)s 
    left join 
    (select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc group by sc.sid)r 
    on s.sid = r.sid);

?文章來源地址http://www.zghlxwxcb.cn/news/detail-822426.html

4.1 查有成績的學生信息

select * from student where exists (select sc.sid from sc where student.sid = sc.sid);

5.查詢「李」姓老師的數量

select count(*) from teacher where tname like '李%';

6.查詢學過「張三」老師授課的同學的信息

select student.* from student,teacher,course,sc
where 
    student.sid = sc.sid 
    and course.cid=sc.cid 
    and course.tid = teacher.tid 
    and tname = '張三';

7.查詢沒有學全所有課程的同學的信息

select * from student
where student.sid not in (
  select sc.sid from sc
  group by sc.sid
  having count(sc.cid)= (select count(cid) from course));

8.查詢至少有一門課與學號為" 01 "的同學所學相同的同學的信

select * from student 
where student.sid in (
    select sc.sid from sc 
    where sc.cid in(
        select sc.cid from sc 
        where sc.sid = '01'));

9.查詢和" 01 "號的同學學習的課程 完全相同的其他同學的信息

select * from student where sid in(
select a.sid from (select sid,group_concat(cid order by cid) as courses 
from sc group by sid) as a
inner join (select sid,group_concat(cid order by cid) as courses 
from sc group by sid having sid='01') as b
on a.sid != 1 and a.courses = b.courses);

10.查詢沒學過"張三"老師講授的任一門課程的學生姓名

select * from student
where student.sid not in(
    select sc.sid from sc,course,teacher 
    where
        sc.cid = course.cid
        and course.tid = teacher.tid
        and teacher.tname= "張三");

11.查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績

select student.sid, student.Sname,b.avg
from student right join
(select sid, AVG(score) as avg from sc
    where sid in (
              select sid from sc 
              where score<60 
              group by sid 
              having count(score)>1)
    group by sid) b on student.sid=b.sid;

12.檢索" 01 "課程分數小于 60，按分數降序排列的學生信息

select student.*, sc.score from student, sc
where student.sid = sc.sid
and sc.score < 60
and cid = "01"
order by sc.score desc;

13.按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績

select *  from sc 
left join (
    select sid,avg(score) as avscore from sc 
    group by sid
    )r 
on sc.sid = r.sid
order by avscore desc;

14.查詢各科成績最高分、最低分和平均分：

以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率;及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90;要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列

select 
sc.CId ,
max(sc.score)as 最高分,
min(sc.score)as 最低分,
AVG(sc.score)as 平均分,
count(*)as 選修人數,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 優(yōu)良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 優(yōu)秀率 
from sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC

15.按各科成績進行排序，并顯示排名， Score 重復時保留名次空缺

SELECT *,row_number() over (PARTITION BY cid ORDER BY score DESC) AS '排名'FROM sc;

15.1 按各科成績進行排序，并顯示排名， Score 重復時合并名次

select *,
case when @sco=score then @rank else @rank:=@rank+1 end as rn,
@sco:=score
from sc,(select @rank:=0,@sco:=NULL) t
order by score desc;

16.查詢學生的總成績，并進行排名，總分重復時保留名次空缺

select a.*,
@rank:=if(@sco=scos,'',@rank+1) as rn,
@sco:=scos
from
(select sid,sum(score) as scos from sc
group by sid order by scos desc) a,
(select @rank:=0,@sco:=NULL) b;

16.1 查詢學生的總成績，并進行排名，總分重復時不保留名次空缺

select a.*,
@rank:=if(@sco=scos,@rank,@rank+1) as rn,
@sco:=scos
from
(select sid,sum(score) as scos from sc
group by sid order by scos desc) a,
(select @rank:=0,@sco:=NULL) b;

17.統(tǒng)計各科成績各分數段人數：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

select course.cname, course.cid,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]"
from sc left join course on sc.cid = course.cid group by sc.cid;

18.查詢各科成績前三名的記錄

select * from sc
where (
select count(*) from sc as a 
where sc.cid = a.cid and sc.score<a.score 
)< 3 order by cid asc, sc.score desc;

19.查詢每門課程被選修的學生數

select cid, count(sid) from sc  group by cid;

20.查詢出只選修兩門課程的學生學號和姓名

select student.SId,student.Sname
from sc,student
where student.SId=sc.SId  
group by sc.SId
having count(*)=2；

21.查詢男生、女生人數

select ssex, count(*) from student group by ssex;

22.查詢名字中含有「風」字的學生信息

select * from student  where student.Sname like '%風%';

23.查詢同名同性學生名單，并統(tǒng)計同名人數

select sname, count(*) from student group by sname having count(*)>1;

24.查詢 1990 年出生的學生名單

select * from student where YEAR(student.Sage)=1990;

25.查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列

select sc.cid, course.cname, AVG(SC.SCORE) as average from sc, course
where sc.cid = course.cid group by sc.cid order by average desc,cid asc;

26.查詢平均成績大于等于 85 的所有學生的學號、姓名和平均成績

select student.sid, student.sname, AVG(sc.score) as aver from student, sc
where student.sid = sc.sid group by sc.sid having aver > 85;

27.查詢課程名稱為「數學」，且分數低于 60 的學生姓名和分數

select student.sname, sc.score from student, sc, course
where student.sid = sc.sid and course.cid = sc.cid and course.cname = "數學" and sc.score < 60;

28.查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況）

select student.sname, cid, score from student left join sc on student.sid = sc.sid;

29.查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數

select student.sname, course.cname,sc.score from student,course,sc
where sc.score>70 and student.sid = sc.sid and sc.cid = course.cid;

30.查詢不及格的課程

select distinct sc.CId from sc where sc.score <60;

31.查詢課程編號為 01 且課程成績在 80 分以上的學生的學號和姓名

select student.sid,student.sname from student,sc
where cid="01" and score>=80 and student.sid = sc.sid;

32.求每門課程的學生人數

select sc.cid,count(*) as 學生人數 from sc group by sc.cid;

33.成績不重復，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績

select student.*, sc.score, sc.cid from student, teacher, course,sc 
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "張三"
order by score desc
limit 1;

34.成績有重復的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績

select student.*, sc.score, sc.cid from student, teacher, course,sc 
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "張三"
and sc.score = (
    select Max(sc.score) 
    from sc,student, teacher, course
    where teacher.tid = course.tid
    and sc.sid = student.sid
    and sc.cid = course.cid
    and teacher.tname = "張三"
);

35.查詢不同課程成績相同的學生的學生編號、課程編號、學生成績

select  a.cid, a.sid,  a.score from sc as a
inner join 
sc as b
on a.sid = b.sid
and a.cid != b.cid
and a.score = b.score
group by cid, sid;

36.查詢每門功成績最好的前兩名

select a.sid,a.cid,a.score from sc as a left join sc as b 
on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid having count(b.cid)<2 order by a.cid;

37.統(tǒng)計每門課程的學生選修人數（超過 5 人的課程才統(tǒng)計）。

select sc.cid, count(sid) as cc from sc group by cid having cc >5;

38.檢索至少選修兩門課程的學生學號

select sid, count(cid) as cc from sc group by sid having cc>=2;

39.查詢選修了全部課程的學生信息

select student.* from sc ,student where sc.SId=student.SId group by sc.SId having count(*) = (select distinct count(*) from course )

40.查詢各學生的年齡，只按年份來算

select *, (year(now()) - year(sage)) as age from student;

41.按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一

select *, timestampdiff(YEAR,sage,NOW()) as age from student;

42.查詢本周過生日的學生

select * from student where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());

43.查詢下周過生日的學生

select * from student where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;

44.查詢本月過生日的學生

select * from student where MONTH(student.Sage)=MONTH(CURDATE());

45.查詢下月過生日的學生

select * from student where MONTH(student.Sage)=MONTH(CURDATE())+1;

?

?

到了這里，關于MySQL45道練習題的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！