Due Date: September 18, 2023
Purpose: To learn to build a more complex data 
base structure and to make alterations to the 
database.

Build additional code where you delete 
the databases at the end.
So, I can run all the code as many times 
as I want.

Your files name will be PX_vehicle_lastname
File PX_vehicle_lastname.sql (The actual sql code)
File PX_vehicle_lastname.png (Screen print of sql code inside the IDE)
File PX_vehicle_lastname.mp4 (Screen recording of this SQL running.)

Be sure to drop it off into google classroom.

**1. Initial Setup:**

Implement the following in the order I give it to you.
Copy the first part as a comment for your table creation.

Create two tables: `vehicles` and `vehicle_types`.

**vehicles**:
- **vehicle_id**: Identifier for each vehicle.
- **brand**: Brand of the vehicle, e.g., "Toyota".
- **model**: Specific model, e.g., "Camry".
- **year**: Year of production, e.g., "2020".
- **type_id**: A foreign key referring to the type of vehicle.

**vehicle_types**:
- **type_id**: Identifier for each type.
- **type_name**: Type of vehicle, e.g., "Sedan", "SUV", "Truck".

```SQL
CREATE TABLE vehicle_types (
    type_id INT PRIMARY KEY AUTO_INCREMENT,
    type_name VARCHAR(50) NOT NULL
);

CREATE TABLE vehicles (
    vehicle_id INT PRIMARY KEY AUTO_INCREMENT,
    brand VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    year INT NOT NULL,
    type_id INT,
    FOREIGN KEY (type_id) REFERENCES vehicle_types(type_id)
);
```

Due Date: September 18, 2023
**2. Complete and run the commands below.

**a. Inserting Data**:
Insert sample vehicle types and vehicles.

```SQL
INSERT INTO vehicle_types (type_name)
 VALUES ('Sedan'), ('SUV'), ('Truck');

INSERT INTO vehicles (brand, model, year, type_id) VALUES 
('Toyota', 'Camry', 2020, 1), 
('Ford', 'Explorer', 2019, 2), 
('Chevrolet', 'Silverado', 2018, 3);
(Add 3 more types of vehicles with data)
```

**b. Retrieve All Vehicles**:
Fetch all columns and rows from the `vehicles` table.

```SQL
SELECT * FROM vehicles;
```

**c. Filter Vehicles by Year**:
Retrieve vehicles produced after 2018.

```SQL
SELECT * FROM vehicles WHERE year > 2018;
```

**d. Find the Type of Specific Vehicles**:
Fetch brand, model, and type name of all vehicles.

```SQL
SELECT v.brand, v.model, t.type_name 
FROM vehicles v 
JOIN vehicle_types t ON v.type_id = t.type_id;
```

**e. Count Vehicles by Type**:
Count the number of vehicles for each type.

```SQL
SELECT t.type_name, COUNT(v.vehicle_id) as num_vehicles 
FROM vehicle_types t 
LEFT JOIN vehicles v ON t.type_id = v.type_id 
GROUP BY t.type_name;
```

**f. Find Vehicles by Brand**:
Fetch details of vehicles with brand "Toyota".

```SQL
SELECT * FROM vehicles WHERE brand = 'Toyota';
```

**g. Update Vehicle Data**:
Change the brand of a vehicle with vehicle_id = 1 to "Honda".

```SQL
UPDATE vehicles SET brand = 'Honda' WHERE vehicle_id = 1;
```

**h. Delete Specific Vehicle Record**:
Remove the vehicle with vehicle_id = 3 from the database.

```SQL
DELETE FROM vehicles WHERE vehicle_id = 3;
```

Due Date: September 18, 2023
Alright, let's expand on our vehicle database 
by adding a table for vehicle owners.
Add my comments to your create owners table.

**owners**:
- **owner_id**: Identifier for each owner.
- **first_name**: Owner's first name.
- **last_name**: Owner's last name.
- **address**: Owner's address.
- **phone_number**: Owner's phone number.

In addition, to link vehicles with their respective owners, 
we should introduce a foreign key `owner_id` in the 
`vehicles` table. 
This assumes that each vehicle can only have one owner 
at a time in our system.

Here's how we can create the `owners` table and 
modify the `vehicles` table:

```SQL
-- Create owners table
CREATE TABLE owners (
    owner_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    address VARCHAR(255),
    phone_number VARCHAR(15)
);

Due Date: September 18, 2023
-- Alter vehicles table to add owner_id as foreign key
ALTER TABLE vehicles 
ADD COLUMN owner_id INT,
ADD FOREIGN KEY (owner_id) REFERENCES owners(owner_id);
```

Due Date: September 18, 2023
**Exercises involving the owners table**:

**a. Inserting Owner Data**:
Insert a sample owner.

```SQL
INSERT INTO owners (first_name, last_name, address, phone_number) 
VALUES ('John', 'Doe', '123 Elm Street', '123-456-7890');
```

**b. Linking Vehicle to Owner**:
Assign an owner to a vehicle.

```SQL
UPDATE vehicles SET owner_id = 1 
WHERE vehicle_id = 1; -- assuming the owner you added has an owner_id of 1
```

**c. Retrieve Vehicles with Owner Information**:
Fetch vehicle and associated owner details.

```SQL
SELECT v.brand, v.model, o.first_name, o.last_name 
FROM vehicles v 
JOIN owners o ON v.owner_id = o.owner_id;
```

**d. Retrieve Owners and Their Vehicles**:
Fetch owners who own a particular type of vehicle, e.g., 'SUV'.

```SQL
SELECT o.first_name, o.last_name, v.brand, v.model 
FROM owners o 
JOIN vehicles v ON o.owner_id = v.owner_id 
JOIN vehicle_types t ON v.type_id = t.type_id 
WHERE t.type_name = 'SUV';
```

**e. Change Vehicle Ownership**:
Update the owner of a vehicle.

```SQL
UPDATE vehicles SET owner_id = 2 
WHERE vehicle_id = 1; -- changes the owner of vehicle with vehicle_id=1 to the owner with owner_id=2
```

**f. Remove an Owner and Their Vehicles**:
To remove an owner, 
you first need to ensure 
that the owner doesn't have 
any vehicles assigned 
(to avoid foreign key violations), 
or you need to delete or update those vehicle records first.

```SQL
-- Remove vehicles for owner with owner_id=2
DELETE FROM vehicles WHERE owner_id = 2;

-- Then remove the owner
DELETE FROM owners WHERE owner_id = 2;
```

Remember, it's vital to ensure data integrity
 when updating or deleting records, 
especially when working with foreign 
keys and relationships.