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: Which states have both owners and professionals living there?
SQL: SELECT state FROM Owners INTERSECT SELECT state FROM Professionals

Question: Find the states where both owners and professionals live.
SQL: SELECT state FROM Owners INTERSECT SELECT state FROM Professionals

Question: What is the average age of the dogs who have gone through any treatments?
SQL: SELECT avg(age) FROM Dogs WHERE dog_id IN ( SELECT dog_id FROM Treatments )

Question: Find the average age of the dogs who went through treatments.
SQL: SELECT avg(age) FROM Dogs WHERE dog_id IN ( SELECT dog_id FROM Treatments )

Question: Which professionals live in the state of Indiana or have done treatment on more than 2 treatments? List his or her id, last name and cell phone.
SQL: SELECT professional_id ,  last_name ,  cell_number FROM Professionals WHERE state  =  'Indiana' UNION SELECT T1.professional_id ,  T1.last_name ,  T1.cell_number FROM Professionals AS T1 JOIN Treatments AS T2 ON T1.professional_id  =  T2.professional_id GROUP BY T1.professional_id HAVING count(*)  >  2

Question: Find the id, last name and cell phone of the professionals who live in the state of Indiana or have performed more than two treatments.
SQL: SELECT professional_id ,  last_name ,  cell_number FROM Professionals WHERE state  =  'Indiana' UNION SELECT T1.professional_id ,  T1.last_name ,  T1.cell_number FROM Professionals AS T1 JOIN Treatments AS T2 ON T1.professional_id  =  T2.professional_id GROUP BY T1.professional_id HAVING count(*)  >  2

Question: Which dogs have not cost their owner more than 1000 for treatment ? List the dog names .
SQL: select name from dogs where dog_id not in ( select dog_id from treatments group by dog_id having sum(cost_of_treatment)  >  1000 )

Question: What are the names of the dogs for which the owner has not spend more than 1000 for treatment ?
SQL: select name from dogs where dog_id not in ( select dog_id from treatments group by dog_id having sum(cost_of_treatment)  >  1000 )

Question: Which first names are used for professionals or owners but are not used as dog names?
SQL: SELECT first_name FROM Professionals UNION SELECT first_name FROM Owners EXCEPT SELECT name FROM Dogs

Question: Find the first names that are used for professionals or owners but are not used as dog names.
SQL: SELECT first_name FROM Professionals UNION SELECT first_name FROM Owners EXCEPT SELECT name FROM Dogs

Question: Which professional did not operate any treatment on dogs? List the professional's id, role and email.
SQL: SELECT professional_id ,  role_code ,  email_address FROM Professionals EXCEPT SELECT T1.professional_id ,  T1.role_code ,  T1.email_address FROM Professionals AS T1 JOIN Treatments AS T2 ON T1.professional_id  =  T2.professional_id

Question: Give me the id, role and email of the professionals who did not perform any treatment on dogs.
SQL: SELECT professional_id ,  role_code ,  email_address FROM Professionals EXCEPT SELECT T1.professional_id ,  T1.role_code ,  T1.email_address FROM Professionals AS T1 JOIN Treatments AS T2 ON T1.professional_id  =  T2.professional_id

Question: Which owner owns the most dogs? List the owner id, first name and last name.
SQL: SELECT T1.owner_id ,  T2.first_name ,  T2.last_name FROM Dogs AS T1 JOIN Owners AS T2 ON T1.owner_id  =  T2.owner_id GROUP BY T1.owner_id ORDER BY count(*) DESC LIMIT 1

Question: Return the owner id, first name and last name of the owner who has the most dogs.
SQL: SELECT T1.owner_id ,  T2.first_name ,  T2.last_name FROM Dogs AS T1 JOIN Owners AS T2 ON T1.owner_id  =  T2.owner_id GROUP BY T1.owner_id ORDER BY count(*) DESC LIMIT 1

Question: Which professionals have done at least two treatments? List the professional's id, role, and first name.
SQL: SELECT T1.professional_id ,  T1.role_code ,  T1.first_name FROM Professionals AS T1 JOIN Treatments AS T2 ON T1.professional_id  =  T2.professional_id GROUP BY T1.professional_id HAVING count(*)  >=  2

Question: What are the id, role, and first name of the professionals who have performed two or more treatments?
SQL: SELECT T1.professional_id ,  T1.role_code ,  T1.first_name FROM Professionals AS T1 JOIN Treatments AS T2 ON T1.professional_id  =  T2.professional_id GROUP BY T1.professional_id HAVING count(*)  >=  2

Question: What is the name of the breed with the most dogs?
SQL: SELECT T1.breed_name FROM Breeds AS T1 JOIN Dogs AS T2 ON T1.breed_code  =  T2.breed_code GROUP BY T1.breed_name ORDER BY count(*) DESC LIMIT 1

Question: Which breed do the most dogs have? Give me the breed name.
SQL: SELECT T1.breed_name FROM Breeds AS T1 JOIN Dogs AS T2 ON T1.breed_code  =  T2.breed_code GROUP BY T1.breed_name ORDER BY count(*) DESC LIMIT 1

Question: Which owner has paid for the most treatments on his or her dogs? List the owner id and last name.
SQL: SELECT T1.owner_id ,  T1.last_name FROM Owners AS T1 JOIN Dogs AS T2 ON T1.owner_id  =  T2.owner_id JOIN Treatments AS T3 ON T2.dog_id  =  T3.dog_id GROUP BY T1.owner_id ORDER BY count(*) DESC LIMIT 1

Question: Tell me the owner id and last name of the owner who spent the most on treatments of his or her dogs.
SQL: SELECT T1.owner_id ,  T1.last_name FROM Owners AS T1 JOIN Dogs AS T2 ON T1.owner_id  =  T2.owner_id JOIN Treatments AS T3 ON T2.dog_id  =  T3.dog_id GROUP BY T1.owner_id ORDER BY count(*) DESC LIMIT 1

Question: What is the description of the treatment type that costs the least money in total?
SQL: SELECT T1.treatment_type_description FROM Treatment_types AS T1 JOIN Treatments AS T2 ON T1.treatment_type_code  =  T2.treatment_type_code GROUP BY T1.treatment_type_code ORDER BY sum(cost_of_treatment) ASC LIMIT 1

Question: Give me the description of the treatment type whose total cost is the lowest.
SQL: SELECT T1.treatment_type_description FROM Treatment_types AS T1 JOIN Treatments AS T2 ON T1.treatment_type_code  =  T2.treatment_type_code GROUP BY T1.treatment_type_code ORDER BY sum(cost_of_treatment) ASC LIMIT 1