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.

Arithmetic Expressions

Arithmetic expressions can be used with SQL statements.

Operator	Description
+	Add
-	Subtract
*	Multiply
/	Divide

Using arithmetic expression in queries

Example

SELECT first_name, salary, salary + 500 FROM   employees;

As result from above query 500 will be added all employees salary and will be shown separately.

First_name	Salary	Salary +500
Kery	400	900
Edy	340	840

Basic SELECT Statement

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM    table;

•SELECT identifies the columns to be displayed

•FROM identifies the table containing those columns

To SELECT all columns from a particular table, the following command is used.

SELECT * FROM   departments;

To SELECT specific columns, column names should be mentioned as below.

SELECT department_id, location_id FROM   departments;

SQL Statements

•SQL statements are not case-sensitive.
•SQL statements can be on one or more lines.
•Keywords cannot be abbreviated or split across lines.
•Clauses are usually placed on separate lines.
•Indents are used to enhance readability.
•In iSQL*Plus, SQL statements can optionally be terminated by a semicolon (;).
Semicolons are required if you execute multiple SQL statements.
•In SQL*plus, you are required to end each SQL statement with a semicolon (;).



Reference : Oracle docs