ssmTRW8M

1-- Midterm test (Databases)
2-- PostgreSQL 9.5
3-- Zakharov Sergey
4-- 06.11.2017
5
6DROP TABLE IF EXISTS meals;
7DROP TABLE IF EXISTS recipe_ingredients;
8DROP TABLE IF EXISTS users;
9DROP TABLE IF EXISTS recipes;
10DROP TABLE IF EXISTS ingredients;
11
12-- 2
13CREATE TABLE IF NOT EXISTS users (
14  ID   SERIAL UNIQUE,
15  name VARCHAR(50)
16);
17
18CREATE TABLE IF NOT EXISTS recipes (
19  ID   SERIAL UNIQUE,
20  name VARCHAR(50)
21);
22
23CREATE TABLE IF NOT EXISTS ingredients (
24  ID            SERIAL UNIQUE,
25  name          VARCHAR(50),
26  calories      FLOAT,
27  proteins      FLOAT,
28  fats          FLOAT,
29  minerals      FLOAT,
30  carbohydrates FLOAT
31);
32
33CREATE TABLE IF NOT EXISTS meals (
34  ID        SERIAL UNIQUE,
35  user_id   INT,
36  recipe_id INT,
37  date      DATE,
38  FOREIGN KEY (user_id) REFERENCES users (ID),
39  FOREIGN KEY (recipe_id) REFERENCES recipes (ID)
40);
41
42CREATE TABLE IF NOT EXISTS recipe_ingredients (
43  recipe_id     INT,
44  ingredient_id INT,
45  amount        FLOAT,
46  FOREIGN KEY (recipe_id) REFERENCES users (ID),
47  FOREIGN KEY (ingredient_id) REFERENCES recipes (ID)
48);
49
50-- 3
51WITH user_meals AS (
52    SELECT
53      user_id,
54      count(*) AS user_meals_at_date,
55      date
56    FROM users
57      INNER JOIN meals ON users.ID = meals.user_id
58    GROUP BY date, user_id)
59SELECT
60  name AS user_name,
61  date AS meal_date,
62  user_meals_at_date
63FROM user_meals
64  INNER JOIN users ON users.ID = user_meals.user_id;
65
66-- 4
67SELECT ingredients.name AS ingredient_name
68FROM
69  recipes
70  INNER JOIN recipe_ingredients ON recipes.ID = recipe_ingredients.recipe_id
71  INNER JOIN ingredients ON recipe_ingredients.ingredient_id = ingredients.ID
72WHERE recipes.name LIKE '%Smoothie%';
73
74-- 5
75SELECT DISTINCT users.name AS user_name
76FROM users
77  INNER JOIN meals ON users.ID = meals.user_id
78  INNER JOIN recipes ON meals.recipe_id = recipes.ID
79  INNER JOIN recipe_ingredients ON recipes.ID = recipe_ingredients.recipe_id
80  INNER JOIN ingredients ON recipe_ingredients.ingredient_id = ingredients.ID
81WHERE ingredients.name = 'blueberries' AND strftime('%Y', meals.date) = strftime('%Y', CURRENT_DATE);
82
83-- 6
84WITH user_ingredients_day AS (
85    SELECT
86      user_id,
87      date,
88      sum(carbohydrates) AS sum_of_carbohydrates
89    FROM users
90      INNER JOIN meals ON users.ID = meals.user_id
91      INNER JOIN recipes ON meals.recipe_id = recipes.ID
92      INNER JOIN recipe_ingredients ON recipes.ID = recipe_ingredients.recipe_id
93      INNER JOIN ingredients ON recipe_ingredients.ingredient_id = ingredients.ID
94    GROUP BY date, user_id
95)
96SELECT
97  users.name,
98  date,
99  sum_of_carbohydrates
100FROM user_ingredients_day
101  INNER JOIN users ON user_ingredients_day.user_id = users.ID;
102
103-- 7
104WITH
105    user_recipe_date1 AS (
106      SELECT
107        user_id,
108        users.name   AS user_name,
109        recipe_id,
110        recipes.name AS recipe_name,
111        date
112      FROM users
113        INNER JOIN meals ON users.ID = meals.user_id
114        INNER JOIN recipes ON meals.recipe_id = recipes.ID
115  ),
116    user_recipe_date2 AS (
117      SELECT *
118      FROM user_recipe_date1
119  )
120SELECT
121  user_recipe_date1.user_name AS user1_name,
122  user_recipe_date2.user_name AS user2_name,
123  user_recipe_date1.recipe_name
124FROM user_recipe_date1, user_recipe_date2
125WHERE strftime('%m', user_recipe_date1.date) = strftime('%m', user_recipe_date2.date)
126      AND user_recipe_date1.recipe_id = user_recipe_date2.recipe_id
127      AND user_recipe_date1.user_id != user_recipe_date2.user_id;