「DB演習sql2」の編集履歴（バックアップ）一覧に戻る

DB演習sql2 - (2008/06/23 (月) 14:58:30) の編集履歴(バックアップ)

---

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)

SELECT kind AS "登録番号"
FROM car
WHERE kind LIKE '%32'
AND mt_flag = '0'
;

登録番号

---

    2132
    1432
    2632

(3 rows)

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)

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)

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 |    2 |      1
2008-06-15 |    3 |      1

(5 rows)