Scribd Logo
Upload

Welcome to Scribd!

  • 106 views

    SQL Aggregate Functions

    Uploaded by

    Sunil
    Aggregate functions in SQL summarize data across multiple rows and return a single value. Some common aggregate functions include COUNT, SUM, AVERAGE, MAX, and MIN. A subquery allows querying results from another SQL statement and is often used in the WHERE clause with comparison operators. Different types of joins in SQL like INNER, LEFT, RIGHT, and FULL OUTER JOIN allow combining data from multiple tables.

    Copyright:

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

    SQL Aggregate Functions

    Uploaded by

    Sunil
    106 views52 pages
    Aggregate functions in SQL summarize data across multiple rows and return a single value. Some common aggregate functions include COUNT, SUM, AVERAGE, MAX, and MIN. A subquery allows querying results from another SQL statement and is often used in the WHERE clause with comparison operators. Different types of joins in SQL like INNER, LEFT, RIGHT, and FULL OUTER JOIN allow combining data from multiple tables.

    Original Description:

    sql aggrigate function book

    Original Title

    AGGRIGATE FUNCTIONS

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Aggregate functions in SQL summarize data across multiple rows and return a single value. Some common aggregate functions include COUNT, SUM, AVERAGE, MAX, and MIN. A subquery allows querying results from another SQL statement and is often used in the WHERE clause with comparison operators. Different types of joins in SQL like INNER, LEFT, RIGHT, and FULL OUTER JOIN allow combining data from multiple tables.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    106 views52 pages

    SQL Aggregate Functions

    Uploaded by

    Sunil
    Aggregate functions in SQL summarize data across multiple rows and return a single value. Some common aggregate functions include COUNT, SUM, AVERAGE, MAX, and MIN. A subquery allows querying results from another SQL statement and is often used in the WHERE clause with comparison operators. Different types of joins in SQL like INNER, LEFT, RIGHT, and FULL OUTER JOIN allow combining data from multiple tables.

    Copyright:

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

    SQL AGGREGATE

    FUNCTIONS
    WHAT IS AGGREGATE
    FUNCTION IN SQL?

    Aggregate functions helps to summarize the large volumes of data.

    This function can produced a single value for an entire group or table.

    They operate on sets of rows and return results based on groups of rows.
    LIST OF AGGREGATE
    FUNCTIONS
    1. COUNT
    2.SUM
    3. AVERAGE
    4. MAX
    5. MIN
    1. COUNT () function:
    The SQL COUNT function returns the number of rows in a table satisfying the
    criteria specified in the WHERE clause. It sets on the number of rows or non NULL
    column values.

    SQL Syntax:
    COUNT(*) , COUNT( [ALL|DISTINCT] expression )
    WHAT IS SUBQUERY IN SQL?

    A subquery is a SQL query nested inside a larger query.


    A subquery may occur in :
     - A SELECT clause
     - A FROM clause
     - A WHERE clause

    The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement
    or inside another subquery.
    A subquery is usually added within the WHERE Clause of another SQL SELECT
    statement.
    You can use the comparison operators, such as >, <, or =. The comparison operator can
    also be a multiple-row operator, such as IN, ANY, or ALL.
    WHAT IS SUBQUERY IN SQL?
    (CONT’D)
    A subquery is also called an inner query or inner select, while the statement
    containing a subquery is also called an outer query or outer select.
    The inner query executes first before its parent query so that the results of an inner
    query can be passed to the outer query
    You can use a subquery in a SELECT, INSERT, DELETE, or UPDATE statement to
    perform the following tasks:
    Compare an expression to the result of the query.
    Determine if an expression is included in the results of the query.
    Check whether the query selects any rows.
    WHAT IS SUBQUERY IN SQL?
    (CONT’D)
    The subquery (inner query) executes once before the main query (outer query)
    executes.
    The main query (outer query) use the subquery result.
    Syntax:
    SUBQUERIES: GUIDELINES
    There are some guidelines to consider when using subqueries :
    A subquery must be enclosed in parentheses. 
    A subquery must be placed on the right side of the comparison operator. 
    Subqueries cannot manipulate their results internally, therefore ORDER BY clause
    cannot be added into a subquery. You can use an ORDER BY clause in the main
    SELECT statement (outer query) which will be the last clause.
    Use single-row operators with single-row subqueries. 
    If a subquery (inner query) returns a null value to the outer query, the outer query
    will not return any rows when using certain comparison operators in a WHERE
    clause.
    TYPE OF SUBQUERIES

    Single row subquery : Returns zero or one row.


    Multiple row subquery : Returns one or more rows.
    Multiple column subqueries : Returns one or more columns.
    Correlated subqueries : Reference one or more columns in the outer SQL statement.
    The subquery is known as a correlated subquery because the subquery is related to
    the outer SQL statement.
    Nested subqueries : Subqueries are placed within another subquery.
    SQL INNER JOIN
    SQL INNER JOIN Keyword
    The INNER JOIN keyword selects all rows from both tables as long as there is a
    match between the columns in both tables.
    SQL INNER JOIN Syntax
    SELECT column_name(s)
    FROM table1
    INNER JOIN table2
    ON table1.column_name=table2.column_name;
    SQL LEFT JOIN
    SQL LEFT JOIN Keyword
    The LEFT JOIN keyword returns all rows from the left table (table1), with the
    matching rows in the right table (table2). The result is NULL in the right side when
    there is no match.
    SQL LEFT JOIN Syntax
    SELECT column_name(s)
    FROM table1
    LEFT JOIN table2
    ON table1.column_name=table2.column_name;
    SQL RIGHT JOIN
    SQL RIGHT JOIN Keyword
    The RIGHT JOIN keyword returns all rows from the right table (table2), with the
    matching rows in the left table (table1). The result is NULL in the left side when
    there is no match.
    SQL RIGHT JOIN Syntax
    SELECT column_name(s)
    FROM table1
    RIGHT JOIN table2
    ON table1.column_name=table2.column_name;
    SQL FULL OUTER
    SQL FULL OUTER JOIN Keyword
    The FULL OUTER JOIN keyword returns all rows from the left table (table1) and
    from the right table (table2).
    The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT
    joins.
    SQL FULL OUTER JOIN Syntax
    SELECT column_name(s)
    FROM table1
    FULL OUTER JOIN table2
    ON table1.column_name=table2.column_name;

    You might also like