Exercise 6.4.7: Write the following queries, based on the database schema Classe

Question

Exercise 6.4.7: Write the following queries, based on the database schema Classes (class, type, country, numGuns, bore, displacement) Ships (name, class, launched) Battles (name, date) Outcomes (ship, battle, result) of Exercise 2.4.3, and evaluate your queries using the data of that exercise. a) Find the number of battleship classes. b) Find the average number of guns of battleship classes. ! c) Find the average number of guns of battleships. Note the difference be- tween (b) and (c); do we weight a class by the number of ships of that class or not? ! d) Find for each class the year in which the first ship of that class was launched.

Explanation / Answer

a) SELECT count(*) FROM classes WHERE type = ‘bc’; //using function count
b) SELECT avg(numGuns) FROM classes WHERE type = ‘bc’; //using aggregate function average
c) SELECT avg(numGuns) FROM ships, classes WHERE ships.class =classes.class AND type = ‘bc’; //using aggregate function average and selecting data from two tables ship and classes
d) SELECT class, launched FROM ships AS L1 WHERE launched <= ALL ( SELECT year FROM ships AS S2 WHERE S2.class = L1.class); //using sub query

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.