Scribd Logo
Upload

Welcome to Scribd!

  • 134 views

    SQL Function 1

    Uploaded by

    vijayap71
    SQL has built-in functions for calculations and data aggregation. The document describes SQL aggregate functions like AVG(), COUNT(), FIRST(), LAST(), MAX(), MIN(), and SUM() which return a single value calculated from a column. It also describes SQL scalar functions like UCASE(), LCASE(), MID(), LEN(), ROUND(), NOW(), and FORMAT() which return a single value based on an input. It provides examples of using these functions with the Northwind sample database to get average price, number of rows, first/last values, maximum/minimum values, and total sums from tables.

    Copyright:

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

    SQL Function 1

    Uploaded by

    vijayap71
    134 views9 pages
    SQL has built-in functions for calculations and data aggregation. The document describes SQL aggregate functions like AVG(), COUNT(), FIRST(), LAST(), MAX(), MIN(), and SUM() which return a single value calculated from a column. It also describes SQL scalar functions like UCASE(), LCASE(), MID(), LEN(), ROUND(), NOW(), and FORMAT() which return a single value based on an input. It provides examples of using these functions with the Northwind sample database to get average price, number of rows, first/last values, maximum/minimum values, and total sums from tables.

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    SQL has built-in functions for calculations and data aggregation. The document describes SQL aggregate functions like AVG(), COUNT(), FIRST(), LAST(), MAX(), MIN(), and SUM() which return a single value calculated from a column. It also describes SQL scalar functions like UCASE(), LCASE(), MID(), LEN(), ROUND(), NOW(), and FORMAT() which return a single value based on an input. It provides examples of using these functions with the Northwind sample database to get average price, number of rows, first/last values, maximum/minimum values, and total sums from tables.

    Copyright:

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

    SQL Function 1

    Uploaded by

    vijayap71
    SQL has built-in functions for calculations and data aggregation. The document describes SQL aggregate functions like AVG(), COUNT(), FIRST(), LAST(), MAX(), MIN(), and SUM() which return a single value calculated from a column. It also describes SQL scalar functions like UCASE(), LCASE(), MID(), LEN(), ROUND(), NOW(), and FORMAT() which return a single value based on an input. It provides examples of using these functions with the Northwind sample database to get average price, number of rows, first/last values, maximum/minimum values, and total sums from tables.

    Copyright:

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

    SQL Functions

    SQL has many built-in functions for performing calculations on data.

    SQL Aggregate Functions


    SQL aggregate functions return a single value, calculated from values in a column.
    Useful aggregate functions:

    AVG() - Returns the average value

    COUNT() - Returns the number of rows

    FIRST() - Returns the first value

    LAST() - Returns the last value

    MAX() - Returns the largest value

    MIN() - Returns the smallest value

    SUM() - Returns the sum

    SQL Scalar functions


    SQL scalar functions return a single value, based on the input value.
    Useful scalar functions:

    UCASE() - Converts a field to upper case

    LCASE() - Converts a field to lower case

    MID() - Extract characters from a text field

    LEN() - Returns the length of a text field

    ROUND() - Rounds a numeric field to the number of decimals specified

    NOW() - Returns the current system date and time

    FORMAT() - Formats how a field is to be displayed

    Tip: The aggregate functions and the scalar functions will be explained in details in the next
    chapters.

    Demo Database_AVG()
    In this tutorial we will use the well-known Northwind sample database.
    Below is a selection from the "Products" table:

    ProductI
    D

    ProductName

    SupplierI CategoryI
    D
    D

    Unit

    Price

    Chais

    10 boxes x 20 bags 18

    Chang

    24 - 12 oz bottles

    Aniseed Syrup

    12 - 550 ml bottles 10

    Chef Anton's Cajun Seasoning 2

    48 - 6 oz jars

    21.35

    Chef Anton's Gumbo Mix

    36 boxes

    25

    SQL Queries: Scearios


    The following SQL statement gets the average value of the "Price" column from the
    "Products" table:

    SELECT AVG(Price) AS PriceAverage FROM Products;

    19

    The following SQL statement selects the "ProductName" and "Price" records that have an
    above average price:

    SELECT ProductName, Price FROM Products


    WHERE Price>(SELECT AVG(Price) FROM Products);

    Demo Database_Count()
    In this tutorial we will use the well-known Northwind sample database.
    Below is a selection from the "Orders" table:
    OrderID

    CustomerID

    EmployeeID

    OrderDate

    ShipperID

    10265

    1996-07-25

    10266

    87

    1996-07-26

    10267

    25

    1996-07-29

    SQL Queries: Scearios


    The following SQL statement counts the number of orders from "CustomerID"=7 from the
    "Orders" table:

    SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders


    WHERE CustomerID=7;

    The following SQL statement counts the total number of orders in the "Orders"
    table:
    SELECT COUNT(*) AS NumberOfOrders FROM Orders;

    The following SQL statement counts the number of unique customers in the
    "Orders" table:

    SELECT COUNT(DISTINCT CustomerID) AS NumberOfCustomers FROM


    Orders;

    Demo Database_First()
    In this tutorial we will use the well-known Northwind sample database.
    Below is a selection from the "Customers" table:
    Custom
    erID

    CustomerName

    ContactNa
    me

    Address

    City

    PostalC Count
    ode
    ry

    12209

    Germa
    ny

    Ana Trujillo

    Avda. de la
    Mxico
    05021
    Constitucin 2222 D.F.

    Mexico

    Antonio Moreno
    Taquera

    Antonio
    Moreno

    Mataderos 2312

    Mxico
    05023
    D.F.

    Mexico

    Around the Horn

    Thomas
    Hardy

    120 Hanover Sq.

    London WA1 1DP UK

    Berglunds snabbkp

    Christina
    Berglund

    Berguvsvgen 8

    Lule

    Alfreds Futterkiste

    Maria Anders Obere Str. 57

    Ana Trujillo
    Emparedados y
    helados

    Berlin

    S-958 22

    Swede
    n

    SQL Queries: Scearios


    The following SQL statement selects the first value of the "CustomerName" column from the
    "Customers" table:

    SELECT FIRST(CustomerName) AS FirstCustomer FROM Customers;

    Demo Database_Last()
    In this tutorial we will use the well-known Northwind sample database.
    Below is a selection from the "Customers" table:
    Custom
    erID

    CustomerName

    ContactNa
    me

    Address

    City

    PostalC Count
    ode
    ry

    12209

    Germa
    ny

    Ana Trujillo

    Avda. de la
    Mxico
    05021
    Constitucin 2222 D.F.

    Mexico

    Antonio Moreno
    Taquera

    Antonio
    Moreno

    Mataderos 2312

    Mxico
    05023
    D.F.

    Mexico

    Around the Horn

    Thomas
    Hardy

    120 Hanover Sq.

    London WA1 1DP UK

    Berglunds snabbkp

    Christina
    Berglund

    Berguvsvgen 8

    Lule

    Alfreds Futterkiste

    Maria Anders Obere Str. 57

    Ana Trujillo
    Emparedados y
    helados

    SQL Queries: Scearios

    Berlin

    S-958 22

    Swede
    n

    The following SQL statement selects the last value of the "CustomerName"
    column from the "Customers" table:
    SELECT LAST(CustomerName) AS LastCustomer FROM Customers;

    Demo Database_MAX()
    In this tutorial we will use the well-known Northwind sample database.
    Below is a selection from the "Products" table:

    ProductI
    D

    ProductName

    SupplierI CategoryI
    D
    D

    Unit

    Price

    Chais

    10 boxes x 20 bags 18

    Chang

    24 - 12 oz bottles

    Aniseed Syrup

    12 - 550 ml bottles 10

    Chef Anton's Cajun Seasoning 2

    48 - 6 oz jars

    21.35

    Chef Anton's Gumbo Mix

    36 boxes

    25

    19

    SQL Queries: Scearios


    The following SQL statement gets the largest value of the "Price" column from the "Products"
    table:

    SELECT MAX(Price) AS HighestPrice FROM Products;

    Demo Database_MIN()
    In this tutorial we will use the well-known Northwind sample database.
    Below is a selection from the "Products" table:
    ProductI
    D

    ProductName

    SupplierI CategoryI
    D
    D

    Unit

    Price

    Chais

    10 boxes x 20 bags 18

    Chang

    24 - 12 oz bottles

    Aniseed Syrup

    12 - 550 ml bottles 10

    Chef Anton's Cajun Seasoning 2

    48 - 6 oz jars

    21.35

    Chef Anton's Gumbo Mix

    36 boxes

    25

    SQL Queries: Scearios


    The following SQL statement gets the smallest value of the "Price" column from the
    "Products" table:

    SELECT MIN(Price) AS SmallestOrderPrice FROM Products;

    19

    Demo Database_SUM()
    In this tutorial we will use the well-known Northwind sample database.
    Below is a selection from the "OrderDetails" table:
    OrderDetailID

    OrderID

    ProductID

    Quantity

    10248

    11

    12

    10248

    42

    10

    10248

    72

    10249

    14

    10249

    51

    40

    SQL Queries: Scearios


    The following SQL statement finds the sum of all the "Quantity" fields for the
    "OrderDetails" table:
    SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

    You might also like