|
Custom Search
| |
|
Inside This Article
Queries to generate aggregated data for Oracle Northwind database In the first two parts of this article series, the Northwind Oracle database has been converted from Access Northwind database. Next we're going to convert the Access queries to Oracle SQL queries. These queries are used for Access Northwind Traders application. Some of them are relatively complex aggregated queries. Here is a screenshot from Access Northwind database.
Please note that, the JOIN clause used in the Oracle queries on this page is compliant with ANSI SQL/92 syntax, for which Oracle only started to support since Oracle 9i. If you are running 8i Oracle server or older, you'll have to change the JOIN to the SQL/86 standard that Oracle supported long ago. The advantage of using ANSI JOIN syntax is that it separates JOIN conditions from WHERE clause. 1. Order Subtotals For each order, calculate a subtotal for each Order (identified by Order_ID). This is a simple query using GROUP BY to aggregate data for each order.
-- Get subtotal for each order. select Order_ID, to_char(sum(Unit_Price * Quantity * (1 - Discount)), '$99,999.99') as Subtotal from Order_Details group by Order_ID order by Order_ID;
Here is the query result. 830 records returned. 2. Sales by Year This query shows how to get the year part from Shipped_Date column. A subtotal is calculated by a sub-query for each order. The sub-query forms a table and then joined with the Orders table.
select distinct a.Shipped_Date, a.Order_ID, b.Subtotal, to_char(a.Shipped_Date,'YYYY') as "Year" from Orders a inner join ( -- Get subtotal for each order select distinct Order_ID, to_char(sum(Unit_Price * Quantity * (1 - Discount)), '$99,999.99') as Subtotal from Order_Details group by Order_ID ) b on a.Order_ID = b.Order_ID where a.Shipped_Date is not null and a.Shipped_Date between to_date('24/12/1996', 'DD/MM/YYYY') and to_date('30/09/1997', 'DD/MM/YYYY') order by a.Shipped_Date;
Here is the query result. 296 records returned. 3. Employee Sales by Country For each employee, get their sales amount, broken down by country name.
select distinct a.Country, a.LastName, a.FirstName, b.Shipped_Date, b.Order_ID, c.Subtotal as Sale_Amount from Employees a inner join Orders b on b.Employee_ID = a.Employee_ID inner join ( -- Get subtotal for each order select distinct Order_ID, to_char(sum(Unit_Price * Quantity * (1 - Discount)), '$99,999.99') as Subtotal from Order_Details group by Order_ID ) c on b.Order_ID = c.Order_ID where b.Shipped_Date between to_date('24/12/1996', 'DD/MM/YYYY') and to_date('30/09/1997', 'DD/MM/YYYY') order by a.LastName, a.FirstName, a.Country, b.Shipped_Date;
Here is the query result. 296 records returned. 4. Alphabetical List of Products This is a rather simple query to get an alphabetical list of products.
select distinct b.*, a.Category_Name from Categories a inner join Products b on a.Category_ID = b.Category_ID where b.Discontinued = 'N' order by b.Product_Name;
Here is the query result. 69 records returned. 5. Current Product List This is another simple query. No aggregation is used for summarizing data.
select Product_ID, Product_Name from Products where Discontinued = 'N' order by Product_Name;
Here is the query result. 69 records returned. 6. Order Details Extended This query calculates sales price for each order after discount is applied.
select distinct y.Order_ID, y.Product_ID, x.Product_Name, y.Unit_Price, y.Quantity, y.Discount, y.Unit_Price * y.Quantity * (1 - y.Discount) as Extended_Price from Products x inner join Order_Details y on x.Product_ID = y.Product_ID order by y.Order_ID;
Here is the query result. 2,155 records returned. 7. Sales by Category For each category, we get the list of products sold and the total sales amount. Note that, the inner query for table c is to get sales for each product on each order. It then joins with outer query on Product_ID. In the outer query, products are grouped for each category.
select distinct a.Category_ID, a.Category_Name, b.Product_Name, sum(c.Extended_Price) as Product_Sales from Categories a inner join Products b on a.Category_ID = b.Category_ID inner join ( select distinct y.Order_ID, y.Product_ID, x.Product_Name, y.Unit_Price, y.Quantity, y.Discount, y.Unit_Price * y.Quantity * (1 - y.Discount) as Extended_Price from Products x inner join Order_Details y on x.Product_ID = y.Product_ID order by y.Order_ID ) c on c.Product_ID = b.Product_ID inner join Orders d on d.Order_ID = c.Order_ID where d.Order_Date between to_date('1/1/1997', 'DD/MM/YYYY') and to_date('31/12/1997', 'DD/MM/YYYY') group by a.Category_ID, a.Category_Name, b.Product_Name order by a.Category_Name, b.Product_Name, Product_Sales;
Here is the query result. 77 records returned. 8. Ten Most Expensive Products The three queries below return the same result. It demonstrates how Oracle limits the number of records selected. The top-N query in Oracle is tricky and is not as easy to create as those in MS SQL Server (TOP n keyword) or MySQL (LIMIT keyword). The first query uses correlated sub-query to get the top 10 most expensive products. It works for any Oracle versions, including Oracle 8i or older. The second query retrieves data from an ordered sub-query table and rownum is used outside the sub-query to restrict the number of rows returned. For Oracle 8i and above, we can use this fashion to get the Top N rows by using a sub-query with ORDER BY clause and rownum function in outer query. The third query uses ROW_NUMBER() function to get ranks with OVER function. From Oracle 9i and above, the RANK (as well as DENSE_RANK) function can be used to get the TOP N records.
-- Query 1 select distinct Product_Name as Ten_Most_Expensive_Products, Unit_Price from Products a where 10 >= (select count (distinct Unit_Price) from Products b where b.Unit_Price >= a.Unit_Price) order by Unit_Price desc; -- Query 2 select * from ( select distinct Product_Name as Ten_Most_Expensive_Products, Unit_Price from Products order by Unit_Price desc ) where rownum <= 10; -- Query 3 select Ten_Most_Expensive_Products, Unit_Price from ( select distinct Product_Name as Ten_Most_Expensive_Products, Unit_Price, ROW_NUMBER() over (order by Unit_Price desc) row_number from Products order by Unit_Price desc ) where row_number between 1 and 10;
Here is the query result. 10 records returned. 9. Products by Category This is a simple query just because it's in Access Northwind so we converted it here in Oracle.
select distinct a.Category_Name, b.Product_Name, b.Quantity_Per_Unit, b.Units_In_Stock, b.Discontinued from Categories a inner join Products b on a.Category_ID = b.Category_ID where b.Discontinued = 'N' order by a.Category_Name, b.Product_Name;
Here is the query result. 69 records returned. 10. Customers and Suppliers by City This query shows how to use UNION to merge Customers and Suppliers into one result set by identifying them as having different relationships to Northwind Traders - Customers and Suppliers.
select City, Company_Name, Contact_Name, 'Customers' as Relationship from Customers union select City, Company_Name, Contact_Name, 'Suppliers' from Suppliers order by City, Company_Name;
Here is the query result. 120 records returned. 11. Products Above Average Price This query shows how to use sub-query to get a single value (average unit price) that can be used in the outer-query.
select distinct Product_Name, Unit_Price from Products where Unit_Price > (select avg(Unit_Price) from Products) order by Unit_Price;
Here is the query result. 25 records returned. 12. Product Sales for 1997 This query shows how to group categories and products by quarters and shows sales amount for each quarter.
select distinct a.Category_Name, b.Product_Name, to_char(sum(c.Unit_Price * c.Quantity * (1 - c.Discount)), '$99,999.99') as Product_Sales, 'Qtr ' || to_char(d.Shipped_Date, 'Q') as Shipped_Quarter from Categories a inner join Products b on a.Category_ID = b.Category_ID inner join Order_Details c on b.Product_ID = c.Product_ID inner join Orders d on d.Order_ID = c.Order_ID where d.Shipped_Date between to_date('1/1/1997', 'DD/MM/YYYY') and to_date('31/12/1997','DD/MM/YYYY') group by a.Category_Name, b.Product_Name, 'Qtr ' || to_char(d.Shipped_Date, 'Q') order by a.Category_Name, b.Product_Name, Shipped_Quarter;
Here is the query result. 286 records returned. 13. Category Sales for 1997 This query shows sales figures by categories - mainly just aggregation with sub-query. The inner query aggregates to product level, and the outer query further aggregates the result set from inner-query to category level.
select Category_Name, to_char(sum(Product_Sales), '$99999,999.99') as Category_Sales from ( select distinct a.Category_Name, b.Product_Name, sum(c.Unit_Price * c.Quantity * (1 - c.Discount)) as Product_Sales, 'Qtr ' || to_char(d.Shipped_Date, 'Q') as Shipped_Quarter from Categories a inner join Products b on a.Category_ID = b.Category_ID inner join Order_Details c on b.Product_ID = c.Product_ID inner join Orders d on d.Order_ID = c.Order_ID where d.Shipped_Date between to_date('1/1/1997', 'DD/MM/YYYY') and to_date('31/12/1997','DD/MM/YYYY') group by a.Category_Name, b.Product_Name, 'Qtr ' || to_char(d.Shipped_Date, 'Q') order by a.Category_Name, b.Product_Name, Shipped_Quarter ) group by Category_Name order by Category_Name;
Here is the query result. 8 records returned. 14. Quarterly Orders by Product This query shows how to convert order dates to the corresponding quarters. It also demonstrates how SUM function is used together with CASE statement to get sales for each quarter, where quarters are converted from Order_Date column.
select a.Product_Name, d.Company_Name, to_char(Order_Date, 'YYYY') as OrderYear, sum(case to_char(c.Order_Date, 'Q') when '1' then b.Unit_Price*b.Quantity*(1-b.Discount) else 0 end) "Qtr 1", sum(case to_char(c.Order_Date, 'Q') when '2' then b.Unit_Price*b.Quantity*(1-b.Discount) else 0 end) "Qtr 2", sum(case to_char(c.Order_Date, 'Q') when '3' then b.Unit_Price*b.Quantity*(1-b.Discount) else 0 end) "Qtr 3", sum(case to_char(c.Order_Date, 'Q') when '4' then b.Unit_Price*b.Quantity*(1-b.Discount) else 0 end) "Qtr 4" from Products a inner join Order_Details b on a.Product_ID = b.Product_ID inner join Orders c on c.Order_ID = b.Order_ID inner join Customers d on d.Customer_ID = c.Customer_ID where c.Order_Date between to_date('1/1/1997', 'DD/MM/YYYY') and to_date('31/12/1997','DD/MM/YYYY') group by a.Product_Name, d.Company_Name, to_char(Order_Date, 'YYYY') order by a.Product_Name, d.Company_Name;
Here is the query result. 937 records returned. 15. Invoice A simple query to get detailed information for each sale so that invoice can be issued.
select distinct b.Ship_Name, b.Ship_Address, b.Ship_City, b.Ship_Region, b.Ship_Postal_Code, b.Ship_Country, b.Customer_ID, c.Company_Name, c.Address, c.City, c.Region, c.Postal_Code, c.Country, d.FirstName || ' ' || d.LastName as Salesperson, b.Order_ID, b.Order_Date, b.Required_Date, b.Shipped_Date, a.Company_Name, e.Product_ID, f.Product_Name, e.Unit_Price, e.Quantity, e.Discount, e.Unit_Price * e.Quantity * (1 - e.Discount) as Extended_Price, b.Freight from Shippers a inner join Orders b on a.Shipper_ID = b.Ship_Via inner join Customers c on c.Customer_ID = b.Customer_ID inner join Employees d on d.Employee_ID = b.Employee_ID inner join Order_Details e on b.Order_ID = e.Order_ID inner join Products f on f.Product_ID = e.Product_ID;
Here is the query result. 2,155 records returned. 16. Number of units in stock by category and supplier continent This query shows that case statement is used in GROUP BY clause to list the number of units in stock for each product category and supplier's continent. Note that, if only s.Country (not the case statement) is used in the GROUP BY, duplicated rows will exist for each product category and supplier continent.
select c.Category_Name as "Product Category", case when s.Country in ('UK','Spain','Sweden','Germany','Norway', 'Denmark','Netherlands','Finland','Italy','France') then 'Europe' when s.Country in ('USA','Canada','Brazil') then 'America' else 'Asia-Pacific' end as "Supplier Continent", sum(p.Units_In_Stock) as Units_In_Stock from Suppliers s inner join Products p on p.Supplier_ID>=s.Supplier_ID inner join Categories c on c.Category_ID>=p.Category_ID group by c.Category_Name, case when s.Country in ('UK','Spain','Sweden','Germany','Norway', 'Denmark','Netherlands','Finland','Italy','France') then 'Europe' when s.Country in ('USA','Canada','Brazil') then 'America' else 'Asia-Pacific' end;
Here is the query result. 21 records returned.
Here comes the end of this article series. I hope you find it useful in your day-to-day job of SQL coding! Don't forget
to download the full script which can be found at the beginning of the first two parts of this article series.
|
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |