sql題

  • 用一條SQL 語句 查詢出每門課都大于80 分的學(xué)生姓名
name course grade
張三 語文 81
張三 數(shù)學(xué) 75
李四 語文 76
李四 數(shù)學(xué) 90
王五 語文 81
王五 數(shù)學(xué) 100
王五 英語 90
  • select name from table group by name having min(grade) > 80

學(xué)生成績例子

  • 學(xué)生表 Student
create table Student(Sid varchar(6), Sname varchar(10), Sage datetime, Ssex varchar(10));
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風(fēng)' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吳蘭' , '1992-03-01' , '女');
insert into Student values('07' , '鄭竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女')
  • 成績表 SC
create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98)
  • 課程表 Course
create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語' , '03')
  • 教師表 Teacher
create table Teacher(Tid varchar(10),Tname varchar(10));
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五')
image.png
  • 查詢" 01 “課程比” 02 "課程成績高的學(xué)生的信息及課程分?jǐn)?shù)
select s.*, a.score as score_01, b.score as score_02
from student s,
     (select sid, score from sc where cid=01) a,
     (select sid, score from sc where cid=02) b
where a.sid = b.sid and a.score > b.score and s.sid = a.sid

+------+--------+---------------------+------+----------+----------+
| Sid  | Sname  | Sage                | Ssex | score_01 | score_02 |
+------+--------+---------------------+------+----------+----------+
| 02   | 錢電   | 1990-12-21 00:00:00 | 男   |     70.0 |     60.0 |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |     50.0 |     30.0 |
+------+--------+---------------------+------+----------+----------+
  • 查詢平均成績大于等于 60 分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
select s.sid, sname, avg(score) as avg_score
from student as s, sc
where s.sid = sc.sid
group by s.sid
having avg_score > 60
  • 沒有g(shù)roup by s.sid 就是返回平均數(shù),并且因?yàn)橛衋vg函數(shù)，只返回一條數(shù)據(jù)
+------+--------+-----------+
| sid  | sname  | avg_score |
+------+--------+-----------+
| 01   | 趙雷   |  89.66667 |
| 02   | 錢電   |  70.00000 |
| 03   | 孫風(fēng)   |  80.00000 |
| 05   | 周梅   |  81.50000 |
| 07   | 鄭竹   |  93.50000 |
+------+--------+-----------+
  • 查詢所有同學(xué)的學(xué)生編號、學(xué)生姓名、選課總數(shù)、所有課程的總成績(沒成績的顯示為 null )
    • 這道題得用到left join或者right join，不能用where連接，因?yàn)轭}目說了要求有顯示為null的，where是inner join，不會出現(xiàn)null，在這道題里會查不出第08號學(xué)生。
select s.sid, s.sname, count(cid) as 選課總數(shù), sum(score) as 總成績
from student as s left join sc
on s.sid = sc.sid
group by s.sid

+------+--------+--------------+-----------+
| sid  | sname  | 選課總數(shù)     | 總成績    |
+------+--------+--------------+-----------+
| 01   | 趙雷   |            3 |     269.0 |
| 02   | 錢電   |            3 |     210.0 |
| 03   | 孫風(fēng)   |            3 |     240.0 |
| 04   | 李云   |            3 |     100.0 |
| 05   | 周梅   |            2 |     163.0 |
| 06   | 吳蘭   |            2 |      65.0 |
| 07   | 鄭竹   |            2 |     187.0 |
| 08   | 王菊   |            0 |      NULL |
+------+--------+--------------+-----------+
  • 查有成績的學(xué)生信息
select s.sid, s.sname, count(*) as 選課總數(shù), sum(score) as 總成績,
    sum(case when cid = 01 then score else null end) as score_01,
    sum(case when cid = 02 then score else null end) as score_02,
    sum(case when cid = 03 then score else null end) as score_03
from student as s, sc
where s.sid = sc.sid
group by s.sid

+------+--------+--------------+-----------+----------+----------+----------+
| sid  | sname  | 選課總數(shù)     | 總成績    | score_01 | score_02 | score_03 |
+------+--------+--------------+-----------+----------+----------+----------+
| 01   | 趙雷   |            3 |     269.0 |     80.0 |     90.0 |     99.0 |
| 02   | 錢電   |            3 |     210.0 |     70.0 |     60.0 |     80.0 |
| 03   | 孫風(fēng)   |            3 |     240.0 |     80.0 |     80.0 |     80.0 |
| 04   | 李云   |            3 |     100.0 |     50.0 |     30.0 |     20.0 |
| 05   | 周梅   |            2 |     163.0 |     76.0 |     87.0 |     NULL |
| 06   | 吳蘭   |            2 |      65.0 |     31.0 |     NULL |     34.0 |
| 07   | 鄭竹   |            2 |     187.0 |     NULL |     89.0 |     98.0 |
+------+--------+--------------+-----------+----------+----------+----------+
  • score沒有加sum,哪個score加了那個生效
image.png
  • 查詢兩門及其以上不及格課程的同學(xué)的學(xué)號，姓名及其平均成績
  • having雖然是在select后影響的。但并不意味著having只能操作select出來的字段。
SELECT s.sid,s.sname ,AVG(sc.`score`)
FROM student s,sc
WHERE s.sid = sc.sid AND s.sid IN(
    SELECT sc2.sid
    FROM sc sc2
    WHERE sc2.`score`<60
    GROUP BY sc2.sid
    HAVING COUNT(sc2.score)>=2
)
GROUP BY s.sid
  • 查詢各科成績最高分、最低分和平均分，以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率(及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90）。要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列。
select c.cid as 課程號, c.cname as 課程名稱, count(*) as 選修人數(shù),
    max(score) as 最高分, min(score) as 最低分, avg(score) as 平均分,
    sum(case when score >= 60 then 1 else 0 end)/count(*) as 及格率,
    sum(case when score >= 70 and score < 80 then 1 else 0 end)/count(*) as 中等率,
    sum(case when score >= 80 and score < 90 then 1 else 0 end)/count(*) as 優(yōu)良率,
    sum(case when score >= 90 then 1 else 0 end)/count(*) as 優(yōu)秀率
from sc, course c
where c.cid = sc.cid
group by c.cid
order by count(*) desc, c.cid asc

+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 課程號    | 課程名稱      | 選修人數(shù)      | 最高分     | 最低分    | 平均分     | 及格率    | 中等率    | 優(yōu)良率     | 優(yōu)秀率     |
+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 01        | 語文         |            6 |      80.0 |      31.0 |  64.50000 |    0.6667 |    0.3333 |    0.3333 |    0.0000 |
| 02        | 數(shù)學(xué)         |            6 |      90.0 |      30.0 |  72.66667 |    0.8333 |    0.0000 |    0.5000 |    0.1667 |
| 03        | 英語         |            6 |      99.0 |      20.0 |  68.50000 |    0.6667 |    0.0000 |    0.3333 |    0.3333 |
+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

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

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