LAB10.SQL

1.  CREATE TABLE USJAP 
    AS SELECT * FROM LOCATIONS
    WHERE COUNTRY_ID IN ('US', ‘JP');

2.  CREATE TABLE PLACES
    AS SELECT * FROM LOCATIONS
    WHERE LOCATION_ID > 2200;

3.  PURGE RECYCLEBIN;
    DROP TABLE PLACES;
    SELECT ORIGINAL_NAME, DROPTIME FROM RECYCLEBIN;

4. FLASHBACK TABLE PLACES TO BEFORE DROP;

   SELECT CITY FROM PLACES
   WHERE COUNTRY_ID = ‘UK';
 
   SELECT * FROM RECYCLEBIN;

5.  DROP TABLE PLACES PURGE;
    SELECT * FROM RECYCLEBIN;
    FLASHBACK TABLE PLACES TO BEFORE DROP;

6.  CREATE VIEW US_CITY_VU
    AS SELECT LOCATION_ID, CITY, STATE_PROVINCE, COUNTRY_ID FROM USJAP
    WHERE COUNTRY_ID = 'US';

7. CREATE OR REPLACE VIEW US_CITY_VU(LOC#, CITY, PROV, CC)
   AS SELECT LOCATION_ID, CITY, STATE_PROVINCE, COUNTRY_ID FROM USJAP
   WHERE COUNTRY_ID IN('JP', 'US');

8. DELETE US_CITY_VU
   WHERE CITY = ‘HIROSHIMA';

   UPDATE US_CITY_VU
   SET CC = 'TP'
   WHERE CITY = ‘TOKYO';

   INSERT INTO US_CITY_VU VALUES (3500, 'OSAKA', NULL, ‘JP’);
   SAVEPOINT ADDED;

8. CREATE VIEW CANUK_DNAME_VU
   AS SELECT D.DEPARTMENT_NAME ,L.CITY, L.STATE_PROVINCE FROM LOCATIONS L JOIN DEPARTMENTS D
   USING(LOCATION_ID)
   WHERE (L.COUNTRY_ID = 'CA' OR L.COUNTRY_ID = 'UK') OR  D.DEPARTMENT_NAME = NULL;

9. CREATE OR REPLACE VIEW CANUK_DNAME_VU
   AS SELECT D.DEPARTMENT_NAME ,L.CITY, L.STATE_PROVINCE FROM LOCATIONS L FULL OUTER JOIN DEPARTMENTS D
   USING(LOCATION_ID)
   WHERE (L.COUNTRY_ID = 'CA' OR L.COUNTRY_ID = 'UK' OR L.COUNTRY_ID = 'BR') OR  D.DEPARTMENT_NAME = NULL;


10. SELECT VIEW_NAME, TEXT FROM USER_VIEWS;
   DROP VIEW CANUK_DNAME_VU;