Skip to content

Instantly share code, notes, and snippets.

@pythoneast

pythoneast/taxi.sql

Created February 16, 2021 06:58
Download ZIP

Revisions

  1. pythoneast created this gist Feb 16, 2021.
    148 changes: 148 additions & 0 deletions taxi.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,148 @@
    --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;