[ Pobierz całość w formacie PDF ]
.PubName, MIN(Price) AS [Minimum Price]FROM PUBLISHERS INNER JOIN BOOKSON PUBLISHERS.PubID = BOOKS.PubIDGROUP BY PUBLISHERS.PubName;The result table appears in Table 6.11.Table 6.11.Each Publisher's Least Expensive BookPubName Minimum PriceAlpha Press $12.00Big House $15.00Small House $22.956.7.3.6 HAVING GroupCriteriaThe HAVING option is used in conjunction with the GROUP BY option and allows us tospecify a criterion, in terms of aggregate functions, for deciding which data to display.For example, the following command is the same as the previous one, with the additionalHAVING option that restricts the return table to those publishers whose minimum priceis less than $20.00:SELECT PUBLISHERS.PubName, MIN(Price) AS [Minimum Price]FROM PUBLISHERS INNER JOIN BOOKS ON PUBLISHERS.PubID = BOOKS.PubIDGROUP BY PUBLISHERS.PubNameHAVING MIN(Price) $20.00.INSERT INTO NEWBOOKSSELECT ISBN, PubID, PriceFROM BOOKSWHERE Price>20;6.7.7.1 NoteText field values must be enclosed in quotation marks.6.7.8 The SELECT.INTO StatementThe SELECT.INTO statement is equivalent to a MakeTable query.It makes a newtable and inserts data from other tables.The syntax is:SELECT FieldName,.INTO NewTableNameFROM SourceWHERE RowConditionORDER BY OrderConditionFieldName is the name of the field to be copied into the new table.Source is the name ofthe table from which data is taken.This can also be the name of a query or a joinstatement.For example, the following statement creates a new table called EXPENSIVEBOOKSand includes books from the BOOKS table that cost more than $45.00:SELECT Title, ISBNINTO EXPENSIVEBOOKSFROM BOOKSWHERE Price>45ORDER BY Title;6.7.8.1 Notes" This statement is unique to Access SQL." This statement does not create indexes in the new table. 6.7.9 TRANSFORMThe TRANSFORM statement (which is not part of SQL-92) is designed to createcrosstab queries.The basic syntax is:TRANSFORM AggregateFunctionSelectStatementPIVOT ColumnHeadingsColumn [IN (Value,.)]The AggregateFunction is one of Access's aggregate functions (Avg, Count, Min, Max,Sum, First, Last, StDev, StDevP, Var, and VarP).The ColumnHeadingsColumn is thecolumn that is pivoted to give the column headings in the crosstab result table.TheValues in the IN clause option specify fixed column headings.The SelectStatement is a select statement that uses the GROUP BY clause, with somemodifications.In particular, the select statement must have at least two GROUP BYcolumns and no HAVING clause.As an example, suppose we wish to display the total number of books from eachpublisher by price.The SELECT statement:SELECT PubName, Price, COUNT(Title) AS TotalFROM PUBLISHERS INNER JOIN BOOKSON PUBLISHERS.PubID=BOOKS.PubIDGROUP BY PubName, Price;whose result table is shown in Table 6.13, doesn't really give the information in thedesired form.For instance, it is difficult to tell how many books cost $20.00.(Remember,this small table is just for illustration.)Table 6.13.Book Prices by PublisherPubName Price TotalBig House $15.00 1Big House $20.00 1Big House $25.00 2Big House $49.00 1Medium House $12.00 2Medium House $20.00 1Medium House $34.00 1Medium House $49.00 1Small House $49.00 1We can transform this into a crosstab query in two steps: 1.Add a TRANSFORM clause at the top and move the aggregate function whosevalue is to be computed to that clause.2.Add a PIVOT line at the bottom and move the column whose values will form thecolumn headings to that clause.Also, delete the reference to this column in theSELECT clause.This gives:TRANSFORM COUNT(Title)SELECT PriceFROM PUBLISHERS INNER JOIN BOOKSON PUBLISHERS.PubID=BOOKS.PubIDGROUP BY PricePIVOT PubName;with the result table shown in Table 6.14.Table 6.14.A Cross-Tabulation of Book Prices by PublisherPrice Big House Medium House Small House$12.00 2$15.00 1$20.00 1 1$25.00 2$34.00 1$49.00 1 1 1We can group the rows by the values in more than one column.For example, supposethat the BOOKS table also had a DISCOUNT column that gave the discount from theregular price of the book (as a percentage).Then by including the DISCOUNT column inthe SELECT and GROUP BY clauses, we get:TRANSFORM COUNT(Title)SELECT Price, DiscountFROM PUBLISHERS INNER JOIN BOOKSON PUBLISHERS.PubID=BOOKS.PubIDGROUP BY Price, DiscountPIVOT PubName;for which the result table is shown in Table 6.15.Table 6.15.Book Prices and Discount by PublisherPrice Discount Big House Medium House Small House$12.00 30% 2$15.00 20% 1$20.00 20% 1 $20.00 30% 1$25.00 10% 1$25.00 20% 1$34.00 10% 1$49.00 10% 1$49.00 30% 1 1In this case, each row represents a unique price/discount pair.A crosstab can also include additional row aggregates by adding additional aggregatefunctions to the SELECT clause, as follows:TRANSFORM COUNT(Title)SELECT Price, COUNT(Price) AS Count, SUM(Price) AS SumFROM PUBLISHERS INNER JOIN BOOKSON PUBLISHERS.PubID=BOOKS.PubIDGROUP BY PricePIVOT PubName;which gives the result table shown in Table 6.16.Table 6.16.Aggregating Results in a Crosstab TablePrice Count Sum Big House Medium House Small House$12.00 2 $24.00 2$15.00 1 $15.00 1$20.00 2 $40.00 1 1$25.00 2 $50.00 2$34.00 1 $34.00 1$49.00 3 $147.00 1 1 1Finally, by including fixed column names, we can reorder or omit columns from thecrosstab result table.For instance, the next statement is just like the previous one exceptfor the PIVOT clause:TRANSFORM COUNT(Title)SELECT Price, COUNT(Price) AS Count, SUM(Price) AS SumFROM PUBLISHERS INNER JOIN BOOKSON PUBLISHERS.PubID=BOOKS.PubIDGROUP BY PricePIVOT PubName IN ("Small House", "Medium House");The result table is shown in Table 6.17.Note that the order of the columns has changedand Big House is not shown.Table 6.17.Omitting Columns from a Crosstab Table Price Count Sum Small House Medium House$12.00 2 $24.00 2$15.00 1 $15.00$20.00 2 $40.00 1$25.00 2 $50.00$34.00 1 $34.00 1$49.00 3 $147.00 1 16.7.10 SubqueriesSQL permits the use of SELECT statements within the following:" Other SELECT statements" SELECT.INTO statements" INSERT.INTO statements" DELETE statements" UPDATE statementsThe internal SELECT statement is referred to as a subquery, and is generally used in theWHERE clause of the main query.The syntax of a subquery takes three possible forms, described below.6.7.10.1 Syntax 1Comparison [ANY | SOME | ALL] (SQLStatement)where Comparison is an expression followed by a comparison relation that compares theexpression with the return value(s) of the subquery.This syntax is used to compare avalue against the values obtained from another query [ Pobierz całość w formacie PDF ]

  • zanotowane.pl
  • doc.pisz.pl
  • pdf.pisz.pl
  • funlifepok.htw.pl

    •