SQL

SQL is a standard interactive and programming language for sending queries to databases and updating a database.

SQL commands are instructions used to communicate with the database to perform specific task that work with data. SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality:

• Data Definition Language (DDL) - These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.
• Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.
• Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.
• Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

DDL Statements

DDL is a set of SQL commands used to create, modify, and delete database structures: Generally it includes CREATE , DROP ,ALTER, TRUNCATE, RENAME

The CREATE TABLE is used to create and define a new relational table.

The DROP TABLE is used to delete a table and all data within the specified table.

The ALTER TABLE is used to change an existing table definition:

The TRUNCATE TABLE is used to delete all the rows within the specified table

RENAME table  is used to change the name of a existing tables to the specified name .

Constraints:

» Constraint is a rule that restricts the values for a column on which it is defined.

» Constraints check data as it is entered or updated in the database and prevent data that does not

conform to the constraint's rule from being entered.

» Constraints are classified into two types:

• Column level constraints

• Table level constraints

 Some are the constraints that are used with CREATE TABLE.

NULL/NOT NULL	NOT NULL specifies that a column must have some value. NULL (default) allows NULL values in the column.
DEFAULT	Specifies some default value if no value entered during INSERT.
UNIQUE	Specifies that column(s) must have unique values.
PRIMARY KEY	Specifies that column(s) must have unique values. Index is automatically generated for column
FOREIGN KEY	Specifies that column(s) are defined primary key in another table. Used for referential uniqueness of parent table. Index is automatically generated for column.
CHECK	Applies a condition to an input column value.
DISABLE	Suffix DISABLE to any other constraint to make Oracle ignore the constraint, the constraint will still be available to applications / tools and you can enable the constraint later if required.

ALTER TABLE Statement:  Use alter table  statement for the followings:

•  Add a new column 
•  Modify an existing Column
• Define a default value for the new column 
•  Drop a column

ƒ Example:

1.       Add a new column lien_code to existing table:

ALTER TABLE DAILY_PIPELINE_LOAN_FACT ADD (lien_code varchar2(5));

2.        Change the existing columns data type and size:

ALTER TABLE DAILY_PIPELINE_LOAN_FACT MODIFY  (lien_code Varchar2(10));

3.       ƒ Dropping a column:

ALTER TABLE DAILY_PIPELINE_LOAN_FACT DROP COLUMN lien_code;

4.        SET UNUSED OPTION:  The set unused option marks one or more column as unused so that they can be dropped when required.

» Example:

ALTER TABLE DAILY_PIPELINE_LOAN_FACT SET UNUSED(lien_code);

 Rename table:   

To change the name of the table ,you must be owner of the table.

» Example:

RENAME TABLE Daily_pipeline_loan_fact to Dly_pipeline_loan_fct

 Truncating a table:  

 Remove all rows from the table . Release the storage space used by the table . You can not rollback rows removal when using TRUNCATE.

» Example:

TRUNCATE TABLE daily_pipeline_loan_fact

DML Statements

A DML statement is executed when you:

•  Add new rows to a table
•  Modifying existing rows in a table
•  Removing existing rows from a table

Adding a new row to a table:

INSERT INTO DAILY_PIPELINE_LOAN_FACT (LOANNO, LOAN_STATUS, LOAN_AMOUNT) VALUES(1001,‘FUNDED’,1000)

 Use and substitution in a SQL statement to prompt for values:

INSERT INTO DAILY_PIPELINE_LOAN_FACT (LOANNO,LOAN_STATUS,LOAN_AMOUNT)

VALUES(&LOANNO,‘& LOAN_STATUS’,& LOAN_AMOUNT); ƒ

Copying rows from another table

INSERT INTO  COPY_DLY_PIPELINE_LOAN_FACT  SELECT * FROM DAILY_PIPELINE_LOAN_FACT;

 Modifying existing rows with the UPDATE statement:

UPDATE daily_pipeline_loan_fact  SET loan_status=‘SHIPPED’ WHERE loan_no=‘1001’

Removing existing rows with the DELETE statement:

DELETE FROM daily_pipeline_loan_fact  WHERE loan_no=‘1001’

 MERGE statement:

 Provide the ability to conditionally update or  insert data into a data base table. It performs an update if the rows exist and insert if it is an new row.

TCL Statements

With COMMIT or ROLLBACK statements, you can:

•  Ensure data consistency
•  Preview data changes before making changes permanent
•  Group logical related transaction.   

An automatic COMMIT occurs when:

» DDL statement is issued

» DCL statement is issued

State of data before commit:

• The previous state of data can be recovered. 
• The current user can review the results of the DML operations by using the SELECT statement.  
• Other users cannot view the results of the DML statements by the current user.
• The affected rows are locked; other users cannot change the data within the affected row.
• Data changes are made permanent in the database.
• The previous state of data is permanently lost.
• All users can view the results.
• All save points are erased.

Controlling Transaction

if we have missed and do not want to make the complete changes permanentaly then we can rollback the changes to a save point.

Rolling back changes to a Save point: ƒ Rollback to that marker by using the ROLLBACK TO

SAVEPOINT statement.

Discard all pending changes by suing the ROLLBACK statement:

» Data changes are undone

» Previous state of the data is released

DCL Statements 

Granting system privileges

GRANT create table, create view, create sequence TO scott; 

Role: A role is a named group of related privileges that can be grant to an user.

 e.g  Create a Role

CREATE ROLE manager;

Grant privileges to a role 

 GRANT  create table, create view, create sequence TO manager;

Grant a role to users

GRANT manager to scott, scott1

Granting object privilegesGRANT     SELECT  ON  daily_pipeline_loan_fact  TO   scott1;

Revoke object privileges: Use the revoke statement to revoke all the privileges granted to other user.

REVOKE    SELECT  ON  daily_pipeline_loan_fact  TO scott1;