task from sql base
Budget: $10 – $30 USD
Task 1
Using the ORACLE SQL Developer tool, load data representing retail sales from an online store into the TEMP table.
Data: Link
Download Version: Link
Task 2
Normalize the TEMP table by creating tables that represent orders, products, product categories, customers, and location (GEOGRAPHY column). The tables should have non-intelligent primary keys other than identifiers in the TEMP table. When loading data, skip the quantity field. Remember to create appropriate integrity constraints.
Task 3
For each table from Task 2, create sequences that will generate primary key values for each table. Then, create triggers that will fetch the next values from the sequences and set the values of the primary key fields each time an INSERT command is executed.
Task 4
Write a package consisting of the following subprograms:
(a) Procedure(s) that will appropriately transfer data from the TEMP table to the tables created in Task 2.
Note: If we repeatedly call the procedure(s) from the package, the tables from Task 2 should contain respectively one location with the given name, one state with the given name, one customer with the given email address, one order with the specified ORDERID, and one product with the given name. The MERGE command can be used for data integration.
(b) Function that, given the state name and the order year (input parameters of the function), will calculate the total profit value from sales. The function should be implemented based on the tables created in Task 2.
Task 5
Write a query that will return the states with the highest sales profit value for a given year. The query should return the state name and the sales profit value. In the query, use the package function from Task 4.
Using the ORACLE SQL Developer tool, load data representing retail sales from an online store into the TEMP table.
Data: Link
Download Version: Link
Task 2
Normalize the TEMP table by creating tables that represent orders, products, product categories, customers, and location (GEOGRAPHY column). The tables should have non-intelligent primary keys other than identifiers in the TEMP table. When loading data, skip the quantity field. Remember to create appropriate integrity constraints.
Task 3
For each table from Task 2, create sequences that will generate primary key values for each table. Then, create triggers that will fetch the next values from the sequences and set the values of the primary key fields each time an INSERT command is executed.
Task 4
Write a package consisting of the following subprograms:
(a) Procedure(s) that will appropriately transfer data from the TEMP table to the tables created in Task 2.
Note: If we repeatedly call the procedure(s) from the package, the tables from Task 2 should contain respectively one location with the given name, one state with the given name, one customer with the given email address, one order with the specified ORDERID, and one product with the given name. The MERGE command can be used for data integration.
(b) Function that, given the state name and the order year (input parameters of the function), will calculate the total profit value from sales. The function should be implemented based on the tables created in Task 2.
Task 5
Write a query that will return the states with the highest sales profit value for a given year. The query should return the state name and the sales profit value. In the query, use the package function from Task 4.