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.
Find the models and the prices of PC priced above laptops at minimal price:
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 four PC models:
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:
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.
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:
As a result we get:
And finally, queries may be present in the SELECT clause. Sometimes, this allows a query to be formulated in a shorthand form.
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:
Here is the result set:
Suggested exercises: 10, 18, 24, 25, 26, 28, 43, 56, 57, 60, 61, 62, 63, 72, 75, 80, 87, 88, 92, 96, 99, 110, 111, 112, 113, 118, 127, 129