SQL Lab 4
use block3;
-- List all the American Bully and all the American Curl (Race Name, Animal Name)
select (select name from races where id = animals.race_id) as "Race Name", animals.name as "Animal Name"
from animals
where race_id in (select id from races where name = 'American Bully' or name = 'American Curl');
-- List of animals (Name, Sex, DOB, Race_Id, Species Name) without a race .
select animals.name as "Animal Name", animals.sex as "SEX", animals.dob as "DOB", (select id from races where id = animals.race_id) as Race_id,(select name from species where id = animals.species_id) as species_name
from animals
where (select id from races where races.id = animals.race_id) is null;
-- List of animals (Name, Sex, DOB, Race_Id) whose race has the word look* in it.
select animals.name as "Animal Name", animals.sex as "SEX", animals.dob as "DOB", (select id from races where id = animals.race_id) as Race_id
from animals
where (select races.description from races where races.id = animals.race_id) like "%look%";
-- List all the dog, cats and turtle whose name starts with the letter 'a' in it.
select species.current_name, animals.name
from species, animals
where animals.name like 'a%' and (species.current_name = 'dog' or species.current_name = 'cat' or species.current_name = 'turtle');
-- List all the male dogs, the female parrots and the 3 youngest cats (name, sex, specie, dob), order by species_name
(select animals.name, animals.dob, animals.sex, (select current_name from species where id = animals.species_id) from animals where (species_id = 1 and sex = 'M') or (species_id = 4 and sex = 'F'))
union
(select animals.name, animals.dob, animals.sex, (select current_name from species where id = animals.species_id) from animals where species_id = 2 order by dob desc limit 3);
-- Display the (1) oldest Cat and the (1) oldest Dog.
(select animals.name, animals.dob, animals.sex, (select current_name from species where id = animals.species_id) from animals where (species_id = 1)order by dob asc limit 1)
union
(select animals.name, animals.dob, animals.sex, (select current_name from species where id = animals.species_id) from animals where species_id = 2 order by dob asc limit 1);
-- Display the dogs whose id is smaller than it's species's id.
(select animals.name, animals.sex, (select current_name from species where id = animals.species_id) as species_name from animals where (species_id = 1 and animals.id < animals.species_id));
select * from animals child join animals mom on child.mother_id = mom.id
join animals dad on child.father_id = dad.id
join animals mom_mom on mom.mother_id = mom_mom.id
join animals mom_dad on mom.father_id = mom_dad.id
join animals dad_mom on dad.mother_id = dad_mom.id
join animals dad_dad on dad.father_id = dad_dad.id;
-- List all the female Chausie cats and all the female turtles. (Name, DOB, Sex, Species)
(select animals.name, animals.dob, animals.sex, (select current_name from species where id = animals.species_id) as "specie"
from animals
where (species_id = 2 and sex = 'F' and race_id = 7) or (species_id = 3 and sex = 'F')); #no female cat with name chausi so it display only turtles
select * from animals where species_id = 3 and comments is null;
-- Update all the turtle's nulled comments to "hum hum hum"
UPDATE animals
SET comments = 'hum hum hum'
WHERE species_id = (
SELECT id
FROM species
WHERE current_name LIKE 'turtle%' and comments is null
);
-- Display the top 3 youngest animals of each species
(select animals.name, animals.dob, animals.sex, (select current_name from species where id = animals.species_id) as species from animals where (species_id = 1) order by dob desc limit 3)
union
(select animals.name, animals.dob, animals.sex, (select current_name from species where id = animals.species_id) as species from animals where (species_id = 2) order by dob desc limit 3)
union
(select animals.name, animals.dob, animals.sex, (select current_name from species where id = animals.species_id) as species from animals where (species_id = 3) order by dob desc limit 3)
union
(select animals.name, animals.dob, animals.sex, (select current_name from species where id = animals.species_id) as species from animals where (species_id = 4) order by dob desc limit 3);
SELECT id, name, species_id
FROM animals
WHERE species_id IN (
SELECT id
FROM species
WHERE current_name IN ('dog', 'cat', 'turtle', 'parrot')
);
SELECT species.current_name
FROM species,animals
WHERE animals.dob IN (SELECT animals.dob
FROM animals
ORDER BY animals.dob DESC);
select * from species;
-- List all the Dogs who has any parents.
select * from animals
WHERE species_id = (
SELECT id
FROM species
WHERE current_name LIKE 'dog%' and (father_id is not null or mother_id is not null)
);
-- List of Zira’s kid(s) (Name, Sex and DOB)
select animals.name as "Animal Name", animals.dob, animals.sex
from animals
where mother_id in (select id from animals where name = 'zira');
-- List of all the animals whose id matches their species id
select species.current_name, animals.name, animals.sex, animals.dob
from species, animals
where animals.id = species.id;
-- Insert the following animal in the table:
SELECT 'Mulan', 'F', '2015-07-11', id AS race_id, species_id
FROM races
WHERE name = 'Chausie';
INSERT INTO animals(name, sex, dob, race_id, species_id)
SELECT 'Mulan', 'F', '2015-07-11', id AS race_id, species_id
FROM races
WHERE name = 'Chausie';
use block_db_w3;
use block3m1;
-- Insert the following animal in the table, on duplicate change the name to 'Musho' and the sex to 'F'
INSERT INTO animals (name, sex, dob, race_id, species_id)
SELECT 'Mulan', 'F', '2015-07-11', id AS race_id, species_id
FROM races
WHERE name = 'Chausie'
ON DUPLICATE KEY UPDATE name = 'Musho' and sex = 'F';
SELECT id, sex, dob, name, species_id, mother_id, father_id
FROM animals
WHERE name = 'Mulan';
delete from animals
where id = 66;
Comments
Post a Comment