Русский    English

Exercise #7 page 1

Find out the models and prices for all the products (of any type) produced by maker B.

The products in the database can be of three types: PCs, laptops, and printers. The natural solution for this exercise would be union of three sets according to the type of products. This is how this exercise was solved by one of our participants:

  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. )

Thereby within the main database this solution produces correct result, but within the verification database it outputs the following error:

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

That is to say, we cannot compare the individual value with the set, which is the case if the maker B manufactures more than one model of any type, which is exactly so for the verification 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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.