DIY1.sql

CREATE DATABASE CarCompany;

USE CarCompany;



CREATE TABLE Cars_24012011080 (
    car_id INT PRIMARY KEY,
    model_name VARCHAR(50),
    year INT,
    price DECIMAL(10,2)
);

DESCRIBE Cars_24012011080;

CREATE TABLE Manufacturers_24012011080 (
    manufacturer_id INT PRIMARY KEY,
    name VARCHAR(50),
    country VARCHAR(50)
);

DESCRIBE Manufacturers_24012011080;


CREATE TABLE Production_24012011080 (
    production_id INT PRIMARY KEY,
    car_id INT,
    manufacturer_id INT,
    quantity INT,
    production_date DATE,
    FOREIGN KEY (car_id) REFERENCES Cars_24012011080(car_id),
    FOREIGN KEY (manufacturer_id) REFERENCES Manufacturers_24012011080(manufacturer_id)
);

DESCRIBE Production_24012011080;



INSERT INTO Cars_24012011080 (car_id, model_name, year, price) 
VALUES (2, 'EcoDrive', 2023, 18000);

SELECT * FROM Cars_24012011080;

-- Insert Manufacturer with ID = 2
INSERT INTO Manufacturers_24012011080 (manufacturer_id, name, country) 
VALUES (2, 'GreenMotors', 'USA');

SELECT * FROM Manufacturers_24012011080;


INSERT INTO Production_24012011080 (production_id, car_id, manufacturer_id, quantity, production_date) 
VALUES (1002, 2, 2, 200, '2023-07-15');

SELECT * FROM Production_24012011080;




SELECT * 
FROM Cars_24012011080 
WHERE year > 2020;


SELECT name, country 
FROM Manufacturers_24012011080;


UPDATE Cars_24012011080 
SET price = 19500 
WHERE car_id = 2;

SELECT * FROM Cars_24012011080;



UPDATE Manufacturers_24012011080 
SET name = 'EcoMotors' 
WHERE manufacturer_id = 2;

SELECT * FROM Manufacturers_24012011080;


DELETE FROM Cars_24012011080 
WHERE car_id = 3;

SELECT * FROM Cars_24012011080;



DELETE FROM Production_24012011080 
WHERE production_id = 1002;

SELECT * FROM Production_24012011080;



SELECT car_id, manufacturer_id, quantity 
FROM Production_24012011080;