SQL Lab 10

By -

/* 1. STORE PROCEDURE: fetch_animal_parents
IN: animal id
Display if both parents exist -> show parent's name (both mom and dad)
Else if only one of the parent exist -> show their name
Else show -> No parents */
   
   

CREATE PROCEDURE fetch_animal_parents(IN a_id INT)
BEGIN
DECLARE ac_name VARCHAR(50);
    DECLARE am_name VARCHAR(50);
    DECLARE ad_name VARCHAR(50);
    DECLARE a_year INT DEFAULT 0;
   
    #get the animal info

   
    select child.name as "Child's name" , 
           mom.name as "Mother's name" , 
           dad.name as "Father's name" into ac_name, am_name, ad_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.id = a_id;

select concat_ws(",",ac_name, am_name, ad_name,if(am_name is null, " no parents", null ), if(ad_name is null, " no parents", " ")) as result3;
   
   
END|
drop procedure fetch_animal_parents|
call fetch_animal_parents(4)|

   
drop procedure is_before_2010|
CREATE PROCEDURE is_before_2010(IN a_id INT)
BEGIN
DECLARE a_name VARCHAR(50);
    DECLARE a_year INT DEFAULT 0;
    declare father int;
    declare mother int;
   
    #get the animal info
    SELECT name, YEAR(dob), father_id, mother_id INTO a_name, a_year, father, mother
    FROM animal
    WHERE id = a_id;
   
    #conditions
    IF father = NULL THEN
SELECT CONCAT(a_id, " no parent") AS Result;
ELSEIF mother = NULL THEN
SELECT CONCAT(a_id, " no parent") AS Result;
ELSEIF father != NULL THEN
SELECT CONCAT(a_name, " father exists") AS Result;
ELSEIF mother != NULL THEN
SELECT CONCAT(a_name, " mother exists") AS Result;
ELSE
SELECT CONCAT(a_name, " was born in 2010") AS Result;
END IF;
END |
   
    call is_before_2010(1)|
   
    DROP PROCEDURE fetch_animal_parents|
   
CREATE PROCEDURE fetch_animal_parents(IN a_id INT)
BEGIN
DECLARE bool TINYINT DEFAULT 0;
    DECLARE a_name VARCHAR(50);
   
    #get the animal name
    SELECT name INTO a_name
    FROM animal
    WHERE id = a_id;

#check to see if animal is in adoption table
    SELECT COUNT(*) INTO bool
    FROM adoption
    WHERE animal_id = a_id;
   
    #if case
    IF bool > 0 THEN
SELECT CONCAT_WS(" ", a_name, "is adopted") As Adoption;
ELSE
SELECT CONCAT_WS(" ", a_name, "is not adopted") As Adoption;
END IF;
   
END |

CALL fetch_animal_parents(39)|



/* 2. STORE PROCEDURE: fetch_animal_group_of_age
IN: animal id
Display: Animal group of age
CASE: 'age'
1. 2006-2007 -> Group 1
2. 2007-2008 -> Group 2
3. 2008-2009 -> Group 3
4. 2009-2010 -> Group 4
5. 2010-2011 -> Group 5
6. 2011-2012 -> Group 6
7. 2012-2013 -> Group 7
8. 2014-2015 -> Group 8
9. 2015-2016 -> Group 9
10. 2016-2017 -> Group 10
11. 2017-2018 -> Group 11 */

CREATE PROCEDURE fetch_animal_group_of_age(IN a_id INT)
BEGIN
DECLARE a_name VARCHAR(50);
    DECLARE a_year INT DEFAULT 0;
    DECLARE before_after VARCHAR(10);
   
    #get the animal info
    SELECT name, YEAR(dob) INTO a_name, a_year
    FROM animal
    WHERE id = a_id;
   
    #conditions
    CASE
WHEN 0 THEN
SELECT CONCAT(a_id, " is invalid") AS Result;
WHEN a_year = 2006 or a_year = 2007 THEN
SELECT CONCAT(a_name, " group1") AS Result; 
WHEN a_year = 2007 or a_year = 2008 THEN
SELECT CONCAT(a_name, " group2") AS Result;
WHEN a_year = 2008 or a_year = 2009 THEN
SELECT CONCAT(a_name, " group3") AS Result;
WHEN a_year = 2009 or a_year = 2010 THEN
SELECT CONCAT(a_name, " group4") AS Result;
WHEN a_year = 2010 or a_year = 2011 THEN
SELECT CONCAT(a_name, " group5") AS Result;
WHEN a_year = 2011 or a_year = 2012 THEN
SELECT CONCAT(a_name, " group6") AS Result;
WHEN a_year = 2012 or a_year = 2013 THEN
SELECT CONCAT(a_name, " group7") AS Result;
WHEN a_year = 2013 or a_year = 2014 THEN
SELECT CONCAT(a_name, " group8") AS Result;
WHEN a_year = 2014 or a_year = 2015 THEN
SELECT CONCAT(a_name, " group9") AS Result;
WHEN a_year = 2015 or a_year = 2016 THEN
SELECT CONCAT(a_name, " group10") AS Result;
WHEN a_year = 2016 or a_year = 2017 THEN
SELECT CONCAT(a_name, " group11") AS Result;
WHEN a_year = 2017 or a_year = 2018 THEN
SELECT CONCAT(a_name, " group12") AS Result;
END CASE;
END |

drop procedure fetch_animal_group_of_age|

CALL fetch_animal_group_of_age(7)|



CREATE PROCEDURE fetch_animal_group_of_age(IN a_id INT)
BEGIN
DECLARE a_name VARCHAR(50);
    DECLARE a_year INT DEFAULT 0;
   
    #get the animal info
    SELECT name, YEAR(dob) INTO a_name, a_year
    FROM animal
    WHERE id = a_id;
   
    #conditions
    CASE
WHEN 0 THEN
SELECT CONCAT(a_id, " is invalid") AS Result;
WHEN a_year between 2006 and 2007 THEN
SELECT CONCAT(a_name, " group1") AS Result; 
WHEN a_year between 2007 and 2008 THEN
SELECT CONCAT(a_name, " group2") AS Result;
WHEN a_year between 2008 and 2009 THEN
SELECT CONCAT(a_name, " group3") AS Result;
WHEN a_year between 2009 and 2010 THEN
SELECT CONCAT(a_name, " group4") AS Result;
WHEN a_year between 20010 and 2011 THEN
SELECT CONCAT(a_name, " group5") AS Result;
WHEN a_year between 2011 and 2012 THEN
SELECT CONCAT(a_name, " group6") AS Result;
WHEN a_year between 2012 and 2013 THEN
SELECT CONCAT(a_name, " group7") AS Result;
WHEN a_year between 2013 and 2014 THEN
SELECT CONCAT(a_name, " group8") AS Result;
WHEN a_year between 2014 and 2015 THEN
SELECT CONCAT(a_name, " group9") AS Result;
WHEN a_year between 2015 and 2016 THEN
SELECT CONCAT(a_name, " group10") AS Result;
WHEN a_year between 2016 and 2017 THEN
SELECT CONCAT(a_name, " group11") AS Result;
WHEN a_year between 2017 and 2018 THEN
SELECT CONCAT(a_name, " group12") AS Result;
END CASE;
END |

CALL fetch_animal_group_of_age(7)|




select * from animal where year(dob) = 2006 or year(dob) = 2007|


/* 3. Regular Query: Using an "If" statement in the select, display the following sentence for each animal based on their gender:
-*name of animal is a sexy Female (for females)
-*name of animal is a macho Male (for males)
-*name of animal gender is to be decided (for null) */

SELECT name, dob, sex, IF(sex = 'F', 'sexy Female', IF(sex = 'M', 'macho Male', 'is to be decided')) AS gender FROM animal|

/* 4. 3. Regular Query: Using a "Case" statement in the select, display the following sentence for each animal based on their gender:
-*name of animal is a sexy Female (for females)
-*name of animal is a macho Male (for males)
-*name of animal gender is to be decided (for nulls) */
SELECT name, dob, sex,
CASE sex
WHEN 'F' THEN 'Sexy Female'
        WHEN 'M' THEN 'macho Male'
        ELSE 'is to be decided'
END AS 'Gender'
FROM animal|

/* 5. STORE PROCEDURE: fetch_animal_info
IN: animal id
Display the animal name, gender, dob and race.
-if animal doesn't have a name display: "No name"
-if animal doesn't have a gender display: "To Be decided"
-if animal doesn't have a race display: "Unknown" */
       
CREATE PROCEDURE fetch_animal_info(IN a_id INT)
BEGIN
DECLARE a_name VARCHAR(50);
DECLARE a_gender VARCHAR(1);
    DECLARE a_dob date;
    DECLARE a_race varchar(50);
   
    #get the animal info
    SELECT animal.name , animal.sex, animal.dob, race.name INTO a_name, a_gender, a_dob, a_race
    FROM animal inner join race on race.id = animal.race_id
    WHERE animal.id = a_id;
   
       select a_id as result;
   
   if (a_name is null) THEN
SELECT CONCAT(a_id, " No Name") AS Result;
            end if;
if (a_gender is null) THEN
SELECT CONCAT(a_id, " to be decided") AS Result;
            end if;
if (a_race is null) THEN
SELECT CONCAT(a_id, " Unknown") AS Result;
            end if;
            select concat_ws(",",a_name, a_gender, a_dob, a_race) as result;
END|

drop procedure fetch_animal_info|

CALL fetch_animal_info(1)|

select id from animal where name is null|

select * from animal where id = 9|

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