Assignment 5  -  David Bozarth  -  CIS 134B  -  1 July 2002

/* qry01 */

SQL> select course_no
  2  from course
  3  where cost is null
  4  /

 COURSE_NO
----------
       450

.....................................................................
/* qry02 */

SQL> edit
Wrote file afiedt.buf

  1  select course_no, to_char(nvl(cost, 0), '$9999.00') dollars
  2  from course
  3* order by nvl(cost, 0)
SQL> /

 COURSE_NO DOLLARS
---------- ---------
       450      $.00
       135  $1095.00
       230  $1095.00
       240  $1095.00

.....................................................................
/* qry03 */

SQL> select distinct to_char(registration_date, 'fmDay') weekday
  2  from student
  3  /

WEEKDAY
---------
Friday
Monday
Saturday
Sunday
Thursday
Tuesday
Wednesday

7 rows selected.

.....................................................................
/* qry04 */

SQL> select distinct salutation
  2  from instructor
  3  /

SALUT
-----
Dr
Hon
Mr
Ms
Rev

.....................................................................
/* qry05 */

SQL> edit
Wrote file afiedt.buf

  1  select decode(salutation, 'Dr',  'Doctor',
  2                            'Hon', 'Honorable',
  3                            'Mr',  'Mister',
  4                            'Ms',  'Miss',
  5                            'Rev', 'Reverend') full_sal,
  6         first_name, last_name
  7  from instructor
  8* order by salutation
SQL> /

FULL_SAL  FIRST_NAME                LAST_NAME
--------- ------------------------- -------------------------
Doctor    Marilyn                   Frantzen
Honorable Rick                      Chow
Mister    Fernand                   Hanks
Mister    Tom                       Wojick
Mister    Gary                      Pertez
Mister    Charles                   Lowry
Miss      Nina                      Schorin
Miss      Anita                     Morris
Miss      Irene                     Willig
Reverend  Todd                      Smythe

10 rows selected.

.....................................................................
/* qry06 */

SQL> col weekday format a10
SQL> col start_date format a10
SQL> col start_time format a10
SQL> col next_wed format a10
SQL> EDIT
Wrote file afiedt.buf

  1  select
  2  distinct to_char(start_date_time, 'Day') weekday,
  3           to_char(start_date_time, 'DD-MON-RR') start_date,
  4           to_char(start_date_time, 'HH24:MI') start_time,
  5           to_char(next_day(start_date_time, 'Wednesday'), 'DD-MON-RR') next_wed
  6  from section
  7  where start_date_time >= to_date('14-APR-99 10:30', 'DD-MON-RR HH24:MI')
  8  and   start_date_time <= to_date('14-JUL-99 09:30', 'DD-MON-RR HH24:MI')
  9  and   to_char(start_date_time, 'Day') = 'Wednesday'
 10* order by to_date(start_date, 'DD-MON-RR'), to_date(start_time, 'HH24:MI')
SQL> /

WEEKDAY    START_DATE START_TIME NEXT_WED
---------- ---------- ---------- ----------
Wednesday  14-APR-99  10:30      21-APR-99
Wednesday  02-JUN-99  09:30      09-JUN-99
Wednesday  09-JUN-99  09:30      16-JUN-99
Wednesday  14-JUL-99  09:30      21-JUL-99

.....................................................................
/* qry07 */

SQL> col adjusted_average format a16
SQL> edit
Wrote file afiedt.buf

  1  select to_char(1.2 * avg(nvl(cost, 0)), '$9,999.99') adjusted_average,
  2         count(*) number_of_courses
  3* from course
SQL> /

ADJUSTED_AVERAGE NUMBER_OF_COURSES
---------------- -----------------
 $1,390.20                      30

.....................................................................
/* qry08 */

SQL> select to_char(max(cost), '$9,999.99') max_cost,
  2         to_char(min(cost), '$9,999.99') min_cost
  3  from course
  4  /

MAX_COST   MIN_COST
---------- ----------
 $1,595.00  $1,095.00

.....................................................................
/* qry09 */

SQL> select zip, count(zip)
  2  from student
  3  group by zip
  4  /

ZIP   COUNT(ZIP)
----- ----------
01247          1
02124          1
02155          1
. . .

145 rows selected.

.....................................................................
/* qry10 */

SQL> edit
Wrote file afiedt.buf

  1  select zip, count(zip)
  2  from student
  3  group by zip
  4* having count(zip) >= 5
SQL> /

ZIP   COUNT(ZIP)
----- ----------
07010          6
07024          9
07042          5
07047          5
11209          5
11355          5
11368          6
11372          5
11373          6
11375          5

10 rows selected.

.....................................................................
/* qry11 */

SQL> edit
Wrote file afiedt.buf

  1  select zip, count(zip)
  2  from student
  3  where zip like '0%'
  4  group by zip
  5* having count(zip) >= 5
SQL> /

ZIP   COUNT(ZIP)
----- ----------
07010          6
07024          9
07042          5
07047          5