MySQLマルチテーブル練習案列3

学生_レッスンマルチテーブル練習
データの準備

 --        ,  ,     
    create table student(
    	id int primary key auto_increment,
    	name varchar(20),
    	city varchar(10),
    	age int
    );
      create table teacher(
    	id int primary key auto_increment,
    	name varchar(20)
    );
    
    create table course(
    	id int primary key auto_increment,
    	name varchar(20),
    	teacher_id int,
    	foreign key (teacher_id) references teacher(id)
    );
    create table student_course(
    	student_id int,
    	course_id int,
    	score int,
    	foreign key (student_id) references student(id),
    	foreign key (course_id) references course(id)
    );

	--    
    insert into teacher values(null,'  ');
    insert into teacher values(null,'  ');
    insert into teacher values(null,'  ');
    
    insert into student values(null,'  ','  ',20);
    insert into student values(null,'  ','  ',18);
    insert into student values(null,'  ','  ',22);
    insert into student values(null,'  ','  ',21);
    insert into student values(null,'  ','  ',22);
    insert into student values(null,'  ','  ',17);
    insert into student values(null,'  ','  ',23);
    insert into student values(null,'  ','  ',25);
    insert into student values(null,'  ','  ',25);
    insert into student values(null,'  ','  ',20);
    
    insert into course values(null,'  ',1);
    insert into course values(null,'  ',1);
    insert into course values(null,'  ',2);
    insert into course values(null,'  ',2);
    insert into course values(null,'  ',2);
    insert into course values(null,'  ',3);
    
    insert into student_course values(1,1,80);
    insert into student_course values(1,2,90);
    insert into student_course values(1,3,85);
    insert into student_course values(1,4,78);
    insert into student_course values(2,2,53);
    insert into student_course values(2,3,77);
    insert into student_course values(2,5,80);
    insert into student_course values(3,1,71);
    insert into student_course values(3,2,70);
    insert into student_course values(3,4,80);
    insert into student_course values(3,5,65);
    insert into student_course values(3,6,75);
    insert into student_course values(4,2,90);
    insert into student_course values(4,3,80);
    insert into student_course values(4,4,70);
    insert into student_course values(4,6,95);
    insert into student_course values(5,1,60);
    insert into student_course values(5,2,70);
    insert into student_course values(5,5,80);
    insert into student_course values(5,6,69);
    insert into student_course values(6,1,76);
    insert into student_course values(6,2,88);
    insert into student_course values(6,3,87);
    insert into student_course values(7,4,80);
    insert into student_course values(8,2,71);
    insert into student_course values(8,3,58);
    insert into student_course values(8,5,68);
    insert into student_course values(9,2,88);
    insert into student_course values(10,1,77);
    insert into student_course values(10,2,76);
    insert into student_course values(10,3,80);
    insert into student_course values(10,4,85);
    insert into student_course values(10,5,83);

需要
-選択カリキュラム:学生外部キー、カリキュラム外部キー、スコア–カリキュラム:カリキュラム番号、カリキュラム名、先生外部キー–学生表:番号、名前、都市、年齢–先生表:番号、先生名
–1、平均成績が70点より大きい学生の学号と平均成績を調べる

    SELECT
       stu.`id`,AVG(score)
    FROM
       student stu,student_course sc
    WHERE
       sc.`student_id` = stu.`id`
    GROUP BY stu.`id`
    HAVING AVG(score)>70;

–2、すべての学生の学生番号、名前、選択科目数、総成績を調べる

    SELECT 
       stu.`id`,
       stu.`name`,
       COUNT(sc.`course_id`),
       SUM(score)
    FROM
       student stu,student_course sc
    WHERE
       stu.`id` = sc.`student_id`
    GROUP BY stu.`id`

–3、趙雲先生が教えてくれた学生の学号、名前を調べる

    SELECT 
       stu.`id`,
       stu.`name`
    FROM
       student stu,student_course sc,course co
    WHERE
       stu.`id` = sc.`student_id`
    AND
       sc.`course_id` IN (SELECT co.`id`
       					 FROM course 
       					 WHERE co.`id` IN(SELECT id 
       					 FROM teacher 
       					 WHERE NAME = '  '));

–4、関羽先生の授業を習ったことがない学生の学名、名前を調べる

    SELECT DISTINCT
       stu.`id`,
       stu.`name`
    FROM
       student stu
    WHERE stu.`id` NOT IN(  --           
       SELECT stu.`id`
       FROM student stu,student_course sc,course co 
       WHERE 
          stu.`id` = sc.`student_id`
          AND
          sc.`course_id` IN (--        id         id
          SELECT co.`id` FROM course WHERE co.`id` IN( --            id
          SELECT id FROM teacher WHERE NAME = '  ')));--        

–5、3科目以上勉強していない学生の学号、名前を調べる

    SELECT 
       stu.`id`,
       stu.`name`,
       COUNT(sc.`course_id`)
    FROM
       student stu,student_course sc
    WHERE
       stu.`id` = sc.`student_id`
    GROUP BY stu.`id`
    HAVING COUNT(sc.`course_id`)<=3;

-6、各科の成績の最高と最低の点数を調べる

    SELECT
       co.`name`,
       MAX(score),
       MIN(score)
    FROM
       student_course sc,course co
    WHERE
       sc.`course_id` = co.`id`
    GROUP BY co.`id`;

注:以上のコードはすべて自分の手で打って、私の初心者、どうしてもいくつかの低級な間違いが現れて、大物たちが指摘することを望みます!自分の練習の例を分かち合って、一緒に進歩して一緒に勉強します!