Order By

Order By clause used to retrieve the records in a sorted order.

Syntax: Select < Fields Name> from < Table Name > order by < Field Name>

Example: Select * from Employee order by EmployeeName;

Here * indicate all the Fields if user wants to show only specific fields than in the below query use has to mention the name of the fields

Example: Select Address, Age from Employee order by EmployeeName;

By Default it shows the records in the Ascending order.

Group By

Group by clause used to display the data by grouping records on a column

Syntax:  select < Fields name> from <Table Name> group by < Field name >

Example: Select Deptno, Sum from employee group by DeptNO having salary <20000

Here having clause is used for the conditional retrieval with group by clause.

Group by clause must be used with having and if user wants to use where class so you can use before Group By .

Union

Union displayed all the distinct records from both the tables

Union All

Union All displayed all the records

Minus

Minus clause shows the records which is not exist in the second table

For Example user have two tables Employee and Employee Details than if user uses the Minus clause than in that case it shows only those records which not present in the Employee Details table

Intersect:

Intersect clause Displayed all the common records

Joins

Simple join:

  • Equal join
  • Non Equal join

 

Syntax: Select < Fields name > from < Table Name> where <condition >

Example:  select E.empno, E.EmployeeName, D.DName, D.location  from Employee Department where E.Deptno=D.Deptno

Select E.*, D.* from Employee E, Department D where E.Deptno=D.DeptNO.

 

Outer Join:

 Select  E.EmployeeNO, E.name , E.job ,E.Deptno, D.location from Employee E, Department D where E.DeptNO(+)=D.DeptNO

 Self-join

Self-join used to display records of a table satisfying condition with respect to column of same table

Select E.EmployeeName , E1.EmployeeName, E.Salary from Employee E , Employee E1 where E.Salary=E1.Salary

Commit:

Commit command used to make changes to make transaction permanent

Commit Employee;

Or

Commit:

It also used error save pointer (All) by releasing transaction [Employee]

Rollback:

Rollback command used to undo or cancelled the changes made in above transaction

Rollback Employee;

Or

Rollback

SAVE Point:

Save point used to add marker to a lengthy transaction ie  It works like divider in a lengthy transaction to make it smaller one.

Syntax: Savepoint < Name>

Savepoint<Employee1>

Syntax: Rollback to< Savepoint>

Rollback to Employee

USER

Syntax:Create user <user name> identified by <Password>

Create user user1 identified by ABCD.

Grant

Grant command is used to give the permission to the user

Provide all the privilege to the specific user

Syntax: Grant <All> privileges to <User name>

Grant All privileges to USER1.

Grant Select, Resource from USER1;

Provide the only Select Privilege to the specific user

Syntax: Grant <operation name> on Table Name <user Name>

Grant Select on Employee to USER 1;

Revoke

Revoke command is used to take the given permission back.

Syntax: Revoke <Operation name> from <user name>

Example:

Revoke select, resource from USER1;