Advanced Group Operators - Exercise

You will receive four points for this exercise. The exercise is auto-scored; when you have completed the exercise, click the Submit button to receive full credit.

You will be shown four queries that do not contain any group functions. You will be asked to modify each query to incorporate group functions. A description of the desired result will help you decide which group functions to use.

For each question, look at the query. Read the description of the change you will make. Copy the query and modify it to use at least one group function, based on the description.

Summarize your quarterly sales. Here is the basic query for listing sales figures:

SELECT TO_CHAR(SALES_DATE,'YYYY') YEAR,
TO_CHAR(SALES_DATE,'Q') Q,
TOTAL_SALE_AMOUNT
FROM CUSTOMER_SALE
ORDER BY TO_CHAR(SALES_DATE,'YYYY'), TO_CHAR(SALES_DATE,'Q')

The result when you execute the query is:

YEAR Q TOTAL_SALE_AMOUNT
---- - -----------------
1999 1             40.45

1999 3             52.66

1999 4              61.9

2000 1            108.03

2000 1             20.47

Your job is to modify this query so that you have a summarized list with one row per year and quarter. Based on the result above, the modified query should return four rows.

Find frequently sold products. The following query lists products that have been sold at the pet store.

SELECT P.PRODUCT_ID, 
P.PRODUCT_NAME,
S.SALE_AMOUNT
FROM PRODUCT P,
SALE_ITEM S
WHERE P.PRODUCT_ID = S.PRODUCT_ID
ORDER BY P.PRODUCT_ID

The result lists all products sold.

PRODUCT_ID PRODUCT_NAME                   SALE_AMOUNT
---------- ------------------------------ -----------
         2 Box Turtle                           15.5
         3 Fish Tank                            24.95
         4 Dog Food                              5.15
         4 Dog Food                              5.15
         8 Canary                                  29
        10 Bird Food                              3.5
        14 Chew Toy                               6.5
        14 Chew Toy                               6.5
        15 Catnip                                1.99
        16 Bird Treat                             4.5
        18 Dog Collar                            5.65
        20 Kitty Package                           55
        21 Puppy Package                           78

You want to modify the query to show you one line for each product that sold more than one time. You will list the product ID, the name, the total dollars sold for each product, and the number of times that product was sold. This means you will add one more column to the query, for a total of four columns.

Find users’ most recent update of the products. Here is your initial query, which shows you the user name and dates of updates that each user has done in the database.

SELECT UPDATED_BY_USER, 
LAST_UPDATE_DATE 
FROM PRODUCT P 

The result looks like this:

UPDATED_BY_USER                LAST_UPDA
------------------------------ ---------
JANET

MARK                           11-JUN-99

JANET                          10-MAY-99

JANET                          21-SEP-99

MARK                           15-SEP-99

JANET                          01-JUL-99

MARK                           30-OCT-99

JANET                          28-FEB-99

MARK                           15-APR-99

JANET                          23-OCT-99

MARK                           24-NOV-99

JANET                          01-JAN-00

HENRY                          22-FEB-00

MARK                           15-DEC-99

JANET                          19-NOV-99

MARTIN                         30-MAY-99

MARTIN                         14-JUL-99

MARTIN                         14-JUL-99

HENRY                          11-JUN-99

JANET                          14-OCT-99

MARTIN                         29-AUG-99

MARTIN                         25-AUG-99

MARTIN                         05-AUG-99

MARTIN                         05-AUG-99

MARTIN                         05-AUG-99

JANET                          15-JUL-99

Who is buying things at the pet store? Your initial query lists all your customers that have made a purchase and how much money he or she spent.

SELECT C.CUST_ID, 
C.LASTNAME,
C.STATE,
TOTAL_SALE_AMOUNT
FROM CUSTOMER C,
CUSTOMER_SALE CS
WHERE C.CUST_ID = CS.CUST_ID

The results look like this:

  CUST_ID LASTNAME                  ST TOTAL_SALE_AMOUNT

--------- ------------------------- -- -----------------
        1 Black                     NE            108.03

       25 Redding                   HI             40.45

       26 Lee                       CA             52.66

        1 Black                     NE              61.9

       26 Lee                       CA             20.47

Your goal is to revise this query so that you see one record for each customer. You are not interested in the sales amount so you can drop that column from the query.
Hint: You can accomplish this goal without using the GROUP BY clause.

This exercise is auto-scored, which means that you must try to match the answer I have used in the exercise. Please use all capital letters and no spaces at the beginning of any line. Do not use a column or table alias unless instructed to do so.