Description

Lab 10 – Week 1 (Transactional SQL)
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

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

2 | P a g e