SQL> -- ORACLE SQL DML: SCHEMA C##ART
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> SET PAGESIZE 30;
SQL> SET HEADING ON;
SQL> 
SQL> -- 20 Queries
SQL> -- Q1: Select all columns and all rows from one table
SQL> SELECT * FROM ROLE;

   ROLE_ID ROLENAME        
---------- ----------------
       100 SYSTEM          
       200 SUPPLIER        
       300 STAFF           
       400 CUSTOMER        

SQL> 
SQL> -- Q2: Select five columns and all rows from one table
SQL> SELECT CONTACT_ID, FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL FROM CONTACT;

CONTACT_ID FIRST_NAME       LAST_NAME        PHONE_NO     EMAIL                                                           
---------- ---------------- ---------------- ------------ ----------------------------------------------------------------
         1 ART              SYSTEM           000-1234     arts@art.com                                                    
         2 Alice            Johnson          650-555-9012 alice.johnson@art.com                                           
         3 Karen            Park             206-555-0110 karen.park@art.com                                              
         4 Jeese            Taylor           713-555-0106 grace.taylor@art.com                                            
         5 James            Lee              206-555-0109 james.lee@art.com                                               
         6 Stephen          Alec             424-546-9312 salec@eizel.com                                                 
         7 Henry            Martinez         602-555-0107 henry@artsupply.com                                             
         8 Carol            White            212-555-0102 carol.white@mail.com                                            
         9 Bob              Smith            212-555-0101 bob.smith@mail.com                                              
        10 David            Brown            312-555-0103 david.brown@mail.com                                            
        11 Emma             Davis            312-555-0104 emma.davis@mail.com                                             
        12 Frank            Wilson           713-555-0105 frank.wilson@mail.com                                           

12 rows selected. 

SQL> 
SQL> -- Q3: Select all columns from all rows from one view
SQL> SELECT * FROM VIEW_AVAILABLE_INVENTORY;
no rows selected
SQL> 
SQL> -- Q4: Using a join on 2 tables, select all columns and all rows from the tables without the use of a Cartesian product
SQL> SELECT * FROM CONTACT ct
  2  JOIN ADDRESS ad ON ad.FK_CONTACT_ID=ct.CONTACT_ID;

CONTACT_ID FIRST_NAME       LAST_NAME        PHONE_NO     EMAIL                                                            MODIFIED_BY CREATED_DT                      MODIFIED_DT                     ZIP   FK_CONTACT_ID STREET                          
---------- ---------------- ---------------- ------------ ---------------------------------------------------------------- ----------- ------------------------------- ------------------------------- ----- ------------- --------------------------------
         1 ART              SYSTEM           000-1234     arts@art.com                                                               1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM 94027             1 789 Oak St, suite 10            
         2 Alice            Johnson          650-555-9012 alice.johnson@art.com                                                      1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM 94027             2 789 Oak St, suite 15            
         3 Karen            Park             206-555-0110 karen.park@art.com                                                         1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM 94027             3 789 Oak St, suite 1             
         4 Jeese            Taylor           713-555-0106 grace.taylor@art.com                                                       1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM 94027             4 789 Oak St, suite 5             
         5 James            Lee              206-555-0109 james.lee@art.com                                                          1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM 94027             5 789 Oak St, suite 15            
         6 Stephen          Alec             424-546-9312 salec@eizel.com                                                            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM 90210             6 890 Venue Rd                    
         7 Henry            Martinez         602-555-0107 henry@artsupply.com                                                        1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM 90210             7 200 Sunset Blvd                 
         8 Carol            White            212-555-0102 carol.white@mail.com                                                       1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM 10001             8 100 Broadway                    
         9 Bob              Smith            212-555-0101 bob.smith@mail.com                                                         1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM 60601             9 300 Michigan Ave                
        10 David            Brown            312-555-0103 david.brown@mail.com                                                       1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM 77001            10 400 Main St                     
        11 Emma             Davis            312-555-0104 emma.davis@mail.com                                                        1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM 85001            11 500 Central Ave                 
        12 Frank            Wilson           713-555-0105 frank.wilson@mail.com                                                      1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM 98101            12 600 Pike St                     

12 rows selected. 

SQL> 
SQL> -- Q5: 5: Select and order data retrieved from one table
SQL> SELECT * FROM ZIP ORDER BY 1;

ZIP   CITY             ST
----- ---------------- --
02101 Boston           MA
10001 New York         NY
33101 Miami            FL
60601 Chicago          IL
77001 Houston          TX
85001 Phoenix          AZ
90210 Beverly Hills    CA
90211 Los Angeles      CA
94027 Atherton         CA
98101 Seattle          WA

10 rows selected. 

SQL> 
SQL> -- Q6: Using a join on 3 tables, select 5 columns from the 3 tables. Use syntax that would limit the output to 10 rows
SQL> SELECT iv.SKU, iv.PRODUCT_NAME,iv.BASE_PRICE,iv.LEAD_TIME_DAYS, iv.QUANTITY_AVAILABLE,
  2   au.AUTHORIZATION_STATUS, au.EXPIRATION_DATE, au.AUTHORIZATION_DATE, c.*, au.FK_DOCUMENT_ID
  3  FROM INVENTORY iv
  4  JOIN "AUTHORIZATION" au ON au.AUTHORIZATION_ID = iv.AUTHORIZATION_ID
  5  join CONTACT c ON c.CONTACT_ID= iv.FK_CONTACT_ID
  6  FETCH FIRST 10 ROWS ONLY;

SKU                                                                                                  PRODUCT_NAME                                                                                         BASE_PRICE LEAD_TIME_DAYS QUANTITY_AVAILABLE AUTHORIZATION_ST EXPIRATION_DATE                 AUTHORIZATION_DATE              CONTACT_ID FIRST_NAME       LAST_NAME        PHONE_NO     EMAIL                                                            MODIFIED_BY CREATED_DT                      MODIFIED_DT                     FK_DOCUMENT_ID
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------- -------------- ------------------ ---------------- ------------------------------- ------------------------------- ---------- ---------------- ---------------- ------------ ---------------------------------------------------------------- ----------- ------------------------------- ------------------------------- --------------
ART-S-001                                                                                            Crimson Horizon                                                                                            1200              7                  5 approved         28-MAR-27 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          6 Stephen          Alec             424-546-9312 salec@eizel.com                                                            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM              2
ART-S-002                                                                                            Misty Valley                                                                                                700              5                  5 approved         28-MAR-27 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          6 Stephen          Alec             424-546-9312 salec@eizel.com                                                            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM              3
ART-S-003                                                                                            Twisted Form                                                                                               2200             14                  5 pending          28-MAR-27 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          6 Stephen          Alec             424-546-9312 salec@eizel.com                                                            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM              4
ART-H-001                                                                                            Urban Solitude                                                                                             1500              3                 10 approved         28-MAR-27 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          6 Stephen          Alec             424-546-9312 salec@eizel.com                                                            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM              7
ART-H-004                                                                                            Neon Dreams                                                                                                1100              5                  6 approved         28-MAR-27 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          6 Stephen          Alec             424-546-9312 salec@eizel.com                                                            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM              7
ART-H-005                                                                                            Woven Stories                                                                                               850             14                  5 approved         28-MAR-27 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          6 Stephen          Alec             424-546-9312 salec@eizel.com                                                            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM              8
ART-S-004                                                                                            Blue Symmetry                                                                                               950              7                  5 approved         28-MAR-27 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          7 Henry            Martinez         602-555-0107 henry@artsupply.com                                                        1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM              2
ART-S-005                                                                                            Urban Collage                                                                                               550              5                  6 approved         28-MAR-27 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          7 Henry            Martinez         602-555-0107 henry@artsupply.com                                                        1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM              3
ART-H-002                                                                                            Earth Vessel                                                                                                420             10                  8 approved         28-MAR-27 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          7 Henry            Martinez         602-555-0107 henry@artsupply.com                                                        1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM              8
ART-H-003                                                                                            Quiet Reflection                                                                                            500              7                  4 pending          24-SEP-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          7 Henry            Martinez         602-555-0107 henry@artsupply.com                                                        1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM              7

10 rows selected. 

SQL> 
SQL> --Q7: Select distinct rows using joins on 3 tables
SQL> SELECT DISTINCT iv.SKU, iv.PRODUCT_NAME,iv.BASE_PRICE,iv.LEAD_TIME_DAYS, iv.QUANTITY_AVAILABLE,
  2   au.AUTHORIZATION_STATUS, au.EXPIRATION_DATE, au.AUTHORIZATION_DATE, au.FK_CONTACT_ID, au.FK_DOCUMENT_ID,
  3   oi.ORDER_ITEM_ID, oi.QUANTITY, oi.UNIT_PRICE, oi.TRANSACTION_ID, oi.LINE_TOTAL
  4  FROM INVENTORY iv
  5  JOIN "AUTHORIZATION" au ON au.AUTHORIZATION_ID = iv.AUTHORIZATION_ID
  6  JOIN ORDER_ITEMS oi ON oi.FK_INVENTORY_ID = iv.INVENTORY_ID;
no rows selected
SQL> 
SQL> -- Q8: Use GROUP BY and HAVING in a select statement using one or more tables
SQL> SELECT iv.SKU
  2  FROM INVENTORY iv
  3  JOIN "AUTHORIZATION" au ON au.AUTHORIZATION_ID = iv.AUTHORIZATION_ID
  4  JOIN ORDER_ITEMS oi ON oi.FK_INVENTORY_ID = iv.INVENTORY_ID
  5  GROUP BY iv.SKU, iv.QUANTITY_AVAILABLE HAVING iv.QUANTITY_AVAILABLE > 0;
no rows selected
SQL> 
SQL> -- Q9: Use IN clause to select data from one or more tables
SQL> SELECT * FROM PROFILE pf JOIN USERROLE ur ON pf.PROFILE_ID = ur.FK_PROFILE_ID  WHERE ur.FK_ROLE_ID  IN (100, 200, 300);

PROFILE_ID FK_CONTACT_ID ACTIV MODIFIED_BY CREATED_DT                      MODIFIED_DT                     FK_PROFILE_ID FK_ROLE_ID PASSWORDHASH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
---------- ------------- ----- ----------- ------------------------------- ------------------------------- ------------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1             1 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             1        100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         2             2 true            2 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             2        300                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         3             3 true            3 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             3        300                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         4             4 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             4        300                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         5             5 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             5        300                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         6             6 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             6        200                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         7             7 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             7        200                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

7 rows selected. 

SQL> 
SQL> -- Q10: Select length of one column from one table (use LENGTH function)
SQL> SELECT LENGTH(ROLENAME) FROM ROLE;

LENGTH(ROLENAME)
----------------
               6
               8
               5
               8

SQL> 
SQL> -- Q11: Delete one record from one table. ROLLBACK afterwards so that the data will not be physically removed.
SQL> SAVEPOINT sp1;

Savepoint created.

SQL> 
SQL> SELECT * FROM PROFILE pf JOIN USERROLE ur ON pf.PROFILE_ID = ur.FK_PROFILE_ID  WHERE ur.FK_ROLE_ID  IN (100, 200, 300);

PROFILE_ID FK_CONTACT_ID ACTIV MODIFIED_BY CREATED_DT                      MODIFIED_DT                     FK_PROFILE_ID FK_ROLE_ID PASSWORDHASH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
---------- ------------- ----- ----------- ------------------------------- ------------------------------- ------------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1             1 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             1        100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         2             2 true            2 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             2        300                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         3             3 true            3 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             3        300                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         4             4 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             4        300                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         5             5 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             5        300                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         6             6 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             6        200                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         7             7 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             7        200                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

7 rows selected. 

SQL> DELETE USERROLE WHERE FK_PROFILE_ID=3 AND FK_ROLE_ID=300;

1 row deleted.

SQL> SELECT * FROM PROFILE pf JOIN USERROLE ur ON pf.PROFILE_ID = ur.FK_PROFILE_ID  WHERE ur.FK_ROLE_ID  IN (100, 200, 300);

PROFILE_ID FK_CONTACT_ID ACTIV MODIFIED_BY CREATED_DT                      MODIFIED_DT                     FK_PROFILE_ID FK_ROLE_ID PASSWORDHASH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
---------- ------------- ----- ----------- ------------------------------- ------------------------------- ------------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1             1 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             1        100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         2             2 true            2 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             2        300                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         4             4 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             4        300                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         5             5 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             5        300                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         6             6 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             6        200                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         7             7 true            1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM             7        200                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

6 rows selected. 

SQL> 
SQL> ROLLBACK TO SAVEPOINT sp1;

Rollback complete.

SQL> 
SQL> -- Q12: Update one record from one table. demonstrate table contents before and after the UPDATE.
SQL> SELECT * FROM PROFILE WHERE PROFILE_ID=3;

PROFILE_ID FK_CONTACT_ID ACTIV MODIFIED_BY CREATED_DT                      MODIFIED_DT                    
---------- ------------- ----- ----------- ------------------------------- -------------------------------
         3             3 true            3 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM

SQL> UPDATE PROFILE SET ACTIVE = FALSE WHERE PROFILE_ID=3;

1 row updated.

SQL> SELECT * FROM PROFILE WHERE PROFILE_ID=3;

PROFILE_ID FK_CONTACT_ID ACTIV MODIFIED_BY CREATED_DT                      MODIFIED_DT                    
---------- ------------- ----- ----------- ------------------------------- -------------------------------
         3             3 false           3 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM

SQL> 
SQL> ROLLBACK TO SAVEPOINT sp1;

Rollback complete.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> -- Q13: Plans show
SQL> SELECT * FROM PLAN pl
  2  LEFT OUTER JOIN DOCUMENT doc ON pl.FK_DOCUMENT_ID = doc.DOCUMENT_ID;

   PLAN_ID DESCRIPTION                      FK_DOCUMENT_ID   DURATION      PRICE MODIFIED_BY CREATED_DT                      MODIFIED_DT                     DOCUMENT_ID DOCUMENT_URL                                                                                                                                                                                                                                                  
---------- -------------------------------- -------------- ---------- ---------- ----------- ------------------------------- ------------------------------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 Spring Gala Preview                           9          4         75           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM           9 https://art.com/docs/plan-spring-gala-2025.pdf                                                                                                                                                                                                                
         2 Spring Gala Standard                          9          8        120           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM           9 https://art.com/docs/plan-spring-gala-2025.pdf                                                                                                                                                                                                                
         3 Spring Gala VIP                               9         12        250           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM           9 https://art.com/docs/plan-spring-gala-2025.pdf                                                                                                                                                                                                                
         4 Summer Exhibit Basic                         10          4         50           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          10 https://art.com/docs/plan-summer-exhibit-2025.pdf                                                                                                                                                                                                             
         5 Summer Exhibit Plus                          10          8        100           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          10 https://art.com/docs/plan-summer-exhibit-2025.pdf                                                                                                                                                                                                             
         6 Summer Exhibit Premium                       10         16        200           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          10 https://art.com/docs/plan-summer-exhibit-2025.pdf                                                                                                                                                                                                             
         7 Fall Exhibition Access                       11          4         60           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          11 https://art.com/docs/plan-fall-exhibition-2025.pdf                                                                                                                                                                                                            
         8 Fall Exhibition Full Day                     11          8        110           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          11 https://art.com/docs/plan-fall-exhibition-2025.pdf                                                                                                                                                                                                            
         9 Fall Exhibition Weekend                      11         24        180           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          11 https://art.com/docs/plan-fall-exhibition-2025.pdf                                                                                                                                                                                                            
        10 Annual Patron Pass                           11        200        500           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM          11 https://art.com/docs/plan-fall-exhibition-2025.pdf                                                                                                                                                                                                            

10 rows selected. 

SQL> 
SQL> -- Q14: show authorized suppliers whose expiration date is greater than 30 days from now.
SQL> SELECT * FROM VIEW_SUPPLIER vsu
  2  LEFT OUTER JOIN AUTHORIZATION aut ON aut.FK_CONTACT_ID = vsu.FK_CONTACT_ID
  3  WHERE aut.AUTHORIZATION_STATUS='approved' AND aut.EXPIRATION_DATE > SYSDATE+30;

FK_PROFILE_ID FK_CONTACT_ID ROLENAME         ACTIV FIRST_NAME       LAST_NAME        AUTHORIZATION_ID FK_CONTACT_ID AUTHORIZATION_ST AUTHORIZATION_DATE              EXPIRATION_DATE                 FK_DOCUMENT_ID MODIFIED_BY CREATED_DT                      MODIFIED_DT                    
------------- ------------- ---------------- ----- ---------------- ---------------- ---------------- ------------- ---------------- ------------------------------- ------------------------------- -------------- ----------- ------------------------------- -------------------------------
            6             6 SUPPLIER         true  Stephen          Alec                            1             6 approved         28-MAR-26 02.36.34.000000000 AM 28-MAR-27 02.36.34.000000000 AM              2           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM
            6             6 SUPPLIER         true  Stephen          Alec                            2             6 approved         28-MAR-26 02.36.34.000000000 AM 28-MAR-27 02.36.34.000000000 AM              3           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM
            7             7 SUPPLIER         true  Henry            Martinez                        5             7 approved         28-MAR-26 02.36.34.000000000 AM 28-MAR-27 02.36.34.000000000 AM              6           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM
            7             7 SUPPLIER         true  Henry            Martinez                        6             7 approved         28-MAR-26 02.36.34.000000000 AM 28-MAR-27 02.36.34.000000000 AM              7           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM
            7             7 SUPPLIER         true  Henry            Martinez                        7             7 approved         28-MAR-26 02.36.34.000000000 AM 28-MAR-27 02.36.34.000000000 AM              8           1 28-MAR-26 02.36.34.000000000 AM 28-MAR-26 02.36.34.000000000 AM

SQL> 
SQL> -- Q15:
SQL> SELECT r.ROLENAME, ct.FIRST_NAME, ct.LAST_NAME, pl.DESCRIPTION FROM SUBSCRIPTION srp
  2  LEFT OUTER JOIN CONTACT ct ON srp.FK_CONTACT_ID = ct.CONTACT_ID
  3  LEFT OUTER JOIN PROFILE pf ON pf.FK_CONTACT_ID = ct.CONTACT_ID
  4  LEFT OUTER JOIN USERROLE ur ON ur.FK_PROFILE_ID = pf.PROFILE_ID
  5  LEFT OUTER JOIN ROLE r ON r.ROLE_ID = ur.FK_ROLE_ID
  6  LEFT OUTER JOIN PLAN pl ON srp.FK_PLAN_ID = pl.PLAN_ID;

ROLENAME         FIRST_NAME       LAST_NAME        DESCRIPTION                     
---------------- ---------------- ---------------- --------------------------------
CUSTOMER         Bob              Smith            Spring Gala Preview             
CUSTOMER         Bob              Smith            Summer Exhibit Plus             
CUSTOMER         Bob              Smith            Fall Exhibition Weekend         
CUSTOMER         David            Brown            Spring Gala Standard            
CUSTOMER         David            Brown            Summer Exhibit Premium          
CUSTOMER         David            Brown            Annual Patron Pass              
CUSTOMER         Emma             Davis            Spring Gala VIP                 
CUSTOMER         Emma             Davis            Fall Exhibition Access          
CUSTOMER         Frank            Wilson           Summer Exhibit Basic            
CUSTOMER         Frank            Wilson           Fall Exhibition Full Day        

10 rows selected. 

SQL> 
SQL> -- Q16:
SQL> SELECT ct.FIRST_NAME, ct.LAST_NAME, adr.STREET, z.CITY, z.STATE, z.ZIP
  2  FROM ADDRESS adr
  3  LEFT OUTER JOIN CONTACT ct ON adr.FK_CONTACT_ID = ct.CONTACT_ID
  4  LEFT OUTER JOIN ZIP z ON adr.ZIP = z.ZIP;

FIRST_NAME       LAST_NAME        STREET                           CITY             ST ZIP  
---------------- ---------------- -------------------------------- ---------------- -- -----
ART              SYSTEM           789 Oak St, suite 10             Atherton         CA 94027
Alice            Johnson          789 Oak St, suite 15             Atherton         CA 94027
Karen            Park             789 Oak St, suite 1              Atherton         CA 94027
Jeese            Taylor           789 Oak St, suite 5              Atherton         CA 94027
James            Lee              789 Oak St, suite 15             Atherton         CA 94027
Stephen          Alec             890 Venue Rd                     Beverly Hills    CA 90210
Henry            Martinez         200 Sunset Blvd                  Beverly Hills    CA 90210
Carol            White            100 Broadway                     New York         NY 10001
Bob              Smith            300 Michigan Ave                 Chicago          IL 60601
David            Brown            400 Main St                      Houston          TX 77001
Emma             Davis            500 Central Ave                  Phoenix          AZ 85001
Frank            Wilson           600 Pike St                      Seattle          WA 98101

12 rows selected. 

SQL> 
SQL> -- Q17:
SQL> SELECT COALESCE(vws.ROLENAME, vwsp.ROLENAME, vwc.ROLENAME, vwsys.ROLENAME) AS ROLENAME,
  2         COALESCE(vws.ACTIVE, vwsp.ACTIVE, vwc.ACTIVE, vwsys.ACTIVE) AS ACTIVE,
  3         ct.FIRST_NAME, ct.LAST_NAME, adr.STREET, z.CITY, z.STATE, z.ZIP
  4  FROM ADDRESS adr
  5  LEFT OUTER JOIN CONTACT ct ON adr.FK_CONTACT_ID = ct.CONTACT_ID
  6  LEFT OUTER JOIN ZIP z ON adr.ZIP = z.ZIP
  7  LEFT OUTER JOIN VIEW_STAFF vws ON vws.FK_CONTACT_ID = ct.CONTACT_ID
  8  LEFT OUTER JOIN VIEW_SUPPLIER vwsp ON vwsp.FK_CONTACT_ID = ct.CONTACT_ID
  9  LEFT OUTER JOIN VIEW_CUSTOMER vwc ON vwc.FK_CONTACT_ID = ct.CONTACT_ID
 10  LEFT OUTER JOIN VIEW_SYSTEMROLE vwsys ON vwsys.FK_CONTACT_ID = ct.CONTACT_ID;

ROLENAME         ACTIV FIRST_NAME       LAST_NAME        STREET                           CITY             ST ZIP  
---------------- ----- ---------------- ---------------- -------------------------------- ---------------- -- -----
CUSTOMER         false Carol            White            100 Broadway                     New York         NY 10001
CUSTOMER         true  Bob              Smith            300 Michigan Ave                 Chicago          IL 60601
CUSTOMER         true  David            Brown            400 Main St                      Houston          TX 77001
CUSTOMER         true  Emma             Davis            500 Central Ave                  Phoenix          AZ 85001
CUSTOMER         true  Frank            Wilson           600 Pike St                      Seattle          WA 98101
SUPPLIER         true  Stephen          Alec             890 Venue Rd                     Beverly Hills    CA 90210
SUPPLIER         true  Henry            Martinez         200 Sunset Blvd                  Beverly Hills    CA 90210
SYSTEM           true  ART              SYSTEM           789 Oak St, suite 10             Atherton         CA 94027
STAFF            true  Alice            Johnson          789 Oak St, suite 15             Atherton         CA 94027
STAFF            true  Jeese            Taylor           789 Oak St, suite 5              Atherton         CA 94027
STAFF            true  James            Lee              789 Oak St, suite 15             Atherton         CA 94027
STAFF            true  Karen            Park             789 Oak St, suite 1              Atherton         CA 94027

12 rows selected. 

SQL> 
SQL> -- Q18: Verify rows in tables
SQL> SELECT 'CONTACT'        AS TABLE_NAME, COUNT(*) AS ROW_COUNT FROM CONTACT        UNION ALL
  2  SELECT 'ZIP',                          COUNT(*)              FROM ZIP            UNION ALL
  3  SELECT 'ADDRESS',                      COUNT(*)              FROM ADDRESS        UNION ALL
  4  SELECT 'ROLE',                         COUNT(*)              FROM ROLE           UNION ALL
  5  SELECT 'PROFILE',                      COUNT(*)              FROM PROFILE        UNION ALL
  6  SELECT 'USERROLE',                     COUNT(*)              FROM USERROLE       UNION ALL
  7  SELECT 'STAFF',                        COUNT(*)              FROM STAFF          UNION ALL
  8  SELECT 'DOCUMENT',                     COUNT(*)              FROM DOCUMENT       UNION ALL
  9  SELECT 'AUTHORIZATION',                COUNT(*)              FROM AUTHORIZATION  UNION ALL
 10  SELECT 'PLAN',                         COUNT(*)              FROM PLAN           UNION ALL
 11  SELECT 'INVENTORY',                    COUNT(*)              FROM INVENTORY      UNION ALL
 12  SELECT 'EXHIBIT',                      COUNT(*)              FROM EXHIBIT        UNION ALL
 13  SELECT 'ORDER_ITEMS',                  COUNT(*)              FROM ORDER_ITEMS    UNION ALL
 14  SELECT 'INVOICE',                      COUNT(*)              FROM INVOICE        UNION ALL
 15  SELECT 'SUBSCRIPTION',                 COUNT(*)              FROM SUBSCRIPTION   UNION ALL
 16  SELECT 'TASK_HISTORY',                 COUNT(*)              FROM TASK_HISTORY
 17  ORDER BY TABLE_NAME;

TABLE_NAME     ROW_COUNT
------------- ----------
ADDRESS               12
AUTHORIZATION          9
CONTACT               12
DOCUMENT              10
EXHIBIT               10
INVENTORY             10
INVOICE               10
ORDER_ITEMS            0
PLAN                  10
PROFILE               12
ROLE                   4
STAFF                  4
SUBSCRIPTION          10
TASK_HISTORY           0
USERROLE              12
ZIP                   10

16 rows selected. 

SQL> 
SQL> -- Q19: rough check uniqueness: across tables columns
SQL> SELECT RPAD(idx.TABLE_NAME,15,' '), COUNT( col.column_name ), COUNT( col.TABLE_NAME )
  2  FROM user_indexes idx
  3  JOIN user_ind_columns col ON ( col.index_name = idx.index_name )
  4  WHERE idx.uniqueness = 'UNIQUE'
  5  GROUP BY idx.TABLE_NAME;

RPAD(IDX.TABLE_ COUNT(COL.COLUMN_NAME) COUNT(COL.TABLE_NAME)
--------------- ---------------------- ---------------------
CONTACT                              1                     1
ZIP                                  1                     1
ADDRESS                              2                     2
ROLE                                 1                     1
PROFILE                              1                     1
USERROLE                             2                     2
STAFF                                1                     1
DOCUMENT                             1                     1
PLAN                                 1                     1
SUBSCRIPTION                         2                     2
INVOICE                              1                     1
AUTHORIZATION                        1                     1
INVENTORY                            3                     3
EXHIBIT                              1                     1
ORDER_ITEMS                          1                     1
TASK_HISTORY                         1                     1

16 rows selected. 

SQL> 
SQL> -- Q20: Likely weak entity tables;
SQL> SELECT pk.table_name
  2         -- LISTAGG(pk.column_name, ', ') WITHIN GROUP (ORDER BY pk.position) AS primary_key_columns,
  3         -- LISTAGG(fk.column_name, ', ') WITHIN GROUP (ORDER BY fk.position) AS foreign_key_columns
  4  FROM all_cons_columns pk
  5  JOIN all_constraints pkc
  6    ON pk.constraint_name = pkc.constraint_name
  7   AND pk.owner = pkc.owner
  8  JOIN all_cons_columns fk
  9    ON pk.table_name = fk.table_name
 10   AND pk.owner = fk.owner
 11  JOIN all_constraints fkc
 12    ON fk.constraint_name = fkc.constraint_name
 13   AND fk.owner = fkc.owner
 14  WHERE pkc.constraint_type = 'P'       -- Primary key
 15    AND fkc.constraint_type = 'R'       -- Foreign key
 16    AND pk.column_name = fk.column_name -- PK column is also FK column
 17    AND pk.owner = USER
 18  GROUP BY pk.table_name
 19  ORDER BY pk.table_name;

TABLE_NAME                                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------
ADDRESS
STAFF
SUBSCRIPTION
USERROLE

SQL> -- CREATE SCHEMA
SQL> 
SQL> /
