SQL Basics

SQL is Structured Query Language used to interacts with RDBMS (Relational Database Management System)
The word Relational means the data is organized in such a way that there is clear relations between different tables.

SQL Constraints -
Constraint means specific rules for the data in the TABLE.
If there is any violation between constraint and data action, then data action is aborted by the constraint.

1.NOT NULL Constraint - makes column not to accept NULL values.
By default table column can hold NULL value.

2.UNIQUE Constrain - uniquely identifies each record in table
Primary Key and Unique both guarantees unique record.
Note*- Primary Key has automatically UNIQUE constraint defined on it.
Also, We can have many UNIQUE constraint per table but one Primary Key per Table.

3.PRIMARY KEY Constrain - UNIQUE, NOT NULL and one per table.

4.FOREIGN KEY Constrain - 
A FOREIGN KEY in one table points to  PRIMARY KEY in another table. 
Need** - 
1.Helps to prevent data action that would destroy links between table. 
2.Helps to prevent insertion of invalid data into table (with FK)  because other table(with PK) must have reference to it. 

5.CHECK Constraint - to limit range of value.

SQL Indexes - 
Indexes allow database application to find data fast,without reading full table.
Users cannot see Indexes.They are used to speed up the searches.
 Disadvantage - 
1. Updating table with indexes takes more time than table without indexes. 
Because indexes will also be updated
Note**-.
Hence use Index only on those tables which will be searched frequently.   

 SQL Views -
This is Virtual Table based on the result-set of SQL Statement.
Its just like table with rows and column with data from one or more table.
 
 IS NULL and IS NOT NULL -
Whenever column has no value, by default its NULL.
Null values cannot be analyzed by operators such as =,>,<
 IS NULL - when column has no value for this record.
IS NOT NULL -  when column has value for this record.

Order By -
To sort the records based on one or more column.
By Default - Ascending Order
In case of order by multiple columns 'order of  sorting base on sequence on column.

 Like - used  for Pattern
s% -  starting with letter 's'
%s - ending with letter 's'
 %sassy% - containing pattern 'sassy'

Not Like - opposite of like.

SQL JOINS -
Joins are used to combine rows from one or more TABLES

JOIN (INNER JOIN) -
It returns all the rows from two table as long as JOIN Condition is met. 
Eg.
 SELECT ORDERS.AMOUNT,ORDERS.ORDER_ID,CUSTOMERS.FNAME
FROM ORDERS
JOIN CUSTOMERS
ON ORDERS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID
As same as -
SELECT ORDERS.AMOUNT,ORDERS.ORDER_ID,CUSTOMERS.FNAME
FROM ORDERS
INNER JOIN CUSTOMERS
ON ORDERS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID

Here the sequence of  tables ORDERS and CUSTOMERS does not matter.

LEFT JOIN -
It returns all the rows from LEFT Table and matching rows from RIGHT Table.
Eg.
SELECT ORDERS.AMOUNT,ORDERS.ORDER_ID,CUSTOMERS.FNAME
FROM ORDERS
LEFT JOIN CUSTOMERS
ON ORDERS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID 

  Here the sequence of  tables ORDERS and CUSTOMERS does matter.
 LEFT Table  = ORDERS (So all rows from ORDERS Table)
 RIGHT Table= CUSTOMERS (matching rows if any)  
  

RIGHT JOIN -
It returns all the rows from RIGHT Table and matching rows from LEFT Table.
Eg.
SELECT ORDERS.AMOUNT,ORDERS.ORDER_ID,CUSTOMERS.FNAME
FROM ORDERS 
RIGHT JOIN CUSTOMERS
ON ORDERS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID 

 Here the sequence of  tables ORDERS and CUSTOMERS does matter.
 LEFT Table  = ORDERS (matching rows if any)
 RIGHT Table= CUSTOMERS (So all rows from CUSTOMERS Table )  

FULL JOIN -
It returns all the rows from RIGHT Table and all rows from LEFT Table whether joining condition is met or not .
Eg.
SELECT ORDERS.AMOUNT,ORDERS.ORDER_ID,CUSTOMERS.FNAME
FROM ORDERS 
FULL JOIN CUSTOMERS
ON ORDERS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID 

 Here the sequence of  tables ORDERS and CUSTOMERS does matter.
 LEFT Table  = ORDERS (So all rows from ORDERS Table )
 RIGHT Table= CUSTOMERS (So all rows from CUSTOMERS Table )  

SELF JOIN - 
It is used to join a table to itself as if there were two tables.
Eg.
SELECT O.ORDER_ID,O.CUSTOMER_ID
FROM ORDERS O,  ORDERS C
WHERE O.AMOUNT < C.AMOUNT

No use of Keywords ON ,JOIN etc