loading..
Русский    English
07:34

Again about subqueries

It should be noted that a query returns generally a collection of values, so a run-time error may occur during the query execution if one uses the subquery in the WHERE clause without EXISTS, IN, ALL, and ANY predicates, which result in Boolean value.

Example 5.8.3

Find the models and the prices of PC priced above laptops at minimal price:

Console
Execute
  1. SELECT DISTINCT model, price
  2. FROM PC
  3. WHERE price > (SELECT MIN(price)
  4. FROM Laptop
  5. );

This query is quite correct, i.e. the scalar value of the price is compared with the subquery which returns a single value. As a result we get three PC models:

model price
1121 850
1233 950
1233 980

However, if in answer to question regarding the models and the prices of PCs that cost the same as a laptop one writes the following query:

Console
Execute
  1. SELECT DISTINCT model, price
  2. FROM PC
  3. WHERE price = (SELECT price
  4. FROM Laptop
  5. );
the following error message will be obtained while executing the above query:

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

This error is due to comparison of the scalar value to the subquery, which returns either more that single value or none.

In its turn, subqueries may also include nested queries.

On the other hand, it is natural that subquery returning a number of rows and consisting of multiple columns may as well be used in the FROM clause. This restricts a column/row set when joining tables.

Example 5.8.4

Find the maker, the type, and the processor's speed of the laptops with speed above 600 MGz.

For example, this query may be formulated as follows:

Console
Execute
  1. SELECT prod.maker, lap.*
  2. FROM (SELECT 'laptop' AS type, model, speed
  3. FROM laptop
  4. WHERE speed > 600
  5. ) AS lap INNER JOIN
  6. (SELECT maker, model
  7. FROM product
  8. ) AS prod ON lap.model = prod.model;

As a result we get:

maker type model speed
B laptop 1750 750
A laptop 1752 750

And finally, queries may be present in the SELECT clause. Sometimes, this allows a query to be formulated in a shorthand form.

Example 5.8.5

Find the difference between the average prices of PCs and laptops, i.e. by how mach is the laptop price higher than that of PC in average.

Generally speaking, a single SELECT clause is sufficient in this case:

Console
Execute
  1. SELECT (SELECT AVG(price)
  2. FROM Laptop
  3. ) -
  4. (SELECT AVG(price)
  5. FROM PC
  6. ) AS dif_price;

Here is the result set:

dif_price
328.3333

Suggested exercises: 10, 18, 24, 25, 262843, 56, 57, 60, 61, 62, 63, 72, 75, 80, 87, 88, 92, 96, 99, 110, 111, 112, 113, 118, 127, 129

Bookmark and Share
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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100