1.
select name, life_span, age from animal a, main_category m, category c
where 
a.main_category_id = m.main_category_id and
m.category_id = c.category_id and
category_name = 'Vulnerable'

2.
select name, life_span, age from animal a, animal_details d, city c, state s
where 
a.animal_id = d.animal_id and
d.city_id = c.city_id and
d.state_id = s.state_id and
state_name = 'Gujarat'

3.
select name, life_span, age from animal a, animal_details d, city c
where 
a.animal_id = d.animal_id and
d.city_id = c.city_id and
city_name = 'Ahmedabad'

4.
select name, life_span, age from animal a, main_category m, category c
where 
a.main_category_id = m.main_category_id and
m.category_id = c.category_id and
category_name = 'Least Concern'

5.
select name, life_span, age from animal
where 
life_span > 30 and life_span <40;

6.
select name from animal where
weight > 100 and weight < 200 and
name like 'A'

7.
select name, sci_name from animal
where 
color = 'Yellow' and 
sci_name like 'C%';

8.
select name from animal
order by name desc

9.
select name, life_span, age, gender from animal where
life_span > 50 and gender = 'Male'

10.
select name, from animal a, main_category m, sub_category s
where
a.main_category_id = m.main_category_id and
m.sub_category_id = s.sub_category_id and 
s.sub_category_name = 'Birds'

11.
select name from animal a, main_category m, sub_category s
where
a.main_category_id = m.main_category_id and
m.sub_category_id = s.sub_category_id and 
s.sub_category_name = 'Mammals' and color = 'Violet'

12.
select count(*) from animal a, main_category m, sub_category s
where
a.main_category_id = m.main_category_id and
m.sub_category_id = s.sub_category_id and 
s.sub_category_name = 'Fishes' and name like 'M%'

13.
select count(*), s.sub_category_name from animal a, main_category m, sub_category s
where
a.main_category_id = m.main_category_id and
m.sub_category_id = s.sub_category_id
group by s.sub_category_name

14.
select sum(t1.total) as TOTAL from(
select count(*) AS total, s.sub_category_name from animal a, main_category m, sub_category s
where
a.main_category_id = m.main_category_id and
m.sub_category_id = s.sub_category_id and
(s.sub_category_name = 'Birds' or s.sub_category_name = 'Mammals' )
group by s.sub_category_name
)
as t1

15.
select count(*), c.category_name from animal a, main_category m, category c
where
a.main_category_id = m.main_category_id and
m.sub_category_id = c.category_id
group by c.category_name

16.
select c.category_name,count(*) from animal a, main_category m, category c
where
a.main_category_id = c.category_id and
m.sub_category_id = c.category_id
group by c.category_name having count(*) > 15

17. 
select count(*), s.sub_category_name from animal a, main_category m, sub_category s
where
a.main_category_id = m.main_category_id and
m.sub_category_id = s.sub_category_id
group by s.sub_category_name having count(*) > 15

18.
select s.sub_category_name, count(*) as total from animal a, main_category m, sub_category s
where
a.main_category_id = m.main_category_id and
m.sub_category_id = s.sub_category_id
group by s.sub_category_name 
having count(*) = 
(
select max(total) from(
select s.sub_category_name, count(*) as total from animal a, main_category m, sub_category s
where
a.main_category_id = m.main_category_id and
m.sub_category_id = s.sub_category_id
group by s.sub_category_name) t1
)

19.
select c.category_name, count(*) as total from animal a, main_category m, category c
where
a.main_category_id = m.main_category_id and
m.category_id = c.category_id
group by c.category_name 
having count(*) = 
(
select max(total) from(
select c.category_name, count(*) as total from animal a, main_category m, category c
where
a.main_category_id = m.main_category_id and
m.category_id = c.category_id
group by c.category_name) t1
)

20.
select  name, c.category_name, s.sub_category_name as total from animal a, main_category m, sub_category s, category c
where
a.main_category_id = m.main_category_id and
m.category_id = c.category_id and
m.sub_category_id = s.sub_category_id and
c.category_name = 'Least Concern' and
s.sub_category_name = 'Reptiles'

nothing.
select  name, c.category_name, s.sub_category_name as total from 
animal a, main_category m, sub_category s, category c, animal_details d, city ct, state st 
where
a.main_category_id = m.main_category_id and
m.category_id = c.category_id and
m.sub_category_id = s.sub_category_id and
a.animal_id = d.animal_id and
d.city_id = ct.city_id and
ct.state_id = st.state_id and
st.state_name = 'Ahmedabad' and
c.category_name = 'Least Concern' and
s.sub_category_name = 'Reptiles'

21.
select name, s.sub_category_name as total from 
animal a, main_category m, sub_category s, category c
where
a.main_category_id = m.main_category_id and
m.category_id = c.category_id and
m.sub_category_id = s.sub_category_id and
c.category_name = 'Vulnerable' except 
(
select name, s.sub_category_name as total from 
animal a, main_category m, sub_category s, category c
where
a.main_category_id = m.main_category_id and
m.category_id = c.category_id and
m.sub_category_id = s.sub_category_id and
c.category_name = 'Vulnerable'and s.sub_category_name = 'Birds'
)

22.
select name from animal where 
char_length(name) = (select max(char_length(name)) from animal) or
char_length(name) =(select min(char_length(name)) from animal)
order by char_length(name)

23.
select name, population from animal a, animal_details d, city c
where
a.animal_id = d.animal_id and
d.city_id = c.city_id and
c.city_name = 'Surat' and
a.name = 'Harlen'

24.
select name, sci_name, age, gender, population from animal a, animal_details d, city c
where
a.animal_id = d.animal_id and
d.city_id = c.city_id and
c.city_name = 'Mumbai' and
population = 
(
select max(population) from animal a, animal_details d, city c
where
a.animal_id = d.animal_id and
d.city_id = c.city_id and
c.city_name = 'Mumbai'
)

25.
select name, sci_name, population from animal a, animal_details d, city c, state s
where
a.animal_id = d.animal_id and
d.city_id = c.city_id and
c.state_id = s.state_id and
s.state_name = 'Maharashtra' and
population = 
(
select max(population) from animal a, animal_details d, city c, state s
where
a.animal_id = d.animal_id and
d.city_id = c.city_id and
c.state_id = s.state_id and
s.state_name = 'Maharashtra'
)

26.
select name, sci_name, population from animal a, animal_details d, city c, state s, main_category m, category ca, sub_category sc
where
a.animal_id = d.animal_id and
d.city_id = c.city_id ad
c.state_id = s.state_id and
m.category_id = ca.category_id and
m.sub_category_id = sc.sub_category_id and
s.state_name = 'West Bengal' and
ca.category_name = 'Vulnerable' and
sc.sub_category_name = 'Birds' and
population = 
(
select max(population) from animal a, animal_details d, city c, state s, main_category m, category ca, sub_category sc
where
a.animal_id = d.animal_id and
d.city_id = c.city_id and
c.state_id = s.state_id and
m.category_id = ca.category_id and
m.sub_category_id = sc.sub_category_id and
s.state_name = 'West Bengal' and
ca.category_name = 'Vulnerable' and
sc.sub_category_name = 'Birds'
)

27.
select sum(population) from animal a, animal_details d, city c, state s, main_category m, sub_category sc
where
a.animal_id = d.animal_id and
a.main_category_id = m.main_category_id and
d.city_id = c.city_id and
c.state_id = s.state_id and
m.sub_category_id = sc.sub_category_id and
s.state_name = 'Karnataka' and
sc.sub_category_name = 'Mammals'

28.
select s.state_name, population, ca.category_name, sc.sub_category_name 
from animal a, animal_details d, city c, state s, main_category m, category ca, sub_category sc
where
a.animal_id = d.animal_id and
a.main_category_id = m.main_category_id and 
d.city_id = c.city_id and
c.state_id = s.state_id and
m.category_id = ca.category_id and
m.sub_category_id = sc.sub_category_id and
ca.category_name = 'Endangered' and
sc.sub_category_name = 'Reptiles' and
population = 
(
select max(population)
from animal a, animal_details d, city c, state s, main_category m, category ca, sub_category sc
where
a.animal_id = d.animal_id and
a.main_category_id = m.main_category_id and 
d.city_id = c.city_id and
c.state_id = s.state_id and
m.category_id = ca.category_id and
m.sub_category_id = sc.sub_category_id and
ca.category_name = 'Endangered' and
sc.sub_category_name = 'Reptiles'
)

29.
select s.state_name, population
from animal a, animal_details d, city c, state s, main_category m, category ca, sub_category sc
where
a.animal_id = d.animal_id and
a.main_category_id = m.main_category_id and 
d.city_id = c.city_id and
c.state_id = s.state_id and
m.category_id = ca.category_id and
m.sub_category_id = sc.sub_category_id and
ca.category_name = 'Critically Endangered' and
sc.sub_category_name = 'Amphibians'

30.
select name from
animal 
where 
animal_id = any (select animal_id from animal_details where population > 500)

31.
select sum(population), state_name
from animal a, animal_details d, city c, state s, main_category m, category ca, sub_category sc
where
a.animal_id = d.animal_id and
a.main_category_id = m.main_category_id and 
d.city_id = c.city_id and
c.state_id = s.state_id and
m.category_id = ca.category_id and
m.sub_category_id = sc.sub_category_id and
sc.sub_category_name = 'Invertebrates'
group by state_name having
sum(population) > ALL 
(
select sum(population)
from animal a, animal_details d, city c, state s, main_category m, category ca, sub_category sc
where
a.animal_id = d.animal_id and
a.main_category_id = m.main_category_id and 
d.city_id = c.city_id and
c.state_id = s.state_id and
m.category_id = ca.category_id and
m.sub_category_id = sc.sub_category_id and
sc.sub_category_name = 'Invertebrates' and
s.state_name IN ('Andhra Pradesh','Karnataka','Kerala','Tamil Nadu','Telangana')
group by state_name
)