DB演習sql2
最終更新:
maha
-
view
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 ;
受講者の名前 | 登録番号 | 教官の名前 |
道下 正樹 | 3321 | 佐々木 孝 |
山川 純一 | 2132 | 別所 直木 |
(2 rows)
A-2
SELECT kind AS "登録番号" FROM car WHERE kind LIKE '%32' AND mt_flag = '0' ;
登録番号 |
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 ;
受講者の名前 | 受講者のTEL | キャンセル待ち登録日時 |
大石 宏美 | 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' ) ) ;
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 ;
日付 | 時刻 | 予約数 |
2008-06-11 | 4 | 2 |
2008-06-13 | 2 | 3 |
2008-06-14 | 4 | 3 |
2008-06-15 | 3 | 1 |
(4 rows)