SELECT Statement Basics

Basic SELECT Syntax:


SELECT   EmpNo, EName    => select_list   /    which columns.

FROM       scott.emp;              => data_source /  which table / view / other datasource. 



SELECT Query:
     It is an operation that retrives data from one or more data sources like: TABLE  / VIEW / MATERIALIZED VIEW.
Prerequisites:
     The user must have the SELECT priviledges on the objects.


Capabilities of SELECT statements.

SELECT statement can be used to select (retrieve)  data from the object using one of the following criteria.
  • SELECTION   => Choose the rows.
  • PROJECTION => Choose the columns
  • JOIN                 => Choose the data from one or more tables by creating links between them.

For a begineers in database “SCOTT” schema is suitable to practice with SQL queries.

Alternatively if u have access into other schema in oracle database, you can refer to the dummy data file “demobld.sql(sqlplus/demo/demobld.sql).
“HR” schema is suitable for Data-warehousing learners.

Executing basic SELECT statements.

SQL Statements:
SQL statements are not case-sensitive.
    Example: Both statements below are same and correct. 
           SELECT empno, ename FROM emp;
           select empno, ename from emp;
SQL statements c an be entered in multiple lines.
  Example:
SELECT empno, ename
FROM emp;

In SQL Developer the SQL Statements can optionally be terminated by semicollon(;) and incase of multiple SQL Statements execution semicolon is required.
IN SQL*Plus it is required to end each SQL Statements with a semicollom(;).
Use of column heading defaults:
SQL*Plus prompt:
Default heading display : UPPER case.
CHARACTER and DATE column: Left aligned.
NUMBER column heading: Right aligned.
Example:
SQL> SELECT DeptNo, DName, Loc
  2  FROM dept;

DEPTNO  DNAME              LOC
————  ————————- ————-
    10  ACCOUNTING      NEW YORK
    20  RESEARCH              DALLAS
    30  SALES                      CHICAGO
    40  OPERATIONS      BOSTON
SQL Developer:
Default heading display : UPPER case.
Default heading alignment: Left aligned.


ColumnAlias:
Column alias is used to rename a column heading.
AS keyword between column_name and alias is optional.
Required double quotation mark(”) if the alias contains Spaces / Special Characters / Case-sensitive alias.
Example:
SQL> SELECT DeptNo AS “Department Number”, DName department_name, Loc AS “@ Location”
  2  FROM dept;

Department Number  DEPARTMENT_NAM @ Location
—————-——––  –————–————- –————
                     10  ACCOUNTING           NEW YORK
                     20  RESEARCH                DALLAS
                     30  SALES                         CHICAGO
                     40  OPERATIONS             BOSTON

Escape the single quote character in select:
Example:
SQL> SELECT   EName || ”’s employee no is ‘ || EmpNo
  2       FROM emp;

ENAME||”’SEMPLOYEENOIS’||EMPNO
——————————————————————–
SMITH’s employee no is 7369
ALLEN’s employee no is 7499
WARD’s employee no is 7521
JONES’s employee no is 7566
MARTIN’s employee no is 7654
BLAKE’s employee no is 7698
CLARK’s employee no is 7782
SCOTT’s employee no is 7788
KING’s employee no is 7839
TURNER’s employee no is 7844
ADAMS’s employee no is 7876
JAMES’s employee no is 7900
FORD’s employee no is 7902
MILLER’s employee no is 7934

14 rows selected.

To describe the table structure use DESCRIBE or DESC keyword.
DESC[RIBE] table_name
Example:
SQL> DESCRIBE dept
 Name                        Null?             Type
 ———————— —————- —————————
 DEPTNO                                        NUMBER(2)
 DNAME                                         VARCHAR2(14)
 LOC                                               VARCHAR2(13)

SQL> DESC dept
 Name                        Null?             Type
 ———————— —————- —————————
 DEPTNO                                        NUMBER(2)
 DNAME                                         VARCHAR2(14)
 LOC                                               VARCHAR2(13)

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn