Ma'lumotlar bazasini yaratish, Jadvallar yaratish, Satirlarga ma'lumotlarni kritish, Where, and, or, not, union, joinlar bilan ishlash

Download 1,49 Mb.
Sana	17.11.2023
Hajmi	1,49 Mb.
	#100630

Bog'liq
sql1

Ma'lumotlar bazasini yaratish,
Jadvallar yaratish,
Satirlarga ma'lumotlarni kritish,
Where, and, or, not, union, joinlar bilan ishlash.

CREATE TABLE avtosalon (
id SERIAL PRIMARY KEY,
nomi VARCHAR(255),
manzili VARCHAR(255),
telefon VARCHAR(15),
email VARCHAR(255));

CREATE TABLE mashina (
id SERIAL PRIMARY KEY,
nomi VARCHAR(255),
modeli VARCHAR(255),
yili INTEGER,
narxi DECIMAL(10, 2),
rangi VARCHAR(50),
karobka_turi VARCHAR(50),
avtosalon_id INTEGER REFERENCES avtosalon(id)
);

INSERT INTO avtosalon (nomi, manzili, telefon, email) VALUES
('Avtosalon1', 'Toshkent shahar, Navoi ko''chasi 123', '+998901234567', 'info@avtosalon1.uz'),
('Avtosalon2', 'Samarqand shahar, Alisher Navoi ko''chasi 45', '+998912345678', 'info@avtosalon2.uz'),
('Avtosalon3', 'Buxoro shahar, Amir Temur ko''chasi 67', '+998923456789', 'info@avtosalon3.uz'),
('Avtosalon4', 'Jizzax shahar, Islom Karimov ko''chasi 12', '+998934567890', 'info@avtosalon4.uz'),
('Avtosalon5', 'Farg''ona shahar, Buyuk Ipak yo''li 56', '+998945678901', 'info@avtosalon5.uz'),
('Avtosalon6', 'Andijon shahar, Temur Malik ko''chasi 78', '+998956789012', 'info@avtosalon6.uz'),
('Avtosalon7', 'Namangan shahar, Amirsoy ko''chasi 34', '+998967890123', 'info@avtosalon7.uz'),
('Avtosalon8', 'Navoiy shahar, Karakalpokiston ko''chasi 90', '+998978901234', 'info@avtosalon8.uz'),
('Avtosalon9', 'Xorazm shahar, Turtkul ko''chasi 21', '+998989012345', 'info@avtosalon9.uz'),
('Avtosalon10', 'Qashqadaryo shahar, Kitob ko''chasi 43', '+998900123456', 'info@avtosalon10.uz'),
('Avtosalon11', 'Surxondaryo shahar, Denov ko''chasi 65', '+998911234567', 'info@avtosalon11.uz'),
('Avtosalon12', 'Sirdaryo shahar, Guliston ko''chasi 87', '+998922345678', 'info@avtosalon12.uz'),
('Avtosalon13', 'Karakalpokiston shahar, Nukus ko''chasi 9', '+998933456789', 'info@avtosalon13.uz'),
('Avtosalon14', 'Toshkent viloyat, Yangiyul ko''chasi 32', '+998944567890', 'info@avtosalon14.uz'),
('Avtosalon15', 'Samarqand viloyat, Urgut ko''chasi 54', '+998955678901', 'info@avtosalon15.uz'),
('Avtosalon16', 'Jizzax viloyat, Zaamin ko''chasi 76', '+998966789012', 'info@avtosalon16.uz'),
('Avtosalon17', 'Farg''ona viloyat, Oltiariq ko''chasi 98', '+998977890123', 'info@avtosalon17.uz'),
('Avtosalon18', 'Andijon viloyat, Asaka ko''chasi 10', '+998988901234', 'info@avtosalon18.uz'),
('Avtosalon19', 'Namangan viloyat, Chortoq ko''chasi 32', '+998999012345', 'info@avtosalon19.uz'),
('Avtosalon20', 'Qashqadaryo viloyat, Shakhrisabz ko''chasi 54', '+998900112233', 'info@avtosalon20.uz');

INSERT INTO mashina (nomi, modeli, yili, narxi, rangi, karobka_turi, avtosalon_id) VALUES
('Chevrolet Malibu', '2022', 2022, 30000.00, 'Qora', 'Avtomat', 1),
('Toyota Camry', '2022', 2022, 35000.00, 'Oq', 'Avto', 2),
('Honda Accord', '2022', 2022, 32000.00, 'Kok', 'Avtomat', 3),
('Nissan Altima', '2022', 2022, 31000.00, 'Qizil', 'Avto', 4),
('Ford Fusion', '2022', 2022, 33000.00, 'Yashil', 'Avtomat', 5),
('BMW 5 Series', '2022', 2022, 45000.00, 'Kok', 'Avtomat', 6),
('Mercedes-Benz E-Class', '2022', 2022, 48000.00, 'Oq', 'Avto', 7),
('Audi A6', '2022', 2022, 47000.00, 'Qora', 'Avtomat', 8),
('Lexus ES', '2022', 2022, 42000.00, 'Oq', 'Avtomat', 9),
('Hyundai Sonata', '2022', 2022, 29000.00, 'Yashil', 'Avto', 10),
('Kia Optima', '2022', 2022, 30000.00, 'Qizil', 'Avtomat', 11),
('Mazda6', '2022', 2022, 31000.00, 'Kok', 'Avtomat', 12),
('Volkswagen Passat', '2022', 2022, 32000.00, 'Oq', 'Avto', 13),
('Subaru Legacy', '2022', 2022, 34000.00, 'Yashil', 'Avtomat', 14),
('Infiniti Q50', '2022', 2022, 38000.00, 'Qora', 'Avtomat', 15),
('Acura TLX', '2022', 2022, 37000.00, 'Oq', 'Avtomat', 16),
('Volvo S60', '2022', 2022, 40000.00, 'Qizil', 'Avtomat', 17),
('Tesla Model 3', '2022', 2022, 60000.00, 'Kok', 'Elektr', 18),
('Porsche Panamera', '2022', 2022, 80000.00, 'Qora', 'Avtomat', 19),
('Jaguar XF', '2022', 2022, 75000.00, 'Oq', 'Avtomat', 20);
SELECT * FROM mashina
WHERE narxi > 40000;

SELECT * FROM mashina
WHERE narxi < 40000 and rangi = 'Oq';

SELECT * FROM mashina
WHERE narxi < 40000 and karobka_turi = 'Avto' and avtosalon_id != 2;

SELECT nomi, manzili, telefon, email FROM avtosalon
UNION
SELECT null as nomi, null as manzili, null as telefon, null as email FROM mashina;

SELECT mashina.*, avtosalon.nomi, avtosalon.manzili
FROM mashina
INNER JOIN avtosalon ON mashina.avtosalon_id = avtosalon.id;

SELECT avtosalon.nomi AS avtosalon_nomi, avtosalon.manzili AS avtosalon_manzili, mashina.*
FROM avtosalon
LEFT JOIN mashina ON avtosalon.id = mashina.avtosalon_id;

Download 1,49 Mb.