DDL & DML

 DDL & DML

DDL-(DATA DEFINITION LANGUAGE)

1.CREATE

2.ALTER
     1.ADD
     2.MODIFY
     3.RENAME
     4.DROP
These four for Only the column level Operations

3.DROP
4.TRUNCATE

DML-(DATA MANIPULATION LANGUAGE)::

1.INSERT
2.UPDATE
3.DELETE
4.MERGE

DCL-(DATA CONTROL LANGUAGE)::

1.GRANT
2.REVOKE

TCL-(TRANSACTIONAL CONTROL LANGUAGE)::

1.COMMIT
2.ROLLBACK
3.SAVE POINT

CREATE::

Its mainly used for Create a New table.

SYNTAX::

CREATE TABLE TABLE_NAME (COL1 DATATYPE,COL2 DATATYPE.....);
EXAMPLE::

CREATE TABLE Students
(
STU_ID NUMBER(4),
STU_NAME VARCHAR(20),
STU_GENGER CHAR,
STU_DPT_ID NUMBER(10)
);

ALTER::

The ALTER is mainly used of done the column level changes like
1.ADD NEW COLUMNS
2.MODIFY THE EXISTING DATATYPES
3.RENAME THE EXISTING COLUMNS
4.DROP THE UNWANTED COLUMNS

1.ADD NEW COLUMNS::

Using ADD keyword we can add new columns in existing table.

SYNTAX::

ALTER TABLE <TABLE_NAME>
ADD <COL1_NAME DATATYPE..........>;

Example::


ALTER TABLE students
ADD Feedback VARCHAR2(50);

2.MODIFY THE EXISTING DATATYPES::

Using MODIFY keyword we can Modify existing column datatypes.

SYNTAX::

ALTER TABLE <TABLE_NAME>
MODIFY <COL1_NAME DATATYPE..........>;

Example::


ALTER TABLE students
MODIFY Feedback VARCHAR2(100);

3.RENAME THE EXISTING COLUMNS::

RENAME is mainly used for RENAME the existing column in a table.

SYNTAX::

ALTER TABLE <TABLE_NAME>
RENAME COLUMN <COL_NAME(OLD) TO COL_NAME(NEW)......>;

Example::


ALTER TABLE students
RENAME Column Feedback TO FEEDBACKS;

4.DROP THE UNWANTED COLUMNS::

DROP IN ALTER CASE is mainly used for DROP the particular unwanted columns in a table.

SYNTAX::

ALTER TABLE <TABLE_NAME>
DROP COLUMN <COL1_NAME.>;

Example::


ALTER TABLE students
DROP COLUMN FEEDBACKS;

INSERT::

The INSERT keyword is maily used for INSERT a new record into a table.

SYNTAX::

INSERT INTO TABLE_NAME VALUES('Col1_Date','Col2_Date','...','...')

Examples::

INSERT INTO Students Values('1','Arul','M','10');
INSERT INTO Students Values('2','Xavier','M','20');
INSERT INTO Students Values('4','Anu','F','30');
INSERT INTO Students Values('4','Vino','M','40');
INSERT INTO Students Values('5','Mathi','M','50');

DELETE::

The Delete is Works in Two Methods

1.We can delete Whole date from the table
2.We can Check the Condition in WHERE and delete the Particular data's.

SYNTAX_1::

DELETE FROM TABLE_NAME;
(Deletes Whole data's from the table)

SYNTAX_2::(USING WHERE CLAUSE)

DELETE FROM TABLE_NAME WHERE Col_Name='';

DELETE FROM TABLE WHERE COL_NAME IN ('','','')

Example_1::

DELETE FROM Students;
This query deletes all the data's from the table.

Example_2::

DELETE FROM Students WHERE STU_ID = '1';

Example_3::

DELETE FROM Students WHERE STU_ID IN ('2','3','4');

4.TRUNCATE::

TRUNCATE is same as the DELETE but here we cant check the WHERE Condition.It delete all the Data's from the table.
We cant roll back the data's again.

SYNTAX::

TRUNCATE TABLE TABLE_NAME;

Example::

TRUNCATE TABLE Students;

5.DROP::

DROP Is mainly used delete the Whole table and data's(Structure and data).

SYNTAX::

DROP TABLE TABLE_NAME;

Example::

DROP TABLE Students;

=====>DDL & DML END<=====

Read More

SUB QUERY

               SUB QUERY

*The Query with in another is query is known as "SUB QUERY".
*In Sub query the inner part is execute first and the Outer query will execute second.
*Can not use the WHERE condition after the GROUP BY Clause.
*Sub query start with "(" and ends with ")" after the conditional operator.

Simple Example 1::


SELECT * FROM employees WHERE Salary>
(SELECT Salary FROM employees where First_Name='Neena');

In this query,The inner part executes first and get the "Salary" of employee "Neena" and then will returns the Output,Who
all are having more than(Greater than) "Neena's" salary.

Simple Example 2::


SELECT * FROM employees WHERE Salary>
(SELECT ROUND(AVG(Salary)) FROM employees);

In this query the same Inner part is executes first and then Get the "Average salary" of employees and then return the Output
Who all are having greater then "AVG" salary.

TYPES OF SUB QUERIES::


There are following two types of subsidiaries are there.

1.Single Row Sub Queries.
2.Multiple Row Sub Queries.

1.Single Row Sub Queries::

Query that return only one row from the inner SELECT statement is Known as "Single Row Sub Queries".

Here below i have mentioned some single-row comparison operators::
 -----------------------------------------
= - Equal To
< - Less than
<= - Less than or Equal to
> - Greater than
>= - Greater than or Equal to
<> - Not Equal to
------------------------------------------


Places Which we are Using SUB Queries::

1.USING Group Functions in Sub Query(MIN,MAX,AVG....)
2.After SELECT Key word
3.After HAVING clause

Examples and their explanations for Single Row Sub quries::


SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141);

OUTPUT::
In this the Inner Query returns the Output is "ST_CLERK"

So it gives the Output like Below format::
----------------------------------
Nayer ST_CLERK
Mikkilineni ST_CLERK
Landry ST_CLERK
Markle ST_CLERK
Bissot ST_CLERK
Atkinson ST_CLERK
....Cont...
------------------------

SELECT last_name, job_id
FROM employees
WHERE salary>
(SELECT salary
FROM employees
WHERE employee_id = 143);

OUTPUT::
In this the Inner Query returns the Output is "2600" Then outer query execute then gives the Outputs.

So it gives the Output like Below format::
-------------------------------------
King AD_PRES 24000
Kochhar AD_VP 17000
De Haan AD_VP 17000
Hunold IT_PROG 9000
...Cont....


USING SUBQUERIES AFTER HAVING CALUES::


SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id);

OUTPUT::
---------------------------
JOB_ID AVG(SALARY)
---------------------------
PU_CLERK 2780
---------------------------

2.Multiple Row Sub Queries::

*If Inner Query returns more than one row then it's known as "Multiple Row Sub queries".
*Using Following Operators after the Conditional Operators to Solve this Simple.

--------------------------------------------------------
IN - Equal to any member in the list

ANY - Compare value to each value returned by
the sub query

ALL - Compare value to every value returned
by the sub query
--------------------------------------------------------

USING IN::

While Using in Don't Specify the Conditional Operator With the Word "IN"

Example::

SELECT Employee_Id,First_Name,Salary
FROM employees
WHERE Salary IN
(SELECT Salary from employees where First_Name='Alexandar');

This returns the Out Put in Below Format because the First_Name='Alexandar' having 2 times and the Same Alexander Salary is
equal to Some employees in employee tables.


--------------------------------------
EMPLOYEE_ID FIRST_NAME SALARY
--------------------------------------
158 Allan 9000
152 Peter 9000
109 Daniel 9000
103 Alexander 9000
196 Alana 3100
181 Jean 3100
142 Curtis 3100
115 Alexander 3100
--------------------------------------

USING "ANY" OPERATOR::

Using ANY Operator Which Compares the Each sub query value with the Outer Query and give the OUTPUT based on below
CONDITION BASES.
=================================================================
< ANY --- Get the Max Value in the Sub Query and Give Output Lessthan than the MAX                                 Value.
------------------------------------------------------------------------------------------------
> ANY --- Get the MIN Value in the Sub Query and give Output Greterthan than the MIN                               Value
------------------------------------------------------------------------------------------------
= ANY --- Which Gives the result same as IN Condition
=================================================================

Example::
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(select Salary from employees where Job_Id='IT_PROG');

Here first the Sub query will excute and it check the MAX Salary of IT_PROG(MAX is 9000) and returns the output like Less than 9000
who all are get

------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
132 Olson ST_CLERK 2100
128 Markle ST_CLERK 2200
136 Philtanker ST_CLERK 2200
127 Landry ST_CLERK 2400
135 Gee ST_CLERK 2400
119 Colmenares PU_CLERK 2500
131 Marlow ST_CLERK 2500
..Cont.........
------------------------------------------------------

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > ANY
(select Salary from employees where Job_Id='IT_PROG');
OUTPUT::

                ------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
100 King AD_PRES 24000
101 Kochhar AD_VP 17000
102 De Haan AD_VP 17000
145 Russell SA_MAN 14000
146 Partners SA_MAN 13500
201 Hartstein MK_MAN 13000
108 Greenberg FI_MGR 12000
-------------------------------------------------------

USING "ALL" OPERATOR::

Following are the conditions for using ALL Operator.

=================================================================
< ALL --- Get the MIN Value in the Sub Query and Give Output Less than than the MIN Value.
------------------------------------------------------------------------------------------------
> ALL --- Get the MAX Value in the Sub Query and give Output Grater than than the MAX Value
=================================================================

Examples::


SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL
(select Salary from employees where Job_Id='IT_PROG');

OUTPUT::

------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
115 Khoo PU_CLERK 3100
116 Baida PU_CLERK 2900
117 Tobias PU_CLERK 2800
118 Himuro PU_CLERK 2600
119 Colmenares PU_CLERK 2500
125 Nayer ST_CLERK 3200
126 Mikkilineni ST_CLERK 2700
127 Landry ST_CLERK 2400
128 Markle ST_CLERK 2200
-------------------------------------------------------

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > ALL
(select Salary from employees where Job_Id='IT_PROG');

OUTPUT::

------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
100 King AD_PRES 24000
101 Kochhar AD_VP 17000
102 De Haan AD_VP 17000
108 Greenberg FI_MGR 12000
114 Raphaely PU_MAN 11000
145 Russell SA_MAN 14000
146 Partners SA_MAN 13500
147 Errazuriz SA_MAN 12000
148 Cambrault SA_MAN 11000
------------------------------------------------------

 =====>SUB QUERY END<=====

Read More

JOINS

Joins In Oracle

       JOIN is used for selecting the data's from one or more tables.

TYPES OF JOINS::

1.EQUI JOIN
2.OUTER JOIN
   i)LEFT OUTER JOIN
  ii)RIGHT OUTER JOIN
 iii)FULL JOIN
3.SELF JOIN
4.CROSS JOIN

EQUI JOIN::

The EQUI JOIN is return the OUTPUT when the condition satisfy(Join Satisfy)returns only the "Matched Values".

Example Table::


Employees:(Table 1)
----------------------------------------------
E_Id E_Name Dept_Id Hire_date Salary
----------------------------------------------
1 Arul              1 01-Jan-11 15000
2 Xavier 2 01-Feb-10 18000
3 Vino              3 25-Sep-08 25000
4 Mathi 4 25-Dec-09 23000
5 Divya             6       05-Jan-06 45000
----------------------------------------------

Departments:(Table 2)
--------------------------
Dept_Id Dept_Name
--------------------------
1 HR
2 SALES
3 TEAM LEAD
4 DEVELOPER
5 MANAGER
---------------------------

1.EQUI JOIN QUERY::
      It return only the Matched record from the tables.

SELECT E_Id,E_Name,Hire_date,Dept_Name,Salary
FROM Employees a JOIN Department b
ON a.Depet_Id=b.Dept_Id;

OUTPUT::
------------------------------------------------------------
1 Arul 01-Jan-11 HR 15000
2 Xavier 01-Feb-10 SALES 18000
3 Vino 25-Sep-08 TL             25000
4 Mathi      25-Dec-09     DEV 23000
-------------------------------------------------------------

2.OUTER JOIN::

The OUTER JOIN returns the Output the Common values(Matched Values ) and Unmatched Values.

OUTER JOIN QUERY::

SELECT E_Id,E_Name,Hire_date,Dept_Name,Salary
FROM Employees a OUTER JOIN Department b
ON a.Depet_Id=b.Dept_Id;

OUTPUT::
-----------------------------------------------------
1 Arul        01-Jan-11         HR                15000
2 Xavier 01-Feb-10 SALES 18000
3 Vino        25-Sep-08       TEAM LEAD 25000
4 Mathi 25-Dec-09       DEVELOPER     23000
5 Divya 05-Jan-06         MANAGER 45000

-----------------------------------------------------

2)i)LEFT OUTER JOIN::
The LEFT OUTER JOIN or LEFT JOIN is return the OUTPUT like Matched records and unmatched records from left side table.

LEFT OUTER JOIN QUERY::
SELECT  a.E_Id
,a.E_Name
,a.Hire_date
,b.Dept_Name
,a.Salary
FROM Employees a
LEFT JOIN Department b
ON a.Depet_Id=b.Dept_Id;

OUTPUT::
-------------------------------------------------------------
1 Arul 01-Jan-11        HR 15000
2 Xavier 01-Feb-10 SALES 18000
3 Vino 25-Sep-08 TL 25000
4 Mathi 25-Dec-09 DEV 23000
5 Divya           05-Jan-06          - 45000
-------------------------------------------------------------
2)ii)RIGHT OUTER JOIN::
The RIGHT OUTER JOIN or RIGHT JOIN returns the Output like Matched records and Unmatched records from the right side table.

RIGHT OUTER JOIN QUERY::
SELECT
E_Id
,E_Name
,Hire_date
,Dept_Name
,Salary
FROM Employees a
RIGHT JOIN Department b
ON a.Depet_Id=b.Dept_Id;

OUTPUT::
-----------------------------------------------------
1 Arul 01-Jan-11 HR 15000
2 Xavier 01-Feb-10 SALES 18000
3 Vino 25-Sep-08 TL             25000
4 Mathi     25-Dec-09 DEV           23000
- - - MANAGER -
-----------------------------------------------------

JOIN Using More than One Table::

Consider the below three table,

JOIN QUERY::
SELECT a.E_Id
,a.E_Name
,a.Hire_date
,b.Dept_Name
,a.Salary
,c.Loc_Name
FROM Employees a
JOIN Department b
ON a.Depet_Id=b.Dept_Id
JOIN Locations c
ON b.Loc_Id=c.Loc_Id;

OUTPUT::
-----------------------------------------------------------------------------
1 Arul 01-Jan-11       HR 15000 Chennai
2 Xavier 01-Feb-10 SALES 18000 Madurai
3 Vino 25-Sep-08 TEAM LEAD 25000 Tirunelveli
4 Mathi 25-Dec-09 DEVELOPER 23000 Trichy
-----------------------------------------------------------------------------

3.SELF JOIN::

SELF JOIN used for JOIN the table Itself.

Example Query::

SELECT   Emp.Employee_Id "Employee ID"
,Emp.First_Name "Employee Name"
,Emp.Salary "Employee Salary"
,Man.First_Name "Manager Name"
,Man.Salary "Manager Salary"
FROM employee Emp
INNER JOIN
employee Man
ON Emp.Manager_Id=Man.Employee_Id;

CROSS JOIN::

*It's a wrong Join (n x m)
*It wroking like all the rows in the left table will join all the row in the right table.
*It will happen if u ignore the JOIN Condition or If you provide invalid Join.
=======================================================================
JOIN NOTES::
If we are using "N" number of tables then we used (N-1) JOINs in the Query.
=======================================================================
JOIN ---USING()::

*While Using the USING() don't specify the alias name for the JOINING column.
*Only one join happens from left table to right table then(We can perform only one column joining) we use USING()

Example::

SELECT   e.Employee_Id
,e.First_Name
,Department_Id
,Department_Name
FROM employees e JOIN
Departments d
USING(Department_Id);

NATURAL JOIN::

*NATURAL JOIN also happen's only one possibilities happen to join left table to right table(only one column)
*Here also don't specify the alias for the common column.

Example::

SELECT   e.Employee_Id
,e.First_Name
,Department_Id
,Department_Name
FROM employees e
NATURAL JOIN
Departments d;
                                                    ====>JOINS END<====

Read More