This assignment is designed to evaluate your understanding of database table design, ERD relationships and SQL queries. You will work with a simplified Vehicle Rental System database.
This section contains SQL queries written based on the designed database schema.
Each query demonstrates specific SQL concepts such as joins, subqueries, filtering, grouping, and aggregation.
The expected results for each query can be verified using the Sample Query Results (QUERY.md) file.
Retrieve booking information along with:
- Customer name
- Vehicle name
SELECTINNER JOIN
This query uses inner join to combine data from the bookings, users, and vehicles tables. It retrieves the customer name and vehicle name for each booking. Only records with matching users and vehicles are included.
Find all vehicles that have never been booked.
- NOT EXISTS
- Subquery
The subquery checks whether a booking exists for each vehicle. The not exists condition ensures that only vehicles with no associated bookings are returned.
Retrieve all available vehicles of a specific type (e.g., cars).
- SELECT
- WHERE
This query filters vehicles based on availability and vehicle type. Only vehicles that are available and classified as cars are retrieved.
Find the total number of bookings for each vehicle and display only those vehicles that have more than two bookings.
- GROUP BY
- HAVING
- COUNT
This query groups booking records by vehicle and counts the number of bookings per vehicle using the count() function. The having clause filters the grouped results to show only vehicles with more than two bookings.
Question 1 What is a foreign key and why is it important in relational databases?
Question 2 What is the difference between WHERE and HAVING clauses in SQL?
Question 3 What is a primary key and what are its characteristics?
Question 4 What is the difference between INNER JOIN and LEFT JOIN in SQL?