human
Schema:
device (Device_ID, Device, Carrier, Package_Version, Applications, Software_Platform)
shop (Shop_ID, Shop_Name, Location, Open_Date, Open_Year)
stock (Shop_ID, Device_ID, Quantity)
Please answer with SQL.
assistant
Sure, I understand.
human
Show all the shop names and the location
assistant
SELECT Shop_name , LOCATION FROM shop;
human
Keep those shops which opened in January.
assistant
SELECT Shop_name , LOCATION FROM shop WHERE Open_Date LIKE '%Jan%';
human
Keep the locations shared by shops with open year later than 2012 and shops with open year before 2008.
assistant
SELECT LOCATION FROM shop WHERE Open_Year > 2012 INTERSECT SELECT LOCATION FROM shop WHERE Open_Year < 2008 ;