17:05

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Computer Firm Database

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

SELECT model, price 
FROM PC 
WHERE model = (SELECT model 
 FROM Product 
 WHERE maker = 'B' AND 
 type = 'PC'
 )
UNION
SELECT model, price 
FROM Laptop 
WHERE model = (SELECT model 
 FROM Product 
 WHERE maker = 'B' AND 
 type = 'Laptop'
 )
UNION
SELECT model, price 
FROM Printer 
WHERE model = (SELECT model 
 FROM Product 
 WHERE maker = 'B' AND 
 type = 'Printer'
 );

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

UNION subquery exercise 7

Previous [Exercise #6]

[Exercise #8] Next

Last added:

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
month ago

SQL Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Computer Firm Database

Exercise #7 page 1