Total # = 51

1. What is the most expensive cake and its flavor?

select id, flavor
from goods
where food = "Cake"
order by price desc
limit 1

What is the cheapest cookie and its flavor?

select id, flavor
from goods
where food = "Cookie"
order by price
limit 1

2. Find the ids of goods that have apple flavor.

Select id
From goods
Where flavor = "Apple"

What are the ids of goods that cost less than 3 dollars?

Select id
From goods
Where price < 3


3. List the distinct ids of all customers who bought a lemon cake?

Select DISTINCT T3.CustomerId
From goods as T1 JOIN items as T2 JOIN receipts as T3 ON T1.Id = T2.Item and T2.Receipt = T3.ReceiptNumber
Where T1.Flavor = "Lemon" and T1.Food = "Cake"


For each type of food, tell me how many customers have ever bought it.

Select T1.food, count(distinct T3.CustomerId)
From goods as T1 JOIN items as T2 JOIN receipts as T3 ON T1.Id = T2.Item and T2.Receipt = T3.ReceiptNumber
Group by T1.food


4. Find the id of customers who shopped at the bakery at least 15 times.

Select CustomerId
From receipts
Group by CustomerId
having count(*) >= 15

What is the last name of the customers who shopped at the bakery more than 10 times?

Select T2.LastName
From receipts as T1 JOIN customers as T2 ON T1.CustomerId = T2.id
Group by T2.id
having count(*) > 10


5. How many types of Cake does this bakery sell?

Select count(*)
From goods
where food = "Cake"

List all the flavors of Croissant available in this bakery.

Select flavor
From goods
where food = "Croissant"


6. Give me a list of all the distinct items bought by the customer number 15.

Select distinct T1.item
from items as T1 JOIN receipts as T2 ON T1.receipt = T2.ReceiptNumber 
Where T2.CustomerId = 15


For each type of food, what are the average, maximum and minimum price?

Select food, avg(price), max(price), min(price)
from goods
Group by food


7. Find the receipt numbers where both Cake and Cookie were bought.

Select T1.receipt
From items as T1 JOIN goods as T2 ON T1.item = T2.id
Where T2.food = "Cake"
INTERSECT
Select T1.receipt
From items as T1 JOIN goods as T2 ON T1.item = T2.id
Where T2.food = "Cookie"


Find all the receipt numbers in which customer id 5 purchased Croissant.

Select ReceiptNumber
From receipts
Where CustomerId = 5
INTERSECT
Select T1.receipt
From items as T1 JOIN goods as T2 ON T1.item = T2.id
Where T2.food = "Croissant"


8. What is the receipt number and date of the receipt in which the most expensive item was bought?

Select T1.ReceiptNumber, T1.Date
From receipts as T1 JOIN items as T2 JOIN goods as T3 ON T1.ReceiptNumber = T2.receipt and T2.item = T3.id
Order by T3.price Desc
LIMIT 1


What is the item that was bought the least number of times?
Select item
From items
Group by item
Order by count(*)
LIMIT 1


9. How many goods are available for each food type?

Select count(*), food
From goods
group by food

What is the average price for each food type?

Select avg(price), food
From goods
group by food


10. What are the goods that have Apricot flavor and are cheaper than 5 dollars?

Select id
From goods
where flavor = "Apricot" and price < 5

Find flavor of cakes that cost more than 10 dollars.

Select flavor
From goods
where food = "Cake" and price > 10


11. Give me the distinct id and price for all goods whose price is below the average of all goods?

Select distinct id, price
From goods
where price < (Select avg(price) From goods)
  

What are the distinct ids of all goods that are cheaper than some goods of type Tart?

Select distinct T1.id
From goods as T1, goods as T2
where T1.price < T2.price and T2.food = "Tart"


12.  List distinct receipt numbers for which someone bought a good that costs more than 13 dollars.

Select distinct T1.ReceiptNumber
from receipts as T1 JOIN items as T2 JOIN goods as T3 ON T1.ReceiptNumber = T2.receipt and T2.item = T3.id
Where T3.price > 13


On which date did some customer buy a good that costs more than 15 dollars?

Select distinct T1.date
from receipts as T1 JOIN items as T2 JOIN goods as T3 ON T1.ReceiptNumber = T2.receipt and T2.item = T3.id
Where T3.price > 15


13. Give me the list of all goods whose id has "APP".

Select id
from goods
Where id LIKE "%APP%"

Which good has "70" in its id? And what is its price?

Select id, price
from goods
Where id LIKE "%70%"


14. List the last names of all customers in an alphabetical oder.

Select distinct LastName
From customers
Order by LastName

Return the ordered list of all good ids.

Select distinct id
From goods
Order by id


15. Find all receipts in which either apple flavor pie was bought or customer id 12 shopped.

Select T1.receipt
From items as T1 JOIN goods as T2 ON T1.item = T2.id
where T2.flavor = "Apple" and T2.food = "Pie"
UNION
Select ReceiptNumber
From receipts
where CustomerId = 12


Find all receipts which has the latest date. Also tell me that date.

Select ReceiptNumber, date
From receipts
where date = (Select date From receipts Order by date Desc LIMIT 1)


Find all receipts which either has the earliest date or has a good with price above 10.

Select T1.Receipt
From items as T1 JOIN goods as T2 ON T1.item = T2.id
where T2.price > 10
UNION
Select ReceiptNumber
From receipts
where date = (Select date From receipts Order by date LIMIT 1)


16. What are the ids of Cookie and Cake that cost between 3 and 7 dollars.

Select id
From goods
where (food = "Cookie" or food = "Cake") and price between 3 and 7 


Find the first name and last name of a customer who visited on the earliest date.

Select T1.FirstName, T1.LastName
From customers as T1 JOIN receipts as T2 ON T1.id = T2.CustomerId
Order by T2.date
LIMIT 1



17. What is average price of goods whose flavor is blackberry or blueberry?

Select avg(price)
From goods
where flavor = "Blackberry" or flavor = "Blueberry"


Return the cheapest price for goods with cheese flavor.

Select min(price)
From goods
where flavor = "Cheese"


18. What are highest, lowest, and average prices of goods, grouped and ordered by flavor?

Select max(price), min(price), avg(price), flavor
From goods
group by flavor
order by flavor


Return the lowest and highest prices of goods grouped and ordered by food type.

Select min(price), max(price), food
From goods
group by food
order by food


19. Find the top three dates when the most goods were sold.

Select date
From receipts
Group by date
Order by count(*)
LIMIT 3

Which customer shopped most often? How many times?

Select CustomerId, count(*)
From receipts
Group by CustomerId
Order by count(*)
LIMIT 1


20. For each date, return how many distinct customers visited on that day.

Select date, count (distinct CustomerId)
from receipts
Group by date


Give me the first name and last name of customers who have bought apple flavor Tart.

Select distinct T4.FirstName, T4.LastName
from goods as T1 JOIN items as T2 JOIN receipts as T3 JOIN customers as T4 ON T1.id = T2.item and T2.receipt = T3.ReceiptNumber and T3.CustomerId = T4.id
where T1.flavor = "Apple" and T1.food = "Tart"


21. What are the ids of Cookies whose price is lower than any Croissant?

Select id
From goods
where food = "Cookie" and price <  (select min(price)
                             from goods
                             where food = 'Croissant')


Give me the ids of Cakes whose price is at least three times as much as the average price of Tart?

Select id
From goods
where food = "Cake" and price >= 3 * (select avg(price)
                             from goods
                             where food = "Tart")


What are the ids of goods whose price is above twice the average price of all goods?

Select id
From goods
where price > 2 * (select avg(price)
                             from goods)


22. List the id, flavor and type of food of goods ordered by price.

Select id, flavor, food
From goods
order by price


Return a list of the id and flavor for Cakes ordered by flavor.

Select id, flavor
From goods
where food = "Cake"
order by flavor



23. Find all the items that have chocolate flavor but were not bought more than 10 times. 

Select distinct T1.item
From items as T1 JOIN goods as T2 ON T1.item = T2.id
Where T2.flavor = "Chocolate"
EXCEPT 
Select distinct item
From items
Group by item
having count(*) > 10


What are the flavors available for Cake but not for Tart?

Select distinct flavor
From goods
where food = "Cake"
EXCEPT 
Select distinct flavor
From goods
where food = "Tart"


24. What is the three most popular goods in this bakery?

Select item
From items
Group by item
Order by count (*) DESC
LIMIT 3



25. Find the ids of customers who have spent more than 150 dollars in total.

Select T3.CustomerId
From goods as T1 JOIN items as T2 JOIN receipts as T3 ON T1.id = T2.item and T2.receipt = T3.ReceiptNumber
Group by T3.CustomerId
having sum(T1.price) > 150


Find the ids of customers whose average spending for each good is above 5.

Select T3.CustomerId
From goods as T1 JOIN items as T2 JOIN receipts as T3 ON T1.id = T2.item and T2.receipt = T3.ReceiptNumber
Group by T3.CustomerId
having avg(T1.price) > 5



On which day did the bakery sell more than 100 dollars in total.

Select T3.date
From goods as T1 JOIN items as T2 JOIN receipts as T3 ON T1.id = T2.item and T2.receipt = T3.ReceiptNumber
Group by T3.date
having sum(T1.price) > 100