Database: pets_1

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: Find the number of pets whose weight is heavier than 10.
SQL: SELECT count(*) FROM pets WHERE weight  >  10

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

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

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

Question: 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: List the maximum weight and type for each type of pet.
SQL: SELECT max(weight) ,  petType FROM pets GROUP BY petType

Question: 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: 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: 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: 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: Find the number of distinct type of pets.
SQL: SELECT count(DISTINCT pettype) FROM pets

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

Question: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: Find the type and weight of the youngest pet.
SQL: SELECT pettype ,  weight FROM pets ORDER BY pet_age LIMIT 1

Question: 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: 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: 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: 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: 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: Find the average weight for each pet type.
SQL: SELECT avg(weight) ,  pettype FROM pets GROUP BY pettype

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

Question: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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)