SQL Lab 6

By -

#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.name);

#4 List all the Dogs with a mother OR a father. I want to see the (the Child's name, Child's race_name, Child's species_name, Mother's name, Mother's race_name, Mother's species_name, Father's name, Father's race_name, Father's species_name.)
select child.name as "Child's name" , child_race.name as "Child's race_name", child_species.current_name as "Child's species_name" , mom.name as "Mother's name" , mom_race.name as "Mother's race_name", mom_species.current_name as "mother's species_name", dad.name as "Father's name",  dad_race.name as "Father's race_name", dad_species.current_name as "Father's species_name" from animal child join animal mom on child.mother_id = mom.id
join animal dad on child.father_id = dad.id
left join race child_race on child_race.id = child.race_id join species child_species on child_species.id = child.species_id
join race mom_race on mom_race.id = mom.race_id join species mom_species on mom_species.id = mom.species_id
join race dad_race on dad_race.id = dad.race_id join species dad_species on dad_species.id = dad.species_id
where (child.father_id is not null or child.mother_id is not null) and (child_species.current_name = 'dog');

/* #5 Add the following animal in the database using a single query
Name: Minou
    Dob: 2009-01-15
    Sex: M
    Race: Nebelung
*/
select 'Minou', 'M', '2009-01-15', id as race_id, species_id
from race
where name = 'Nebelung';

insert into animal(name, sex, dob, race_id, species_id)
select 'Minou', 'M', '2009-01-15', id as race_id, species_id
from race
where name = 'Nebelung';

#6 Alphabetically speaking, list the first and last 5 animal with a given name
(select name from animal
where (name is not null)
order by id asc limit 5)
union all
(select name from animal
where (name is not null)
order by id desc limit 5)
order by name;


#7 List all the animal whose name doesn't end by a vowel
SELECT DISTINCT name 
FROM animal 
WHERE (name NOT IN (SELECT DISTINCT name FROM animal WHERE name LIKE '%a' OR name LIKE '%e' OR name LIKE '%i' OR name LIKE '%o' OR name LIKE '%u'));

#8 List all the male and female cats and their price. Display their name, sex, price and total price. Group by race. Use the races price by default, if the animal dosn't have a race then use the species.price.
SELECT group_concat(distinct a.name), a.sex, COALESCE(NULLIF(r.price,''), s.price) as price, sum(COALESCE(NULLIF(r.price,''), s.price)) as "total_price", GROUP_CONCAT(DISTINCT a.name, ': (', COALESCE(NULLIF(r.price,''), s.price),  ')' ORDER BY a.name SEPARATOR ', ') as "total price of species with name", GROUP_CONCAT(DISTINCT a.name, ': (', s.current_name, ' - ', r.name, ')' ORDER BY a.name SEPARATOR ', ') animal
FROM animal a
JOIN species s
ON s.id = a.species_id
JOIN race r
ON r.id = a.race_id
WHERE s.current_name = 'cat' and (a.sex = 'M' or a.sex = 'F')
group by r.id;


#9 How much would it cost me to adopt all the CATS and DOGS in my databse? Group them by species. Display their name, price per animals, total price of species and total price of all the animals. Race price by default, then species if race price is null or doesn't exists
SELECT group_concat(distinct a.name), a.sex, COALESCE(NULLIF(r.price,''), s.price) as "price per animal", sum(s.price) as "total price of species",GROUP_CONCAT(DISTINCT a.name, ': (', r.price,  ')' ORDER BY a.name SEPARATOR ', ') as "total price of species with name",sum(COALESCE(NULLIF(r.price,''), s.price)) as "total price of all animals", GROUP_CONCAT(DISTINCT a.name, ': (', s.current_name, ' - ', r.name, ')' ORDER BY a.name SEPARATOR ', ') animal
FROM animal a
JOIN species s
ON s.id = a.species_id
JOIN race r
ON r.id = a.race_id
WHERE (s.current_name = 'cat' or s.current_name = 'dog')
group by s.id;



Comments

Post a Comment

Popular posts from this blog

5th sem OOPJ Write an interactive program to print a diamond shape. For example, if user enters the number 3, the diamond will be as follows:

By -
import java.util.Scanner; /**  *  * @author Yash  */ public class pra10 {     public static void main(String arg[]){         int i,j,k,n;         Scanner sc = new Scanner(System.in);         System.out.print("Enter the Range :");         n=sc.nextInt();         for(i=1;i<=n;i++){             for(j=n;j>=i;j--){                 System.out.print(" ");             }             for(k=1;k<=i;k++){                 System.out.print(" *");             }             System.out.println(" ");         }         for(i=1;i<=n;i++){             for(j=0;j<=i;j++){                 System.out.print(" ");             }             for(k=n;k>i;k--){                 System.out.print(" *");             }             System.out.println(" ");         }     } }
Read more

SQL Lab 3

By -
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
Read more

SQL Lab1: Create a Database Table for DayCare

By -
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', &#
Read more