Data Retrieval/Query Language (DRL/DQL)

Data Retrieval/Query Language (DRL/DQL)

General SQL Commands

- Display all the rows and columns in EMP table
SQL> Select * from emp ;

- Display all the Employees of their EMPNO, name, designation ,salary and department no from EMP table

SQL> select EMPNO,ENAME,JOB,SAL,DEPTNO from emp ;

ARITHMETRIC EXPRESSION

Display all the Employees of their EMPNO, name, adding $1000 to everybodies salary from EMP table with changing the salary column heading
SQL> select empno,ename,sal + 1000 "Total Salary" from emp;
SQL> select empno,ename,sal + 1000 as TotalSalary from emp;
SQL> select empno,ename,sal + 1000 TotalSalary from emp;

- Putting 0 in place of null values
SQL> select empno,ename,sal, sal + nvl(comm,0) "Total Salary" from emp;

Concatenation Operator
- Concating employee no and name column
SQL> select empno||' '||ename from emp ;
SQL> select ename ||' having Employee Is '||empno from emp;

Duplicate Row
SQL> select distinct deptno from emp;
SQL> select distinct job from emp ;

Limiting the Row Selected by comparison condition
SQL> select * from emp where empno=7369;
SQL> select empno,ename,sal from emp where sal > 1000;
SQL> select empno,ename,sal from emp where sal >= 1000;
SQL> select empno,ename,sal from emp where sal <= 1000;
SQL> select empno,ename,sal from emp where sal != 1000;

Logical condition
SQL> select * from emp where sal > 1000 and job='SALESMAN' ;
SQL> select * from emp where sal > 1000 or job='SALESMAN' ;
SQL> select * from emp where job in ('SALESMAN','CLERK') ;
SQL> select * from emp where job not in ('SALESMAN','CLERK') ;

Other Comparison Condition
SQL> select * from emp where comm is null and mgr is null ;
SQL> select empno,ename,sal from emp where deptno in (10,20);
SQL> select empno,ename,sal from emp where deptno not in (10,20) ;
SQL> select empno,ename,sal from emp where job in ('CLERK','ANALYST');
SQL> select empno,ename,sal from emp where job not in ('CLERK','ANALYST');
SQL> select * from emp where sal = any (select sal from emp where deptno=20) ;
SQL> select empno,ename,sal from emp where ename like 'S%' ;

SQL> select empno,ename,sal from emp where ename not like 'S%' ;
SQL> select empno,ename,sal from emp where sal between 1000 and 2000 ;
SQL> select empno,ename,sal from emp where sal not between 1000 and 2000 ;