Gtu Programs

select dob from animals; SELECT name, dob FROM animals WHERE MONTH(dob) = 6;  # 1. Select all the animals born in June (assuming you don't now the month number of June)      SELECT * FROM animals WHERE MONTHNAME(dob) = 'june'; # 2. Select all the animals born in the first 8 weeks of the year SELECT name, dob, WEEK(dob) AS week, WEEKOFYEAR(dob) AS week2, YEARWEEK(dob) AS week_year FROM animals where WEEKOFYEAR(dob) <= 8; # 3. Display the day (in numbers) and month of birth (in words) of all the turtles and cats born before 2007 (two columns). SELECT name, dob, DATE_FORMAT(dob, '%d')AS day_in_no, DATE_FORMAT(dob, '%M')AS month_in_words from animals where (species_id = 2 or species_id = 3) and (dob < "2007"); # 4. Display the day (in numbers) and month of birth (in words) of all the turtles and cats born before 2007 (one column). SELECT name, dob, DATE_FORMAT(dob, '%d of %M')AS format_date from animals where (speci

#1 Set the default value "George to the names in the animal table" ALTER TABLE animal ALTER name SET DEFAULT 'George'; #2 How many CATS are born the same year? I want to see the total per sex and overall total. Display the name, dob, race_name, species_name, sex) (todo) SELECT animal.name, animal.dob, race.name as "race_name", species.current_name as "species_name",  animal.sex, COALESCE(animal.sex, 'Total') Sex, COUNT(*) AS num_animal FROM animal join race on race.id = animal.race_id join species on species.id = animal.species_id WHERE YEAR(animal.dob) = (SELECT YEAR(animal.dob) FROM species WHERE species.current_name = 'cat') GROUP BY sex WITH ROLLUP; #3 List all the animal(name, race_name) race name is longer then their name SELECT animal.name as "Aname", race.name as "Rname" FROM animal JOIN race ON race.id = animal.race_id where length(race.name) > length(animal

use block_db_w3d3; -- How many races exist in the animals table? (Display all of their name) select count(race_id) as races_number, GROUP_CONCAT(name) from animals; -- How many species exist in the races table? (Display all of their name) select count(distinct species_id) as species, GROUP_CONCAT(distinct species.current_name) from races inner join species on races.species_id = species.id; -- How many Dogs have a father? (Display the children's name and the father's name) select * from animals where species_id = 1 and father_id is not null; select * from animals as t1 join animals as t2 join animals as t3 on (t2.father_id = t1.id) = t3.id where t2.father_id is not null and t2.species_id = 1; -- What is the average race price of each specie? SELECT AVG(price) AS "Average Price",        id AS "Race ID"     FROM races GROUP BY id; -- How many males and females exist in the animals table? select count(*) as male_number from animals where

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

use block3; select * from animals; select * from species; select * from races; -- List all the Affenpinscher (Race Name, Animal Name) select races.name as "Race Name", animals.name as "Animal Name" from races inner join animals on races.id = animals.race_id where races.name = 'Affenpinscher'; -- List of animals (Name, DOB, Race Name, Race Description) who does not have the word pound in their race description. select animals.name as "Animal Name", animals.dob as "DOB", races.name as "Race Name",  races.description as "Race Description" from races inner join animals on races.id = animals.race_id WHERE races.description not like "%pound%"; -- List of animals (Name, DOB, Race Name, Race Description) who does not have a race description. select animals.name as "Animal Name", animals.dob as "DOB", races.name as "Race Name",  races.description as "Race Descript

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 tr

1: Create a Database Table for DayCare sol :         CREATE TABLE dc_kids ( kid_id INT NOT NULL AUTO_INCREMENT, kid_fname VARCHAR(30) NOT NULL,         kid_lname VARCHAR(30) NOT NULL, kid_gender CHAR(1) NOT NULL,         kid_age SMALLINT UNSIGNED NOT NULL,         kid_address TEXT NOT NULL, kid_dob DATETIME NOT NULL,         kid_father VARCHAR(30) NOT NULL,         kid_mother VARCHAR(30) NOT NULL, kid_emgcontact VARCHAR(15) NOT NULL, PRIMARY KEY (kid_id)  )  ENGINE=INNODB; // Inserting data to Table... INSERT INTO dc_kids (kid_fname, kid_lname, kid_gender, kid_age, kid_address, kid_dob, kid_father, kid_mother, kid_emgcontact) VALUES ('Deep', 'Bhati', 'M', '3', '21st Jump Street', '1996-12-06 05:18:01', 'Mahesh', 'Megha', '987654321'),     ('Scarlett', 'Johansson', 'F', '4', '21B Baker Street', '1992-11-06 06:19:02', 'Bruce', &#