Description
This week reviews and extends the knowledge of Transactional SQL using the DBMS Oracle.
Getting Started
Your submission will be a single text-based SQL file with appropriate header and commenting.
Tasks
You will create 2 tables firstly, then remove / restore these tables and also to add / modify
/remove certain database objects like views in this lab.
1. Create table L10Cities from table LOCATIONS, but only for location numbers less
than 2000 (do NOT create this table from scratch, i.e. create and insert in one
statement).
→ You will have exactly 10 rows here.
→ When you describe L10Cities, the output is shown below:
Name Null? Type
———————————— ——– —————
LOCATION_ID NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
CITY NOT NULL VARCHAR2(30)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_ID CHAR(2)
2. Create table L10Towns from table LOCATIONS, but only for location numbers less
than 1500 (do NOT create this table from scratch). This table will have same structure as
table L10Cities.
→ You will have exactly 5 rows here.
3. Now you will empty your RECYCLE BIN with one powerful command. Then remove
your table L10Towns, so that will remain in the recycle bin. Check that it is really there
and what time was removed. Hint: Show RecycleBin, Purge, Flashback
4. Restore your table L10Towns from recycle bin and describe it. Check what is in your
recycle bin now.
5. Now remove table L10Towns so that does NOT remain in the recycle bin. Check that is
really NOT there and then try to restore it. Explain what happened?
DBS301 – Database Design II and SQL using Oracle Lab 10 – Week 11
2 | P a g e
6. Create simple view called CAN_CITY_VU, based on table L10Cities so that will
contain only columns Street_Address, Postal_Code, City and State_Province for
locations only in CANADA. Then display all data from this view.
7. Modify your simple view so that will have following aliases instead of original column
names: Str_Adr, P_Code, City and Prov and also will include cities from ITALY as well.
Then display all data from this view.
8. Create complex view called vwCity_DName_VU, based on tables LOCATIONS and
DEPARTMENTS, so that will contain only columns Department_Name, City and
State_Province for locations in ITALY or CANADA. Include situations even when city does
NOT have department established yet. Then display all data from this view.
9. Modify your complex view so that will have following aliases instead of original column
names: DName, City and Prov and also will include all cities outside United States
Include situations even when city does NOT have department established yet. Then
display all data from this view.
10. Create a transaction, ensuring a new transaction is started, and include all the SQL
statements required to merge the Marketing and Sales departments into a single
department “Marketing and Sales”. Create a new department such that the history of
employees departments remains intact. The Sales staff will change locations to the
existing Marketing department’s location. All staff from both previous departments will
change to the new department. Add appropriate save points where the transaction
could potentially be rolled back to (i.e. good checkpoints).
Execute these statements, double check everything worked as intended, and then once
it works through a single execution, commit it. If errors occur or the data is incorrect,
you can rollback and rerun after the errors have been corrected in the SQL code.
11. Check in the Data Dictionary what Views (their names and definitions) are created so far
in your account. Then drop your vwCity_DName_VU and check Data Dictionary again.
What is different?
resource: https://docs.oracle.com/cd/A97630_01/server.920/a96524/c05dicti.htm