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;