[Solved] DBS301 Lab 11 (Transactional SQL)

This week reviews and extends the knowledge of Transactional SQL using the DBMS Oracle.Getting StartedYour submission will be a single text-based SQL file with appropriate header and commenting.TasksYou 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 lessthan 2000 (do NOT create this table from scratch, i.e. create and insert in onestatement).→ 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 lessthan 1500 (do NOT create this table from scratch). This table will have same structure astable L10Cities.→ You will have exactly 5 rows here.3. Now you will empty your RECYCLE BIN with one powerful command. Then removeyour table L10Towns, so that will remain in the recycle bin. Check that it is really thereand what time was removed. Hint: Show RecycleBin, Purge, Flashback4. Restore your table L10Towns from recycle bin and describe it. Check what is in yourrecycle bin now.5. Now remove table L10Towns so that does NOT remain in the recycle bin. Check that isreally NOT there and then try to restore it. Explain what happened?DBS301 – Database Design II and SQL using Oracle Lab 10 – Week 112 | P a g e6. Create simple view called CAN_CITY_VU, based on table L10Cities so that willcontain only columns Street_Address, Postal_Code, City and State_Province forlocations only in CANADA. Then display all data from this view.7. Modify your simple view so that will have following aliases instead of original columnnames: 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 andDEPARTMENTS, so that will contain only columns Department_Name, City andState_Province for locations in ITALY or CANADA. Include situations even when city doesNOT 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 columnnames: DName, City and Prov and also will include all cities outside United StatesInclude situations even when city does NOT have department established yet. Thendisplay all data from this view.10. Create a transaction, ensuring a new transaction is started, and include all the SQLstatements required to merge the Marketing and Sales departments into a singledepartment “Marketing and Sales”. Create a new department such that the history ofemployees departments remains intact. The Sales staff will change locations to theexisting Marketing department’s location. All staff from both previous departments willchange to the new department. Add appropriate save points where the transactioncould potentially be rolled back to (i.e. good checkpoints).Execute these statements, double check everything worked as intended, and then onceit 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 farin 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