「DB演習sql2」(2008/06/23 (月) 15:30:03) の最新版変更点
追加された行は緑色になります。
削除された行は赤色になります。
*A-1
SELECT student_name AS "受講者の名前",kind AS "登録番号",teacher_name AS "教官の名前"
FROM student,resurve,teacher,car,dispatch
WHERE resurve.class_date = '2008-06-11'
AND resurve.class_time = '4'
AND resurve.student_no = student.student_no
AND resurve.teacher_no = teacher.teacher_no
AND dispatch.car_no = car.car_no
AND dispatch.resurve_no1 = resurve.resurve_no
;
|BGCOLOR(#D3D3D3):受講者の名前|BGCOLOR(#D3D3D3):登録番号|BGCOLOR(#D3D3D3):教官の名前|
|道下 正樹|3321|佐々木 孝|
|山川 純一|2132|別所 直木|
(2 rows)
*A-2
SELECT kind AS "登録番号"
FROM car
WHERE kind LIKE '%32'
AND mt_flag = '0'
;
|BGCOLOR(#D3D3D3):登録番号|
|2132|
|1432|
|2632|
(3 rows)
*A-3
SELECT student_name AS "受講者の名前",student.tel AS "受講者のTEL",cancel.regi_time AS "キャンセル待ち登録日時"
FROM student,cancel
WHERE cancel.student_no = student.student_no
AND cancel.class_date = '2008-06-13'
AND cancel.class_time = '2'
ORDER BY cancel.regi_time
;
|BGCOLOR(#D3D3D3):受講者の名前 | BGCOLOR(#D3D3D3):受講者のTEL | BGCOLOR(#D3D3D3):キャンセル待ち登録日時|
|大石 宏美 | 090-0000-0004 | 2008-06-03 15:46:00|
|道下 正樹 | 090-0000-0003 | 2008-06-06 13:50:00|
(2 rows)
*B-1
SELECT COUNT(car_no) AS "20080614の4時限のAT車の空数"
FROM car
WHERE mt_flag = '0'
AND car_no NOT IN (
SELECT dispatch.car_no
FROM dispatch
WHERE dispatch.resurve_no1 IN (
SELECT resurve_no
FROM resurve
WHERE resurve.class_date = '2008-06-14'
AND resurve.class_time = '4'
AND cancel_day = '0000-01-01'
)
)
;
|BGCOLOR(#D3D3D3):20080614の4時限のAT車の空数|
| 4|
(1 row)
*B-2
SELECT class_date AS "日付",class_time AS "時刻",COUNT(class_date) AS "予約数"
FROM resurve
WHERE resurve.class_date BETWEEN '2008-06-10' AND '2008-06-15'
AND cancel_day = '0000-01-01'
GROUP BY class_date,class_time
ORDER BY class_date,class_time
;
| BGCOLOR(#D3D3D3):日付 | BGCOLOR(#D3D3D3):時刻 | BGCOLOR(#D3D3D3):予約数|
| 2008-06-11 | 4 | 2|
| 2008-06-13 | 2 | 3|
| 2008-06-14 | 4 | 3|
| 2008-06-15 | 3 | 1|
(4 rows)
----
*A-1
SELECT student_name AS "受講者の名前",kind AS "登録番号",teacher_name AS "教官の名前"
FROM student,resurve,teacher,car,dispatch
WHERE resurve.class_date = '2008-06-11'
AND resurve.class_time = '4'
AND resurve.student_no = student.student_no
AND resurve.teacher_no = teacher.teacher_no
AND dispatch.car_no = car.car_no
AND dispatch.resurve_no1 = resurve.resurve_no
;
|BGCOLOR(#D3D3D3):受講者の名前|BGCOLOR(#D3D3D3):登録番号|BGCOLOR(#D3D3D3):教官の名前|
|道下 正樹|3321|佐々木 孝|
|山川 純一|2132|別所 直木|
(2 rows)
*A-2
SELECT kind AS "登録番号"
FROM car
WHERE kind LIKE '%32'
AND mt_flag = '0'
;
|BGCOLOR(#D3D3D3):登録番号|
|2132|
|1432|
|2632|
(3 rows)
*A-3
SELECT student_name AS "受講者の名前",student.tel AS "受講者のTEL",cancel.regi_time AS "キャンセル待ち登録日時"
FROM student,cancel
WHERE cancel.student_no = student.student_no
AND cancel.class_date = '2008-06-13'
AND cancel.class_time = '2'
ORDER BY cancel.regi_time
;
|BGCOLOR(#D3D3D3):受講者の名前 |BGCOLOR(#D3D3D3):受講者のTEL |BGCOLOR(#D3D3D3):キャンセル待ち登録日時|
|大石 宏美 | 090-0000-0004 | 2008-06-03 15:46:00|
|道下 正樹 | 090-0000-0003 | 2008-06-06 13:50:00|
(2 rows)
*B-1
SELECT COUNT(car_no) AS "20080614の4時限のAT車の空数"
FROM car
WHERE mt_flag = '0'
AND car_no NOT IN (
SELECT dispatch.car_no
FROM dispatch
WHERE dispatch.resurve_no1 IN (
SELECT resurve_no
FROM resurve
WHERE resurve.class_date = '2008-06-14'
AND resurve.class_time = '4'
AND cancel_day = '0000-01-01'
)
)
;
|BGCOLOR(#D3D3D3):20080614の4時限のAT車の空数|
| 4|
(1 row)
*B-2
SELECT class_date AS "日付",class_time AS "時刻",COUNT(class_date) AS "予約数"
FROM resurve
WHERE resurve.class_date BETWEEN '2008-06-10' AND '2008-06-15'
AND cancel_day = '0000-01-01'
GROUP BY class_date,class_time
ORDER BY class_date,class_time
;
|BGCOLOR(#D3D3D3):日付 |BGCOLOR(#D3D3D3):時刻 |BGCOLOR(#D3D3D3):予約数|
| 2008-06-11 | 4 | 2|
| 2008-06-13 | 2 | 3|
| 2008-06-14 | 4 | 3|
| 2008-06-15 | 3 | 1|
(4 rows)
----
表示オプション
横に並べて表示：
変化行の前後のみ表示：
