Sql is simple

Data definition language, Data manipulation language, Data control language :

DDL:  Examples

Create table dept (deptno number (5) primary key,

                             Dname varchar2 (15),

                             Loc varchar2 (15));

Create table EMP (    empno number (5) Primary Key,

Ename varchar2 (15),

Job varchar2 (10),

Mgr number (5),

Hiredate date,

Sal number (7, 2),

Comm number (7, 2),

Deptno number (5) Not Null,

CONSTRAINT Mgr FOREIGN KEY (Mgr) REFERENCES EMP (empno),

CONSTRAINT fk_deptno Foreign key (deptno) REFERENCES dept (deptno));

Alter table EMP Add DOB Date;

Alter table EMP Modify DOB Year;

Alter table EMP DROP DOB;

DROP table EMP;

DML: Examples

INSERT INTO EMP VALUES (7566,'JONES','MANAGER',’’,'04-Feb-81', 2975,100, 20);

INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER', 7566,'05-Jan-81', 2700,500, 30);

INSERT INTO EMP VALUES (7789,'CLARK','MANAGER', 7566,'06-Sep-81', 2450, 0, 10);

INSERT INTO EMP VALUES (7725,'SAMUEL','MANAGER', 7789,'15-Aug-82', 5500,350, 10);

INSERT INTO EMP VALUES (7782,'MALINGA','MANAGER', 7789,'14-Feb-83', 7500,'', 20);

UPDATE TABLE EMP SET SAL=3500 WHERE EMPNO=7566;

UPDATE TABLE EMP SET COMM=NVL (COMM,0)+500 WHERE JOB=’MANAGER’;

DELETE FROM EMP WHERE SAL<1000;

DCL: Examples

Once you create the user with username/password

Create user username identified by password;

Ex: Create user Scott identified by tiger;

GRANT CREATE, SELECT, INSERT, UPDATE, DELETE on employee TO SCOTT;

REVOKE CREATE ON employee TO SCOTT;

Revoke Create from Username;

Examples for Select:

Where clause

Select ename from EMP where deptno=10;

Select * from EMP where job=’MANAGER’;

Select ename from EMP where job='CLERK' OR JOB='SALESMAN' OR JOB='ANALYST'AND SAL>3000;

Select empno, ename from EMP where comm is NOT NULL;

Using Date

Select ename from EMP where hiredate < '30-JUN-1990' or hiredate >

'31-DEC-90';

Select sysdate from dual;

Select username from all_users;

Using IN, Not IN, Like, Between, Length and Wildcards

Select ename from EMP where ename like 'S%';

Select ename from EMP where ename like '_A%';

Select ename from EMP where length (ename) =5;

Select ename from EMP where job not in ('MANAGER');

Select ename from EMP where deptno in (10, 20, 40) or job in 'CLERKS','SALESMAN','ANALYST');

Select ename from EMP where job not in ('SALESMAN','CLERK','ANALYST');

Select ename from EMP where Sal between 2500 and 5000;

Using Aggregate Functions

Select count (*) from EMP;

Select avg (Sal) from EMP;

Select max (Sal) from EMP where job='CLERK';

Select sum (Sal) from EMP where job='ANALYST' and deptno=40;

Using Aggregate with Group By and Having

Select deptno, count (deptno) from EMP group by deptno;

Select deptno, sum (Sal) from EMP group by deptno;

Select job, sum (Sal) from EMP group by job;

Select deptno, count (deptno) from EMP group by deptno having

Count (*)>3;

Select job, sum (Sal) from EMP group by job having sum (Sal)>40000;

Order By and Alias

Select ename from EMP order by Sal;

Select ename, sal*12 from EMP order by Sal desc;

Select empno, ename, deptno, sal from EMP order by Sal;

Select ename, sal, sal/100*15 as hra, sal/100*5 as pf, Sal/100*10 as da, sal+sal/100*15+sal/100*10-sal/100*5 as total from EMP;