(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.
