Exam solution

taxi.sql

--1) 

-----------------------------------------------
createdb kurut_taxi            -- in terminal

# или

CREATE DATABASE kurut_taxi;    -- in psql

-----------------------------------------------
\c kurut_taxi
-----------------------------------------------

CREATE TABLE car (
    id             bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    brand          varchar(50),
    model          varchar(50),
    fuel           varchar(20),
    volume         integer,
    transmission   varchar(20),
    year_of_issue  integer,
    color          varchar(20)
);


CREATE TABLE driver (
    id             bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    first_name     varchar(50),
    last_name      varchar(50),
    birthday       date,
    experience     integer,
    gender         varchar(20),
    car_id         integer UNIQUE REFERENCES car (id)
);


CREATE TABLE operator (
    id             bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    first_name     varchar(50),
    last_name      varchar(50),
    birthday       date,
    gender         varchar(20)
);



--2)

INSERT INTO car (brand, model, fuel, volume, transmission, year_of_issue, color) VALUES
  ('Toyota', 'Camry', 'petrol', 3, 'automatic', 2017, 'white'),
  ('Mercedes', 'G55', 'petrol', 6, 'automatic', 2020, 'black'),
  ('Toyota', 'Camry', 'petrol', 2, 'automatic', 2012, 'yellow'),
  ('Toyota', 'Camry', 'petrol', 4, 'automatic', 2017, 'white'),
  ('Toyota', 'Camry', 'petrol', 3, 'automatic', 2019, 'green'),
  ('Toyota', 'Camry', 'petrol', 2, 'automatic', 2015, 'blue'),
  ('Mercedes', 'E500', 'petrol', 5, 'automatic', 2021, 'black'),
  ('BMW', '5 Series', 'petrol', 5, 'automatic', 2018, 'grey'),
  ('BMW', 'X5', 'diesel', 3, 'automatic', 2021, 'purple'),
  ('Tesla', 'Model S', 'electro', 100, 'automatic', 2021, 'white');




INSERT INTO driver (first_name, last_name, birthday, experience, gender, car_id) VALUES
  ('Azamat', 'Azamatov', '1950-01-01', 30, 'male', 1),
  ('Samat', 'Samatov', '1955-01-01', 25, 'male', 2),
  ('Aliaskar', 'Aliaskarov', '1960-01-01', 20, 'male', 3),
  ('Alina', 'Alinova', '1965-01-01', 15, 'female', 4),
  ('Malika', 'Malikova', '1970-01-01', 15, 'female', 5),
  ('Bermet', 'Bermetova', '1975-01-01', 10, 'female', 6),
  ('Asel', 'Aseleva', '1980-01-01', 10, 'female', 7),
  ('Arman', 'Armanov', '1985-01-01', 9, 'male', 8),
  ('Barat', 'Baratov', '1990-01-01', 5, 'male', 9),
  ('Anna', 'Annova', '1995-01-01', 3, 'female', 10);


INSERT INTO operator (first_name, last_name, birthday, gender) VALUES
  ('Olga', 'Petrova', '1990-01-01', 'female'),
  ('Anara', 'Anarova', '1995-01-01', 'female'),
  ('Olga', 'Ivanova', '1990-01-01', 'female'),
  ('Anara', 'Samatova', '2000-01-01', 'female'),
  ('Gala', 'Galova', '2001-01-01', 'female'),
  ('Gala', 'Aleksova', '1997-01-01', 'female'),
  ('Asel', 'Manasova', '1993-01-01', 'female'),
  ('Asel', 'Nurlanova', '2001-01-01', 'female'),
  ('Asel', 'Bulatova', '1998-01-01', 'female'),
  ('Anara', 'Kayratova', '1995-01-01', 'female');

-----------------------------------------------

--3)

SELECT * FROM car
WHERE brand = 'Toyota' AND model='Camry'
ORDER BY year_of_issue DESC;

--4)

SELECT DISTINCT first_name FROM operator
ORDER BY first_name DESC
LIMIT 10;


--5)

UPDATE car SET brand = 'Mersus'
WHERE brand = 'Mercedes';


--6)

DELETE FROM driver
WHERE first_name = 'Azamat' AND last_name = 'Azamatov';


--7)

SELECT COUNT(*) FROM driver
WHERE experience > 10 AND gender = 'female';


--8)

SELECT AVG(experience) FROM driver
WHERE birthday > '1975-10-10';


--9)

SELECT first_name, last_name, brand, model, year_of_issue FROM driver
JOIN car
ON driver.car_id = car.id;

--10)

SELECT brand, COUNT(*) AS brand_count
FROM car
GROUP BY brand
ORDER BY brand_count DESC;


--11)

SELECT first_name, last_name, count(car_id) AS car_count FROM driver
LEFT JOIN car 
ON driver.car_id = car.id
GROUP BY driver.first_name, driver.last_name
ORDER BY car_count DESC;