ASSIGNMENT-4.txt

ASSIGNMENT 4
(DATABASE WORKLOAD)
ADB B3 Bauyrzhan Marat

1. Query
select animals.animal_name, animals.age as animal_age, animals.domesticated, villagers.first_name as villager_name, 
    villagers.animal_id, villagers.age as villager_age from animals
        full outer join villagers on animals.animal_id = villagers.villager_id
        full outer join families on villagers.family_id = families.family_id
        full outer join housings on families.house_id = housings.house_id
            where competence_bonus > 0.5
            or villagers.school_id in (select villagers.competence from villagers join schools on villagers.school_id = schools.school_id);
         
1st run
2nd run
3rd run
4th run
5th run
3,058
3,01
3,008
2,988
2,973

MIN
MAX
AVG
2,973
3,058
3,0074
2. Query
select count(*) from (
select villagers.first_name, villagers.last_name, villagers.age as villagers_age, villagers.school_id,
    families.family_happiness, villagers.gender,
    housings.condition, cast ((housings.size_in_m2 / 100) as INT) as string_size from villagers
        full outer join families on villagers.family_id = families.family_id
        full outer join housings on families.house_id = housings.house_id
            where housings.village_id in (select villages.village_id from villages join housings on housings.village_id = villages.village_id)
            or housings.condition > 0.8);
1st run
2nd run
3rd run
4th run
5th run
10,546
10,606
10,869
10,55
10,823
MIN
MAX
AVG
10,546
10,869
10,67
3. Query
select count(*) from (
select * from villagers
    full outer join animals on animals.animal_id = villagers.animal_id
    full outer join families on villagers.family_id = families.family_id
    full outer join housings on housings.house_id = families.house_id
    full outer join villages on villages.village_id = housings.village_id
    full outer join locations on locations.location_id = villages.location_id
        where animals.animal_id in 
            (select animals.animal_id from animals where animals.animal_id in
                (select animal_id from animals where animal_name like 'Gol%' and (substr(animal_name, 7,2) like 'ey')))
                or villagers.school_id is not null 
                or villages.popularity > 0.5
                or locations.condition >= (select avg(condition) from locations));
1st run
2nd run
3rd run
4th run
5th run
1,674
1,579
1,6
1,155
1,182
MIN
MAX
AVG
1,155
1,674
1,438
4. Transaction
update jobs
    set wage = 17
        where risk_factor > (select avg(risk_factor) from jobs) 
        or round(required_competence) >= (select round(avg(required_competence)) from jobs)
        or substr(job_name, 2,3) like '%ata%' and end > '04/20/1948'
        or job_id in (select job_id from locations
        where number_of_trees > (select avg(number_of_trees) from locations));

1st run
2nd run
3rd run
4th run
5th run
7,428
9,477
8,509
9,419
7,527
MIN
MAX
AVG
7,428
9,477
8,472
5. Transaction
delete from VILLAGERS
    where first_name = 'Burnard' and gender = 'female'
       or substr(last_name, 2,3) not like '%a' and hapiness < (select avg(hapiness) from villagers);    

1st run
2nd run
3rd run
4th run
5th run
4,251
4,523
4,609
4,677
4,571
MIN
MAX
AVG
4,251
4,677
4,526
6. Transaction
update villages
    set popularity = (select avg(popularity) from villages
        where villages.village_id in (select villages.village_id from housings join villages on housings.village_id = villages.village_id)
        and substr(village_name, 2,3) like '%aji%' and hospital <> 'false'
        or location_id in (select location_id from locations
        where number_of_trees > (select avg(number_of_trees) from locations)));

1st run
2nd run
3rd run
4th run
5th run
3,244
3,605
3,447
3,523
3,369
MIN
MAX
AVG
3,244
3,605
3,437