sql题

用一条SQL 语句查询出每门课都大于80 分的学生姓名

name	course	grade
张三	语文	81
张三	数学	75
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
王五	英语	90

select name from table group by name having min(grade) > 80

学生成绩例子

学生表 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' , '孙风' , '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' , '数学' , '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 "课程成绩高的学生的信息及课程分数

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 分的同学的学生编号和学生姓名和平均成绩

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

没有group by s.sid 就是返回平均数,并且因为有avg函数，只返回一条数据

+------+--------+-----------+
| sid  | sname  | avg_score |
+------+--------+-----------+
| 01   | 赵雷   |  89.66667 |
| 02   | 钱电   |  70.00000 |
| 03   | 孙风   |  80.00000 |
| 05   | 周梅   |  81.50000 |
| 07   | 郑竹   |  93.50000 |
+------+--------+-----------+

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
- 这道题得用到left join或者right join，不能用where连接，因为题目说了要求有显示为null的，where是inner join，不会出现null，在这道题里会查不出第08号学生。

select s.sid, s.sname, count(cid) as 选课总数, sum(score) as 总成绩
from student as s left join sc
on s.sid = sc.sid
group by s.sid

+------+--------+--------------+-----------+
| sid  | sname  | 选课总数     | 总成绩    |
+------+--------+--------------+-----------+
| 01   | 赵雷   |            3 |     269.0 |
| 02   | 钱电   |            3 |     210.0 |
| 03   | 孙风   |            3 |     240.0 |
| 04   | 李云   |            3 |     100.0 |
| 05   | 周梅   |            2 |     163.0 |
| 06   | 吴兰   |            2 |      65.0 |
| 07   | 郑竹   |            2 |     187.0 |
| 08   | 王菊   |            0 |      NULL |
+------+--------+--------------+-----------+

查有成绩的学生信息

select s.sid, s.sname, count(*) as 选课总数, 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  | 选课总数     | 总成绩    | 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   | 孙风   |            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

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
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，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

select c.cid as 课程号, c.cname as 课程名称, count(*) as 选修人数,
    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 优良率,
    sum(case when score >= 90 then 1 else 0 end)/count(*) as 优秀率
from sc, course c
where c.cid = sc.cid
group by c.cid
order by count(*) desc, c.cid asc

+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 课程号    | 课程名称      | 选修人数      | 最高分     | 最低分    | 平均分     | 及格率    | 中等率    | 优良率     | 优秀率     |
+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 01        | 语文         |            6 |      80.0 |      31.0 |  64.50000 |    0.6667 |    0.3333 |    0.3333 |    0.0000 |
| 02        | 数学         |            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 |
+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

学生成绩例子

推荐阅读更多精彩内容