0090

      Y Box Games        Bounce                          25.55        21.13
      Y Box Games        Comic Book Heroes               25.76        22.14
      Y Box Games        Endurance Racing                42.58        34.29
      Y Box Games        Finding Fido                     16.6        12.79
      Y Box Games        Martial Arts Champions          25.76        22.14
      Y Box Games        Smash up Boxing                 38.64         33.2

      9 rows selected.

Project Summary

While the final example, and a few transformations along the way, could be considered more along the lines of intermediate SQL, take some time to study each of the steps and the resulting changes to the output. Once you understand the different components of the SQL statement that evolved in this project, you'll be well on your way to unleashing the power of SQL.

Progress Check

1. Retrieve a list of all product categories, subcategories, names, and list prices where the list price is greater than $100. Order this query by product category, subcategory, and name.

2. List the aggregate total sales for every product category and subcategory group using the ANSI natural join syntax.

3. Retrieve a list of all customers IDs and last names where the customer only has one entry in the SALES table.

Progress Check Answers

1. An ordered list of all product categories, subcategories, names, and list prices greater than $100 are returned by the following query:

SQL> select prod_category, prod_subcategory, prod_name, prod_list_price
  2  from  products
  3  where  prod_list_price > 100
  4  order by prod_category, prod_subcategory, prod_name;

2. The SQL statement that will return the aggregate amount sold for every product category and subcategory using the ANSI SQL natural join is shown here:

SQL> select prod_category, prod_subcategory, sum(amount_sold)
2 from products natural join sales
3 group by prod_category, prod_subcategory;

3. The list of all customer IDs and last names for customers that only had one sale is returned by the following SQL statement:

SQL> select c.cust_id, cust_last_name, count(*)
  2  from customers c, sales s
  3  where c.cust_id = s.cust_id
  4  group by c.cust_id, cust_last_name
  5  having count(*) = 1;

Team Fly

0090