Database Assignment - Queries, Reports, and Form Creation

This assignment solution demonstrates the creation and execution of SQL queries, report generation, and form design within a database context. The solution covers several key tasks, including creating and populating tables for members, mechanics, appointments, and administration staff. It details the implementation of queries to retrieve specific data based on various criteria such as location, phone numbers, and specializations. The solution also includes the creation of a form based on a query and a report summarizing mechanic specializations. This assignment is designed to showcase proficiency in database management and data manipulation techniques, providing a comprehensive overview of database operations and reporting.

TASK 1
The required four tables, member, mechanic, appointment and administration staff were created
successfully.

TASK 2
The data was populated in each of the four tables, as shown in below pictures –

TASK 3
The relationships identified within the database are linked as follows –
TASK 4
1. Find the first name, surname, suburb and phone of all the members who live in Lidcombe (do
not show suburb in answer). Your query should arrange the data in ascending order of
surname. Save the query as Suburb. Run the query. Take a screen print of the result and paste
it into the Word document.
2. Find the first name and surname of all the members that live in a postcode that ends with the
digit “4”. Arrange the output of the query in descending order of first name. Save the query
as Postcode.

3. Find the name and address details of all the Members who live in NSW and have a telephone
number that ends with a "02" or a "12". Save the query as Tel02or12.
4. Find all the mechanics who charge $60 or more per hour. Include the Mechanic ID,
Mechanic name, specialization, and hourly rate in the result. Save the query as
HighlyPaidMechanics.
5. Find the first name, surname, and hire date of all the administration staff members who were
hired in 2017. Arrange the query output in the descending order of hire date. Save the query
as 2017Recruit.

6. Find all appointments that have been booked for October 12, 2018. Include Member name,
Mechanic name, appointment time and duration. Arrange the query output in the ascending
order of Appointment time. Save the query as AppointmentOfDay.
7. Find the appointment details of each member who has multiple visits with the mechanics.
Include Member name, Appointment date and time in the result. Arrange the query output in
the ascending order of Appointment date. Save the query as MultipleVisit.
TASK 5

Find the total amount charged for all the appointments that have been booked by each member.
Include the MemberID, FirstName, Surname in the query along with the total amount charged. Save
the query as MemberBill.
TASK 6
The following is the HighlyPaidMechanics form created by HighlyPaidMechanics query.
TASK 7
Create a query that will select the different specialisation areas of the mechanics and the number of
available mechanics in the service centre.

After creating the query, the following report is created and named as SpecialisedMechanics.