[ Pobierz całość w formacie PDF ]
.To calculate it, you must add the products of quantities times prices, taking into con-sideration each invoice s discount:USE NORTHWINDSELECT SUM(Quantity * UnitPrice * (1 - Discount))FROM [Order Details]WHERE ProductID = 11You will find out that Queso Cabrales generated a total revenue of $12,901.77.If you want toknow how many items of this product were sold, add one more aggregate function to the query tosum the quantities of each row that refers to the specific product ID:USE NORTHWINDSELECT SUM(Quantity),SUM(Quantity * UnitPrice * (1 - Discount))FROM [Order Details]WHERE ProductID = 11SQL JoinsJoins specify how you connect multiple tables in a query, and there are four types of joins:Left outer, or left joinRight outer, or right joinFull outer, or full joinInner joinA join operation combines all the rows of one table with the rows of another table.Joins are usu-ally followed by a condition, which determines which records in either side of the join will appear inthe result.The WHERE clause of the SELECT statement is very similar to a join, but there somefine points that will be explained momentarily.Note The left, right, and full joins are sometimes called  outer joins to differentiate them from an inner join. Leftjoin and  left outer join mean the same thing.Left JoinsThis join displays all the records in the left table and only those records of the table on the right thatmatch certain user-supplied criteria.This join has the following syntax:FROM (primary table) LEFT JOIN (secondary table) ON (primary table).(field)(comparison) (secondary table).(field)www.sybex.comCopyright ©2002 SYBEX, Inc., Alameda, CA2877c20.qxd 11/11/01 4:21 PM Page 900900 Chapter 20 DATABASES: ARCHITECTURE AND BASIC CONCEPTSThe left outer join retrieves all rows in the primary table and the matching rows from a secondarytable.The following statement will retrieve all the titles from the Pubs database along with theirpublisher.If some titles have no publisher, they will be included to the result:USE PUBSSELECT title, pub_nameFROM titles LEFT JOIN publishersON titles.pub_id = publishers.pub_idRight JoinsThis join is similar to the left outer join, except that all rows in the table on the right are displayedand only the matching rows from the left table are displayed.This join has the following syntax:FROM (secondary table) RIGHT JOIN (primary table) ON (secondary table).(field)(comparison) (primary table).(field)The following statement retrieves all the publishers from the Pubs database along with their titles.Notice that this statement is almost exactly the same as the example of the left outer join entry; Ionly changed LEFT to RIGHT:USE PUBSSELECT title, pub_nameFROM titles RIGHT JOIN publishersON titles.pub_id = publishers.pub_idFull JoinsThe full join returns all the rows of the two tables, regardless of whether there are matching rows ornot.In effect, it s a combination of left and right joins.To retrieve all the titles and all publishers,and match publishers to their titles, use the following join:USE PUBSSELECT title, pub_nameFROM titles FULL JOIN publishersON titles.pub_id = publishers.pub_idInner JoinsThis join returns the matching rows of both tables, similar to the WHERE clause, and has the fol-lowing syntax:FROM (primary table) INNER JOIN (secondary table) ON (primary table).(field)(comparison) (secondary table).(field)The following SQL statement combines records from the Titles and Publishers tables of thePUBS database if their PubID fields match.It returns all the titles and their publishers.Titles with-out publishers will not be included in the result:USE PUBSSELECT titles.title, publishers.pub_name FROM titles, publishersWHERE titles.pub_id = publishers.pub_idwww.sybex.comCopyright ©2002 SYBEX, Inc., Alameda, CA2877c20.qxd 11/11/01 4:21 PM Page 901STRUCTURED QUERY LANGUAGE 901You can retrieve the same rows using an inner join, as follows:USE PUBSSELECT titles.title, publishers.pub_nameFROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_idFor more examples with joins, see the section  Specifying Left, Right, and Inner Joins, later inthis chapter.Grouping RowsSometimes you need to group the results of a query, so that you can calculate subtotals.Let s say youneed not only the total revenues generated by a single product, but a list of all products and the rev-enues they generated.The example of the previous section  Totaling and Counting calculates thetotal revenue generated by a single product [ Pobierz caÅ‚ość w formacie PDF ]