Oracle Database Admin (DBA) Tutorial: SQL Commands for DBAs

1) Select all columns

select *from emp;

2) Select few columns

select empno, ename from emp;

3) DISTINCT

select distinct job from emp;

4) WHERE

select *from emp where JOB='CLERK';

5) AND operator

select *from emp where JOB='CLERK' and DEPTNO=20;

6) OR operator

select *from emp where JOB='CLERK' or JOB='ANALYST';

7) ORDER BY

select *from emp order by sal;

8) ORDER BY DESC

select *from emp order by sal desc;

9) INSERT

10) UPDATE

SQL> update emp set sal=9000 where empno=7902;

1 row updated.

SQL> commit;

Commit complete.


11) DELETE


SQL> delete from emp where empno=7839;

1 row deleted.

SQL> commit;

Commit complete.


12) MAX

select max(sal) from emp;

13) MIN

select min(sal) from emp;

14) AVG

select avg(sal) from emp;

15) SUM

select sum(sal) from emp;

15) LIKE

select *from emp where ename like 'SCO%';

select *from emp where ename like 'SC_TT';

16) IN

select *from emp where empno in (7369,7788);

17) BETWEEN

select *from emp where sal between 1000 and 2000;

select *from emp where ename between 'A' and 'M';

18) NOT BETWEEN

select *from emp where sal not between 1000 and 2000;

select *from emp where ename not between 'A' and 'M';

19) ALIAS (AS)

select empno as eno from emp;

20) Normal Join:-

SQL> select empno, ename, dname from emp, dept where emp.deptno=dept.deptno;

21) Right outer join:-

SQL> select empno, ename, dname from emp, dept where emp.deptno(+)=dept.deptno;

22) Left outer join:-

SQL> select empno, ename, dname from emp, dept where emp.deptno=dept.deptno(+);

23) Full outer join:-

SQL> select empno, ename, dname from emp full outer join dept on (emp.deptno=dept.deptno);

24) UNION

select deptno from emp union select deptno from dept;

25) UNION ALL

select deptno from emp union all select deptno from dept;

26) CREATE TABLE

27) GROUP BY

select object_type, count(object_name) from user_objects group by object_type;

28) HAVING

select object_type, count(object_name) from user_objects group by object_type having object_type='TABLE';