SQL Step-by-Step (21) More subqueries

More subqueries We can use the Select query statement to include a select subquery. For example, first of all, we list all customers who buy valuables, and the standard of valuables is 100 yuan more than the average price of all the items sold. The specific statements are as follows: SELECT ownerid From antiques WHERE price (SELECT AVG. Price) From Antiques); The Face query statement calculates the average price of an item plus 100 yuan, and searches for all ownerid in the antiques table that is greater than this value. Here you can use the distinct ownerid to eliminate the phenomenon of replication. The following statement lists all the people who have purchased items in the Antiqueowners table LastName: SELECT ownerlastname from antiqueowners WHERE ownerid = (SELECT DISTINCT buyerid from antiques); This subquery returns a series of customers, and the LastName of the antique owner is displayed only if the ID of the owner of the item appears in the subquery list. To update This example, let's say that there is a customer who has bought a bookcase and his firstname has made an error in the database. John: Update antiqueowners SET Ownerfirstname = ' John ' WHERE ownerid = (SELECT buyerid From antiques where ITEM = ' Bookcase '); The subquery in the above statement first searches for the buyerid of the customer who bought the bookcase, and then updates his firstname in the outer query.