1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL: SELECT Statement

Discussion in 'SQL PL/SQL' started by SBH, Oct 4, 2010.

  1. SBH

    SBH Forum Expert

    Likes Received:
    Trophy Points:

    The article demonstrates the Oracle SELECT statement, its abilities to Select and Project data from database.

    1. Overview of SELECT statement

    SQL (Structured Query Language) is a computer language aimed to store, manipulate, and query data stored in relational databases. SQL Commands are divided into five categories, depending upon what they do.

    • DDL (Data Definition Language): Used to define data structures during database design. CREATE, DROP, ALTER, TRUNCATE, RENAME are DDL commands
    • DML (Data Manipulation Language): Modify and manipulate stored data using INSERT, UPDATE, DELETE and CALL.
    • DCL (Data Control Language): Used to control permission access on database objects. GRANT and REVOKE.
    • TCL (Transaction Control Language): Used to control Transactions in database. COMMIT, ROLLBACK, SAVEPOINT
    • Query (Retrieving data): Query is used to retrieve data stored in database using SELECT.

    2. Capabilities of the SELECT Statement

    Out of the above listed strengths of SQL, querying the data from database is possible through SQL statement. Efficient use of SELECT ensures better selection and representation of data. Based on relational theory concepts, capabilities of SELECT statement are branched as Projection, Selection and Joining.

    • Projection: The ability to select single/multiple column(s) of the table to form a result set is called Projection.
    • Selection: The ability to select rows from out of complete result set is called Selection. It involves conditional filtering and data staging.
    • Joining: Data retrieval from multiple tables by joining them on a defined condition is called Joining.

    For example, consider the above two tables for better clarity.
    • Fetching Customer Id, Customer Names and state for a single Customer from CUSTOMER table is Projection.
    • Fetching Customer details from Rajasthan (State), from CUSTOMER table is Selection.
    • Fetching Customer name, Order Id by joining CUSTOMER and ORDERS is Joining

    3. SELECT statement Syntax

    Code (Text):
    SELECT [hint][DISTINCT] column_list
    FROM table_list
    [WHERE conditions]
    [GROUP BY group_by_list]
    [HAVING search_conditions]
    [ORDER BY order_list [ASC | DESC]]

    From the above SELECT syntax, SELECT and FROM are the mandatory clauses. WHERE (filtering purpose), GROUP BY (Aggregation clause), HAVING (Filter aggregated results), ORDER BY (Sorting the result set) are optional clauses.


    Example 3.1. Selecting individual columns from the table
    Code (SQL):

    Above query displays data for only three columns from CUSTOMERS table.

    Example 3.2. Selecting ALL defined and usable columns from the table
    Code (SQL):
    SELECT *

    Above query displays data for all the columns of CUSTOMERS table. Please note that above SELECT query would not include UNUSED columns of the table. A dispensable column in the table can be set UNUSED, similar to logical drop of a column. Once set UNUSED, it cannot be queried from SELECT query. Restore an unused column is not possible. Column must be physically dropped and added again to include in the table.

    4. ROWID

    ROWID is a unique row identifier which Oracle database assigns to each row of the table and uses internally to store its physical location. A ROWID is an 18-digit number that is represented as a base-64 number. It can be viewed for rows in a table by retrieving the ROWID column in a query.
    Similarly, ROWNUM shows the current row number. It is dynamically generated during query execution.
    Since, both ROWID and ROWNUM are not stored in database but internally maintained by oracle, these are known as Pseudo column

    Example 4.1. Selecting ROWID and ROWNUM, along with other columns from the table
    Code (SQL):

    ROWID                   ROWNUM            CUSTOMER_NAME
    --------------                     ------------      ----------------

    AAAF4yAABAAAHeKAAA      1           AAA
    AAAF4yAABAAAHeKAAB      2           BBB
    AAAF4yAABAAAHeKAAC      3           CCC
    AAAF4yAABAAAHeKAAD      4           DDD
    AAAF4yAABAAAHeKAAE      5           EEE

    5. Column Alias in SELECT statement

    For display purpose, a column in the SELECT statement can hold an alias name. In the displayed output, alias name will be displayed in place of actual column name, in capital letters. Use of alias avoid ambiguity situation which can arise during joins between same table or referring same table column in a subquery.
    If alias is required to be case sensitive or with spaces, it must be enclosed within quotes. ‘AS’ is an optional keyword to specify column alias.

    Example 5.1. Aliasing column expressions in a SELECT query
    Code (SQL):
    price * 2 AS DOUBLE_PRICE, price * 10 "Double Price"
    FROM products;

    ------------    -------------


    SELECT statement can make use of expressions in the column list. Oracle provides some basic operators namely arithmetic operators for numbers, string concatenation operators, and quote operator, which can produce desired results at required situation.

    6. Use of Literals in SELECT query

    A fixed value, preferably in string format, is known as Literal. Character and date literals are enclosed within quotes.

    Example 6.1. Concatenating two string literals using SELECT statement
    Code (SQL):

    Example 6.2. Concatenating column value with a string literal in SELECT statement
    Code (SQL):

    7. NULL: No data

    In Oracle, missing value for a tuple is considered to be NULL. NULL or void implies no value to that cell and has no data type. Unlike other programming languages, it is not numeric zero or blank character.
    It can produce hazardous results, if not properly handled. It nullifies any numeric value, operated with it.

    Example 7.1. SELECT query to show NULL effect in arithmetic operations
    Code (SQL):
    SQL> SELECT NULL + 3201

    NULL + 3201



    Attached Files: