SQL Lab 8

By -

# 1. Show all the animals (id, name, sex)
prepare query1 from 'select id, name, sex from animal';
execute query1;

# 2. Show all the animals by ? race_id (id, name, sex, dob, race_name)
prepare query2 from 'select animal.id, animal.name, animal.sex, animal.dob, race.name
from animal join race on race.id = animal.race_id
                    where race_id = ?';
set @r_id = '1';
execute query2 using @r_id;

# Show all the animals by ? race_id and ? species_id (id, name, sex, race_name, species_name)
prepare query3 from 'select animal.id, animal.name, animal.sex, animal.dob, race.name, species.current_name
from animal join race on race.id = animal.race_id join species on species.id = animal.species_id
                    where animal.race_id = ? and animal.species_id = ?';
set @r_id = 1, @s_id = 1;
execute query3 using @r_id, @s_id;

# 4. Which specie has less than ? males and ? females (id, name, sex, race_name, species_name) where ? is a number of animals
prepare query4 from 'select animal.id, animal.name, animal.sex, animal.dob, race.name, species.current_name, count(*)
from animal join race on race.id = animal.race_id join species on species.id = animal.species_id
                    where animal.sex = ? or animal.sex = ?
                    group by sex, species.current_name
                    having count(*) < @int';
set @r_id = 1, @s_id = 1, @a_m = 'M', @a_f = 'F', @int = '10';
execute query4 using @a_f, @int;

select animal.id, animal.name, animal.sex, animal.dob, race.name, species.current_name, count(*)
from animal join race on race.id = animal.race_id join species on species.id = animal.species_id
                    where animal.sex = 'M' or animal.sex = 'F'
                    group by sex,species.current_name
                    having count(*) < 10;

select species.current_name, animal.id, animal.name, animal.sex, race.name
from animal join race on race.id = animal.race_id join species on species.id = animal.species_id
where sex = 'M' < 1 and sex = 'F' < 3;

SELECT animal.sex, species.latin_name
  FROM animal
     INNER JOIN species
       ON species.id = animal.species_id
        GROUP BY latin_name
          HAVING COUNT(*) < 5;

# 5. Assuming the inflation rises 20% more every year, show the prices of ? animals for the ? year based on specie's race (id, name, sex, race_name, species_name)
# where first argument is the number of animals to show and the second argument is the year number
select animal.id, animal.name, animal.sex, race.name, species.current_name,
   species.price + (species.price * 20.0 / 100.0)*1 as 'year1',
   species.price + (species.price * 20.0 / 100.0)*2 as 'year2',
       species.price + (species.price * 20.0 / 100.0)*3 as 'year3',
       species.price + (species.price * 20.0 / 100.0)*4 as 'year4',
       species.price + (species.price * 20.0 / 100.0)*5 as 'year5' 
from animal join race on race.id = animal.race_id join species on species.id = animal.species_id
where animal.name = ? and year = ?;


prepare query5 from 'select animal.id, animal.name, animal.sex, animal.dob, race.name, species.current_name, species.price + (species.price * 20.0 / 100.0)*@year
from animal join race on race.id = animal.race_id join species on species.id = animal.species_id
                    where animal.name = ?';
set @a_name = 'rox', @year=2;
execute query5 using @a_name;

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