データベース工学前期末

SQLの穴埋め

テーブルが与えられた状態

手書き

空欄25ヶ所×4点

2025/08/01　10:38 スライド以外の持参不可

リレーショナル代数からSQLを作る問題？

久しぶりに10時間寝た　8:00まで頑張る

関係代数

https://scrapbox.io/files/688bd74a1af1f2cc0217853f.png

https://scrapbox.io/files/688bd69c437ed17d53a26606.png

https://scrapbox.io/files/688bd6097e46e2f9cefed529.png

直積

https://scrapbox.io/files/688bd6e75f9ad529a9b4c5ad.png

https://scrapbox.io/files/688bd79294556e1354069751.png

https://scrapbox.io/files/688bd7c1a979e0afa1b37140.png

MySQLについて

1. データベースの作成

CREATE DATABASE database_name;

2. テーブルの作成

データ型

INT: 整数

VARCHAR(size): 可変長文字列

DATE: 日付

DATETIME: 日付と時刻

TEXT: 長文の文字列

constraints(=制約)は任意

NOT NULL: NULL値を許可しない

UNIQUE: 列の値が一意であること

AUTO_INCREMENT: 自動的に連番を振る（通常は主キーに設定）

code:create_table

CREATE TABLE table_name (

column1_name data_type constraints(任意),

column2_name data_type constraints(任意),

...

PRIMARY KEY (column_name)

);

3. テーブルの削除

DROP TABLE table_name;

---

データの操作

1. データの挿入 (INSERT)

code:insert_data

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

全ての列に値を挿入する場合、列名を省略できる

code:insert_data2

INSERT INTO table_name

VALUES (value1, value2, ...);

2. データの選択 (SELECT)

code:select_data

SELECT column1, column2, ...

FROM table_name;

全ての列を選択する場合

code:select_data2

SELECT *

FROM table_name;

---

条件を指定する (WHERE句)

code:select_condition

SELECT column1, column2

FROM table_name

WHERE condition;

WHERE句の条件例（condition)

column = 'value'

column > 100

column IS NULL

column IN ('value1', 'value2')

columnがvalue1、または、columnがvalue2ならばTrue

column LIKE '%text%' (%はワイルドカード)

商品テーブル内の商品コード1003のデータを参照する

code:select_data3

select * from items where item_code=1003;

並び替え (ORDER BY)

code:select_order

SELECT *

FROM table_name

取得する行数を制限する (LIMIT)

code:limit

SELECT *

FROM table_name

LIMIT number;

---

3. データの更新 (UPDATE)

code:update

UPDATE table_name

SET column1 = new_value1, column2 = new_value2, ...

WHERE condition;

WHERE句を付けないと、テーブル内の全ての行が更新されます

4. データの削除 (DELETE)

code:delete

DELETE FROM table_name

WHERE condition;

WHERE句を付けないと、テーブル内の全ての行が削除されます

---

OTHERs

テーブルの結合 (JOIN)

複数のテーブルを関連付けてデータを取得する場合に使用

INNER JOIN（INNERは省略可）

内部結合

両方のテーブルの結合条件に一致する行のみを結果として返す。

ベン図の「共通部分」のみ取得

code:inner_join

SELECT t1.column1, t2.column2

FROM table1 AS t1

INNER JOIN table2 AS t2

ON t1.common_column = t2.common_column;

---

customersテーブル:

| customer_id | name |

|-------------|-----------|

| 1 | Alice |

| 2 | Bob |

| 3 | Charlie |

ordersテーブル:

| order_id | customer_id | product |

|----------|-------------|---------|

| 101 | 1 | Laptop |

| 102 | 1 | Mouse |

| 103 | 2 | Keyboard|

| 104 | 4 | Monitor |

code:innner_join2

SELECT c.name, o.product

FROM customers AS c

INNER JOIN orders AS o ON c.customer_id = o.customer_id;

customer_idが両方のテーブルに存在する行だけが抽出されます。

| name | product |

|-------|----------|

| Alice | Laptop |

| Alice | Mouse |

| Bob | Keyboard |

LEFT JOIN

外部結合

左側のテーブルのすべての行を返します。

右側のテーブルに一致する行がない場合はNULL

code:left_join

SELECT t1.column1, t2.column2

FROM table1 AS t1

LEFT JOIN table2 AS t2

ON t1.common_column = t2.common_column;

例）

code:left_join2

SELECT c.name, o.product

FROM customers AS c

LEFT JOIN orders AS o ON c.customer_id = o.customer_id;

| name | product |

|---------|----------|

| Alice | Laptop |

| Alice | Mouse |

| Bob | Keyboard |

| Charlie | (NULL) |

▪ テーブル内のデータを参照するコマンド

※group by句 , having句 , order by句の順序は変えられない

code:command

---

1. GROUP BY

役割: 指定した列の値ごとに、データをグループ化します。

用途: グループごとに集計関数（COUNT、SUM、AVGなど）を適用して、集計結果を得たいときに使います。

具体例:「部署ごとの平均給与」を計算したい場合。

SELECT department, AVG(salary)

FROM employees

GROUP BY department;

department列の値（例: '開発部', '営業部'）ごとに、salaryの平均値を算出します。

---

2. HAVING

役割: GROUP BYでグループ化された結果に対して、さらに条件を適用して絞り込みます。

用途: 集計関数（COUNT, SUMなど）の結果を条件にしたいときに使います。WHERE句は集計前の行に対して使いますが、HAVING句は集計後のグループに対して使います。

具体例: 「平均給与が500万円を超える部署」のみを抽出したい場合。

SELECT department, AVG(salary)

FROM employees

GROUP BY department

HAVING AVG(salary) > 5000000;

GROUP BYで算出された各部署の平均給与が500万を超えるグループだけを抽出します。

---

3. ORDER BY

役割: クエリの結果セットを特定の列の値で並べ替えます。

用途: 結果を見やすい順序（昇順または降順）にしたいときに使います。

具体例:「平均給与の高い順に部署を並べたい」場合。

SELECT department, AVG(salary)

FROM employees

GROUP BY department

HAVING AVG(salary) > 5000000

ORDER BY AVG(salary) DESC;

AVG(salary)の結果を降順 (DESC) に並べ替えることで、平均給与の高い部署から表示されます。ASCを指定するか、省略すると昇順になります。

---

▪ 四則演算を用いることができる

 select product_name,price*1000 from products;

---

副問い合わせ

itemsテーブル:

item_id (主キー)

item_name

price

vendor_id (外部キー)

vendorsテーブル:

vendor_id (主キー)

vendor_name

1. 「ボールペン」を取り扱っている業者名

副問い合わせを使わない方法（JOINを使用）

この方法では、itemsテーブルとvendorsテーブルをvendor_idで結合し、items.item_nameが「ボールペン」という条件で絞り込みます。

code:ballpointpen

SELECT T2.vendor_name

FROM items AS T1

JOIN vendors AS T2 ON T1.vendor_id = T2.vendor_id

WHERE T1.item_name = 'ボールペン';

副問い合わせを使う方法

この方法では、まずitemsテーブルから「ボールペン」を販売している業者のvendor_idを抽出し、そのvendor_idを使ってvendorsテーブルから業者名を検索します。

「IN」でむすぶと、副問い合わせになります

code:ballpointpen2

SELECT vendor_name

FROM vendors

WHERE vendor_id IN (

SELECT vendor_id

FROM items

WHERE item_name = 'ボールペン'

);

2. 「100円の商品」を取り扱っている業者名

副問い合わせを使わない方法（JOINを使用）

itemsテーブルとvendorsテーブルをvendor_idで結合し、items.priceが100という条件で絞り込みます。DISTINCTを使用することで、重複する業者名を排除します。

code:100yen1:

SELECT DISTINCT T2.vendor_name

FROM items AS T1

JOIN vendors AS T2 ON T1.vendor_id = T2.vendor_id

WHERE T1.price = 100;

副問い合わせを使う方法

まずitemsテーブルから価格が100である商品のvendor_idを抽出し、そのvendor_idを使ってvendorsテーブルから業者名を検索します。ここでもIN句と組み合わせることで、複数のvendor_idに対応できます。

code:100yen2

SELECT vendor_name

FROM vendors

WHERE vendor_id IN (

SELECT vendor_id

FROM items

WHERE price = 100

);

---

companiesテーブル:

company_id

company_address (会社の所在地)

ordersテーブル:

order_number (注文番号)

company_id (注文した会社のID)

会社の所在地が大阪市の会社の注文番号を調べなさい

code:osaka_city

SELECT order_number

FROM orders

WHERE company_id IN (

SELECT company_id

FROM companies

WHERE company_address = '大阪市'

);

---

製品名(product_name)がSSDが発注された日付を問い合わせる

SQLを答えなさい

products：製品情報が含まれています。

id：製品の一意のID。

product_name：製品名（例： 'SSD'）。

order_amount：各注文に含まれる製品の情報を記録しています。

order_number：注文番号。

product_id：注文された製品のID。

orders：注文に関する情報が含まれています。

order_number：注文の一意の番号。

order_date：注文が行われた日付。

code:ssd

SELECT DISTINCT order_date

FROM orders

WHERE order_number IN (

SELECT order_number

FROM order_amount

WHERE product_id IN (

SELECT id

FROM products

WHERE product_name = 'SSD'

)

);