Advanced SQL Queries MCQs Using Real Car Data (Mileage, Speed & Safety Ratings)

✔ Scroll down and test yourself — answers are hidden under the “View Answer” button.

🚨 Quiz Instructions:
Attempt all questions first.
✔️ Click SUBMIT at the end to unlock VIEW ANSWER buttons.

This SQL quiz uses real automobile data including mileage, speed, and Global/Euro NCAP safety ratings to help you master advanced SQL queries used in real-world database scenarios.

The table given below shows popular car brands and their variants. Each car is listed along with its best-known feature, mileage, top speed, and an indicative international safety rating (based on widely reported Global/Euro NCAP crash test results).

Table: CAR_SPECS

car_id	brand	model	best_feature	mileage_kmpl	top_speed_kmph
1	Toyota	Corolla	Mileage	22	180
2	Honda	City	Comfort	18	190
3	BMW	M3	Speed	12	280
4	Hyundai	Verna	Mileage	21	185
5	Mercedes	C-Class	Comfort	14	240

Table: CAR_SAFETY

car_id	ncap_agency	safety_rating_stars	airbags
1	Global NCAP	5	7
2	Global NCAP	4	6
3	Euro NCAP	5	8
4	Global NCAP	5	6
5	Euro NCAP	5	7

About the Cars Used in This SQL Quiz

This SQL quiz is built using real-world automobile models such as Toyota Corolla, Honda City, BMW M3, Hyundai Verna, and Mercedes-Benz C-Class. These cars are widely compared based on fuel mileage, top speed, driving comfort, and Global / Euro NCAP safety ratings.

Such specifications are commonly used in car reviews, sedan comparisons, and automobile buying guides.

Queries involve CAR_SPECS table.

1. Which SQL query retrieves the car brand and model having the highest mileage in the CAR_SPECS table?

SELECT brand, model FROM CAR_SPECS WHERE mileage_kmpl = (SELECT MAX(mileage_kmpl) FROM CAR_SPECS);
SELECT brand, model FROM CAR_SPECS WHERE mileage_kmpl > MAX(mileage_kmpl);
SELECT brand, model FROM CAR_SPECS GROUP BY mileage_kmpl;
SELECT brand, model FROM CAR_SPECS ORDER BY mileage_kmpl;

✅ Correct Answer: A.
📌 The highest mileage car is Toyota Corolla, with a mileage of 22 kmpl.

2. Which SQL query correctly retrieves the brand, model, and price of all cars? Assume another table CAR_PRICE(car_id, price_lakh).

SELECT brand, model, price FROM CAR_SPECS;
SELECT brand, model, price_lakh FROM CAR_SPECS, CAR_PRICE;
SELECT brand, model FROM CAR_SPECS JOIN CAR_PRICE;
SELECT c.brand, c.model, p.price_lakh FROM CAR_SPECS c JOIN CAR_PRICE p ON c.car_id = p.car_id;

✅ Correct Answer: D

3. Which query finds the car with the highest speed for each best_feature?

SELECT * FROM CAR_SPECS WHERE top_speed_kmph = ( SELECT MAX(top_speed_kmph) FROM CAR_SPECS );
SELECT * FROM CAR_SPECS GROUP BY best_feature;
SELECT * FROM CAR_SPECS c1 WHERE top_speed_kmph = ( SELECT MAX(top_speed_kmph) FROM CAR_SPECS c2 WHERE c1.best_feature = c2.best_feature );
SELECT * FROM CAR_SPECS ORDER BY best_feature;

✅ Correct Answer: C
📌 The fastest cars per best feature are Toyota Corolla (Mileage), Mercedes C-Class (Comfort), and BMW M3 (Speed).

4. Which SQL query ranks cars based on top speed (highest first)?

SELECT brand, model FROM CAR_SPECS GROUP BY top_speed_kmph;
SELECT brand, model, RANK(top_speed_kmph) FROM CAR_SPECS;
SELECT brand, model, RANK() OVER (ORDER BY top_speed_kmph DESC) AS speed_rank FROM CAR_SPECS;
SELECT brand, model ORDER BY RANK();

✅ Correct Answer: C
📌 Based on top speed, BMW M3 ranks highest, followed by Mercedes C-Class and Honda City.

5. Which SQL query finds the top-speed car within each best_feature category?

SELECT * FROM ( SELECT brand, model, best_feature, top_speed_kmph, ROW_NUMBER() OVER (PARTITION BY best_feature ORDER BY top_speed_kmph DESC) rn FROM CAR_SPECS ) t WHERE rn = 1;
SELECT * FROM CAR_SPECS GROUP BY best_feature;
SELECT * FROM CAR_SPECS ORDER BY best_feature;
SELECT best_feature, MAX(top_speed_kmph) FROM CAR_SPECS;

✅ Correct Answer: A
📌 The top-speed cars per feature are Toyota Corolla (Mileage), Mercedes C-Class (Comfort), and BMW M3 (Speed).

---

Queries involve both CAR_SPECS and CAR_SAFETY tables.

1. Which SQL query retrieves the brand and model of all cars having a 5-star safety rating?

SELECT c.brand, c.model FROM CAR_SPECS c JOIN CAR_SAFETY s ON c.car_id = s.car_id WHERE s.safety_rating_stars = 5;
SELECT brand, model FROM CAR_SPECS, CAR_SAFETY WHERE safety_rating_stars = 5;
SELECT brand, model FROM CAR_SPECS, CAR_SAFETY;
SELECT * FROM CAR_SPECS JOIN CAR_SAFETY;

✅ Correct Answer: A
📌 The 5-star safety rated cars are Toyota Corolla, BMW M3, Hyundai Verna, and Mercedes C-Class.

2. Which query finds the car with the highest mileage among 5-star safety rated cars?

SELECT brand, model FROM CAR_SPECS GROUP BY mileage_kmpl;
SELECT brand, model FROM CAR_SPECS ORDER BY mileage_kmpl;
SELECT brand, model FROM CAR_SAFETY WHERE safety_rating_stars = 5;
SELECT brand, model FROM CAR_SPECS WHERE mileage_kmpl = ( SELECT MAX(c.mileage_kmpl) FROM CAR_SPECS c JOIN CAR_SAFETY s ON c.car_id = s.car_id WHERE s.safety_rating_stars = 5 );

✅ Correct Answer: D
📌 Among cars with a 5-star safety rating, Toyota Corolla offers the highest mileage.

3. Which SQL query retrieves the highest safety-rated car(s) for each best_feature?

SELECT * FROM CAR_SPECS c JOIN CAR_SAFETY s ON c.car_id = s.car_id WHERE s.safety_rating_stars = ( SELECT MAX(s2.safety_rating_stars) FROM CAR_SAFETY s2 JOIN CAR_SPECS c2 ON s2.car_id = c2.car_id WHERE c.best_feature = c2.best_feature );
SELECT * FROM CAR_SPECS GROUP BY best_feature;
SELECT * FROM CAR_SAFETY GROUP BY safety_rating_stars;
SELECT * FROM CAR_SPECS ORDER BY best_feature;

✅ Correct Answer: A
📌 For each best feature category, the query retrieves the car(s) with the highest safety rating, which is 5 stars.
📌 Based on the given data, the highest safety-rated cars per feature are:

• Mileage: Toyota Corolla, Hyundai Verna (5★)
• Comfort: Mercedes C-Class (5★)
• Speed: BMW M3 (5★)

4. Which SQL query ranks cars based on safety rating (highest first)?

SELECT brand, model FROM CAR_SPECS GROUP BY safety_rating_stars;
SELECT RANK(safety_rating_stars) FROM CAR_SAFETY;
SELECT c.brand, c.model, RANK() OVER (ORDER BY s.safety_rating_stars DESC) AS safety_rank FROM CAR_SPECS c JOIN CAR_SAFETY s ON c.car_id = s.car_id;
SELECT brand, model ORDER BY RANK();

✅ Correct Answer: C
📌 Cars with 5-star safety ratings rank highest, whereas Honda City ranks lower due to its 4-star rating.

5. Which query retrieves the safest car within each best_feature category?

SELECT * FROM CAR_SPECS GROUP BY best_feature;
SELECT * FROM ( SELECT c.brand, c.model, c.best_feature, ROW_NUMBER() OVER ( PARTITION BY c.best_feature ORDER BY s.safety_rating_stars DESC ) rn FROM CAR_SPECS c JOIN CAR_SAFETY s ON c.car_id = s.car_id ) t WHERE rn = 1;
SELECT * FROM CAR_SAFETY ORDER BY safety_rating_stars;
SELECT best_feature, MAX(safety_rating_stars) FROM CAR_SAFETY;

✅ Correct Answer: B
📌 The safest cars within each feature category are Toyota Corolla (Mileage), Mercedes C-Class (Comfort), and BMW M3 (Speed).

Real-World Automobile Data Use Case

Automobile companies and car review platforms often store vehicle specifications such as fuel efficiency, engine performance, top speed, and safety ratings in relational databases. SQL queries like those used in this quiz help analysts generate car comparison reports, identify best mileage cars, and rank safest sedans for customers.