MIS437

download the file attached!

Module 4 Lab Activity: Group By and Implementing a Data Warehouse

Follow the instructions below for this Lab Activity. The data you will need to use is linked below. Once completed, upload the appropriate deliverable(s) to the corresponding assignments submission folder for this activity.

Deliverable:

· Using the dvdrental database, write queries to provide the results below. Submit a query for each problem below. NOTE – if you are unable to successfully create the query, submit what you have tried and a summary of your thought process.

· A list of all movie titles that have a length equal to or over 3 hours

· A list of all movie titles that have the word ‘Dinosaur’ in it

· A list of all movie titles that belong to the category name ‘Animation’

· A list of all movie titles that Ray Johansson starred in

· A list of all cities that have dvd rental stores (I sometime miss going to rent a dvd)

· A print out of your unlocked achievements in Microsoft. These can be found under your Profile > Achievements. This should now include the content from Module 4.

Part 1 – Joins using PostgreSQL

Instructions:

· Complete the PostgreSQL GROUP BY tutorial:

https://www.postgresqltutorial.com/postgresql-group-by/

· Complete the PostgreSQL HAVING tutorial:

https://www.postgresqltutorial.com/postgresql-having/

· Complete the PostgreSQL UNION tutorial:

https://www.postgresqltutorial.com/postgresql-union/

· Complete the PostgreSQL INTERSECT tutorial:

https://www.postgresqltutorial.com/postgresql-intersect/

· Complete the PostgreSQL EXCEPT tutorial:

PostgreSQL EXCEPT

· Complete the PostgreSQL GROUPING SETS tutorial:

https://www.postgresqltutorial.com/postgresql-grouping-sets/

· Complete the PostgreSQL CUBE tutorial:

https://www.postgresqltutorial.com/postgresql-cube/

· Complete the PostgreSQL ROLLUP tutorial:

https://www.postgresqltutorial.com/postgresql-rollup/

· Complete the PostgreSQL Subquery tutorial:

https://www.postgresqltutorial.com/postgresql-subquery/

· Complete the PostgreSQL ANY tutorial:

https://www.postgresqltutorial.com/postgresql-any/

· Complete the PostgreSQL ALL tutorial:

https://www.postgresqltutorial.com/postgresql-all/

· Complete the PostgreSQL EXISTS tutorial:

https://www.postgresqltutorial.com/postgresql-exists/

· Complete the PostgreSQL Get Sizes tutorial:

https://www.postgresqltutorial.com/postgresql-database-indexes-table-size/

Part 2 – Implementing a Data Warehouse

Throughout the readings this week you will find several exercises built in. These will help you learn the Microsoft Azure platform and how it is used for data warehousing.

Instructions:

· Make sure you are logged into your NLU Microsoft Account when accessing the following modules. NOTE – You are most likely already signed in if you have checked your email through the online portal.

· Work through the following exercises while completing the readings. The readings can be found on D2L Module 4: Activities & Assignments. NOTE – Remember that exercises within a module may stack.

· Exercise – Create a data warehouse in Azure Synapse Analytics by using the portal

· Exercise – Create an Azure Synapse Analytics server in the Azure portal

· Exercise – Connect to an Azure Synapse Analytics data warehouse by using SQL Server Management Studio

· Exercise – Run queries against an Azure Synapse Analytics data warehouse

· Exercise – Query an Azure Synapse Analytics data warehouse by using Power BI

· Exercise – Create a blank Azure Synapse Analytics

· Exercise – Create a blob container for the source data

· Exercise – Upload data to an Azure Blob storage container

· Exercise – Retrieve the URL and access key for the storage account

· Exercise – Import data from blob storage to Azure Synapse Analytics by using PolyBase