A:查询姓‘王’的学生的个数
SELECT
COUNT(*)
FROM
student
WHERE
name LIKE ('王%')
;
B:查询‘数学’比‘语文’成绩高的所有学生的学号
SELECT
id
FROM
(
SELECT
student.id,
max(
CASE
WHEN course.`name` = '语文' THEN
sc.score
END
) chinese,
max(
CASE
WHEN course.`name` = '数学' THEN
sc.score
END
) math
FROM
student
JOIN sc ON student.id = sc.sid
JOIN course ON course.id = sc.cid
GROUP BY
student.id
HAVING
chinese > math
) 嵌套表
C:查询平均成绩大于90分的同学的学号和平均成绩
SELECT
student.id,
AVG(score)
FROM
student
JOIN sc ON student.id = sc.sid
GROUP BY
student.id
HAVING
AVG(score) >= 90;
(1):请写出计算所有学生的英语平均水平成绩的sql语句
SELECT AVG(english) FROM mark;
(2):现有五个学生,其学号假定为11,22,33,44,55;请用一条sql语句实现列出这五个学生的数学成绩及其姓名、学生地址、电话号码;
SELECT
cust.studentno,
cust. NAME,
cust.address,
cust.telno,
mark.math
FROM
cust
JOIN mark ON cust.studentno = mark.studentno
WHERE
cust.studentno IN (11, 22, 33, 44, 55);
(3):查询所有学生的姓名、计算机成绩、按照计算机成绩从高到低排序;
SELECT
cust. NAME,
mark.computer
FROM
cust
JOIN mark ON cust.studentno = mark.studentno
ORDER BY
mark.computer DESC;
(4):查询所有总成绩大于240分学生的学号、姓名、总成绩,按照总成绩由高到低排序;
SELECT
cust.studentno,
cust. NAME,
SUM(
mark.english + mark.computer + mark.math
)
FROM
cust
JOIN mark ON cust.studentno = mark.studentno
GROUP BY
cust.studentno
HAVING
SUM(
mark.english + mark.computer + mark.math
) > 240;
ORDER BY
SUM(
mark.english + mark.computer + mark.math
)