SP09-Mar26-Lec09-summary-MIS3100

SP09-Mar26-Lec09-summary-MIS3100 - MIS310-DUTTA-SPRING2009...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
MIS310-DUTTA-SPRING2009 Classnotes, Lecture 09, Mar26-2009 Advanced SQL Chapters 4 and 9 Using Northwind database OPEN NORTHWIND AND DOWNLOAD THE WORD DOCUMENT NAMES Lecture Notes Mar26 [9] What is the average price of each product category? List them in decreasing/increasing order of average price. SELECT Categoryname, avg(Unitprice) AS AvgPrice FROM Categories C, Products P WHERE C.CategoryID = P.CategoryID GROUP BY Categoryname ORDER BY AvgPrice desc [10] Combining grouping and joins: For each Customer, list the company name and the total dollar value of all orders placed by that customer. SELECT C.CompanyName, sum(UnitPrice*Quantity) AS TotValue FROM Customers C, Orders O, [Order Details] D WHERE C.CustomerID = O.CustomerID AND O.OrderID = D.OrderID GROUP BY C.CompanyName [11] Same as [10] but list the companies in descending order of total dollar value. (difference between Access & SQL srvr in naming) SELECT C.CompanyName, sum(UnitPrice*Quantity) AS TotValue FROM Customers C, Orders O, [Order Details] D WHERE C.CustomerID = O.CustomerID AND O.OrderID = D.OrderID GROUP BY C.CompanyName
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ORDER BY Totvalue Desc [12] Self Joins: List the last/first names of employees as well as the last/first names for their supervisor for all employees located in the
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 02/21/2011 for the course MIS 310 taught by Professor Staff during the Spring '08 term at George Mason.

Page1 / 8

SP09-Mar26-Lec09-summary-MIS3100 - MIS310-DUTTA-SPRING2009...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online