loading..
Русский    English
00:53

Exercise #57 page 2

Exercise 3.13.2

Console
Execute
  1. SELECT class, SUM(sunk)
  2. FROM (SELECT class, COUNT(*) AS sunk
  3. FROM Ships a JOIN
  4. Outcomes b ON a.name = b.ship AND
  5. a.class <> b.ship
  6. WHERE result = 'sunk'
  7. GROUP BY class
  8. UNION ALL
  9. SELECT class, '1'
  10. FROM Classes a JOIN
  11. Outcomes b ON a.class = b.ship
  12. WHERE result = 'sunk'
  13. UNION ALL
  14. SELECT class, '0'
  15. FROM classes
  16. ) t
  17. -- classes the number of ships in which great than 2:
  18. WHERE class IN (SELECT t1.class
  19. FROM (SELECT a.class
  20. FROM Classes a LEFT JOIN
  21. Ships b ON a.class = b.class
  22. ) t1 LEFT JOIN (SELECT DISTINCT ship
  23. FROM Outcomes
  24. WHERE ship NOT IN (SELECT name
  25. FROM Ships
  26. )
  27. ) t2 ON t1.class = t2.ship
  28. GROUP BY t1.class
  29. HAVING COUNT(*) > 2
  30. )
  31. GROUP BY class
  32. HAVING SUM(sunk) > 0


Exercise 3.13.3

Console
Execute
  1. SELECT a.class AS cls, a.num_sunks AS sunk
  2. FROM (SELECT c.class, COUNT (o.ship) AS num_sunks
  3. FROM Outcomes o LEFT JOIN
  4. Ships s ON o.ship = s.name LEFT JOIN
  5. Classes c ON s.class = c.class
  6. WHERE o.result = 'sunk'
  7. GROUP BY c.class) a,
  8. (SELECT c1.class
  9. FROM Ships s1, Classes c1
  10. WHERE s1.class = c1.class
  11. GROUP BY c1.class
  12. HAVING COUNT(name) >= 3
  13. ) B
  14. WHERE a.class = b.class


Exercise 3.13.4

Console
Execute
  1. SELECT class, COUNT(result) AS sunk
  2. FROM (SELECT class, result, name
  3. FROM Ships LEFT JOIN
  4. Outcomes ON ship=name AND
  5. class IS NOT NULL AND
  6. result = 'sunk'
  7. ) T
  8. GROUP BY class
  9. HAVING COUNT(class) > 2 AND
  10. COUNT(result) > 0

Analyse subtleties of the above-mentioned solutions, the most beautiful of which, certainly, is 3.13.4. Only one join for which at once it is counted up both quantity of sunken ships, and the total number of the ships in a class. These solutions have a common mistake we have talked above about: the head ships which are present in Outcomes table and are absent in Ships table have been not taken into account.

T&S

To solve the problem on SQL-EX.RU

Bookmark and Share
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100