SQL: Conceptual Solutions

creating tables

Write the SQL statement(s) to create each of the requested tables. For each question, the table that your code should produce is shown.

Screen Shot 2019-01-16 at 11.20.10 AM.png

Create a table of all of the restaurants and prices ordered by price.
‘John’s’ | 2
‘Soft Stuff’ | 3
‘Sweet Frog’ | 5
‘Ben & Jerry’s’ | 6
‘Fenton’s’ | 7
‘Dreyer’s’ | 8
‘Talenti’ | 9
‘Breyer’s’ | 10
select restaurant, avg_price
    from Ice_Cream
    order by avg_price
;

Create a table of type and the average rating for each type if average rating is greater than 4.4.
‘gelato’ | 4.6
‘soft serve’ | 5.0
select type, average(rating)
    from Ice_Cream
    group by type
    having average(rating) > 4.4
;

Create a table of type name and cheapest average price for each type.
‘cream’ | 2
‘gelato’ | 7
‘soft serve’ | 3
‘froyo’ | 5
select type, min(avg_price)
    from Ice_Cream
    group by type
;