DB演習sql2 - まはめも。@wiki

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)

---