Interactive SQL Tutorial
12. More subqueries
Another common usage of subqueries involves the use of operators to
allow a Where condition to include the Select output of a subquery.
First, list the buyers who purchased an expensive item (the Price of
the item is $100 greater than the average price of all items purchased):
SELECT BUYERID
FROM ANTIQUES
WHERE PRICE >
(SELECT AVG(PRICE) + 100
FROM ANTIQUES)
The subquery calculates the average Price, plus $100, and using that
figure, an OwnerID is printed for every item costing over that
figure. One could use DISTINCT OWNERID, to eliminate duplicates.
List the Last Names of those in the AntiqueOwners table, ONLY if they
have bought an item:
SELECT OWNERLASTNAME
FROM ANTIQUEOWNERS
WHERE OWNERID IN
(SELECT DISTINCT BUYERID
FROM ANTIQUES)
The subquery returns a list of buyers, and the Last Name is printed
for an Antique Owner if and only if the Owner's ID appears in the
subquery list (sometimes called a candidate list). Note: on some
DBMS's, equals can be used instead of IN, but for clarity's sake,
since a set is returned from the subquery, IN is the better choice.
For an Update example, we know that the gentleman who bought the
bookcase has the wrong First Name in the database...it should be John:
UPDATE ANTIQUEOWNERS
SET OWNERFIRSTNAME = 'John'
WHERE OWNERID =
(SELECT BUYERID
FROM ANTIQUES
WHERE ITEM = 'Bookcase')
First, the subquery finds the BuyerID for the person(s) who bought
the Bookcase, then the outer query updates his First Name.
Remember this rule about subqueries: when you have a subquery as part
of a WHERE condition, the Select clause in the subquery must have
columns that match in number and type to those in the Where clause of
the outer query. In other words, if you have "WHERE ColumnName =
(SELECT...);", the Select must have only one column in it, to
match the ColumnName in the outer Where clause, and they must match
in type (both being integers, both being character strings, etc.).
<< Prev
1
2
3
4
5
6
7
8
9
10
11
12
13
Next >>
| 
