question 5 - 9

Consider the following relational schema: Classes(class, type, country, numGuns, bore, displacement) Ships name, class, launched) Battles(name, date) Outcomes(ship, battle, result) Write the SQL, statement and draw the relational algebra query execution tree for each of the following queries (8 points/SQL +4.5 points/query tree): 1. What are the ship names of USA enemies (Germany and Japan)? 2. What countries did launch battle-ships but did not launch battle-carriers after 19407 3, what countries had damaged ships (result- 'damaged') n 1944 battles? 4 What battle-ships from USA did participate in the Pearl Harbor battle? . What ships from USA did fight in exactly one battle in 1944 and got 'sunk' in that battle? 6. What is the number of ships in each battle-carrier class from Germany? 7. What is the average number of guns installed on USA ships? 8. what are the battles in which at least three ships fron the same country got damaged'? 9. For each battle in 1940 return the number of USA ships that got 'damaged' and that got 'sunk'. 10. What is the average number of guns lost by German ships per battle 11. What are the ship names of the USA class(es) with the smallest number of guns? 12. What are the battles in which there were involved ships from at least three countries? 13. What is the year in which USA launched the largest number of ships? 14. What are the battles in which no ship got damaged? 15. What are the battles in which at least one ship from USA got 'sunk'? 16. All the ships launched before 1940 by Germany were inherited by West Germany after the war, while all the ships launched after 1940 by Germany were inherited by East Germany. Update your database to reflect these changes Consider the following relational schema: Classes(class, type, country, numGuns, bore, displacement) Ships name, class, launched) Battles(name, date) Outcomes(ship, battle, result) Write the SQL, statement and draw the relational algebra query execution tree for each of the following queries (8 points/SQL +4.5 points/query tree): 1. What are the ship names of USA enemies (Germany and Japan)? 2. What countries did launch battle-ships but did not launch battle-carriers after 19407 3, what countries had damaged ships (result- 'damaged') n 1944 battles? 4 What battle-ships from USA did participate in the Pearl Harbor battle? . What ships from USA did fight in exactly one battle in 1944 and got 'sunk' in that battle? 6. What is the number of ships in each battle-carrier class from Germany? 7. What is the average number of guns installed on USA ships? 8. what are the battles in which at least three ships fron the same country got damaged'? 9. For each battle in 1940 return the number of USA ships that got 'damaged' and that got 'sunk'. 10. What is the average number of guns lost by German ships per battle 11. What are the ship names of the USA class(es) with the smallest number of guns? 12. What are the battles in which there were involved ships from at least three countries? 13. What is the year in which USA launched the largest number of ships? 14. What are the battles in which no ship got damaged? 15. What are the battles in which at least one ship from USA got 'sunk'? 16. All the ships launched before 1940 by Germany were inherited by West Germany after the war, while all the ships launched after 1940 by Germany were inherited by East Germany. Update your database to reflect these changes