CREATE DATABASE auto_transport;
CREATE TABLE cars(
id INT IDENTITY(1,1) PRIMARY KEY,
nomi VARCHAR(50),
yili INT,
narxi INT,
kuzov_turi VARCHAR(50),
rangi VARCHAR(50),
tavsif VARCHAR(255));
CREATE TABLE Ishchilar(
id INT IDENTITY(1,1) PRIMARY KEY,
Ismi VARCHAR(20),
Familyasi VARCHAR(20),
lavozimi VARCHAR(50),
maosh INT,
contact VARCHAR(255));
CREATE TABLE mijozlar(
id INT IDENTITY(1,1) PRIMARY KEY,
Ismi VARCHAR(20),
Familyasi VARCHAR(20),
contact VARCHAR(255),
address VARCHAR(200));
CREATE TABLE ishlab_chiqarish(
id INT IDENTITY(1,1) PRIMARY KEY,
car_id INT,
boshlash_sanasi datetime,
tugash_sanasi datetime,
holati VARCHAR(300),
FOREIGN KEY (car_id) REFERENCES cars(id));
CREATE TABLE buyurtmalar(
id INT IDENTITY(1,1) PRIMARY KEY,
mijoz_id INT,
car_id INT,
miqdor INT,
buyurtma_sanasi datetime,
yetkazish_sanasi datetime,
holati VARCHAR(300),
FOREIGN KEY (mijoz_id) REFERENCES mijozlar(id),
FOREIGN KEY (car_id) REFERENCES cars(id));
CREATE TABLE sotuvlar(
id INT IDENTITY(1,1) PRIMARY KEY,
car_id INT,
buyurtma_id INT,
umumiy_miqdor INT,
narxi INT,
sotuv_sanasi DATETIME,
FOREIGN KEY (car_id) REFERENCES cars(id),
FOREIGN KEY (buyurtma_id) REFERENCES buyurtmalar(id));
INSERT INTO cars (nomi, yili, narxi, kuzov_turi, rangi, tavsif)
VALUES
('Chevrolet Spark', 2020, 9000, 'Hatchback', 'Qora', 'Kichik va tejamkor shahar avtomobili.'),
('Hyundai Sonata', 2019, 20000, 'Sedan', 'Oq', 'Oila uchun qulay sedan.'),
('Toyota Camry', 2018, 22000, 'Sedan', 'Kumush', 'Ishonchli va samarali.'),
('BMW X5', 2021, 50000, 'SUV', 'Qora', 'Luks va kuchli.'),
('Mercedes-Benz C-Class', 2020, 40000, 'Sedan', 'Kok', 'Zamonaviy va qulay.'),
('Audi Q7', 2019, 55000, 'SUV', 'Oq', 'Oilaviy yol tanlamas.'),
('Ford Mustang', 2021, 35000, 'Coupe', 'Qizil', 'Sport korinish va kuchli dvigatel.'),
('Lexus RX', 2020, 45000, 'SUV', 'Kumush', 'Yuqori darajadagi qulaylik.'),
('Honda Civic', 2018, 18000, 'Sedan', 'Kok', 'Ishonchli va samarali.'),
('Nissan Altima', 2019, 19000, 'Sedan', 'Oq', 'Qulay va tejamkor.'),
('Mazda CX-5', 2021, 32000, 'SUV', 'Qizil', 'Sport va uslubiy yoltanlamas.'),
('Tesla Model 3', 2020, 38000, 'Sedan', 'Oq', 'Elektr va zamonaviy.'),
('Volvo XC90', 2018, 47000, 'SUV', 'Qora', 'Xavfsiz va qulay oilaviy avtomobil.'),
('Chevrolet Malibu', 2019, 23000, 'Sedan', 'Oq', 'Yuqori darajada jihozlangan sedan.'),
('Kia Soul', 2021, 20000, 'Hatchback', 'Yashil', 'Yoshlar uchun qulay shahar avtomobili.'),
('Jeep Wrangler', 2020, 40000, 'SUV', 'Qora', 'Sarguzashtlarga mos yol tanlamas.'),
('Volkswagen Golf', 2018, 21000, 'Hatchback', 'Kok', 'Ishonchli va tejamkor.'),
('Subaru Outback', 2019, 30000, 'SUV', 'Kumush', 'Oilaviy va sarguzasht avtomobili.'),
('Porsche Cayenne', 2021, 70000, 'SUV', 'Qizil', 'Luks va kuchli yoltanlamas.'),
('Ferrari Roma', 2020, 200000, 'Coupe', 'Qizil', 'Eng yuqori darajadagi sport avtomobil.');
INSERT INTO Ishchilar (Ismi, Familyasi, lavozimi, maosh, contact)
VALUES
('Ali', 'Valiyev', 'Dasturchi', 1200, 'ali.valiyev@example.com'),
('Olim', 'Karimov', 'Boshqaruvchi', 1500, 'olim.karimov@example.com'),
('Gulnora', 'Shakarova', 'Buxgalter', 1100, 'gulnora.shakarova@example.com'),
('Javohir', 'Mahmudov', 'Loyihachi', 1300, 'javohir.mahmudov@example.com'),
('Shirin', 'Bekmuratova', 'HR menejeri', 1000, 'shirin.bekmuratova@example.com'),
('Asad', 'Aliyev', 'Sotuv menejeri', 1400, 'asad.aliyev@example.com'),
('Gulnoza', 'Abdullayeva', 'Marketing mutaxassisi', 1250, 'gulnoza.abdullayeva@example.com'),
('Jasur', 'Rashidov', 'Tizim administratori', 1350, 'jasur.rashidov@example.com'),
('Shaxzoda', 'Tursunova', 'Loyiha menejeri', 1300, 'shaxzoda.tursunova@example.com'),
('Nodir', 'Umarov', 'Texnik yordamsi', 950, 'nodir.umarov@example.com'),
('Said', 'Kamilov', 'Dizayner', 1150, 'said.kamilov@example.com'),
('Malika', 'Xudoyberdiyeva', 'Analitik', 1250, 'malika.xudoyberdiyeva@example.com'),
('Rustam', 'Xusanov', 'Biznes analitik', 1400, 'rustam.xusanov@example.com'),
('Madina', 'Yusupova', 'Dasturchi', 1200, 'madina.yusupova@example.com'),
('Timur', 'Sobirov', 'Sotuv vakili', 1100, 'timur.sobirov@example.com'),
('Lola', 'Nabieva', 'Qabulxona yordamchisi', 900, 'lola.nabieva@example.com'),
('Orif', 'Mirzaev', 'Texnik muhandis', 1300, 'orif.mirzaev@example.com'),
('Dilshod', 'Islomov', 'Loyihachi', 1250, 'dilshod.islomov@example.com'),
('Zarina', 'Ahmedova', 'HR mutaxassisi', 1050, 'zarina.ahmedova@example.com'),
('Kamila', 'Olimova', 'Yordamchi', 950, 'kamila.olimova@example.com');
INSERT INTO mijozlar (Ismi, Familyasi, contact, address)
VALUES
('Ali', 'Valiyev', 'ali.valiyev@example.com', 'Toshkent, Chilonzor tumani, 45-uy'),
('Olim', 'Karimov', 'olim.karimov@example.com', 'Samarqand, Amir Temur kochasi, 12-uy'),
('Gulnora', 'Shakarova', 'gulnora.shakarova@example.com', 'Buxoro, Navoiy kochasi, 56-uy'),
('Javohir', 'Mahmudov', 'javohir.mahmudov@example.com', 'Namangan, Bobur kochasi, 23-uy'),
('Shirin', 'Bekmuratova', 'shirin.bekmuratova@example.com', 'Fargona, Mustaqillik kochasi, 19-uy'),
('Asad', 'Aliyev', 'asad.aliyev@example.com', 'Andijon, Sohibqiron kochasi, 33-uy'),
('Gulnoza', 'Abdullayeva', 'gulnoza.abdullayeva@example.com', 'Toshkent, Uchtepa tumani, 67-uy'),
('Jasur', 'Rashidov', 'jasur.rashidov@example.com', 'Qarshi, Xonobod kochasi, 15-uy'),
('Shaxzoda', 'Tursunova', 'shaxzoda.tursunova@example.com', 'Jizzax, Yangi shahar kochasi, 27-uy'),
('Nodir', 'Umarov', 'nodir.umarov@example.com', 'Toshkent, Yunusobod tumani, 55-uy'),
('Said', 'Kamilov', 'said.kamilov@example.com', 'Urganch, Mustaqillik kochasi, 10-uy'),
('Malika', 'Xudoyberdiyeva', 'malika.xudoyberdiyeva@example.com', 'Navoiy, Navbahor kochasi, 5-uy'),
('Rustam', 'Xusanov', 'rustam.xusanov@example.com', 'Xiva, Ichan-qala kochasi, 8-uy'),
('Madina', 'Yusupova', 'madina.yusupova@example.com', 'Margilon, Shohimardon kochasi, 44-uy'),
('Timur', 'Sobirov', 'timur.sobirov@example.com', 'Termiz, Alpomish kochasi, 13-uy'),
('Lola', 'Nabieva', 'lola.nabieva@example.com', 'Guliston, Gagarin kochasi, 28-uy'),
('Orif', 'Mirzaev', 'orif.mirzaev@example.com', 'Toshkent, Mirzo Ulugbek tumani, 71-uy'),
('Dilshod', 'Islomov', 'dilshod.islomov@example.com', 'Buxoro, Buxoro kochasi, 2-uy'),
('Zarina', 'Ahmedova', 'zarina.ahmedova@example.com', 'Qoqon, Xonobod kochasi, 17-uy'),
('Kamila', 'Olimova', 'kamila.olimova@example.com', 'Toshkent, Shayxontohur tumani, 21-uy');
INSERT INTO ishlab_chiqarish (car_id, boshlash_sanasi, tugash_sanasi, holati)
VALUES
(1, '2023-01-01', '2023-02-01', 'bajarildi'),
(2, '2023-01-15', '2024-03-01', 'kutilmoqda'),
(3, '2023-02-01', '2024-04-01', 'buyurtma berildi'),
(4, '2023-03-01', '2023-05-01', 'bajarildi'),
(5, '2023-04-01', '2024-06-01', 'kutilmoqda'),
(6, '2023-05-01', '2024-07-01', 'buyurtma berildi'),
(7, '2023-06-01', '2023-08-01', 'bajarildi'),
(8, '2023-07-01', '2024-09-01', 'kutilmoqda'),
(9, '2023-08-01', '2024-10-01', 'buyurtma berildi'),
(10, '2023-09-01', '2023-11-01', 'bajarildi'),
(11, '2023-10-01', '2024-12-01', 'kutilmoqda'),
(12, '2023-11-01', '2025-01-01', 'buyurtma berildi'),
(13, '2023-12-01', '2024-02-01', 'bajarildi'),
(14, '2024-01-01', '2024-03-01', 'kutilmoqda'),
(15, '2024-02-01', '2025-04-01', 'buyurtma berildi'),
(16, '2024-03-01', '2024-05-01', 'bajarildi'),
(17, '2024-04-01', '2025-06-01', 'kutilmoqda'),
(18, '2024-05-01', '2025-07-01', 'buyurtma berildi'),
(19, '2024-06-01', '2024-08-01', 'bajarildi'),
(20, '2024-07-01', '2025-09-01', 'kutilmoqda');
INSERT INTO buyurtmalar (mijoz_id, car_id, miqdor, buyurtma_sanasi, yetkazish_sanasi, holati)
VALUES
(1, 1, 1, '2023-01-01', '2023-01-10', 'tayyor'),
(2, 2, 2, '2023-01-15', '2023-01-25', 'tayyor'),
(3, 3, 1, '2023-02-01', '2023-02-10', 'kutilmoqda'),
(4, 4, 2, '2023-03-01', '2023-03-10', 'tayyor'),
(5, 5, 3, '2023-04-01', '2023-04-10', 'kutilmoqda'),
(6, 6, 1, '2023-05-01', '2023-05-10', 'tayyor'),
(7, 7, 2, '2023-06-01', '2023-06-10', 'kutilmoqda'),
(8, 8, 3, '2023-07-01', '2023-07-10', 'tayyor'),
(9, 9, 1, '2023-08-01', '2023-08-10', 'kutilmoqda'),
(10, 10, 2, '2023-09-01', '2023-09-10', 'tayyor'),
(11, 11, 3, '2023-10-01', '2023-10-10', 'kutilmoqda'),
(12, 12, 1, '2023-11-01', '2023-11-10', 'tayyor'),
(13, 13, 2, '2023-12-01', '2023-12-10', 'kutilmoqda'),
(14, 14, 3, '2024-01-01', '2024-01-10', 'tayyor'),
(15, 15, 1, '2024-02-01', '2024-02-10', 'kutilmoqda'),
(16, 16, 2, '2024-03-01', '2024-03-10', 'tayyor'),
(17, 17, 3, '2024-04-01', '2024-04-10', 'kutilmoqda'),
(18, 18, 1, '2024-05-01', '2024-05-10', 'tayyor'),
(19, 19, 2, '2024-06-01', '2024-06-10', 'kutilmoqda'),
(20, 20, 3, '2024-07-01', '2024-07-10', 'tayyor');
INSERT INTO sotuvlar (car_id, buyurtma_id, umumiy_miqdor, narxi, sotuv_sanasi)
VALUES
(1, 1, 1, 20000, '2023-01-11'),
(2, 2, 2, 25000, '2023-01-26'),
(3, 3, 1, 30000, '2023-02-11'),
(4, 4, 2, 35000, '2023-03-11'),
(5, 5, 3, 40000, '2023-04-11'),
(6, 6, 1, 45000, '2023-05-11'),
(7, 7, 2, 50000, '2023-06-11'),
(8, 8, 3, 55000, '2023-07-11'),
(9, 9, 1, 60000, '2023-08-11'),
(10, 10, 2, 65000, '2023-09-11'),
(11, 11, 3, 70000, '2023-10-11'),
(12, 12, 1, 75000, '2023-11-11'),
(13, 13, 2, 80000, '2023-12-11'),
(14, 14, 3, 85000, '2024-01-11'),
(15, 15, 1, 90000, '2024-02-11'),
(16, 16, 2, 95000, '2024-03-11'),
(17, 17, 3, 100000, '2024-04-11'),
(18, 18, 1, 105000, '2024-05-11'),
(19, 19, 2, 110000, '2024-06-11'),
(20, 20, 3, 115000, '2024-07-11');
UPDATE cars
SET nomi = 'Malibu2', narxi = 25000
WHERE id = 1;
UPDATE cars
SET nomi = 'Nexia 2', narxi = 7000
WHERE id = 2;
UPDATE Ishchilar
SET Ismi = 'Sardor', maosh = 5000
WHERE id = 1;
UPDATE Ishchilar
SET Ismi = 'Mohira', maosh = 6000
WHERE id = 14;
UPDATE mijozlar
SET Ismi = 'Asadbek', address = 'Sebzor 13'
WHERE id = 10;
UPDATE mijozlar
SET Familyasi = 'Rashidjonov', address = 'Bogishamol 7'
WHERE id = 15;
UPDATE ishlab_chiqarish
SET tugash_sanasi = '2024-12-31'
WHERE id = 1;
SELECT nomi, narxi, buyurtma_sanasi, yetkazish_sanasi
FROM cars LEFT JOIN buyurtmalar ON cars.id=buyurtmalar.car_id;
SELECT b.id AS buyurtma_id,
b.buyurtma_sanasi,
b.yetkazish_sanasi,
b.holati,
b.miqdor,
m.Ismi AS mijoz_ismi,
m.Familyasi AS mijoz_familyasi,
m.contact AS mijoz_contact,
m.address AS mijoz_address,
c.nomi AS car_nomi,
c.yili AS car_yili,
c.narxi AS car_narxi,
c.kuzov_turi AS car_kuzov_turi,
c.rangi AS car_rangi,
c.tavsif AS car_tavsif
FROM buyurtmalar b
INNER JOIN mijozlar m ON b.mijoz_id = m.id
INNER JOIN cars c ON b.car_id = c.id;