SQL加强练习

新建一个数据库,然后在查询中粘贴以下内容建立数据表并插入数据

score表

CREATE TABLE `score` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `stu_id` int(10) NOT NULL,
  `c_name` varchar(20) DEFAULT NULL,
  `grade` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `score` VALUES ('1', '901', '计算机', '98');
INSERT INTO `score` VALUES ('2', '901', '英语', '80');
INSERT INTO `score` VALUES ('3', '902', '计算机', '65');
INSERT INTO `score` VALUES ('4', '902', '中文', '88');
INSERT INTO `score` VALUES ('5', '903', '中文', '95');
INSERT INTO `score` VALUES ('6', '904', '计算机', '70');
INSERT INTO `score` VALUES ('7', '904', '英语', '92');
INSERT INTO `score` VALUES ('8', '905', '英语', '94');
INSERT INTO `score` VALUES ('9', '906', '计算机', '90');
INSERT INTO `score` VALUES ('10', '906', '英语', '85');

student表

CREATE TABLE `student` (
  `id` int(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `birth` year(4) DEFAULT NULL,
  `department` varchar(20) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('901', '张老大', '男', '1985', '计算机系', '北京市海淀区');
INSERT INTO `student` VALUES ('902', '张老二', '男', '1986', '中文系', '北京市昌平区');
INSERT INTO `student` VALUES ('903', '张三', '女', '1990', '中文系', '湖南省永州市');
INSERT INTO `student` VALUES ('904', '李四', '男', '1990', '英语系', '辽宁省阜新市');
INSERT INTO `student` VALUES ('905', '王五', '女', '1991', '英语系', '福建省厦门市');
INSERT INTO `student` VALUES ('906', '王六', '男', '1988', '计算机系', '湖南省衡阳市');

以下为查询练习及参考答案

3.查询student表的所有记录
SELECT * FROM student;

4.查询student表的第2条到4条记录 
SELECT * FROM student LIMIT 1,3;

5.从student表查询所有学生的学号(id)、姓名(name)和院系(department)的信息
SELECT id,name,department FROM student;

6.从student表中查询计算机系和英语系的学生的信息
SELECT * FROM student WHERE department = '计算机系' OR department = '英语系';

SELECT * FROM student WHERE department IN('计算机系','英语系');

7.从student表中查询出生于1986-1990年的学生信息
SELECT * FROM student WHERE birth BETWEEN 1986 and 1990;

8.从student表中查询每个院系有多少人
SELECT COUNT(*) stu_count,department FROM student GROUP BY department;

9.从score表中查询每个科目的最高分
SELECT c_name,MAX(grade) FROM score GROUP BY c_name;

10.查询李四的考试科目(c_name)和考试成绩(grade)
SELECT c_name,grade FROM score where stu_id = (SELECT id FROM student WHERE name = '李四');

SELECT name,c_name,grade FROM score LEFT JOIN student stu ON stu_id = stu.id WHERE stu_id = (SELECT id FROM student WHERE name = '李四');

11.用连接的方式查询所有学生的信息和考试信息
SELECT stu.*,c_name,grade FROM score RIGHT JOIN student stu ON stu_id = stu.id;

SELECT student.id,name,sex,birth,department,address,c_name,grade
FROM student,score
WHERE student.id=score.stu_id;

12.计算每个学生的总成绩
SELECT name,SUM(grade) FROM student stu,score sco WHERE stu.id = sco.stu_id GROUP BY stu.id;

13.计算每个考试科目的平均成绩
SELECT c_name,AVG(grade) FROM student stu,score sco WHERE stu.id = sco.stu_id GROUP BY c_name;

14.查询计算机成绩低于95的学生信息
SELECT student.*,c_name,grade FROM student,score WHERE student.id = stu_id HAVING (c_name = '计算机' AND grade < 95)

15.查询同时参加计算机和英语考试的学生的信息
SELECT *  FROM student
WHERE id = ANY
(SELECT stu_id FROM score
WHERE stu_id IN (
SELECT stu_id FROM
score WHERE c_name=  '计算机')
AND c_name= '英语' );

16.将计算机考试成绩按从高到低进行排序
SELECT * FROM score WHERE c_name = '计算机' ORDER BY grade DESC;

17.从student表和score表中查询出学生的学号,然后合并查询结果
SELECT id FROM student
UNION
SELECT stu_id FROM score

18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
SELECT name,department,c_name,grade FROM student,score WHERE student.id = stu_id AND (name LIKE '张%' OR name LIKE '王%')

19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
SELECT name,birth,department,c_name,grade,address FROM student,score WHERE student.id = stu_id AND address LIKE '湖南%'