Database: pets_1
Schema: create table Student (        StuID    	INTEGER PRIMARY KEY,        LName		VARCHAR(12),        Fname		VARCHAR(12),        Age		INTEGER,        Sex		VARCHAR(1),        Major		INTEGER,        Advisor		INTEGER,        city_code	VARCHAR(3) ); create table Has_Pet (        StuID		INTEGER,        PetID		INTEGER,        FOREIGN KEY(PetID) REFERENCES Pets(PetID),        FOREIGN KEY(StuID) REFERENCES Student(StuID) ); create table Pets (        PetID		INTEGER PRIMARY KEY,        PetType		VARCHAR(20),        pet_age INTEGER,        weight REAL );

Question (easy): Find the number of pets whose weight is heavier than 10.
SQL: SELECT count(*) FROM pets WHERE weight  >  10

Question (easy): How many pets have a greater weight than 10?
SQL: SELECT count(*) FROM pets WHERE weight  >  10

Question (medium): Find the weight of the youngest dog.
SQL: SELECT weight FROM pets ORDER BY pet_age LIMIT 1

Question (medium): How much does the youngest dog weigh?
SQL: SELECT weight FROM pets ORDER BY pet_age LIMIT 1

Question (medium): Find the maximum weight for each type of pet. List the maximum weight and pet type.
SQL: SELECT max(weight) ,  petType FROM pets GROUP BY petType

Question (medium): List the maximum weight and type for each type of pet.
SQL: SELECT max(weight) ,  petType FROM pets GROUP BY petType

Question (medium): Find number of pets owned by students who are older than 20.
SQL: SELECT count(*) FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid WHERE T1.age  >  20

Question (medium): How many pets are owned by students that have an age greater than 20?
SQL: SELECT count(*) FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid WHERE T1.age  >  20

Question (hard): Find the number of dog pets that are raised by female students (with sex F).
SQL: SELECT count(*) FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T2.petid  =  T3.petid WHERE T1.sex  =  'F' AND T3.pettype  =  'dog'

Question (hard): How many dog pets are raised by female students?
SQL: SELECT count(*) FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T2.petid  =  T3.petid WHERE T1.sex  =  'F' AND T3.pettype  =  'dog'

Question (easy): Find the number of distinct type of pets.
SQL: SELECT count(DISTINCT pettype) FROM pets

Question (easy): How many different types of pet are there?
SQL: SELECT count(DISTINCT pettype) FROM pets

Question (extra): Find the first name of students who have cat or dog pet.
SQL: SELECT DISTINCT T1.Fname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat' OR T3.pettype  =  'dog'

Question (extra): What are the first names of every student who has a cat or dog as a pet?
SQL: SELECT DISTINCT T1.Fname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat' OR T3.pettype  =  'dog'

Question (extra): Find the first name of students who have both cat and dog pets .
SQL: select t1.fname from student as t1 join has_pet as t2 on t1.stuid  =  t2.stuid join pets as t3 on t3.petid  =  t2.petid where t3.pettype  =  'cat' intersect select t1.fname from student as t1 join has_pet as t2 on t1.stuid  =  t2.stuid join pets as t3 on t3.petid  =  t2.petid where t3.pettype  =  'dog'

Question (extra): What are the students' first names who have both cats and dogs as pets?
SQL: SELECT T1.Fname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat' INTERSECT SELECT T1.Fname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'dog'

Question (extra): Find the major and age of students who do not have a cat pet.
SQL: SELECT major ,  age FROM student WHERE stuid NOT IN (SELECT T1.stuid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat')

Question (extra): What major is every student who does not own a cat as a pet, and also how old are they?
SQL: SELECT major ,  age FROM student WHERE stuid NOT IN (SELECT T1.stuid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat')

Question (hard): Find the id of students who do not have a cat pet.
SQL: SELECT stuid FROM student EXCEPT SELECT T1.stuid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat'

Question (hard): What are the ids of the students who do not own cats as pets?
SQL: SELECT stuid FROM student EXCEPT SELECT T1.stuid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat'

Question (extra): Find the first name and age of students who have a dog but do not have a cat as a pet.
SQL: SELECT T1.fname ,  T1.age FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'dog' AND T1.stuid NOT IN (SELECT T1.stuid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat')

Question (extra): What is the first name of every student who has a dog but does not have a cat?
SQL: SELECT T1.fname ,  T1.age FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'dog' AND T1.stuid NOT IN (SELECT T1.stuid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pettype  =  'cat')

Question (medium): Find the type and weight of the youngest pet.
SQL: SELECT pettype ,  weight FROM pets ORDER BY pet_age LIMIT 1

Question (medium): What type of pet is the youngest animal, and how much does it weigh?
SQL: SELECT pettype ,  weight FROM pets ORDER BY pet_age LIMIT 1

Question (medium): Find the id and weight of all pets whose age is older than 1.
SQL: SELECT petid ,  weight FROM pets WHERE pet_age  >  1

Question (medium): What is the id and weight of every pet who is older than 1?
SQL: SELECT petid ,  weight FROM pets WHERE pet_age  >  1

Question (medium): Find the average and maximum age for each type of pet.
SQL: SELECT avg(pet_age) ,  max(pet_age) ,  pettype FROM pets GROUP BY pettype

Question (medium): What is the average and maximum age for each pet type?
SQL: SELECT avg(pet_age) ,  max(pet_age) ,  pettype FROM pets GROUP BY pettype

Question (medium): Find the average weight for each pet type.
SQL: SELECT avg(weight) ,  pettype FROM pets GROUP BY pettype

Question (medium): What is the average weight for each type of pet?
SQL: SELECT avg(weight) ,  pettype FROM pets GROUP BY pettype

Question (medium): Find the first name and age of students who have a pet.
SQL: SELECT DISTINCT T1.fname ,  T1.age FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid

Question (medium): What are the different first names and ages of the students who do have pets?
SQL: SELECT DISTINCT T1.fname ,  T1.age FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid

Question (medium): Find the id of the pet owned by student whose last name is ‘Smith’.
SQL: SELECT T2.petid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid WHERE T1.Lname  =  'Smith'

Question (medium): What is the id of the pet owned by the student whose last name is 'Smith'?
SQL: SELECT T2.petid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid WHERE T1.Lname  =  'Smith'

Question (medium): Find the number of pets for each student who has any pet and student id.
SQL: SELECT count(*) ,  T1.stuid FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid GROUP BY T1.stuid

Question (medium): For students who have pets , how many pets does each student have ? list their ids instead of names .
SQL: select count(*) ,  t1.stuid from student as t1 join has_pet as t2 on t1.stuid  =  t2.stuid group by t1.stuid

Question (medium): Find the first name and gender of student who have more than one pet.
SQL: SELECT T1.fname ,  T1.sex FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid GROUP BY T1.stuid HAVING count(*)  >  1

Question (medium): What is the first name and gender of the all the students who have more than one pet?
SQL: SELECT T1.fname ,  T1.sex FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid GROUP BY T1.stuid HAVING count(*)  >  1

Question (hard): Find the last name of the student who has a cat that is age 3.
SQL: SELECT T1.lname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pet_age  =  3 AND T3.pettype  =  'cat'

Question (hard): What is the last name of the student who has a cat that is 3 years old?
SQL: SELECT T1.lname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pet_age  =  3 AND T3.pettype  =  'cat'

Question (extra): Find the average age of students who do not have any pet .
SQL: select avg(age) from student where stuid not in (select stuid from has_pet)

Question (extra): What is the average age for all students who do not own any pets ?
SQL: select avg(age) from student where stuid not in (select stuid from has_pet)