Requêtes restaurant

queries.md

SQL Queries

Import the restaurant.sql file into MySQL.

Create the following queries :

1. Select all restaurant's name

2. Select all menu's title alphabetically

3. Select each dish's name and price, in decreasing price order

4. Select restaurant's name located in the city of Paris

5. Select all menu's title which contains the word "menu"

6. Select cities without duplicates

7. Select the name of the most expensive dish

8. Select all menu's title and the restaurant's name where they are sold

9. Select all dish's name (without duplicate) which are contained by at least one menu

10. Select all dish's name and price with corresponding menu's title

11. Select each dish's name and price, corresponding menu's title and restaurant's name who sells it

12. Select all the restaurants' name which don't sell any menu

13. Select all dish's name not contained in any menu

14. Select each city's name and how many restaurants located in this city

15. Select menu's title and dishes' count for each menu

16. Select the title of the menu with the most dishes

17. Select, for each restaurant, its name and how many menus it sells (some restaurants might not have any menu)

18. Select all menu's title and dishes count, where menus have more than two dishes.

19. Select all restaurant's name which sells at least one menu where dishes' price combined is 30 or more

Hints :

Request 1

SELECT, FROM

Request 2

ORDER BY

Request 3

ORDER BY

Request 4

WHERE

Request 5

LIKE

Request 6

DISTINCT

Request 7

ORDER BY, LIMIT

Request 8

INNER JOIN

Request 9

DISTINCT, INNER JOIN

Request 10

INNER JOIN (x2)

Request 11

INNER JOIN (x3)

Request 12

LEFT JOIN

Request 13

LEFT JOIN

Request 14

COUNT, GROUP BY

Request 15

COUNT, INNER JOIN (x2), GROUP BY

Request 16

CCOUNT, INNER JOIN (x2), GROUP BY, ORDER BY, LIMIT

Request 17

COUNT, LEFT JOIN, GROUP BY

Request 18

COUNT, INNER JOIN, GROUP BY, HAVING

Request 19

DISTINCT, INNER JOIN (x3), GROUP BY, HAVING

restaurant.sql

-- =========================================================
-- PostgreSQL 17 - Script compatible
-- =========================================================

DROP TABLE IF EXISTS menu_dish;
DROP TABLE IF EXISTS menu;
DROP TABLE IF EXISTS dish;
DROP TABLE IF EXISTS restaurant;

CREATE TABLE restaurant (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name varchar(100) NOT NULL,
    city text NOT NULL
);

CREATE TABLE dish (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name varchar(100) NOT NULL,
    price numeric(10,2) NOT NULL
);

CREATE TABLE menu (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title varchar(100) NOT NULL,
    restaurant_id integer NOT NULL,
    CONSTRAINT menu_restaurant_fk
        FOREIGN KEY (restaurant_id)
        REFERENCES restaurant (id)
);

CREATE TABLE menu_dish (
    dish_id integer NOT NULL,
    menu_id integer NOT NULL,
    PRIMARY KEY (dish_id, menu_id),
    CONSTRAINT menu_dish_dish_fk
        FOREIGN KEY (dish_id)
        REFERENCES dish (id),
    CONSTRAINT menu_dish_menu_fk
        FOREIGN KEY (menu_id)
        REFERENCES menu (id)
);

-- =========================================================
-- Insertion des données
-- =========================================================

INSERT INTO dish (id, name, price) VALUES
    (1, 'Tartiflette', 12.00),
    (2, 'Coq au Vin', 14.00),
    (3, 'Pot au feu', 13.50),
    (4, 'Quiche lorraine', 9.00),
    (5, 'Crêpe', 4.50),
    (6, 'Steak tartare', 8.00),
    (7, 'Cassoulet', 16.00),
    (8, 'Huîtres', 22.00),
    (9, 'Gratin dauphinois', 10.00),
    (10, 'Bœuf bourguignon', 15.00),
    (11, 'Biscôme', 5.90),
    (12, 'Brioche', 6.00),
    (13, 'Pain perdu', 4.00),
    (14, 'Panettone', 4.50),
    (15, 'Tarte à la rhubarbe', 5.00),
    (16, 'Macarons', 3.50),
    (17, 'Flan pâtissier', 5.00),
    (18, 'Forêt noire', 8.00),
    (19, 'Tarte Tatin', 6.00),
    (20, 'Merveilleux', 5.50);

INSERT INTO restaurant (id, name, city) VALUES
    (1, 'Guy Savoy', 'Paris'),
    (2, 'Alain Ducasse', 'Paris'),
    (3, 'La Vague d’Or', 'Saint Tropez'),
    (4, 'L’Ambroisie', 'Paris'),
    (5, 'L’Auberge du Vieux Puits', 'Fonjoncouse'),
    (6, 'L’Assiette Champenoise', 'Tinqueux'),
    (7, 'L’Arpège', 'Paris'),
    (8, 'Pavillon Ledoyen', 'Paris'),
    (9, 'Le Pré Catelan', 'Paris'),
    (10, 'La Maison Troisgros', 'Ouches');

INSERT INTO menu (id, title, restaurant_id) VALUES
    (1, 'Le menu cher', 1),
    (2, 'Le menu très cher', 1),
    (3, 'Le menu trop cher', 1),
    (4, 'Le menu unique', 2),
    (5, 'Pastèque et camembert', 3),
    (6, 'Picouli', 3),
    (7, 'Menu midi', 4),
    (8, 'Menu soir', 4),
    (9, 'Brunch de ouf', 5),
    (10, 'Les petits plats', 6),
    (11, 'Menu enfant', 6),
    (12, 'Menu A', 7),
    (13, 'Menu B', 7),
    (14, 'Menu C', 7),
    (15, 'Menu S+', 7),
    (16, 'Mangeons bien', 8),
    (17, 'Mangeons équilibré', 8),
    (18, 'Mangeons peu', 8),
    (19, 'Mangeons beaucoup', 8),
    (20, 'Faim de table', 10);

INSERT INTO menu_dish (dish_id, menu_id) VALUES
    (1, 1),
    (10, 1),
    (2, 2),
    (2, 3),
    (6, 3),
    (12, 3),
    (7, 4),
    (18, 4),
    (1, 5),
    (4, 5),
    (7, 5),
    (16, 5),
    (3, 6),
    (8, 7),
    (16, 7),
    (19, 8),
    (20, 8),
    (7, 9),
    (9, 10),
    (15, 10),
    (3, 12),
    (7, 12),
    (8, 13),
    (14, 13),
    (7, 15),
    (18, 15),
    (2, 16),
    (3, 16),
    (4, 16),
    (11, 16),
    (12, 16),
    (1, 17),
    (4, 18),
    (15, 18),
    (6, 19),
    (8, 19),
    (8, 20),
    (9, 20),
    (10, 20),
    (11, 20);

-- =========================================================
-- Réalignement des séquences identity
-- =========================================================

SELECT setval(
    pg_get_serial_sequence('restaurant', 'id'),
    (SELECT MAX(id) FROM restaurant),
    true
);

SELECT setval(
    pg_get_serial_sequence('dish', 'id'),
    (SELECT MAX(id) FROM dish),
    true
);

SELECT setval(
    pg_get_serial_sequence('menu', 'id'),
    (SELECT MAX(id) FROM menu),
    true
);