Oracle SQL Commands: Part 7

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_idSup_nameItemQuantityPrice
0001Analog Devices, IncPC480k
0002Emerson Electric Co.LAPTOP243k
0003Brady CorporationPC660k
0004Cyntec Co. LtdLAPTOP858k
0005Intel CorporationMOBILE1035k
0006Micron Technology, IncLAPTOP374k

 
Query
 

SELECT Sup_name

FROM Supplier

WHERE item = laptop;
 
Result

Sup_nameItem

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_idStateItemPrice
44332MaharashtraSoap300
44338Uttar PradeshTalc480
44336Himachal PradeshBlanket226
44337Himachal PradeshKitchen Ware5200
44339MaharashtraMedicines1200

 
Query
 

UPDATE Customer

SET Item = Shampoo

WHERE Item='Soap';
 
Result


Cust_idStateItemPrice
44332MaharashtraShampoo300
44338Uttar PradeshTalc480
44336Himachal PradeshBlanket226
44337Himachal PradeshKitchen Ware5200
44339MaharashtraMedicines1200


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_idMed_NameQuantityPrice
53112Combiflame3001000
67453Pantocid450750
45643Montair 102701500

 
Query
 

SELECT Med_Name

FROM Medicine

WHERE Price < 1000;
 
Result


Med_NamePrice

Pantocid

750


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_idMed_NameQuantityPrice
53112Combiflame3001000
67453Pantocidh450750
45643Montair 102701500

  
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