коли

CREATE DATABASE CAR_OWNERS
USE CAR_OWNERS

CREATE TABLE PEOPLE
(
PERSON_ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(50) NOT NULL
)

CREATE TABLE CARS
(
CAR_ID INT NOT NULL PRIMARY KEY,
MODEL VARCHAR(40) NOT NULL,
PERSON_ID INT NOT NULL FOREIGN KEY REFERENCES PEOPLE
)

ALTER TABLE CARS
ADD MARK VARCHAR(40) NOT NULL

INSERT INTO PEOPLE(PERSON_ID, NAME)
VALUES(1, 'IVAN IVANOV'),
      (2, 'IVAN ILIEV'),
      (3, 'ANTOAN TODOROV'),
      (4, 'MIRA TONEVA'),
      (5, 'INA STOYANOVA')

INSERT INTO CARS (CAR_ID, MODEL, PERSON_ID, MARK)
VALUES(100, 'GOLF 4', 1, 'VW'),
      (101, 'GOLF 5', 2, 'VW'),
      (102, 'PASSAT', 1, 'VW'),
      (103, 'ARTHEON', 1, 'VW'),
      (104, 'A6', 3, 'AUDI'),
      (105, 'CORROLA', 4, 'TOYOTA')

DELETE FROM CARS
WHERE  CAR_ID = 105

UPDATE PEOPLE
SET NAME = 'TRAYANA GEORGIEVA'
WHERE PERSON_ID = 5

SELECT P.NAME, C.MODEL
FROM CARS C RIGHT JOIN PEOPLE P
ON C.PERSON_ID = P.PERSON_ID

SELECT P.NAME, COUNT(CAR_ID)
FROM CARS C JOIN PEOPLE P
ON P.PERSON_ID = C.PERSON_ID
GROUP BY P.NAME