Just IT - Software Skills Bootcamp - Database Assignment



TASK 1


Creating the Database





Creating the Tables


Adding in the foreign keys






TASK 2

Below are screenshots of the tables populated with data

Members

Vehicle

Engineer

Engvan

Breakdown


The breakdown table had specific requirements in the data that was to be stored there



  1. 2 breakdowns on the same day: See 2022-07-28
  2. 3 breakdowns in the same month: 2022-07-28 has had 2 breakdowns and 2022-07-15 has had 1 breakdown. Together that is 3 breakdowns in the same month.

At least 3 vehicles broken down more than once: See SQL statement and accompanying results shown below

TASK 3

The names of members who live in a location
All cars registered with the company
The number of engineers that work for the company
The number of members registered


All the breakdowns after a particular date

Query shows breakdowns since 1st January 2023

All breakdowns between 2 dates

Query shows all breakdowns between 1st January 2022 and 30th June 2022 (both inclusive)

How many times a particular vehicle has broken down

Query shows how many times vehicle "DXVK09BQIA" has broken down

Vehicles that have broken down more than once

TASK 4

This task involved the creation of a new table (MshipType)

TASK 5


TASK 6

All the vehicles a member owns

Number of vehicles each member owns in descending order

The number of vans driven by a particular engineer
All vehicles broken down in a particular location includes Member details
All vehicles that have broken down along with the member details and the engineer who attended

All breakdowns along with member and engineer details between two dates

These are 3 additional queries that would be useful for the breakdown company

Shows all the breakdowns there have been so far in the current year.

Shows how many breakdowns there have been grouped by year.
Shows how many members have selected each of the different membership types

TASK 7

This task did not involve using the breakdown table, instead a different table was used appropriate for the tasks to be completed.


Here is an example of a data list of all the orders from a transactional website. In total there are 100 records in this table.
TABLE NAME: Orders



The functions used in this task focus on the 'order_payment' field as this is the best field to test out how the following functions work.

MIN()
This function returns the smallest number found in the field specified.




In this case the smallest payment made in the 'order_payment' field is '2.46'.
MAX()
This function returns the largest number found in the field specified. In simpler terms it does the opposite to "min()".




In this case the maximum order payment made in the 'order_payment' field was '100.00'.
AVG()
This function calculates and returns the average from the field specified.




In this case the average order payment is 50.781400
SUM()
This function adds all the numbers from the column specified and returns the calculated result




In this case the sum of all the order payments is 5078.14

TASK 8

This function is used to determine if a member has single or multicar policy, judging by how many cars that member owns


This function determines the policy cost of each vehicle based on how many breakdowns it has had