SQL查询语句练习题集

有以下几张表及表结构

Student(Sid,Sname,Sage,Ssex) 学生表

Course(Cid,Cname,Tid) 课程表

SC(Sid,Cid,score) 成绩表

Teacher(Tid,Tname) 教师表

创建表：

######################################建表，插入数据
##创建数据库
##学生表
CREATE TABLE student(
	s_id INT PRIMARY KEY AUTO_INCREMENT,
	s_name VARCHAR(20)  NOT NULL ,
	s_birth DATE NOT NULL,
	s_sex VARCHAR(10)  NOT NULL
);

##课程表
CREATE TABLE course(
	c_id INT PRIMARY KEY AUTO_INCREMENT,
	c_name VARCHAR(20)  NOT NULL,
	t_id VARCHAR (20) NOT NULL
);

##教师表
CREATE TABLE teacher(
	t_id INT PRIMARY KEY AUTO_INCREMENT,
	t_name VARCHAR(20) NOT NULL
);

##成绩表
CREATE TABLE score(
	s_id INT,##学生编号
	c_id INT,##课程编号
	s_score INT(3),##分数
	PRIMARY KEY (s_id,c_id)##设置联合主键
);

##插入数据--学生表
INSERT INTO student VALUES(1,'赵磊','1990-01-01','男');
INSERT INTO student VALUES(NULL,'钱电','1990-12-21','男');
INSERT INTO student VALUES(NULL,'孙风' , '1990-05-20' , '男');
INSERT INTO student VALUES(NULL, '李云' , '1990-08-06' , '男');
INSERT INTO student VALUES(NULL,'周梅' , '1991-12-01' , '女');
INSERT INTO student VALUES(NULL,'吴兰' , '1992-03-01' , '女');
INSERT INTO student VALUES(NULL,'郑竹' , '1989-07-01' , '女');
INSERT INTO student VALUES(NULL,'王菊' , '1990-01-20' , '女');

SELECT * FROM student;

##插入数据--科目表
INSERT INTO course VALUES(NULL,'语文','1');
INSERT INTO course VALUES(NULL,'数学','1');
INSERT INTO course VALUES(NULL,'英语','1');

##更正数据
UPDATE course SET t_id=2 WHERE c_id=2;
UPDATE course SET t_id=3 WHERE c_id=3;

SELECT * FROM course;

##插入数据--教师表
INSERT INTO teacher VALUES(NULL,'张三');
INSERT INTO teacher VALUES(NULL,'李四');
INSERT INTO teacher VALUES(NULL,'王五');

SELECT * FROM teacher;
##插入数据--成绩表

INSERT INTO score VALUES ('1','1',80);
INSERT INTO Score VALUES('1' , '2' , 90);
INSERT INTO Score VALUES('1' , '3' , 99);
INSERT INTO Score VALUES('2' , '1' , 70);
INSERT INTO Score VALUES('2' , '2' , 60);
INSERT INTO Score VALUES('2' , '3' , 80);
INSERT INTO Score VALUES('3' , '1' , 80);
INSERT INTO Score VALUES('3' , '2' , 80);
INSERT INTO Score VALUES('3' , '3' , 80);
INSERT INTO Score VALUES('4' , '1' , 50);
INSERT INTO Score VALUES('4' , '2' , 30);
INSERT INTO Score VALUES('4' , '3' , 20);
INSERT INTO Score VALUES('5' , '1' , 76);
INSERT INTO Score VALUES('5' , '2' , 87);
INSERT INTO Score VALUES('6' , '1' , 31);
INSERT INTO Score VALUES('6' , '3' , 34);
INSERT INTO Score VALUES('7' , '2' , 89);
INSERT INTO Score VALUES('7' , '3' , 98);

SELECT * FROM score;

题目及解决办法：

##常见搜索题目
## 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT * FROM student s,
(
SELECT s1id,s1score,s2score FROM
(SELECT s_id AS s1id,c_id AS c1id,s_score AS s1score FROM score WHERE c_id=1) s1,
(SELECT s_id AS s2id,c_id AS c2id,s_score AS s2score FROM score WHERE c_id=2) s2
WHERE s1.s1id=s2.s2id AND s1score>s2score
)m 
WHERE s.s_id=s1id;

##2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT sci,s_name,sss FROM student s,
(
SELECT s_id AS sci,AVG(s_score) AS sss FROM score GROUP BY s_id HAVING AVG(s_score)>=60) sc
WHERE s.s_id=sc.sci;

##3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩