SQL Statements in Oracle

Relational Database Terminologies:

ROW or TUPLE:
  • A row or a TUPLE represents all data required in an entity for a particular instance.
  • Each row in an entity is uniquely identified by declaring its PRIMARY KEY or UNIQUE KEY.
  • The order of the row is not significant while retrieving data.

COLUMN or ATTRIBUTE:
  • A column or an attribute represents same kind of data collected vertically in a table.
  • The order of the column is not significant while storing the data.

FIELD:
  • A field is the intersection of a row and a column.
  • A field can have only one value or may not have a value at all.
  • The absence of value in a field is represented as NULL value in Oracle.
Relating Multiple tables:
  • Each table should contain data that describe exactly and only one entity.
  • Data about different entity is stored in different tables.
  • RDBMS enables the data in one table to be related to another table by using foreign key.
  • A foreign key is a column or a set of columns that refers to a primary key in the same table or another table.

“Structured Query Language” Statements in Oracle:

ORACLE SQL comply with Industry accepted standards.
Below are various categories of SQL Statements:
1. Data Retrieval Statements (DRL):
SELECT statement: A SELECT statement retrieves zero or more rows from one or more database tables or views.
In most applications, SELECT is the most commonly used data query language (DQL) command. 
Ex: SELECT col1, col2, … colN FROM Tab1;
2. Data Manipulation Language (DML):
It Used to change the data contains in a database objects like TABLE / VIEW / MATERIALIZED VIEW.
INSERT statement: It used to insert records into table.
UPDATE statement: It used to update the previously existing records in a table.
DELETE statement: It used to delete a part of table (/ few selective records) or the complete records.
3. Data Definition Language (DDL)
It  Used to change the structure and arrangement of the Database objects.
CREATE statement: It used to create a new object to keep the information for a specific
entity. 
Ex:  create table/view/materialized view/sequence/synonyms/index/ etc
ALTER statement: It used to Alter / Change the a structure of the database object 
Ex:  ALTER TABLE,add column, drop column
DROP statement: It used to drop any database objects. 
Ex: Drop table/ view/ index/ sequence synonyms etc.
RENAME statement: It used to rename Database objects 
Ex: Rename table to new_name
TRUNCATE statement: It used to Truncate a table, once truncate a table data will be remove permanently from a table and we won’t get back our data using rollback.
4. Transaction Control Language (TCL): 
It used to control the transection like wheather the current transaction should SAVE now or should UNDO its changes till a particular pointer.
COMMIT statement: It used to save the data change till current time.
ROLLBACK statement: It used to undo the changes till a perticular savepoint mentioned.
SAVEPOINT statement: It used to mention a pointer to the instance so that later we can roll back the changes till that particular save point.
5. Data Control Language (DCL): 
It used for the control flow of data means to specify wheather a user should have the allowed access to any specific database objects or not.
GRANT statement: It used to ALLOW some privilege to a user. 
Ex: GRANT SELECT INSERT UPDATE DELETE ON Table1 TO User1;
REVOKE statement: It used to REMOVE the previously allowed privilege from a user.
Ex: REVOKE DELETE ON table1 FROM user1;

For more details on RDB Terminologies and SQL Statements visit to the link: < https://youtu.be/4sj-QVlDTA0  >

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn