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

…..

How to find first week day (Sun, Mon .ect) of the month in Oracle PL SQL

How to find first Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday of the month in Oracle PL SQL.

To do so I used Next_day and Trunc functions of Oracle PL SQL

Select NEXT_DAY( TRUNC(sysdate, 'MM') - 1 , 'Sunday') from dual;

By changing sysdate to any particular date and Sunday to any week days, you will find that months mentioned first week day.