Oracle view

Posted Posted in Oracle, SQL

View is a virtual table and that does not exist physically. it is created using sql statements on one or more tables. to create view in your own schema you should have CREATE VIEW privilege and to create in other schema you should be having CREATE ANY VIEW privilege. you must have EXECUTE ANY system […]

Oracle Transaction Control Languages(TCL)

Posted Posted in Oracle, SQL

Transaction control statements are used to manage the changes made by DML’s. Savepoint Rollback Commit Set Transaction statements Savepoints: you can create intermediate steps and name it in a complex query using SAVEPOINT. SAVEPOINT lets you undo part of transaction instead of whole transaction.When you ROLLBACK to that savepoint, all savepoints created after that would […]

Oracle Data Control Languages(DCL)

Posted Posted in Oracle, SQL

Data control languages are mainly used to GRANT or REVOKE privileges/access to other users. There are 2 type of privileges. System Privilege: Normally granted by DBA’s to schema objects Object Privilege:Normally granted by the object owner to allow data manipulation GRANT: GRANT is used to grant privileges to other users. Syntax: GRANT PRIVILEGE_TYPE ON SCHEMA_OBJECT […]

Oracle Data Manipulation Language (DML)

Posted Posted in Oracle, SQL

Oracle has Data Manipulation Language(DML) for manipulating data in database. Those are: SELECT: for data retrieval INSERT: For inserting data to table UPDATE:For updating data in table DELETE: for deleting data from table SELECT is mainly used to retrieve data from Oracle database Syntax:

  Description: SELECT: select is the mandatory keyword to retrieve […]

Oracle Data Definition Language(DDL)

Posted Posted in Oracle, SQL

DDL includes: Create,Alter,Drop schema objects Grant or Revoke privileges Analyze information on a table,index and cluster Add comments to data dictionary  Tables are the basic unit of data storage. Data is stored in rows and columns. Naming Convention: The table name must begin with a letter The table name can be upto 30 characters It […]

Oracle Constraints: Check constraints

Posted Posted in SQL

A check constraint allows you to specify a condition on each column in a table.Check constraint is enforced in one column at a time. CREATE check constraint: CREATE TABLE table_name ( column1 datatype null/not null, column2 datatype null/not null, … CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE] ); The DISABLE keyword is optional. If you create […]

Oracle Constraints: Unique key

Posted Posted in SQL

Unique key is a single column or combination of columns uniquely defines the record. Unique key can contain NULL values but not duplicates. CREATE Unique Constraints: CREATE TABLE table_name (   column1 datatype [ NULL | NOT NULL ],   column2 datatype [ NULL | NOT NULL ],   …   CONSTRAINT constraint_name UNIQUE (column1, […]