Build a Menu for Bytes of China

Get Help SQL
,
#1

Hi,

Is it possible that the solutions to Bytes for China, challenge can be shared.

https://www.codecademy.com/paths/full-stack-engineer-career-path/tracks/fscp-designing-relational-databases/modules/fscp-designing-a-database/projects/restaurant-database-project-with-postgresql

It will be helpful - thanks.

#2

Hello @css8331182305 ,

Is there a specific part that you are having problems with?

#3

Hi, all good - no longer require assistance with this issue.

1 Like
#4

@coffeencake apologies, I forgot my manners today. Thanks for trying to help!

1 Like
#5

Just share too, my work

CREATE TABLE restaurant (
  id integer PRIMARY KEY,
  name varchar(20),
  description varchar(100),
  rating decimal,
  telephone char(10),
  hours varchar(100)
);

CREATE TABLE address (
  id integer PRIMARY KEY,
  street_number varchar(10),
  street_name varchar(20),
  city varchar(20),
  state varchar(15),
  google_map_link varchar(50),
  restaurant_id integer REFERENCES restaurant(id)
);

SELECT constraint_name, table_name, column_name
FROM information_schema.key_column_usage
WHERE table_name = 'address';

CREATE TABLE category (
  id char(2) PRIMARY KEY,
  name varchar(20),
  description varchar(200)
);

CREATE TABLE dish (
  id integer PRIMARY KEY,
  name varchar(50),
  description varchar(200),
  hot_and_spicy boolean
);

CREATE TABLE review (
  id integer PRIMARY KEY,
  rating decimal,
  description varchar(100),
  date date,
  restaurant_id integer REFERENCES restaurant(id)
);

SELECT constraint_name, table_name, column_name
FROM information_schema.key_column_usage
WHERE table_name = 'review';

-- cross reference table
CREATE TABLE categories_dishes (
  category_id char(2),
  dish_id integer,
  price money,
  PRIMARY KEY( category_id, dish_id)
);


-- POPULATE TABLE

/* 
 *--------------------------------------------
 Insert values for restaurant
 *--------------------------------------------
 */
INSERT INTO restaurant VALUES (
  1,
  'Bytes of China',
  'Delectable Chinese Cuisine',
  3.9,
  '6175551212',
  'Mon - Fri 9:00 am to 9:00 pm, Weekends 10:00 am to 11:00 pm'
);

/* 
 *--------------------------------------------
 Insert values for address
 *--------------------------------------------
 */
INSERT INTO address VALUES (
  1,
  '2020',
  'Busy Street',
  'Chinatown',
  'MA',
  'http://bit.ly/BytesOfChina',
  1
);

/* 
 *--------------------------------------------
 Insert values for review
 *--------------------------------------------
 */
INSERT INTO review VALUES (
  1,
  5.0,
  'Would love to host another birthday party at Bytes of China!',
  '05-22-2020',
  1
);

INSERT INTO review VALUES (
  2,
  4.5,
  'Other than a small mix-up, I would give it a 5.0!',
  '04-01-2020',
  1
);

INSERT INTO review VALUES (
  3,
  3.9,
  'A reasonable place to eat for lunch, if you are in a rush!',
  '03-15-2020',
  1
);

/* 
 *--------------------------------------------
 Insert values for category
 *--------------------------------------------
 */
INSERT INTO category VALUES (
  'C',
  'Chicken',
  null
);

INSERT INTO category VALUES (
  'LS',
  'Luncheon Specials',
  'Served with Hot and Sour Soup or Egg Drop Soup and Fried or Steamed Rice  between 11:00 am and 3:00 pm from Monday to Friday.'
);

INSERT INTO category VALUES (
  'HS',
  'House Specials',
  null
);

/* 
 *--------------------------------------------
 Insert values for dish
 *--------------------------------------------
 */
INSERT INTO dish VALUES (
  1,
  'Chicken with Broccoli',
  'Diced chicken stir-fried with succulent broccoli florets',
  false
);

INSERT INTO dish VALUES (
  2,
  'Sweet and Sour Chicken',
  'Marinated chicken with tangy sweet and sour sauce together with pineapples and green peppers',
  false
);

INSERT INTO dish VALUES (
  3,
  'Chicken Wings',
  'Finger-licking mouth-watering entree to spice up any lunch or dinner',
  true
);

INSERT INTO dish VALUES (
  4,
  'Beef with Garlic Sauce',
  'Sliced beef steak marinated in garlic sauce for that tangy flavor',
  true
);

INSERT INTO dish VALUES (
  5,
  'Fresh Mushroom with Snow Peapods and Baby Corns',
  'Colorful entree perfect for vegetarians and mushroom lovers',
  false
);

INSERT INTO dish VALUES (
  6,
  'Sesame Chicken',
  'Crispy chunks of chicken flavored with savory sesame sauce',
  false
);

INSERT INTO dish VALUES (
  7,
  'Special Minced Chicken',
  'Marinated chicken breast sauteed with colorful vegetables topped with pine nuts and shredded lettuce.',
  false
);

INSERT INTO dish VALUES (
  8,
  'Hunan Special Half & Half',
  'Shredded beef in Peking sauce and shredded chicken in garlic sauce',
  true
);

/*
 *--------------------------------------------
 Insert valus for cross-reference table, categories_dishes
 *--------------------------------------------
 */
INSERT INTO categories_dishes VALUES (
  'C',
  1,
  6.95
);

INSERT INTO categories_dishes VALUES (
  'C',
  3,
  6.95
);

INSERT INTO categories_dishes VALUES (
  'LS',
  1,
  8.95
);

INSERT INTO categories_dishes VALUES (
  'LS',
  4,
  8.95
);

INSERT INTO categories_dishes VALUES (
  'LS',
  5,
  8.95
);

INSERT INTO categories_dishes VALUES (
  'HS',
  6,
  15.95
);

INSERT INTO categories_dishes VALUES (
  'HS',
  7,
  16.95
);

INSERT INTO categories_dishes VALUES (
  'HS',
  8,
  17.95
);

SELECT restaurant.name AS name,
  address.street_number AS street_number,
  address.street_name AS street_name,
  restaurant.telephone AS telephone
FROM restaurant
INNER JOIN address
  ON restaurant.id = address.id;

SELECT restaurant.name AS name,
  MAX(review.rating) AS Best_rating
FROM restaurant
INNER JOIN review
  ON restaurant.id = review.id
GROUP BY 1;

SELECT dish.name AS dish_name,
  categories_dishes.price AS price,
  category.name AS category
FROM dish
JOIN categories_dishes
  ON dish.id = categories_dishes.dish_id
JOIN category
  ON category.id = categories_dishes.category_id
ORDER BY 1;

SELECT category.name AS category,
  dish.name AS dish_name,
  categories_dishes.price AS price  
FROM dish
JOIN categories_dishes
  ON dish.id = categories_dishes.dish_id
JOIN category
  ON category.id = categories_dishes.category_id
ORDER BY 1;

SELECT dish.hot_and_spicy AS spicy_dish_name,
  category.name AS category,      
  categories_dishes.price AS price  
FROM dish
JOIN categories_dishes
  ON dish.id = categories_dishes.dish_id
JOIN category
  ON category.id = categories_dishes.category_id
WHERE hot_and_spicy = 'true'
ORDER BY 1;

SELECT dish.id AS dish_id,
  COUNT(dish_id) AS dish_count
FROM dish
JOIN categories_dishes
  ON dish.id = categories_dishes.dish_id
GROUP BY dish.id
HAVING COUNT(dish_id) > 1;

SELECT dish.name AS name,
  COUNT(categories_dishes.category_id) AS dish_count
FROM dish, categories_dishes
GROUP BY dish.id, 
  dish.name, 
  categories_dishes.category_id
HAVING COUNT(categories_dishes.category_id) > 1
  AND COUNT(categories_dishes.category_id) = dish.id
;


SELECT restaurant.name AS name,
  MAX(review.rating) AS Best_rating,
  review.description AS description
FROM restaurant
INNER JOIN review
  ON restaurant.id = review.id
GROUP BY 1, 3;