RANDOM data – Timur Bashirov Portfolio

https://www.mockaroo.com

insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (1, 'Noland', 'Dalinder', 'ndalinder0@reuters.com', 'Mitsubishi', 'Endeavor', 2005, 9423.2);
insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (2, 'Itch', 'Tute', 'itute1@barnesandnoble.com', 'Chevrolet', 'Astro', 1993, 4079.69);
insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (3, 'Lindsy', 'Drawmer', 'ldrawmer2@yahoo.co.jp', 'Ford', 'E150', 2003, 5489.62);
insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (4, 'Hans', 'Gallaccio', 'hgallaccio3@altervista.org', 'Mazda', 'B-Series', 1991, 3735.59);
insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (5, 'Wallie', 'Dulson', 'wdulson4@jugem.jp', 'Suzuki', 'Samurai', 1994, 7118.26);
insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (6, 'Giacopo', 'McGeady', 'gmcgeady5@miibeian.gov.cn', 'Honda', 'Prelude', 1984, 3132.9);
insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (7, 'Elisabeth', 'Syer', 'esyer6@weibo.com', 'Ford', 'Econoline E250', 1993, 6299.49);
insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (8, 'Armando', 'Coull', 'acoull7@wiley.com', 'MINI', 'Clubman', 2008, 2610.32);
insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (9, 'Martica', 'Jurca', 'mjurca8@zdnet.com', 'Jeep', 'Wrangler', 2011, 3725.84);
insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (10, 'Iggy', 'Corrison', 'icorrison9@gravatar.com', 'Bentley', 'Continental', 2009, 8020.48);

insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (101, 'Timur', 'Basirov', 'atimur.basirov@gmail.com', 'Nissan', 'GT-R', 2024, 9999); 

insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (102, 'Martin', 'Sild', 'Martinsild@gmail.com', 'Tata Motors', 'Tata Nano', 2008, 1691); 

insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (103, 'Vlad', 'Kudrjashev', 'VladosKurjashov@gmail.com', 'Fendt', '800 Vario', 2023, 9998);

SELECT, UPDATE, DELETE lausete kasutamine

UPDATE kliendiAuto
SET car_make='Zaporozec'
WHERE id=101 OR id=102 OR id=103;

SELECT * FROM kliendiauto
WHERE id=101 OR id=102 OR id=103;

SELECT * FROM kliendiauto
WHERE car_make LIKE 'cadillac';

UPDATE kliendiauto
set car_make='Citroën'
WHERE car_make LIKE 'cadillac';

SELECT * FROM kliendiauto
WHERE car_make='Cadillac';

DELETE FROM KliendiAuto
WHERE id = 5 AND 10 AND 15;

SELECT ID, last_name, car_make, car_year
FROM KliendiAuto
ORDER BY ID;

SELECT last_name, car_make, car_year
FROM KliendiAuto
WHERE last_name LIKE 'B%'
ORDER BY car_year desc
LIMIT 10;

SELECT last_name, car_make, car_price
FROM kliendiauto 
WHERE car_make in ('BMW','Lexus') and car_price > 57767;

–keskmine autode hind ja summarne autode hind

SELECT AVG(car_price) AS 'average price', sum(car_price) AS 'sum price'
FROM kliendiauto;

create table muuk (
	muugi_id INT PRIMARY KEY,
	kliendi_id INT,
	kuupaev DATE,
	kliendi_email VARCHAR(50),
	muugihind INT,
	FOREIGN KEY (kliendi_id) REFERENCES kliendiAuto(id)
);

insert into muuk (muugi_id, kliendi_id, kuupaev, kliendi_email, muugihind) values (1, 1, '2023-10-25', 'seake0@scribd.com', 9743);
--+

Access

SELECT, UPDATE, DELETE lausete kasutamine

Kuna mul ei ole Cadillaci, siis lisan uue kasutaja.

insert into kliendiAuto (id, first_name, last_name, email, car_make, car_model, car_year, car_price) values (14, 'test', 'test', 'test@gmail.com', 'Cadillac', 'test', 2024, 9999);

UPDATE kliendiauto
set car_make='Citroën'
WHERE car_make LIKE 'cadillac';

DELETE FROM KliendiAuto
WHERE id = 5 AND 10 AND 15;

SELECT ID, last_name, car_make, car_year
FROM KliendiAuto
ORDER BY ID;

SELECT last_name, car_make, car_year
FROM KliendiAuto
WHERE last_name LIKE 'B%'
ORDER BY car_year desc;

SELECT last_name, car_make, car_price
FROM kliendiauto 
WHERE car_make in ('BMW','Lexus') and car_price > 57767;

–keskmine autode hind ja summarne autode hind

SELECT AVG(car_price) AS 'average price', sum(car_price) AS 'sum price'
FROM kliendiauto;

create table muuk (
	muugi_id INT PRIMARY KEY,
	kliendi_id INT,
	kuupaev DATE,
	kliendi_email VARCHAR(50),
	muugihind INT,
	FOREIGN KEY (kliendi_id) REFERENCES kliendiAuto(id)
);

insert into muuk (muugi_id, kliendi_id, kuupaev, kliendi_email, muugihind) values (1, 1, ‘2023-10-25’, ‘seake0@scribd.com’, 9743);
+random values