2023-04-02 練習(xí)題二

1.查詢(xún)各個(gè)班級(jí)的班級(jí)名稱(chēng)、總成績(jī)、平均成績(jī)、及格率（按平均成績(jī)從大到小排序）。

SELECT
    cid,
    caption,
    sum( num ),
    avg( num ) ,
    round(100 * SUM( CASE WHEN ifnull( num, 0 ) >= 60 THEN 1 ELSE 0 END ) / COUNT( * ),2) as 及格率
FROM
    class 
    LEFT JOIN student  ON class.cid = student.class_id
    LEFT JOIN score  ON  score.student_id = student.sid 
GROUP BY
    cid
order by avg( num ) desc;

2.查詢(xún)學(xué)過(guò) “波多” 老師課的同學(xué)的學(xué)號(hào)、姓名。

SELECT
    student_id,sname
FROM
    teacher
    LEFT JOIN course  ON teacher.tid = course.teacher_id    
    LEFT JOIN  score ON  score.course_id = course.cid 
    LEFT JOIN  student ON  score.student_id = student.sid
where
    tname = "波多"
GROUP BY
    student_id
    ;

3.查詢(xún)沒(méi)學(xué)過(guò) “波多” 老師課的同學(xué)的學(xué)號(hào)、姓名。

-- 錯(cuò)誤,這個(gè)結(jié)果是選了不是波多老師課的學(xué)生，這些學(xué)生里有人選了波多，有人沒(méi)選
SELECT
    student_id,sname
FROM
    teacher
    LEFT JOIN course  ON teacher.tid = course.teacher_id    
    LEFT JOIN  score ON  score.course_id = course.cid 
    LEFT JOIN  student ON  score.student_id = student.sid
where
    tname != "波多"
GROUP BY
    student_id
    ;

SELECT
    sid,sname
FROM
     student 
where
    sid not in (
        select score.student_id 
        from  teacher
            LEFT JOIN course  ON teacher.tid = course.teacher_id    
            LEFT JOIN  score ON  score.course_id = course.cid 
            LEFT JOIN  student ON  score.student_id = student.sid
        where
            tname = "波多")
GROUP BY
    sid
    ;

4. 查詢(xún)選修 “蒼空” 老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)（不考慮并列）。

SELECT
        student_id,sname,max(num),cname
FROM
        teacher
        LEFT JOIN course  ON teacher.tid = course.teacher_id    
        LEFT JOIN  score ON  score.course_id = course.cid 
        LEFT JOIN  student ON  score.student_id = student.sid
where
        tname = "波多"
GROUP BY
        student_id,cname
order by
        max(num) desc
limit 1;

SELECT
    student.sid,
    student.sname 
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
    LEFT JOIN course ON score.course_id = course.cid
    LEFT JOIN teacher ON course.teacher_id = teacher.tid 
WHERE
    teacher.tname = "波多" 
ORDER BY
    score.num DESC 
    LIMIT 1

5.查詢(xún)選修 “蒼空” 老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)（考慮并列）。

SELECT
    student.sid,
    student.sname 
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
    LEFT JOIN course ON score.course_id = course.cid
    LEFT JOIN teacher ON course.teacher_id = teacher.tid 
WHERE
    teacher.tname = "波多" 
    AND score.num = (
        SELECT
            max( num ) 
        FROM
            score
            LEFT JOIN course ON score.course_id = course.cid
            LEFT JOIN teacher ON course.teacher_id = teacher.tid 
        WHERE
        teacher.tname = "波多" 
    )

6.查詢(xún)只選修了一門(mén)課程的全部學(xué)生的學(xué)號(hào)、姓名。

select
    student.sid,
    student.sname
from
    score
    left join student on score.student_id = student.sid
group by
    student_id
having
    count(*)=1;

7.查詢(xún)至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)、學(xué)生姓名、選修課程數(shù)量。

select
    student.sid,
    student.sname,
    count(*)
from
    score
    left join student on score.student_id = student.sid
group by
    student_id
having
    count(*)>=2;

8.查詢(xún)兩門(mén)及以上不及格的同學(xué)的學(xué)號(hào)、學(xué)生姓名、選修課程數(shù)量。

select
    student.sid,
    student.sname,
     count(*)
from
    score
    left join student on score.student_id = student.sid
where
    score.num < 60
group by
    student_id
having
     count(*) >= 2;

9.查詢(xún)選修了所有課程的學(xué)生的學(xué)號(hào)、姓名。

select
    student.sid,
    student.sname,
    count(*)
from
    score
    left join student on score.student_id = student.sid
group by
    student_id
having
    count(*)=(select count(*) from course );

10.查詢(xún)未選修所有課程的學(xué)生的學(xué)號(hào)、姓名。

select
    student.sid,
    student.sname,
    count(*)
from
    score
    left join student on score.student_id = student.sid
group by
    student_id
having
    count(*)!=(select count(*) from course );

11.查詢(xún)所有學(xué)生都選修了的課程的課程號(hào)、課程名。

select
    course.cid,
    course.cname
from
    score
    left join course on score.course_id = course.cid
group by
    course_id
having
    count(*)=(select count(*) from course );

12.查詢(xún)選修 “生物” 和 “物理” 課程的所有學(xué)生學(xué)號(hào)、姓名。

SELECT
    student.sid,
    student.sname 
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
    LEFT JOIN course ON score.course_id = course.cid
WHERE
    course.cname = "生物"  and course.cname = "物理"

SELECT
    student.sid,
    student.sname
FROM
    score
    LEFT JOIN course ON score.course_id = course.cid
    LEFT JOIN student ON score.student_id = student.sid
WHERE
    course.cname in ("生物","物理")
GROUP BY
    student_id
having 
    count(1) = 2;

13.查詢(xún)至少有一門(mén)課與學(xué)號(hào)為“1”的學(xué)生所選的課程相同的其他學(xué)生學(xué)號(hào) 和 姓名 。

select
    student.sid,
    student.sname 
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
    LEFT JOIN course ON score.course_id = course.cid
where 
    course_id in (select
                        course_id
                    from
                        score
                    where
                        student_id = 1)
      and
      student_id != 1

SELECT
    student.sid,
    student.sname
FROM
    score
    LEFT JOIN course ON score.course_id = course.cid
    LEFT JOIN student ON score.student_id = student.sid
WHERE
    score.course_id in ( select course_id from score where student_id=1)
    and score.student_id != 1
GROUP BY
    student_id
HAVING
    count(1) >= 1

14.查詢(xún)與學(xué)號(hào)為 “2” 的同學(xué)選修的課程完全相同的其他 學(xué)生學(xué)號(hào) 和 姓名 。

-- 學(xué)號(hào)為 “2” 的同學(xué)選修的課程數(shù)
select 
    count(*)
from score
where student_id = 2

-- 和學(xué)號(hào)為 “2” 的同學(xué)選修的課程數(shù)不同的學(xué)生id
select 
    student_id
from score
group by student_id
having
    count(*) not in ( select count(*)   from score   where student_id = 2)

-- 和學(xué)號(hào)為 “2” 的同學(xué)選修的課程數(shù)相同的學(xué)生id
select
    student_id
from score
where
    student_id not in (select  student_id from score  group by student_id  having   count(*) not in (select   count(*)  from score where student_id = 2))
      and
      course_id in (select course_id
                 from score
                 where student_id=2)
      and
      student_id != 2


select
    student.sid,
    student.sname 
from score
    inner join student on student.sid=score.student_id
where
    student_id not in (select 
                            student_id
                        from score
                        group by student_id
                        having
                            count(*) not in (select 
                                                count(*)
                                            from score
                                            where student_id = 2))
      and
      course_id in (select course_id
                 from score
                 where student_id=2)
      and
      student_id != 2
    

select
   sc.student_id,
   student.sname
from
   (select student_id 
   from score
   group by student_id 
   having count(*)=(select count(*)
                   from score
                   where student_id = 2)) sc,
     score
     inner join student on score.student_id=student.sid
where 
   sc.student_id = score.student_id
   and
   course_id in (select course_id
                from score
                where student_id=2)
   and
   sc.student_id !=2

SELECT
    student.sid,
    student.sname
FROM
    score
    LEFT JOIN course ON score.course_id = course.cid
    LEFT JOIN student ON score.student_id = student.sid
WHERE
    score.course_id in ( select course_id from score where student_id=2)
    and score.student_id in (
        select student_id from score where student_id!=2 group by student_id having count(1) = (select count(1) from score where student_id=2)
    )
GROUP BY
    student_id
HAVING
    count(1) = ( select count(1) from score where student_id=2 )

-- 如果id=2學(xué)生他的課程數(shù)量和其他人的課程數(shù)量是一樣。
select student_id from score where student_id!=2 group by student having count(1) = select count(1) from score where student_id=2

select 
    student_id 
from 
    score 
where 
    student_id!=2 
group by 
    student_id 
having 
    count(1) = select count(1) from score where student_id=2

15.查詢(xún)“生物”課程比“物理”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)；

SELECT 
    c.student_id,
    c.num,
    d.num 
from 
    (select 
        a.student_id,a.num,b.cname 
     from score a 
        LEFT JOIN course b on a.course_id = b.cid 
     where b.cname='生物'
    )as c  
    LEFT JOIN (select * 
               from score a 
               LEFT JOIN course b on a.course_id = b.cid 
               where b.cname='物理' )  d 
    on c.student_id = d.student_id  
WHERE c.num > IFNULL(d.num,0)

select  
    a.student_id,  
    (select num  
     from score h 
        LEFT JOIN course m on h.course_id = m.cid 
     where 
        m.cname='生物' 
        and h.student_id=a.student_id ) as shengwu,
    
    (select num  
     from score h 
        LEFT JOIN course m on h.course_id = m.cid 
     where 
        m.cname='物理' 
        and h.student_id=a.student_id ) as wuli 
from 
    score a  
GROUP BY 
    a.student_id  

SELECT
    student_id,
    max( CASE cname WHEN "生物" THEN num ELSE 0 END ) AS sw,
    max( CASE cname WHEN "物理" THEN num ELSE 0 END ) AS wl 
FROM
    score
    LEFT JOIN course ON score.course_id = course.cid 
WHERE
    cname IN ( "生物", "物理" ) 
GROUP BY
    student_id 
HAVING
    sw > wl;

16.查詢(xún)每門(mén)課程成績(jī)最好的前3名 (不考慮成績(jī)并列情況) 。

select 
    s1.* ,
    course.cname
from score s1
    inner join course on course.cid = s1.course_id
where 
    (select count(1) from score s2 where s1.course_id=s2.course_id and s1.num<s2.num)<3
order by 
    s1.course_id desc,s1.num desc;

SELECT
    cid,
    cname,
    ( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 0) as "第1名",
    ( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 1) as "第2名",
    ( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 2) as "第3名"
FROM
    course;

17.查詢(xún)每門(mén)課程成績(jī)最好的前3名 (考慮成績(jī)并列情況) 。

select 
    s1.student_id,s1.course_id,s1.num,
    course.cname
from score s1
    inner join course on course.cid = s1.course_id
    left join (select distinct course_id,num from score) s2
    on s1.course_id=s2.course_id
    and s1.num<s2.num
group by 
    s1.student_id,s1.course_id,s1.num
having 
    count(1)<3
order by 
    course_id desc,num desc;

select 
    * 
from 
    score 
    
    left join (
        SELECT
            cid,
            cname,
            ( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 2) as third
        FROM
            course ) as C on score.course_id = C.cid 
WHERE
    score.num >= C.third

18.創(chuàng)建一個(gè)表 sc，然后將 score 表中所有數(shù)據(jù)插入到 sc 表中。

CREATE table sc1 as
select * from score;

CREATE TABLE `sc` (
  `sid` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `student_id` int NOT NULL,
  `course_id` int NOT NULL,
  `num` int NOT NULL,
  CONSTRAINT `fk_sc_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_sc_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) DEFAULT CHARSET=utf8;

INSERT INTO sc  SELECT * from score; --要求列數(shù)完全相同

19.向 sc 表中插入一些記錄，這些記錄要求符合以下條件：

  • 學(xué)生ID為：沒(méi)上過(guò)課程ID為 “2” 課程的學(xué)生的 學(xué)號(hào)；
  • 課程ID為：2
  • 成績(jī)?yōu)椋?0
-- 上過(guò)
select student_id from score where course_id =2;

-- 沒(méi)上過(guò)
SELECT
    sid
FROM
    student 
WHERE
    sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )


-- 構(gòu)造數(shù)據(jù)
SELECT
    sid,
    2,
    80
FROM
    student 
WHERE
    sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 );

INSERT INTO sc ( student_id, course_id, num ) SELECT
sid,
2,
80 
FROM
    student 
WHERE
    sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )

20.向 sc 表中插入一些記錄，這些記錄要求符合以下條件：

  • 學(xué)生ID為：沒(méi)上過(guò)課程ID為 “2” 課程的學(xué)生的 學(xué)號(hào)。
  • 課程ID為：2。
  • 成績(jī)?yōu)椋赫n程ID為3的最高分。
INSERT INTO sc ( student_id, course_id, num ) SELECT
sid,
2,
(select max(num) from score where course_id=3) as num
FROM
    student 
WHERE
    sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容