WHAT ARE SQL, PL/SQL, Oracle, DDL,DML,DCL,TCL?

SQL (STRUCTURE QUERY LANGUAGE)
SQL is an ANSI (AMERICAN NATIONAL STANDARDS INSTITUTE) computer language which is use to maniuplate & access the data from the database. It is used to retrieve the information, update it, delete it, insert it etc on the database. SQL is used by many database programs like Oracle, SQL Server, Sybase etcs.



PL/SQL (PROCEDURAL LANGUAGE)
PL/SQL is Oracle's procedural language to SQL. It extends SQL & more powerful than SQL. It has a block called PL/SQL block.

DECLARE
x datatype;
y datatype;
z datatype;

BEGIN
SELECT col1 INTO x FROM tablex WHERE col1=1;
.......
....... EXECUTIVE PORTION
.......

EXCEPTION (IF NECESSARY)
END;



ORACLE
The Oracle Database (Oracle RDBMS) is an Object Relational Database Management System. It is produced & marketed by Oracle Corporation.
Larry Ellison & his friends, former co-workers Bob Miner & Ed Oates started the Consultancy Software Development Laboratories (SDL) in 1977. SDL developed the original version of the Oracle Software.


 
DDL (DATA DEFINITION LANGUAGE)
DDL is used to perform the operations like create, alter, drop, truncate on the table structure.
We  have some below commands of DDL.

CREATE
To create a table structure
CREATE TABLE table_name
(
  column_x DATATYPE,
  column_y DATATYPE,
   .
   .
  CONSTRAINT CONSTRAINT_NAME CONSTRAINT TYPE
);


ALTER
To add or modify the existing table structure. Here we have two options to alter i.e. ADD (For adding new column) or MODIFY (For modifying an existing column)

ALTER TABLE table_name
ADD
column_name DATATYPE,
column_name DATATYPE 
.
.
COLUMN_NAME_N DATATYPE;


ALTER TABLE table_name
MODIFY
old_column_name new_column_name DATATYPE,
old_column_name new_column_name DATATYPE;


DROP
To remove the table structure completely
DROP TABLE table_name;


TRUNCATE
Truncate is an DDL command. Here we can remove all the data from the table without dropping a table. Here all the removed data has completely deleted from the memory. It is not even safe under the recycle been of oracle
TRUNCATE TABLE table_name;



DML (DATA MANIPULATION LANGUAGE)
DML is used to do the select, insert, update, delete row transactions on the table.
Below are the following commands.

SELECT
Select rows from the table for looking the output
SELECT column_name1, column_name2 FROM table_name WHERE condition.. ;


INSERT
Insert some rows in the table
INSERT INTO table_name 
(column_name1, column_name2 ....) 
VALUES
(column_value1, column_value2.....);


UPDATE
Update some existing rows in the table
UPDATE TABLE tablename
SET
column_name1 = set_value1,
column_name2 = set_value2,
column_name3 = set_value3
.
.
WHERE
CONDITION....;


DELETE
Delete some or completely rows from the table
DELETE FROM table_name; (To remove the data completely from the table)
DELETE column_name1, column_name2 FROM table_name; (To remove partial data)

THE DIFFERENCE BETWEEN DELETE, TRUNCATE & DROP
DELETE: To delete the data from the table but we can restore this data via Oracle Recycle Been.
TRUNCATE:  To delete the data from the table but we can not restore this data.
DROP: To remove the table definition with table data completely. We can not restore this too.


MERGE
To merge the MATCHED & NOT MATCHED rows of two table.
CREATE TABLE TEMP_TABLE 
(
  COL1 VARCHAR2(10), 
  COL2 VARCHAR2(10), 
  COL3 VARCHAR2(10), 
  COL4 VARCHAR2(10)
);

INSERT INTO TEMP_TABLE VALUES ('10','20','30','40');
INSERT INTO TEMP_TABLE VALUES ('10','','30','40');
INSERT INTO TEMP_TABLE VALUES ('10','','','40');
INSERT INTO TEMP_TABLE VALUES ('','','','');

CREATE TABLE temp_table2 AS (SELECT * FROM temp_table);
ALTER TABLE temp_table ADD status varchar2(20);
ALTER TABLE temp_table2 ADD status varchar2(20);

INSERT INTO temp_table2 values (50, 50, 50, 50, '');
INSERT INTO temp_table2 values (60, 60, 60, 60, '');
INSERT INTO temp_table2 values (70, 70, 70, 70, '');
INSERT INTO temp_table2 values (80, 80, 80, 80, '');


MERGE INTO temp_table t1
     USING temp_table2 t2
     ON (t1.col1=t2.col1)
WHEN MATCHED THEN
     UPDATE SET t1.status=t2.status WHERE (PUT CONDITION)
WHEN NOT MATCHED THEN
     INSERT (col1, col2, col3, col4, status) VALUES (t2.col1, t2.col2, t2.col3, t2.col4, t2.status) WHERE (PUT CONDITION);


 
DCL (DATA CONTROL LANGUAGE)
DCL is used to control the authority of access on DATABASE OBJECTS & USERS. Below are the following commands.

GRANT
Grant insert, update, delete, select etc authority on DATABASE OBJECTS to the USERS
GRANT INSERT, UPDATE ON tablename TO user1;
GRANT DBA ON table_name TO user2;


REVOKE
Revoke the insert, update, delete, select etc authority on DATABASE OBJECTS to the USERS
REVOKE INSERT, UPDATE ON tablename FROM user1;
REVOKE DBA ON table_name FROM user2;















TCL (TRANSACTION CONTROL LANGUAGE)
TCL is used to put controls on the transactions on the table that are done by the users. Below are the commands

COMMIT
If a user did some transaction & put commit after it then this transaction will permanently save

ROLLBACK
If a user did some mistake while accessing the DML command then it can be rollback like undo

SAVEPOINT
This is a point where you can rollback your transaction up to this level

SET TRANSACTION
Rollback to savepoint






NOTE: IF YOU HAVE DOUBTS, KINDLY CONTACT ME VIA THIS BLOG