数据库sql 练习一

系统管理员 2022-05-26 02:38 198阅读 0赞

最近接触了一到数据库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

欢迎指正。