advertisement

Oracle select statment

50 %
50 %
advertisement
Information about Oracle select statment
Education

Published on February 26, 2014

Author: renuindia

Source: slideshare.net

Description

Oracle Select Statement
advertisement

SELECT STATEMENT BASICS Author: Renuka Parasuraman

Purpose  The select statement is used to select the data from the database.

Select Single Column SELECT COLUMN_NAME FROM TABLE_NAME; Example: SELECT D.DEPTNO FROM DEPT D;

Select Multiple Columns SELECT COLUMN_NAME, COLUMN_NAME FROM TABLE_NAME; Example: SELECT D.DEPTNO , D.DEPT_NAME FROM DEPT D;

Select Columns From Multiple Tables SELECT * FROM TABLE_NAME TABLE_1, TABLE_NAME TABLE_2 WHERE TABLE_1.COLUMN_NAME = TABLE_2.COLUMN_NAME; Example: SELECT * FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO

Select All Columns SELECT * FROM TABLE_NAME; Example: SELECT * FROM DEPT D;

Where Clause SELECT COLUMN_NAME FROM TABLE_NAME WHERE CONDITIONS; Example: SELECT D.DEPTNO FROM DEPT D WHERE D.DEPTNO = 10;

DISTINCT Rows SELECT DISTINCT COLUMN_NAME FROM TABLE_NAME; Example: SELECT DISTINCT E.DEPTNO FROM EMP E;

AND Operator SELECT COLUMN_NAME FROM TABLE_NAME WHERE CONDITION_1 AND CONDITION_2; Example: SELECT D.DEPTNO FROM DEPT D WHERE D.DEPTNO = 10 AND D.LOCATION = ‘CHENNAI’;

OR Operator SELECT COLUMN_NAME FROM TABLE_NAME WHERE CONDITION_1 OR CONDITION_2; Example: SELECT D.DEPTNO FROM DEPT D WHERE D.DEPTNO = 10 OR D.DEPTNO = 20;

Select Rows With NULL values SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IS NULL; Example: SELECT D.DEPTNO FROM DEPT D WHERE D.LOCATION IS NULL;

Select Rows Without NULL values SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IS NOT NULL; Example: SELECT D.DEPTNO FROM DEPT D WHERE D.LOCATION IS NOT NULL;

ARITHMETIC OPERATORS

EQUAL(=) Operator SELECT * FROM TABLE_NAME WHERE COLUMN_NAME = VALUE; Example: SELECT D.DEPTNO FROM DEPT D WHERE D.LOCATION = ‘CHENNAI’;

Greater Than(>) Operator SELECT * FROM TABLE_NAME WHERE COLUMN_NAME > VALUE; Example: SELECT D.DEPTNO FROM DEPT D WHERE D.DEPTNO > 20’;

Lesser Than(<) Operator SELECT * FROM TABLE_NAME WHERE COLUMN_NAME < VALUE; Example: SELECT D.DEPTNO FROM DEPT D WHERE D.DEPTNO < 20’;

AGGREGATE FUNCTIONS

SUM SELECT SUM(COLUMN_NAME) FROM TABLE_NAME; Example: SELECT SUM(E.BONUS) FROM EMP E

MIN SELECT MIN(COLUMN_NAME) FROM TABLE_NAME; Example: SELECT MIN(E.SALARY) FROM EMP E

MAX SELECT MAX(COLUMN_NAME) FROM TABLE_NAME; Example: SELECT MAX(E.SALARY) FROM EMP E

AVG SELECT AVG(COLUMN_NAME) FROM TABLE_NAME; Example: SELECT AVG(E.SALARY) FROM EMP E

COUNT SELECT COUNT(COLUMN_NAME) FROM TABLE_NAME; Example: SELECT COUNT(E.BONUS) FROM EMP E

COUNT(*) SELECT COUNT(*) FROM TABLE_NAME; Example: SELECT COUNT(*) FROM EMP E

GROUP BY SELECT COLUMN_NAME, GROUP_FUNCTION(COLUMN_NAME) FROM TABLE_NAME; Example: SELECT E.DEPTNO, COUNT(*) FROM EMP E GROUP BY E.DEPTNO;

HAVING CLAUSE SELECT COLUMN_NAME, GROUP_FUNCTION(COLUMN_NAME) FROM TABLE_NAME HAVING CONDITION; Example: SELECT E.DEPTNO, COUNT(*) FROM EMP E GROUP BY E.DEPTNO HAVING COUNT(*) > 2;

ORDER BY ASCENDING ORDER SELECT * FROM TABLE_NAME ORDER BY COLUMN_NAME, COLUMN_NAME ASC; Example: SELECT * FROM EMP E ORDER BY E.EMPTNO ASC;

ORDER BY DESCENDING ORDER SELECT * FROM TABLE_NAME ORDER BY COLUMN_NAME, COLUMN_NAME DESC; Example: SELECT * FROM EMP E ORDER BY E.EMPTNO DESC;

ORDER BY COLUMN POSITION SELECT * FROM TABLE_NAME ORDER BY COLUMN_ORDER, COLUMN_ORDER; Example: SELECT * FROM EMP E ORDER BY 1,2 ;

IN Operator SELECT * FROM TABLE_NAME COLUMN_NAME IN (VALUE,VALUE); Example: SELECT * FROM EMP E WHERE E.DEPTNO IN (10,20);

BETWEEN Operator SELECT * FROM TABLE_NAME COLUMN_NAME BETWEEN START_RANGE AND END_RANGE; Example: SELECT * FROM EMP E WHERE E.DEPTNO BETWEEN 10 AND 30;

ARITHMETIC FUNCTIONS

ABS SELECT ABS(X) FROM DUAL; Example: SELECT ABS(-100), ABS(100) FROM DUAL;

SIGN SELECT SIGN(X) FROM DUAL; Example: SELECT SIGN(-1), SIGN(1), SIGN(0) FROM DUAL;

MOD SELECT MOD(X,Y) FROM DUAL; Example: SELECT MOD(100,10) FROM DUAL;

FLOOR SELECT FLOOR(X) FROM DUAL; Example: SELECT FLOOR(13.36) FROM DUAL;

POWER SELECT POWER(X,Y) FROM DUAL; Example: SELECT POWER(10,2) FROM DUAL;

ROUND SELECT ROUND(X) FROM DUAL; Example: SELECT ROUND(10.20) FROM DUAL; SELECT ROUND(10.20, 2) FROM DUAL;

SQRT SELECT SQRT(X) FROM DUAL; Example: SELECT SQRT(2) FROM DUAL;

Add a comment

Related presentations

Related pages

SELECT - Oracle

SELECT . Purpose. Use a SELECT statement or subquery to retrieve data from one or more tables, object tables, views, object views, or materialized views.
Read more

Oracle/PLSQL: SELECT Statement - TechOnTheNet

This Oracle tutorial explains how to use the Oracle SELECT statement with syntax, examples, and practice exercises. The Oracle SELECT statement is used to ...
Read more

Oracle SQL Select Statement - ORACLE DBA Online

Tutorial and How to use Oracle SQL Select Statement and Filtering data using WHERE Conditions with Examples.
Read more

Oracle Select Statement - DBMS Packages | PL/ SQL | SQL

Free Oracle Magazine Subscriptions and Oracle White Papers: Oracle Select Statements: Version 11.1 : Basic Select Statements: Select All Columns and All ...
Read more

sql - If statement in select (ORACLE) - Stack Overflow

Hi I have simply select and works great: select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue ...
Read more

The Oracle SELECT Statement

The SELECT statement retrieves or extracts information from tables in the database. Using the SELECT statement, you can choose the rows in a table tha
Read more

Ask Tom "How to use CASE in Select statement" - Oracle

Is it possible to use a SELECT statement within case For ex, SELECT CASE WHEN A1.COL1=B1.COL1 THEN ... Oracle disclaims all express and implied ...
Read more

SQL 101: Getting Answers with SELECT - Oracle

Getting Answers with SELECT ... This article has shown you how to construct and execute simple SQL SELECT statements with Oracle SQL Developer and the SQL ...
Read more

SQL : SELECT Statement - TechOnTheNet

This SQL tutorial explains how to use the SQL SELECT statement with syntax, examples, and practice exercises. The SQL SELECT statement is used to retrieve ...
Read more

SQL SELECT Statement - W3Schools

The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set. SQL SELECT Syntax.
Read more