Assignment 8  -  David Bozarth  -  CIS 134B  -  8 July 2002

.....................................................................
/* qry01 */
SQL> edit
Wrote file afiedt.buf

  1  select distinct a.first_name first, a.last_name last, a.zip zip
  2  from   instructor a, instructor b
  3  where  a.zip = b.zip
  4  and    a.instructor_id <> b.instructor_id
  5* order by zip, last, first
SQL> /

FIRST                     LAST                      ZIP
------------------------- ------------------------- -----
Rick                      Chow                      10015
Fernand                   Hanks                     10015
Anita                     Morris                    10015
Charles                   Lowry                     10025
Nina                      Schorin                   10025
Todd                      Smythe                    10025
Tom                       Wojick                    10025

7 rows selected.

.....................................................................
/* qry02 */
SQL> edit
Wrote file afiedt.buf

  1  select distinct s1.instructor_id, s1.section_id,
  2         to_char(s1.start_date_time, 'DD-Mon-YYYY HH24:MI:SS') start_date,
  3         s1.course_no, c.description
  4  from   section s1, section s2, course c
  5  where  s1.course_no =   c.course_no
  6  and    s1.course_no <> s2.course_no
  7  and    s1.instructor_id = s2.instructor_id
  8  and    s1.start_date_time = s2.start_date_time
  9* order by s1.instructor_id, to_date(start_date, 'DD-Mon-YYYY HH24:MI:SS')
SQL> /

INSTRUCTOR_ID SECTION_ID START_DATE            COURSE_NO DESCRIPTION
------------- ---------- -------------------- ---------- -------------------------------
          101        101 16-Apr-1999 09:30:00        240 Intro to the Basic Language
          101        140 16-Apr-1999 09:30:00        134 Advanced Unix Admin
          102         88 04-May-1999 09:30:00         25 Intro to Programming
          102        149 04-May-1999 09:30:00        120 Intro to Java Programming
          103         89 15-May-1999 09:30:00         25 Intro to Programming
          103        150 15-May-1999 09:30:00        120 Intro to Java Programming
          . . .
21 rows selected.

.....................................................................
/* qry03 */
SQL> edit
Wrote file afiedt.buf

  1  select s.section_id, c.course_no, c.description, c.cost
  2  from   section s, course c
  3  where  s.course_no(+) = c.course_no
  4  and    c.cost >= 1195
  5* order by s.section_id desc
SQL> /

SECTION_ID  COURSE_NO DESCRIPTION                                              COST
---------- ---------- -------------------------------------------------- ----------
                   80 Structured Programming Techniques                        1595
                  430 JDeveloper Techniques                                    1195
       156        122 Intermediate Java Programming                            1195
       155        122 Intermediate Java Programming                            1195
      . . .

71 rows selected.

.....................................................................
/* qry04 */
SQL> edit
Wrote file afiedt.buf

  1  insert into zipcode values
  2* ('94501', 'Alameda', 'CA', user, sysdate, user, sysdate)
SQL> /

1 row created.

.....................................................................
/* qry05 */
SQL> edit
Wrote file afiedt.buf

  1  insert into student
  2  (student_id, first_name, last_name, street_address, employer, phone, zip,
  3     registration_date, created_by, created_date, modified_by, modified_date)
  4  values
  5  (student_id_seq.nextval, 'Lily', 'Quan', '1511 Linda View Place',
  6     null, '510-555-1212', '94501',
  7*    sysdate, user, sysdate, user, sysdate)
SQL> /

1 row created.

.....................................................................
/* qry06 */
SQL> select student_id, first_name, last_name, created_by
  2  from student where rownum = 1
  3  order by student_id desc
  4  /

SQL> col first_name format a15
SQL> col last_name format a15
SQL> col created_by format a10
SQL> /

STUDENT_ID FIRST_NAME      LAST_NAME       CREATED_BY
---------- --------------- --------------- ----------
       401 Lily            Quan            SYSTEM

.....................................................................
/* qry07 */
SQL> update student
  2  set    salutation = 'Ms.'
  3  where  student_id = 401
  4  /

1 row updated.

.....................................................................
/* qry08 */
SQL> update zipcode
  2  set city = 'Bay Farm Island'
  3  where  zip = '94501'
  4  /

1 row updated.

.....................................................................
/* qry09 */
SQL> delete from student where student_id = '401'
  2  /

1 row deleted.

SQL>  delete from zipcode where zip = '94501'
  2  /

1 row deleted.