rdbms assignment
DESCRIPTION
SQL queriesTRANSCRIPT
![Page 1: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/1.jpg)
RDBMS ASSIGNMENT
By,Ritam GhoshA14025
![Page 2: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/2.jpg)
Listing 1.1: Creating dwid user id
![Page 3: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/3.jpg)
Listing 1.2: Creating dw and source databases
![Page 4: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/4.jpg)
Listing 1.3: Creating data warehouse tables
![Page 5: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/5.jpg)
Listing 2.1: Applying SCD1 to the customer names in customer_dim
![Page 6: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/6.jpg)
Listing 2.2: Creating and loading the customer_stg table
![Page 7: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/7.jpg)
Listing 2.3: Applying SCD2 to product_name and product_category in the product_dim table
![Page 8: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/8.jpg)
Listing 2.4: Creating the product_stg table
![Page 9: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/9.jpg)
Listing 2.5: Loading products to its staging table
![Page 10: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/10.jpg)
Listing 3.2: Querying across all dimensions
![Page 11: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/11.jpg)
Listing 3.3: Querying across the date, product, and order
![Page 12: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/12.jpg)
Listing 3.5: Querying across the date and order
![Page 13: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/13.jpg)
Listing 4.2: Daily Aggregation
![Page 14: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/14.jpg)
Listing 4.3: Annual aggregation
![Page 15: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/15.jpg)
Listing 4.5: Specific query (quarterly sales in Mechanicsburg)
![Page 16: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/16.jpg)
Listing 4.6: Inside-out - Monthly Product Performer
![Page 17: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/17.jpg)
Listing 4.7: Inside-out (loyal customer)
![Page 18: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/18.jpg)
Listing 5.3: Data for testing Push mode
![Page 19: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/19.jpg)
Listing 6.1: Stored procedure to pre-populate the date dimension
![Page 20: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/20.jpg)
Listing 6.2: Daily date population
![Page 21: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/21.jpg)
Listing 6.3: Loading dates from the source
![Page 22: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/22.jpg)
Listing 8.1: Daily dw regular population
![Page 23: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/23.jpg)
Listing 8.2: Adding sales orders
![Page 24: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/24.jpg)
Listing 10.2: Adding the order quantity column
![Page 25: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/25.jpg)
Listing 10.3: Revised daily DW regular population
![Page 26: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/26.jpg)
Listing 10.5: Adding nine sales orders with order quantities
![Page 27: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/27.jpg)
Listing 11.2: Populating the promotion indicator
![Page 28: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/28.jpg)
Listing 12.2: The revised date pre-population script
![Page 29: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/29.jpg)
Listing 12.3: PA customers
![Page 30: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/30.jpg)
Listing 12.4: Non-PA customers
![Page 31: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/31.jpg)
Listing 12.6: Adding two customers
![Page 32: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/32.jpg)
Listing 13.1: Adding the request_delivery_date_sk column
![Page 33: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/33.jpg)
Listing 13.2: The revised daily DW regular population
![Page 34: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/34.jpg)
Listing 13.3: Adding the request_delivery_date column to the sales_order table
![Page 35: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/35.jpg)
Listing 13.4: Adding three sales orders with request delivery dates
![Page 36: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/36.jpg)
Listing 13.5: Daily sales summary
![Page 37: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/37.jpg)
Listing 13.6: Creating date views
![Page 38: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/38.jpg)
Listing 13.7: Database view role playing
![Page 39: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/39.jpg)
13.8 the final
![Page 40: RDBMS ASSIGNMENT](https://reader033.vdocuments.us/reader033/viewer/2022061514/558101e4d8b42a9d198b51dd/html5/thumbnails/40.jpg)
THANK YOU