Data Definition Language


CREATE TABLE statement

SQL CREATE TABLE Syntax:-

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
);
CREATE TABLE DB_DEPT
(
	DEPTNO NUMBER (8) ,
	DNAME VARCHAR2(20) ,
	LOC VARCHAR2(20)
);

To check table structure:

DESC DB_DEPT;
Name        Null?      Type         
------      -----     --------------- 
DEPTNO                NUMBER(8)    
DNAME                 VARCHAR2(20) 
LOC                   VARCHAR2(20)

CREATE TABLE DB_EMP
(
	EMPNO NUMBER(4),
	ENAME VARCHAR2(50),
	EJOB VARCHAR(10),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(8,2),
	COMM NUMBER(4,2),
	DEPTNO NUMBER(8)
);
DESC DB_EMP;
Name       Null?   Type         
--------   -----   --------------- 
EMPNO               NUMBER(4)    
ENAME               VARCHAR2(50) 
EJOB                VARCHAR2(10) 
MGR                 NUMBER(4)    
HIREDATE            DATE         
SAL                 NUMBER(8,2)  
COMM                NUMBER(4,2)    
DEPTNO              NUMBER(8)

SQL INSERT INTO Syntax

This command is used to insert the data into table.

There are two method to insert the data into table.

1) Direct Method: We can inser the data directly

Syntax:
INSERT INTO table_name (column1, column2, column3,...)  VALUES (value1, value2, value3,...);
INSERT INTO DB_DEPT(DEPTNO,DNAME,LOC) VALUES(100,'IT','blr');
INSERT INTO DB_DEPT(DEPTNO,DNAME,LOC) VALUES(200,'SALES','blr');
INSERT INTO DB_DEPT VALUES(300,'MARKETING','DEL');
INSERT INTO DB_EMP(EMPNO,ENAME,EJOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(2,'JOHN','LEVEL1',1,'20-APR-2014',2000,0.2,400);

INSERT INTO DB_EMP(EMPNO,ENAME,EJOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(3,'ROBERT','LEVE1',1,'22-APR-2014',3000,0.3,100);

INSERT INTO DB_EMP(EMPNO,ENAME,EJOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (4,'MAC','LEVEL1',1,'02-APR-1981',4000,0.4,200);

INSERT INTO DB_EMP(EMPNO,ENAME,EJOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (5,'LEVIS','LEVEL1',1,'02-APR-1981',4000,NULL,300);

2) Reference Method: We can insert data into table through prompt.

Syntax : INSERT INTO TABLE_NAME VALUES (&COLUMN1,&COLUMN2,....);
INSERT INTO DB_DEPT VALUES(&DNO,&DNAME,&LOC);
INSERT INTO DB_DEPT VALUES(&DNO,'&DNAME','&LOC');

DQL-Data Query Language

This commond is used to get/retrive the data from database table.

Syntax: SELECT * FROM TABLE_NAME;
SELECT * FROM DB_DEPT;
SELECT DNAME,DEPTNO FROM DB_DEPT;
DISTINCT keyword- To remove duplicate rows
SELECT DISTINCT EMPNO,ENAME FROM DB_EMP;

Adding column Alias

SELECT EMPNO,ENAME,(SAL*12) SALARY FROM DB_EMP;
SELECT EMPNO,ENAME,(SAL*COMM) BONUS,DEPTNO FROM DB_EMP;
SELECT EMPNO,ENAME,(SAL*12) SALARY FROM DB_EMP;
SELECT EMPNO,ENAME,(SAL*COMM) BONUS,DEPTNO FROM DB_EMP;

Handling NULL in SQL:NVL() FUNCTION

Null is an undefined, unknown value and it is not same as zero.

SELECT COMM, NVL(COMM,0) COMMISION FROM DB_EMP;
SELECT EMPNO,ENAME,(SAL*12+COMM) SALARY ,DEPTNO FROM DB_EMP;
SELECT EMPNO,ENAME,(SAL*12+NVL(COMM,0)) SALARY ,DEPTNO FROM DB_EMP;

TAB- data dictionary table

This table shows all the database objects(i.e tables, views, index etc)

SELECT * FROM TAB;

ORDER BY clause

We can perform sorting of column value through ORDER BY clause.

SELECT EMPNO FROM DB_EMP ORDER BY EMPNO;
SELECT EMPNO FROM DB_EMP ORDER BY EMPNO ASC;
SELECT EMPNO FROM DB_EMP ORDER BY EMPNO DESC;

WHERE clause

This clause is used to restrict the based on given condition.

SELECT EMPNO FROM DB_EMP WHERE EMPNO=5;
Footer with Map