Simple select statement

SELECT statement retrieves information from database tables. In the statement's simplest form, you specify the table and columns. The following SELECT statement retrieves the employee number, employee name from the employee table: You can specify the column names and table name in upper case or lower case.


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL> select empno, ename from emp;

     EMPNO ENAME
---------- ----------
         1 SMITH
         2 ALLEN
         3 WARD
         4 JONES
         5 MARTIN
         6 BLAKE
         7 CLARK
         8 SCOTT
         9 KING
        10 TURNER
        11 ADAMS

11 rows selected.

SQL>
SQL>

The Six Main Components of the SELECT Command:

ComponentDescription
SELECTcolumns in the result
FROMfrom what table(s)
WHEREhow to filter the rows
GROUP BYgrouped/aggregated
HAVINGconditions to filter the aggregated groups
ORDER BYorder the resulting rows
Home »
Oracle »
Select » 

Simple Select:
  1. Simple select statement
  2. Retrieve date type information from a table
  3. Retrieving All Columns from a Table
  4. ROWID:Row Identifiers
  5. ROWNUM:Row Numbers
  6. Arithmetic calculation
  7. Date Arithmetic
  8. Column Arithmetic
  9. Column Aliases
  10. Combining Column Using Concatenation
  11. distinct rows
Related: