CIS课程SQL项目求助：编写符合特定车辆特征的SQL查询语句

Hey there! Since you're just starting out with SQL for your CIS course project, I'll walk you through a straightforward query that meets your requirements, plus add some notes to help you adapt it if your database structure is a bit different.

First, let's assume you have a table (let's call it driver_vehicles) that links drivers to their vehicles, with these relevant columns:

• driver_name: The name of the driver
• license_plate_number: The vehicle's license plate
• vehicle_make: The brand of the vehicle (e.g., BMW)
• vehicle_year: The year the vehicle was made
• vehicle_color: The color of the vehicle

Here's the query to pull the data you need:

SELECT 
    driver_name,
    license_plate_number,
    vehicle_make,
    vehicle_year,
    vehicle_color  -- Added this as a quick verification check
FROM 
    driver_vehicles
WHERE 
    vehicle_make = 'BMW'
    AND vehicle_color = 'White'
    AND license_plate_number LIKE '%C%C%C%';

Let's break down the key parts: #

• vehicle_make = 'BMW': This filters results to only BMW vehicles. If your database stores make names in lowercase (e.g., 'bmw'), adjust this to LOWER(vehicle_make) = 'bmw' to avoid case sensitivity issues.
• vehicle_color = 'White': Narrows results to white vehicles. Same case sensitivity tip applies here if needed.
• license_plate_number LIKE '%C%C%C%': The % is a wildcard that matches any sequence of characters (including none). This pattern will find any license plate with three 'C's anywhere in it—regardless of their position (e.g., 'CCC123', 'C1C2C3', '123CCC').

If your data is split across two tables: #

If you have a separate drivers table (with driver_id and driver_name) and a vehicles table (with vehicle_id, driver_id, license_plate_number, etc.), you'll need to join them like this:

SELECT 
    d.driver_name,
    v.license_plate_number,
    v.vehicle_make,
    v.vehicle_year,
    v.vehicle_color
FROM 
    drivers d
JOIN 
    vehicles v ON d.driver_id = v.driver_id
WHERE 
    v.vehicle_make = 'BMW'
    AND v.vehicle_color = 'White'
    AND v.license_plate_number LIKE '%C%C%C%';

Feel free to tweak column or table names to match your actual database schema—this is just a starting point tailored to your request. Let me know if you need help adjusting it further!

内容的提问来源于stack exchange，提问作者Soft_ibo