DB midterm

task1.sql

DROP TABLE IF EXISTS meal_consumption CASCADE;
DROP TABLE IF EXISTS recipe CASCADE;
DROP TABLE IF EXISTS ingr_count_recipe CASCADE;
DROP TABLE IF EXISTS ingredient CASCADE;

CREATE TABLE ingredient (
  ing_name      TEXT UNIQUE,
  calories      INT,
  proteins      INT,
  fats          INT,
  carbohydrates INT,
  vitamins      INT,
  minerals      INT,
  PRIMARY KEY (ing_name)
);

CREATE TABLE recipe (
  recipe_name TEXT UNIQUE,
  PRIMARY KEY (recipe_name)
);

CREATE TABLE meal_consumption (
  consuption_id       INT,
  time_of_consumption DATE,
  user_full_name      TEXT,
  recipe_name_of_meal TEXT,
  PRIMARY KEY (consuption_id),
  FOREIGN KEY (recipe_name_of_meal) REFERENCES recipe (recipe_name)
);

CREATE TABLE ingr_count_recipe (
  recipe_name    TEXT,
  ing_name       TEXT,
  amount_of_ingr INT NOT NULL,
  FOREIGN KEY (recipe_name) REFERENCES recipe (recipe_name),
  FOREIGN KEY (ing_name) REFERENCES ingredient (ing_name)
);

INSERT INTO ingredient VALUES ('milk', 1, 2, 3, 4, 5, 6);
INSERT INTO ingredient VALUES ('какао', 1, 2, 3, 4, 5, 6);
INSERT INTO ingredient VALUES ('лайм', 1, 2, 3, 4, 5, 6);
INSERT INTO ingredient VALUES ('мука', 1, 2, 3, 4, 5, 6);

INSERT INTO recipe VALUES ('хлеб');

INSERT INTO ingr_count_recipe VALUES ('хлеб', 'мука', 20);
INSERT INTO ingr_count_recipe VALUES ('хлеб', 'milk', 50);

INSERT INTO meal_consumption VALUES (1, to_date('15-09-2017', 'dd-mm-yyyy'), 'Илья гульков', 'хлеб');
INSERT INTO meal_consumption VALUES (2, to_date('15-09-2017', 'dd-mm-yyyy'), 'Илья гульков', 'хлеб');
INSERT INTO meal_consumption VALUES (3, to_date('15-09-2017', 'dd-mm-yyyy'), 'Илья гульков', 'хлеб');
INSERT INTO meal_consumption VALUES (4, to_date('15-09-2017', 'dd-mm-yyyy'), 'Илья гульков', 'хлеб');
INSERT INTO meal_consumption VALUES (5, to_date('15-09-2017', 'dd-mm-yyyy'), 'Илья гульков', 'хлеб');
INSERT INTO meal_consumption VALUES (6, to_date('15-09-2017', 'dd-mm-yyyy'), 'Илья гульков', 'хлеб');

INSERT INTO meal_consumption VALUES (7, to_date('15-09-2017', 'dd-mm-yyyy'), 'Павел Драньков', 'хлеб');
INSERT INTO meal_consumption VALUES (8, to_date('15-09-2017', 'dd-mm-yyyy'), 'Павел Драньков', 'хлеб');
INSERT INTO meal_consumption VALUES (9, to_date('15-09-2017', 'dd-mm-yyyy'), 'Павел Драньков', 'хлеб');
INSERT INTO meal_consumption VALUES (10, to_date('15-09-2017', 'dd-mm-yyyy'), 'Павел Драньков', 'хлеб');
INSERT INTO meal_consumption VALUES (11, to_date('15-09-2017', 'dd-mm-yyyy'), 'Павел Драньков', 'хлеб');
INSERT INTO meal_consumption VALUES (12, to_date('15-09-2017', 'dd-mm-yyyy'), 'Павел Драньков', 'хлеб');


INSERT INTO meal_consumption VALUES (13, to_date('15-09-2017', 'dd-mm-yyyy'), 'Как то зовут меня', 'хлеб');

task2.sql

/* Работает на postgresql 9 */
DROP TABLE IF EXISTS meal_consumption CASCADE;
DROP TABLE IF EXISTS recipe CASCADE;
DROP TABLE IF EXISTS ingr_count_recipe CASCADE;
DROP TABLE IF EXISTS ingredient CASCADE;

CREATE TABLE ingredient (
  ing_name      TEXT UNIQUE,
  calories      INT,
  proteins      INT,
  fats          INT,
  carbohydrates INT,
  vitamins      INT,
  minerals      INT,
  PRIMARY KEY (ing_name)
);

CREATE TABLE recipe (
  recipe_name TEXT UNIQUE,
  PRIMARY KEY (recipe_name)
);

CREATE TABLE meal_consumption (
  consuption_id       INT,
  time_of_consumption DATE,
  user_full_name      TEXT,
  recipe_name_of_meal TEXT,
  PRIMARY KEY (consuption_id),
  FOREIGN KEY (recipe_name_of_meal) REFERENCES recipe (recipe_name)
);

CREATE TABLE ingr_count_recipe (
  recipe_name    TEXT,
  ing_name       TEXT,
  amount_of_ingr INT NOT NULL,
  FOREIGN KEY (recipe_name) REFERENCES recipe (recipe_name),
  FOREIGN KEY (ing_name) REFERENCES ingredient (ing_name)
);

INSERT INTO ingredient VALUES ('milk', 1, 2, 3, 4, 5, 6);
INSERT INTO ingredient VALUES ('какао', 1, 2, 3, 4, 5, 6);
INSERT INTO ingredient VALUES ('лайм', 1, 2, 3, 4, 5, 6);
INSERT INTO ingredient VALUES ('мука', 1, 2, 3, 4, 5, 6);

INSERT INTO recipe VALUES ('хлеб');

INSERT INTO recipe VALUES ('Smoothie1');
INSERT INTO recipe VALUES ('Smoothie2');

INSERT INTO ingr_count_recipe VALUES ('хлеб', 'мука', 21);
INSERT INTO ingr_count_recipe VALUES ('хлеб', 'milk', 55);

INSERT INTO ingr_count_recipe VALUES ('Smoothie1', 'лайм', 20);
INSERT INTO ingr_count_recipe VALUES ('Smoothie1', 'какао', 50);

INSERT INTO ingr_count_recipe VALUES ('Smoothie2', 'milk', 20);
INSERT INTO ingr_count_recipe VALUES ('Smoothie2', 'какао', 50);


INSERT INTO meal_consumption VALUES (1, to_date('15-09-2017', 'dd-mm-yyyy'), 'Илья гульков', 'хлеб');
INSERT INTO meal_consumption VALUES (2, to_date('15-09-2017', 'dd-mm-yyyy'), 'Илья гульков', 'хлеб');
INSERT INTO meal_consumption VALUES (3, to_date('15-09-2017', 'dd-mm-yyyy'), 'Илья гульков', 'хлеб');
INSERT INTO meal_consumption VALUES (4, to_date('15-09-2017', 'dd-mm-yyyy'), 'Илья гульков', 'хлеб');
INSERT INTO meal_consumption VALUES (5, to_date('15-09-2017', 'dd-mm-yyyy'), 'Илья гульков', 'хлеб');
INSERT INTO meal_consumption VALUES (6, to_date('15-09-2017', 'dd-mm-yyyy'), 'Илья гульков', 'хлеб');

INSERT INTO meal_consumption VALUES (7, to_date('15-09-2017', 'dd-mm-yyyy'), 'Павел Драньков', 'хлеб');
INSERT INTO meal_consumption VALUES (8, to_date('15-09-2017', 'dd-mm-yyyy'), 'Павел Драньков', 'хлеб');
INSERT INTO meal_consumption VALUES (9, to_date('15-09-2017', 'dd-mm-yyyy'), 'Павел Драньков', 'хлеб');
INSERT INTO meal_consumption VALUES (10, to_date('15-09-2017', 'dd-mm-yyyy'), 'Павел Драньков', 'хлеб');
INSERT INTO meal_consumption VALUES (11, to_date('15-09-2017', 'dd-mm-yyyy'), 'Павел Драньков', 'хлеб');
INSERT INTO meal_consumption VALUES (12, to_date('15-09-2017', 'dd-mm-yyyy'), 'Павел Драньков', 'хлеб');
INSERT INTO meal_consumption VALUES (13, to_date('15-09-2017', 'dd-mm-yyyy'), 'Как то зовут меня', 'хлеб');

INSERT INTO meal_consumption VALUES (14, to_date('16-09-2017', 'dd-mm-yyyy'), 'Павел Драньков', 'хлеб');

task3.sql

SELECT *
FROM (SELECT
        user_full_name,
        time_of_consumption,
        count(*) AS meal_daily_count
      FROM meal_consumption
      GROUP BY time_of_consumption, user_full_name) AS t
WHERE meal_daily_count > 5;

task4.sql

SELECT ing_name
FROM ingr_count_recipe
WHERE ingr_count_recipe.recipe_name LIKE 'Smoothie%'
GROUP BY ing_name;

task5.sql

SELECT meal_consumption.user_full_name
FROM meal_consumption
  JOIN ingr_count_recipe ON meal_consumption.recipe_name_of_meal = ingr_count_recipe.recipe_name
WHERE ing_name LIKE 'blueberries'
GROUP BY user_full_name;

task6.sql

SELECT
  meal_consumption.user_full_name,
  time_of_consumption,
  sum(carbohydrates)
FROM meal_consumption
  JOIN ingr_count_recipe ON meal_consumption.recipe_name_of_meal = ingr_count_recipe.recipe_name
  JOIN ingredient ON ingr_count_recipe.ing_name = ingredient.ing_name
GROUP BY user_full_name, time_of_consumption;

task7.sql

SELECT
  user1_name,
  user2_name,
  recipe_name_of_meal
FROM
  (SELECT
     user_full_name AS user1_name,
     meal_consumption.recipe_name_of_meal,
     date_trunc('month', time_of_consumption)
   FROM meal_consumption AS t1
     JOIN
     (SELECT
        user_full_name AS user2_name,
        meal_consumption.recipe_name_of_meal,
        date_trunc('month', time_of_consumption)
      FROM meal_consumption) AS t
       ON t1.recipe_name_of_meal = t.recipe_name_of_meal) AS hello
WHERE user1_name != user2_name;