Scribd Logo
Upload

Welcome to Scribd!

  • 93 views

    MySQL Practical 7

    Uploaded by

    crybert zinyama
    This document discusses MySQL aggregate functions such as COUNT, MAX, MIN, SUM, and AVG. It provides examples of how to use each function to return aggregated results from a table. It also covers the GROUP BY clause for grouping rows and the HAVING clause for filtering groups. Fifteen exercises are provided to help learn how to apply these concepts to count rows, find minimum/maximum values, calculate sums and averages, group by columns, and filter groups.

    Copyright:

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

    MySQL Practical 7

    Uploaded by

    crybert zinyama
    93 views5 pages
    This document discusses MySQL aggregate functions such as COUNT, MAX, MIN, SUM, and AVG. It provides examples of how to use each function to return aggregated results from a table. It also covers the GROUP BY clause for grouping rows and the HAVING clause for filtering groups. Fifteen exercises are provided to help learn how to apply these concepts to count rows, find minimum/maximum values, calculate sums and averages, group by columns, and filter groups.

    Original Title

    MySQL Practical 7 .docx

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses MySQL aggregate functions such as COUNT, MAX, MIN, SUM, and AVG. It provides examples of how to use each function to return aggregated results from a table. It also covers the GROUP BY clause for grouping rows and the HAVING clause for filtering groups. Fifteen exercises are provided to help learn how to apply these concepts to count rows, find minimum/maximum values, calculate sums and averages, group by columns, and filter groups.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    93 views5 pages

    MySQL Practical 7

    Uploaded by

    crybert zinyama
    This document discusses MySQL aggregate functions such as COUNT, MAX, MIN, SUM, and AVG. It provides examples of how to use each function to return aggregated results from a table. It also covers the GROUP BY clause for grouping rows and the HAVING clause for filtering groups. Fifteen exercises are provided to help learn how to apply these concepts to count rows, find minimum/maximum values, calculate sums and averages, group by columns, and filter groups.

    Copyright:

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

    M.

    Chinyuku Database Systems 2023

    MySQL Practical 7- Aggregate Functions


    • The GROUP BY clause is used to combine, or group, rows with related values into elements
    of a smaller set of rows.

    • GROUP BY is often used in conjunction with SQL aggregate functions or to eliminate


    duplicate rows from a result set.

    • The GROUP BY statement is used with aggregate functions (COUNT, MAX, MIN, SUM, AVG)
    to group the result-set by one or more columns.

    • The five aggregate functions, each of which returns a single value.

    • SUM and AVERAGE may be used only on numeric data.

    Exercise
    1. Create a table to record CUIT201 marks. Use information given in the following table. Select
    appropriate primary key.

    StudentNum StudentName Assignment Test1


    1
    S100 John - 78
    S110 James 76 65
    S101 Mary 85 90
    S205 Rumbi 60 50
    S105 Anesu 50 45
    S150 David - -
    S111 Tawanda 80 75
    S140 Anesu 30 40

    COUNT
    • COUNT function counts the number of values in a column.
    • MySQL Aggregate Functions - COUNT()
    • COUNT(*) counts all the rows in a table, regardless of whether there are nulls present.
    Otherwise, each function ignores nulls.
    • If an aggregate function is applied to an empty set of values, then COUNT returns zero and
    the other aggregate functions return null.

    COUNT Syntax

    To count the number of rows in a table


    SELECT COUNT(*) FROM table_name;

    2. Count the number of rows in the student table

    SELECT COUNT(*)

    Page 1|5
    M. Chinyuku Database Systems 2023

    FROM cuit201;

     To replace the column field COUNT(*) with Total_Rows


    SELECT COUNT(*) AS Total_Rows
    FROM cuit201;

    3. Find number of students who got marks greater than 60 for Test 1
    SELECT COUNT(*) AS Total_Test1
    FROM cuit201
    WHERE Test1>60;

     you want to know the number of values in a given column (excluding null values), specify
    the column name rather than the asterisk.
    SELECT COUNT( Test1) AS Total_Test1
    FROM cuit201
    WHERE Test1>60;

    4. How many students wrote assignment 1?

    SELECT COUNT(Assignment1) AS Total_Assignment1 FROM cuit201;

    5. If pass mark is 55. Find the number of students who failed both the test and assignment
    SELECT COUNT(*) AS Fail
    FROM cuit201
    WHERE Assignment1<55 AND Test1 <55;

     DISTINCT is used if duplicate values are to be eliminated before the function is applied. The
    DISTICT keyword can be added to the COUNT function
    6. Find the number of marks less than 55 for test1

    SELECT COUNT(DISTINCT StudentName) AS Fail_Test1


    FROM cuit201
    WHERE Test1 <55;

    Page 2|5
    M. Chinyuku Database Systems 2023

     The result removed duplicate ANESU

    MIN
    • MIN finds the smallest value in a column.
    • MySQL Aggregate Functions - MIN()

    7. Find the maximum mark for Test 1

    SELECT MIN(Test1)
    FROM cuit201;

    MAX
    • MAX finds the largest value in a column.
    • MySQL Aggregate Functions - MAX()
    8. Find the maximum mark for Assignment 1

    SELECT MAX(Assignment1)
    FROM cuit201;

    SUM
    • SUM sums the values in a column.
    • MySQL Aggregate Functions - SUM()

    9. Find the total of Test 1 marks


    SELECT SUM(Test1)
    FROM cuit201;

    AVG
    • AVG calculates the average of the values in a column.
    • MySQL Aggregate Functions - AVG()
    10. Find the average of assignment 1 marks

    11. Find the total and average marks for students with names Anesu.

    SELECT SUM(Test1), SUM(Assignment1), AVG(Test1), AVG(Assignment1)


    FROM cuit201
    WHERE StudentName = 'Anesu';

    Page 3|5
    M. Chinyuku Database Systems 2023

    Group by Clause
     Groups rows in an intermediate results table where the values in those rows are the same
    for one or more columns

    12. Create the customer table with the following information

    Customer
    Account FirstName LastName Address City
    A1234 Mary Jonas 10 Second Street Chinhoyi
    B1245 John Jonas 101 first street Harare
    C1234 Mary Javu Nguruve avenue Chinhoyi
    A1100 James Ruke 11th Street Chinhoyi
    A3210 Gewa Mupo 24 Wine Street Bulawayo
    B2200 Mary Donni 32 Magamba Way Harare
    C4000 Jaji Chasi 41 Shepure Street Masvingo
    D1213 Anesu Lunga 12 Mopane Road Karoi

    13. Count the number of customers with addresses in each city. List the cities.

    SELECT City, COUNT(City)


    FROM Customer
    GROUP BY City;

    The HAVING Clause


     The HAVING clause acts like a WHERE clause, but it identifies groups, rather than rows, that
    meet a criterion.
     Therefore, you will usually see a HAVING clause following a GROUP BY clause.
    14. Find only cities with more than one customer

    SELECT City, COUNT(City)


    FROM Customer
    GROUP BY City HAVING COUNT(City) > 1;

    Page 4|5
    M. Chinyuku Database Systems 2023

    15. List, in alphabetical order, the ProductID and the average price for each product for selected
    products P100 and P123 having an average price greater than 50

    SELECT ProductID, AVG(Price)


    FROM offers
    WHERE ProductID IN ('P100', 'P123')
    GROUP BY ProductID
    HAVING AVG (Price) > 50
    ORDER BY ProductID;

    General syntax of the SELECT statement used in DML

    Page 5|5

    You might also like