数据库sql 练习一

    最近接触了一到数据库sql的题目，在这里记录一下，方便以后查看，也欢迎各位小伙伴一起学习，如果不对，还请指出，万分感激。

    题目：

一。根据下面四个表，写出sql

我直接在数据库中新建了这几张表，建库脚本语句如下：

CREATE TABLE pe_student(
	id INT(9),
	grade_id INT(9),
	stu_name VARCHAR(20)
);

学生表

CREATE TABLE pe_grade(
	id INT(9),
	grade_name VARCHAR(13)
);

年级表，id与pe_student 表中的grade_id 关联

CREATE TABLE pr_stu_elective(
	id INT(9),
	fk_stu_id INT(9),
	fk_course_id INT(9)
);

选课表pr_stu_elective， 其中fk_stu_id 与pe_student表中的id关联， fk_course_id 与下表pe_tch_course 表中的id 关联

CREATE TABLE pe_tch_course(
	id INT(9),
	course_name VARCHAR(20)
);

课程表pe_tch_course

下面是我写的一些假数据的脚本：

INSERT INTO pe_student() VALUES (1001, 2001, '张三1');
INSERT INTO pe_student() VALUES (1002, 2002, '张三2');
INSERT INTO pe_student() VALUES (1003, 2002, '张三3');
INSERT INTO pe_student() VALUES (1004, 2002, '张三4');
INSERT INTO pe_student() VALUES (1005, 2003, '张三5');
INSERT INTO pe_student() VALUES (1006, 2003, '张三6');
INSERT INTO pe_student() VALUES (1007, 2004, '张三7');
INSERT INTO pe_student() VALUES (1008, 2005, '张三8');
INSERT INTO pe_student() VALUES (1009, 2006, '张三9');
INSERT INTO pe_student() VALUES (1010, 2006, '张三10');
INSERT INTO pe_student() VALUES (1011, 2006, '张三11');
INSERT INTO pe_student() VALUES (1012, 2006, '张三12');
INSERT INTO pe_student() VALUES (1013, 2006, '张三13');
INSERT INTO pe_student() VALUES (1014, 2002, '张三14');
INSERT INTO pe_student() VALUES (1015, 2001, '张三15');

INSERT INTO pe_grade() VALUES (2001, '一年级');
INSERT INTO pe_grade() VALUES (2002, '二年级');
INSERT INTO pe_grade() VALUES (2003, '三年级');

INSERT INTO pe_grade() VALUES (2004, '四年级');

INSERT INTO pe_grade() VALUES (2005, '五年级');

INSERT INTO pe_grade() VALUES (2006, '六年级');

INSERT INTO pr_stu_elective () VALUES (3001, 1001, 4001);
INSERT INTO pr_stu_elective () VALUES (3002, 1002, 4002);
INSERT INTO pr_stu_elective () VALUES (3003, 1003, 4001);
INSERT INTO pr_stu_elective () VALUES (3004, 1004, 4003);
INSERT INTO pr_stu_elective () VALUES (3005, 1005, 4003);
INSERT INTO pr_stu_elective () VALUES (3006, 1006, 4002);
INSERT INTO pr_stu_elective () VALUES (3007, 1007, 4002);
INSERT INTO pr_stu_elective () VALUES (3008, 1008, 4004);
INSERT INTO pr_stu_elective () VALUES (3009, 1009, 4006);
INSERT INTO pr_stu_elective () VALUES (3010, 1010, 4005);
INSERT INTO pr_stu_elective () VALUES (3011, 1011, 4003);
INSERT INTO pr_stu_elective () VALUES (3012, 1012, 4006);

INSERT INTO pe_tch_course() VALUES (4001, '语文');
INSERT INTO pe_tch_course() VALUES (4002, '数学');
INSERT INTO pe_tch_course() VALUES (4003, '英语');
INSERT INTO pe_tch_course() VALUES (4004, '物理');
INSERT INTO pe_tch_course() VALUES (4005, '化学');
INSERT INTO pe_tch_course() VALUES (4006, '生物');

题目1.写sql统计各个年级每门课程的选课数量，查询结果显示为一下格式：

    年级名称    课程名称    选课数量

我写的sql:

SELECT n.grade_name, o.course_name, m.stu_sum
FROM (
	SELECT b.`grade_id`, a.`fk_course_id`, COUNT(b.`id`) stu_sum
	FROM pr_stu_elective a, pe_student b
	WHERE a.`fk_stu_id`=b.`id`
	GROUP BY a.`fk_course_id`, b.`grade_id`
) m, pe_grade n, pe_tch_course o
WHERE m.grade_id=n.id AND m.fk_course_id=o.id 
ORDER BY m.grade_id

其中： 

SELECT b.`grade_id`, a.`fk_course_id`, COUNT(b.`id`) stu_sum
	FROM pr_stu_elective a, pe_student b
	WHERE a.`fk_stu_id`=b.`id`
	GROUP BY a.`fk_course_id`, b.`grade_id`

为各个年级的每门课程的选课数量，都是以id关联的，外层为按照题目需要将id转换成名称

题目二：写sql统计各个年级没有选课的学生数，查询结果显示的列如下：

    年级名称     学生数量

我写的sql为：

SELECT c2.grade_name, c1.sum_stu
FROM (
	SELECT b1.grade_id, COUNT(b1.id) sum_stu
	FROM (
		SELECT a1.id, a1.grade_id, a2.fk_course_id 
		FROM pe_student a1 LEFT JOIN pr_stu_elective a2
		ON a1.id=a2.fk_stu_id 	
	) b1
	WHERE b1.fk_course_id IS NULL
	GROUP BY b1.grade_id 
) c1, pe_grade c2
WHERE c1.grade_id=c2.id

欢迎指正。