Assignment 4  -  David Bozarth  -  CIS 134B  -  24 June 2002

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

  1  select state st, zip
  2  from zipcode
  3* order by state, zip
SQL> /

ST ZIP
-- -----
CT 06401
CT 06455
CT 06483

.....................................................................
/* qry02 */
SQL> select salutation, first_name, last_name
  2  from instructor
  3  where rownum < 6
  4  order by last_name, first_name
  5  /

SALUT FIRST_NAME                LAST_NAME
----- ------------------------- -------------------------
Mr    Fernand                   Hanks
Ms    Anita                     Morris
Mr    Gary                      Pertez
Ms    Nina                      Schorin
Mr    Tom                       Wojick

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

  1  select zip, lower(state) st
  2  from zipcode
  3  where state in ('PR', 'MA') and rownum < 5
  4* order by state desc, zip
SQL> /

ZIP   ST
----- --
00914 pr
01247 ma
02124 ma
02155 ma

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

  1  select (salutation || ' ' || first_name || ' ' || last_name) as "Full Name"
  2  from student
  3  where last_name > 'Waldon' and last_name < 'Yourish'
  4* order by salutation
SQL> /

Full Name
---------------------------------------------------------
Mr. Larry Walter
Mr. Daniel Wicelinski
Mr. Artie Ward
Ms. Yvonne Williams
Ms. Reeva Yeroushalmi
Ms. Vera Wetcel
Ms. Sarah Wilson
Ms. Donna Walston
Ms. Yvonne Winnicki

9 rows selected.

.....................................................................
/* qry05 */
SQL> select (instructor_id || ',' || 
  2          salutation    || ',' || 
  3          first_name    || ',' || 
  4          last_name     || ',' ||
  5          zip)          as "FULL NAME"
  6  from instructor
  7  order by last_name
  8  /

FULL NAME
------------------------------------------
109,Hon,Rick,Chow,10015
107,Dr,Marilyn,Frantzen,10005
101,Mr,Fernand,Hanks,10015
108,Mr,Charles,Lowry,10025

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

  1  select last_name, first_name,
  2         translate(to_char(student_id), '1234567890', '!@#$%^&*()') "ENCODED ID"
  3  from student
  4* order by student_id desc
SQL> /

LAST_NAME                 FIRST_NAME                ENCODED ID
------------------------- ------------------------- --------------------------------
Abdou                     Jerry                     #((
Lloyd                     Margaret                  #(&
Norman                    James E.                  #(^

.....................................................................
/* qry07 */
SQL> select (salutation || ' ' || first_name || ' ' || last_name) as "Full Name"
  2  from student
  3  where last_name > 'Waldon' and last_name < 'Yourish' and instr(first_name, 'a') > 0
  4  order by length(last_name)
  5  /

Full Name
---------------------------------------------------------
Mr. Larry Walter
Ms. Sarah Wilson
Ms. Vera Wetcel
Ms. Donna Walston