Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 147 views

    DP8 Practice Activities - Answers

    Uploaded by

    gery sumual
    This document contains practice problems and explanations for SQL aggregate functions such as COUNT, AVG, SUM, MIN, MAX and more. It includes examples of using these functions to return average costs, total salaries, earliest and latest dates, and counts of distinct values. Null values are handled using NVL. The document provides the solutions and reasoning for each problem.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    DP8 Practice Activities - Answers

    Uploaded by

    gery sumual
    147 views2 pages
    This document contains practice problems and explanations for SQL aggregate functions such as COUNT, AVG, SUM, MIN, MAX and more. It includes examples of using these functions to return average costs, total salaries, earliest and latest dates, and counts of distinct values. Null values are handled using NVL. The document provides the solutions and reasoning for each problem.

    Original Description:

    Original Title

    DP8 Practice activities - Answers

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains practice problems and explanations for SQL aggregate functions such as COUNT, AVG, SUM, MIN, MAX and more. It includes examples of using these functions to return average costs, total salaries, earliest and latest dates, and counts of distinct values. Null values are handled using NVL. The document provides the solutions and reasoning for each problem.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    147 views2 pages

    DP8 Practice Activities - Answers

    Uploaded by

    gery sumual
    This document contains practice problems and explanations for SQL aggregate functions such as COUNT, AVG, SUM, MIN, MAX and more. It includes examples of using these functions to return average costs, total salaries, earliest and latest dates, and counts of distinct values. Null values are handled using NVL. The document provides the solutions and reasoning for each problem.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 2

    Practice Activity for DP 8.1 – 8.

    2
    Gery J. Sumual (01123574291-53)

    8.1 Group Functions


    Vocabulary: AVG, COUNT, STDDEV, group functions, MIN, VARIANCE, SUM, MAX, aggregate
    Try It / Solve It:
    1. – AVG takes a range of numeric data then return the average
    - COUNT takes a range of rows of data of any type, count them, then return the result
    - MAX takes range of rows of data of any type, then returns the maximum value in respect
    of the datatype
    - MIN takes range of rows of data of any type, then returns the minimum value in respect of
    the datatype
    - STDDEV takes a range of numeric data then measure their spread then return the result
    - SUM takes a range of numeric data then return the sum total of the values
    - VARIANCE takes a range of numeric data then measure their spread around the mean then
    return the result
    *GROUP FUNCTIONS IGNORE NULL VALUES
    2. SELECT ROUND(AVG(cost),2)
    FROM d_events;
    3. SELECT AVG(salary)
    FROM f_staffs
    WHERE manager_id = 19;
    4. SELECT TO_CHAR(ROUND(SUM(salary), 2), '$999.99') as total_salary
    FROM f_staffs
    WHERE id IN (12,9);
    5. SELECT MIN(salary) "lowest salary", MIN(hire_date) "most recent hire date",
    MAX(last_name), MIN(last_name)
    FROM employees
    WHERE department_id IN (50,60);
    6. One row
    7. Because some employees that are paid by hourly instead of salary would have null values in
    the salary column, so the function average would not take them into account. I can use a
    null function to manipulate the query and obtain the appropriate average result.
    8. The earlier date, March 30, 1969.
    9. SELECT AVG(order_total)
    FROM f_orders
    WHERE ORDER_DATE BETWEEN TO_DATE('1-Jun-2002') AND TO_DATE('21-Dec-2002');
    10. SELECT MAX(hire_date)
    FROM employees; (29-Jan-2000)
    11. SUM(operating_cost)

    8.2 COUNT, DISTINCT, NVL


    Vocabulary: COUNT, DISTINCT, COUNT(DISTINCT expression)
    Try It / Solve It:
    1. SELECT COUNT(*)
    FROM d_songs; (6)
    2. SELECT COUNT(*)
    FROM d_songs; (6)
    3. SELECT COUNT(DISTINCT song_id), COUNT(DISTINCT cd_number)
    FROM d_track_listings;(5, 4)
    4. SELECT COUNT(email)
    FROM d_clients; (3)
    5. SELECT COUNT(*)
    FROM d_partners
    WHERE auth_expense_amt IS NOT NULL; (1)
    6. 4, 3
    7. SELECT ROUND(AVG(NVL(auth_expense_amt, 100000)), 2)
    FROM d_partners;
    8. a. F
    b. T
    c. T
    d. F
    9. a. F
    b. F
    c. T
    d. F

    You might also like