Changing the Oracle Surrounding

Clusters
Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk. For example, instead of the worker table being in one section of the disk and the workerskill table being somewhere else, their rows could be interleaved together in a single area, called a cluster. To cluster tables, you must own the tables you are going to cluster together. Syntax of CREATE CLUSTER command is:

CREATE CLUSTER cluster (Column datatype [, Column datatype] . . . ) [OTHER OPTIONS];

Example,

SQL> CREATE CLUSTER Worker_Skill (Judy VARCHAR2(25));
Cluster created.

Next, the first tables is created to be included in this cluster:

SQL> CREATE TABLE worker (
Name VARCHAR2(25) NOT NULL,
Age NUMBER,
Lodging VARCHAR2(15)
)
CLUSTER Worker_Skill (Name);

Name is the column in this table that will be stored in the cluster key Judy.

Note: Prior to inserting rows into worker, you must create a cluster index:

SQL> CREATE INDEX Worker_Skill_NDX ON CLUSTER Worker_Skill ;

Now a second table is added to the cluster:

SQL> CREATE TABLE workerskill (
Name VARCHAR2(25) NOT NULL,
Skill VARCHAR2(25) NOT NULL,
Ability VARCHAR2(15)
)
CLUSTER Worker_Skill (Name);

Creating, Dropping, and Altering Tables and Views

Creating Table

SQL> DESCRIBE trouble;
Name Null? Type
CITY NOT NULL VARCHAR2(13)
SAMPLEDATE NOT NULL DATE
NOON NUMBER(3,1)
MIDNIGHT NUMBER(3,1)
PRECIPITATION NUMBER

ð The columns in the table represent the three major data types in Oracle - VARCHAR2, DATE & NUMBER.

SQL> CREATE TABLE trouble(City VARCHAR2(13) NOT NULL, SampleDate DATE NOT NULL, Noon NUMBER(3,1), Midnight NUMBER(3,1), Precipitation NUMBER);

ð The individual column definitions are separated by commas. There is no comma after the last column definition. The table and column names must start with a letter of the alphabet, but may include letters, numbers and underscores. Names may be 1 to 30 characters in length, must be unique within the table and cannot be an Oracle reserved word. Case does not matter in creating a table. There are no options for DATE data types. Character data types must have their maximum length specified. Numbers may be either high-precision (up to 38 digits) or specified-precise, based on the maximum number of digits and the number of places allowed to the right of the decimal. The maximum width for CHAR (fixed-length) columns is 2,000 characters. VARCHAR2 (varying-length character) columns can have up to 4,000 characters.

Constraints in Creating Table
The CREATE TABLE statement lets you enforce several different kinds of constraints on a table: candidate keys, primary keys, foreign keys and check conditions. A constraint clause can constrain a single column or group of characters in a table. The point of these constraints is to get Oracle to do most of the work in maintaining the integrity of your database.

There are two ways to specify constraints: as part of the column definition (a column constraints) or at the end of the CREATE TABLE statement (a table constraint).

The Candidate Key
A candidate key is a combination of one or more columns, the values of which uniquely identify each row of a table.

SQL> CREATE TABLE trouble(City VARCHAR2(13) NOT NULL,
SampleDate DATE NOT NULL,
Noon NUMBER(3,1),
Midnight NUMBER(3,1),
Precipitation NUMBER,
CONSTRAINT Trouble_UQ UNIQUE (City, SampleDate));

ð Notice that both columns are also declared to be NOT NULL. If NOT NULL isn't specified, the column can have NULL valus.

The Primary Key
The primary key of a table is one of the candidate keys that you give some special characteristics. You can have only one primary key and a primary key column cannot contains NULLs.

Example 1:
SQL> CREATE TABLE trouble(City VARCHAR2(13) NOT NULL,
SampleDate DATE NOT NULL,
Noon NUMBER(3,1),
Midnight NUMBER(3,1),
Precipitation NUMBER,
CONSTRAINT Trouble_PK PRIMARY KEY (SampleDate));

Example 2:
SQL> CREATE TABLE worker(
Name VARCHAR2(25) PRIMARY KEY,
Age NUMBER,
Lodging VARCHAR2(15));

ð In the case, the Name column is the primary key and Oracle will generate a name for the PRIMARY KEY constraint.

The Foreign Key
A foreign key is a combination of columns with values based on the primary key values from another table. In the worker table the Lodging column refers to values for the lodging column in the lodging table:

SQL> CREATE TABLE worker(
Name VARCHAR2(25),
Age NUMBER,
Lodging VARCHAR2(15)
CONSTRAINT worker_PK PRIMARY KEY( Name),
FOREIGN KEY (Lodging) REFERENCES lodging( Lodging));

ð You can refer to a primary or unique key, even in the same table. However, you can't refer to a table in a remote database in the references clause. Sometimes you may want to delete these dependent row when you delete the row they depend on. In the case of worker and lodging, if you delete lodging, you'll want to make the Lodging column NULL. In another case, you might want to delete the whole row. The clause on delete cascade added to the references clause tells Oracle to delete the dependent row when you delete the corresponding row in the parent table. The action automatically maintains referential integrity.

The Check Constraint
Many columns must have values that are within a certain range or that satisfy certain conditions.

SQL> CREATE TABLE worker(
Name VARCHAR2(25),
Age NUMBER CHECK (Age BETWEEN 18 AND 65),
Lodging VARCHAR2(15)
CONSTRAINT worker_PK PRIMARY KEY( Name),
FOREIGN KEY (Lodging) REFERENCES lodging( Lodging));

Naming Constraints
You can name your constraints. For example, the primary key on the trouble table could be named trouble_PK.

SQL> CREATE TABLE trouble(City VARCHAR2(13) NOT NULL,
SampleDate DATE NOT NULL,
Noon NUMBER(3,1),
Midnight NUMBER(3,1),
Precipitation NUMBER,
CONSTRAINT Trouble_PK PRIMARY KEY (City, SampleDate));

ð If you do not specify a name for the constraint, then Oracle will generate a name. Most of Oracle's generated constraint names are of the form SYS_C######; for example, SYS_C000145.

Dropping Tables

SQL> DROP TABLE trouble;
Table dropped.

In Oracle, the truncate command lets you remove all the rows in the table and reclaim the space for other uses without removing the table definition from the database.

SQL> TRUNCATE TABLE trouble;
Table truncated.

ð Truncate can't be rolled back. If there are triggers that delete rows that depend on rows in the table, truncating does not execute those triggers. You should be sure you really want to truncate before doing it.

Altering Table
Tables can be altered in one of three ways: by adding a column to an existing table; by changing a column's definition; or by dropping a column. Suppose you decide to add two new columns to the trouble table: Condition, which you believe should be NOT NULL and Wind, for the wind speed. Example,

SQL> ALTER TABLE trouble ADD (Condition VARCHAR2(9) NOT NULL,
Wind NUMBER(3));
alter table trouble add (
*
ERROR at line 1: ORA-01758: table must be empty to add mandatory (NOT NULL) column.

ð You get an error message because you cannot add a column defied as NOT NULL in a existing table where data are stored in other columns. Each row in the table would have a new empty column defied as NOT NULL. The alternative is to first alter the table by adding the column without the NOT NULL restriction:

SQL> ALTER TABLE trouble ADD (Condition VARCHAR2(9),
Wind NUMBER(3));

ð Then fill the column with data for every row by using following command.

SQL> UPDATE trouble SET Condition='SUNNY';

ð Finally, alter the table again and modify the column definition to NOT NULL:

SQL> ALTER TABLE trouble MODIFY (Condition VARCHAR2(9) NOT NULL);

Dropping a Column
Dropping a column is more complicated than adding or modifying a column, because of the additional work that Oracle has to do. It's recovering the space that was actually taken up by the column values that is more complex and potentially very time-consuming for the database. For this reason, you can drop a column immediately or you can mark it as "unused" to be dropped at a later time. If the column is dropped immediately, the action may impact performance. If the column is marked as unused; there will be no impact on performance.

To drop a column, use either the set unused clause or the drop clause of the alter table command. You cannot drop a pseudo-column, a column of a nested table, or a partition key column.

SQL> ALTER TABLE trouble DROP COLUMN Wind;

Or

SQL> ALTER TABLE trouble SET UNUSED COLUMN Wind;

ð Marking a column as "unused" does not release the space previously used by the column, until you drop the unused columns:

SQL> ALTER TABLE trouble DROP UNUSED COLUMNS;

Note: You can query USER_UNUSED_COL_TABS. DBA_UNUSED_COL_TABS and ALL_UNUSED_COL_TABS to see all tables with columns marked as unused.

You can drop multiple columns in a single command, as shown in the following listing:

SQL> ALTER TABLE trouble DROP (Condition, Wind);

If the dropped columns are part of primary key or unique constraints, then you will need to also use the cascade constraints clause as part of your alter table command. If you drop a column that belongs to a primary key, Oracle will drop both the column and the primary key index.

Creating View
If a view is based on a single underlying table, you can inset, update or delete rows in the view.

╟ You cannot insert if the underlying table has any NOT NULL columns that don't appear in the view.
╟ You cannot insert or update if any one of the view's columns referenced in the insert or update contains functions or calculations.
╟ You cannot insert, update or delete if the view contains group by, distinct, or a reference to the pseudo-column RowNum.

Stability of a View
Remember that the results of querying a view are built instantly from a table (or tables) when you execute the query. Until that moment, the view has no data of its own, as a table does. It is merely a description (a SQL statement) of what information to pull out of other tables and how to organize it. As a consequence, if a table is dropped, the validity of a view is destroyed. Attempting to query a view where the underlying table has been dropped will produce will produce an error message about the view.

In the following sequence, a view is created on an existing table, the table is dropped and the view then is queried:

SQL> CREATE VIEW rain AS SELECT City, Precipitation FROM trouble;
View created.

SQL> DROP TABLE trouble;
Table dropped.

SQL> SELECT * FROM rain;
ERROR at line 1: ORA-00942: table or view does not exist.

Again, if you add a new column in trouble trouble table, then what will the view rain act.

SQL> ALTER TABLE trouble ADD (Warning VARCHAR2(20));
Table altered.

ð Despite the change to the view's base table, the view is still valid and will access the new columns in the trouble table.

ORDER BY in View
You cannot use an order by in a create view statement. Occasionally, a group by, which can be used, may accomplish the purpose for which an order by might be used.

SQL> CREATE VIEW discomfort AS SELECT City, Precipitation FROM comfort GROUP BY Precipitation, City;
View created.

Creating a Read-Only View
You can use the with read only clause of the create view command to prevent users from manipulating records via the view.

SQL> CREATE or REPLACE VIEW rain AS SELECT City, Precipitation FROM trouble WITH READ ONLY;

Creating a Table from a Table

SQL> CREATE TABLE rain AS SELECT City, Precipitation FROM trouble;
Table created.

ð When the new table is described, it reveals that is has "inherited" its column definitions from the trouble table.

You also can use this technique to create a table with column definitions like that of the source table, but with no rows in it, by building a where clause that will select no rows from the old table:

SQL> CREATE TABLE rain AS SELECT City, Precipitation FROM trouble WHERE 1=2;

Using Partitioned Tables
You can divide the rows of a single table into multiple parts. Dividing a table's date in this manner is called partitioning table; the table that is partitioned is called a partitioned table and the parts are called partitions. Partitioning is useful for very large tables. By splitting a large table's rows across multiple smaller partitions; you accomplish several important goals:
╟ The performance of queries against the tables may improve.
╟ Since the partitioned table's data is stored in multiple parts, it may be easier to load and delete data in the partitions than in the large table.
╟ Backup and recovery operations may perform better.

Creating a Partitioned Table By Range
Typically, table are partitioned by ranges of values. To partition the table's records, use the partition by range clause of the create table command, as shown next. The ranges will determine the values stored in each partition.

SQL> CREATE TABLE worker (
Name VARCHAR2(25),
Age NUMBER,
Lodging VARCHAR2(15),
CONSTRAINT worker_PK PRIMARY KEY (Name)
)
PARTITION BY RANGE (Lodging)(
PARTITION Part1 VALUES LESS THAN ('F')
TABLESPACE Part1_TS,
PARTITION Part2 VALUES LESS THAN ('N')
TABLESPACE Part2_TS,
PARTITION Part3 VALUES LESS THAN ('T')
TABLESPACE Part3_TS,
PARTITION Part4 VALUES LESS THAN (MAXVALUE)
TABLESPACE Part4_TS
)
;

ð For any lodging values less than F, the record will be stored in the partition name Part1. The part1 partition will be stored in the Part1_TS tablespace. Any Lodging in the range between F and N will be stored in the Part2 partition; values between N and T will be stored in the PART3 partition. Here, you don't need to specify a maximum value for the last partition; the maxvalue keyword tells Oracle to use the partition to store any data that could not be stored in the earlier partitions. Notice that for each partition, you only specify the maximum value for the range. The minimum value for the range is implicitly determined by Oracle.

Creating a Partitioned Table By Hash
A has partition determines the physical placement of data by performing a hash function on the values of the partition key. In range partitioning, consecutive values of the partition key are usually stored in the same partition. In hash partitioning, consecutive values of the partition key are not necessarily stored in the same partition. To create a hash partition, use the partition by hash clause in place of the partition by range clause:

SQL> CREATE TABLE worker (
Name VARCHAR2(25),
Age NUMBER,
Lodging VARCHAR2(15),
CONSTRAINT worker_PK PRIMARY KEY (Name)
)
PARTITION BY HASH (Lodging)
PARTITIONS 10;

You can name each partition and specify its tablespace, just as you would for range partition,

SQL> CREATE TABLE worker (
Name VARCHAR2(25),
Age NUMBER,
Lodging VARCHAR2(15),
CONSTRAINT worker_PK PRIMARY KEY (Name)
)
PARTITION BY HASH (Lodging)
PARTITIONS 2
STORE IN (Part1_TS, Part2_TS);

This method will create partitions with system-generated names of the format SYS_Pnnn. The number of tablespaces specified in the store in clause does not have to equal the number of partitions. You can specify named patitions:

.....................
.....................
PARTITION BY HASH (Lodging) (
PARTITION P1 TABLESPACE P1_TS,
PARTITION P2 TABLESPACE P2_TS
);

ð In this method, each partition is given a name and a tablespace, with the option of using an additional lob or varray storage clause.

Creating Subpartitions
You can create subpartitions - partitions of partitions. You can use subpartitions to combine two types of partitions: range partitions and has partitions.

SQL> CREATE TABLE worker (
Name VARCHAR2(25) PRIMARY KEY,
Age NUMBER,
Lodging VARCHAR2(15),
CONSTRAINT worker_PK PRIMARY KEY (Name)
)
PARTITION BY RANGE (Lodging)
SUBPARTITION BY HASH (Name)
SUBPARTITIONS 10
(
PARTITION Part1 VALUES LESS THAN ('F')
TABLESPACE Part1_TS,
PARTITION Part2 VALUES LESS THAN ('N')
TABLESPACE Part2_TS,
PARTITION Part3 VALUES LESS THAN ('T')
TABLESPACE Part3_TS,
PARTITION Part4 VALUES LESS THAN (MAXVALUE)
TABLESPACE Part4_TS
)
;

ð The worker table will be range-partitioned table, you should create into four partitions, using the range specified for the four named partitions. Each of those partitions will be hash-partitioned on the Name column.

Indexing Partitions
When you create a partitioned table, you should create an index on the table. The index may be partitioned according to the same range values that were used to partition the table. In the following listing, the create index command for the worker table is shown:

SQL> CREATE INDEX worker_lodging ON worker (Lodging)
LOCAL
(PARTITION Part1
TABLESPACE Part1_NDX_TS,
PARTITION Part2
TABLESPACE Part2_NDX_TS,
PARTITION Part3
TABLESPACE Part3_NDX_TS,
PARTITION Part4
TABLESPACE Part4_NDX_TS);

ð Notice the local keyword. In this create index command, no ranges are specified. Instead, the local keyword tells Oracle to create a separate index for each partition of the worker table.

You can also create "global" indexes. A global index may contain values from multiple partitions. The index itself may be partitioned,

SQL> CREATE INDEX worker_lodging ON worker (Lodging)
GLOBAL PARTITION BY RANGE (Lodging)
(PARTITION Part1 VALUES LESS THAN ('F')
TABLESPACE Part1_NDX_TS,
PARTITION Part2 VALUES LESS THAN ('N')
TABLESPACE Part2_NDX_TS,
PARTITION Part3 VALUES LESS THAN ('T')
TABLESPACE Part3_NDX_TS,
PARTITION Part4 VALUES LESS THAN (MAXVALUE)
TABLESPACE Part4_NDX_TS)
;

ð The global clause in this create index command allows you to specify ranges for the index values that are different from the range the ranges for the table partitions. Local indexes may be easier to manage than global indexes; however, global indexes may perform uniqueness checks faster than local (partitioned) indexes perform them.

Managing Partitioned Tables
you can use the alter table command to add, drop, exchange, move, modify, rename, split and truncate partitions. These alter table command options allow you to alter the existing partition structure, as may be required after a partitioned table has been used heavily.

Querying Directly from Partitions
If you know the partition from which you will be retrieving your data, you can specify the name of the partition as part of the from clause of you query. For example, suppose you want to query the records for the workers whose Lodgings begin with the letter R.

SQL> SELECT * FROM worker PARTITION (Part3) WHERE NAME LIKE 'R%';

Using Single and Multi-Row Function

Conversion function
Oracle includes three conversion functions: TO_NUMBER, TO_CHAR and TO_DATE.

TO_NUMBER function
The TO_NUMBER function is used to convert character data or string, into numeric data.

Syntax: TO_NUMBER(String , [ format ])

Ex. A table called sales_records contains two columns with a variable character data type (varchar2(10)) called Asking_price and Sale_price. If the data in these columns look like this,

Asking_price Sale_price
2000 1000
3000 1500

Oracle can execute the following query,

SQL > SELECT Asking_price-Sale_Price AS “Price Difference” FROM Sale_Records;

In this example, Oracle is able to implicitly convert the varchar2 value into numeric value and perform the arithmetic expression. However, if the data in these columns look like this:

Asking_price Sale_price
2,000.00 1,000.00
3,000.00 1,500.00

The previous query return a error:

SQL > SELECT Asking_price-Sale_Price AS “Price Difference” FROM Sale_Records;

ERROR at line 1:
ORA-01722: Invalid number.

To avoid this error you can use the TO_NUMBER function.

SQL > SELECT TO_NUMBER(Asking_price,’99,999.99’)-TO_NUMBER(Sale_Price,’99,999.99’) AS “Price Difference” FROM Sale_Records;

Note: The last example in this section does not work properly when the column has a fixed-length character data type. This can be dealt with by using the TRIM function inside the TO_NUMBER function.

Ex.
SQL > SELECT TO_NUMBER(TRIM(Asking_price),’99,999.99’)-TO_NUMBER(TRIM(Sale_Price),’99,999.99’) AS “Price Difference” FROM Sale_Records;

TO_CHAR function
The TO_CHAR function is used to convert either date or numeric data to character data.

Syntax: TO_CHAR ( { Numeric data | date data }, [ ‘ Format ‘ ] )

TO_CHAR with NUMERIC data
EX. If your database, NLS language is set to American, the following query returns a value with American currency symbol.

SQL > SELECT InstructorID, TO_CHAR(PerDiemCost*5,’L99,999.99’) AS “Weekly Cost” FROM Instructors;
INSTRUCTORID Weekly Cost
300 $2,500.00
310 $2,250.00
100 $3,000.00
110 $2,500.00
200 $3,750.00

Note: When you use “L”, it display the currency symbol specified in the NLS_Currency parameter. In this case the NLS_Currency value is the Dollar sign. If you change the NLS_Currency value to Italy, you receive a different output from the same query.

Ex.
SQL > SELECT InstructorID, TO_CHAR(PerDiemCost*5,’L99,999.99’) AS “Weekly Cost” FROM Instructors;
INSTRUCTORID Weekly Cost
300 L.2,500.00
310 L.2,250.00
100 L.3,000.00
110 L.2,500.00
200 L.3,750.00

ð In this case, the NLS_Currency value has changed from the dollar sign to the Lira symbol (L.).

To know what your currency NLS_Currency value is, give the query as,
SQL > SELECT Value FROM V$NLS_Parameters WHERE Parameters=’NLS_Currency’;

Symbol	Significance
0	Display a leading zero.
9	Represent any number.
,	Thousand Separators.
.	Floating currency symbol.
L	Floating currency symbol.
$	Floating dollar sign.
MI	Places minus sign to right of value if the value is nagative.

Ex.
SQL > SELECT FirstName, LastName, TO_CHAR(PerDiemCost*5,’L99,999.00’) AS “Weekly Cost” FROM Instructors;
FIRSTNAME LASTNAME Weekly Cost
Michael Harrison $2,500.00
Susan Keele $2,250.00

Ex. If you do not include enough number symbols, Oracle is unable to returns any output.
SQL > SELECT InstructorID, TO_CHAR(PerDiemCost*5,’$999.99’) AS “Weekly Cost” FROM Instructors;
INSTRUCTORID Weekly Cost
300 ########
310 ########
100 ########

Top |^|

TO_CHAR DATE format elements

Symbol	Significance
DD	Two digits day value.
MM	Two digits month value.
Mon	Abbreviated month value.
Month	The full month spelled output.
YYYY	Four digits year value.
year	The full year spelled out.
Day	The day of the week.
Dy	Three letter abbreviation for the day of the week.
Q	The quarter.

Ex. The following query display the start date for all classes, including the day of the week and the month spelled out:
SQL > SELECT ClassID, TO_CHAR(StartDate,’DAY MONTH DD, YYYY’) AS “Start Date” FROM ScheduledClasses;
CLASSID Start Date
50 SATURDAY JANUARY 06,2004
51 SATURDAY JANUARY 13,2004
53 WEDNESDAY FEBRUARY 14,2004

Ex.
SQL > SELECT ClassID, TO_CHAR(StartDate,’Day Month DD, YYYY’) AS “Start Date” FROM ScheduledClasses;
CLASSID Start Date
50 Saturday January 06,2004
51 Saturday January 13,2004
53 Wednesday February 14,2004

Ex.
SQL > SELECT ClassID, TO_CHAR(StartDate,’fmDay Month fmDD, YYYY’) AS “Start Date” FROM ScheduledClasses;
CLASSID Start Date
50 Saturday January 06,2004
51 Saturday January 13,2004
53 Wednesday February 14,2004

Ex.
SQL > SELECT ClassID, TO_CHAR(StartDate,’fmDay“ The ”DDth” of ”Month DD, YYYYsp’) AS “Start Date” FROM ScheduledClasses;
CLASSID Start Date
50 SATURDAY The 6th of January 06,TWO THOUSAND FOUR
51 SATURDAY The 13th of January 13, TWO THOUSAND FOUR
53 WEDNESDAY The 14th of February 14, TWO THOUSAND FOUR

TO_DATE function
The TO_DATE function is used to help Oracle convert string values into its internal date format.

Syntax: TO_DATE (String, ‘ Format ’)
When Oracle encounters a character string that if needs to treat as a date, it uses the NLS_date_format value to attempt to interpret the string. If the date string does not conform to this format, Oracle returns as error.
In order to make this query work, you have to use the TO_DATE function to show Oracle how to interpret the string.

SQL > SELECT ClassID FROM ScheduledClasses WHERE StartDate=TO_DATE(‘January 6,2004’,’Month DD,YYYY’);
CLASSID
50

CHARACTER function
Character functions are used to manipulate case and appearance of character data.

Case-Conversion function

Function	Description
LOWER	Return all alphabetic character in lower cases.
UPPER	Return all alphabetic character in upper cases.
INITCAP	Capitalizes the first letter of each word and sets the remaining character in lowercase.

Ex.
SQL > SELECT UPPER(‘Hello there’) AS UPPER, LOWER(‘HOW ARE’) AS LOWER, INITCAP(‘you today’) AS INITCAP FROM Dual;
UPPER LOWER INITCAP
HELLO THERE how are You Today

Ex.
SQL > SELECT InstructorId, City FROM Instructors WHERE City=’TORONTO’;

no rows selected.

ð There are instructors in this table from Toronto; however, in the table, the string is stored in initial capital letters. To solve the problem we can use the following query,

SQL > SELECT InstructorId, City FROM Instructors WHERE UPPER(City)=’TORONTO’;
INSTRUCTORID CITY
300 Toronto
310 Toronto
210 Toronto

Character-Manipulation function

Function	Description
SUBSTR	Return a portion of a string.
CONCAT	Combines two string values.
LPAD	Pads a string with a defined character to a defined length.
TRIM	Trims leading or ending character.
LENGTH	Return the length of a string value.
INSTR	Return the position of a particular character within a string.

SUBSTR

Syntax: SUBSTR (String, , )
Both the start position and number of character are numeric values indicating the position of the characters with in the string that you want returned.

Ex.
SQL > SELECT SUBSTR(FirstName,1,1), LastName Form Instructors;
S LASTNAME
M Harrison
S Keele
D Ungar

Ex.
SQL > SELECT SUBSTR(CourseName,5,10) AS SUBSTRING Form Courses;
SUBSTRING
C SQL
nced SQL
ormance Tu
base perto

CONCAT

Syntax: CONCAT(String1, String2)

Ex.
SQL > SELECT CONCAT(‘Oracle’,’Server’) AS CONCAT FROM Dual;
CONCAT
OracleServer

LPAD

Syntax: LPAD(char, size of string,’ padding character’)

Ex.
SQL> SELECT LPAD(LastName, 10 ,’*’) AS LPAD FROM Students;
LPAD
*****Jones
*Patterson
*******Hee

TRIM
The TRIM function removes leading and trailing characters from a string.

Syntax: TRIM([{LEADING | TRAILING |BOTH }] char FROM string)

Ex. The following query removes the character “s” from the string.
SQL> SELECT TRIM(‘S’ FROM ‘STEVENS’) AS TRIM FROM Dual;
TRIM
TEVEN

ð if you want only one trimmed, you must specify which string you want trimmed.

Ex.
SQL> SELECT TRIM(TRAILING ‘S’ FROM ‘STEVENS’) AS TRIM FROM Dual;
TRIM
STEVEN

Ex.
SQL> SELECT TRIM(BOTH ‘S’ FROM ‘Stevens’) AS TRIM FROM Dual;
TRIM
Tevens

ð The character you choose to trim must match the case of the characters in the string.

Ex.
SQL> SELECT TRIM(‘ST’ FROM ‘STEVENS’) AS TRIM FROM Dual;
ERROR at line 1:
ORA-30001: trim set should have only one character.

ð you can’t include multiple characters in the list of characters to be trimmed.

LENGTH

Ex.
SQL> SELECT FirstName, LENGTH(FirstNAme) AS LENGTH FROM Instructors;
FIRSTNAME LENGTH
Michael 7
Susan 5
David 5

INSTR

Syntax: INSTR(String, char)

Ex.
SQL> SELECT FirstName, INSTR(FirstNAme, ‘a’) AS INSTR FROM Instructors;
FIRSTNAME INSTR
Michael 5
Susan 4
David 2
Geoff 0
Lana 2

Number function
Number functions enable you to manipulate numeric values.

ROUND
The ROUND function is used to round numeric values to a decimal place specified as a parameter of the function.

Syntax: ROUND(number, n)

Ex.
SQL> SELECT ROUND(44.647 , 2) AS POSITIVE, ROUND(44.647 , 0) AS ZERO, ROUND(44.647 , -1) AS NEGATIVE FROM Dual;
POSITIVE ZERO NEGATIVE
44.65 45 4

ð The value for negative has been rounded to the nearest ten.

TRUNC
The TRUNC function works much like the ROUND function.

Syntax: TRUNC(number, n)

Ex.
SQL> SELECT TRUNC(44.647 , 2) AS POSITIVE, TRUNC (44.647 , 0) AS ZERO, TRUNC (44.647 , -1) AS NEGATIVE FROM Dual;
POSITIVE ZERO NEGATIVE
44.64 44 40

ð It simply truncates the value at the specified decimal place.

MOD
The MOD function returns the remainder of two values.

Syntax: MOD(value1, value2)
When this function is called, it divides value1 by value2 and returns only the remainder.

Ex.
SQL> SELECT MOD(2200, 300) AS ODD, MOD(2100,300) AS EVEN FROM Dual;
ODD EVEN
100 0

Ex.
SQL> SELECT MOD(1000, 0) AS ZERO_DIVIDE FROM Dual;
ZERO_DIVIDE
1000

ð The above query should return a mathematical error. However, in Oracle this function returns the Remainder value.

Date function
Oracle includes a number of functions that enable you to modified and manipulate date data. Most of these functions enable you to perform calculations on dates.

Function	Description
ADD_MONTHS	Add or subtract a number of months to date.
MONTHS_BETWEEN	Determines the number of months between two dates.
NEXT_DAY	Determines the next occurrence of a particular day of the week.
LAST_DAY	Determines the last day of the month.
ROUND	Round to the nearest date part.
TRUNC	Truncates to the nearest date part.

ADD_MONTHS

Syntax: ADD_MONTHS(date, number)

Ex.
SQL> SELECT StartDate, ADD_MONTHS( StartDate, 6) AS 6_MONTHS FROM ScheduledClasses;
STARTDATE 6_MONTHS
06-JAN-04 06-JUL-04
13-JAN-04 13-JUL-04
14-FEB-04 14-AUG-04

MONTHS_BETWEEN

Syntax: MONTHS_BETWEEN( date1, date2)

Ex.
SQL> SELECT MONTHS_BETWEEN(‘01-JAN-01’,’01-AUG-01’) AS MONTHS_BETWEEN FROM Duel;
MONTHS_BETWEEN
-7

ð The result of this function returns a negative value because in this example, you are subtracting a higher value from a lower value.

NEXT_DAY

Syntax: NEXT_DAY(date, day)

Ex. To find the date of the Friday after each course start date, you use the following query.
SQL> SELECT StartDate, NEXT_DAY(StartDate,’FRIDAY’) AS NEXT_DAY FROM ScheduledClasses;
STARTDATE NEXT_DAY
06-JAN-04 02-JUL-04
13-JAN-04 19-JUL-04
14-FEB-04 16-AUG-04

LAST_DAY

Syntax: LAST_DAY(date)

Ex. Suppose you pay your instructions on the last day of each month. You can calculate the payment date with the following query.
SQL> SELECT classID, StartDate, LAST_DAY(StartDate) AS payment FROM ScheduleClasses;
CLASSID STARTDATE PAYMENT
50 02-JUL-04 31-JAN-01
51 19-JUL-04 31-JAN-01
53 16-AUG-04 28-FEB-01

ð Oracle determines the month in which each date value exists and then determines the last day for the month.

ROUND and TRUNC
In the case, you specify the part of the date you want to round or truncate calues on rather than a decimal position. You can round a date to a particular month or year.

Ex.
SQL> SELECT ROUND(StartDate,'MONTH') AS MONTH, ROUND(StartDate,'YEAR') AS YEAR FROM ScheduleCalsses;
MONTH YEAR
01-JAN-01 01-JAN-01
01-JAN-01 01-JAN-01
01-FEB-01 01-JAN-01

Top |^|

Additional functions

Use the NUL function
The NVL function is used to provide an actual value in place of a NULL returned by a query.

Syntax: NVL(colume, value)

Ex.
SQL> SELECT InstructorID, (PerDiewCost + PerDiewExpenses)*5 AS "Weekly Cost" FROM Instructors;
INSTRUCTORID Weekly Cost
300 3500
310 3200
210
410
450 3250

ð Any arithmetic expression containing a NULL in one of its values always returns NULL. To solve this problem we can use NUL function.

SQL> SELECT InstructorID, (PerDiewCost + NUL(PerDiewExpenses))*5 AS "Weekly Cost" FROM Instructors;
INSTRUCTORID Weekly Cost
300 3500
310 3200
210 2000
410 2000
450 3250

Note: The value you include in the NUL function must match the data type of the column being evaluated.

SQL> SELECT InstructorID, NUL(PerDiewExpenses,'No expression') FROM Instructors;
ERROR at line 1:
ORA-01722: Invalid number

Using the SYSDATE function
The SYSDATE function is used to return the current date from the system.

Ex.
SQL> SELECT CourseNumber, Startdate FROM ScheduleClasses WHERE Startdate

ð The query always returns courses that started previous to the current day.

Using the DECODE function
The DECODE function enables you to build some condition logic into your queries.

Syntax: DECODE(column/expression, condition1, result1[, condition2, result2][,default])

Ex.
SQL> SELECT InstructorID, PerDiemCost, DECODE(ROUND(PerDiewCost/100,0), 4, 1, 5, 2, 6, 3, 7, 4, 5) AS Rank From Instructors ORDER BY Rank DESC;
INSTRUCTORID PERDIEMCOST RANK
200 750 5
100 600 3
300 500 2
410 400 1

Nesting function
When functions are nested, Oracle evaluate the innermost function first and panes its value to the outer function, only the outer most function actually returns a value back to the result function.

Ex.
SQL> SELECT CONCAT( SUBSTR (LastName, 1,3), SUBSTR( FirstName, 1, 2)) AS CONCAT_ID FROM Instructors;
CONCAT_ID
Har M.
Kee Su
Ung Da

Group/Aggregate Functions
Group (Some times referred to as multi-row) functions differ greatly from the single row functions. They are used to derive aggregate values. All of these functions return one and only one value, regardless of how many values are passed into the function.

Function	Description
AVG	Returns the average of all values inputted
SUM	Returns the total of all values inputted
COUNT	Counts the number of rows inputted
MIN	Returns the lowest of all values inputted
MAX	Returns the highest of all values inputted

A SELECT list can contain one or more functions.

AVG and SUM
The AVG and SUM functions accept only numeric data type values. The AVG function returns the average of all NOT NULL values passed into the function and the SUM function returns the total for all NOT NULL value.

Ex.
SQL> SELECT AVG(PerDiewCost) AS AVERAGE, SUM(PerDiewCost) AS TOTAL FORM Instructors;
AVERAGE TOTAL
506.25 4050

COUNT(Column) and COUNT(*)
The difference between COUNT(Column) and COUNT(*) is that COUNT(*) includes rows where there are NULL values, whereas COUNT(Column) only counts those rows that are not NULL in the specified.

Ex.
SQL> SELECT COUNT(*) AS ALL_ROWS, COUNT(PerDiemExpenses) AS NOT_NULL FROM Instructor;
ALL_ROWS NOT_NULL
8 6

MIN and MAX

Ex.
SQL> SELECT Max(PerDiewExpenses) AS HIGHEST, MIN(PerDiewExpenses) AS LOWEST FROM Instructors;
HIGHEST LOWEST
250 200

Ex.
SQL> SELECT Max(EnrollmentDate) AS LATEST, MIN(EnrollmentDate) AS EARLIEST FROM Instructors;
LATEST EARLIEST
05-JAN-01 02-DES-00

Note: When you use MIN and MAX with character data, Oracle retrieves the lowest or highest alphabetically i.e. clorest to "a" and elorest to "z".

Ex.
SQL> SELECT Max(LastName) AS "Clorest To A", MIN(LastName) AS "Clorest to Z" FROM Instructors;
Clorest To A Clorest to Z
chiu williams

GROUP function and NULL

Ex. Consider a table which bearing data as below.
Employee Hourly_wage
Bob 15
Sue 10
Jane
Rich 5

Now, if we query the table then,

SQL> SELECT AVG(NVL(Hourly_wage,0)) AS AVG_SAL FROM Wages;
AVG_SAL
7.5

Using the DISTINCT keyword with NULL
If you want to consider only unique rows, it is possible to use the DISTINCT operator inside a group function.

Ex.
SQL> SELECT AVG(DISTINCT PerDiewExpenses) AS WITH_DISTINCT, AVG(PerDiewExpenses) AS NO_DISTINCT FROM Instructors;
WITH_DISTINCT NO_DISTINCT
255 208.33333

Ex.
SQL> SELECT DISTINCT AVG(PerDiewExpenses) AS WITH_DISTINCT FROM Instructors;

Using the WHERE clause with group functions

Ex.
SQL> SELECT AVG(PerDiewCost) AS "AVG COST" FROM Instructors WHERE City='Toronto';

Using the GROUP BY clause

Ex.
SQL> SELECT City, AVG(PerDiewCost) AS "Average Cost" FROM Instructors GROUP BY City';
CITY Average Cost
New York 487.5
Palo Alto 750
Toronto 450

Ex.
SQL> SELECT InstructorID, City, MAX(PerDiewCost) AS "Highest Cost" FROM Instructors GROUP BY InstructorID, City;
INSTRUCTORID CITY Highest Cost
100 New York 600
110 New York 500
200 Palo Alto 750
210 Toronto 400
300 Toronto 500

Ex.
SQL> SELECT City, AVG(PerDiewCost) AS "Average Cost" FROM Instructors WHERE Country='USA' GROUP BY City;
CITY Average Cost
New York 487.5
Palo Alto 750

Using the HAVING Clause
The HAVING Clause is essentially a second WHERE clause that is evaluated after Oracle has calculated the grouping value.

Ex.
SQL> SELECT City, AVG(PerDiewCost) AS "Average Cost" FROM Instructors WHERE AVG(PerDiewCost)<500>

=> Now, we will get a Oracle error. So, we should write the query with using HAVING clause. i.e.

SQL> SELECT City, AVG(PerDiewCost) AS "Average Cost" FROM Instructors GROUP BY City HAVING AVG(PerDiewCost)<500;
CITY Average Cost
New York 487.5
Toronto 450

Ex.
SQL> SELECT City, AVG(PerDiewCost) AS "Average Cost" FROM Instructors WHERE Country='USA' GROUP BY City HAVING AVG(PerDiewCost)<500;
CITY Average Cost
New York 487.5