SQL数据库第三章所有例题

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 isalso 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 customer does not place an order through agent a05.EXAMPLE 3.4.13retrieving all customer names where the customer does not place an order through agent a05, but using the two equivalent NOT IN and <>ALL predicates in place of NOT EXISTS.EXAMPLE 3.4.14Find cid values of customers who do not place any order through agent a03.EXAMPLE 3.4.15Retrieve the city names containing customers who order product p01.3.5UNION Operators and FOR ALL Conditions EXAMPLE 3.5.1to create a list of cities where either a customer or an agent, or both, is based.EXAMPLE 3.5.2Get the cid values of customers who place orders with all agents based inNew York.EXAMPLE 3.5.3Get the aid values of agents in New York or Duluth who place orders for all products costing more than a dollar.EXAMPLE 3.5.4Find aid values of agents who place orders for product p01 as well as for all products costing more than a dollar.EXAMPLE 3.5.5find cl d values for customers with the following property: if customer c006 orders a particular product, so does the customer under consideration.EXAMPLE 3.5.6Find pid values of products supplied to all customers in Duluth.3.6 Some Advanced SQL SyntaxEXAMPLE 3.6.1Request cid values of customers who order both products p01 andp07.EXAMPLE 3.6.2Retrieve all customer names where the customer does not place an order through agent a05.EXAMPLE 3.6.3Retrieve all customer names where the customer places at least two orders for the same product.EXAMPLE 3.6.4to retrieve all customers who purchased at least one product costing less than $0.50.3.7 Set Functions in SQL EXAMPLE 3.7.1determine the total dollar amount of all orders.EXAMPLE 3.7.2To determine the total quantity of product p03 that has been ordered.EXAMPLE 3.7.4Get the number of cities where customers are based.EXAMPLE 3.7.5List the cid values of alt customers who have a discount less than the maximum discount.EXAMPLE 3.7.6Find products ordered by at least two customers.EXAMPLE 3.7.7Add a row with specified values for columns cid, cname, and city (c007, Windix, Dallas, null)to the customers table.insertinto customers(cid,cname,city)values ('c007','Windix','Dallas')EXAMPLE 3.7.9After inserting the row (c007, Windix, Dallas, null) to the customers table in Example 3.7.7, assume that we wish to find the average discount of all customers.select avg(discnt)from customers3.8 Groups of Rows in SQLEXAMPLE 3.8.1to calculate the total product quantity ordered of each individual product by each individual agent.EXAMPLE 3.8.2Print out the agent name and agent identification number, and the product name and product identification number, together with the total quantity each agent supplies of that product to customers c002 and c003.EXAMPLE 3.8.3Print out all product and agent IDs and the total quantity ordered of the product by the agent, when this quantity exceeds 1000.EXAMPLE 3.8.4Provide pid values of all products purchased by at least two customers.EXAMPLE 3.8.5find the average, over all agents, of the maximum dollar sales made by each agent.3.9 A Complete Description of SQL Select EXAMPLE 3.9.1List all customers, agents, and the dollar sales for pairs of customers andagents, and order the result from largest to smallest sales totals. Retain only those pairs for which the dollar amount is at least equal to 900.00.EXAMPLE 3.9.2listed the cid values of all customers with a discount less than the maximum discount.EXAMPLE 3.9.3Retrieve the maximum discount of all customers.EXAMPLE 3.9.4Retrieve all data about customers whose cname begins with the letter “A”.EXAMPLE 3.9.5Retrieve cid values of customers whose cname does not have a third letter equal to “%”.EXAMPLE 3.9.6Retrieve cid values of customers whose cname begins “Tip_” and has an arbitrary number of characters following.EXAMPLE 3.9.7Retrieve cid values of customers whose cname starts with the sequence “ab\”.3.10 Insert, Update, and Delete Statements EXAMPLE 3.10.1Add a row with specified values to the orders table, setting the qty and dollars columns null.insert into orders (ordno, month, cid, aid, pid)values (1107, ‘aug’, ‘c006’, ‘a04’, ‘p01’)EXAMPLE 3.10.2Create a new table called swcusts of Southwestern customers, and insert into it all customers from Dallas and Austin.CREATE TABLE [dbo].[swcusts]([cid] [char](4) NOT NULL,[cname] [char](10) NULL,[city] [char](13) NULL,[discnt] [real] NULL,PRIMARY KEY([cid]))insert into swcustsselect * from customerswhere city in (‘Dallas’, ‘Austin’)EXAMPLE 3.10.3Give all agents in New York a 10% raise in the percent commission they earn on an order.update agents set percent = 1.1 * percent where city = ‘New York’EXAMPLE 3.10.4Give all customers who have total orders of more than $1000 a 10% increase in the discnt.Update customers set discnt =1.1* discntwhere cid in(selectcid from orders group by cid having sum(dollars) > 1000) EXAMPLE 3.10.5Update the discnt values in rows of the swcusts table created in Example 3.10.2 with more up-to-date di sent values from the customers table. (题目好像打错了) with more up-to-date di sent values？Update swcusts set discnt=(select discnt from customers where cid=swcust.cid)EXAMPLE 3.10.6Delete all agents in New York.delete from agents where city = ‘New York’EXAMPLE 3.10.7Delete all agents who have total orders of less than $600.delete from agents where aid in (select aid from ordersgroup by aid having sum(dollars)<600)3.11 The Power of the Select Statement EXAMPLE 3.11.2Retrieve the names of customers who order products costing $0.50.EXAMPLE 3.11.4find the average, over all agents, of the total dollar sales by agent.。