SQL数据库第三章所有例题.docx

3.3Simple Select StatementsEXAMPLE 3.3.1find the aid values and names of agents that are based in New York. EXAMPLE 3.3.3Retrieve all pid values of parts for which orders are placed. EXAMPLE 3.3.4retrieve all customer-agent name pairs, (cname, aname), where the customer places an order through the agent. EXAMPLE 3.3.5Retrieve a “table based on the orders table, with columns ordno, cid, aid, pid, and profit, where profit is calculated from quantity and price of the product sold by subtracting 60% for wholesale cost, the discount for the customer, and the percent commission for the agent.EXAMPLE 3.3.6all pairs of customers based in the same city. EXAMPLE 3.3.7find pid values of products that have been ordered by at least two customers. EXAMPLE 3.3.8Get cid values of customers who order a product for which an order is also placed by agent a06. 3.4SubqueriesEXAMPLE 3.4.1Get cid values of customers who place orders with agents in Duluth or Dallas. EXAMPLE 3.4.2to retrieve all information concerning agents based in Duluth or Dallas (very close to the Subquery in the previous example). EXAMPLE 3.4.3to determine the names and discounts of all customers who place orders through agents in Duluth or Dallas. EXAMPLE 3.4.4to find the names of customers who order product p05. EXAMPLE 3.4.5Get the names of customers who order product p07 from agent a03.EXAMPLE 3.4.6to retrieve ordno values for all orders placed by customers in Duluth through agents in New York. EXAMPLE 3.4.7find aid values of agents with a minimum percent commission. EXAMPLE 3.4.8find all customers who have the same discount as that of any of the customers in Dallas or Boston. EXAMPLE 3.4.9Get cid values of customers with discnt smaller than those of any customers who live in Duluth. EXAMPLE 3.4.10Retrieve all customer names where the customer places an order through agent a05. EXAMPLE 3.4.11Get cid values of customers who order both products p01 and p07.EXAMPLE 3.4.12Retrieve all customer names where the custome