题目：

设计数据库表，用来存放学生信息、课程信息、学生的课程及成绩，并给出SQL语句，查询平均成绩大于85的所有学生。

解答：

CREATE DATABASE interview;
 
 
CREATE TABLE class (
cls_id VARCHAR(30) PRIMARY KEY,
cls_name VARCHAR(30)
);
 
CREATE TABLE student (
std_id VARCHAR(30) PRIMARY KEY,
cls_id VARCHAR(30),
std_name VARCHAR(30),
CONSTRAINT cls_id_fk1 FOREIGN KEY(cls_id) REFERENCES class(cls_id)
);
 
CREATE TABLE timetable (
t_id VARCHAR(30) PRIMARY KEY,
t_name VARCHAR(30)
);
 
CREATE TABLE score (
s_id VARCHAR(30),
std_id VARCHAR(30),
t_id VARCHAR(30),
s_value INT,
CONSTRAINT std_id_fk1 FOREIGN KEY(std_id) REFERENCES student(std_id),
CONSTRAINT t_id_fk1 FOREIGN KEY(t_id) REFERENCES timetable(t_id)
);
 
 
INSERT INTO class (cls_id,cls_name) VALUES ('cls001','a');
INSERT INTO class (cls_id,cls_name) VALUES ('cls002','b');
INSERT INTO class (cls_id,cls_name) VALUES ('cls003','c');
INSERT INTO class (cls_id,cls_name) VALUES ('cls004','d');
 
 
INSERT INTO timetable (t_id,t_name) VALUES ('t001','语文');
INSERT INTO timetable (t_id,t_name) VALUES ('t002','数学');
INSERT INTO timetable (t_id,t_name) VALUES ('t003','英语');
INSERT INTO timetable (t_id,t_name) VALUES ('t004','化学');
 
 
INSERT INTO student (std_id,cls_id,std_name) VALUES ('std001','cls001','张三');
INSERT INTO student (std_id,cls_id,std_name) VALUES ('std002','cls001','李四');
INSERT INTO student (std_id,cls_id,std_name) VALUES ('std003','cls002','王五');
INSERT INTO student (std_id,cls_id,std_name) VALUES ('std004','cls003','小刘');
INSERT INTO student (std_id,cls_id,std_name) VALUES ('std005','cls004','大气');
 
 
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s001','std001','t001',23);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s002','std001','t001',33);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s003','std001','t002',59);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s004','std002','t003',66);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s005','std002','t004',72);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s006','std003','t001',87);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s007','std003','t002',99);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s008','std004','t003',61);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s009','std005','t004',94);
INSERT INTO score (s_id,std_id,t_id) VALUES ('s010','std005','t004');
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s011','std001','t003',86);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s012','std002','t003',88);
 
 
SELECT * FROM class;
SELECT * FROM timetable;
SELECT * FROM student;
SELECT * FROM score;
 
 
 
SELECT st.*,score_avg.* FROM student st
LEFT JOIN (SELECT AVG(s_value) AS avg_score, std_id FROM score GROUP BY std_id) score_avg ON score_avg.std_id = st.std_id
WHERE score_avg.avg_score > 89;