02:14

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Typical mistakes at the solution of SELECT statement exercises

Database "Computer firm"

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:

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

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.

Pages

UNION subquery exercise 7

Previos [Exercise #6]

[Exercise #8] Next

Last added:

Tags

aggregate functions ALL ASCII AVG Battles Bezhaev Bismarck C.J.Date Cartesian product CASE cast CHAR CHARINDEX classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CTE data type conversion database schema DATEDIFF DATEPART DATETIME DELETE DISTINCT EXCEPT exercise (-2) exercise 19 exercise 23 exercise 37 exercise 39 exercise 46 exercise 54 exercise 55 exercise 56 exercise 57 exercise 7 exercise 70 exercise 8 exercises EXISTS FROM FULL JOIN GROUP BY grouping Guadalcanal More tags

The book was updated
yesterday

SQL Problems and solutions

S. I. Moiseenko

Typical mistakes at the solution of SELECT statement exercises

Database "Computer firm"

Exercise #7 page 1