Question

 Display the mechanic name along with the total number of vehicles serviced by each mechanic.

08 Aug 2026
Answer :
Word Count : 876
In a database system used by a vehicle service centre, the information about mechanics and the vehicles serviced by them is generally stored in related tables. To display the mechanic name along with the total number of vehicles serviced by each mechanic, SQL aggregate functions can be used with the `GROUP BY` clause. The `COUNT()` function is particularly useful because it counts the number of records associated with each mechanic. Suppose the database contains two tables: `Mechanic` and `Vehicle`. The `Mechanic` table may contain attributes such as `Mechanic_ID` and `Mechanic_Name`, while the `Vehicle` table may contain `Vehicle_ID`, `Vehicle_Number`, and `Mechanic_ID`. The `Mechanic_ID` acts as a common attribute and establishes a relationship between the two tables. A suitable SQL query is: ```sql SELECT m.Mechanic_Name, COUNT(v.Vehicle_ID) AS Total_Vehicles_Serviced FROM Mechanic m JOIN Vehicle v ON m.Mechanic_ID = v.Mechanic_ID GROUP BY m.Mechanic_Name; ``` Here, `m` and `v` are aliases for the `Mechanic` and `Vehicle` tables respectively. The `JOIN` operation combines records from both tables where the `Mechanic_ID` in the two tables is the same. This allows the database to associate every serviced vehicle with the mechanic responsible for servicing it. The `COUNT(v.Vehicle_ID)` expression counts the vehicles associated with each mechanic. The result is given the alias `Total_Vehicles_Serviced`, which makes the output easier to understand. The `GROUP BY m.Mechanic_Name` clause groups all records belonging to the same mechanic. Consequently, instead of displaying every vehicle individually, SQL produces one row for each mechanic and shows the total number of vehicles serviced by that mechanic. For example, if the `Mechanic` table contains: ```text Mechanic_ID Mechanic_Name ----------- ------------- 101 Ravi 102 Amit 103 Suresh ``` and the `Vehicle` table contains: ```text Vehicle_ID Vehicle_Number Mechanic_ID ---------- -------------- ----------- 1 DL01AB1234 101 2 DL02CD4567 101 3 DL03EF7890 102 4 DL04GH1111 102 5 DL05IJ2222 102 6 DL06KL3333 103 ``` the query will produce: ```text Mechanic_Name Total_Vehicles_Serviced ------------- ----------------------- Amit 3 Ravi 2 Suresh 1 ``` The order of the output is not guaranteed unless an `ORDER BY` clause is specified. If the mechanics
_________ _______ ______ ____ ___ ______ ___ _________ ________ ___ ____.
____ ___ _________ _________ ___ _______ ________ ________ _______ ______.
_______ _____ _______ _____ __________ ___ ________.
_____ ____ ______ _____ ___ ____.
_______ ____ ___ ___ _________ _______ ___ ________ ____.
______ _____ _____ ____ ________ _____.
______ ______ ______ ________ __________ ___ _________.
__________ ______ ____ ____ ________ __________ _______ _______.
________ ____ _______ ___ ___ ____ ________.
____ ____ ___ ___ ____ ______ ________ _____.
_____ _______ ________ __________ _____ _____ _________ _________ _________.
________ ________ _______ ___ ________.
____ ______ ______ __________ ____ _______ _________.
________ ______ ______ ____ ________ ________ __________.
____ _____ ______ ____ __________ _____ _________ _________ ___ _______.
_________ ____ ________ ________ ____ _________ ___ ______.
_____ ____ __________ __________ ___ ____ ________ ______ __________ ________.
_________ ____ ________ _______ ________ _____.
_________ ________ _______ __________ _________ __________.
_______ ____ ___ _________ ____ _________ ___ ______ _________ __________ ____ ___.
__________ ____ ___ __________ ____ ________ ____.
_________ __________ ____ ____ _____ _____ ________ _____ __________ ____.
_________ ________ __________ ____ ________ ___.
______ ________ __________ _______ _________ ___ _____.
________ _________ __________ ____ ________ ____.
______ _________ _______ _______ _____ ________ ____.
_______ ______ __________ _________ ________ ____ _________ _______ _______.
________ ___ __________ __________ ____ ____.
________ ____ ________ ______ _____ __________ _________.
_______ ________ _____ ________ ______ _________.
______ ________ _______ _________ ______ __________ ____ __________ ______ _________ ________ _______.
_________ ________ _____ _____ _____.
_______ ______ ____ ________ ____ ______ __________ ________ ____ ________ __________ ________.
__________ _____ ____ _____ ____ __________ _____.
_________ ___ _____ ______ __________ _______ _____ ___ _____ ____.
__________ _____ ________ _____ ______ _______ _________.
____ ___ _________ ___ ______ ____ _________.
________ ___ ______ _____ ________ _______.
_______ _________ _______ _______ ____ _______ ____ __________ ________ __________ ____.
____ ______ _______ _______ ________ __________ _______ _______ __________ ___ ______.
______ __________ _____ ___ _________ _________ _________ _____ ____ __________ ______.
____ _______ _______ _________ _______ ________.
___ ________ __________ __________ _____ _________ ______ ____ _________ ___ ______.
________ _____ ___ ____ ____ ____ _________ ________ ____ ________.
______ ___ ____ _________ __________ _________ ____ _____ ___ _________ __________.
___ _______ _______ __________ __________ ____ ___ ___ _____ ____.
_________ ________ ______ ________ _______ _____ _________ _______ _________ ___.
_____ ____ ___ __________ __________ __________ _________ ___.
________ ____ ___ _________ ________ ___ _____ ______ _____.
_______ _____ ___ _________ ____ _____ _____.
_____ ______ __________ ________ _____.
__________ ___ _______ _____ ____ ___ _______ _____ ______.
___ __________ ___ ________ ________.
__________ __________ ______ ____ _____.
___ _________ ______ _____ __________ _____ _______ ______ __________.
_______ _______ _______ ______ _______ ________ ____ _________ ______ ________.
_____ _____ ________ _____ _________.
__________ _____ ________ ______ ______ ___ _______ ___.
_____ ________ __________ ______ ______ __________ __________.
____ _________ ____ ___ __________.
____ ____ _______ ____ ________ ______.
___ _______ ________ ________ _____ _____ _________ ___.
_________ _______ _________ _______ _____.
________ ____ __________ __________ ________.
____ ______ ___ ___ _______ ____ ____.
____ _______ ______ _________ ___.
________ __________ _________ ________ __________ ____ ___ ___ ________ ____ ______.
____ ___ _________ ________ ________ _____ _____ ____ ______.
__________ _______ _______ _____ _____.
______ _________ ________.
IGNOU ACADEMY
Your IGNOU journey, made easier.
  • Solved Assignments
  • Study Notes
  • Guess Papers
  • Question Papers
Call Us WhatsApp
STUDY • PREPARE • SUCCEED
Skip in 10
Related Questions

. Display the top five customers based on the highest expenditure on vehicle servicing.

 Database Schema: (10 Marks)
Create the necessary tables to store the following information:
 A unique Contact_ID for each contact.
 Customer Name, Address, Mobile Number, and Email Address.
 A unique Vehicle_ID for each vehicle.
 Vehicle Registration Number, Model, Manufacturer, Year of Manufacture, and Fuel Type.
 Each customer may own multiple vehicles.
 A unique Mechanic_ID for each mechanic.
 Mechanic Name, Specialization, Experience, and Contact Number.
 A unique Service_ID for each service transaction.
 Service Date, Service Type (Periodic Service, General Repair, Engine Repair, Electrical Repair, Washing, etc.), Labour Charges, Spare Parts Cost, and Total Bill Amount.
 Each service transaction must be assigned to one mechanic.
 Multiple spare parts may be used during a service. Store the Part_ID, Part Name, Quantity, and Unit Price for each spare part. Your design should include:
 An ER Diagram for the Vehicle Service Management System.
 The final set of tables with all columns clearly defined.
 Underline the Primary Key and specify all Foreign Keys.
 Ensure that the database schema satisfies Third Normal Form (3NF).

Service Class
Create a class named Service with private data members to store:

Find the registration numbers and owner names

Vehicle Class
Create a class named Vehicle with private data members to store:
 Vehicle ID, Registration Number, Model, Manufacturer, Year of Manufacture, Fuel Type Include appropriate constructors and member functions.

: Project: Vehicle Service Management System
You are required to design and develop a Vehicle Service Management System for an automobile service
centre. This project will be completed in two parts. In Section A, you will design the database schema and
write SQL queries. In Section B, you will develop a C++ console application to manage vehicle service
records.
Section A:
Design a database schema in Third Normal Form (3NF) for the Vehicle Service Management System.

Display the average labour charges for each service type.

IGNOU Solved Assignments Assignments Ignou Projects Project Previous Year Solved Exam Papers Notes Notes Sample Question Papers Sample Question Papers
Login
Top
📞
Call Support Instant phone assistance IGNOU BCSL 135 2026 2027 -  Display the mechanic name along with the total number of vehicle | IGNOU Academy
→
🟢
WhatsApp Chat Fast live messaging
→
Email Us Business enquiries & support
→