1. SELECT with WHERE Clause
A WHERE clause is used when you want to retrieve some unique information from the table and return only those rows that you need to see. It is used with SELECT, UPDATE and DELETE clauses.
Syntax
SELECT column_list
FROM table-name
WHERE condition;
Example
Supplier Table
| Sup_id | Sup_name | Item | Quantity | Price |
| 0001 | Analog Devices, Inc | PC | 4 | 80k |
| 0002 | Emerson Electric Co. | LAPTOP | 2 | 43k |
| 0003 | Brady Corporation | PC | 6 | 60k |
| 0004 | Cyntec Co. Ltd | LAPTOP | 8 | 58k |
| 0005 | Intel Corporation | MOBILE | 10 | 35k |
| 0006 | Micron Technology, Inc | LAPTOP | 3 | 74k |
Query
SELECT Sup_name
FROM Supplier
WHERE item = laptop;
Result
| Sup_name | Item |
Emerson Electric Co. | LAPTOP |
Cyntec Co. Ltd | LAPTOP |
Micron Technology, Inc | LAPTOP |
2. UPDATE Command
The UPDATE statement, in simple language, is used to alter/modify the table. In other words we want to do some modification/changes in the data of our table in the database. To do that we use an UPDATE statement.
Syntax
UPDATE table_name
SET Attribute = New_Value
WHERE Attribute='Value';
Example
Assume the following Customer Table that already exists in the database:
Customer Table
| Cust_id | State | Item | Price |
| 44332 | Maharashtra | Soap | 300 |
| 44338 | Uttar Pradesh | Talc | 480 |
| 44336 | Himachal Pradesh | Blanket | 226 |
| 44337 | Himachal Pradesh | Kitchen Ware | 5200 |
| 44339 | Maharashtra | Medicines | 1200 |
Query
UPDATE Customer
SET Item = Shampoo
WHERE Item='Soap';
Result
| Cust_id | State | Item | Price |
| 44332 | Maharashtra | Shampoo | 300 |
| 44338 | Uttar Pradesh | Talc | 480 |
| 44336 | Himachal Pradesh | Blanket | 226 |
| 44337 | Himachal Pradesh | Kitchen Ware | 5200 |
| 44339 | Maharashtra | Medicines | 1200 |
3. WHERE Command
In Oracle the WHERE Clause is used when you want to retrieve some unique information from a table. We can also say that WHERE is a condition that filters rows from the table and returns only those rows that you need to see.
Syntax
SELECT "column_name"
FROM "table_name"
WHERE "condition";
Example
Assume the following Medicine Table:
| Med_id | Med_Name | Quantity | Price |
| 53112 | Combiflame | 300 | 1000 |
| 67453 | Pantocid | 450 | 750 |
| 45643 | Montair 10 | 270 | 1500 |
Query
SELECT Med_Name
FROM Medicine
WHERE Price < 1000;
Result
4. EXPLAIN PLAN Command
To execute a SQL statement Oracle will follow a list of steps that is known as an Plan. By executing the statement step-by-step the complexity of SQL Commands will also be reduced. Oracle allows creating your own Plan Tables with the name of your own choice.
To create a Plan Table we need to run the following command:
Utlxplan.sql
that is located in "$ORACLE_HOME/rdbms/admin".
Then, your plan table will be created.
Syntax
EXPLAIN PLAN
[SET STATEMENT_ID = <string in single quotes>]
[INTO <plan table name>]
FOR
<SQL statement>;
Example
Assume the following Medicine Table:
| Med_id | Med_Name | Quantity | Price |
| 53112 | Combiflame | 300 | 1000 |
| 67453 | Pantocidh | 450 | 750 |
| 45643 | Montair 10 | 270 | 1500 |
Query
EXPLAIN PLAN
SET Med_ID = 'Med1' FOR
SELECT Med_name FROM Medicines;
DCL Commands
1. GRANT Clause
The GRANT Clause grants privileges, or we can say to grant the permission to the other user so that they may perform a specific action.
Syntax
GRANT privileges on object to user;
Example
All select, insert, update, and delete privileges on a table are known as Vendor to a user name Emerson Electric Co.
Query
GRANT
SELECT,
INSERT,
UPDATE,
DELETE on Vendor to Emerson Electric Co.;
2. REVOKE Clause
The REVOKE Statement is just the opposite of the GRANT statement since it takes back all the privileges that were granted to the other users by the GRANT statement. We can revoke any combination of select, insert, update, delete, references, alter, and index.
Syntax
REVOKE privileges on object from user;
Example
To revoke select, update and insert privileges you have granted to Emerson Electric Co.
Query
REVOKE
SELECT,
INSERT,
UPDATE,
DELETE on Vendor to Emerson Electric Co.;
Oracle provides a very fine-grained approach to permissions, allowing nearly any system function to be allowed or denied individually by user and/or role. Listed below are the most commonly used privileges, or grants, that may be applied or revoked. Below this section is a table listing all Oracle grants.
System Privileges
- Create session
- Create table
- Create view
- Create procedure
- Sysdba
- Sysoper
Object Privileges
These Privileges can be assigned to any of the following types of database objects:
- Tables: all, alter, debug, delete, flashback, insert, on commit refresh, query rewrite, references, select, update
- Views: debug, delete, insert, flashback, references, select, under, update
- Sequence: alter, select
- Packages, Procedures, Functions: debug, execute
- Materialized Views: delete, flashback, insert, select, update
- Directories: read, write
- Libraries: execute
- User Defined Types: debug, execute, under
- Operators: execute
- Indextypes: execute
For more information on these privileges please read:
http://psoug.org/definition/GRANT.htm
Previous article: Oracle SQL Commands: Part 6
Next Article:
Oracle SQL Commands: Part 8