Hi,
Is it possible that the solutions to Bytes for China, challenge can be shared.
It will be helpful - thanks.
Hi, all good - no longer require assistance with this issue.
1 Like
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;
6 Likes
May I suggest my alternative solution to step 18 which I believe answers the task as it uses a nested query:
SELECT
restaurant.name,
review.rating as best_rating,
restaurant.description as description
FROM
restaurant
JOIN
review
ON
review.restaurant_id = restaurant.id
WHERE
review.rating = (
SELECT MAX(rating) from review
)
GROUP BY
restaurant.name,
restaurant.description,
review.rating;
I tried both of these possible answers to Q18 and they brought up slightly different results under ‘description’…
One says ‘Delectable Chinese Cuisine’ and the other says ‘Would love to host another birthday party at Bytes of China!’