In Oracle : Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle SQL & PL/SQL

Oracle SQL Oracle PL/SQL

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle SQL & PLSQL

-> SELECT statement in Oracle

 

 SELECT statement in Oracle

 

Here you can see how the SELECT statement is used in an Oracle database. The examples are tested in an Oracle 11g, but you can run these statements in 8i, 9i, 10g as well.

Before you run these statements you must be connected as scott.

select EMPNO, ENAME from scott.EMP;

select EMPNO, ENAME from scott.EMP where ename = 'SMITH';

select EMPNO, ENAME from scott.EMP where ename like 'SMITH';

select EMPNO, ENAME from scott.EMP where ename like 'SMI%';

select EMPNO, ENAME from scott.EMP where ename NOT like 'SMI%';

select EMPNO, ENAME from scott.EMP where ename in ('SMITH', 'DAN');

select EMPNO, ENAME from scott.EMP where ename NOT in ('SMITH', 'DAN');

select EMPNO, ENAME, job from scott.EMP where job = 'CLERK';

select EMPNO, ENAME, job, sal from scott.EMP where job = 'CLERK' and sal > 1000;

select ENAME, job, sal, comm from scott.EMP where sal+ nvl(comm,0) > 1000;

select ENAME, job, sal, comm from scott.EMP where comm is not null;

select * from scott.EMP;

 

select e.* from scott.EMP e;

select e.ENAME, job, sal, comm from scott.EMP e;

select e.ENAME "Name", job, sal, comm from scott.EMP e;

select e.ENAME Name, job, sal, comm from scott.EMP e;

select 100, ENAME Name, job, sal, comm from scott.EMP;

select 100 "OneHundred", ENAME Name, job, sal, comm from scott.EMP;

select ENAME , job, sal, comm from scott.EMP order by 1;

select ENAME , job, sal, comm from scott.EMP order by 2 asc, 3 desc;

select job, sum(sal) from scott.EMP group by job;

select job, sum(sal) from scott.EMP group by job having sum(sal) > 5000;

SELECT 1 FROM dual;

SELECT (SELECT 1 FROM dual) FROM dual;

 


If we have a function named F1 we can use the following function as well:

SELECT   F1(44)    from  dual;

 

SELECT job, ename FROM dept NATURAL JOIN emp;

SELECT job, ename FROM scott.dept, scott.emp where emp.DEPTNO = dept.DEPTNO ;

SELECT job, ename, dept.DNAME FROM scott.dept, scott.emp where emp.DEPTNO = dept.DEPTNO and ename = 'SMITH';

 

 

In-Oracle.com  -> Oracle SQL & PLSQL

-> SELECT statement in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.