‍SQL Project

A college project which provided an opportunity to consolidate an understanding of Database Design and SQL by taking a database scenario, and:

1. Drawing an ERD
2. Implement the ERD as a set of tables in a database
3. Populate the database with sample data
4. Write questions and the matching SQL queries to answer them (focus on Analytic Functions in Oracle) and execute those SQL queries to interrogate and summarize the data in the database
5. Record a screencast that explains one of the more comprehensive/ambitious queries in the project

‍

In brief, the project provided a short scenario about an organization, in this case School Ski Trips, and required the design of an SQL based database to be prepared and for powerful queries to be executed to interrogate the data in the database.

ERD's

The ERD's for the project were as below:

‍

The above ERD shows the one-to-many relationship between schools and Ski Trips. This shows that a school may go on any school trip, while for this scenario I have assumed that only one school goes one ski trip. There is also optionality as a School may exist in the database on its own, but a Ski Trip cannot exist without a school.

‍

The above fully attributed ERD shows the link between School Id in the school table and School Id in the Ski Trip table. School Id is the primary key in the school table and the foreign key in the ski trip table. Ski trip id is the primary key in the ski trip table.

Functions

To fully investigate the database, I processed many different analytic functions. Analytic Functions which are sometimes called window functions can be split into three main categories; Aggregate, Ranking, and Value as outlined from the below image from the website Strata Scratch. 

Within the project I wrote various syntaxes for different scenarios interacting with the created database. A list of the functions used in the queries are as below:

Aggregate

Query1: Avg, Median, Min, Max

Query2: Max, Min, Avg, Sum

Query3: Sum

Query4: Sum Calculation

Query5: Running Totals

Query6: Shortfall Calculation using Max

Query7: Max Calculation

Query8: Average Calculation

Ranking

Query9: Row Number

Query10: Rank

Query11: Dense Rank

Query12: Percent Rank

Query13: Ntile

Query14: Nth Row

Value

Query15: Lag

Query16: Lag Calculation

Query17: Lead

Query18: First Value & Last Value

Query19: Nth Value

Additional Functions

Query20: ListAgg

Query21: Correlation

Query22: Prediction

Why not watch it?

As part of the report, I created a screencast for one of the more complex queries. The video can be viewed below on my YouTube channel:

SQL | Project School Ski Trip: Prediction Query

Report Files

You can view the main project report in PDF format on the below Google drive:

https://drive.google.com/drive/folders/1pa-LhYa1ii04pcmKLkyeXSErMxYnf-py?usp=share_link

‍