Sql

Oracle -------Overview of DBMS -----------------------> DBMS = Database Management System --> It's a software, which offers to store, maintain and manage the databases. --> DataBase: "DataBase" is the collection of large tabular formatted data. In other words, database is a collection of interlinked tables. --> Each table can be called as entity here. --> There may be some links (references) between one table and another table. Ex: College db, Company db etc. --> Table: It's a collection of some data in the form of rows and columns. --> Row: "The horizontal collection of data values" is called as "row". --> Column: "The vertical collection of data values" is called as "column". --> To store this kind of software.

data, we require a software. That software is "DBMS"

--> Always the DBMS is called as a "Package". ==================================== Classification of Software -----------------------------1) Languages 2) Packages 1) Languages -----------------> These are known as "Programming Languages", where the programmer can development some "Computer programs". Ex: Pascal, Cobol, C, C++, VB, ASP, VC++, Java, .NET etc. --> The overall target of any programming language is to develop some "Software applications". --> Whenever that application is used by any company / organization, then that can be called as "Project". Ex: Banking Project, College Project, Online Shopping Project etc. 2) Packages ---------------> The ready-made software could be called as "Package".

--> This can be directly used by the user. Classification of Packages -----------------------------1) Word Processing Packages 2) Presentation and Designing Packages 3) WorkSheet Packages 4) DBMS Packages 5) Accounting Packages 6) Utility Packages ==================================== 1) Word Processing Packages -----------------------------------> It contains necessary options to create and develop documents, with rich features like better formatted text, images, colors etc. Ex: WordStar, WordPerfect, MS-Word 2) Presentation Packages -------------------------------> It offers to create better-looking presentations with multi-media features. Ex: MS Power Point, Flash etc. 3) WorkSheet Packages ----------------------------> Offers to develop work sheets, work books. Ex: Lotus 123, MS Excel etc. 4) DBMS Packages -----------------------> Contains necessary features to create, store, manipulate and manage the databases. Ex: dBase, FoxPro, Visual FoxPro, MS Access, Sybase, DB2, Oracle, MS Sql Server etc. 5) Accounting Packages -----------------------------> Used for all purposes of "Financial Accounting" in any Ltd., Pvt Ltd. Organizations. Ex:

Tally, TATA-EX, Power, Wings, 4 to 4, Focus etc.

6) Utlity Packages ---------------------

--> These are the useful softwares, which can be used by any type of user with general purpose. ===================================== Types of DataBases ----------------------1) File DataBases These databases could be maintained in the form of db file. The main drawback of these databases is the file database can be accessed by only a single client at-a-time over the network. In other words, it would not be accessed from multiple clients simultaneously. 2) Server DataBases In order to overcome the above limitation of file databases, server databases were introduced. The server databases gets maintained in the form of "objects". The advantage of "objects" maintenance in the databases is, the db object could be accessible from any no. of clients over the network simultaneously. ===================================== Application Development Tools -----------------------------------1) Backend 2) Frontend 1) Backend ---------------> A DBMS software could be called as "Backend" tool. --> It's major activities are to store, maintain and manage the large scale databases. --> It offers only storage mechanisam and doesn't offers UI development. --> To offer the UI for the application, we require another tool known as "Frontend" tool. Ex: All DBMS software, mentioned above. 2) Frontend ---------------> It's a programming language, which is used to develop the UI. --> This application can be connected with the backend storage mechanism, as per necessity.

Ex: VB, VC++, D2K (Developer 2000), .NET, Java ====================================

Database Architectures: ---------------------------The db architecture specifies type of backend and type of frontend being used. There are four db architures are available. A) B) C) D)

One-Tier Architecture Two-Tier Architecture Three-Tier Architecture Multi-Tier Architecture

A) One-Tier Architecture: -------------------------------> It contains one tool that acts as both backend and frontend. Ex: MS Access Visual FoxPro etc. Limitation of One-Tier ---------------------------> The tool may not support better backend features / better frontend features. B) Two-Tier Architecture: ------------------------------> "One tool acts as backend and another tool acts as frontend". --> It overcomes the limitations of One-Tier architecture. Ex: Oracle / Developer 2000 MS Access / VB MS Access / VC++ FoxPro / VB FoxPro / VC++ Limitation of Two-Tier: ----------------------------> Both these backend and frontend tools must be released by the same company. C) Three-Tier Architecture --------------------------------> It is an extension of Two-Tier architecture. --> It contains backend and frontend tools seperately and also contains "Driver" software. --> The "driver" software can also be called as "Provider" / "Interface" software. --> It acts as mediator between backend and frontend. --> It's small logical software, which can't be opened individually.

--> Using this driver software only, Three-Tier architecture is possible. --> This driver software may be released by the backend company or frontend company. Advantage of Three-Tier: ------------------------------> The backend and frontend tools may be released by two different companies. Limitation of Three-Tier: ------------------------------> It does't supports "online db communications". D) Multi-Tier Architecture: --------------------------------> It can also be called as "N-Tier architecture". --> It is meant for "Online db communications". --> It is applicable for "WWW" (World Wide Web) --> It is an extension of Three-Tier. --> It is implemented by "Web development languages" only. Ex: J2EE, ASP, ASP.NET, PHP. ==================================== Models of DBMS ------------------1) DBMS (Database Management System) It offers general db features like db creation, storage, maintenance, manipulations and administration etc., only. 2) RDBMS (Relational Database Management System) In addition to the standard DBMS features, it supports "Referential Integrity" concept. 3) ORDBMS (Object Oriented Relational DBMS) In addition to the RDBMS features, it offers maintenance of db in the form of db objects. 4) NDBMS (Network Oriented DBMS) In addition to the ORDBMS features, it contains network based db connections, network oriented administration features. IMP Note:

"Oracle" known as DBMS, RDBMS, ORDBMS, NDBMS also.

===================================== Oracle Corp. Products -------------------------

1) Oracle 2) D2K (Developer 2000) 1) Oracle ------------> It acts as backend tool. 2) D2K ----------> It acts as frontend tool. --> It contains internal link with oracle db. ==================================== Oracle -------1) SQL (Structured Query Language) 2) PL/SQL (Procedural Language extensions for SQL) ==================================== Versions of Oracle --------------------i) Oracle 2.0 ---------------> Released in 1979. --> Supports minimum SQL functionality with db storage mechanism. --> It is the single user dbms. ii) Oracle 3.0 ----------------> Released in 1983. --> Supported by UNIX O/S only. --> It is the multi-user dbms. iii) Oracle 4.0 -----------------> Released in 1984. --> New features are added into SQL, when compared with Oracle 3. iv) Oracle 5.0 -----------------> Released in 1985. --> New features are added into SQL, when compared with Oracle 4. v) Oracle 6.0 ----------------> Released in 1988. --> Contains better security features and backup features.

vi) Oracle 7.0 -----------------> Released in 1992. --> Contains new features like "Referential integrity" and "triggers" etc. --> PL/SQL was introduced. vii) Oracle 8.0 ------------------> Released in 1997. --> Contains "Object Oriented Features" in dbms, so that it is known as ORDBMS. viii) Oracle 8i ------------------> Released in 1999. --> Supports "online db transactions" on Internet. --> The letter "i" stands for "Internet" viii) Oracle 9i ------------------> Released in 2001. --> Contains few misclaneous additional features, when compared with 8i. --> Support of XML documents in db. ix) Oracle 10g ------------------> Relased in 2003. --> The letter "g" stands for "grid-computing" technology. x) Oracle 11g -----------------> Relased in 2005. --> The letter "g" stands for "grid-computing" technology. ==================================== ==================================== Data Types of Oracle -----------------------1) NUMBER 2) CHAR 3) VARCHAR / VARCHAR2 4) DATE 5) LONG ===================================== 1) NUMBER

---------------> To hold any type of numerical values. --> It can hold positive or negetive values also. --> It can hold integer values / floating point values. Syn:

NUMBER(width) NUMBER(width,dec)

Note: Here, "width" means total no. of digits. Ex:

NUMBER(3) ==> (Max: 999) NUMBER(5) ==> (Max: 99999) NUMBER(5,2) ==> (Max: 999.99) NUMBER(10,4) ==> (Max: 999999.9999)

Note:

The maximum value for "width" : 19 The maximum value for "dec"

:

8

2) CHAR -----------> To store the character type of values. --> It can hold single / multiple characters also. --> Use single quotes ( 'xxxx' ) while specifying the string values. Syn:

CHAR(width)

Here, width means no. of characters to be stored. Ex: Note:

CHAR(20) The min value for width is 1. Max width is 2000.

Limitation of CHAR:

It occupies the un-used memory locations also.

3) VARCHAR / VARCHAR2 -------------------------------> This is also used to hold string type values. --> It is preferrable, when compared with CHAR. --> It overcomes the limitation of CHAR type. --> Use single quotes ( 'xxxx' ) while specifying the string values. Syn:

VARCHAR2(width)

Note:

Min width is 1. Max width is 4000.

4) DATE -----------> To hold date type of values --> Use single quotes ( 'xxxx' ) while specifying the date values. --> Date format: 'dd-mon-yy' --> Ex Date : '2-apr-08' Syn:

DATE

5) LONG -----------> It can hold OLE objects. --> One table able to contain exactly one LONG column. More than one long columns are not allowed in the table. --> Max capacity: 2 GB Syn:

LONG

==================================== Classification of SQL Commands ------------------------------------I) DDL (Data Definition Language) II) DML (Data Manipulation Language) III) TCL (Transaction Control Language) IV) DCL (Data Control Language) ===================================== I) "DDL" Commands: -----------------------1) CREATE To create a new table 2) DROP To delete an existing table 3) RENAME To rename an existing table, with a different name 4) ALTER To make modifications in the table structure.

II) "DML" Commands: -------------------------1) INSERT To insert a new row into the existing table. 2) DELETE To delete an existing row. 3) UPDATE

To make changes / modifications in table data. 4) SELECT To retrieve the data from existing table. III) "TCL" Commands: -------------------------1) ROLLBACK 2) COMMIT 3) SAVEPOINT IV) "DCL" Commands: --------------------------1) GRANT 2) REVOKE =================================== Executing the above commands practically =========================== a) Creating a new table ---------------------------Command:

CREATE (DDL)

Syn: CREATE TABLE TableName(Column1 DataType(width), Column2 DataType(width), Column3 DataType(width), ........); B) Inserting the data into the table ---------------------------------------Command: Syn:

INSERT(DML) INSERT INTO TableName VALUES(Val1,'Val2',Val3,....);

Note: While speciying the values, copulsory use single quotes for CHAR / VARCHAR2 / DATE types of values. C) Retrieving the data from existing table -----------------------------------------------Command: Syn:

Here,

SELECT(DML) SELECT * FROM TableName; SELECT ColumnName FROM TableName; SELECT Col1,Col2,.... FROM TableName;

* means "all columns".

D) Retrieving the list of all existing tables -----------------------------------------------Command:

SELECT(DML)

Syn:

SELECT

*

FROM

TAB;

Here, TAB represents all the tables. E) Dropping an existing table: ---------------------------------Command:

DROP (DDL)

Syn:

DROP TABLE TableName;

F) Renaming a table: -----------------------Command:

RENAME (DDL)

Syn:

RENAME

OldTableName

==================================== OPERATORS -------------1) Arithmatical op +, -, *, / 2) Relational op <, >, <=, >=, =, <> 3) Logical op AND, OR, NOT 4) Special op IN, NOT IN, BETWEEN NOT BETWEEN LIKE NOT LIKE IS NULL IS NOT NULL

TO

NewTableName;

===================================

Working with special op ---------------------------1) IN --------> This operator contains specific possibilities for a specific column. --> Whenever the column data is matched with any one of the specified values, then that data can be received. Syn:

WHERE

ColName

IN

(Val1,Val2,Val3.....);

2) NOT IN -------------> It is exactly reverse to IN operator. --> It retrieves the data except the given data values. Syn:

WHERE

ColName

NOT

IN

(Val1,Val2,Val3.....);

3) BETWEEN ----------------> It displays the data, whenever it is in the specified boundaries (with min and max values). Syn:

WHERE

ColName

BETWEEN

MinVal

AND

MaxVal;

4) NOT BETWEEN ---------------------> It displays the data, whenever it is not in the specified boundaries (with min and max values). Syn:

WHERE

ColName

NOT BETWEEN

MinVal

AND

MaxVal;

5) LIKE ----------> It offers to specify a format for the value. --> It retrieves the data, which is matched with that format. --> In order to specify the "format", we require to use "format specification characters". Format specification chars ------------------------------1) % (Multiple char) 2) _ (Single char) Syn:

WHERE

ColName

LIKE

'format';

6) NOT LIKE ----------------> It is reverse to LIKE operator. --> It retrieves the data, which is not matched with that format. Syn:

WHERE

ColName

NOT LIKE

'format';

7) IS NULL --------------> It retrieves only NULL values in the table. Syn:

WHERE

ColName

IS

NULL;

8) IS NOT NULL -------------------> It retrieves the data except NULL values in the table. Syn:

WHERE

ColName

IS

NOT

NULL;

=================================== Deleting table data -----------------------> This is used to delete one / more row(s) in the table. Command: DELETE (DML) Types of deletion -------------------a) Un-conditional deletion b) Conditional deletion ==================================== a) Un-conditional deletion --------------------------------> This includes with deleting the rows, without any condition. --> This leads to deletion of all the rows in the table. Syn:

DELETE

FROM

TableName;

b) Conditional deletion ----------------------------> This is to delete specific no. of rows in the table, based on the given condition. Syn:

DELETE

FROM

TableName

WHERE

==================================

Condition;

Updating the table data -----------------------------> This is used to update one / more row(s) in the table. Command: UPDATE (DML) Types of updation -------------------a) Un-conditional updation b) Conditional updation a) Un-Conditional updation -------------------------------Syn: UPDATE TableName SET ColName = value; b) Conditional updation ---------------------------Syn: UPDATE TableName SET

ColName = value

WHERE

condition;

=================================== =================================== Altering the table ---------------------> This is to make changes in the "table structure". --> Table Structure is format that explains about all the column names, data types and width. Ex: Table Structure for "Students" Table ------------------------------------------Column Name ---------------StuNo Name DOB

DataType Width -----------------NUMBER 5 VARCHAR2 20 DATE --

==================================== Possible modifications in the table structure --------------------------------------------------1) Adding a new column. 2) Dropping an existing column. 3) Change the data type of existing column. 4) Change the width of existing column. Command:

ALTER (DDL)

==================================== 1) Adding a new column ---------------------------Syn:

ALTER

TABLE

TableName

ADD(NewColName

Datatype(width));

2) Dropping an existing column ------------------------------------Syn:

ALTER

TABLE

TableName

DROP

COLUMN

ExistingColName;

3) Changing the data type of existing column ---------------------------------------------------Syn:

ALTER

TABLE

Rule to follow:

TableName

MODIFY(ExistingColName

NewDataType(width));

The given column should be "empty", to change its data type.

4) Changing the width of existing column -----------------------------------------------Syn:

ALTER

TABLE

TableName

MODIFY(ExistingColName

DataType(NewWidth));

Rule to follow: The given column should be "empty", to decrease the column width. But it may / may not be empty to increase the column width. =================================== Integrity Constraints -------------------------> Integrity constraints are the rules that can be applied to a specific column in the table. --> The main purpose of applying these rules is to maintain perfect data in the tables, without having any mistakes. --> According to this the db manager creates the tables along with some rules. --> While inserting / updating automatically checks the given satisfied, then only it allows it won't allow the data to the

the data of the table, db storage mechanism condition(s). Whenever the given rules are the data to be inserted into the table. Otherwise, stored.

--> But the rules are various types. In order to implement different rules, Oracle offers several constraints. Constraints --------------

1) 2) 3) 4) 5)

NOT NULL UNIQUE PRIMARY KEY CHECK REFERENCES

==================================== 1) NOT NULL ----------------> It is one of the constraints, which make a column as mandatory column. --> That means the user can't insert NULL values into NOT NULL column. --> Finally the NOT NULL column can't be blank. In other words, the NOT NULL column can't contain NULL values. Syn:

ColName

DataType(width) NOT NULL

2) UNIQUE --------------> UNIQUE means "without duplicates". --> It is the another constraint available in Oracle, which avoids entry of duplicate values in a specific column. --> The UNIQUE column can't contain any duplicate values. Syn:

ColName

DataType(width) UNIQUE

3) PRIMARY KEY ---------------------> According to the good db designing practice, it is strongly suggested to maintain a primary key column for each table. --> The PRIMARY KEY column can't contain duplicates and NULL values also. --> PRIMARY KEY = UNIQUE + NOT NULL --> Generally, "ID" columns are most suitable for primary key columns. Syn:

ColName

DataType(width) PRIMARY KEY

4) CHECK ------------> This is used to provide the user-defined conditions are constraints. --> Here, the developer gives a condition. The column can contain the data, which satisfies the given condition. Syn:

ColName

DataType(width) CHECK (Condition)

5) REFERENCES -------------------

--> It is the most important constraint, using which it is possible to implement "Referential Integrity". --> The "Referential Integrity" concept discussed in the next chapter. ==================================== Referential Integrity -------------------------> It is the major feature of RDBMS. --> As Oracle called as RDBMS, it supports "Referential Integrity" concept. --> According to this, it is possible to establish "relations" among two or more tables. --> According to this Referential Integrity, two kinds of tables are maintained. 1) Master Table 2) Detailed Table 1) Master Table -------------------> It contains the master (main) information. --> Depending on master table of "detailed table" can be created. --> In other words, the master table contains the "independent" data. --> It should be defined along with "primary key" column. 2) Detailed Table ----------------------> It contains the "dependent" data, which depends on master table data. --> It contains a reference to master table. --> It should be defined along with "reference key" column. ===================================== Steps for Development ----------------------------> Create the master table create table mastertablename(primarykeycolname datatype(width) primary key, colname datatype(width), ...); --> Create the detailed table create table detailedtablename(colname datatype(width), colname datatype(width),..., referencekeycolname datatype(width) references mastertable(primarykeycolname)); =====================================

IMP rules for Implementation of Referential Integrity --------------------------------------------------------------> At first, master table is to be created and then detailed table is to be created. --> The master table should contain primary key col and detailed table should contain reference key col. The reference key can also be called as "foreign key". --> The names of primary key col and foreign key col generally will be same, but those may be same or may not be same. That means there is no such restriction to maintain same name for primary key and reference key columns. --> One table can be the master table for multiple detailed tables also. --> The detailed table may contain primary key, and can act as master table for another detailed table. --> The foreign key can be created along with "ON DELETE CASCADE" option, if needed. --> While inserting the data into master table, any duplicate and NULL values are not allowed into primary key column. --> While inserting the data into detailed table, the value to be inserted to into the reference key column should be already exist in the primary key column at master table. --> The data at master table can't be deleted directly, whenever its respective "child rows" are existed in the detailed table. --> The datatype and width for primary key and foreign key should be same. ==================================== ON DELETE CASCADE --------------------------> It is the additional option that could be used while the implementation of detailed table. --> Whenever the referential integrity has been implemented with this option, the master table data directly can be deleted even though corresponding child records are existed in the detailed table. At the time of this kind of deletion, the respective child records automatically get deleted. ==================================== Constraint Naming: / ----------------------1) System-defined Naming 2) User-defined Naming

==================================== Altering the table constraints --------------------------------1) Drop an existing constraint 2) Enable the constraint 3) Disable the constraint 4) Add new constraint =================================== 1) Drop an existing constraint ---------------------------------Syn:

ALTER

TABLE

TableName

DROP

CONSTRAINT

ConstraintName;

2) Disable the constraint ---------------------------Syn:

ALTER

TABLE

TableName

DISABLE

CONSTRAINT

ConstraintName;

3) Enable the constraint ---------------------------Syn:

ALTER

TABLE

TableName

ENABLE

CONSTRAINT

ConstraintName;

4) Adding new constraints -----------------------------i) Primary key: Syn: ALTER TABLE TableName ADD CONSTRAINT KEY(ColName);

ConstraintName

PRIMARY

ii) Unique -----------Syn: ALTER TABLE TableName ADD CONSTRAINT UNIQUE(ColName);

ConstraintName

iii) Not Null -------------Syn: ALTER TABLE TableName MODIFY(ColName ConstraintName NOT NULL); iv) Check -----------

DataType(width) CONSTRAINT

Syn: ALTER TABLE TableName ADD CONSTRAINT CHECK(Condition);

ConstraintName

iv) References ----------------Syn: ALTER TABLE DetailedTableName ADD CONSTRAINT ConstraintName KEY(PrimaryKeyCol REFERENCES DetailedTable(RefKeyCol);

FOREIGN

=================================== Joins -------> The process of retrieving the data from two or more tables is called as "Joins" --> This join operation is possible, whenever there is "Referential Integrity" among these tables. Types of Joins: -----------------1) Simple Join 2) Equi Join 3) Non-Equi Join 4) Outer Join =================================== 1) Simple Join ----------------Syn: Ex:

SELECT * FROM TABLE1,TABLE2,.....; SELECT * FROM EMP,DEPT;

Note: It retrives the correct data and wrong data also. Actually it merges the each row detailed table with each row of master table. 2) Equi Join ---------------> It is an extension to Simple Join. --> It retrieves only correct data. --> It contains a condition with "=" operator. Syn: SELECT * FROM MasterTableName, DetailedTableName MasterTable.PrimaryKey = DetailedTable.ForeignKey;

WHERE

3) Non-Equi Join ---------------------> It is an extension to Simple Join. --> It retrieves only wrong data. --> It contains a condition with a relational operator, other than "=" operator. Syn: SELECT * FROM MasterTableName, DetailedTableName MasterTable.PrimaryKey <> DetailedTable.ForeignKey;

WHERE

4) Outer Join -----------------> It is an extension to Equi Join. --> It retrives the data from master table, which not having the respective child records also. Ex: Syn: SELECT * FROM MasterTableName, DetailedTableName MasterTable.PrimaryKey = DetailedTable.ForeignKey(+);

WHERE

==================================== Functions in Oracle -----------------------> Function: process.

A function is just like a small program that performs a certain

--> A function contains a name and it can be called with its name. --> If necessary, one or more values can be sent to the function. --> A function produces / returns a value, after completion of some logic. ==================================== Types of Functions supported by Oracle ---------------------------------------------I) Pre-defined Functions II) User-defined Functions I) Pre-defined Functions ------------------------------> A ready-made function is called as "Pre-defined" / "System-defined" function. --> Those function can be called by the programmer.

II) User-defined Functions --------------------------------> A function i.e. created by the programmer is called as "User-defined" function. ==================================== Pre-defined Functions Categories in Oracle --------------------------------------------------> These pre-defined functions offer pre-defined functionality. --> There are two models of pre-defined functions available. i) Aggregate Functions ---------------------------> These are also known as single row functions. --> Generally, these functions can be called with "DUAL" table. Syn:

SELECT

FunName(Arguments)

FROM

DUAL;

Note: DUAL is a system-defined table, which contains only one row and one column. It is used to offer table representation for general purpose values. Ex:

SELECT SQRT(10) FROM DUAL;

Here, 10 is the general constant value, DUAL offers "table" representation for 10. --> Again, these aggregate functions are defined as several types. Types of Aggregate functions ---------------------------------A) Numeric Functions B) Character / String Functions C) Convertion Functions D) Date Functions ------------------------------------------------------------------ii) Group Functions -----------------------> The group functions are also called as "Multi-row functions". --> These functions performs the logic once per all the rows. Ex:

SELECT MAX(SAL) FROM EMP;

==================================== A) Numeric Functions ============== 1) SQRT(N) Returns the square root value of N. 2) ABS(N) Returns the absolute (positive) value of N. 3) FLOOR(N) Returns the previous integer value of N. (Removes the decimal part). Ex:

10.45

==> 10

4) CEIL(N) Returns the next integer value of N. Ex:

10.45

==> 11

5) ROUND(N) Returns the rounded integer value of N. Ex: Ex:

10.45 10.62

==> 10 ==> 11

6) ROUND(N,dec) Returns the rounded floating point value of N, based on the given no. of decimals. 7) POWER(M,N) Returns M power N value. 8) SIN(N) Returns the SIN value of N. 9) COS(N) Returns COS value of N. 10) TAN(N) Returns TAN value of N. 11) GREATEST(N1,N2,...) Returns the maximum of given numbers.

12) LEAST(N1,N2,....) Returns the minimum of given numbers. 13) LN(N) Returns the natual log value of N. 14) LOG(M,N) Returns the logarithm value base M of N. =================================== B) String Functions ---------------------1) UPPER('string') Returns the upper case format of given string. 2) LOWER('string') Returns the lower case format of given string. 3) INITCAP('string') Returns the initial capitalization format of given string. 4) ASCII('chr') Returns the ASCII code of given character 5) CHR(n) Returns the respective character, based on the given ASCII value. 6) CONCAT(Val1,Val2) Returns the concatinated format of the given twostrings. 7) LENGTH('string') Returns the total no. of characters in the given string. 8) LPAD('string',length) Adds "spaces" at the left side of given string, to maintain the given string length. 9) RPAD('string',length) Adds "spaces" at the right side of given string, to maintain the given string length. 10) LTRIM('string')

Removes the extra "spaces" at left side of given string. 11) RTRIM('string') Removes the extra "spaces" at right side of given string. 12) TRIM('string') Removes the extra "spaces" at both sides of given string. 13) INSTR('string','chr') Searches the string for the given character. If the given character is found in the string, then it returns its corresponding character index. If it is not found then it returns 0. The character index starts from 1. 14) INSTR('string','chr',startindex) Same as above, but searching starts from given starting index. 15) INSTR('string','chr',startindex,occurance no) Same as above, but searching starts from given starting index and findouts the index of 'n'th occurance. 16) REPLACE('string','old chr','new chr') Replaces the each occurance of old character with new character. 17) REVERSE('string') Returns the reversed string. 18) SUBSTR('string',startindex) Returns the part of the string, starting from specific character upto end of the string. (including the specific character). 19) SUBSTR('string',startindex,length) Same as above, but returns the sub string with specific string length only. ==================================== C) Convertion Functions ----------------------------1) TO_CHAR(N) Converts the given number into string format. 2) TO_NUMBER('string') Converts the given string into numerical format.

3) TO_DATE('string','format') Converts the given into oracle supporting date, based on the given format. =================================== D) Date Functions ---------------------1) SYSDATE Returns the system date 2) TO_CHAR('Date','Format') Retrives the date in the specified format. DD MM YY DAY MON MONTH YEAR HH MI SS AM W DY 3) LAST_DAY('Date') Retrieves the ending of the month, based on the given date. 4) ADD_MONTHS('Date',N) Adds the specified no. of months to the given date. 5) MONTHS_BETWEEN('New Date','Old Date') Findouts the difference between given to dates. ================================== ii) Group Functions ============ --> The group functions are meant for performing logics on the entire of column of a table. --> The aggregate functions take the each value in the column individually and

gets called once per each value. But whereas the group functions treats all the column values as a group and gets called only once per entire column. --> The following are known as group functions available at Oracle. 1) 2) 3) 4) 5)

SUM() AVG() MAX() MIN() COUNT()

1) SUM(ColName) Returns the SUM of all the values in a specific column. Syn:

SELECT

SUM(ColumnName)

FROM

TableName;

2) AVG(ColName) Returns the AVERAGE of all the values in a specific column. Syn:

SELECT

AVG(ColumnName)

FROM

TableName;

3) MAX(ColName) Returns the MAXIMUM of all the values in a specific column. Syn:

SELECT

MAX(ColumnName)

FROM

TableName;

4) MIN(ColName) Returns the MINIMUM of all the values in a specific column. Syn:

SELECT

MIN(ColumnName)

FROM

TableName;

5) COUNT(*) Returns the total no. of rows in the table. Syn:

SELECT

COUNT(*)

FROM

TableName;

6) COUNT(ColName) Returns the total no. of values in the specified column. (Except NULL values) Syn:

SELECT

COUNT(ColName)

FROM

=====================================

TableName;

Clauses ----------> A clause is a part of the "query", which can produce some meaning. --> It can produce some meaning in the query, but not the complete meaning. --> For example, WHERE is a clause that can produce a "conditional" expression. But it can't executed individually. Available clauses in SQL ---------------------------1) WHERE 2) DISTINCT 3) ORDER BY 4) GROUP BY 5) HAVING 1) WHERE -------------> This is the most frequently used clause in SQL, which offers to provide a condition in the query. Syn:

SELECT

*

FROM

TableName

WHERE

Condition;

2) DISTINCT ----------------> It retrives only UNIQUE data from the specific column. Syn:

SELECT

DISTINCT

ColName

FROM

TableName;

3) ORDER BY ----------------> It offers sorting of table rows, based on the specified column. --> That means it sortouts the table data in ascending / descending order. --> Syn for Ascending Order: SELECT * FROM TableName ORDER BY ColName; --> Syn for Descending Order: SELECT * FROM TableName ORDER BY ColName DESC; --> Syn for Sorting based on multiple columns: SELECT * FROM TableName ORDER BY Col1,Col2...;

4) GROUP BY -----------------> It is similar to "DISTINCT" clause. It also retrieves UNIQUE data from a specific column. --> In addition to this, it applies a GROUP FUNCTION on another column in the same table. Syn: Ex:

SELECT Col1,GroupFun(Col2) FROM TableName GROUP BY Col1; SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB;

In the above example, the list of all designations and respective total amount of salaries will be displayed. 5) HAVING --------------> It is an extension to GROUP BY clause. --> It is used to produce a condition, based on the group function result used in the GROUP BY statement. Syn:

SELECT Col1,GroupFun(Col2) FROM TableName GROUP BY Col1

HAVING

Condition;

=================================== Copying the tables / CREATE Command with SELECT ------------------------------------------------------------a) Copy entire table -----------------------Syn: CREATE TABLE NewTableName

AS

SELECT

*

FROM

OldTableName;

b) Copy partial columns from the table --------------------------------------------Syn: CREATE TABLE NewTableName AS SELECT Col1,Col2,.... c) Copy partial rows from the table ---------------------------------------Syn: CREATE TABLE NewTableName AS SELECT Condition;

*

FROM

FROM OldTableName;

OldTableName

WHERE

e) Copy partial columns and partial rows from the table ---------------------------------------------------------------Syn: CREATE TABLE NewTableName AS SELECT Col1,Col2,.... FROM OldTableName WHERE Condition;

f) Copy only table structure without data -----------------------------------------------Syn: CREATE TABLE NewTableName AS SELECT * FROM GeneralFALSECondition;

OldTableName

WHERE

==================================== Sub Queries ---------------> A query acts as an individual command. --> But in a single query, another query can be integrated as a part. That integrated query is called as "Sub Query". --> Here, two kinds of queries are there in the "Sub Queries". 1) Outer Query 2) Inner Query Syn:

SELECT * FROM

TableName

WHERE

ColName = (SELECT ColName FROM TableName);

In the above syntax, the inner query (mentioned in brackets) will be executed at first. Finally outer query gets executed. Ex: --> SELECT * FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP); --> SELECT NAME FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP); --> SELECT * FROM EMP WHERE SAL < (SELECT AVG(SAL) FROM EMP); --> SELECT MAX(SAL) FROM EMP WHERE SAL < (SELECT MAX(SAL) FROM EMP); --> SELECT MIN(SAL) FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP); --> SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP));

==================================== Set Operators -----------------> The set operators are meant for retrieving the data from more than one table at-a-time. --> In other words, one query can retrieve the data from two or more tables. --> Ofcourse, the "Joins" are also available with similar purpose. But the "set operators" and "joins" functionality is different in this case.

--> Joins depends on the concept of "Referential integrity" but "Set operators" doesn't need "Referential integrity". --> Join operation displays the records from table1 and table2 simultaneously. But set operator displays the records from table1 and table2 one after another. Rule for "Set Operators" -----------------------------> The table structure of the two tables exactly should be same. Available Set Operators --------------------------1) UNION ALL 2) UNION 3) INTERSECT 4) MINUS ==================================== 1) UNION ALL ------------------> It is just like UNION concept in maths. --> It retrieves the data from table1 first and then table2. Syn:

SELECT * FROM Table1

UNION

ALL

SELECT * FROM Table2;

2) UNION ------------> It is almost all similar to UNION ALL command. --> It also retrieves the data from table1 first and then table2. Syn:

SELECT * FROM Table1

UNION

SELECT * FROM Table2;

Note: The main difference between UNION ALL and UNION is, "UNION" command avoids to print duplicate records if any. 3) INTERSECT -----------------> It is same as "Intersection" in maths. --> This command retrieves only common (duplicate) records among the two tables. Syn:

SELECT * FROM Table1

INTERSECT

SELECT * FROM Table2;

4) MINUS ------------> It is reverse to INTERSECT. --> It retrieves the records from table1 which doesn't exist in table2. Syn:

SELECT * FROM Table1

MINUS

SELECT * FROM Table2;

==================================== Views --------> A view is an imaginary table according to db terminology. --> It is one of the db objects.

===================================== TCL (Transaction Control Language) -------------------------------------------> The TCL commands are used to control the DML transactions. 1) ROLLBACK 2) COMMIT 3) SAVEPOINT