Oracle SQL

DQL statements are used to retrieve data from the database.

• 1. SELECT

  It is used to retrieve data from the database, by selecting all the records.
  SYNTAX:
  SELECT * FROM TABLE_NAME;

• 2. *(PROJECTION)

  It is used to retrieve data from the database, by selecting all the columns.
  SYNTAX:
  SELECT * FROM TABLE_NAME;

• 3. FROM

  It is a keyword and it is used to specify the table name.
  SYNTAX:
  SELECT * FROM TABLE_NAME;

• 4. WHERE

  Where clause is used to give condition to the query and it will execute the output based on the condition.
  SYNTAX:
  SELECT * FROM TABLE_NAME
  WHERE CONDITION;

• 5. ORDER BY

  Order by clause is used to rearrange the outputs either in ascending or in descending order.
  SYNTAX:
  SELECT * FROM TABLE_NAME
  WHERE CONDITION
  ORDER BY COLUMN_NAME ASC/DESC;

• 6. GROUP BY

  Group by clause is used to divide the table into multiple groups and it will execute each output for each group.
  *(Whatever column we are using inside the select statement along with multi-row function the same column we have to use inside the Group by clause)
  SYNTAX:
  SELECT COLUMN_NAME1, COLUMN_NAME2, MULTI ROW FUNCTION FROM TABLE_NAME
  WHERE CONDITION
  GROUP BY(COLUMN_NAME1, COLUMN_NAME2)
  ORDER BY COLUMN_NAME ASC/DESC;

• 7. HAVING

  Having clause it is used to create groups for the group by condition and if there is a condition for multi-row functions we have to go for the having clause.
  *(For using Having clause Group by condition is mandatory)
  SYNTAX:
  SELECT COLUMN_NAME1, COLUMN_NAME2, MULTI ROW FUNCTION FROM TABLE_NAME
  WHERE CONDITION
  GROUP BY(COLUMN_NAME1, COLUMN_NAME2)
  HAVING CLAUSE
  ORDER BY COLUMN_NAME ASC/DESC;

• EXAMPLE FOR DQL

  SELECT DEPTNO, MAX(SAL) FROM EMP
  WHERE JOB = 'SALESMAN' OR JOB = 'MANAGER'
  GROUP BY(DEPTNO)
  HAVING MAX(SAL)> 2500
  ORDER BY DEPTNO ASC;

It is a dummy table and it consist of only one column and one record.

-->Syntax:-
      SELECT 'LITERAL' FROM DUAL;

  EXAMPLES:-
      SELECT 'Surya' FROM DUAL;

It is used to rename column name and table name for temporarily.

      For Column:-
                  SELECT COLUMN_NAME1 ALIASNAME1,
                  COLUMN_NAME2 ALIASNAME2
                  FROM TABLLE_NAME;

      For Table:-
                  SELECT * FROM TABLE_NAME ALIASNAME;

It is used to restrict invalid data to enter into the table is known as "DATA Integrity".

-->Types of DATA Integrity

      1. DATA TYPES

      2. CONSTRAINTS / ATTRIBUTES

It is used to specify which type of data to be stored in particular column is known as Data type.

• 1. NUMBER / INT

  It is used to store only numeric values.
  - The Maximum size of number data type is 38 blocks.
  SYNTAX:
  NUMBER (SIZE);

• 2. CHAR

  It is used to store alphanumeric values along with some special symbols.
  - Wherever we are using the character data type we must have to mention size for the data type.
  - Maximum size for character data type is 10,000 blocks.
  EX:- 'a-z', 'A-Z', '0-9', '!, @, #, $, %, ^, &, *'.
  SYNTAX:
  CHAR (SIZE)

• 3. VARCHAR

  It is used to store alphanumeric values along with some special symbols.
  - The maximum size for VARCHAR data type is 2000 bytes.
  SYNTAX:
  VARCHAR(SIZE)

• 4. VARCHAR2

  It is used to store alphanumeric values along with some special symbols.
  - Maximum size for VARCHAR2 data type is 4000 bytes.
        (4000 bytes = 4000 * 256 = 1024000 CHAR)
  SYNTAX:
  VARCHAR2(SIZE)

• 5. DATE

  It is used to store date format values and there is no size for date type.
  SYNTAX:
  DD-MON-YY
  DD-MON-YYYY
  DD-MM-YY
  DD-MM-YYYY
  FMDAY-MON-YYYY
  DY-MON-YYYY

• 6. LOB

  LOB stands for Large Object Data type and that refers to the large data items like text, Graphic images, video clips and also sound waveforms.
  

• 6.1. CLOB

  CLOB stand for Character Large Object data type.
  - It is used to store large amount of data up to 4GB of size.
  - We shouldn't mention size for CLOB data type.
  

• 6.2. BLOB

  It is used to store binary values such as images, mp3, documents, etc. up to 4GB of size.
  

It is used to give additional condition to the column to store the data.

• 1. UNIQUE CONSTRAINT

  It will not allow duplicate values as records.
  Unique constraints allows not null values.
  SYNTAX:
  CREATE TABLE TABLE_NAME
  (COLUMN_NAME DATATYPE(SIZE) UNIQUE);

• 2. NOT NULL CONSTRAINT

  It will allow duplicate vlaues but it wont allow null values.
  SYNTAX:
  CREATE TABLE TABLE_NAME
  (COLUMN_NAME1 DATATYPE(SIZE) UNIQUE,
  COLUMN_NAME2 DATATYPE(SIZE) NOT NULL);

• 3. CHECK CONSTRAINT

  It is used to give condition to the column and it will store data based on condition.
  SYNTAX:
  CREATE TABLE TABLE_NAME
  (COLUMN_NAME1 DATATYPE(SIZE) UNIQUE,
  COLUMN_NAME2 DATATYPE(SIZE) NOT NULL,
  COLUMN_NAME3 DATATYPE(SIZE) CHECK(CONDITION));

• 4. PRIMARY KEY CONSTRAINT

  - Primary key is also known as a unique representation column in a table.
  - It is a combination of UNIQUE & NOT NULL Constraints.
  - It won't allow duplicate values and null values.
  - There should be only one primary key in a table.
  - It is used to build a relation between two or more tables with the help of Foreign Key using common columns.
  SYNTAX:
  CREATE TABLE TABLE_NAME
  (COLUMN_NAME1 DATATYPE(SIZE) UNIQUE,
  COLUMN_NAME2 DATATYPE(SIZE) NOT NULL,
  COLUMN_NAME3 DATATYPE(SIZE) CHECK(CONDITION),
  COLUMN_NAME4 DATATYPE(SIZE) PRIMARY KEY);

• 5. FOREIGN KEY CONSTRAINT

  - Foreign key constraint is also known as reference integrity constraint.
  - The key which we are using for foreign key is "REFERENCES".
  - Foreign key allows duplicate values and null values.
  - Foreign key is not a combination of unique and not null constraints.
  SYNTAX:
  CREATE TABLE TABLE_NAME
  (COLUMN_NAME1 DATATYPE(SIZE) UNIQUE,
  COLUMN_NAME2 DATATYPE(SIZE) NOT NULL,
  COLUMN_NAME3 DATATYPE(SIZE) CHECK(CONDITION),
  COLUMN_NAME4 DATATYPE(SIZE) PRIMARY KEY,
  COLUMN_NAME DATATYPE(SIZE) REFERENCES ParentTABLE_NAME(COLUMN_NAME));

•     EXAMPLE FOR CONSTRAINTS

  SYNTAX:
  CREATE TABLE parent_table (
  parent_id NUMBER(10) PRIMARY KEY,
  parent_name VARCHAR2(50) NOT NULL);
  
  CREATE TABLE child_table (
  child_id NUMBER(10) PRIMARY KEY,
  child_name VARCHAR2(50) NOT NULL,
  parent_id NUMBER(10) REFERENCES parent_table(parent_id));

All the Data Definition Language statements are used to create the structure of the table and it is used to modify the structure of the table.
- All the Data Definition Language statements are permanent changes.

• 1. CREATE

  - This statement is used to CREATE a table in database and it is a permanent change.
  SYNTAX:
  CREATE TABLE TABLE_NAME
  (COLUMN_NAME1 DATATYPE(SIZE) CONSTRAINT,
  COLUMN_NAME2 DATATYPE(SIZE) CONSTRAINT,
  COLUMN_NAME3 DATATYPE(SIZE) CONSTRAINT,);
  

• 2. RENAME TABLE NAME

  - This statement is used to Rename old table name with new table name.
  SYNTAX:
  RENAME OLD_TABLE_NAME TO NEW_TABLE_NAME;
  

• 3. DROP TABLE

  - This statement is used to Delete table from the database.
  (OR)
  - This statement is used to Delete structur of the table.
  SYNTAX:
  DROP TABLE TABLE_NAME;

• 4. TRUNCATE TABLE

  - This statement it is used to entire records present in a table and structure of the table remains the same.
  SYNTAX:
  TRUNCATE TABLE TABLE_NAME;

• 5. FLACHBACK TABLE

  - This statement it is used to restore deleted table from the recycle bin.
  SYNTAX:
  FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;
  

• 6. PURGE

  - This statement is used to delete deleted table from recycle bin.
  SYNTAX:
  PURGE TABLE TABLE_NAME;

• 7. ALTER

  - Alter statement it is used to modify structure of the table.
  

• 7.1. ADD SINGLE COLUMN TO AN EXISTING TABLE

  SYNTAX:
  ALTER TABLE TABLE_NAME
  ADD (COLUMN_NAME DATATYPE(SIZE) CONSTRAINT);
  

• 7.2. ADD MULTIPLE COLUMNS TO AN EXISTING TABLE

  SYNTAX:
  ALTER TABLE TABLE_NAME
  ADD (COLUMN_NAME1 DATATYPE(SIZE) CONSTRAINT
  COLUMN_NAME2 DATATYPE(SIZE) CONSTRAINT);
  

• 7.3. DROP SINGLE COLUMN

  SYNTAX:
  ALTER TABLE TABLE_NAME
  DROP COLUMN COLUMN_NAME;
  

• 7.4. DROP MULTIPLE COLUMNS FROM TABLE

  SYNTAX:
  ALTER TABLE TABLE_NAME
  DROP (COLUMN_NAME1 COLUMN_NAME2);
  

• 7.5. MODIFY DATATYPE FOR A COLUMN

  SYNTAX:
  ALTER TABLE TABLE_NAME
  MODIFY(COLUMN_NAME NEW_DATA_TYPE(SIZE));
  

• 7.6. MODIFY CONSTRAINTS FOR A COLUMN

  SYNTAX:
  ALTER TABLE TABLE_NAME
  MODIFY(COLUMN_NAME DATA_TYPE(SIZE) NEW_CONSTRAINT);
  

• 7.7. RENAME COLUMN NAME

  SYNTAX:
  ALTER TABLE TABLE_NAME
  RENAME OLD_COLUMN_NAME TO NEW_COLUMN_NAME;
  

• 7.8. DROP PRIMARY KEY

  SYNTAX:
  ALTER TABLE TABLE_NAME
  DROP PRIMARY KEY;
  

- All Data Manipulation Statements are used to manage the database.
- Manage stands for storing data, retrieving data, updating data, deleting data.

• 1. INSERT DATA

  - It is used to store data in table as per the requirement.
  SYNTAX:
  1. INSERT INTO TABLE_NAME VALUES('V1', 'V2', 'V3');
  2. INSERT INTO TABLE_NAME VALUES(&COLUMN_NAME1, &COLUMN_NAME2, &COLUMN_NAME30;)
  3. INSERT INTO TABLE_NAME (COLUMN_NAME1, COLUMN_NAME2)
          VALUES(&V1, &V2);
  Inserting VALUES IN MULTIPLE TABLES USING ONE SYNATAX:-
  INSERT ALL INTO TABLE_NAME1(COLUMN_NAME1, COLUMN_NAME2) VALUES(&V1, &V2);
          INTO TABLE_NAME2(COLUMN_NAME1, COLUMN_NAME2) VALUES(&V1, &V2)
         (SELECT * FROM DUAL);

• 2. UPDATE DATA

  - This statement is used to update old data with new data.
  SYNTAX:
  UPDATE TABLE_NAME SET COLUMN_NAME = NEW VALUE;
  

• 3. DELETE DATA

  - This statement is used to delete unwanted data from a table.
  SYNTAX:
  1. DELETE TABLE_NAME;
  2. DELETE FROM TABLE WHERE CONDITION;
  

- All data manuplation language statements are temporary changes to make them permanent changes we have to use TCL.

• 1. COMMIT

  - it is used to save DML statements permanently.
  SYNTAX:
  COMMIT;
  

• 2. ROLLBACK

  - It is used to restore DML changes.
  SYNTAX:
  ROLLBACK;
  

• 3. SAVE POINT

  - It is used to mark the location at DML transaction and after creating savepoint previous DML changes we can't restore.
  SYNTAX:
  1. SAVEPOTINT SAVEPOTINT_NAME;
  2. ROLLBACK TO SAVEPOTINT SAVEPOTINT_NAME;

- DCL stands for Data Control Language. It comprises a set of commands used to control access to data stored in the database.
- The primary focus of DCL is on permissions, security, and other related aspects of database management.

• 1. GRANT

  - It is used to give our database access to another user.
  SYNTAX:
  GRANT SELECT ON TABLE_NAME TO USERNAME;
  

• 2. REVOKE

  - It is used to take given access back from another user.
  SYNTAX:
  REVOKE SELECT ON TABLE_NAME FROM USERNAME;
  

- Operators are some special symbols and all are used to perform some operations on operand.

• 1. ARITHMETIC OPERATORS

  - Arithmetic operators consists of +, -, *, /.
  - Arithmetic operator are used to perform mathmatical operation on operands and expressions.
  - All arithmetic operators we have to use only inside the select statement and we can use arithmetic operators in side where condition but along with relational operators.
  EXAMPLE:
  SELECT EMP.*, SAL*12, (SAL*12)/365 FROM EMP ORDER BY SAL DESC;
  

• 2. RELATIONAL OPERATORS

  - Relational operators consists of <, >, <=, >=, !=, =.
  - All relational operators have to use only inside the where condition.
  - Relational operators are used to give the condition to the query and it will execute the output as per the condition.
  - Relational operator can't use inside the select statement.
  EXAMPLE:
  SELECT * FROM EMP
  WHERE HIREDATE = '03 - DEC - 1981';
  

• 3. LOGICAL OPERATORS

  - Whenever we are having multiple conditions we have to go for logical operators.
  - Logical operators consist of AND, OR, NOT operators.
  - All logical operators we have to use only inside the WHERE condition.
  
  AND OPERATOR
  
  - Whatever we are having multiple condition it all conditions are true, AND operator will print output.
  EXAMPLE
  SELECT * FROM EMP
  WHERE
  JOB = 'MANAGER' AND DEPTNO= 30;
  
  OR OPERATOR
  
  - Whatever we are having multiple condition if any one conditions is true, OR operator will print output.
  Note:-
        - Whatever we have multiple condition for Similar column we have to use OR operator.
  EXAMPLE
  SELECT * FROM EMP
  WHERE
  SAL > 2000 AND(JOB = 'MANAGER OR JOB = 'ANALYST');
  

• 4. SPECIAL OPERATORS

  - Special operator consists of IN, BETWEEN, LIKE, IS, NOT IN, NOT BETWEEN, NOT LIKE, IS NOT.
  
  IN OPERATOR
  
  - Instead of using multiple times OR operator, we can use IN operator.
  EXAMPLE
  SELECT * FROM EMP
  WHERE
  SAL > 2000 AND DEPTNO IN(20, 30, 40);
  ,
  BETWEEN OPERATOR
  
  - Whatever we are having values in range we have to use 'BETWEEN OPERATOR' .
  EXAMPLE
  SELECT * FROM EMP
  WHERE
  HIREDATE BETWEEN '01-JAN-1981' AND '31-DEC-1981';
  
  LIKE OPERATOR
  
  - Like operator used for pattern matching.
  - To achieve pattern matching, we have to use 'SQL Wild Cards' WILD CARDS:-
      % -> (It derives 0 to n character)
      _ -> (It derives single character)
  EXAMPLE
  1. Query to display details whose designation contains MAN?
      SELECT * FROM EMP
      WHERE JOB LIKE '%MAN%';
  2. Query to display employee information for who is earning 4 digit salary in manager, analyst designation?
      SELECT * FROM EMP
      WHERE SAL LIKE '_ _ _ _' AND JOB IN('MANAGER', 'ANALYST');
  

• 5. CONCATENATION OPERATOR

  - Joining or merging two or more LITERALS is know is Concatenation.
  - To achieve concatenation we have to use parallel symbol(||).
  - Concatenation operator we have to use only inside the select statement.
  EXAMPLE
  SELECT 'Smith' || 'is' || 'earning' || 800 FROM DUAL;
  

• 6. SET OPERATOR

  - Set operators are used to combine the results of two or more select statements.
  - In each and every select statement we have to use same data type columns.
  1. UNION SET OPERATOR:-
  
  - Union set Operator it is used to combine the results of two or more select statements and it will remove all duplicate values.
  SYNTAX
  SELECT COLUMN_NAME FROM TABLE_NAME
  UNION
  SELECT COLUMN_NAME FROM TABLE_NAME;
  
  2. UNION ALL OPERATOR
  
  - It is used to combine the results of two or more select statements but it will not remove duplicate values from all select statements.
  SYNTAX
  SELECT COLUMN_NAME FROM TABLE_NAME
  UNION ALL
  SELECT COLUMN_NAME FROM TABLE_NAME;
  
  3. INTERSECT OPERATOR
  
  - It is used to combine the results of two or more select statements and it will remove duplicate values from all select statements but it will display only matched records from all select statements.
  SYNTAX
  SELECT COLUMN_NAME FROM TABLE_NAME
  INTRSECT
  SELECT COLUMN_NAME FROM TABLE_NAME;
  
  4. MINUS OPERATOR
  
  - It is used to combine the results of two or more select statements and it will display unmatched records only from first select statement.
  SYNTAX
  SELECT COLUMN_NAME FROM TABLE_NAME
  MINUS
  SELECT COLUMN_NAME FROM TABLE_NAME;
  
  

Functions are important features of sql and all functions are used to perform some calculations on data.

- Wherever we are having indirect condition we have to go for SUB QUERY(Unknown condition).
- Query inside another query is know as subquery.
- To join inner query and outer query we have to use operators based on the condition.
- In sub query first the inner query will get executed and we will get one output from inner query, that output will become input to the outer query and we will get final output from outer query.
- *** In Sub query whatever column we are using inside the where condition for outer the same column we have to use inside the select statement for inner query.
- If we have indirect condition on multiple table we have to use sub query.
-*** We cant display multiple tables data as an output using sub query.
- In sub query inner query is independent and outer is dependent on output of the inner query.

• 1. SINGLE ROW SUB QUERY

  - In single row sub query to join inner query and outer query we have to use Relational Operator.
  - In single row sub query from inner query we will get single value output, that output will become input to the outer query and we will get a final output from the outer query.
  SYNTAX:
  SELECT * FROM TABLE_NAME
  WHERE COLUMN_NAME =
  (SELECT COLUMN_NAME FROM TABLE_NAME WHERE CONDITION); EXAMPLE:
  SELECT * FROM EMP WHERE JOB =
  (SELECT JOB FROM EMP WHERE ENAME = 'MARTIN');
  

• 2. MULIT ROW SUB QUERY

  - In Multiple row sub query to join inner query and outer query we have to use (IN, ANY, ALL) operators.
  - ANY & ALL these are sub query operators we have to use along with only Relational operators.
  - In multi row sub query from inner query we will get Multiple Values output.
  SYNTAX:
  SELECT * FROM TABLE_NAME
  WHERE CONDITION IN (SELECT COLUMN_NAME FROM TABLE_NAME WHERE CONDITION); EXAMPLE:
  SELECT * FROM EMP WHERE JOB IN
  (SELECT JOB FROM EMP WHERE ENAME = 'MARTIN' OR ENAME = 'SMITH');
  

• 3. NESTED SUB QUERY

  - Query inside sub query is known as Nested sub query.
  - Whenever we are having multiple indirect conditions we have to go for nested sub query.
  - If we have multiple indirect condition on multiple tables we have to go foe nested sub query.
  SYNTAX:
  SELECT COLUMN_NAME1, COLUMN_NAME2 FROM TABLE_NAME1 WHERE CONDITION IN
  (SELECT COLUMN_NAME FROM) TABLE_NAME2 WHERE COLUMN_NAME =
  (SELECT COLUMN_NAME FROM TABLE_NAME2 WHERE CONDITION));
  EXAMPLE:
  SELECT DNAME, LOC FROM DEPT WHERE DEPTNO IN
  (SELECT DEPTNO FROM EMP WHERE JOB =
  (SELECT JOB FROM EMP WHERE ENAME = 'MARTIN'));
  

• 4. CO – RELATED SUB QUERY

  - To achieve Co - Related subquery we have to use ROWNUM Column.
  - Rownum column is also known as "Pseudo Column" or "Hidden Column".
  - Co - related sub query is used for row - by - row processing.
  - In Co - related subquery inner query and outer query both are dependent on each other.
  SYNTAX:
  SELECT * FROM TABLE_NAME WHERE CONDICTION OPERATOR
  (SELECT AGGREGATE_FUNCTION(COLUMN_NAME) FROM
  (SELECT DISTINT(COLUMN_NAME) FROM TABLE_NAME ORDER BY COLUMN_NAME ASC/DESC
  WHERE ROWNUM RELATIONAL_CONDICTION));
  EXAMPLE:
  SELECT * FROM EMP WHERE SAL =
  (SELECT MIN(SAL) FROM
  (SELECT DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM <= 1);
  

- Joining or merging two or more tables and retrieve data from tables is known as Joins.
- To perform joins on multiple tables Common Columns are mandatory.

• 1. CROSS JOIN

  - In cross join each and every record of Table1 is going to match with each and every record of another Table2 and it will display output both matched records and unmatched records from all the tables.
  - In real time scenario we are not using cross joins because it will display unwanted outputs.
  SYNTAX:
      ORACLE:-
          SELECT * FROM T1, T2, T3;
      ANSI:-
          SELECT * FROM T1 CROSS JOIN T2 CROSS JOIN T3 ;
      EXAMPLE:-
          SELECT * FROM EMP CROSS JOIN DEPT ;
  

• 2. INNER JOIN

  - In inner joins each and every record of Table1 is going to match with each and every record of another Table2 and it will display only matched records from all the tables.
  SYNTAX:
      ORACLE:-
          SELECT * FROM T1, T2         WHERE T1.COMMON_COLUMN = T2.COMMON_COLUMN
          AND CONDITION;
      ANSI:-
          SELECT * FROM T1 INNER JOIN T2
          ON T1.COMMON_COLUMN = T2.COMMON_COLUMN
          AND CONDITION;
      EXAMPLE:-
          SELECT * FROM EMP INNER JOIN DEPT
          ON EMP.DEPTNO = DEPT.DEPTNO
  

• 3. LEFT OUTER JOIN

  - In left outer join each and every record of Table1 will match with another table and it will display the output both matched records and unmatched record from only left side table.
  SYNTAX:
      ORACLE:-
          SELECT * FROM T1, T2         WHERE T1.COMMON_COLUMN = T2.COMMON_COLUMN (+)
          AND CONDITION;
      ANSI:-
          SELECT * FROM T1 LEFT JOIN T2
          ON T1.COMMON_COLUMN = T2.COMMON_COLUMN
          AND CONDITION;
      EXAMPLE:-
          SELECT * FROM EMP LEFT OUTER JOIN DEPT
          ON DEPT.DEPTNO = EMP.DEPTNO
  

• 4. RIGHT OUTER JOIN

  - In Right outer join each and every record of Table1 will match with another table and it will display the output both matched records and unmatched record from only right side table.
  SYNTAX:
      ORACLE:-
          SELECT * FROM T1, T2         WHERE T1.COMMON_COLUMN (+)= T2.COMMON_COLUMN
          AND CONDITION;
      ANSI:-
          SELECT * FROM T1 RIGHT JOIN T2
          ON T1.COMMON_COLUMN = T2.COMMON_COLUMN
          AND CONDITION;
      EXAMPLE:-
          SELECT * FROM EMP RIGHT OUTER JOIN DEPT
          ON EMP.DEPTNO = DEPT.DEPTNO
  

• 5. FULL OUTER JOIN

  - Full outer join it will display matched records and unmatched records from both the tables.
  - By using Oracle standard we are not able to achieve Full outer join, to achieve full outer join we have to use ANSI Rule Syntax SYNTAX:
      ORACLE:-
          SELECT * FROM T1, T2         WHERE T1.COMMON_COLUMN (+)= T2.COMMON_COLUMN (+)
          AND CONDITION;
      ANSI:-
          SELECT * FROM T1 FULL JOIN T2
          ON T1.COMMON_COLUMN = T2.COMMON_COLUMN
          AND CONDITION;
      EXAMPLE:-
          SELECT * FROM EMP FULL OUTER JOIN DEPT
          ON EMP.DEPTNO = DEPT.DEPTNO
  

• 6. SELF JOIN

  - Joining the tables itself is know as self join.
  - To perform self join common columns are mandatory.
  - To achieve self join we have to use Aliasing.
  - In self join whenever we are using aliasing for table_name we have to use in below format.
  ALIAS_NAME.COLUMN_NAME
  - If we have employees and their manager relation, for employee we have to check their manager_number
  and
  For manager we have to check their employee_number.
  SYNTAX:
      ANSI:-
          SELECT * FROM TABLE_NAME1 ALIASING_NAME1 JOIN TABLE_NAME2 ALIAS_NAME2
          ON ALIAS_NAME1.CC = ALIAS_NAME2.CC
          AND CONDICTION;
      EXAMPLE:-
          SELECT E1.ENAME, E2.ENAME FROM EMP E1 JOIN EMP E2
          ON E1.MGR = E2.EMPNO ;
      SYNTAX TO PERFORM JOINS ON MORE THEN TWO TABLES:-
      ORACLE:-
          SELECT * FROM T1, T2, T3         WHERE T1.COMMON_COLUMN = T2.COMMON_COLUMN
          AND T2.COMMON_COLUMN = T3.COMMON_COLUMN
          AND CONDITION;
      ANSI:-
          SELECT * FROM T1 JOIN T2
          ON T1.COMMON_COLUMN = T2.COMMON_COLUMN JOIN T3
          ON T2.COMMON_COLUMN = T3.COMMON_COLUMN
          AND CONDITION;
  

- The process of organizing the data in data base is known as NORMALIZATION.
- The process of decomposing large amount of data into small amount data is known as NORMALIZATION.
- Normalization it is used to reduce Data Redundancy.

DATA REDUNDANCY:-

- Similar type if data present in a table multiple times is known as Data Redundancy.
- Using Normalization we can convert inconsistent data into consistent data.
- Inconsistent means Anomalies(Errors).

TYPES OF ANOMALIES:-

1. Inserting Anomaly
2. Updating Anomaly
3. Deleting Anomaly

- To overcome from these anomalies we have to go for normalization forms.
- Normalization forms are introduced by Edgar F. Codd in the year 1970.

TYPES OF NORMALIZATION FORMS:-

- 1NF
- 2NF
- 3NF
- 3.5NF (BCNF)
- 4NF
- 5NF