TRAINING (SQL-5) - Type of SQL (DQL, DML, DDL, DCL) & Single, Multiple row (Aggregate) functions

SQL (Structured Query Language):
Structured Query Language (SQL) is the technique/language to perform different kind of operations (like select, insert, update, delete, create, alter, drop, grant, revoke) on tables, rows, columns & cells values in a RDBMS.

SQL could further be categorized in below categories:

1.) DQL (Data Query Language) - SELECT

2.) DML (Data Maniplulation Language) - INSERT, UPDATE, DELETE

3.) DDL (Data Definition Language) - CREATE, ALTER, DROP

4.) DCL (Data Control Language) - GRANT, REVOKE

SQL Functions:
As with any other language SQL too came bundled with several delivered functions which could be used to transform the data in a way that is different from the way data is stored in the database.
A function is a type of formula whose result is one of two things, either
i) A Transformation, such as change name of student to upper case letters.
or
ii) An Information, such as length, count etc of a word or result.

SQL Functions can be categorized in two categories:
1.) Single Row Functions
Works on one row at a time.

    A.1.) Character Function
    Requires alphanumeric inputs.
        A.1.1) LOWER
        A.1.2) LPAD
        A.1.3) RPAD
        A.1.4) SUBSTR
        A.1.5) RPAD
        A.1.6) INSTR
        A.1.7) CONCAT
        A.1.8) TRANSLATE
        A.1.9) REPLACE
        A.1.10) LTRIM

    A.2.) Number Function
    Requires numbers inputs.
        A.2.1) ABS
        A.2.2) SIGN
        A.2.3) MOD
        A.2.4) ROUND
        A.2.5) TRUNC

    A.3.) DATE & Conversion Function
    Used to convert the datatypes.
        A.3.1) TO_NUMBER
        A.3.2) TO_CHAR
        A.3.3) TO_DATE
        
    A.4.) NVL & DECODE Function
    Special task functions.
        A.4.1) NVL
        A.4.2) DECODE

2.) Multiple Row Function (Aggregate Function)
Works on group of rows, and aggregate or group the data to produce a single result.

    B.1.) Number Function
    Requires numbers inputs.
        A.1.1) COUNT
        Used to count the rows in results.
Count can be used with entire table using count(*) or with a table column like count(column name). However when results are displayed when * is used it counts NULL as well but when used with column name it excludes the rows that contains NULL values.

        A.1.2) SUM
        A.1.3) MIN
        A.1.4) MAX
        A.1.5) AVG


DISTINCT is often used in conjunction with aggregate functions to operate on distinct values withn a group. For example the below query and results.

SELECT COUNT(DISTINCT section_id), COUNT(section_id)
FROM enrollment;

COUNT(DISTINCT section_id)     COUNT(section_id)
----------------------------------     -------------------------
64                                            226