human
Schema:
shop (Shop_ID, Address, Num_of_staff, Score, Open_Year)
member (Member_ID, Name, Membership_card, Age, Time_of_purchase, Level_of_membership, Address)
happy_hour (HH_ID, Shop_ID, Month, Num_of_shaff_in_charge)
happy_hour_member (HH_ID, Member_ID, Total_amount)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the average score and average staff number of all shops?
assistant
SELECT AVG ( num_of_staff ) , AVG ( score ) FROM shop;
human
Which shop has the highest score?
assistant
SELECT * FROM shop ORDER by score desc LIMIT 1;
human
How many shops have more staff than shop 10?
assistant
SELECT COUNT( * ) FROM shop WHERE num_of_staff > ( SELECT num_of_staff FROM shop WHERE shop_id = 10 );
human
And are there any other shops on the same street? | What street are you referring to? | The street shop 10 is on.
assistant
SELECT * FROM shop WHERE address = ( SELECT address FROM shop WHERE shop_id = 10 ) AND shop_id ! = 10 ;