SQL Commands

SQL Commands:

• The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP
• DDL – Data Definition Language:
  • CREATE: Creates a new table, a view of a table, or other object in the database
  • ALTER: Modifies an existing database object, such as a table
  • DROP: Deletes an entire table, a view of a table or other objects in the database
• DML – Data Manipulation Language:
  • SELECT: Retrieves certain records from one or more tables.
  • INSERT: Creates a record
  • UPDATE: Modifies records
  • DELETE: Deletes records
• DCL – Data Control Language:
  • GRANT: Gives a privilege to the user
  • REVOKE: Takes back privileges granted by the user

SQL:

• Constraints are the rules enforced on data columns in a table. These 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 either be column level or table level. Column level constraints are applied only to one column, whereas, table level constraints are applied to the entire table
• Following are some of the most commonly used constraints available in SQL
  • NOT NULL Constraint: Ensures that a column cannot have a NULL value
  • DEFAULT Constraint: Provides a default value for a column when none is specified
  • UNIQUE Constraint: Ensures that all the values in a column are different
  • PRIMARY Key: Uniquely identifies each row/record in a database table
  • FOREIGN Key: Uniquely identifies a row/record in any other database table
  • CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions
  • INDEX: Used to create and retrieve data from the database very quickly

Data Integrity:

• Entity Integrity: There are no duplicate rows in a table
• Domain Integrity: Enforces valid entries for a given column by restricting the type, the format, or the range of values
• Referential integrity: Rows cannot be deleted, which are used by other records
• User-Defined Integrity: Enforces some specific business rules that do not fall into an entity, domain or referential integrity

Database Normalization:

• Database normalization is the process of efficiently organizing data in a database.
• There are two reasons of this normalization process:
  • Eliminating redundant data, for example, storing the same data in more than one table
  • Ensuring data dependencies make sense
• Normalization consists of a series of guidelines that help guide us in creating a good database structure
• There are in total 5 normal form, but in general, the third normal form is more than enough
• First Normal Form (1NF):
  • Define the data items required, because they become the columns in a table: You must define the data items. This means looking at the data to be stored, organizing the data into columns, defining what type of data each column contains and then finally putting the related columns into their own table
  • Place the related data items in a table: The next step is ensuring that there are no repeating groups of data
  • Ensure that there are no repeating groups of data: we need to ensure that there are no repeating groups of data
  • Ensure that there is a primary key: create a primary key for each table
• Second Normal Form (2NF):
  • Data should meet all the rules for 1NF and there must be no partial dependences of any of the columns of the primary key
  • Data should meet all the rules for 1NF and there must be no partial dependences of any of the columns of the primary key
  • Data should not have partial dependencies of primary keys and columns. If the data does, then it has to be separated into multiple tables, Example:
    
    CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL, CUST_NAME VARCHAR (20) NOT NULL, ORDER_ID INT NOT NULL, ORDER_DETAIL VARCHAR (20) NOT NULL, SALE_DATE DATETIME, PRIMARY KEY (CUST_ID, ORDER_ID) );

    1 2 3 4 5 6 7 8

    CREATE TABLE CUSTOMERS(      CUST_ID    INT              NOT NULL,      CUST_NAME VARCHAR (20)      NOT NULL,      ORDER_ID   INT              NOT NULL,      ORDER_DETAIL VARCHAR (20)  NOT NULL,      SALE_DATE  DATETIME,      PRIMARY KEY (CUST_ID, ORDER_ID) );

  • Above single table should be separated in 3 tables like:
    
    CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL, CUST_NAME VARCHAR (20) NOT NULL, PRIMARY KEY (CUST_ID) );

    1 2 3 4 5

    CREATE TABLE CUSTOMERS(      CUST_ID    INT              NOT NULL,      CUST_NAME VARCHAR (20)      NOT NULL,      PRIMARY KEY (CUST_ID) );

    
    
    CREATE TABLE ORDERS( ORDER_ID INT NOT NULL, ORDER_DETAIL VARCHAR (20) NOT NULL, PRIMARY KEY (ORDER_ID) );

    1 2 3 4 5

    CREATE TABLE ORDERS(      ORDER_ID   INT              NOT NULL,      ORDER_DETAIL VARCHAR (20)  NOT NULL,      PRIMARY KEY (ORDER_ID) );

    
    
    CREATE TABLE CUSTMERORDERS ( CUST_ID INT NOT NULL, ORDER_ID INT NOT NULL, SALE_DATE DATETIME, PRIMARY KEY (CUST_ID, ORDER_ID) );

    1 2 3 4 5 6

    CREATE TABLE CUSTMERORDERS (      CUST_ID    INT              NOT NULL,      ORDER_ID   INT              NOT NULL,      SALE_DATE  DATETIME,      PRIMARY KEY (CUST_ID, ORDER_ID) );

• Third Normal Form (3NF):
  • Data should meet all the rules of second normal form.
  • All non primary fields are dependent on the primary key
  • Removal of transitive dependencies, Example:
    
    CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL, CUST_NAME VARCHAR (20) NOT NULL, DOB DATE, STREET VARCHAR(200), CITY VARCHAR(100), STATE VARCHAR(100), ZIP VARCHAR(12), EMAIL_ID VARCHAR(256), PRIMARY KEY (CUST_ID) );

    1 2 3 4 5 6 7 8 9 10 11

    CREATE TABLE CUSTOMERS(      CUST_ID INT NOT NULL,      CUST_NAME VARCHAR (20) NOT NULL,      DOB DATE,      STREET VARCHAR(200),      CITY VARCHAR(100),      STATE VARCHAR(100),      ZIP VARCHAR(12),      EMAIL_ID VARCHAR(256),      PRIMARY KEY (CUST_ID) );

  • The dependency between the zip code and the address is called as a transitive dependency
    
    CREATE TABLE ADDRESS( ZIP VARCHAR(12), STREET VARCHAR(200), CITY VARCHAR(100), STATE VARCHAR(100), PRIMARY KEY (ZIP) );

    1 2 3 4 5 6 7

    CREATE TABLE ADDRESS(      ZIP VARCHAR(12),      STREET VARCHAR(200),      CITY VARCHAR(100),      STATE VARCHAR(100),      PRIMARY KEY (ZIP) );

  • The advantages of removing transitive dependencies are mainly twofold
    • First, the amount of data duplication is reduced and therefore your database becomes smaller.
    • The second advantage is data integrity. When duplicated data changes, there is a big risk of updating only some of the data, especially if it is spread out in many different places in the database

JOINS:

• The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. There are different types of joins available in SQL:
• INNER JOIN: returns rows when there is a match in both tables
  • The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row
  • Returns rows when there is a match in both tables
    
    SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;

    1	SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;

• LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table
  • The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table
  • This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate
    
    SELECT table1.column1, table2.column2 FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field;

    1	SELECT table1.column1, table2.column2 FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field;

• RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table
  • The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in the left table; the join will still return a row in the result, but with NULL in each column from the left table
  • This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate
    
    SELECT table1.column1, table2.column2 FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field;

    1	SELECT table1.column1, table2.column2 FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field;

• FULL JOIN: returns rows when there is a match in one of the tables
  • The SQL FULL JOIN combines the results of both left and right outer joins
  • The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side
    
    SELECT table1.column1, table2.column2 FROM table1 FULL JOIN table2 ON table1.common_field = table2.common_field;

    1	SELECT table1.column1, table2.column2 FROM table1 FULL JOIN table2 ON table1.common_field = table2.common_field;

     

• SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement
  • The SQL SELF JOIN is used to join a table to itself as if the table were two tables; temporarily renaming at least one table in the SQL statements
    
    SELECT a.column_name, b.column_name FROM table1 a, table1 b WHERE a.common_field = b.common_field;

    1	SELECT a.column_name, b.column_name FROM table1 a, table1 b WHERE a.common_field = b.common_field;

     

• CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables
  • The CARTESIAN JOIN OR CROSS JOIN returns the Cartesian product of the sets of records from two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to either True or where the join-condition is absent from the statement
  • This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate
    
    SELECT table1.column1, table2.column2 FROM table1, table2 [, table3 ]

    1	SELECT table1.column1, table2.column2 FROM  table1, table2 [, table3 ]

     

UNION clause/operator:

• The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows. To use this UNION clause, each SELECT statement must have:
  • The same number of columns selected
  • The same number of column expressions
  • The same data type and
  • Have them in the same order
    
    SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]

    1 2 3

    SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]

UNION ALL Clause:

• The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows
• The same rules that apply to the UNION clause will apply to the UNION ALL operator
  
  SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]

  1 2 3

  SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]

Various Syntax in SQL: