CREATE TABLE Ref_Payment_Methods (
    payment_method_code TEXT PRIMARY KEY,
    payment_method_description TEXT
);
CREATE TABLE Ref_Service_Types (
    Service_Type_Code TEXT PRIMARY KEY,
    Parent_Service_Type_Code TEXT,
    Service_Type_Description TEXT
);
CREATE TABLE Addresses (
    Address_ID TEXT PRIMARY KEY,
    Line_1 TEXT,
    Line_2 TEXT,
    City_Town TEXT,
    State_County TEXT,
    Other_Details TEXT
);
CREATE TABLE Products (
    Product_ID TEXT PRIMARY KEY,
    Product_Name TEXT,
    Product_Price NUMBER,
    Product_Description TEXT,
    Other_Product_Service_Details TEXT
);
CREATE TABLE Marketing_Regions (
    Marketing_Region_Code TEXT PRIMARY KEY,
    Marketing_Region_Name TEXT,
    Marketing_Region_Descriptrion TEXT,
    Other_Details TEXT
);
CREATE TABLE Clients (
    Client_ID NUMBER PRIMARY KEY,
    Address_ID NUMBER,
    Customer_Email_Address TEXT,
    Customer_Name TEXT,
    Customer_Phone TEXT,
    Other_Details TEXT,
    FOREIGN KEY ("Address_ID") REFERENCES Addresses("Address_ID")
);
CREATE TABLE Drama_Workshop_Groups (
    Workshop_Group_ID NUMBER PRIMARY KEY,
    Address_ID NUMBER,
    Currency_Code TEXT,
    Marketing_Region_Code TEXT,
    Store_Name TEXT,
    Store_Phone TEXT,
    Store_Email_Address TEXT,
    Other_Details TEXT,
    FOREIGN KEY ("Address_ID") REFERENCES Addresses("Address_ID")
);
CREATE TABLE Performers (
    Performer_ID NUMBER PRIMARY KEY,
    Address_ID NUMBER,
    Customer_Name TEXT,
    Customer_Phone TEXT,
    Customer_Email_Address TEXT,
    Other_Details TEXT,
    FOREIGN KEY ("Address_ID") REFERENCES Addresses("Address_ID")
);
CREATE TABLE Customers (
    Customer_ID TEXT PRIMARY KEY,
    Address_ID NUMBER,
    Customer_Name TEXT,
    Customer_Phone TEXT,
    Customer_Email_Address TEXT,
    Other_Details TEXT,
    FOREIGN KEY ("Address_ID") REFERENCES Addresses("Address_ID")
);
CREATE TABLE Stores (
    Store_ID TEXT PRIMARY KEY,
    Address_ID NUMBER,
    Marketing_Region_Code TEXT,
    Store_Name TEXT,
    Store_Phone TEXT,
    Store_Email_Address TEXT,
    Other_Details TEXT,
    FOREIGN KEY ("Marketing_Region_Code") REFERENCES Marketing_Regions("Marketing_Region_Code"),
    FOREIGN KEY ("Address_ID") REFERENCES Addresses("Address_ID")
);
CREATE TABLE Bookings (
    Booking_ID NUMBER PRIMARY KEY,
    Customer_ID NUMBER,
    Workshop_Group_ID TEXT,
    Status_Code TEXT,
    Store_ID NUMBER,
    Order_Date TIME,
    Planned_Delivery_Date TIME,
    Actual_Delivery_Date TIME,
    Other_Order_Details TEXT,
    FOREIGN KEY ("Workshop_Group_ID") REFERENCES Drama_Workshop_Groups("Workshop_Group_ID"),
    FOREIGN KEY ("Customer_ID") REFERENCES Clients("Client_ID")
);
CREATE TABLE Performers_in_Bookings (
    Order_ID NUMBER PRIMARY KEY,
    Performer_ID NUMBER,
    FOREIGN KEY ("Order_ID") REFERENCES Bookings("Booking_ID"),
    FOREIGN KEY ("Performer_ID") REFERENCES Performers("Performer_ID")
);
CREATE TABLE Customer_Orders (
    Order_ID NUMBER PRIMARY KEY,
    Customer_ID NUMBER,
    Store_ID NUMBER,
    Order_Date TIME,
    Planned_Delivery_Date TIME,
    Actual_Delivery_Date TIME,
    Other_Order_Details TEXT,
    FOREIGN KEY ("Store_ID") REFERENCES Stores("Store_ID"),
    FOREIGN KEY ("Customer_ID") REFERENCES Customers("Customer_ID")
);
CREATE TABLE Order_Items (
    Order_Item_ID NUMBER PRIMARY KEY,
    Order_ID NUMBER,
    Product_ID NUMBER,
    Order_Quantity TEXT,
    Other_Item_Details TEXT,
    FOREIGN KEY ("Product_ID") REFERENCES Products("Product_ID"),
    FOREIGN KEY ("Order_ID") REFERENCES Customer_Orders("Order_ID")
);
CREATE TABLE Invoices (
    Invoice_ID NUMBER PRIMARY KEY,
    Order_ID NUMBER,
    payment_method_code TEXT,
    Product_ID NUMBER,
    Order_Quantity TEXT,
    Other_Item_Details TEXT,
    Order_Item_ID NUMBER,
    FOREIGN KEY ("payment_method_code") REFERENCES Ref_Payment_Methods("payment_method_code"),
    FOREIGN KEY ("Order_ID") REFERENCES Bookings("Booking_ID"),
    FOREIGN KEY ("Order_ID") REFERENCES Customer_Orders("Order_ID")
)

Question: Return the the names of the drama workshop groups that are located in Feliciaberg city.
SQL: SELECT T2.Store_Name FROM Addresses AS T1 JOIN Drama_Workshop_Groups AS T2 ON T1.Address_ID  =  T2.Address_ID WHERE T1.City_Town  =  "Feliciaberg"

Question: What are the email addresses of the drama workshop groups with address in Alaska state?
SQL: SELECT T2.Store_Email_Address FROM Addresses AS T1 JOIN Drama_Workshop_Groups AS T2 ON T1.Address_ID  =  T2.Address_ID WHERE T1.State_County  =  "Alaska"

Question: List the email addresses of the drama workshop groups located in Alaska state.
SQL: SELECT T2.Store_Email_Address FROM Addresses AS T1 JOIN Drama_Workshop_Groups AS T2 ON T1.Address_ID  =  T2.Address_ID WHERE T1.State_County  =  "Alaska"

Question: Show all cities along with the number of drama workshop groups in each city.
SQL: SELECT T1.City_Town ,  count(*) FROM Addresses AS T1 JOIN Drama_Workshop_Groups AS T2 ON T1.Address_ID  =  T2.Address_ID GROUP BY T1.City_Town

Question: How many drama workshop groups are there in each city? Return both the city and the count.
SQL: SELECT T1.City_Town ,  count(*) FROM Addresses AS T1 JOIN Drama_Workshop_Groups AS T2 ON T1.Address_ID  =  T2.Address_ID GROUP BY T1.City_Town

Question: What is the marketing region code that has the most drama workshop groups?
SQL: SELECT Marketing_Region_Code FROM Drama_Workshop_Groups GROUP BY Marketing_Region_Code ORDER BY count(*) DESC LIMIT 1

Question: Which marketing region has the most drama workshop groups? Give me the region code.
SQL: SELECT Marketing_Region_Code FROM Drama_Workshop_Groups GROUP BY Marketing_Region_Code ORDER BY count(*) DESC LIMIT 1

Question: Show all cities where at least one customer lives in but no performer lives in.
SQL: SELECT T1.City_Town FROM Addresses AS T1 JOIN Customers AS T2 ON T1.Address_ID  =  T2.Address_ID EXCEPT SELECT T1.City_Town FROM Addresses AS T1 JOIN Performers AS T2 ON T1.Address_ID  =  T2.Address_ID

Question: Which cities have at least one customer but no performer?
SQL: SELECT T1.City_Town FROM Addresses AS T1 JOIN Customers AS T2 ON T1.Address_ID  =  T2.Address_ID EXCEPT SELECT T1.City_Town FROM Addresses AS T1 JOIN Performers AS T2 ON T1.Address_ID  =  T2.Address_ID

Question: What is the most frequent status of bookings?
SQL: SELECT Status_Code FROM BOOKINGS GROUP BY Status_Code ORDER BY count(*) DESC LIMIT 1

Question: Which status code is the most common of all the bookings?
SQL: SELECT Status_Code FROM BOOKINGS GROUP BY Status_Code ORDER BY count(*) DESC LIMIT 1

Question: What are the names of the workshop groups that have bookings with status code "stop"?
SQL: SELECT T2.Store_Name FROM Bookings AS T1 JOIN Drama_Workshop_Groups AS T2 ON T1.Workshop_Group_ID  =  T2.Workshop_Group_ID WHERE T1.Status_Code  =  "stop"

Question: Which workshop groups have bookings with status code "stop"? Give me the names.
SQL: SELECT T2.Store_Name FROM Bookings AS T1 JOIN Drama_Workshop_Groups AS T2 ON T1.Workshop_Group_ID  =  T2.Workshop_Group_ID WHERE T1.Status_Code  =  "stop"

Question: Show the names of all the clients with no booking.
SQL: SELECT Customer_Name FROM Clients EXCEPT SELECT T2.Customer_Name FROM Bookings AS T1 JOIN Clients AS T2 ON T1.Customer_ID  =  T2.Client_ID

Question: What are the names of the clients who do not have any booking?
SQL: SELECT Customer_Name FROM Clients EXCEPT SELECT T2.Customer_Name FROM Bookings AS T1 JOIN Clients AS T2 ON T1.Customer_ID  =  T2.Client_ID

Question: What is the average quantities ordered with payment method code "MasterCard" on invoices?
SQL: SELECT avg(Order_Quantity) FROM Invoices WHERE payment_method_code  =  "MasterCard"

Question: Check the invoices record and compute the average quantities ordered with the payment method "MasterCard".
SQL: SELECT avg(Order_Quantity) FROM Invoices WHERE payment_method_code  =  "MasterCard"