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

.....................................................................
/* qry01 */

SQL> select location
  2  from section
  3  where capacity =
  4        (select max(capacity)
  5         from section)
  6  /

LOCATION
--------------------------------------------------
L509
L509
L210

/*  OR  */

SQL> edit
Wrote file afiedt.buf

  1  select distinct location
  2  from section
  3  where capacity =
  4        (select max(capacity)
  5*        from section)
SQL> /

LOCATION
--------------------------------------------------
L210
L211
L214
L507
L509
L511
M311
M500

8 rows selected.

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

SQL> col location format a10
SQL> col description format a20
SQL> edit
Wrote file afiedt.buf

  1  select s.location, c.course_no course_number, c.description, s.capacity
  2  from section s, course c
  3  where s.capacity =
  4        (select max(capacity)
  5         from section)
  6* and rownum < 5
SQL> /

LOCATION   COURSE_NUMBER DESCRIPTION            CAPACITY
---------- ------------- -------------------- ----------
L509                  10 DP Overview                  25
L509                  10 DP Overview                  25
L210                  10 DP Overview                  25
M311                  10 DP Overview                  25

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

SQL> edit
Wrote file afiedt.buf

  1  select e.student_id, s.course_no, s.section_id,
  2         to_char(s.start_date_time, 'DD-MON-RR') start_date_time
  3  from enrollment e, section s
  4  where trunc(s.start_date_time) in
  5  (select trunc(start_date_time)
  6  from section
  7  where start_date_time between to_date('01-APR-99')
  8                        and     to_date('01-MAY-99', 'DD-MON-RR')
  9  )
 10  and   e.section_id = s.section_id
 11* order by e.student_id
SQL> col start_date_time format a15
SQL> /

STUDENT_ID  COURSE_NO SECTION_ID START_DATE_TIME
---------- ---------- ---------- ---------------
       106        240        101 16-APR-99
       107         25         87 14-APR-99
       109        240        101 16-APR-99

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

SQL> edit
Wrote file afiedt.buf

  1  select e.student_id, count(s.section_id) courses_taken
  2  from enrollment e, section s
  3  where   e.section_id = s.section_id
  4* group by e.student_id
SQL> /

STUDENT_ID COURSES_TAKEN
---------- -------------
       102             2
       103             1
       104             1

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

SQL> edit
Wrote file afiedt.buf

  1  select z.state st, count(*) number_of_students
  2  from student s, zipcode z
  3  where s.zip = z.zip
  4* group by z.state
SQL> /

ST NUMBER_OF_STUDENTS
-- ------------------
CT                 18
MA                  5
MI                  1
NJ                 77
NY                163
OH                  1
WV                  3

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

SQL> select c.course_no, c.description, sum(s.capacity) total_capacity
  2  from course c, section s
  3  where c.course_no = s.course_no
  4  group by c.course_no, c.description
  5  /

 COURSE_NO DESCRIPTION          TOTAL_CAPACITY
---------- -------------------- --------------
        10 DP Overview                      15
        20 Intro to Computers               80
        25 Intro to Programming            205

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

SQL> col description format a35
SQL> col max_revenue format a12
SQL> edit
Wrote file afiedt.buf

  1  select c.course_no, c.description description,
  2         to_char(sum(s.capacity * c.cost), '$999,999.99') max_revenue
  3  from course c, section s
  4  where c.course_no = s.course_no
  5* group by c.course_no, c.description
SQL> /

 COURSE_NO DESCRIPTION                         MAX_REVENUE
---------- ----------------------------------- ------------
        10 DP Overview                           $17,925.00
        20 Intro to Computers                    $95,600.00
        25 Intro to Programming                 $244,975.00