Database - SQL fundamental concepts

The difference of null, zero and blank space: 

They are not the same. The null value is a value which is unavailable, unassigned, unknown or not applicable.

Zero is a number and blank space is a character.

DDL, DML and DCL commands :

DDL(Data Definition Language): CREATE, ALTER, DROP  [CAD]
DML(Data Manupulation language): SELECT, INSERT, DELETE, UPDATE [SIDU]
DCL (Data Control Langauge):   GRANT, REVOKE

Grant and Revoke: Grant gives a privilege to the user and revoke takes back privileges granted from the user.

ACID property in a database: 

 A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity.

A: Atomicity:
This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. Refers to the transactions which are either completely successful or failed. Here a transaction refers to a single operation. So, even if a single transaction fails, then the entire transaction fails and the database state is left unchanged. There is no midway i.e. transactions do not occur partially.

C:Consistency:

So, this basically makes sure that the transaction never leaves the database without completing its state. It refers to the correctness of a database. Referring to the example above,
The total amount before and after the transaction must be maintained.
Total before T occurs = 500 + 200 = 700.
Total after T occurs = 400 + 300 = 700.
Therefore, the database is consistent. Inconsistency occurs in case T1 completes but T2 fails. As a result, T is incomplete.

I: Isolation:

This property ensures that multiple transactions can occur concurrently without leading to the inconsistency of the database state. That means parallel execution. So basically each and every transaction is independent.

D: Durability:

The durability makes sure that your committed transaction is never lost. So, this guarantees that even if there is a power loss, crash or any sort of error the server can recover from an abnormal termination. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.

Normalization

Normalization is the process of organizing data to avoid duplication and redundancy. 

1NF: No repeating groups within rows. A relation is in first normal form if every attribute in that relation is singled valued attribute. There shouldn't be any multi-valued attribute.  

2NF: A relation is in 2NF iff it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table. Every non-key (supporting) column value is dependent on the whole primary key.

3NF:  Dependent solely on the primary key and no other non-key (supporting) column value. Example: If the table contains 

Employee_ID,
Employee_Name,

ZIP Code,

City,

State, Road

Here, We can make a separate table for Address containing the 

ZIP -> Address. And 

Employee_ID -> ZIP Code 

Inner Join vs Outer Join
Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.

An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

A    B
-    -
1    3
2    4
3    5
4    6
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join:

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join:

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4
Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3     |  3
4     |  4
null |  5
null |  6
Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1  | null
   2  | null
   3  |    3
   4  |    4
null |    6

null |    5

Reference: stackoverflow

Union vs Union All

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.