CREATE TABLE Breeds (
    breed_code TEXT PRIMARY KEY,
    breed_name TEXT
);
CREATE TABLE Charges (
    charge_id NUMBER PRIMARY KEY,
    charge_type TEXT,
    charge_amount NUMBER
);
CREATE TABLE Sizes (
    size_code TEXT PRIMARY KEY,
    size_description TEXT
);
CREATE TABLE Treatment_Types (
    treatment_type_code TEXT PRIMARY KEY,
    treatment_type_description TEXT
);
CREATE TABLE Owners (
    owner_id NUMBER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    street TEXT,
    city TEXT,
    state TEXT,
    zip_code TEXT,
    email_address TEXT,
    home_phone TEXT,
    cell_number TEXT
);
CREATE TABLE Dogs (
    dog_id NUMBER PRIMARY KEY,
    owner_id NUMBER,
    abandoned_yn TEXT,
    breed_code TEXT,
    size_code TEXT,
    name TEXT,
    age TEXT,
    date_of_birth TIME,
    gender TEXT,
    weight TEXT,
    date_arrived TIME,
    date_adopted TIME,
    date_departed TIME,
    FOREIGN KEY ("owner_id") REFERENCES Owners("owner_id"),
    FOREIGN KEY ("owner_id") REFERENCES Owners("owner_id"),
    FOREIGN KEY ("size_code") REFERENCES Sizes("size_code"),
    FOREIGN KEY ("breed_code") REFERENCES Breeds("breed_code")
);
CREATE TABLE Professionals (
    professional_id NUMBER PRIMARY KEY,
    role_code TEXT,
    first_name TEXT,
    street TEXT,
    city TEXT,
    state TEXT,
    zip_code TEXT,
    last_name TEXT,
    email_address TEXT,
    home_phone TEXT,
    cell_number TEXT
);
CREATE TABLE Treatments (
    treatment_id NUMBER PRIMARY KEY,
    dog_id NUMBER,
    professional_id NUMBER,
    treatment_type_code TEXT,
    date_of_treatment TIME,
    cost_of_treatment NUMBER,
    FOREIGN KEY ("dog_id") REFERENCES Dogs("dog_id"),
    FOREIGN KEY ("professional_id") REFERENCES Professionals("professional_id"),
    FOREIGN KEY ("treatment_type_code") REFERENCES Treatment_Types("treatment_type_code")
);

Question: List the arrival date and the departure date for all the dogs.
SQL: SELECT date_arrived ,  date_departed FROM Dogs

Question: How many dogs went through any treatments?
SQL: SELECT count(DISTINCT dog_id) FROM Treatments

Question: Count the number of dogs that went through a treatment.
SQL: SELECT count(DISTINCT dog_id) FROM Treatments

Question: How many professionals have performed any treatment to dogs?
SQL: SELECT count(DISTINCT professional_id) FROM Treatments

Question: Find the number of professionals who have ever treated dogs.
SQL: SELECT count(DISTINCT professional_id) FROM Treatments

Question: Which professionals live in a city containing the substring 'West'? List his or her role, street, city and state.
SQL: SELECT role_code ,  street ,  city ,  state FROM professionals WHERE city LIKE '%West%'

Question: Find the role, street, city and state of the professionals living in a city that contains the substring 'West'.
SQL: SELECT role_code ,  street ,  city ,  state FROM professionals WHERE city LIKE '%West%'

Question: Which owners live in the state whose name contains the substring 'North'? List his first name, last name and email.
SQL: SELECT first_name ,  last_name ,  email_address FROM Owners WHERE state LIKE '%North%'

Question: Return the first name, last name and email of the owners living in a state whose name contains the substring 'North'.
SQL: SELECT first_name ,  last_name ,  email_address FROM Owners WHERE state LIKE '%North%'

Question: How many dogs have an age below the average?
SQL: SELECT count(*) FROM Dogs WHERE age  <  ( SELECT avg(age) FROM Dogs )

Question: Count the number of dogs of an age below the average.
SQL: SELECT count(*) FROM Dogs WHERE age  <  ( SELECT avg(age) FROM Dogs )

Question: How much does the most recent treatment cost?
SQL: SELECT cost_of_treatment FROM Treatments ORDER BY date_of_treatment DESC LIMIT 1

Question: Show me the cost of the most recently performed treatment.
SQL: SELECT cost_of_treatment FROM Treatments ORDER BY date_of_treatment DESC LIMIT 1

Question: How many dogs have not gone through any treatment?
SQL: SELECT count(*) FROM Dogs WHERE dog_id NOT IN ( SELECT dog_id FROM Treatments )

Question: Tell me the number of dogs that have not received any treatment .
SQL: select count(*) from dogs where dog_id not in ( select dog_id from treatments )

Question: How many owners temporarily do not have any dogs?
SQL: SELECT count(*) FROM Owners WHERE owner_id NOT IN ( SELECT owner_id FROM Dogs )

Question: Find the number of owners who do not own any dogs at this moment.
SQL: SELECT count(*) FROM Owners WHERE owner_id NOT IN ( SELECT owner_id FROM Dogs )

Question: How many professionals did not operate any treatment on dogs?
SQL: SELECT count(*) FROM Professionals WHERE professional_id NOT IN ( SELECT professional_id FROM Treatments )

Question: Find the number of professionals who have not treated any dogs.
SQL: SELECT count(*) FROM Professionals WHERE professional_id NOT IN ( SELECT professional_id FROM Treatments )

Question: List the dog name, age and weight of the dogs who have been abandoned? 1 stands for yes, and 0 stands for no.
SQL: SELECT name ,  age ,  weight FROM Dogs WHERE abandoned_yn  =  1

Question: What are the dog name, age and weight of the dogs that were abandoned? Note that 1 stands for yes, and 0 stands for no in the tables.
SQL: SELECT name ,  age ,  weight FROM Dogs WHERE abandoned_yn  =  1

Question: What is the average age of all the dogs?
SQL: SELECT avg(age) FROM Dogs

Question: Compute the average age of all the dogs.
SQL: SELECT avg(age) FROM Dogs

Question: What is the age of the oldest dog?
SQL: SELECT max(age) FROM Dogs

Question: Tell me the age of the oldest dog.
SQL: SELECT max(age) FROM Dogs

Question: How much does each charge type costs? List both charge type and amount.
SQL: SELECT charge_type ,  charge_amount FROM Charges

Question: List each charge type and its amount.
SQL: SELECT charge_type ,  charge_amount FROM Charges

Question: How much does the most expensive charge type costs?
SQL: SELECT max(charge_amount) FROM Charges

Question: What is the charge amount of the most expensive charge type?
SQL: SELECT max(charge_amount) FROM Charges

Question: List the email, cell phone and home phone of all the professionals.
SQL: SELECT email_address ,  cell_number ,  home_phone FROM professionals

Question: What are the email, cell phone and home phone of each professional?
SQL: SELECT email_address ,  cell_number ,  home_phone FROM professionals

Question: What are all the possible breed type and size type combinations?
SQL: SELECT DISTINCT breed_code ,  size_code FROM dogs

Question: Find the distinct breed type and size type combinations for dogs.
SQL: SELECT DISTINCT breed_code ,  size_code FROM dogs

Question: List the first name of all the professionals along with the description of the treatment they have done.
SQL: SELECT DISTINCT T1.first_name ,  T3.treatment_type_description FROM professionals AS T1 JOIN Treatments AS T2 ON T1.professional_id  =  T2.professional_id JOIN Treatment_types AS T3 ON T2.treatment_type_code  =  T3.treatment_type_code

Question: What are each professional's first name and description of the treatment they have performed?
SQL: SELECT DISTINCT T1.first_name ,  T3.treatment_type_description FROM professionals AS T1 JOIN Treatments AS T2 ON T1.professional_id  =  T2.professional_id JOIN Treatment_types AS T3 ON T2.treatment_type_code  =  T3.treatment_type_code