Advanced Group Operators - Exercise

Objective: Place the appropriate group function into each query.

You will receive four points for this exercise. This exercise is auto-scored. When you are finished, click Submit.

You will be shown four queries that do not contain group functions (or do not yet produce the correct grouped result). Your task is to copy each query, then modify it to match the requested output using the appropriate operators or aggregate functions.

For each question:

1. Read the query and the goal description.
2. Copy the query into the text area at the bottom of the page.
3. Modify it to meet the goal (use DISTINCT, UNIQUE, HAVING, and/or aggregate functions as needed).

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
---- - -----------------
120259 1             40.45
120259 3             52.66
120259 4              61.9
2000 1            108.03
2000 1             20.47

Modify the query so you get one row per year and quarter. Based on the output above, your revised 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.2025
        16 Bird Treat                             4.5
        18 Dog Collar                            5.65
        20 Kitty Package                           55
        21 Puppy Package                           78

Modify the query to show one line per product that sold more than one time. Your output must include:

1. product ID
2. product name
3. total dollars sold for that product
4. number of times the product was sold

This means your final query will return four columns.

Find each user’s most recent product update. Here is the initial query:

SELECT UPDATED_BY_USER,
LAST_UPDATE_DATE
FROM PRODUCT P

The result looks like this:

UPDATED_BY_USER                LAST_UPDA
------------------------------ ---------
JANET
MARK                           11-JUN-2025
JANET                          10-MAY-2025
JANET                          21-SEP-2025
MARK                           15-SEP-2025
JANET                          01-JUL-2025
MARK                           30-OCT-2025
JANET                          28-FEB-2025
MARK                           15-APR-2025
JANET                          23-OCT-2025
MARK                           24-NOV-2025
JANET                          01-JAN-00
HENRY                          22-FEB-00
MARK                           15-DEC-2025
JANET                          19-NOV-2025
MARTIN                         30-MAY-2025
MARTIN                         14-JUL-2025
MARTIN                         14-JUL-2025
HENRY                          11-JUN-2025
JANET                          14-OCT-2025
MARTIN                         29-AUG-2025
MARTIN                         25-AUG-2025
MARTIN                         05-AUG-2025
MARTIN                         05-AUG-2025
MARTIN                         05-AUG-2025
JANET                          15-JUL-2025

Modify the query to show the most recent update date for each user. Your results should show one line per user.

Who is buying things at the pet store? Your initial query lists all customers that have made a purchase:

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

Revise the query so you see one record per customer. You are not interested in the sales amount, so drop that column.

Hint: You can accomplish this goal without using GROUP BY.

This exercise is auto-scored, which means you should try to match the expected answer format. Use all capital letters and avoid leading spaces at the beginning of lines. Do not use a column or table alias unless instructed.

When you have completed the exercise, click Submit.