表格

-- 學(xué)生表
CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(s_id)
);
-- 課程表
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);
-- 教師表
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);
-- 成績(jī)表
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id)
);
-- 插入學(xué)生表測(cè)試數(shù)據(jù)
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' , '女');
-- 課程表測(cè)試數(shù)據(jù)
INSERT INTO Course VALUES('01' , '語文' , '02');
INSERT INTO Course VALUES('02' , '數(shù)學(xué)' , '01');
INSERT INTO Course VALUES('03' , '英語' , '03');

-- 教師表測(cè)試數(shù)據(jù)
INSERT INTO Teacher VALUES('01' , '張三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');

-- 成績(jī)表測(cè)試數(shù)據(jù)
INSERT INTO Score VALUES('01' , '01' , 80);
INSERT INTO Score VALUES('01' , '02' , 90);
INSERT INTO Score VALUES('01' , '03' , 99);
INSERT INTO Score VALUES('02' , '01' , 70);
INSERT INTO Score VALUES('02' , '02' , 60);
INSERT INTO Score VALUES('02' , '03' , 80);
INSERT INTO Score VALUES('03' , '01' , 80);
INSERT INTO Score VALUES('03' , '02' , 80);
INSERT INTO Score VALUES('03' , '03' , 80);
INSERT INTO Score VALUES('04' , '01' , 50);
INSERT INTO Score VALUES('04' , '02' , 30);
INSERT INTO Score VALUES('04' , '03' , 20);
INSERT INTO Score VALUES('05' , '01' , 76);
INSERT INTO Score VALUES('05' , '02' , 87);
INSERT INTO Score VALUES('06' , '01' , 31);
INSERT INTO Score VALUES('06' , '03' , 34);
INSERT INTO Score VALUES('07' , '02' , 89);
INSERT INTO Score VALUES('07' , '03' , 98);

題目

-- 1、查詢"01"課程比"02"課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)

SELECT stu.*,s1.s_score 課程一成績(jī),s2.s_score 課程二成績(jī) FROM student stu,(SELECT s.`s_id`,s.`s_score` FROM score s INNER JOIN course c ON s.c_id = c.c_id AND c.c_id = '01') s1 
,(SELECT s.`s_id`,s.`s_score` FROM score s INNER JOIN course c ON s.c_id = c.c_id AND c.c_id = '02') s2 
WHERE stu.`s_id` = s1.s_id AND stu.`s_id` = s2.s_id HAVING 課程一成績(jī) > 課程二成績(jī);

-- 2、查詢"01"課程比"02"課程成績(jī)低的學(xué)生的信息及課程分?jǐn)?shù)

SELECT stu.*,s1.s_score 課程一成績(jī),s2.s_score 課程二成績(jī) FROM student stu,(SELECT s.`s_id`,s.`s_score` FROM score s INNER JOIN course c ON s.c_id = c.c_id AND c.c_id = '01') s1 
,(SELECT s.`s_id`,s.`s_score` FROM score s INNER JOIN course c ON s.c_id = c.c_id AND c.c_id = '02') s2 
WHERE stu.`s_id` = s1.s_id AND stu.`s_id` = s2.s_id HAVING 課程一成績(jī) < 課程二成績(jī);

-- 3、查詢平均成績(jī)大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

SELECT stu.*,t.平均成績(jī) FROM student stu,(SELECT AVG(s.`s_score`) 平均成績(jī),s.`s_id` FROM score s INNER JOIN course c ON s.`c_id` = c.`c_id` GROUP BY s.`s_id`) t 
WHERE t.s_id = stu.`s_id` HAVING 平均成績(jī) >= 60;

-- 4、查詢平均成績(jī)小于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
-- (包括有成績(jī)的和無成績(jī)的)

SELECT stu.*,IFNULL(t.平均成績(jī),0) AS r FROM student stu LEFT JOIN (SELECT AVG(s.`s_score`) 平均成績(jī), s.`s_id` FROM score s INNER JOIN course c ON s.`c_id` = c.`c_id` GROUP BY s.`s_id`) t 
ON t.s_id = stu.`s_id` HAVING r < 60;

-- 5、查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)

SELECT stu.*,COUNT(c.`c_id`)選課數(shù),IFNULL(SUM(s.`s_score`),0) 總成績(jī) FROM student stu LEFT JOIN score s ON s.`s_id` = stu.s_id LEFT  JOIN course c ON s.`c_id` = c.`c_id` 
GROUP BY stu.`s_id`