SQL SELECT Statement

---

The SELECT statement is used to select or display data from a database.

Syntax:

SELECT [DISTINCT] [*] {COL1 [ALIAS],...}
FROM TABLE_NAME;

• SELECT keyword Specifies the list of column.
• DISTINCT keyword show you unique data(suppresses the duplicate data). 
• "*" projects all the column from selected table.
• ALIAS hide the original column name and we can use alias for table name .
• FROM keyword Specifies the table name.

Example:

Selecting all columns.

SELECT * FROM STUDENTS;

Selecting list of column.

SELECT STUDNO,FNAME,LNAME,DOB FROM STUDENTS;

Using Alias name to hide the column name.

SELECT STUDNO as "Student ID",FNAME as "First Name",LNAME AS "Last Name",DOB AS "Date Of Birth"
FROM STUDENTS;

Using Alias name for table.

SELECT A.STUDNO as "Student ID",A.FNAME as "First Name",A.LNAME AS "Last Name",A.DOB AS "Date Of Birth"
FROM STUDENTS A;

The SELECT statement has many optional clauses:

WHERE specifies which rows to retrieve.
GROUP BY groups rows sharing a property so that an aggregate function can be applied to
 each group.
HAVING selects among the groups defined by the GROUP BY clause.
ORDER BY specifies an order in which to return the rows.

SQL DROP Statement

---

In Oracle DROP TABLE statement is use to remove the table from Oracle data base.
For DROP TABLE you should have DROP ANY TABLE system privilege.
DROP TABLE will drop the  associated table indexes  and triggers  and all dependent objects became INVALID.
All synonyms for a dropped table remain, but return an error when used.

Syntax:

DROP TABLE [schema].table_name [ CASCADE CONSTRAINTS(optional) ] [ PURGE (optional) ];

CASCADE CONSTRAINTS
Specify CASCADE CONSTRAINTS to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this clause, and such referential integrity constraints exist, then the database returns an error and does not drop the table.

PURGE
Specify PURGE if you want to drop the table and release the space associated with it in a single step. If you specify PURGE. We can not recover the table if we use the PURGE.

Example:

DROP TABLE STUDENT PURGE;

DROP TABLE example will remove STUDENT table from the data base and PURGE will release the space .

SQL ALTER Statement

---

In Oracle ALTER TABLE statement to add, modify, rename,drop a column and ALTER TABLE statement also use for rename a table.

ADD Column In a Table: ALTER  TABLE statement is use to add columns in a existing table. We can add single or multiple column.

Syntax: Add single column

ALTER TABLE table_name
ADD column_name column_definition;

Add multiple columns

ALTER TABLE table_name
  ADD (column_name1 column_definition,
       column_name2 column_definition,
       ...
       column_nameN column_definition);

Example:

ALTER TABLE STUDENTS
ADD PHONE_NUMBER number(15);

ALTER TABLE example will add a column to the STUDENTS table column name called PHONE_NUMBER.

MODIFY Column In a Table: ALTER  TABLE statement is use to modify columns in a existing table. We can modify single or multiple columns.

Syntax: Modify single column

ALTER TABLE table_name
MODIFY column_name column_type; 

Modify multiple columns

ALTER TABLE table_name
  MODIFY (column_name1 column_type,
          column_name2 column_type,
          ...
          column_nameN column_type);

Example:

ALTER TABLE STUDENTS
MODIFY (PHONE_NUMBER VARCHAR2(15));

ALTER TABLE example will MODIFY a column to the STUDENTS table column name called PHONE_NUMBER from NUMBER to VARCHAR2.

RENAME Column In a Table: ALTER  TABLE statement is use to rename column in a existing table. 

Syntax: Rename single column

ALTER TABLE table_name
RENAME COLUMN old_column_name new_column_name;

Example:

ALTER TABLE STUDENTS
RENAME COLUMN PHONE_NUMBER PH_NO;

ALTER TABLE example will RENAME a column to the STUDENTS table old column name called PHONE_NUMBER and new column PH_NO.



DROP Column In a Table: ALTER  TABLE statement is use to drop column in a existing table. 

Syntax: DROP  column

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

ALTER TABLE STUDENTS
DROP COLUMN PH_NO;

ALTER TABLE example will DROP a column to the STUDENTS table  column name called PH_NO.


RENAME Table: ALTER  TABLE statement is use to rename TABLE existing table. 

Syntax: 

ALTER TABLE table_name
RENAME TO new_table_name;

Example:

ALTER TABLE STUDENTS
RENAME TO STUDENT;

ALTER TABLE example will RENAME table name  STUDENTS to STUDENT.

NB:
If a view, trigger, check constraint, foreign key constraint,  an attempt to rename it will generate an error.


The RENAME COLUMN statement is not allowed if there are any open cursors that reference the column that is being altered.

Lenght Function

Length function returns the number of characters in a value.
If the string is NULL it returns NULL.

Syntax: LENGTH(COLUMNS/Exp)
Example:

Use of LENGTH Function
SELECT 'ORACLE' String,LENGTH('ORACLE') Length FROM DUAL;
SELECT Ename||' Your Name is A Collection of '||LENGTH(Ename)||' Characters.' EnameLength FROM Emp;
SELECT INITCAP(Ename) Name,Job FROM Emp WHERE LENGTH(Job) = 7;

Variable In PL/SQL

---

SELECT INTO clause is used to place the returned data into predefined variables.

Use Of Variables:

• Reusability.
• Temporary Storage.
• Data Manipulation.
• Easy Of Maintenance.

Declare PL/SQL variables:

• All variables are declared and initialized only in the DECLARATIVE section.
• Maximum length  of variable is 30 characters .
• Variable name should be meaningful.
• PL/SQL is case-insensitive, which means v_all_data and V_ALL_DATA refer to the same variable.
• To declare the variable , variable name followed by data type and terminated by semicolon';'.

Variable naming convention:
You should follow the below variable naming convention , which is highly recommended for a standard code. 

Prefix	Data Type
v_	VARCHAR2
n_	NUMBER
t_	TABLE
r_	ROW
d_	DATE
b_	BOOLEAN

Assign value to a variable:
    In PL/SQL assigning values to a variable in three way

• By using assignment operator (:=).
• By fetching data base values (using INTO ).
• By passing IN,OUT or IN OUT to a subprogram.

Syntax:

VARIABLE_NAME DATATYPE  [NOT NULL :=DEFAULT VALUE];

Example:

DECLARE /*Declare variable , variable name V_EMPLOYEE_NAME*/  V_EMPLOYEE_NAME VARCHAR2(100);  BEGIN SELECT ENAME INTO V_EMPLOYEE_NAME FROM EMP WHERE EMPNO=7369;  DBMS_OUTPUT.PUT_LINE('Employee name is '||V_EMPLOYEE_NAME); END;

Base on variable declaration we can classify into two types

Local Variables: Local variable declare in inner block which cannot be referenced by outside blocks. 

Global Variables: Global variable declare in outer block and which can referenced by all inner blocks.

Example Of Global and Local Variables :

DECLARE  /*Declare global variable*/  V_GLOBAL_VAR varchar2(100):='Rabindra';  BEGIN DBMS_OUTPUT.PUT_LINE('Global variable in outer block :' ||V_GLOBAL_VAR);  declare   /*Declare local variable*/   V_LOCAL_VAR varchar2(100):='Oracle';  begin  /*Now i can access global variable in inner block*/  DBMS_OUTPUT.PUT_LINE('Global variable in inner block: '||V_GLOBAL_VAR);  DBMS_OUTPUT.PUT_LINE('Local variable in inner block: '||V_LOCAL_VAR);  end;  /*We cannot access Local variable in outer block, it will through error comment below DBMS_OUTPUT.PUT_LINE*/  --DBMS_OUTPUT.PUT_LINE('Local variable in outer block: '||V_LOCAL_VAR);  END;

SQL CREATE Statement

---

While creating the table we need to follow below rules:

• The user should have CREATE TABLE privilege.
• The table name should starts with letter.
• The table name 1-30 characters long.
• Table name cannot be duplicate with other objects.
• Table name should not be oracle server reserved word.

Syntax:

CREATE TABLE Table_Name (Column_Name1 1<Data_type> [size],Column_Name2  <Data_type>[size],...);

Example:

CREATE TABLE STUDENTS
        (STUDNO NUMBER(6),FNAME VARCHAR2(30),LNAME VARCHAR2(30),
         DOB DATE,DOJ DATE,FEES NUMBER(7,2),
         GENDER VARCHAR2(1),INSERTBY VARCHAR2(10));

If you try to create with same column name in same table, it will show you below error


Two columns can't have same name in same table :

create table col_same_name(col1 varchar2(10),col1 number);
ORA-00957: duplicate column name

Other way to create table:

If you want to create a table in your database with existing table structure than follow the below SQL.

CREATE TABLE STUDENTS_TBL AS SELECT * FROM STUDENTS WHERE 1=2;

If you want to create a table in your database with existing table structure and data than follow the below sql

CREATE TABLE STUDENTS_TAB AS SELECT * FROM STUDENTS;

Error List : While creating a table we can get bellow errors.

List Of Error
ORA-00972: identifier is too long
ORA-00955: name is already used by an existing object
ORA-00903: invalid table name
ORA-00957: duplicate column name

Group By Clause

GROUP BY clause is use to group the rows.

Syntax:
SELECT column1,groupfunction(column)
FROM TableName
WHERE Conditions
GROUP BY columns
HAVIN filters
ORDER BY columns;

How GROUP BY clause works:

SELCT job FROM EMP;

SELECT JOB FROM EMP GROUP BY EMP;

Guidelines:

• Non group functional columns should be declare in GROUP BY clause
• WHERE clause will filter the records before the grouping the records.
• Columns aliases can not be used in GROUP BY clause.
• All the SELECT list column must be part of GROUP BY but all GROUP BY columns may not be part of SELECT list.

Trim Function

TRIM heading  or trailing  characters or both from the character string.
LEADING trim the leading characters.
TRAILING trim trailing  characters.
BOTH OR trim both leading or trailing  characters.

Syntax: TRIM(CHAR,SET)
Example:

Use of LTRIM Function
SELECT 'xyzXxyORACLE' OrgStr,LTRIM('xyzXxyORACLE', 'xzyX') Ltrim FROM DUAL;
SELECT LTRIM(' Oracle') FROM DUAL;  --trimming space

Rtrim Function

Trimming of trailing characters from a character string.
The rightmost characters that appear in the set are removed.

Syntax: RTRIM(CHAR,SET)
Example:

Use of RTRIM Function
SELECT 'BROWNINGyxXxy' String, RTRIM('BROWNINGyxXxy', 'xyX') Rtrim FROM DUAL;
SELECT  'BROWNING ' String,RTRIM('BROWNING ') Rtrim FROM DUAL --trimming space

Ltrim Function

Trimming of heading characters from a character string.
The leftmost characters that appear in the set are removed.

Syntax: LTRIM(CHAR,SET)
Example:

Use of LTRIM Function
SELECT 'xyzXxyORACLE' OrgStr,LTRIM('xyzXxyORACLE', 'xzyX') Ltrim FROM DUAL;
SELECT LTRIM(' Oracle') FROM DUAL;  --trimming space

Concat Function

This function concatenates the first and second character value(accept only two parameters).

Syntax: CONCAT(column1,column2)
Example:

Use of CONCAT Function
SELECT 'Oracle' AS String1, 'Corporation' AS String2,CONCAT('Oracle', CONCAT(' ', 'Corporation')) AS Concat FROM DUAL;

Initcap Function

It converts Upper case for the first letter of each word, keeping all other letters as lower case.

Syntax: INITCAP(column/expression)
Example:

Use of INITCAP Function
SELECT 'oracle corporation'  AS String, INITCAP('oracle corporation') AS InitCap FROM DUAL;
SELECT 'oracleinq blogspot in' AS String,INITCAP( 'oracleinq blogspot in') AS InitCap FROM DUAL;

Upper Function

This function converts all characters into upper case.
The return value has the same data type as argument CHAR or VARCHAR2 type

Syntax: UPPER(column/expression)
Example:

Use of UPPER Function
SELECT 'oracle' AS String, UPPER ('oracle') AS UPPER FROM DUAL;
SELECT Ename||' is Designated As '||LOWER(Job) FROM EMP WHERE Job = 'MANAGER';
SELECT Ename||' is Designated As '||LOWER(Job) Ename FROM Emp WHERE Job = UPPER('manager');

Lower Function

This function converts all characters into lower case.
The return value has the same data type as argument CHAR or VARCHAR2 type

Syntax: LOWER(column/expression)
Example:

Use of LOWER Function
SELECT 'ORACLE' AS String, LOWER ('ORACLE') AS LOWER FROM DUAL;
SELECT LOWER(Ename) Ename, LOWER(Job) Job, Sal, HireDate FROM EMP;
SELECT LOWER(Ename)||' is Working As '||Job Employee FROM EMP;
SELECT LOWER(Ename)||' is Working As '||Job Employee FROM EMP WHERE LOWER(Job) = 'manager';

Advanced PL/SQL

Home Variable In PL/SQL ALTER DROP RENAME TRUNCATE		Home CREATE ALTER DROP RENAME TRUNCATE		Home CREATE ALTER DROP RENAME Global Temporary Table

Basic PL/SQL

Home Variable In PL/SQL ALTER DROP RENAME TRUNCATE		Home CREATE ALTER DROP RENAME TRUNCATE		Home CREATE ALTER DROP RENAME TRUNCATE

Advanced SQL

Home Data Type Constraint SET Operators Index Object Oriented Concepts Local Temporary Tables		Home Need Of Dual Table Joins Hierarchical Query Data Base Security or Object privileges Merge		Home Functions Sub Query View Table Partitions Global Temporary Tables DBMS_XPLAN.DISPLAY_CURSOR

Update with RETURNING clause

1 2 3 4 5 6 7 8 9 10

DECLARE TYPE TYPE_NAME_TB IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; TYPE_NAME_TB_V TYPE_NAME_TB; EMPNAME VARCHAR2(4000); EMPSALARY VARCHAR2(400); BEGIN UPDATE EMP SET SAL = SAL + 1000 WHERE DEPTNO=20 RETURNING ENAME BULK COLLECT INTO TYPE_NAME_TB_V; FOR I IN 1.. TYPE_NAME_TB_V.COUNT LOOP DBMS_OUTPUT.PUT_LINE(TYPE_NAME_TB_V(I)); END LOOP; END;

Basic Oracle SQL

Home CREATE ALTER DROP RENAME TRUNCATE INSERT UPDATE DELETE SELECT COMMIT ROLLBACK SAVEPOINT GRANT REVOKE ALIASES LITERALS ARITHMETIC OPERATORS CONCATENATION OPERATOR DISTINCT WHERE CLAUSE ORDER BY GROUP BY HAVING COMPARISON OPERATORS AND(&) OPERATOR OR OPERATOR NOT OPERATOR BETWEEN...AND... OPERATOR IN/NOT IN OPERATOR IS NULL OPERATOR LIKE OPERATOR		Home ANY & ALL Number Varchar2 Char Date Lower Function Upper Function Initcap Function Concat Function Substr Function Instr Function Length Function Lpad Function Rpad Function Ltrim Function Rtrim Function Trim Function CHR Function Replace Function Translate Function Round Function Trunc Function Cell Function Floor Function Mod Function Power Function Sqrt Function Sign Function ABS Function SYSDATE Function ADD_MONTHS Function GREATEST FUNCTION		Home Adding Column in a Table Modify Column in a Table Drop Column in a Table Connect By Prior Start With Case Decode Rownum Union UnionALL Intersect Create View Simple View Complex View View Data Dictonary Drop View Create Sequence Use Sequence Alter and Drop Sequence Create Index Drop Index Enable Enable & Disable Index Synonym Comment On Table Rank Function Dense_Rank Function Default value NULL And NVL COUNT FUNCTION SOUNDEX FUNCTION

INDEX

Oracle Basic SQL

Oracle Basic PL/SQL

Oracle Advanced SQL

Oracle Advanced PL/SQL

Oracle DBA Guide

UNIX

Data Structures