Introduction to SQL

 What is SQL?

         . Stands for Structured Query Language.
         . Used to perform operations on database like creating, updating, deleting tables or records.
         . It is just a language which helps us to use database using commands.
         . We need to install any of the databases like Oracle or My-SQL to use SQL.

 Types of Commands in SQL:

      1. Data Definition Language(DDL): 

           . Used to create, modify and drop structure of database objects. 
           Commands: CREATE, ALTER, DROP, TRUNCATE
             . CREATE command is used to create a table.
             . Syntax:
                CREATE TABLE table_name(col1 datatype, col2 datatype,.....,coln datatype);

      2. Data Manipulation Language(DML):

           . Used to store, modify data from databases. 
           Commands: INSERT, UPDATE, DELETE
             INSERT command is used to insert the new records into the table.
             . Syntax:
                INSERT INTO table_name VALUES(val1, val2,.....,valn);
             . UPDATE statement is used to modify the existing records in table.
             . Syntax:
                UPDATE table_name
                SET col1=val1, col2=val2
                WHERE condition;

             . Used to delete existing records in the table.
             . Syntax: To delete according to any condition:
                DELETE FROM table_name
                WHERE condition;

             . Syntax: To delete complete records in the table:
                DELETE FROM table_name;

      3. Data Query Language(DQL):

           Used to retrieve data from the databases.
           Only one command- SELECT
             . SELECT statement is used to read data from the tables.
             . The data from the database is called as result-set. 
             . Syntax: To read all the records from the table:
                SELECT *
                FROM table_name; 
             . Syntax: To read particular column data from the table:
               SELECT col1, col2, col3...
               FROM table_name;

      4. Transaction Control Language(TCL):

            Used to handle changes that effect databases like commiting, setting a point and rollback to previous change.
            Commands: SAVEPOINT, ROLLBACK, COMMIT

      5. Data Control Language(DCL):

            Used to implement security on database objects.
            Commands: GRANT, REVOKE

      6. Other commands:


            . WHERE clause is used to filter the records.
            . Used to extract only those records that fulfill a specified condition.
            . WHERE can be used in other commands like UPDATE, DELETE etc.
            . Syntax:
               SELECT column(s)
               FROM table_name
               WHERE condition;

             AND, OR, NOT Operators:

            . WHERE can be combined with AND, OR and NOT.
            . AND and OR operators are used to filter the records based on the conditon.
            . AND displays a record if all conditions are True.
            . OR  displays a record if any of the conditions is True.
            . NOT displays a record if condition is False.
            Syntax: For AND, OR:
              SELECT column(s)
              FROM table_name
              WHERE condition1 AND condition2 AND condition3;

             . Syntax: For NOT:
              SELECT column(s)
              FROM table_name
              WHERE NOT condition;
          LIKE Operator:
            . WHERE clause uses LIKE operator to search for a specified pattern in a column.
            . % represents zero, one or multiple characters.
            .  _ represents single character.
            . Syntax:
              SELECT column(s)
              FROM table_name
              WHERE column LIKE pattern;
            . Example: WHERE name LIKE 'a%' returns all names starting with 'a'.
                              WHERE name LIKE '_r%' returns names where 'r' is in 2nd position.
          IN Operator:

            . Used to specify multiple values in WHERE clause.
            . Can be used in place of 'OR' condition.
            . Syntax: 
              SELECT column(s)
              FROM table_name
              WHERE column_name IN(val1, val2);

        BETWEEN Operator:

            . Used in combination with WHERE clause.
            . BETWEEN operator selects values within given range.
            . Values can be numbers, text or dates.
            . Begin and End values are included in the range.
            . Syntax:
              SELECT column(s)
              FROM table_name
              WHERE column_name BETWEEN val1 AND val2;

        ORDER BY:

         . It is a keyword used to sort the result in ascending or descending order.
         . Sorts in ascending order by default, but to sort in descending order use DESC keyword.
         . Syntax:
           SELECT column(s)
           FROM table_name
           ORDER BY column_name ASC/DESC;

         MIN() returns smallest value of selected column.
         . MAX() returns largest value of selected column.
         COUNT() returns number of rows that matches condition
         . AVG() returns average values of a numeric column.
         . SUM() returns total sum of numeric column.

         . Syntax: MIN()
           SELECT MIN(column_name)
           FROM table_name;

         . Syntax: MAX()
           SELECT MAX(column_name)
           FROM table_name;

         . Syntax: COUNT()
           SELECT COUNT(column_name)
           FROM table_name;

         . Syntax: AVG()
           SELECT AVG(column_name)
           FROM table_name;
         . Syntax: SUM()
           SELECT SUM(column_name)
           FROM table_name;

       GROUP BY:

         . Often used with aggregate functions like COUNT, MAX, MIN, SUM, AVG to group result-set by one or more columns.
         . Syntax:
           SELECT column(s)
           FROM table_name
           WHERE condition
           GROUP BY column
           ORDER BY column;


         . WHERE keyword cannot be used with aggregate functions.
         . So, to filter aggregate function values, we use HAVING clause.
         . Syntax:
            SELECT column(s)
            FROM table_name
            WHERE condition
            GROUP BY column
            HAVING condition
            ORDER BY column;

  What are Constraints?

         These are used to limit the data that goes into the table.
         . Can be applied at column level or table level.
         1. NOT NULL:

              . Ensures that column cannot have a null value.
              . Example: CREATE TABLE emp(id int NOT NULL, name varchar(20)); 
         2. DEFAULT:

              Provides a default value when no data is specified.
              . Example:  CREATE TABLE transaction(id int, sent_on date DEFAULT);

         3. PRIMARY KEY:

               Doesn't allow duplicate and null value in the column.
               . It helps in reducing the duplicate values so that during referential time, it is easy to refer any data.
               . Primary key can be assigned only once in a table.
               . It is treated as a parent table when it contains a primary key.
               . Example: CREATE TABLE employee(empID int Primary key, name varchar(20));
         4. FORIEGN KEY: 

               . References the the data from the table assigned with primary key.
               . Data in the column assigned with Foreign Key can contain duplicate or null values.
               . It is mandatory that the data should match with the column assigned with Primary key in its parent table.
               . A table with a foreign key is considered as a child table.
               . When relation is established between two tables, we cannot perform updation and deletion on the Parent table directly, so we need to define Foreign key                    with ON UPDATE/DELETE CASCADE rules.
               . Example:  CREATE TABLE department( depID int, name varchar(20), empID int Foreign Key REFERENCES employee(empID);

         5. UNIQUE: 

               Ensures there is no duplicate data but allows null values.
               . A table may contain number of UNIQUE constraints declared.
               . Example:  CREATE TABLE emp(ID int UNIQUE, name varchar(20));

         6. CHECK:
               . Ensures all values in column satisfies a condition.
               . Example: CREATE TABLE voter(Id int Primary key, name varchar(20), age int CHECK(age>=18));


         . It is a technique to organize the contents of tables by reducing redundancy.
         . Involves dividing a database into two or more tables, defining a relationship between them.
         . Main objective is to isolate data, so that any modification can be made in just one table.
         . We can extract data from any table using the relationships among tables.


            . Otains storage efficiency.
            . Increases consistency.
            . Reduces redundancy.
            . Fewer null values so that it obtains referential integrity.


            . Slower performance.
            . Requires more joins to get desired result.
            . Higher the level of normalization, greater the number of tables in database.

       Levels of Normalization:

         1. First Normal Form(1 NF):
             . It ensures that every column is unique.
             . Data in the fields must be single values.
             . Data should be entered into the column of same type.
              . In the above example, 1st table is with multiple values initially.
              . As per 1 NF, data must be singular.

         2. Second Normal Form(2 NF):

              . A database is said be in 2 NF if it satisfies the following:
              . It must be in 1 NF.
              . Repeating data should not be present.
              . There should not be partial dependency.
              . In the above example, 1st table consists of repetitive information of HOD and Tel for each and every student.
              . So, this can be divided into another table called Branch table and it can be referenced by Branch column.

         3. Third Normal Form(3 NF):

              . A database is said to be in 3 NF if it satisfies the following:
              . It must be in 2 NF.
              . All non Primary key columns must be dependent on Primary key column.
              . Whatever the columns which are not dependent directly on Primary key should be moved to another table.
              . It means transitive dependency of columns should be avoided.
              . In the above example, Details Table has Primary Key Id, on which only few of the columns are dependent on.
              . B_Code depends on Bank, Postal Code depends on Address.
              . So dividing the tables in such a way that each non primary key is dependent on primary key.

          4. Boyce Codd Normal Form (BCNF):

              . It is advanced version and stricter than 3 NF.
              . A database is said to be in BCNF if it satisfies the following:
              . It must satisfy 3 NF.
              . For every functional dependency X->Y, X must be the superkey in the table.
              . The dependencies must be in numerical form for faster access.
              . In the above example, Employee table has fields like Dept_Id, Incharge which are not functionally dependent on Emp_id.
              . So, tables must be divided as per they maintain functional dependency on a key.
              . Finally, mapping table is required for quicker access to the other fields.

 What are JOINS?

         . Joins are used to combine records from different table which have similar fields.
         . Let us consider two tables 'employee' and 'department' with a common column called 'EmpId'.

         . There are different types of joins as listed below with their syntax: 
         1. INNER JOIN:

              . It selects the records which have matching values in both tables.
              . Syntax:
                            SELECT column(s)
                            FROM table1 INNER JOIN table2
                            ON table1.column_name=table2.column_name;

          2. LEFT JOIN:

              Returns all records from left table and matched records from right table.
              . Result is NULL from right table if there is no match. 
              . Syntax:
                            SELECT column(s)
                            FROM table1 LEFT JOIN table2
                            ON table1.column_name=table2.column_name;

           3. RIGHT JOIN:

              Returns all records from right table and matched records from left table.
              . Result is NULL from left table if there is no match. 
              . Syntax:
                            SELECT column(s)
                            FROM table1 RIGHT JOIN table2
                            ON table1.column_name=table2.column_name;

           4. FULL OUTER JOIN:

              Returns all records when there is match either in left or right table records.
              . If there are rows in left that do not match with right and vice-versa, those rows will also be listed as well with NULL values.
              . Syntax:
                            SELECT column(s)
                            FROM table1 FULL OUTER JOIN table2
                            ON table1.column_name=table2.column_name;   

           5. SELF JOIN:
              It is a regular join but the table is joined with itself.
              . Syntax:
                            SELECT column(s)
                            FROM table1 T1, table1 T2
                            WHERE condition;

  What are Views?

        . Views are the virtual tables created based on the tables in the database.
        . It contains rows and columns similar to the real tables.
        . It is created as an object whenever we run the query related to it.


               . Whenever we are repeatedly retrieving the same data from multiple tables using joins, it takes long time to write query.
               . In that case, we can create a view which acts as virtual table.
               . Simplifies the query.
               . Security: If we want to give access to only limited columns of a table, we can create a view on that particular column and give it to others.
               . Abstraction: Can make views on only essential data hiding unwanted data to load each time query is running.


               . Complexity issues if the views are based on multiple tables.
               . If the structure of base table is changed, then view should also be updated.
               . DML queries would not work if the views are based on multiple tables.

      Creating View:

               . Consider a base table "emp".
               . Syntax:
                              CREATE VIEW view_name
                              AS SELECT column(s)
                              FROM table_name;

      Creating View based on multiple tables:

               . We use join query to create view on multiple tables.
               Here view called "my_view" is created based on two tables 'employee' and 'department'.

      Retrieving data from view:

               . It is similar to the select query in tables.
               . Syntax:
                              SELECT column(s)
                             FROM view_name;

      Retrieving data from view based on multiple tables:

               . It is similar to the normal table.

      Inserting data into view:

                 . When we insert data into view, it is automatically inserted into its base table.
                 . Syntax:
                                INSERT INTO view_name VALUES(val1, val2,..,valn);         

               . We can observe that record is inserted into the base table also.

      Inserting record in a view based on multiple tables:

               . If the view is based on multiple tables, basic DML queries are restricted.

      Updating a record in a view:

               . It is similar to the normal tables.
               . Once data is updated in the view, it is automatically effected in its base table.
               . Syntax:
                              UPDATE view_name set col_name=value
                              WHERE condition;

      Updating a record in a view based on multiple tables:

               . While updating, the effected values is changed only in a single table.
               It gives an error if we try to update any value which is based on multiple tables.

      Deleting a record from view:

               . Similar to the normal tables.
               . Once the data is deleted from the view, it is also deleted from the base table.
               . Syntax: 
                             DELETE FROM view_name
                             WHERE condition;

      Deleting a record from view based on multiple tables:

               . We cannot delete the data from a view which is based on multiple tables. 
               . It gives an error if we try to delete any record from view.

  What is an Index?

     . Index in SQL is used to retrieve data quickly.
     . Index is created on column which is frequently used to retrieve data.
     . It works similar to the index of a page to locate Chapter easily by looking into it.
     . Index is made up index nodes that are organized in B-Tree(Balanced Tree) structure.
     . It is hierarchical in nature with root node on top and physical node (data) on the bottom as shown in below figure.
     . Whenever we perform any SELECT operation, it scans the data by looking into the index nodes.
     . Without defining indexes, data is scanned from starting to ending which is called 'table scan' which is time taking.


               . Speeds up SELECT query.
               . Helps to make a column with unique values.
               . They can be used for sorting as they arrange data in sorted order.


               . Decreases performance on INSERT, UPDATE, DELETE queries.
               . This is because on each updation, index value has to be modified.
               . Indexes take additional disk space.

      Creating Index:

               . Syntax for creating index is as follows:
                 CREATE INDEX index_name ON table_name(column_name);

        Dropping Index:

                . Syntax for dropping index is as follows:
                  DROP INDEX table_name.index_name;

  What is a Stored Procedure?

        . Stored Procedure is a set of SQL statements which is assigned by a name and it can be reused for multiple times.
        . Stored procedures are compiled only once.
        . It reduces the traffic as a single statement can execute the query which contains multiple lines.
        . Saves time to write the query which is used frequently.

        Creating Stored Procedure:

                Following syntax is used to create Stored procedure:
                   CREATE PROCEDURE procedure_name
                   . (set of sql statements)
               . Let us consider we need to retrieve records from a table called employee where EmpId is >=30.
               . We use the following query for each time we need to look at the result.
               . So we can create a Stored Procedure to execute this query as show below:

        Executing Stored Procedure:

                     . For each time we want to execute the same query, we need not to write all the commands.
                     . We can just execute the stored procedure by its name as follows:
                        Execute procedure_name;   

        Creating parameterized Stored Procedure:

                  . If we execute which contains data like inserting values, we can also create a procedure to it.
                  . Following example shows how to create a procedure for inserting values.
                 . We can run the parameterized query as follows: