The law firm project 'Order By SQL', is a group project created for the final presentation of an 8-week certificate course run by Code First Girls. The project involves the creation of a database and implementing SQL queries to extract useful insights from it. The requirements for the project are listed here:
- Create relational database with minimum 5 tables
- Set Primary and Foreign Key constraints to create relations between the tables
- Using any type of joins create a view that combines multiple tables in a logical way
- In your database, create a stored function that can be applied to a query in your database
- Prepare an example query with a subquery to demonstrate how to extract data from your database for analysis
- Create database diagram where all table relations are shown
- In your database, create a stored procedure and demonstrate how it runs
- In your database, create a trigger and demonstrate how it runs
- In your database, create an event and demonstrate how it runs
- Create a view that uses at least 3-4 base tables; prepare and demonstrate a query that uses the view to produce a logically arranged result set for analysis
- Prepare an example query with group by and having to demonstrate how to extract data from your database for analysis
This project was voted joint best project of our cohort (50 students).
To explore this project first run the 'OrderBySQL DB Creation.sql' script in MySQL Workbench to create and fill the database. The 'OrderBySQL DB Diagram.mwb' and 'OrderBySQL Queries.sql' files can then be explored to show a relational database diagram and run queries respectively.
Two Tableau Dashboards were created to compliment this project, they can be found on my public account here https://public.tableau.com/app/profile/tara.biddle, named 'CFG Order By SQL - Revenue' and 'CFG Order By SQL - Cases' respectively.