Custom Search
 




Inside This Article
1How the data types in Access Northwind are converted to Oracle
2Building Oracle Northwind database objects
   
3. Queries to generate aggregated data for Oracle Northwind database

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© GeeksEngine.com




Inside This Article
1How the data types in Access Northwind are converted to Oracle
2Building Oracle Northwind database objects
   
3. Queries to generate aggregated data for Oracle Northwind database
Other Recent Articles from the Database SQL category:

1.Get single records when duplicate records exist
2.Find duplicate values or non-duplicate values in a table
3.How to get Top 1 record from Oracle by using PL/SQL
4.How to get Top N rows from Oracle by using SQL
5.How the data types in Access Northwind are converted to Oracle
6.How to do cross table update in Oracle
7.Export Northwind Access database to MySQL via ODBC

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