マルチテーブル結合クエリとネストサブクエリの実践

  1. (記述問題) stumデータベースとcourses(コーステーブル)、score(スコアテーブル)、student_info(学生情報テーブル)、teaching(授業テーブル)、instructors(教員テーブル)の5つのテーブルを復元してください。

stumデータベースバックアップファイル: stum_backup.sql

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `stum` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `stum`;

--
-- テーブル構造 courses
--

DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
  `cno` char(4) NOT NULL,
  `cname` varchar(20) DEFAULT NULL,
  `cperiod` tinyint(3) DEFAULT NULL,
  `credit` tinyint(3) DEFAULT NULL,
  `ctype` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='コーステーブル';

INSERT INTO `courses` VALUES ('c01','コンピュータ基礎',68,3,'必修'),('c02','データベース',72,5,'必修'),('c03','C言語',68,4,'必修'),('c04','C#プログラミング',54,3,'選択'),('c05','Java開発',72,4,'選択'),('c06','オペレーティングシステム',68,4,'必修'),('c07','コンピュータ構成論',68,2,'必修'),('c08','ソフトウェア工学',54,3,'選択');

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (
  `sno` char(12) NOT NULL,
  `cno` char(4) NOT NULL,
  `grade` decimal(5,2) DEFAULT '0.00',
  PRIMARY KEY (`sno`,`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='スコアテーブル';

LOCK TABLES `score` WRITE;
INSERT INTO `score` VALUES ('2015001','c01',92.00),('2015001','c02',88.00),('2015001','c05',70.00),('2015002','c01',90.00),('2015002','c03',66.00),('2015002','c05',70.00),('2015002','c07',81.00),('2015002','c08',83.00),('2015003','c04',97.00),('2015004','c06',93.00),('2015004','c07',78.00),('2015005','c02',50.00),('2015005','c06',41.00),('2015006','c05',60.00),('2015006','c07',89.00),('2015006','c08',69.00);

UNLOCK TABLES;

DROP TABLE IF EXISTS `student_info`;

CREATE TABLE `student_info` (
  `sno` char(12) NOT NULL,
  `sname` char(8) NOT NULL,
  `sex` enum('男','女') DEFAULT '男',
  `sbirthday` date DEFAULT NULL,
  `sdept` varchar(10) DEFAULT NULL,
  `smajor` varchar(20) DEFAULT NULL,
  `spolitic` char(5) DEFAULT NULL,
  `saddress` varchar(20) DEFAULT NULL,
  `sphone` char(12) DEFAULT NULL,
  `sphoto` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生情報テーブル';

LOCK TABLES `student_info` WRITE;

INSERT INTO `student_info` VALUES ('2015001','悟空','男','1990-10-01','コンピュータ学部','ソフトウェア工学','党員','北京','135111','d:\\pic\\1.jpg'),('2015002','八戒','男','1991-06-23','数学学部','数学解析','党員','上海','135222',NULL),('2015003','嫦娥','女','1990-10-21','外国語学部','日本語','団員','北京','135333','d:\\pic\\3.jpg'),('2015004','三蔵','男','1989-01-05','コンピュータ学部','ソフトウェア工学','党員','上海','135444',NULL),('2015005','哪吒','男','1988-03-11','数学学部','数理モデリング','団員','北京','135555',NULL),('2015006','達地','男','1988-07-10','外国語学部','英語教育','一般','上海','135666',NULL),('2015007','会友','男','1991-09-03','デザイン学部','ランドスケープ','一般','広州','135777','d:\\pic\\7.jpg'),('2015008','胡美','女','1992-12-10','音楽学部','音楽教育','団員','天津','135888',NULL),('2015009','雪友','男','1991-11-21','音楽学部','声楽器楽','党員','北京','135999',NULL),('2015010','葉飛','女','1992-05-31','コンピュータ学部','ネットワーク技術','民盟','広州','136111',NULL),('2015011','マリ','女','1990-06-23','コンピュータ学部','デジタルメディア','一般','上海','159111',NULL),('2015012','リリー','女','1990-06-25','数学学部','数学解析','団員','深圳','188666',NULL);
/*!40000 ALTER TABLE `student_info` ENABLE KEYS */;
UNLOCK TABLES;

--
-- テーブル構造 teaching
--

DROP TABLE IF EXISTS `teaching`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teaching` (
  `tno` char(8) NOT NULL,
  `cno` char(4) NOT NULL,
  `tscore` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`tno`,`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='授業テーブル';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- データ teaching
--

LOCK TABLES `teaching` WRITE;
/*!40000 ALTER TABLE `teaching` DISABLE KEYS */;
INSERT INTO `teaching` VALUES ('t01','c01',99.00),('t01','c02',97.00),('t02','c05',92.00),('t03','c01',90.00),('t04','c03',91.00),('t04','c05',97.00),('t04','c07',89.00);
/*!40000 ALTER TABLE `teaching` ENABLE KEYS */;
UNLOCK TABLES;

--
-- テーブル構造 instructors
--

DROP TABLE IF EXISTS `instructors`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `instructors` (
  `tno` char(8) NOT NULL,
  `tname` char(10) NOT NULL,
  `dno` varchar(4) DEFAULT NULL,
  `title` char(5) DEFAULT NULL,
  PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教員テーブル';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- データ instructors
--

LOCK TABLES `instructors` WRITE;
/*!40000 ALTER TABLE `instructors` DISABLE KEYS */;
INSERT INTO `instructors` VALUES ('t01','向左','d1','教授'),('t02','向右','d2','准教授'),('t03','王上','d3','講師'),('t04','王霞','d4','助教'),('t05','趙前','d1','准教授'),('t06','趙後','d3','講師');
/*!40000 ALTER TABLE `instructors` ENABLE KEYS */;
UNLOCK TABLES;

以下クエリを実行してください：

  1. 各学生の学籍番号、氏名、履修科目コードおよび成績を取得
  2. 各学生の氏名、履修科目名および成績を取得
  3. 「データベース」科目の不合格者の学籍番号と氏名を取得
  4. 「データベース」科目の平均点を取得
  5. 「八戒」の住所と同じ女性学生の氏名を自身結合で取得
  6. 学籍番号「2015002」の学生が履修した科目の最高点を取得
  7. 履修していない学生を含む全学生の履修状況を取得
  8. 「八戒」の住所と同じ学生の履修科目コードと成績をサブクエリで取得
  9. 「コンピュータ構成論」を履修していない学生情報を取得
  10. 北京在住者より年齢が若い他の地域の学生を取得
  11. 平均点が最も高い科目のコードと平均点を取得
  12. 学籍番号「2015002」の学生の最高点、最低点、合計点、平均点を取得
  13. 「八戒」の住所と同じ女性学生の氏名をネストサブクエリで取得
  14. 「八戒」が履修したすべての科目コードと名前をネストサブクエリで取得
  15. 「データベース」を履修した学生の氏名を取得 16.1 「データベース」を履修していない学生の氏名をNOT INで取得 16.2 「データベース」を履修していない学生の氏名をNOT EXISTSで取得
  16. すべての科目を合格した学生の学籍番号と氏名を2通りの方法で取得
  17. すべての選択科目を履修した学生の氏名を取得
  18. 「c07」と「c08」の両科目を履修した学生の氏名を取得
  19. 教評点数がない教員のコードと氏名を取得
  20. 「王上」の授業を履修していない学生の氏名を取得
  21. 2科目以上80点以上を取得した学生の氏名と平均点を取得
-- 1. 各学生の学籍番号、氏名、履修科目コードおよび成績
SELECT student_info.sno, student_info.sname, score.cno, score.grade
FROM student_info
JOIN score ON student_info.sno = score.sno;

-- 2. 各学生の氏名、履修科目名および成績
SELECT student_info.sname, courses.cname, score.grade
FROM student_info
JOIN score ON student_info.sno = score.sno
JOIN courses ON score.cno = courses.cno;

-- 3. 「データベース」科目の不合格者
SELECT student_info.sno, student_info.sname, score.grade
FROM student_info
JOIN score ON student_info.sno = score.sno
WHERE score.grade < 60 AND courses.cname = 'データベース';

-- 4. 「データベース」の平均点
SELECT AVG(score.grade)
FROM score
JOIN courses ON score.cno = courses.cno
WHERE courses.cname = 'データベース';

-- 5. 自身結合による住所一致確認
SELECT s1.sname
FROM student_info s1
JOIN student_info s2 ON s1.saddress = s2.saddress
WHERE s2.sname = '八戒' AND s1.sex = '女';

-- 6. 学籍番号「2015002」の最高点
SELECT MAX(score.grade)
FROM score
WHERE score.sno = '2015002';

-- 7. 外部結合による未履修学生の取得
SELECT stu.sno, sco.cno, sco.grade
FROM student_info stu
LEFT JOIN score sco ON stu.sno = sco.sno;

-- 8. 住所一致者の履修情報
SELECT score.cno, score.grade
FROM score
JOIN student_info ON score.sno = student_info.sno
WHERE student_info.saddress IN (
    SELECT saddress
    FROM student_info
    WHERE sname = '八戒'
);

-- 9. 「コンピュータ構成論」未履修者
SELECT student_info.sno, student_info.sname, courses.cname
FROM student_info
LEFT JOIN score ON student_info.sno = score.sno
LEFT JOIN courses ON score.cno = courses.cno
WHERE student_info.sno NOT IN (
    SELECT score.sno
    FROM score
    JOIN courses ON score.cno = courses.cno
    WHERE courses.cname = 'コンピュータ構成論'
);

-- 10. 年齢比較クエリ
SELECT sname, sbirthday
FROM student_info
WHERE sbirthday > (
    SELECT MAX(sbirthday)
    FROM student_info
    WHERE saddress = '北京'
) AND saddress != '北京';

-- 11. 平均点最大値の取得
SELECT courses.cno, AVG(score.grade) AS avg_grade
FROM score
JOIN courses ON score.cno = courses.cno
GROUP BY courses.cno
HAVING AVG(score.grade) = (
    SELECT MAX(avg_grade)
    FROM (
        SELECT AVG(score.grade) AS avg_grade
        FROM score
        GROUP BY cno
    ) AS avg_subquery
);

-- 12. 学籍番号「2015002」の統計
SELECT MAX(grade), MIN(grade), SUM(grade), AVG(grade)
FROM score
WHERE sno = '2015002';

-- 13. 住所一致の女性学生
SELECT sname
FROM student_info
WHERE saddress = (
    SELECT saddress
    FROM student_info
    WHERE sname = '八戒'
) AND sex = '女' AND sname != '八戒';

-- 14. 「八戒」の履修科目
SELECT courses.cno, courses.cname
FROM courses
WHERE cno IN (
    SELECT cno
    FROM score
    WHERE sno = (
        SELECT sno
        FROM student_info
        WHERE sname = '八戒'
    )
);

-- 15. 「データベース」履修者
SELECT DISTINCT student_info.sname
FROM student_info
JOIN score ON student_info.sno = score.sno
JOIN courses ON score.cno = courses.cno
WHERE courses.cname = 'データベース';

-- 16.1 NOT INによる非履修者
SELECT sname
FROM student_info
WHERE sno NOT IN (
    SELECT score.sno
    FROM score
    WHERE cno IN (
        SELECT cno
        FROM courses
        WHERE cname = 'データベース'
    )
);

-- 16.2 NOT EXISTSによる非履修者
SELECT sname
FROM student_info
WHERE NOT EXISTS (
    SELECT 1
    FROM score
    JOIN courses ON score.cno = courses.cno
    WHERE student_info.sno = score.sno
    AND courses.cname = 'データベース'
);

-- 17. 全科目合格者
SELECT student_info.sno, student_info.sname
FROM student_info
WHERE NOT EXISTS (
    SELECT 1
    FROM score
    WHERE score.sno = student_info.sno AND grade < 60
);

-- 18. 全選択科目履修者
SELECT DISTINCT student_info.sname
FROM student_info
JOIN score ON student_info.sno = score.sno
WHERE score.cno IN (
    SELECT cno
    FROM courses
    WHERE ctype = '選択'
);

-- 19. 両科目履修者
SELECT sname
FROM student_info
JOIN score ON student_info.sno = score.sno
WHERE sno IN (
    SELECT sno
    FROM score
    WHERE cno = 'c07'
) AND cno = 'c08';

-- 20. 教評点数なしの教員
SELECT instructors.tno, instructors.tname
FROM instructors
WHERE tno NOT IN (
    SELECT tno
    FROM teaching
);

-- 21. 「王上」の授業未履修者
SELECT sname
FROM student_info
WHERE sno NOT IN (
    SELECT score.sno
    FROM score
    WHERE cno IN (
        SELECT cno
        FROM teaching
        JOIN instructors ON teaching.tno = instructors.tno
        WHERE instructors.tname = '王上'
    )
);

-- 22. 2科目以上80点以上
SELECT sname, AVG(grade)
FROM score
JOIN student_info ON score.sno = student_info.sno
WHERE sno IN (
    SELECT sno
    FROM score
    WHERE grade > 80
    GROUP BY sno
    HAVING COUNT(*) >= 2
)
GROUP BY sno;

タグ: SQL MySQL データベース設計 結合クエリ サブクエリ

7月10日 16:02 投稿

ホットタグ

©2026 異端開発室