database
thegs1
myfriendcode.docx· SQL Commands and their output:
> /* Step-0 used to create and load Assign03_3_5 tables */
/* CREATE TABLE TRAVEL */
> CREATE TABLE TBL_PRJ_TRAVEL (
TRV_CODE NUMBER(5) PRIMARY KEY,
TRV_DESTINATION VARCHAR(20) NOT NULL,
TRV_ORIGIN VARCHAR(20) NOT NULL,
TRV_DATE_START DATE NOT NULL)
CREATE TABLE succeeded.
/* CREATE TABLE PASSENGER */
> CREATE TABLE TBL_PRJ_PASSENGER (
PASSENGER_ID VARCHAR(20) PRIMARY KEY,
PASSENGER_FN VARCHAR(25) NOT NULL,
PASSENGER_LN VARCHAR(20) NOT NULL,
ENFANT_or_ADULT VARCHAR(6) NOT NULL,
Handicapped VARCHAR(3) NOT NULL)
CREATE TABLE succeeded.
/* CREATE TABLE BAGGAGE */
> CREATE TABLE TBL_PRJ_BAGGAGE (
BAG_NUM NUMBER(8) PRIMARY KEY,
PASSENGER_ID VARCHAR(20) NOT NULL,
BAG_WEIGHT NUMBER(2) NOT NULL,
BAG_OVERWEIGHT NUMBER(3) NOT NULL,
PAYMENT_METHOD VARCHAR(10) NOT NULL,
FOREIGN KEY (PASSENGER_ID) REFERENCES TBL_PRJ_PASSENGER(PASSENGER_ID))
CREATE TABLE succeeded.
/* CREATE TABLE SEAT */
> CREATE TABLE TBL_PRJ_SEAT (
SEAT_NUM VARCHAR(5) PRIMARY KEY,
AISLE VARCHAR(1) NOT NULL)
CREATE TABLE succeeded.
/* CREATE TABLE TICKET */
> CREATE TABLE TBL_PRJ_TICKET (
TICKET_NUM NUMBER(6) PRIMARY KEY,
TICKET_PRICE NUMBER(9,2) DEFAULT 0.00 NOT NULL,
TRV_CODE NUMBER(5) NOT NULL,
SEAT_NUM VARCHAR(5) NOT NULL,
TRV_DATE_ARRIVAL DATE NOT NULL,
FOREIGN KEY (TRV_CODE) REFERENCES TBL_PRJ_TRAVEL(TRV_CODE),
FOREIGN KEY (SEAT_NUM) REFERENCES TBL_PRJ_SEAT(SEAT_NUM))
CREATE TABLE succeeded.
· Used to create indexes and / or sequences, if appropriate
No index and / or sequences
· Used to insert initial sample data into all tables
/* INSERT VALUES INTO TABLE TRAVEL */
> /* TRAVEL rows */
> INSERT INTO TBL_PRJ_TRAVEL VALUES (00001,'DELWARE','AKRON','04-NOV-11')
1 rows inserted
> INSERT INTO TBL_PRJ_TRAVEL VALUES (00002,'COLUMBUS','CLEVELAND','06-JUN-11')
1 rows inserted
> INSERT INTO TBL_PRJ_TRAVEL VALUES (00003,'CLEVELAND','CINCINATTI','07-JUL-11')
1 rows inserted
> INSERT INTO TBL_PRJ_TRAVEL VALUES (00004,'AKRON','CIRCLEVILLE','12-DEC-10')
1 rows inserted
> INSERT INTO TBL_PRJ_TRAVEL VALUES (00005,'DAYTON','COLUMBUS','01-JAN-11')
1 rows inserted
> INSERT INTO TBL_PRJ_TRAVEL VALUES (00006,'PITTSBURG','COLUMBUS','03-MAR-10')
1 rows inserted
> INSERT INTO TBL_PRJ_TRAVEL VALUES (00007,'CINCINATTI','DELWARE','07-APR-11')
1 rows inserted
> INSERT INTO TBL_PRJ_TRAVEL VALUES (00008,'CIRCLEVILLE','PITTSBURG','06-MAY-12')
1 rows inserted
/* INSERT VALUES INTO TABLE PASSENGER */
> /* PASSENGER rows */
> INSERT INTO TBL_PRJ_PASSENGER VALUES ('PASSGR-#1321','FRANCK','YEBOUE','ADULT','NO')
1 rows inserted
> INSERT INTO TBL_PRJ_PASSENGER VALUES ('PASSGR-#1331','YANNICK','HARRIS','ADULT','NO')
1 rows inserted
> INSERT INTO TBL_PRJ_PASSENGER VALUES ('PASSGR-#1341','ROGER','BRIAN','ADULT','NO')
1 rows inserted
> INSERT INTO TBL_PRJ_PASSENGER VALUES ('PASSGR-#1351','DOUGLAS','ROGER','ADULT','NO')
1 rows inserted
> INSERT INTO TBL_PRJ_PASSENGER VALUES ('PASSGR-#1361','HENRY','MOORE','ENFANT','NO')
1 rows inserted
> INSERT INTO TBL_PRJ_PASSENGER VALUES ('PASSGR-#1371','LIZ','ZEN','ADULT','YES')
1 rows inserted
> INSERT INTO TBL_PRJ_PASSENGER VALUES ('PASSGR-#1381','NENE','DORA','ADULT','NO')
1 rows inserted
> INSERT INTO TBL_PRJ_PASSENGER VALUES ('PASSGR-#1301','HELEN','LEWIS','ENFANT','NO')
1 rows inserted
/* INSERT VALUES INTO TABLE BAGGAGE */
> /* BAGGAGE rows */
> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00001123,'PASSGR-#1361',14,000,'CASH')
1 rows inserted
> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00001124,'PASSGR-#1321',00,000,'CASH')
1 rows inserted
> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00001125,'PASSGR-#1371',23,013,'CREDIT')
1 rows inserted
> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00001126,'PASSGR-#1331',17,000,'DEBIT')
1 rows inserted
> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00001127,'PASSGR-#1301',03,000,'CASH')
1 rows inserted
> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00007728,'PASSGR-#1341',12,000,'CREDIT')
1 rows inserted
> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00009929,'PASSGR-#1381',01,000,'CASH')
1 rows inserted
> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00003630,'PASSGR-#1351',10,650,'CREDIT')
1 rows inserted
/* INSERT VALUES INTO TABLE SEAT */
> /* SEAT rows */
> INSERT INTO TBL_PRJ_SEAT VALUES ('A-001','A')
1 rows inserted
> INSERT INTO TBL_PRJ_SEAT VALUES ('A-003','A')
1 rows inserted
> INSERT INTO TBL_PRJ_SEAT VALUES ('B-001','A')
1 rows inserted
> INSERT INTO TBL_PRJ_SEAT VALUES ('B-002','B')
1 rows inserted
> INSERT INTO TBL_PRJ_SEAT VALUES ('B-003','B')
1 rows inserted
> INSERT INTO TBL_PRJ_SEAT VALUES ('A-017','A')
1 rows inserted
> INSERT INTO TBL_PRJ_SEAT VALUES ('B-030','B')
1 rows inserted
> INSERT INTO TBL_PRJ_SEAT VALUES ('C-008','C')
1 rows inserted
/* INSERT VALUES INTO TABLE TICKET */
> /* TICKET rows */
> INSERT INTO TBL_PRJ_TICKET VALUES ('002021',45.23,00001,'A-001','04-NOV-11')
1 rows inserted
> INSERT INTO TBL_PRJ_TICKET VALUES ('002022',42.32,00002,'A-003','06-JUN-11')
1 rows inserted
> INSERT INTO TBL_PRJ_TICKET VALUES ('002023',14.93,00003,'B-001','07-JUL-11')
1 rows inserted
> INSERT INTO TBL_PRJ_TICKET VALUES ('002024',63.24,00004,'B-002','12-DEC-10')
1 rows inserted
> INSERT INTO TBL_PRJ_TICKET VALUES ('002025',58.36,00005,'C-008','01-JAN-11')
1 rows inserted
> INSERT INTO TBL_PRJ_TICKET VALUES ('002026',43.25,00006,'B-030','03-MAR-10')
1 rows inserted
> INSERT INTO TBL_PRJ_TICKET VALUES ('002027',78.56,00007,'A-017','07-APR-11')
1 rows inserted
> INSERT INTO TBL_PRJ_TICKET VALUES ('002028',35.45,00008,'B-003','06-MAY-12')
1 rows inserted
· Used to list the contents of all tables after inserting the initial sample data
> /* CONTENT OF TABLE TRAVEL*/
> SELECT * FROM TBL_PRJ_TRAVEL
TRV_CODE TRV_DESTINATION TRV_ORIGIN TRV_DATE_START
---------------------- -------------------- -------------------- -------------------------
1 DELWARE AKRON 04-NOV-11
2 COLUMBUS CLEVELAND 06-JUN-11
3 CLEVELAND CINCINATTI 07-JUL-11
4 AKRON CIRCLEVILLE 12-DEC-10
5 DAYTON COLUMBUS 01-JAN-11
6 PITTSBURG COLUMBUS 03-MAR-10
7 CINCINATTI DELWARE 07-APR-11
8 CIRCLEVILLE PITTSBURG 06-MAY-12
8 rows selected
> /* CONTENT OF TABLE TPASSENGER*/
> SELECT * FROM TBL_PRJ_PASSENGER
PASSENGER_ID PASSENGER_FN PASSENGER_LN ENFANT_OR_ADULT HANDICAPPED
-------------------- ------------------------- -------------------- --------------- -----------
PASSGR-#1321 FRANCK YEBOUE ADULT NO
PASSGR-#1331 YANNICK HARRIS ADULT NO
PASSGR-#1341 ROGER BRIAN ADULT NO
PASSGR-#1351 DOUGLAS ROGER ADULT NO
PASSGR-#1361 HENRY MOORE ENFANT NO
PASSGR-#1371 LIZ ZEN ADULT YES
PASSGR-#1381 NENE DORA ADULT NO
PASSGR-#1301 HELEN LEWIS ENFANT NO
8 rows selected
> /* CONTENT OF TABLE BAGGAGE*/
> SELECT * FROM TBL_PRJ_BAGGAGE
BAG_NUM PASSENGER_ID BAG_WEIGHT BAG_OVERWEIGHT PAYMENT_METHOD
---------------------- -------------------- ---------------------- ---------------------- --------------
1123 PASSGR-#1361 14 0 CASH
1124 PASSGR-#1321 0 0 CASH
1125 PASSGR-#1371 23 13 CREDIT
1126 PASSGR-#1331 17 0 DEBIT
1127 PASSGR-#1301 3 0 CASH
7728 PASSGR-#1341 12 0 CREDIT
9929 PASSGR-#1381 1 0 CASH
3630 PASSGR-#1351 10 650 CREDIT
8 rows selected
> /* CONTENT OF TABLE SEAT */
> SELECT * FROM TBL_PRJ_SEAT
SEAT_NUM AISLE
-------- -----
A-001 A
A-003 A
B-001 A
B-002 B
B-003 B
A-017 A
B-030 B
C-008 C
8 rows selected
> /* CONTENT OF TABLE TICKET */
> SELECT * FROM TBL_PRJ_TICKET
TICKET_NUM TICKET_PRICE TRV_CODE SEAT_NUM TRV_DATE_ARRIVAL
---------------------- ---------------------- ---------------------- -------- -------------------------
2021 45.23 1 A-001 04-NOV-11
2022 42.32 2 A-003 06-JUN-11
2023 14.93 3 B-001 07-JUL-11
2024 63.24 4 B-002 12-DEC-10
2025 58.36 5 C-008 01-JAN-11
2026 43.25 6 B-030 03-MAR-10
2027 78.56 7 A-017 07-APR-11
2028 35.45 8 B-003 06-MAY-12
8 rows selected
· Used to produce basic system queries including table joins
> /*WANT TO KNOW EACH PASSENGER AND THE BAGGAGE NUMBER THAT HE/SHE POSSESSES */
> SELECT PASSENGER_FN, PASSENGER_LN, P.PASSENGER_ID, BAG_NUM
FROM TBL_PRJ_PASSENGER P, TBL_PRJ_BAGGAGE D
WHERE P.PASSENGER_ID = D.PASSENGER_ID
ORDER BY P.PASSENGER_LN
PASSENGER_FN PASSENGER_LN PASSENGER_ID BAG_NUM
------------------------- -------------------- -------------------- ----------------------
ROGER BRIAN PASSGR-#1341 7728
NENE DORA PASSGR-#1381 9929
YANNICK HARRIS PASSGR-#1331 1126
HELEN LEWIS PASSGR-#1301 1127
HENRY MOORE PASSGR-#1361 1123
DOUGLAS ROGER PASSGR-#1351 3630
FRANCK YEBOUE PASSGR-#1321 1124
LIZ ZEN PASSGR-#1371 1125
8 rows selected
> /*WANT TO KNOW THE DESTINATIONS AND THE PRICE FOR THE TRIP FOR TICKETS THAT ARE MORE THAN $25*/
> SELECT TICKET_PRICE, TRV_DATE_START, TRV_DATE_ARRIVAL, T.TRV_CODE
FROM TBL_PRJ_TICKET T , TBL_PRJ_TRAVEL V
WHERE T.TRV_CODE = V.TRV_CODE
AND T.TICKET_PRICE > 50
TICKET_PRICE TRV_DATE_START TRV_DATE_ARRIVAL TRV_CODE
---------------------- ------------------------- ------------------------- ----------------------
63.24 12-DEC-10 12-DEC-10 4
58.36 01-JAN-11 01-JAN-11 5
78.56 07-APR-11 07-APR-11 7
> /* CREATE A MANAGER VIEW THAT SELECT COMPLETE INFORMATION FROM TABLES */
> CREATE VIEW MANAGER AS (
SELECT TICKET_NUM, V.TRV_CODE, S.SEAT_NUM, AISLE, TRV_DESTINATION, TRV_ORIGIN, TICKET_PRICE
FROM TBL_PRJ_TICKET T, TBL_PRJ_SEAT S, TBL_PRJ_TRAVEL V
WHERE S.SEAT_NUM = T.SEAT_NUM
AND V.TRV_CODE = T.TRV_CODE)
CREATE VIEW succeeded.
> /* DISPLAY THE MANAGER'S VIEW CREATED */
> SELECT * FROM MANAGER
TICKET_NUM TRV_CODE SEAT_NUM AISLE TRV_DESTINATION TRV_ORIGIN TICKET_PRICE
---------------------- ---------------------- -------- ----- -------------------- -------------------- ----------------------
2021 1 A-001 A DELWARE AKRON 45.23
2022 2 A-003 A COLUMBUS CLEVELAND 42.32
2023 3 B-001 A CLEVELAND CINCINATTI 14.93
2024 4 B-002 B AKRON CIRCLEVILLE 63.24
2025 5 C-008 C DAYTON COLUMBUS 58.36
2026 6 B-030 B PITTSBURG COLUMBUS 43.25
2027 7 A-017 A CINCINATTI DELWARE 78.56
2028 8 B-003 B CIRCLEVILLE PITTSBURG 35.45
8 rows selected
· Used to create an updateable view for data entry purposes
> /* CREATE AN UPDATEABLE VIEW */
> CREATE VIEW UPDATEABLE_V AS (
SELECT BAG_NUM, P.PASSENGER_ID , PASSENGER_FN, PASSENGER_LN, BAG_WEIGHT ,BAG_OVERWEIGHT , PAYMENT_METHOD, Handicapped
FROM TBL_PRJ_PASSENGER P, TBL_PRJ_BAGGAGE B
WHERE P.PASSENGER_ID = B.PASSENGER_ID)
CREATE VIEW succeeded.
> /* DISPLAY DATA IN THE UPDATEABLE VIEW */
> SELECT * FROM UPDATEABLE_V
BAG_NUM PASSENGER_ID PASSENGER_FN PASSENGER_LN BAG_WEIGHT BAG_OVERWEIGHT PAYMENT_METHOD HANDICAPPED
---------------------- -------------------- ------------------------- -------------------- ---------------------- ---------------------- -------------- -----------
1123 PASSGR-#1361 HENRY MOORE 14 0 CASH NO
1124 PASSGR-#1321 FRANCK YEBOUE 0 0 CASH NO
1125 PASSGR-#1371 LIZ ZEN 23 13 CREDIT YES
1126 PASSGR-#1331 YANNICK HARRIS 17 0 DEBIT NO
1127 PASSGR-#1301 HELEN LEWIS 3 0 CASH NO
7728 PASSGR-#1341 ROGER BRIAN 12 0 CREDIT NO
9929 PASSGR-#1381 NENE DORA 1 0 CASH NO
3630 PASSGR-#1351 DOUGLAS ROGER 10 650 CREDIT NO
8 rows selected
> /* UPDATE CUSTOMERS USING THE UPDATEABLE VIEW CREATED */
> /* SHOW THE DATA WE WANT TO UPDATE */
> SELECT BAG_NUM, PASSENGER_ID, BAG_WEIGHT, BAG_OVERWEIGHT, PAYMENT_METHOD
FROM TBL_PRJ_BAGGAGE
WHERE PASSENGER_ID = 'PASSGR-#1371'
OR PAYMENT_METHOD = 'CREDIT'
BAG_NUM PASSENGER_ID BAG_WEIGHT BAG_OVERWEIGHT PAYMENT_METHOD
---------------------- -------------------- ---------------------- ---------------------- --------------
1125 PASSGR-#1371 23 13 CREDIT
7728 PASSGR-#1341 12 0 CREDIT
3630 PASSGR-#1351 10 650 CREDIT
> /* UPDATE DATA USING THE UPDATEABLE VIEW */
> UPDATE UPDATEABLE_V
SET PAYMENT_METHOD = 'DEBIT'
WHERE PASSENGER_ID = 'PASSGR-#1371'
1 rows updated
> /*CHECK IF THE DATA WAS UPDATED */
> SELECT BAG_NUM, PASSENGER_ID, BAG_WEIGHT, BAG_OVERWEIGHT, PAYMENT_METHOD
FROM TBL_PRJ_BAGGAGE
WHERE PASSENGER_ID = 'PASSGR-#1371'
OR PAYMENT_METHOD = 'DEBIT'
BAG_NUM PASSENGER_ID BAG_WEIGHT BAG_OVERWEIGHT PAYMENT_METHOD
---------------------- -------------------- ---------------------- ---------------------- --------------
1125 PASSGR-#1371 23 13 DEBIT
1126 PASSGR-#1331 17 0 DEBIT
· Used to create roles that define access privileges
Access privileges and limits for the tables in this database will be set-up by the DBA after the database is implemented and tested.
· Used to create reports with headers / footers / column headings / page numbers
> /* CREATE reports with headers / footers / column headings / page numbers */
> CREATE VIEW MANAGER AS (
SELECT TICKET_NUM, V.TRV_CODE, S.SEAT_NUM, AISLE, TRV_DESTINATION, TRV_ORIGIN, TICKET_PRICE
FROM TBL_PRJ_TICKET T, TBL_PRJ_SEAT S, TBL_PRJ_TRAVEL V
WHERE S.SEAT_NUM = T.SEAT_NUM
AND V.TRV_CODE = T.TRV_CODE)
CREATE VIEW succeeded.
> /* DISPLAY THE MANAGER'S VIEW CREATED */
> SELECT * FROM MANAGER
TICKET_NUM TRV_CODE SEAT_NUM AISLE TRV_DESTINATION TRV_ORIGIN TICKET_PRICE
---------------------- ---------------------- -------- ----- -------------------- -------------------- ----------------------
2021 1 A-001 A DELWARE AKRON 45.23
2022 2 A-003 A COLUMBUS CLEVELAND 42.32
2023 3 B-001 A CLEVELAND CINCINATTI 14.93
2024 4 B-002 B AKRON CIRCLEVILLE 63.24
2025 5 C-008 C DAYTON COLUMBUS 58.36
2026 6 B-030 B PITTSBURG COLUMBUS 43.25
2027 7 A-017 A CINCINATTI DELWARE 78.56
2028 8 B-003 B CIRCLEVILLE PITTSBURG 35.45
8 rows selected
