SQL project in Fall semester (Sep-Dec) 2023
Thanks to professor Jim Myronic guidance
- Install the Oracle sample schemas
Confirmation the schemas have been successfully installed;
- Query the installed schemas
From SQL Developer or SQL Plus, the SQL DML query and query results (i.e., result set) that successfully demonstrate:
i. The joining of 5 tables (Note: add a row to one table that requires an outer join);
ii. A 4 level subquery (i.e., 3 inner queries feeding a result to the outer query);
iii. A SET query showing row and aggregate values;
iv. A correlated subquery;
v. Use of analytic functions: SUM(), RANK(), DENSE_RANK(), LEAD(), LAG() and CUME_DIST(), each involving PARTITION clauses.
Download the Oracle Database Sample Schemas here: https://github.com/oracle-samples/db-sample-schemas
Install it with some configuration and changing the address path to your own installing path. reference: https://www.youtube.com/watch?v=o7g5K-0EXb4
Here are the first few pictures after I success installed the mksample.sql, which include BI, HR, IX, OE, PM, SH schemas:
I found the mksample.sql does not include all the schemas. Then, except these schemas, I tried to install the CO and QS schemas individually.
For CO schema:
Then I checked all the schema users’ status:
Then connected my sql developer with all these schemas:
I found that there are just 5 tables in the CO schema, and these 5 tables connect by primary and foreign keys. These keys are product_id, order_id, customer_id, store_id.
