※上記の広告は60日以上更新のない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)