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.

☰ Quick Links - Browse Related MCQs

🚨 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.

RDBMS MCQ - Referential integrity and cardinality ratio in ER model

Multiple choices questions in DBMS, Interview questions in RDBMS, How referential integrity and cardinality ratio are related? How do we represent referential integrity in ER diagram? How to model referential integrity using cardinality ratios in entity relationship diagram?

RDBMS solved MCQ - Referential integrity and cardinality ratio in entity relationship model

< Previous

Next >

 

1. A relationship R connecting entity sets E and F has referential integrity from E to F, i.e., the edge connecting R and F has an arrow directed towards F. Which among the following statements may be true?

a) R is many-to-many from E to F

b) R is many-to-one from E to F

c) R is many-to-one from F to E

d) R is one-to-one from E to F

Answer: (b) R is many-one from E to F and (d) R is one-one from E to F Referential integrity is a constraint added to a many-to-one relationship in the “one direction.” Since a one-to-one relationship is a special case of a many-to-one relationship, R may be one-one from E to F. While reducing ER diagram into conceptual schemas, the primary key of the ONE side entity set will be added as the foreign key in the MANY side (or ONE side in one-to-one relationship) entity set. Example:   In the ERD given above, Teacher is on ONE side and Subject is MANY side. After converting the ERD, the conceptual schemas are as follows; Teacher (ERP, Name, Qualification, Address, Phone) Subject (Sub_ID, Sub_Name, Syllabus, Medium, ERP) ERP in Subject is the foreign key that refers ERP of Teacher.

 

< Previous

Next >

************************

Related posts:

• Go to Multiple Choice Questions in DBMS home

• Go to Normalization - MCQs page

• Go to General Mixed Quiz in all topics of DBMS page

• Go to Advanced DBMS Concepts page

What is referential integrity?

Cardinality ratios and referential integrity in Entity relationship model

How to represent referential integrity for a given type of relationship set in ER diagram?

Referential integrity and ER modeling

DBMS solved MCQ; database management system MCQ solved questions