sqlnotes.htm; updated 2/22/01
 


STRUCTURED QUERY LANGUAGE (SQL)

Introduction.

Connecting to the ORACLE Database. Naming Difficulties.

SQL BASIC INFORMATION

SQL and UNIX Syntax.

SQL Standards - Their Characteristics and Benefits. SQL Data Types.
KEY DATA TYPES
CHAR(size) Fixed-length character data, size characters long. Maximum size=255; default=1 byte. Padded on right with blanks to full length of size.
DATE Valid dates range from Jan 1, 4712 B.C. to Dec 31, 4712 A.D.
NUMBER For NUMBER column with space for 40 digits, plus space for a decimal point and sign. Numbers may be expressed in two ways: first, with numbers 0 to 9, the signs + and -, and a decimal point(.); second, in scientific notation, e.g. 1.85E3 for 1850. Valid values are 0 and positive and negative numbers from 1.0E-130 to 9.99…E125.
VARCHAR2(size) Variable length character string, maximum size up to 2000 bytes.
MISCELLANEOUS DATA TYPES AND VARIATIONS
DECIMAL Same as NUMBER. 
FLOAT Same as NUMBER.
INTEGER Same as NUMBER.
INTEGER(size) Integer of specified size digits wide; same as NUMBER(size) of specific size digits wide.
LONG Character data of variable size up to 2Gb in length. Only one LONG column may be defined per table. LONG columns may not be used in subqueries, functions, expressions, where clauses, or indexes. A table containing LONG data may not be clustered.
LONG RAW Raw binary data; otherwise the same as LONG (used for images).
LONG VARCHAR Same as LONG
NUMBER(size) For NUMBER column of specified size in digits.
NUMBER(size,d) For NUMBER column of specified size with d digits after the decimal point, e.g. NUMBER(5,2) could contain nothing larger than 999.99 without an error being generated.
NUMBER(*) Same as NUMBER.
SMALLINT Same as NUMBER.
RAW(size) Raw binary data, size bytes long, maximum size=255 bytes.
ROWID A value that uniquely identifies a row in an Oracle database - it is returned by the pseudo-column ROWID. Table columns may not be assigned this type.
VARCHAR(size) Same as VARCHAR2.  Always use VARCHAR2.

 
SQL and SQLPLUS Basics and Error Messages.

 Case in Names and Data.




SELECT QUERIES

SQL Query Suntax.

SELECT (column list)
FROM (table list)
WHERE (condition clause)

A Sample Query.


SQL> Select City, Temperature, Humidity from WEATHER
       2 where Temperature > 80 and Humidity < 90
       3 order by Temperature ;

CITY        TEMPERATURE HUMIDITY
----------- ----------- ----------
PARIS       81          62
ATHENS      97          89
 

Describing a Table.


SQL> describe weather;
Name            Null?    Type
--------------- -------- ----
CITY                     VARCHAR2(11)
TEMPERATURE              NUMBER
HUMIDITY                 NUMBER
CONDITION                VARCHAR2(9)

SQL> describe location;
Name            Null?    Type
--------------- -------- ----
CITY                     VARCHAR2(25)
COUNTRY                  VARCHAR2(25)
CONTINENT                VARCHAR2(25)
LATITUDE                 NUMBER
NORTHSOUTH               CHAR(1)
LONGITUDE                NUMBER
EASTWEST                 CHAR(1)
 

Result of a SELECT Command.

SELECT List the columns including expressions involving columns from base tables or views to be projected into the table that will be the result of the command.
FROM Identifies the tables or views from which columns will be chosen to appear in the result table. This must also list the tables or views needed to join tables to process the query.
WHERE Includes conditions that restrict the rows selected as well as the conditions for joining two or more tables or views.



THE COMMUNITY HOSPITAL
EXAMPLE DATABASE



BILLED
BILL_NO NUMBER(5) - PRI KEY
PATIENT_NO NUMBER(9) 
ITEM_CODE  NUMBER(5)
CHARGE NUMBER(7,2)
TREATS
PHY_ID NUMBER(4) - PRI KEY
PATIENT_NO NUMBER(4) - PRI KEY
PROCEDURE_NO NUMBER(4) - PRI KEY
DATE_TREATED DATE - PRI KEY
TREAT_RESULT VARCHAR2(50)
ITEM
ITEM_CODE NUMBER(4) - PRI KEY
DESCRIPTION VARCHAR2(50)
NORMAL_CHARGE NUMBER(7,2)
PHYSICIANS
PHY_ID NUMBER(4) - PRI KEY
PHY_PHONE CHAR(8)
PHY_NAME VARCHAR2(50)
PATIENT
PATIENT_NO NUMBER(4) - PRI KEY
DATE_LAST_TREATED DATE
PAT_NAME VARCHAR2(50)
ROOM_LOCATION CHAR(4)
ROOM
ROOM_LOCATION CHAR(4) - PRI KEY
ROOM_ACCOMODATION CHAR(2)
ROOM_EXTENSION NUMBER(4)
PROCEDURES
PROCEDURE_NO NUMBER(4) - PRI KEY
PROC_DESCRIPTION VARCHAR2(50)

 

EXAMPLE #1 – SIMPLE SELECT TO RETURN A SET OF ROWS BASED ON A SIMPLE SINGLE VALUE IN THE WHERE CLAUSE.

SELECT patient_no, item_code, charge
FROM billed
WHERE patient_no = 1117;

PATIENT_NO  ITEM_CODE     CHARGE
---------- ---------- ----------
      1117       2222       7.54
      1117       2255         25

EXAMPLE #2 – ELIMINATE DUPLICATE ROWS. SELECT DISTINCT charge
FROM billed;

    CHARGE
----------
      2.21
      4.56
      6.68
      7.54
      7.75
        25
 ...not all rows are displayed here.

EXAMPLE #3 – SIMPLEST QUERY DISPLAYS ALL COLUMNS IN A TABLE.

SELECT *
FROM billed
WHERE patient_no = 1116;

PATIENT_NO  ITEM_CODE     CHARGE
---------- ---------- ----------
      1116       2242       4.56
      1116       2243       6.68

EXAMPLE #4 – QUERY DISPLAYS ALL COLUMNS AND ROWS.

SELECT *
FROM billed ;

PATIENT_NO  ITEM_CODE     CHARGE
---------- ---------- ----------
      1117       2222       7.54
      1113       2233       2.21
      1117       2255         25
      1113       2222       7.75
      1115       2245        175
...not all rows are displayed here.

ARITHMETIC, LOGICAL, AND OTHER OPERATORS.

ARITHMETIC OPERATORS
Page = 6 Page is equal to 6
Page > 6 Page is greater than 6
Page >= 6 Page is greater than or equal to 6
Page < 6 Page is less than 6
Page <= 6 Page is less than or equal to 6
Page != 6 or Page ^= 6

or Page <> 6

Page is not equal to 6

 
USING THE LIKE OPERATOR
Feature LIKE ‘Mo%’ Feature begins with the letters Mo. Note the % wildcard represents any number of spaces or characters.
Feature LIKE ‘_ _I%’ Feature has the letter I in the third position. Note the underline (_) represents one space. 

 
USING THE IS NULL and IS NOT NULL OPERATORS
Precipitation IS NULL The column named Precipitation contains no value or is unknown.
Precipitation IS NOT NULL The column named Precipitation contains some value or is known.

 
USING THE IN and BETWEEN OPERATORS
Page IN (1,2,3)Page is in the list (1,2,3)
Page BETWEEN 6 AND 10 Page is equal to 6, 10, or anything in between.
Section IN (‘A’, ‘B’, ‘F’) Section is in the list (A, B, or F ) Note use of ‘’ quotes.

 
USING LOGICAL OPERATORS – OR, AND, NOT
Section = ‘A’ OR Section = ‘B’ The value of the column Section is either A or B for this to evaluate to TRUE.
Section = ‘A’ AND Page > 2 The value of the column Section must be A and the value of the column Page must be greater than 2 for this to evaluate to TRUE.
NOT Section = ‘A’ Section must have some value other than A for this to evaluate to TRUE.

 

EXAMPLE #5 – USES LOGICAL AND OPERATOR.

SELECT charge
FROM billed
WHERE patient_no = 1116 AND charge > 5.00;

    CHARGE
----------
      6.68

EXAMPLE #6 – USES LOGICAL OR OPERATOR.

SELECT patient_no, charge
FROM billed
WHERE patient_no = 1116 OR patient_no = 1117;

PATIENT_NO     CHARGE
---------- ----------
      1117       7.54
      1117         25
      1116       4.56
      1116       6.68
      1117     167.67
      1117     222.21
      1117       4.92

EXAMPLE #7 – USES COUNT FUNCTION.

SELECT COUNT(*)
FROM billed
WHERE patient_no = 1116;

 COUNT(*)
---------
        2

EXAMPLE #8 – DISPLAYS DISTINCT ROWS – ELIMINATES DUPLICATE ROWS ON THE OUTPUT.

SELECT COUNT ( DISTINCT procedure_no )
FROM treats
WHERE patient_no = 1113;

COUNT(DISTINCTPROCEDURE_NO)
---------------------------
                          2

EXAMPLE #9 – SPECIFY MEANINGFUL COLUMN NAMES.

SELECT COUNT (DISTINCT procedure_no) AS "No. of Procedures"
FROM treats
WHERE patient_no = 1113;

No. of Procedures
-----------------
                2

DISPLAYING CONSTANTS AND CALCULATED VALUES

EXAMPLE #10. SELECT normal_charge, normal_charge*1.06
FROM item;

NORMAL_CHARGE NORMAL_CHARGE*1.06
------------- ------------------
         7.54             7.9924
         2.21             2.3426
          4.5               4.77
       284.59           301.6654
           25               26.5
...not all items are listed here.

EXAMPLE #11.

SELECT patient_no, date_discharged, date_admitted,
  (date_discharged - date_admitted + 1)
    As "No. of Days In"
FROM patient
WHERE (date_discharged - date_admitted + 1 ) > 6;

PATIENT_NO DATE_DISC DATE_ADMI No. of Days In
---------- --------- --------- --------------
      1117 22-DEC-01 16-DEC-01              7
      1114 24-DEC-01 15-DEC-01             10
      1115 25-DEC-01 15-DEC-01             11
 

SORTING, GROUPING and the BETWEEN OPERATOR

EXAMPLE #12 – USING SORTING AND GROUPING.

SELECT patient_no, SUM(charge)
FROM billed
WHERE (patient_no BETWEEN 1110 AND 1200) AND
    charge > 100
GROUP BY patient_no
HAVING SUM(charge) > 200
ORDER BY patient_no;

PATIENT_NO SUM(CHARGE)
---------- -----------
      1112      782.55
      1117      389.88


EXAMPLE #13 – MAJOR AND MINOR SORTS.

SELECT patient_no, SUM(charge) total_charge
FROM billed
WHERE (patient_no BETWEEN 1110 AND 1200) AND
    charge > 100
GROUP BY patient_no
HAVING SUM(charge) > 50
ORDER BY total_charge DESC, patient_no;

PATIENT_NO TOTAL_CHARGE
---------- ------------
      1112       782.55
      1117       389.88
      1115          175

EXAMPLE #14 – ANOTHER BETWEEN EXAMPLE.

SELECT patient_no, charge
FROM billed
WHERE item_code = 2245 AND
       charge BETWEEN 160 AND 170;

PATIENT_NO     CHARGE
---------- ----------
      1112     167.67
      1112     167.67
      1117     167.67

SELECT patient_no, charge
FROM billed
WHERE item_code = 2245;

PATIENT_NO     CHARGE
---------- ----------
      1115        175
      1112     167.67
      1112     167.67
      1117     167.67

THE IN OPERATOR


EXAMPLE #15 – USING THE IN OPERATOR.

SELECT DISTINCT patient_no, phy_id
FROM treats
WHERE phy_id IN
  (8887, 8886, 8882);

PATIENT_NO     PHY_ID
---------- ----------
      1112       8887
      1113       8882
      1113       8887
      1115       8886
      1116       8886
      1117       8887
 

MULTIPLE TABLE QUERIES
EQUI-JOIN and NATURAL JOIN EXAMPLE #16a – JOIN THREE TABLES.

COLUMN pat_name FORMAT A18;
COLUMN phy_name FORMAT A18;
SELECT DISTINCT PA.patient_no, pat_name, PH.phy_id, phy_name
FROM patient PA, physician PH, treats TR
WHERE PA.patient_no = TR.patient_no AND
      PH.phy_id = TR.phy_id AND
      PH.phy_id IN (8887, 8886, 8882);

PATIENT_NO PAT_NAME           PHY_ID PHY_NAME
---------- ------------------ ------ ------------------
      1112 Eminem               8887 Dr. Hawkeye Pierce
      1113 George Marshall      8882 Dr. Jonas Salkman
      1113 George Marshall      8887 Dr. Hawkeye Pierce
      1115 Barbara Streisand    8886 Dr. Trapper John
      1116 G. W. Bush           8886 Dr. Trapper John
      1117 Sally Field          8887 Dr. Hawkeye Pierce

EXAMPLE #16b – JOIN TWO TABLES.

SELECT billed.patient_no, date_discharged, charge
FROM billed, patient
WHERE patient.patient_no = billed.patient_no;

PATIENT_NO DATE_DISC     CHARGE
---------- --------- ----------
      1117 22-DEC-01       7.54
      1113 21-DEC-01       2.21
      1117 22-DEC-01         25
      1113 21-DEC-01       7.75
      1115 25-DEC-01        175
      1116 22-DEC-01       4.56
...not all of the rows are shown here.

 
OUTER JOIN and TABLE NAME ALIASES (ABBREVIATIONS)

EXAMPLE #17 – OUTER JOIN and USING ALIAS. SELECT p.patient_no, b.charge
FROM billed b, patient p
WHERE p.patient_no = b.patient_no(+);

PATIENT_NO     CHARGE
---------- ----------
      1111
      1112        225
      1112     167.67
      1112     167.67
      1112       4.92
      1112     222.21
      1113       2.21
      1113       7.75
      1114
      1115        175
      1116       4.56
...not all rows are shown here.

SELECT p.patient_no, b.charge
FROM billed b, patient p
WHERE p.patient_no = b.patient_no(+) AND
      b.charge IS NULL;

PATIENT_NO     CHARGE
---------- ----------
      1111
      1114
 


SUBQUERIES

EXAMPLE #18 – A COMPLEX QUERY AND THE SUBQUERY.

SELECT item.item_code, description
FROM item, billed
WHERE item.item_code = billed.item_code AND
     patient_no = 1116;

 ITEM_CODE DESCRIPTION
---------- ----------------
      2242 4 inch dressing
      2243 Syringe, 8 gauge
 

SELECT item_code, description
FROM item
WHERE item_code IN
    (SELECT item_code FROM billed
        WHERE patient_no = 1116);

ITEM_CODE DESCRIPTION
--------- ----------------
     2242 4 inch dressing
     2243 Syringe, 8 gauge

  EXAMPLE #19 – USING IN WITH THE SUBQUERY. SELECT item_code, description
FROM item
WHERE item_code IN
    (SELECT item_code FROM billed
        WHERE patient_no = 1116);


VIEW DEFINITION

EXAMPLE #20 – CREATE A VIEW.
 
CREATE VIEW patient_bill AS
    SELECT B.patient_no, P.pat_name,
        B.item_code, charge,
        description, date_discharged
    FROM patient P, billed B, item I
    WHERE P.patient_no = B.patient_no AND
        I.item_code = B.item_code;
 
View created.
  Column Description Format A22;
SELECT patient_no, item_code, charge,
        description
    FROM patient_bill
    WHERE patient_no = 1117;
 
PATIENT_NO ITEM_CODE   CHARGE DESCRIPTION
---------- --------- -------- ---------------------
      1117      2222     7.54 Syringe, 19 gauge
      1117      2255       25 Saline Soln, 1 liter
      1117      2245   167.67 Surgical Prep Pack #8
      1117      2224   222.21 Surgical Prep Pack #4
      1117      2267     4.92 Bed Pan
 

DERIVED COLUMNS


EXAMPLE #21 – DERIVED COLUMNS.
 
CREATE VIEW room_charge (occupant, room_chgs) AS
    SELECT pat_name, sum(CHARGE)
    FROM patient_bill
    WHERE item_code BETWEEN 2200 AND 2250
    GROUP BY pat_name;
 

EXAMPLE #21 – SELECTING FROM A VIEW.

Column occupant Format A20;
SELECT occupant, room_chgs
    FROM room_charge
    WHERE room_chgs > 100;
 
OCCUPANT              ROOM_CHGS
-------------------- ----------
Barbara Streisand           175
Eminem                   782.55
Sally Field              397.42  
 which is equivalent to:
SELECT pat_name, SUM(charge)
    FROM patient_bill
    WHERE item_code BETWEEN 2200 AND 2250
    GROUP BY pat_name
    HAVING SUM(charge) > 100;

PAT_NAME             SUM(charge)
-------------------- -----------
Barbara Streisand            175
Eminem                    782.55
Sally Field               397.42



Attached here is the Script file used to create and populate the tables used in this handout.  They files assume the existence of the DATA tablespace and the DATA_INDEX tablespace for storage of tables and indexes.  The user account assumed is named DBOCK.  If you run the script to create the tables with data in your own Oracle account, replace the name of the user account with your own account name.  Do not attempt to create the PUBLIC SYNONYMS since these already exist for the DBOCK account.

REM  Table ROOM
        drop table ROOM;
        create table ROOM (
          ROOM_LOCATION  char(4)
            constraint PK_ROOM primary key
              using index tablespace DATA_INDEX
              pctfree 5,
          ROOM_ACCOMMODATION  char(2)
            constraint NN_ACCOMMODATION NOT NULL,
          ROOM_EXTENSION      number(4) default NULL
    )
        tablespace DATA;

Insert into ROOM values ('GM01', 'P2', 1111);
Insert into ROOM values ('GM02', 'P2', 1112);
Insert into ROOM values ('GM03', 'P2', 1113);
Insert into ROOM values ('GM04', 'P2', 1114);
Insert into ROOM values ('IC01', 'P1', 2221);
Insert into ROOM values ('IC02', 'P1', 2222);
Insert into ROOM values ('IC03', 'P1', 2223);

REM  Table PATIENT
        drop table PATIENT;
        create table PATIENT (
          PATIENT_NO        number(4)
            constraint PK_PATIENT primary key
              using index tablespace DATA_INDEX
              pctfree 5,
          DATE_ADMITTED     date  default sysdate
            constraint NN_DATE_ADMITTED NOT NULL,
          DATE_DISCHARGED   date default NULL,
          PAT_NAME          varchar2(50)
            constraint NN_PAT_NAME NOT NULL,
          ROOM_LOCATION     char(4)
            constraint NN_ROOM_LOCATION NOT NULL,
       constraint FK_PATIENT_ROOM foreign key (ROOM_LOCATION)
          references ROOM
          on delete cascade
    )
        tablespace DATA;

Insert into PATIENT values (1111, '15-DEC-01', '20-DEC-01', 'Tom Thumb', 'GM03');
Insert into PATIENT values (1117, '16-DEC-01', '22-DEC-01', 'Sally Field', 'GM01');
Insert into PATIENT values (1113, '17-DEC-01', '21-DEC-01', 'George Marshall', 'GM03');
Insert into PATIENT values (1114, '15-DEC-01', '24-DEC-01', 'William Clinton', 'GM02');
Insert into PATIENT values (1116, '18-DEC-01', '22-DEC-01', 'G. W. Bush', 'IC01');
Insert into PATIENT values (1115, '15-DEC-01', '25-DEC-01', 'Barbara Streisand', 'IC03');
Insert into PATIENT values (1112, '18-DEC-01', '20-DEC-01', 'Eminem', 'GM01');

REM  Table ITEM
        drop table ITEM;
        create table ITEM (
          ITEM_CODE      number(4)
            constraint PK_ITEM primary key
              using index tablespace DATA_INDEX
              pctfree 5,
          DESCRIIPTION   varchar2(50) default NULL,
          NORMAL_CHARGE  number(7,2)
    )
        tablespace DATA;

Insert into ITEM values (2222, 'Syringe, 19 gauge', 7.54);
Insert into ITEM values (2233, '9 inch gauze', 2.21);
Insert into ITEM values (2244, 'Tylenol 800MG', 4.50);
Insert into ITEM values (2223, 'Morphine Drip, 0.2 liter', 284.59);
Insert into ITEM values (2255, 'Saline Soln, 1 liter', 25.00);
Insert into ITEM values (2243, 'Syringe, 8 gauge', 6.68);
Insert into ITEM values (2267, 'Bed Pan', 4.92);
Insert into ITEM values (2266, 'Surgical Prep Pack #2', 195.95);
Insert into ITEM values (2265, '4 inch gauze', 1.19);
Insert into ITEM values (2245, 'Surgical Prep Pack #8', 167.67);
Insert into ITEM values (2268, 'Bandaid, 3 inch', 0.76);
Insert into ITEM values (2242, '4 inch dressing', 4.56);
Insert into ITEM values (2224, 'Surgical Prep Pack #4', 222.21);

REM  Table PHYSICIAN
        drop table PHYSICIAN;
        create table PHYSICIAN (
          PHY_ID         number(4)
            constraint PK_PHYSICIAN primary key
              using index tablespace DATA_INDEX
              pctfree 5,
          PHY_PHONE      char(8) default NULL,
          PHY_NAME       varchar2(50)
            constraint NN_PHY_NAME NOT NULL
    )
        tablespace DATA;

Insert into PHYSICIAN values (8880, '888-0001', 'Dr. Oprah Heart');
Insert into PHYSICIAN values (8887, '888-0007', 'Dr. Hawkeye Pierce');
Insert into PHYSICIAN values (8882, '888-0002', 'Dr. Jonas Salkman');
Insert into PHYSICIAN values (8884, '888-0004', 'Dr. Winifred Winfield');
Insert into PHYSICIAN values (8886, '888-0006', 'Dr. Trapper John');

REM  Table PROCEDURES
        drop table PROCEDURES;
        create table PROCEDURES (
          PROCEDURE_NO       number(4)
            constraint PK_PROCEDURES primary key
              using index tablespace DATA_INDEX
              pctfree 5,
          PROC_DESCRIPTION  varchar2(50)
    )
        tablespace DATA;

Insert into PROCEDURES values (3225, 'Appendectomy');
Insert into PROCEDURES values (3233, 'Lobotomy');
Insert into PROCEDURES values (3234, 'Thoracic Procedure');
Insert into PROCEDURES values (3232, 'Cervical Disk');
Insert into PROCEDURES values (3230, 'Hip Replacement');
Insert into PROCEDURES values (3231, 'Arthroscopic Procedure');
Insert into PROCEDURES values (3237, 'Ingrown Toenail');

REM  Table TREATS
 drop table TREATS;
  create table TREATS (
    PHY_ID       number(4),
          PATIENT_NO   number(4),
    PROCEDURE_NO number(5),
    DATE_TREATED date,
          TREAT_RESULT varchar2(50) default NULL,
        constraint FK_TREATS_PATIENT foreign key (PATIENT_NO)
          references PATIENT
          on delete cascade,
        constraint FK_TREATS_PHYSICIAN foreign key (PHY_ID)
          references PHYSICIAN
          on delete cascade,
       constraint FK_TREATS_PROCEDURES foreign key (PROCEDURE_NO)
          references PROCEDURES
          on delete cascade,
        constraint PK_TREATS
          primary key (PHY_ID, PATIENT_NO, PROCEDURE_NO, DATE_TREATED)
          using index tablespace DATA_INDEX
          pctfree 5
   )
        tablespace DATA;

Insert into TREATS values (8887, 1117, 3232, '17-DEC-01','Repaired Disk OK');
Insert into TREATS values (8882, 1113, 3230, '18-DEC-01','Repaired Right Hip OK');
Insert into TREATS values (8887, 1113, 3232, '17-DEC-01','Repaired Disk OK');
Insert into TREATS values (8884, 1114, 3233, '15-DEC-01','Brain Partially Removed');
Insert into TREATS values (8886, 1116, 3234, '19-DEC-01','Removed Left Lung');
Insert into TREATS values (8880, 1111, 3237, '15-DEC-01','Removed toenail');
Insert into TREATS values (8886, 1115, 3230, '17-DEC-01','Repaired Left Hip OK');
Insert into TREATS values (8887, 1112, 3231, '18-DEC-01','Repaired Left Knee OK');
Insert into TREATS values (8887, 1112, 3231, '19-DEC-01','Repaired Right Knee OK');

REM  Table BILLED
 drop table BILLED;
  create table BILLED (
    BILL_NO    number(5),
          PATIENT_NO number(4),
    ITEM_CODE  number(5),
    CHARGE     number(7,2),
        constraint FK_BILLED_PATIENT foreign key (PATIENT_NO)
          references PATIENT
          on delete cascade,
        constraint FK_BILLED_ITEM foreign key (ITEM_CODE)
          references ITEM
          on delete cascade,
        constraint PK_BILLED
          primary key (BILL_NO)
          using index tablespace DATA_INDEX
          pctfree 5
   )
        tablespace DATA;

Insert into BILLED values (00001, 1117, 2222, 7.54);
Insert into BILLED values (00002, 1113, 2233, 2.21);
Insert into BILLED values (00003, 1117, 2255, 25.00);
Insert into BILLED values (00004, 1113, 2222, 7.75);
Insert into BILLED values (00005, 1115, 2245, 175.00);
Insert into BILLED values (00006, 1116, 2242, 4.56);
Insert into BILLED values (00007, 1112, 2224, 225.00);
Insert into BILLED values (00008, 1112, 2245, 167.67);
Insert into BILLED values (00009, 1116, 2243, 6.68);
Insert into BILLED values (00010, 1112, 2245, 167.67);
Insert into BILLED values (00011, 1112, 2224, 222.21);
Insert into BILLED values (00012, 1112, 2267, 4.92);
Insert into BILLED values (00013, 1117, 2245, 167.67);
Insert into BILLED values (00014, 1117, 2224, 222.21);
Insert into BILLED values (00015, 1117, 2267, 4.92);
 

REM End of Database Table and Data Insertion Code
REM Now create the public synonyms to allow easy access
REM by students to the tables.

drop public synonym BILLED;
create public synonym BILLED for DBOCK.BILLED;
drop public synonym ROOM;
create public synonym ROOM for DBOCK.ROOM;
drop public synonym PATIENT;
create public synonym PATIENT for DBOCK.PATIENT;
drop public synonym ITEM;
create public synonym ITEM for DBOCK.ITEM;
drop public synonym TREATS;
create public synonym TREATS for DBOCK.TREATS;
drop public synonym PHYSICIAN ;
create public synonym PHYSICIAN for DBOCK.PHYSICIAN;
drop public synonym PROCEDURES;
create public synonym PROCEDURES for DBOCK.PROCEDURES;
 

REM End of Script



END OF NOTES