SQL Statements Versus iSQL*Plus Commands

SQL	iSQL*Plus
•         A language •         ANSI standard •         Keyword cannot be abbreviated •         Statements manipulate data and table definitions in the database	•         An environment •         Oracle-proprietary •         Keywords can be abbreviated •         Commands do not allow manipulation of values in the database •         Runs on a browser •         Centrally loaded; does not have to be implemented on each machine

Alternative Quote (q) Operator

•      Specify your own quotation mark delimiter

•      Choose any delimiter

•      Increase readability and usability

SELECT department_name || q'[, it's assigned Manager Id:]'

|| manager_id  AS "Department and Manager"  FROM departments;

DEPARTMENT AND MANAGER

Internal Control, it’s assigned Manager ID: 200

Marketing, it’s assigned Manager ID: 201

…

Using Literal Character Strings and Duplicate Rows

Using Literal Character Strings

SELECT first_name ||' is a '||job_id      AS "Employee Details"

FROM   employees;

EMPLOYEES DETAILS

John is a C_CEO

Leonardo is a F_CEO

…

Duplicate Rows

The default display of queries is all rows, including duplicate rows. By using Distinct you can eliminate repeated rows

SELECT department_id FROM   employees;

DEPARTMENT_ID

20

20

SELECT DISTINCT department_id FROM   employees;

DEPARTMENT_ID

20

10

Concatenation Operator

A concatenation operator:

•      Links columns or character strings to other columns

•      Is represented by two vertical bars (||)

•      Creates a resultant column that is a character expression

SELECT   last_name||job_id AS "Employees"  FROM employees;

EMPLOYEES

John11111

Kery22222

…….

Defining a Column Alias

A column alias:

•      Renames a column heading

•      Is useful with calculations

•      Immediately follows the column name (There can also be the optional AS keyword between the column name and alias.)

•      Requires double quotation marks if it contains spaces or special characters or if it is case-sensitive

Using Column Aliases

SELECT first_name AS name, com_pct comm

FROM   emp;

NAME	COMM
John
Kery

…..

SELECT first_name "Name" , salary*12 "Annual Salary"

FROM   emp;

NAME	ANNUAL SALARY
John
Kery

…..