Limiting the Rows Selected

• We can restrict the rows returned from the query by using the WHERE clause.

Syntax :

SELECT * | { [DISTINCT] column / expression [alias],...} FROM table [WHERE condition ];

Example :

SELECT name,job,FROM dept WHERE emp_id = 101;

Display Table Structure

• This key word is used to display the entire table.

Example :

DESCRIBE tablename;

Duplicate Rows

• It is use to eliminating Duplicate Rows.
• This keyword eliminate the duplicate rows in the tables.

Example :

1. SELECT job FROM dept;
2. SELECT DISTINCT job FROM dept;

Literal Character

• It is a character, number or date included in the SELECT list.
• Date and character literal values should be with in single quotation marks.

Example :

SELECT name || ' is a '|| job AS "Employee Details" FROM dept;

Concatenation Operator

• You can link the the columns to other columns by using concatenation operator(||).
• Creates a resultant column that is a character expression.

Example :

SELECT name || job AS "Employees" FROM dept;

Column Alias

Defining :

• Rename the column heading and useful with calculations.
• Must be put double quotation marks if it contains spaces or special characters or is case sensitive.

Example:

SELECT name AS  emp_name, comm commission FROM dept;
SELECT name "Name", salary*12 "Annual Salary" FROM dept;

NULL VALUES

• A null is a value that is unavailable, unassigned, unknown/inapplicable.
• It is not the same as zero or a blank space.

Example:

SELECT name,job_id, salary, comm FROM dept;

In arithmetic expression:

• Arithmetic expression containing a null value evaluate to null.

Example:
SELECT name, 12*salary*comm FROM employee;

Operator Precedence

*

/

+

-

• Multiplication and division take priority over addition and subtraction.
• operators of the same priority are evaluated from left to right.
• Parentheses are used to force prioritized evaluation and clarify statements.

Example :

SELECT emp_name,salary, 12*salary+200 FROM dept;

Note :

It calculated the annual income as 12 multiplied by the monthly salary,plus a one-time bonus of 200.

USING PARENTHESES :

Example :

SELECT emp_name, salary,12*(salary+200) FROM dept;

Note :

It calculated the annual income as monthly salary plus a monthly bonus of 200,multiplied by 12.

Arithmetic Expressions

Operator	Description
+	Add
-	Subtract
*	Multiply
/	Divide

Example :

SELECT emp_name, salary, salary + 300 FROM dept;

Note :

In this statement, the addition operator to calculate a salary increase Rs.300 for all employees.

Selecting Specific Columns

Command :

SELECT dept_name, location, FROM dept;

Note :

Select particular columns from the dept table.

Example Table :

Emp_name	Emp_id	Location	Dept_Name	Salary
Arun	101	Salem	Accounts	12000
Balu	109	Chennai	Purchase	15000
Ramesh	200	Salem	Admin	13000
Saravanan	150	Coimbatore	Sales	14500
Velu	333	Trichy	Production	10500
Gopi	104	Salem	Marketing	15000
Prabu	108	Salem	IT	12500

Out put :

Dept_Name	Location
Accounts	Salem
Purchase	Chennai
Admin	Salem
Sales	Coimbatore
Production	Trichy
Marketing	Salem
IT	Salem

Selecting All Columns

Command :

SELECT * FROM dept;

Note:

All the rows and columns are selected from the table dept.

SQL STATEMENTS

 

STATEMENTS	DESCRIPTIONS
SELECT	Retrieves data from the database.
INSERT UPDATE DELETE MERGE	Enters new rows, changes existing rows, and removes unwanted rows from tables in the database, respectively. Collectively known as Data Manipulation Language (DML)
CREATE ALTER DROP RENAME TRUNCATE	Sets up, changes, and removes data structures from tables. Collectively known as Data Definition Language (DDL).
COMMIT ROLLBACK SAVEPOINT	Manages the changes made by DML statements. Changes to the data can be grouped together into logical transactions.
GRANT REVOKE	Gives or removes access rights to both the Oracle database and the structures within it. Collectively known as Data Control Language (DCL)