COMP1350 Department of Computing- Trucks at Meraki

1: Create these tables based on the list provided above: TruckMake, TruckModel, Truck, Service, and Allocation.

Insert at least 5 records into each of the tables. No extra marks will be provided for adding more records, but more records may be needed depending on the query results for different questions.

2: Write a query to print the details (VIN Number, Colour, Cost) of the truck.

Note that the cost must be prefixed with a ‘$’ sign. Sort the records in the order of the most expensive truck at the top of the list.

3: Write a query to print the allocation details (VIN Number, Transport ID, from and to date, along with the number of days) each of the trucks is allocated/reserved for.

4: Write a query to print the truck details (VIN Number, name of the model), if the make of the truck is “Volvo”. Use equi-join to answer this question.

5: Write a query to print the VIN Numbers of the trucks if they have been allocated for 3 days or above for a transport that costs between $1500 and $2500. Please ensure no duplicate results are included. Use “join using” to answer this question.

6: Using a subquery, print name, cost and the maximum distance of the transport if the transport has been allocated (use the start date of the allocation here) within the last 6 months calculated from today (Today here implies the date the query is run. Must not hardcode the date)

7: Rewrite Task 6 using a Join on.

8: Write a query to print the names of transport, the maximum distance in kilometres, the maximum distance in miles that have used a red truck. Please ensure no duplicate results are included in the result. Please rename the column names and ensure the numerical columns have 2 decimal places only. You must use join on to write your answer

A sample result looks like this

                                                                                       

9: Write a query to print the number of trucks at Meraki in each colour. Sort the records in order of the number of trucks with the highest number at the top of the list.

10: Show the make of the truck (name) along with the number of models, if the number of models is more than one.

11: Write a query to print the details of all trucks which have never been booked.

12: Write a query to print the details of any transport/service that is more than $5000.

Only include the allocation (both to and from dates) that have been made in either January of any year or any month in the years – 2020 or 2021.

Sort the results by the cost of transport/service in descending order.

13: Write a query to print the names of make & model of all red trucks that have had at least one allocation.

Section-Two

This section has 2 questions. Each of the questions is worth 5 marks. You may be eligible for partial marks if there are errors in your answers. To be able to answer the questions, you will have to create and populate the following tables based on the list provided above: Customer, BookingReq, Invoice, and Payment.

14: Write a query to print all the details of the booking request and the total amount that it has been invoiced for. The data saved in the table are exclusive of taxes. Your query should include the 10% tax. Only include reservations that exceed the total amount of $7,000.

15: Write a query to print the names of the customers who have made payments in the fourth quarter of 2020 that is lesser than the average cost of payments made in the fourth quarter of 2020.

Section-Three

This section has 2 questions. Each of the questions is worth 5 marks. You may be eligible for partial marks if there are errors in your answers. To be able to answer the questions, you will have to create and populate the tables- Location, Staff, and TripSchedule based on the list provided above.

16: Write a query to print the names of Staff and their managers, only if the managers manage 2 staff or more.

17: Write a query to print the names of the customers who have a schedule booked by a manager with the trips that start in the afternoon (between 12 pm -3 pm) and end before midday (between 6 am – 12 pm) along with the names of the staff who created the schedule.

Section-Four

This section has 2 questions. Each of the questions is worth 5 marks. You may be eligible for partial marks if there are errors in your answers. To be able to answer the questions, you will have to create the ‘SupportStaff table based on the list provided above

18: Write a query to print the details of the schedule (Start Location Name, Name of the Service/Transport) and the staff involved in the activities. This should involve the staff who created the schedule and all the support staff involved. Please note you will have to display the data in multiple rows.

A sample result looks like this

                                                             

19: List the name of the start and end location, for every schedule along with the name of the customer the schedule is for. Only include Customers whose surnames start with J. Also, this schedule must have at least 2 invoices generated for it.

20: Write a query to list the id of the schedule along with the total cost (in currency format) of both invoice amount and payment amount for every schedule. If the schedule doesn’t involve invoice/payment, 0 must be displayed as the cost.

A sample result looks like this

                                                                               

QUALITY: 100% ORIGINAL PAPER – NO PLAGIARISM – CUSTOM PAPER

Leave a Reply

Your email address will not be published. Required fields are marked *