SQL Lab 2

drop table animals;
drop table races;
drop table species;

use block3;
create table species (id INT unsigned primary key not null auto_increment, current_name varchar(50),latin_name varchar(50),description text)engine=InnoDB;


Insert into species values(Default,'Dog','Canis familiaris','A domesticated carnivorous mammal that typically has a long snout, an acute sense of smell.'),
  (Default,'Cat','Felis catus','A small domesticated carnivorous mammal with soft fur, a short snout, and retractile claws.'),
                          (Default,'Turtle','Testudo hermanni','A slow-moving reptile, enclosed in a scaly or leathery domed shell into which it can retract its head and thick legs.'),
                          (default,'Parrot','Alipiopstitta xanthops','A bird, often vividly colored, with a short down-curved hooked bill, grasping feet, and a raucous voice, found esp. in the tropics and feeding on fruits and seeds.');

ALTER TABLE animals add species_id INT unsigned , add constraint fk_special_id_animal
FOREIGN KEY(species_id) REFERENCES species(id);
                    
                    select * from animals;
                    
UPDATE animals
SET species_id = 1
WHERE species = 'dog';

UPDATE animals
SET species_id = 2
WHERE species = 'cat';

UPDATE animals
SET species_id = 3
WHERE species = 'turtle';

UPDATE animals
SET species_id = 4
WHERE species = 'parrot';

ALTER TABLE animals 
   ADD FOREIGN KEY (species_id) REFERENCES species(id);

select * from animals;

select * from species;

select * from races;

create table races (id INT unsigned primary key not null auto_increment, name varchar(50), species_id INT unsigned not null, description text, 
constraint fk_fk_special_id_races FOREIGN KEY(species_id) REFERENCES species(id))engine=InnoDB;
         
INSERT INTO races (name, species_id, description)
VALUES 
('Affenpinscher', 1, 'An Affenpinscher generally weighs 6.5 to 13.2 pounds (2.9 to 6.0 kg) and stands 9 to 12 inches (23 to 30 cm) tall at the withers.'),
('Boxer', 1, 'The breed standard dictates that it must be in perfect proportion to the body and above all it must never be too light.[3] The greatest value is to be placed on the muzzle being of correct form and in absolute proportion to the skull.'),
('American Bully', 1, 'According to the American Bully Kennel Club the American bully has a well-defined, powerful appearance with straight, muscular legs. The head is medium-length and broad with a well-defined stop and high-set ears, which may be natural or cropped.'),
('American Curl', 2, 'The American Curl is a breed of cat characterized by its unusual ears, which curl back from the face toward the center of the back of the skull.'),
('Abyssinian', 2, 'The Abyssinian has alert, relatively large pointed ears. The head is broad and moderately wedge shaped. Its eyes are almond shaped and colors include gold, green, hazel or copper. The paws are small and oval. The legs are slender in proportion to the body, with a fine bone structure. The Abyssinian has a fairly long tail, broad at the base and tapering to a point.'),
('Bengal', 2, 'Bengal cats have "wild-looking" markings, such as large spots, rosettes, and a light/white belly, and a body structure reminiscent of the leopard cat. A Bengals rosetted spots occur only on the back and sides, with stripes elsewhere. The breed typically also features "mascara" (horizontal striping alongside the eyes), and foreleg striping.'),
('Chausie', 2, 'Chausies are bred to be medium to large in size, as compared to traditional domestic breeds (Chausie breed standard). Most Chausies are a little smaller than a male Maine Coon, for example, but larger than a Siamese. Adult Chausie males typically weigh 9 to 15 pounds. Adult females are usually 7 to 10 pounds.');


alter table animals add race_id INT unsigned , add constraint fk_race_id_animals FOREIGN KEY(race_id) REFERENCES races(id);

update animals set race_id = '1' where id in ('1','13','20','18','22','25','26','28');
update animals set race_id = '2' where id in ('12','14','19','7');
update animals set race_id = '3' where id in ('23','17','21','27');
update animals set race_id = '4' where id in ('33','35','37','41','44','31','3');
update animals set race_id = '5' where id in ('43','40','30','32','42','34','39','8');
update animals set race_id = '6' where id in ('29','36','38');


show index from animals;

select * from animals;

ALTER TABLE animals 
   ADD FOREIGN KEY (mother_id) REFERENCES animals(id);
   
ALTER TABLE animals
DROP mother_id;

ALTER TABLE animals
DROP father_id;

ALTER TABLE animals
ADD mother_id SMALLINT(6) UNSIGNED;

ALTER TABLE animals
ADD father_id SMALLINT(6) UNSIGNED;


ALTER TABLE animals 
   ADD FOREIGN KEY (father_id) REFERENCES animals(id);


select * from races;

select * from animals;


UPDATE animals
SET mother_id = 18
WHERE id = 1;

UPDATE animals
SET father_id = 22
WHERE id = 1;

UPDATE animals
SET mother_id = 7
WHERE id = 10;

UPDATE animals
SET father_id = 21
WHERE id = 10;

UPDATE animals
SET mother_id = 41
WHERE id = 3;

UPDATE animals
SET father_id = 31
WHERE id = 3;


UPDATE animals
SET mother_id = 40
WHERE id = 2;

UPDATE animals
SET father_id = 30
WHERE id = 2;

alter table animals drop column species;

UPDATE animals
SET comments = 'To Come'
WHERE comments IS NULL;