loading..
Русский    English
17:31
go

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:

Console
Execute
  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.
Pages 1 2 3
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
IT помощь для проектов любой сложности
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.