有以下三张表(学生表、课程表、成绩表),请写出对应的sql(考察点:sql)
附:数据库
Student 学生表
CREATE TABLE student (
sid varchar(10) NOT NULL,
sName varchar(20) DEFAULT NULL,
sAge datetime DEFAULT '1980-10-12 23:12:36',
sSex varchar(10) DEFAULT NULL,
PRIMARY KEY (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Course 课程表
CREATE TABLE course (
cid varchar(10) NOT NULL,
cName varchar(10) DEFAULT NULL,
tid int(20) DEFAULT NULL,
PRIMARY KEY (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SC 成绩表
CREATE TABLE sc (
sid varchar(10) DEFAULT NULL,
cid varchar(10) DEFAULT NULL,
score int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
问题:
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
– 自连接,一张表当成2张表使用,如何区别这2张表,使用别名
SELECT DISTINCT
s1.sid
FROM sc s1,sc s2
WHERE s1.cid = ‘001’ AND s2.cid =‘002’ AND s1.score>s2.score;
2、查询平均成绩大于60分的同学的学号和平均成绩;
SELECT
s.sid,AVG(c.score)
FROM student s
JOIN sc c
ON s.sid = c.sid
GROUP BY s.sid
HAVING AVG(c.score)>60;
3、查询所有同学的学号、姓名、选课数、总成绩;
SELECT
s.sid ,s.sName,COUNT(c.cid),SUM(c.score)
FROM student s
JOIN sc c
ON s.sid = c.sid
GROUP BY s.sid