SQL

Fundamentals---dropVSdelete.sql

/*
    DROP
    It will delete all data and the table structure as well.
    Those are auto-commited statements, no rolling back!

    DELETE
    It will delete the data but table structure will remain the same
    and we can still rollback the data.
    It also allows us to use WHERE to only delete some data.
*/

Fundamentals---keywords.sql

/*
    RDBMS (Relational Database Management System)
    It is the basis for SQL and for all modern database systems
    like MS SQL Server / Oracle / MySQL.

    It is a database management system that is based on the relational
    model as introduced by E.F. Codd.


    TABLE
    It is basically a colllection of related data entries
    and it consists of numerous columns and rows.
    It is the most common and simplest form of data storage
    in a relational database.

    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+
    |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
    |  2 | Khilan   |  25 | Delhi     |  1500.00 |
    |  3 | kaushik  |  23 | Kota      |  2000.00 |
    |  4 | Chaitali |  25 | Mumbai    |  6500.00 |
    |  5 | Hardik   |  27 | Bhopal    |  8500.00 |
    |  6 | Komal    |  22 | MP        |  4500.00 |
    |  7 | Muffy    |  24 | Indore    | 10000.00 |
    +----+----------+-----+-----------+----------+


    FIELD 
    Every table is broken up into smaller entities called fields.
    A field is a column in a table that is designed to maintain
    specific information about every record in the table.

    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+

    Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS 
    table consist of ID, NAME, AGE, ADDRESS and SALARY.


    RECORD / ROW
    A record / row is each individual entry that exists in a table.

    +----+----------+-----+-----------+----------+
    |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
    +----+----------+-----+-----------+----------+


    COLUMN
    A column is a vertical entity in a table that contains
    all information associated with a specific field in a table.
    
    +-----------+
    | ADDRESS   |
    +-----------+
    | Ahmedabad |
    | Delhi     |
    | Kota      |
    | Mumbai    |
    | Bhopal    |
    | MP        |
    | Indore    |
    +----+------+


    CONSTRAINTS
    Those are rules enforced on data columns on a table.
    They are used to limit the type of data that can go into a table.
    This ensures the accuracy and reliability of the data in the database.
    
    Constraints can be column level or table level.

    Example constraints:
    a) NOT NULL - ensures that a column cannot have a NULL value
    b) DEFAULT - provides a default value for a column when none is specified
    c) UNIQUE - ensures that all the values in a column are different
    d) PRIMARY KEY - uniquely identifies each rows/records in any another table
    e) FOREIGN KEY - uniquely identifies a row/record in any another table
    f) CHECK - ensures that all values in a column satisfy certain conditions
    g) INDEX - used to create and retrieve data from the database very quickly


    INDEX
    It is a database structure that you can use to improve the performance
    of database activity.
    Database can have one or more indexes associated with it.

    An index is defined by a field expression that you specify when you create the index.
    Typically field expression is a single field name, like EMP_ID.
*/