SQL LANGUAGES

SQL commands are mainly categorized into five categories:

Types of languages

DDL – Data Definition Language
DML – Data Manipulation Language
DRL – Data Retrival Language
TCL – Transaction Control Language
DCL – Data Control Language

DDL- Data Definition Language(DDL) statements let you perform these tasks

Create

This command is used to create the database or its objects like(Table,index, function, views, store procedure, and triggers).

Drop

This command is used to delete objects from the database.

Alter

This is used to alter the structure of the database.

Truncate

This is used to remove all records from a table, including all spaces allocated for the records are removed.

Rename

This is used to rename an object existing in the database.

CREATE COMMAND :

syntax: Create table (column name1,column name2,....column name n);
To Create a table:
SQL>  create table tesdb_tab (id number(3),name varchar2(20), deptid number(4), salary number(8));
Table created.

To describe table:
SQL> desc tesdb_tab
Name			Null?	Type
--------------------- -------- -----------------
ID				NUMBER(3)
NAME				VARCHAR2(20)
DEPTID				NUMBER(4)
SALARY				NUMBER(8)

To Create a Duplicate Table using CTAS statement without rows or copying only Structure:
syntax: create table tablename  as
select column_name,… from tablename where 1=2;

SQL> create table emp_worows as select * from emp where 1=2;
Table created.

SQL> select * from emp_worows;
no rows selected

SQL> desc emp_worows
Name			Null?		Type
-------------------	-------- --------------
EMPNO					NUMBER(4)
ENAME					VARCHAR2(10)
JOB					VARCHAR2(9)
MGR					NUMBER(4)
HIREDATE				DATE
SAL					NUMBER(7,2)
COMM					NUMBER(7,2)
DEPTNO					NUMBER(2)

To Create a Duplicate Table using CTAS statement with rows:
syntax: create table tablename  as
select column_name,… from tablename;

SQL> create table emp1 as select * from emp;
Table created.

SQL> desc emp
Name				Null?		Type
------------------------	--------	-------------
EMPNO				NOT NULL 	NUMBER(4)
ENAME						VARCHAR2(10)
JOB						VARCHAR2(9)
MGR						NUMBER(4)
HIREDATE					DATE
SAL						NUMBER(7,2)
COMM						NUMBER(7,2)
DEPTNO						NUMBER(2)

SQL> desc emp1
Name				Null?		Type
------------------------	--------	---------
EMPNO						NUMBER(4)
ENAME						VARCHAR2(10)
JOB						VARCHAR2(9)
MGR						NUMBER(4)
HIREDATE					DATE
SAL						NUMBER(7,2)
COMM						NUMBER(7,2)
DEPTNO						NUMBER(2)

SQL> select * from emp1;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
----- ------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH      CLERK           7902 17-DEC-80        800                    20
7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
7566 JONES      MANAGER         7839 02-APR-81       2975                    20
7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
7839 KING       PRESIDENT            17-NOV-81       5000                    10
7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
7900 JAMES      CLERK           7698 03-DEC-81        950                    30
7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 rows selected.

ALTER COMMAND :

syntax:
To add a column:
alter table tablename add column_name (data type);
SQL> create table dept_test as select * from dept;
Table created.


SQL> desc dept_test
Name				Null?    Type
-------------------- 		------ --------
DEPTNO					NUMBER(2)
DNAME					VARCHAR2(14)
LOC					VARCHAR2(13)

SQL> alter table dept_test add(sal number(10));
Table altered.

DROP COLUMN COMMAND :

syntax:
alter table tablename drop column column_name;
SQL> alter table dept_test drop column sal;
Table altered.

MODIFY COLUMN COMMAND :

syntax:
alter table tablename drop column column_name;
SQL>  alter table dept_test modify(sal number(12,5));
Table altered.

DROP TABLE COMMAND :

syntax:
drop table tablename  [PURGE];
SQL> drop table dept_test;
Table dropped.

TRUNCATE TABLE COMMAND :

Truncate is a ddl command and it releases space , but delete command will not release space after removing data from table.

syntax:
truncate table tablename
SQL> truncate table dept_test;
Table truncated.

RENAME TABLE COMMAND :

syntax:
rename  old tablename  to  new tablename
SQL> rename  dept_test to dept_prod;
Table renamed.

DML - Data Manipulation Language(DML) Statements

INSERT

Insert command is used to insert rows/records in a table

DELETE

This command is used to delete records in a table. We can delete all records or can use conditions to delete the records

UPDATE

Update command is used to modify data in a table

MERGE

This is used to update and insert data in a table in a single command. This is also called as UPSERT(UPDATE+INSERT) command. When this is executed, it updates the records already exists and insert new records. All depeds on the condition

Examples of DML(INSERT) Commands :

SQL> desc dept_prod
Name			Null?    	Type
-------------------	--------	-------
DEPTNO					NUMBER(2)
DNAME					VARCHAR2(14)
LOC					VARCHAR2(13)

SQL> select * from dept_prod;
no rows selected

SQL> insert into dept_prod(deptno,dname) values(10,'POLICE');
1 row created.

SQL> insert into dept_prod values(20,'HEALTH','CHENNAI');
1 row created.

SQL> insert into dept_prod values(30,null,'VADUVUR');
1 row created.

SQL> select * from dept_prod;
DEPTNO DNAME          LOC
---------- -------------- -------------
10 POLICE
20 HEALTH         CHENNAI
30                VADUVUR

SQL> insert into dept_prod select * from dept_prod;
3 rows created.

SQL> select * from dept_prod;
DEPTNO 		DNAME          LOC
---------- -------------- -------------
10 		POLICE
20 		HEALTH		CHENNAI
30				VADUVUR
10 		POLICE
20 		HEALTH		CHENNAI
30				VADUVUR
6 rows selected.

Examples of DML(UPDATE) Commands :

SQL> update dept_prod set LOC='THANJAVUR' ;
6 rows updated.

SQL> select * from dept_prod;
DEPTNO DNAME          LOC
---------- -------------- -------------
10 POLICE         THANJAVUR
20 HEALTH         THANJAVUR
30                THANJAVUR
10 POLICE         THANJAVUR
20 HEALTH         THANJAVUR
30                THANJAVUR
6 rows selected.

SQL> update dept_prod set LOC='AVADI' where deptno=30;
2 rows updated.

   SQL> select * from dept_prod;
DEPTNO DNAME          LOC
---------- -------------- -------------
10 POLICE         THANJAVUR
20 HEALTH         THANJAVUR
30                AVADI
10 POLICE         THANJAVUR
20 HEALTH         THANJAVUR
30                AVADI

6 rows selected.

Examples of DML(DELETE) Commands :

SQL> select * from dept_prod;
DEPTNO DNAME          LOC
---------- -------------- -------------
10 POLICE         THANJAVUR
20 HEALTH         THANJAVUR
30                AVADI
10 POLICE         THANJAVUR
20 HEALTH         THANJAVUR
30                AVADI
6 rows selected.

SQL> delete from dept_prod where DEPTNO=10 and DNAME='POLICE';
2 rows deleted.

SQL> delete from dept_prod;
4 rows deleted.

SQL> select * from dept_prod;
no rows selected

Examples of DML(MERGE) Commands :

SQL> select * from dept;
DEPTNO DNAME          LOC
---------- -------------- -------------
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

SQL> select * from dept_prod; 
DEPTNO DNAME          LOC
---------- -------------- -------------
50 POLICE         CHENNAI
30 HEALTH         AVADI

SQL> merge into dept_prod a
using dept b on (a.deptno=b.deptno)
when matched then
update set
a.dname=b.dname,a.loc =b.loc
when not matched then
insert values (b.deptno,b.dname,b.loc);

4 rows merged.

SQL> select * from dept_prod;
DEPTNO DNAME          LOC
---------- -------------- -------------
50 POLICE         CHENNAI
30 SALES          CHICAGO
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
40 OPERATIONS     BOSTON

DRL- Data Retrieval Language(DRL) statements

SELECT

DRL stands for Data Retrieval Language.It also known as Data Query Language.It used to retrieve data from the database.


Examples of DRL(SELECT ) Commands :

SQL>select * from dept_prod;
DEPTNO DNAME          LOC
---------- -------------- -------------
50 POLICE         CHENNAI
30 SALES          CHICAGO
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
40 OPERATIONS     BOSTON

  SQL> select deptno from dept_prod;
DEPTNO
----------
50
30
10
20
40

  SQL> select sysdate from dept_prod;
SYSDATE
---------
26-OCT-23
26-OCT-23
26-OCT-23
26-OCT-23
26-OCT-23

  SQL> select 'My Deptno is '||deptno from dept_prod;
'MYDEPTNOIS'||DEPTNO
----------------------
My Deptno is 50
My Deptno is 30
My Deptno is 10
My Deptno is 20
My Deptno is 40

TCL- TRANSACTION CONTROL LANGUAGE (TCL)

TCL stands for Transaction Control Language. It used to control the transactions made against the database.

COMMIT

To store the transactions into database

ROLLBACK

Undo the changes till previous commit

SAVEPOINT

Create a book mark for the tranactions , where we can rollback to any bookmark.

Examples of (TCL)TRANSACTION CONTROL LANGUAGE :

SQL> commit;
commit completed.

SQL> rollback;  
Rollback complete.

SQL> create table tesdb_spoint (no number(3),name char(4));
Table created.

SQL> insert into tesdb_spoint values (1,'Tesdb');
1 row created.

SQL> insert into tesdb_spoint values (2,'Tesdb'); 
1 row created.

SQL> insert into tesdb_spoint values (3,'Tesdb');
1 row created.

SQL> savepoint tesdb_1;
Savepoint created.

SQL> insert into tesdb_spoint values (4,'Tesdb');
1 row created.

SQL> insert into tesdb_spoint values (5,'Tesdb');
1 row created.

SQL> insert into tesdb_spoint values (6,'Tesdb');
1 row created.

SQL> savepoint tesdb_2;
Savepoint created.

SQL> insert into tesdb_spoint values (101,'tesdb');
1 row created.

SQL> insert into tesdb_spoint values (102,'tesdb');
1 row created.

SQL> insert into tesdb_spoint values (103,'tesdb');
1 row created.

SQL> savepoint tesdb_3;
Savepoint created.

SQL> rollback to savepoint tesdb_2;
Rollback complete.

SQL> select * from tesdb_spoint;

DCL- DATA CONTROL LANGUAGE (DCL)

DCL stands for Data Control Language. It used to control the access to data stored in the database.

GRANT

To grant object and system level privileges to users

REVOKE

To revoke object and system level privileges from users

Examples of (DCL)DATA CONTROL LANGUAGE :

SQL> grant select on tesdb_tab1 to u1;
Grant succeeded.

SQL> revoke select on tesdb_tab1 from u1;
Revoke succeeded.