Repository files navigation

A SQL project based on theWideWorldImporters database. I've created new SQL objects in theChallenges schema:

• Challenges.MonthlySalesSummary - A view of monthly sales figures split by city
• Challenges.TopSellingProductsByCity - A view of the top 5 selling products split for each city
• Challenges.StockItemSalesDistribution - A view showing the cumulative distribution of revenue by product
• Challenges.ufn_GetTotalOrderWeight - A scalar function returning the total weight of a given OrderID
• Challenges.ufn_DaysSinceLastInvoice - A tabular function returning the date of the previous invoice, current invoice and the number of days since the last invoice for an InvoiceID
• Challenges.usp_PurgeCustomerData - A stored procedure to remove customer data from the database after a GDPR request

The project has been built into a.dacpac file for easy deployment using either SQL Server Data Tools (SSDT) or Azure Data Studio (see Installation).

CI/CD is achieved using Azure Pipelines to validate the build process. SQLFluff is used to ensure consistency across code.

Project structure:

WorldWideImporters is a sample database provided by Microsoft, to install followthis guide.

SQLFluff is a SQL linter and formatter, to install followthis guide.

This repository builds into a Data-Tier Application Package (.dacpac), which is a single file containing the full database schema. This can be used to deploy the database to various environments using SQL Server Data Tools in Visual Studio or Azure Data Studio.

To deploy, download the latest.dacpac fromReleases and followthis guide. This will add the Challenges schema to the WorldWideImporters database installed above.