172DB2

SHOW DATABASES;

CREATE DATABASE autopark;

USE autopark;

CREATE TABLE persons (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
firstname VARCHAR(200) NOT NULL,
lastname VARCHAR(200) NOT NULL,
age INT,
city VARCHAR(100)
);

INSERT INTO persons
VALUES (1, 'Gosho', 'Borisov', 32, 'Kaspichan');

INSERT INTO persons (id, firstname, lastname, age, city)
VALUES (2, 'Stamat', 'Todorov', 22, 'Lesidren');

INSERT INTO persons (firstname, lastname, city)
VALUES ('Slavi', 'Trifonov', 'Pleven');

INSERT INTO persons (firstname, lastname, city)
VALUES ('Ani', 'Vasileva', 'Pleven');


SELECT * FROM persons WHERE city = 'Pleven';

SELECT * FROM persons;

DELETE FROM persons WHERE id = 5;

UPDATE persons SET city = 'Pleven' WHERE id = 2;



CREATE TABLE cars(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
model VARCHAR(100),
maxspeed INT,
personId INT,
FOREIGN KEY (personId) REFERENCES persons(id)
);


INSERT INTO cars
VALUES (1, 'Opel', 200, 1);

INSERT INTO cars
VALUES (2, 'Renault', 180, 1);

INSERT INTO cars (model, maxspeed, personId)
VALUES ('Moskvich', 165, 2);

INSERT INTO cars (model, maxspeed, personId)
VALUES ('Lada', 195, 2);

SELECT * FROM cars;

SELECT cars.id, cars.model, CONCAT(persons.firstname, ' ', persons.lastname) AS carowner
FROM persons, cars
WHERE cars.personId = persons.id;