Русский    English

Exercise #7 page 1

Get the models and prices for all commercially available products (of any type) produced by maker B.

The products in the database can be of three types: PCs, laptops, and printers. A natural way to solve this exercise is combining the three sets corresponding to each type of products. And here is the solution by one of our users:

  1. SELECT model, price
  2. FROM PC
  3. WHERE model = (SELECT model
  4. FROM Product
  5. WHERE maker = 'B' AND
  6. type = 'PC'
  7. )
  8. UNION
  9. SELECT model, price
  10. FROM Laptop
  11. WHERE model = (SELECT model
  12. FROM Product
  13. WHERE maker = 'B' AND
  14. type = 'Laptop'
  15. )
  16. UNION
  17. SELECT model, price
  18. FROM Printer
  19. WHERE model = (SELECT model
  20. FROM Product
  21. WHERE maker = 'B' AND
  22. type = 'Printer'
  23. );

This query returns the correct result for the main database, but terminates with the following error message when run against the checking database:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

In other words, we cannot compare a single value with the set that is returned if the maker B manufactures more than one model of any type - which is exactly the case for the checking database.

Bookmark and Share
Pages 1 2 3
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
month ago
тюль жаккард Brotsy Home . Ночной клуб Once Hotel
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.