//Car Shop Management
CREATE TABLE Car (
car_id INT PRIMARY KEY,
brand VARCHAR(255),
model VARCHAR(255),
year INT,
color VARCHAR(50),
price DECIMAL(10, 2)
);
CREATE TABLE Customer (
customer_id INT PRIMARY KEY,
name VARCHAR(255),
contact_number VARCHAR(15),
email VARCHAR(255),
address VARCHAR(255)
);
CREATE TABLE `Order` (
order_id INT PRIMARY KEY,
customer_id INT,
car_id INT,
order_date DATE,
total_amount DECIMAL(10, 2),
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
FOREIGN KEY (car_id) REFERENCES Car(car_id)
);
CREATE TABLE Employee (
employee_id INT PRIMARY KEY,
name VARCHAR(255),
position VARCHAR(255),
contact_number VARCHAR(15),
email VARCHAR(255)
);
CREATE TABLE Transaction (
transaction_id INT PRIMARY KEY,
order_id INT,
transaction_date DATE,
payment_method VARCHAR(50),
amount_paid DECIMAL(10, 2),
FOREIGN KEY (order_id) REFERENCES `Order`(order_id)
);
CREATE TABLE Service (
service_id INT PRIMARY KEY,
service_name VARCHAR(255),
description TEXT,
cost DECIMAL(10, 2),
duration INT
);
CREATE TABLE Supplier (
supplier_id INT PRIMARY KEY,
supplier_name VARCHAR(255),
contact_number VARCHAR(15),
email VARCHAR(255),
address VARCHAR(255)
);
CREATE TABLE Car_Service (
car_id INT,
service_id INT,
date DATE,
PRIMARY KEY (car_id, service_id),
FOREIGN KEY (car_id) REFERENCES Car(car_id),
FOREIGN KEY (service_id) REFERENCES Service(service_id)
);
ALTER TABLE Service
ADD COLUMN supplier_id INT,
ADD FOREIGN KEY (supplier_id) REFERENCES Supplier(supplier_id);
CREATE TABLE Appointment (
appointment_id INT PRIMARY KEY,
customer_id INT,
service_id INT,
appointment_date DATE,
status VARCHAR(50), -- e.g., pending, confirmed
notes TEXT,
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
FOREIGN KEY (service_id) REFERENCES Service(service_id)
);
CREATE TABLE Feedback (
feedback_id INT PRIMARY KEY,
customer_id INT,
order_id INT,
rating INT,
comments TEXT,
feedback_date DATE,
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
FOREIGN KEY (order_id) REFERENCES `Order`(order_id)
);
//insert value
INSERT INTO Car (car_id, brand, model, year, color, price)
VALUES (1, 'Toyota', 'Camry', 2020, 'Blue', 25000.00);
INSERT INTO Customer (customer_id, name, contact_number, email, address)
VALUES (1, 'Omor', '123456789', 'omor@email.com', '123 Main Street');
INSERT INTO `Order` (order_id, customer_id, car_id, order_date, total_amount)
VALUES (1, 1, 1, '2024-01-11', 25000.00);
INSERT INTO Employee (employee_id, name, position, contact_number, email)
VALUES (1, 'Nahid', 'Sales Associate', '987654321', 'nahid@email.com');
INSERT INTO Transaction (transaction_id, order_id, transaction_date, payment_method, amount_paid)
VALUES (1, 1, '2024-01-11', 'Credit Card', 25000.00);
INSERT INTO Service (service_id, service_name, description, cost, duration)
VALUES (1, 'Oil Change', 'Regular oil change service', 50.00, 30);
INSERT INTO Supplier (supplier_id, supplier_name, contact_number, email, address)
VALUES (1, 'Karim Supplier', '555-1234', 'karim@email.com', '456 Supplier Lane');
INSERT INTO Car_Service (car_id, service_id, date)
VALUES (1, 1, '2024-01-11');
INSERT INTO Appointment (appointment_id, customer_id, service_id, appointment_date, status, notes)
VALUES (1, 1, 1, '2024-01-12', 'Pending', 'Please confirm the appointment.');
INSERT INTO Feedback (feedback_id, customer_id, order_id, rating, comments, feedback_date)
VALUES (1, 1, 1, 5, 'Great service!', '2024-01-13');
//add col
ALTER TABLE Car
ADD COLUMN mileage INT;
//where operation
// and operation
//or
SELECT * FROM Car
WHERE brand = 'Toyota' OR model = 'Camry';
// not
SELECT * FROM Car
WHERE NOT color = 'Blue';
//order by
SELECT * FROM Car
ORDER BY price DESC;
//update
//delete
//avg
SELECT AVG(price) AS average_price FROM Car;
//sum
SELECT customer_id, SUM(total_amount) AS total_spent
FROM `Order`
GROUP BY customer_id;
//stirng
SELECT * FROM Customer
WHERE name LIKE 'O%';
//between
//insert value again
INSERT INTO Car (car_id, brand, model, year, color, price)
VALUES (2, 'Honda', 'Accord', 2022, 'Silver', 28000.00),
(3, 'Ford', 'Mustang', 2021, 'Red', 35000.00),
(4, 'Chevrolet', 'Malibu', 2023, 'Black', 30000.00);
INSERT INTO Customer (customer_id, name, contact_number, email, address)
VALUES (2, 'Riyad', '987654321', 'riyad@email.com', '456 Oak Street'),
(3, 'Karim', '555-9876', 'karim@email.com', '789 Maple Avenue'),
(4, 'Nadia', '111-2222', 'nadia@email.com', '101 Pine Lane');
INSERT INTO `Order` (order_id, customer_id, car_id, order_date, total_amount)
VALUES (2, 2, 2, '2024-01-12', 28000.00),
(3, 3, 3, '2024-01-13', 35000.00),
(4, 4, 4, '2024-01-14', 30000.00);
INSERT INTO Employee (employee_id, name, position, contact_number, email)
VALUES (2, 'Sara', 'Service Advisor', '555-5678', 'sara@email.com'),
(3, 'Farhan', 'Mechanic', '777-8888', 'farhan@email.com'),
(4, 'Lina', 'Customer Support', '999-0000', 'lina@email.com');
INSERT INTO Transaction (transaction_id, order_id, transaction_date, payment_method, amount_paid)
VALUES (2, 2, '2024-01-12', 'Debit Card', 28000.00),
(3, 3, '2024-01-13', 'Cash', 35000.00),
(4, 4, '2024-01-14', 'Credit Card', 30000.00);
INSERT INTO Service (service_id, service_name, description, cost, duration)
VALUES (2, 'Brake Inspection', 'Check and adjust brakes', 60.00, 45),
(3, 'Tire Rotation', 'Rotate and balance tires', 40.00, 30),
(4, 'Engine Diagnostic', 'Identify engine issues', 80.00, 60);
INSERT INTO Supplier (supplier_id, supplier_name, contact_number, email, address)
VALUES (2, 'Ali Parts', '555-2222', 'ali@email.com', '789 Supplier Street'),
(3, 'Mia Motors', '777-3333', 'mia@email.com', '1011 Auto Lane'),
(4, 'Raj Auto', '999-4444', 'raj@email.com', '1212 Parts Avenue');
INSERT INTO Car_Service (car_id, service_id, date)
VALUES (2, 2, '2024-01-12'),
(3, 3, '2024-01-13'),
(4, 4, '2024-01-14');
INSERT INTO Appointment (appointment_id, customer_id, service_id, appointment_date, status, notes)
VALUES (2, 2, 2, '2024-01-15', 'Pending', 'Please schedule a tire rotation.'),
(3, 3, 3, '2024-01-16', 'Confirmed', 'Engine diagnostic scheduled.'),
(4, 4, 4, '2024-01-17', 'Pending', 'Brake inspection requested.');
INSERT INTO Feedback (feedback_id, customer_id, order_id, rating, comments, feedback_date)
VALUES (2, 2, 2, 4, 'Good experience with the service.', '2024-01-16'),
(3, 3, 3, 5, 'Excellent service on the engine diagnostic.', '2024-01-17'),
(4, 4, 4, 3, 'Satisfactory brake inspection.', '2024-01-18');
//group by , having
