Oracle and SQL commands

*************      COMMANDS     ************

String Functions

1.    LEN (string) - Returns the number of characters of the specified string expression, excluding trailing blanks.

Example:

use Vendor

GO

Select LEN('Raj'), LEN('Raj    ') FROM VENDOR WHERE VendorFName='Raj'

GO

LEN doesn't count length of spaces. So result looks like this.

2.    LTRIM (string) - LTRIM function to return a character expression after removing leading spaces.

Example:

use Vendor

GO

use Vendor

SELECT LTRIM('   Raj')

FROM VENDOR WHERE VendorFName='Raj'

Go

3.    RTRIM (string) - RTRIM function to return a character expression after removing trailing spaces.

Example:

use Vendor

GO

use Vendor

Select RTRIM('Raj     ')

FROM VENDOR WHERE VendorFName='Raj'

GO

4.    LEFT (string, length) - Returns the specified number of characters from the beginning of the string.

Example:

use Vendor

SELECT VendorFName, VendorLName, LEFT(VendorFName, 1) + LEFT (VendorLName, 1) AS InitialsFROM Vendor

5.    RIGTH (string, length) - Returns the specified number of characters from the end of the string.

Example:

use Vendor

SELECT VendorFName, VendorLName, RIGHT(VendorFName, 1) + RIGHT (VendorLName, 1) ASInitials FROM Vendor 

6.    SUBSTRING (string, start, length) - Returns the specified number of characters from the string starting at the specified position.

Example:

use Vendor

GO

SELECT SUBSTRING('beniwal', 2, 2) FROM VENDOR WHERE VendorFName='Raj'

GO

7.    REPLACE (search, find, replace) - Returns the search string with all occurrences of the find string replaced with the replace string.

Example:

use Vendor

GO

use Vendor

SELECT REPLACE('Beniwal', 'Beniwal', 'Choudhary')

FROM VENDOR WHERE VendorFName='Raj'

GO

8.    REVERSE (string) - Returns the string with the character in reverse order.

Example:

use Vendor

GO

use Vendor

SELECT REVERSE('Raj')

FROM VENDOR WHERE VendorFName='Raj'

GO

9.    CHARINDEX (find, search [, start]) - Returns an integer that represents the position of the first occurrence of the find string in the search string starting at the specified position. If the starting position isn't specified, the search starts at the beginning of the string. If the staring isn't found, the functions returns zero.

Example:

use Vendor

GO

use Vendor

SELECT CHARINDEX('w', 'Beniwal')

FROM VENDOR WHERE VendorFName='Raj'

GO

10. PATINDEX (find, search [, start]) - PATINDEX is useful with text data types; it can be used in a WHERE clause in addition to IS NULL, IS NOT NULL, and LIKE (the only other comparisons that are valid on text in a WHERE clause). If either pattern or expression is NULL, PATINDEX returns NULL when the database compatibility level is 70. If the database compatibility level is 65 or earlier, PATINDEX returns NULL only when both pattern and expression are NULL.

Example:

use Vendor

GO

use Vendor

SELECT PATINDEX('%Krew%', VendorLName)

FROM VENDOR WHERE VendorId=5

GO

11. LOWER (string) - Returns the string converted to lowercase letters.

Example:

use Vendor

GO

use Vendor

SELECT LOWER('Raj')

FROM VENDOR WHERE VendorFName='Raj'

GO

12. UPPER (string) - Returns the string converted to uppercase letters.

Example:

use Vendor

GO

use Vendor

SELECT UPPER('Raj')

FROM VENDOR WHERE VendorFName='Raj'

GO

13. SPACE (integer) - Returns the string with the specified number of space characters (blanks).

Example:

use Vendor

GO

use Vendor

SELECT VendorFName + ',' + SPACE(2) + VendorLName

FROM VENDOR WHERE VendorFName='Raj'

GO

      

Numeric Functions

1      ROUND (number, length, [function]) - Returns the number rounded to the precision specified by length. If length is positive, the digits to the right of the decimal point are rounded. If it's negative the digits to the left of the decimal point are rounded. To truncate the number rather than round it code a non zero value for function.

Example:

USE Vendor

GO

--Used Round the estimates

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3)

--Use ROUND and rounding approximations

SELECT ROUND(123.4545, 2), ROUND(123.45, -2)

--Use ROUND to truncate

SELECT ROUND(150.75, 0), ROUND(150.75, 0, 1)

GO

2      ISNUMERIC(expressions) - Returns a value of 1 (true) if the expression is a numeric value; returns a values of 0 (false) otherwise.

Example:

USE Vendor

GO

SELECT IsNumeric(VendorId) FROM Vendor

SELECT ISNumeric(VendorFName) FROM Vendor

GO

3      ABS (number) - Returns the absolute value of number.

Example:

USE Vendor

GO

SELECT ABS(-1.0), ABS(0.0), ABS(1.0)

GO

4      CEILING (number) - Returns the smallest integer that is greater than or equal to the number.

Example:

USE Vendor

GO

SELECT CEILING($123.45),CEILING($-123.45), CEILING($0.0)

GO

5      FLOOR (number) - Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Example:

USE Vendor

GO

SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)

GO

6      SQUARE (float_number) - Returns the square of the given expression.

Example:

USE Vendor

GO

DECLARE @h float, @r float

SET @h = 5

SET @r = 1

SELECT PI()* SQUARE(@r)* @h AS 'Cyl Vol'

GO

7      SQRT (float_number) - Returns a square root of a floating-point number.

Example:

USE Vendor

GO

DECLARE @myvalue float

SET @myvalue = 1.00

WHILE @myvalue < 10.00

   BEGIN

      SELECT SQRT(@myvalue)

      SELECT @myvalue = @myvalue + 1

   END

GO

8      RAND ([seed]) - Returns a random float value from 0 through 1.

Seed

Is an integer expression (tinyint, smallint, or int) that specifies the seed value. If seed is not specified, Microsoft SQL Server 2000 assigns a seed value at random. For a given seed value, the result returned is always the same.

Example:

USE Vendor

GO

DECLARE @counter smallint

SET @counter = 1

WHILE @counter < 5

   BEGIN

      SELECT RAND() Random_Number

      SET NOCOUNT ON

      SET @counter = @counter + 1

      SET NOCOUNT OFF

   END

GO

      

Date/Time Functions:

1     GetDate () - Returns the current system date and time in the Microsoft SQL Server standard internal format for date time values.

Example:

USE Vendor

GO

SELECT GetDate()

GO

2      GETUTCDATE() - Returns the current UTC date and time based on the system's clock and time zone setting. UTC (Universal Time Coordination) is the same as Greenwich Mean Time.

Example:

USE Vendor

GO

SELECT GETUTCDATE()

GO

3      DAY (date) - Returns the day of the month as an integer.

Example:

USE Vendor

GO

SELECT DAY('03/12/1998') AS 'Day Number'

GO

4      MONTH (date) - Returns the month as an integer.

Example:

USE Vendor

GO

SELECT "Month Number" = MONTH('03/12/1998')

SELECT MONTH(0), DAY(0), YEAR(0)

GO

5      YEAR (date) - Returns the 4-digit year as an integer.

Example:

USE Vendor

GO

SELECT "Year Number" = YEAR('03/12/1998')

GO

6      DATENAME (datepart, date) - Returns an integer representing the specified date part of the specified date.

Example:

USE Vendor

GO

SELECT DATENAME(month, getdate()) AS 'Month Name'

GO

7      DATEPART(datepart, date)

Is the parameter that specifies the part of the date to return? The table lists date parts and abbreviations recognized by Microsoft SQL Server.

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.

Date

Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.

If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

For more information about specifying time values, see Time Formats. For more information about specifying dates, see datetime and smalldatetime.

Example :

USE Vendor

GO

SELECT GETDATE() AS 'Current Date'

SELECT DATEPART(month, GETDATE()) AS 'Month Number'

SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)

GO

8      DATEADD (datepart, number, date) - Returns the date that results from adding the specified number of datepart units to the date.

Example -

USE Vendor

GO

SELECT DATEADD(day, 21, PostedDate) AS timeframe FROM Vendor

GO

9      DATEDIFF (datepart, startdate, enddate) - Returns the number of datepart units between the specified start date and end date.

Example:

USE Vendor

GO

SELECT DATEDIFF(day, posteddate, getdate()) AS no_of_days

FROM Vendor WHERE VendorFName='Raj'

GO

10   ISDATE (expression) - Returns a value of 1(true) if the expression is a valid date/time value; returns a value of 0(false) otherwise.

Example:

USE Vendor

GO

DECLARE @datestring varchar(8)

SET @datestring = '12/21/98'

SELECT ISDATE(@datestring)

GO

      

More Functions -

1      CASE - Evaluate a list of conditions and returns one of multiple possible return expressions.

CASE has two formats:

·         The simple CASE function compares an expression to a set of simple expressions to determine the result.

·         The searched case function evaluates a set of boolean expressions to determine the result.

Syntax:

Simple CASE function:

CASE input_expression

          WHEN when_expression THEN result_expression

                   [...n]

          [

                   ELSE else_result_expression

          ]

END

Searched CASE function:

CASE

          WHEN Boolean_expression THEN result_expression

                   [...n]

          [

          ELSE else_result_expression

          ]

END

Example:

use Vendor

GO

SELECT VendorId, VendorFName, VendorLName,

CASE VendorId

          WHEN 1 THEN 'This is vendor id one'

          WHEN 2 THEN 'This is vendor id two'

          WHEN 3 THEN 'This is vendor id three'

          WHEN 4 THEN 'This is vendor id four'

          WHEN 5 THEN 'this is vendor id five'

END AS PrintMessage

FROM Vendor

2      COALESCE - Returns the first nonnull expression among its arguments.

Syntax:

COALESCE (expression [...n])

Example:

use Vendor

GO

SELECT PostedDate, COALESCE(PostedDate, '1900-01-01') AS NewDate

FROM Vendor

3      ISNULL - Replaces NULL with the specified replacement value.

Syntax:

ISNULL (check_expression, replacement_value)

Example:

use Vendor

GO

SELECT PostedDate, ISNULL(PostedDate, '1900-01-01') AS NewDate

FROM Vendor

GO

4      GROUPING - Is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP.Grouping is allowed only in the select list associated with a GROUP BY clause that contains either the CUBE or ROLLUP operator.

Syntax:  GROUPING (column_name)

Example -

Use Vendor

GO

SELECT royality, SUM(advance) 'total advance', GROUPING(royality) 'grp'

FROM advance

GROUP BY royality

WITH ROLLUP

5      ROW_Number() - Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition

Syntax:  ROW_NUMBER ( )     OVER ([<partition_by_clause>] <order_by_clause>)

Note:  The ORDER BY in the OVER clause orders ROW_NUMBER. If you add an ORDER BY clause to the SELECT statement that orders by a column(s) other than 'Row Number' the result set will be ordered by the outer ORDER BY.

Example:

Use Vendor

GO

SELECT VendorFName, VendorLName,

ROW_Number() Over(ORDER BY PostedDate) AS 'Row Number'

FROM Vendor

GO

6      RANK () - Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

Syntax: RANK ( )    OVER ([< partition_by_clause >] < order_by_clause >)

Arguments: < partition_by_clause >

Divides the result set produced by the FROM clause into partitions to which the RANK function is applied. For the syntax of PARTITION BY, see OVER Clause (Transact-SQL).

< order_by_clause >

Determines the order in which the RANK values are applied to the rows in a partition. For more information, see ORDER BY Clause (Transact-SQL). An integer cannot represent a column when the < order_by_clause > is used in a ranking function.

Example:

Use Vendor

GO

SELECT VendorId, VendorFName, VendorLName,

RANK() Over(PARTITION BY PostedDate ORDER BY VendorId) AS 'RANK'

FROM Vendor ORDER BY PostedDate DESC

GO

7      DENSE_RANK () - Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

Syntax:  DENSE_RANK ( )    OVER ([< partition_by_clause >] < order_by_clause >)

Arguments: < partition_by_clause >

Divides the result set produced by the FROM clause into partitions to which the DENSE_RANK function is applied. For the syntax of PARTITION BY, see OVER Clause (Transact-SQL).

< order_by_clause >

Determines the order in which the DENSE_RANK values are applied to the rows in a partition. An integer cannot represent a column in the <order_by_clause> that is used in a ranking function.

Example :

Use Vendor

GO

SELECT VendorId, VendorFName, VendorLName,

DENSE_RANK() OVER(PARTITION BY PostedDate ORDER BY VendorId) AS 'DENSE RANK'

FROM Vendor ORDER BY PostedDate DESC

GO

      

NTILE (integer_expression) - Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

Syntax: NTILE (integer_expression) OVER ([<partition_by_clause>] < order_by_clause >)

Arguments: integer_expression

Is a positive integer constant expression that specifies the number of groups into which each partition must be divided? integer_expression can be of type int, or bigint.

Note: 

integer_expression can only reference columns in the PARTITION BY clause. integer_expression cannot reference columns listed in the current FROM clause.

<partition_by_clause>

Divides the result set produced by the FROM clause into partitions to which the RANK function is applied. For the syntax of PARTITION BY, see OVER Clause (Transact-SQL).

< order_by_clause >

Determines the order in which the NTILE values are assigned to the rows in a partition. For more information, see ORDER BY Clause (Transact-SQL). An integer cannot represent a column when the <order_by_clause> is used in a ranking function.

Example :

Use Vendor

GO

SELECT VendorFName, VendorLName,

NTILE(4) OVER(PARTITION BY PostedDate ORDER BY VendorId DESC) AS 'Quartile'

FROM Vendor

GO

      

Login to add your contents and source code to this article

Article Extensions

Contents added by vijay bala on Jun 20, 2012

STUFF : It works like replace

select empname,STUFF(empname,4,3,'f') from EmpDetails

Result:

empname     (New)

jagatheesan jagfeesan

prabhakar   prafkar

sibi        sibf

Rajesh      Rajf

siva        sivf

null        nulf

Contents added by vijay bala on Jun 20, 2012

ISNUMERIC: This function Returns 1 if thevalue are numeric & zero when values are not numeric.
EXAMPLE :

select ISNUMERIC(name) from Employee

Result

new
0
0
0
0
0
0
0
0
0
0

1. The ESCAPE Option

When you need to have an exact match for the actual % and _ characters, use the ESCAPE option. This option specifies what the escape character is. If you want to search for strings that contain SA_, you can search for it using the following SQL statement:

SELECT employee_id, Last_Name, Job_id
FROM employees
WHERE Job_id LIKE '%SA\_%' ESCAPE '\';

The ESCAPE option identifies the backslash (\) as the escape character. In the pattern, the escape character precedes the unserscore( _ ). This causes the Oracle Server to interpret the underscore literally.

2. Rules of Precedence in Oracle 9i

1. Arithmetic Operators
2. Concatenation Operator
3. Comparison conditions
4. IS [NOT] NULL, LIKE, [NOT] IN
5. [NOT] BETWEEN
6. NOT logical condition
7. AND logical condition
8. OR logical condition

3. Use Parentheses to force priority

4. ORDER BY caluse ASCENDING is the default option

5. ASCENDING means least values comes first i.e 1995...1996...1997... etc

6. Manipulations

CONCAT('Hello','World') displays HelloWorld
SUBSTR('HelloWorld',1,5) displays Hello
LENGTH('HelloWorld') displays 10
INSTR('HelloWorld','W') displays 6
LPAD(salary,10,'*') displays *****24000
RPAD(salary, 10, '*') displays 24000*****
TRIM('H' FROM 'HelloWorld') displays elloWorld

7. Number Funcations

ROUND - Rounds value to specified decimal - ROUND(45.926,2) gives 45.93
TRUNC - Truncates value to specified decimal - TRUNC(45.926,2) gives 45.92
MOD - Returns remained of division - Mod(1600,300) gives 100

8. The DUAL table

The DUAL table is owned by the user SYS and can be accessed by all users. It contains one column, DUMMY
and one row with the value x. The DUAL table is useful when you wnat to return a value once only; for instance the value of a constant, pseudocolumn, or expression that is not derived from a table with user data. The DUAL table is generally used for SELECT clause syntax completness, becuase both SELECT and FROM clauses are mandatory, and several calculations do not need to select from actual tables.

9. Working with Dates

Oracle database stores dates in an internal numeric format: Century, year, month, day, hours, minutes,seconds

The default date display format is DD-MON-RR. Allows you to stoew 21st century dates in the 20th century by specifying only the last two digits of the year.

SELECT sysdate from dual

date + Number = Adds a number of days to a date
date - number = Substracts a number of days from a date
date - date = Substracts one date from another
Date + number /24 = Add a nsumber of hours to a date

Funcations :

MONTHS_BETWEEN - Number of months between two dates
ADD_MONTHS - Add calendar months to date
NEXT_DAY - Next day of the date specified

Assume SYSDATE = '25-JUL-95' :

ROUND (SYSDATE, 'MONTH') gives 01-Aug-95
ROUND(SYSDATE, 'YEAR') gives 01-Jan-96
TRUNC(SYSDATE,'MONTH') gives 01-Jul-95
TRUNC(SYSDATE,'YEAR') gives 01-Jan-95


10. Implicit Data-Type Conversion

VARCHAR2 Or Char --> Number
VARCHAR2 OR CHAR --> Date
NUMBER --> Varchar2
DATE --> Varchar2

11. Conversion Funcation

TO_CHAR(Number| date, [fmat],[nlsparams]) - Convert a number or date value to VARCHAR2 character string with format model fmt.

TO_NUMBER(char,[fmt],[nlsparams]) - Converts a character string containing digits to a number int he format specified by the optional format model fmt.

TO_DATE(char,[fmt],[nlsparams]) - Convert a character string representing a date to a date value according to the fmt specifed. IF fmt is omiotted, the format is DD-MON-YY.

YYYY = Full year in numbers
YEAR = Year spelled out
MM = Two-digit vale for month
MONTH = Full name of the month
MON = Three letter abbreviation of the month
DY - Three letter abbrevation of the day of the Week
DAY = Full name of the day of the weel
DD = Numeric day o the month


************Important*********** Understand more on To_CHAR functionality

12.
NVL - Converts a null value to an actual value
NVL2 - If expr1 is not null, NVL2 return expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have data type.
NULLIF - Compares two expression and returns null if they are equal, or the first expression if they ar not equal
COALESCE - Returns the first non-null expression in the expression list.

13. DECODE funcation is same as CASE statement in SQL Server

14. The ANY operator compares a value to each value returned by a subquery. ANY in equivalent to IN

15. The ALL operator compares a value to every value returned by a subquery.

16. You can predefine user variables before executing a SELECT statement using DEFINE.

17. Use the UNDEFINE command to clear it or EXIT iSQL*Plus session

18. Example of define

DEFINE employee_num = 200
SELECT employee_id, Last_name,Salary
FROM employees
WHERE employee_id = &employee_Num

19. You can use the double-ampersand (&&) substitution variable if you want to reuse the variable value wihout prompting the user each time. The user will see the prompt for the value only once.

20. Use the VERIFY command to toggle the display of the substitution variable, before and after iSQL*Plus replaces substitution variables with values. Usage SET VERIFY ON

21. SET system_variable value. Usage SET ECHO ON

22. SET Variable and Values

ARRAY[SIZE] {20 | n} - Sets the database data fetch size

FEED[BACK] {6| n| OFF | ON} - Displays the number of records returned by a query when the query selects at least n records

HEA[DING] {OFF | ON} - Determines whether column hadings are displayed in reports

LONG {80 |n} - Sets the maximum width for displaying LONG values

23. iSQL*Plus Format commands

COLUMN [Column option] - Controls column formats
TTITLE [text | OFF | ON] - Specifies a header to appear at the top of each page of the report
BTITLE [text | OFF | ON] - Specifies a footer to appear at the bottom of each page of the report
BREAK [ON report_element] - Suppresses duplicate values and divides roews of data into sections by using line breaks

24. Column Options

CLE[AR] - Clears any column formats
HEA[DING] text - Sets the column heading ( a vertical line (|) forces a line feed int he heading if you do not use justification)
FOR[MAT] format - Chnages the display of the column data
NOPRI[NT] - Hides the column
NUL[L] text - Specifies text to be displayed for null values
PRI[NT] - Shows the column

25. The BREAK command

Use the BREAK command to divide rows into section and suppress duplicate values. To ensure that the BREAK command works effectively, use the ORDER BY clause to order the columns that you are breaking on.

BREAK on column[ |alias | row]

CLEAR BREAK

26. The WITH CHECK OPTION keyword prohibits you from changing rows that are not in the subquery.

27. Specify DEFAULT to set the column to the value previously specified as the default for the column.

28. The MERGE statement
Provides ability to conditionally update or insert data into a database table
Perofrms an UPDATE if the row exists and an INSERT if it is a new row
Because the MERGE command combines the INSERT and UPDATE commands, you need both INSERT and UPDATE privileges on the target table and the SELECT privilege on the source table.
The MERGE statement is deterministic. You cannot update the same row of the target table multiple times in the same MERGE statement.

29. The MERGE SYNTAX

MERGE INTO table_name AS table_Alias
USING (Table|View|Sub_query) AS alias
ON (Join Condition)
WHEN MATCHED THEN
UPDATE SET
Col1 = Col_Val1
Col2 = Col2_Val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES(Column_Values);

INTO Clause - Specifies the target table you are updating or inserting into
USING Clause - identifies the source of the data to be updated or inserted can be a table view or subquery
ON clause - the condition upon which MERGE operation either updates or inserts
WHEN MATCHED | WHEN NOt MATCHED - Instructs the server how to respond to the result of the join condition.

See example in the ILT-007-Part1.PDF Page 350+

30. IF you create a second savepoint with the same name as an earlier savepoint, the earlier savepoint is deleted.

31. If a single DML statement fails during execution, only that statement is rolled back.

32. The oracle server issues an implicit commit before and after any data definition language(DDL) statement. So even if your DDL statement does not execute successfully, you cannot roll back the previous statement becuase the server issued a commit.

33. Implicit Locking

Exclusive : Locks out other users
Share: Allows other users to access the server

34. Data dictionary Is a collection of tables created and maintained by the Oracle Server, Contains database information. All data dictionary tables are owned bythe SYS user.

35. Four categories of data dictionary views

USER_ : These views contains information about objects owned by the user.
ALL_ : These views contain information anout all of the tables(Object tables and relational tables) accessible to the user.
DBA_ : These views are restricted views, which can be accessed only by people who have been assigned the DBA role.
V$ : These views are dynamic performance views, database server performance, memory and locking.

36. User_tables : See the names of the tables owned by the user.
USer_Objects : View distinct object types owned by the user
User_Catalog or Cat : View tables, views, synonyms and sequences owned by the user.

37. Data Types

VARCHAR2(Size) : Variable-length character data( a maximum size must be specified. Minimum size is 1, Maximum size is 4000)

CHAR [ (Size) ] : Fixed-length character data of length size bytes(default and minimum size is 1, maximum size is 2000)

NUMBER [ (p,s) ] : Number having precision p and scale s( The precision is the total number of decimal digits, and the scale is the number of digits to the right of the decimal point, the precision can range from 1 to 38 and the scale can range from -84 to 127)

DATE : Date and time values to the nearest second between January 1, 4712 B.C. and A.D. December 31 9999

LONG : Variable length character data up to 2 gigabytes

CLOB : Character data up to 4 gigabytes.

RAW(size): Raw binary data of length size(a maximum size must be specified maximum size is 2000)

LONG RAW : Raw binary data of variable lenght up to 2 gigabytes.

BLOB : Binary data up to 3 gigabytes.

BFILE : Binary data stored in an external file; up to 4 gigabytes.

ROWID : Hexadecimal string representing the unique addres of a row in its table. The datatype is primarily for values returned by the ROWID pseudocolumn.


38. LONG columns

is not copied when a table is created using a subquery
Cannot be included in a GROUP BY or an ORDER BY clause
Only one LONG column can be used per table
No constraint can be defined on a LONG column
You may wnat to use a CLOB column rather than a LONG column.

39. DATE

TIMESTAMP : Allows the time to be stored as a date with fractional seconds. There are several variations of the data type.

INTERVAL YEAR TO MONTH : Allow time to stored as an interval of years and months.

INTERVAL DAY TO SECOND : Allows time to be stored as an interval of days to hours minutes and seconds.

40. The integrity rules are not passed onto the new table, only the column data type definitions.

41. The SET UNUSED option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. This is a feature available in Oralce8i and later release. Specifying this clause does not actually remove the target columns from each row in the table(That is, it does not restore the disk space used by these columns). Therefore the response time is fater than if you executed the DROP clause. Unused columns are treated as if they were dropped, even though their column data remains in the tables rows. After a column has been marked as unused, you have to access to that column. A SELECT * query will not retrieve data from unused columns. In addition, the names and typoes of columns marked unused will nbot be displayed during a DESCRIBE, and you can add to the table a new column with the same bame as an unused column. SET UNUSED information is tored in the USER_UNUSED_COL_TABS dictionary view. (Page 402).

42. Comments can be viewed through the data dictionary views:

ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS

43. cONSTRAINTS
enforces rules at the table level
prevent the deleteion of a table if there are dependencies

44. Valid constraintes

NOT NULL : Specifies that the column cannot contain a null value
UNIQUE : Specifies a column or combination of folumns whose values must be unique for all rows in the table.
Primary Key : Uniquely identifies each row of the table
Foreign Key : Estabilishes and enforces a foreign key relationship between the column and a column of the refrenced table
CHECK : Specifies a condtion that must be true.

45. Constraints are stored int he data dictionary. If you do not name a constraint the Oralce server generates a name with the format SYS_Cn, where n is an integer so that the constraint nema is unique. You can view the constraints defined for a specific table by looking at the USER_CONSTRAINTS data dictionary table.


46. FOREIGN KEY constraint

FOREIGN KEY is used to define the column in the child table at the table constrinat level.

REFERENCES identifies the table and column in the parent table.

ON DELETE CASCADE indicates that when the row in the parent table is deleted, the dependent rows in the child table will also be deleted

ON DELETE SET NULL convers foreign key values to null when the parent value is removed.
Withou the ON DELETE CASECADE or the ON DELERE SET NULL options, the row in the parent table cannot be deleted if it is referenced in the child table.

47. The CHECK constraint defines a condition that each row must satisfy. The condtion can use the same contructs as query condtions, with the following exceptions.

Refernces to the CURRVAL,NEXTVAL,LEVEL and ROWNUM pseudocolumns

Calls to SYSDATE,UID,USER and USERENV functions


48. Query the USER_Constraints table to view all constraint definitions and names. View the columns associated with the constraint names in the USER_CONS_Columns view.

49. Creating VIEWS

FORCE : Creates the view regardless of whether or not the base tables exist

NOFORCE: Creates the view inly if the base tables exist(This is the default)

WITH CHECK OPTION: specifies that only rows accessible to the view can be inserted or updated

WITH READ ONLY : ensures that no DML operations can be performed on this view

50. You can remove a row from a view unless it contains any of the following

- Group functions
- A GROUP BY clause
- The DISTINCT keyword
- The pseudocolumn ROWNUM keyboard

51. You can add data through a view unless it contains any of the items listed in the slide and there are NOT NULL columns, without default values, in the base table that are not selected by the view.

The WITH CHECK OPTION clause specified that INSERTS and UPDATES performed throguh the view cannot creat rows which cannot select and therefore it allows integrity constraints and data validation checks to be enforced on data being inserted or updated.

52. INLINE VIEWS is a subquery with an alias(or correlation name) that you can use within SQL statement. A named sunquery in the FROM clause of the main query is an example of an inline view.. An inline view is not a schema object.

53. Performing Top-n analysis

Top-n queries use a consistent nested query structure with the elements describled below:

A subquery or an iline view to genereate the sorted list of data. The subquery or the iline view includes the ORDER BY clause to ensure that the ranking is in the desired order. For results retrieving the largest values, a DESC parameter is needed.

An outer query to limit the number of rows in the final result set. The outer query includes the following components:

The ROWNUM pseudocolumn, whcih assigns a sequential value starting with 1 to each of the following rows returned from the subquery

A WHERE clause, whcih specifies the n rows to be returned. The outer WHERE clause must use a < or <= operator.

54. A sequence is a number created database object that can be shared by multiple users to generate unique integers. A typical usage for sequences is to create a primary key value, whcih must be unique for each row. The sequence is generated and incremented by an internal Oralce routine. This can be a time saving object because it can reduce the amount of application code needed to write a sequence-generating routine.

Sequence numbers are stored and generated independently of tables. Therfore the same sequence can be used for multiple tables.

55. CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

NOMAXVALUE - specifies a maximum value of 10^27 for an ascending sequence and -1 for a descending sequence

CYCLE | NOCYCLE : specifies where the sequence continues to generate values after reaching its maximum or minimum value

CACHE n | NOCACHE: Specifies how many values the Oracle Server preallocates and keep in memory(By default, the Oracle Server caches 20 values)

Data dictionary table : USER_SEQUENCES


56. You can use NEXTVAL and CURRVAL in the follwoing contexts:

The SELECT list of a SELECT statement that is no part of a sunquery
The SELECT list of a subquery in an iNSERT statement
The VALUES clause of an INSERT statement
The SET clause of an UPDATE statement.

You cannot use NEXTVAL and CURRVAL in the following contexts :

A SELECT list of a view
A SELECT statement with the DISTINCT keyword
A SELECT statement with GROUP BY, HAVING or ORDER BY clause
A subquery in a SELECT,DELETE or UPDATE statement
The DEFAULT expression in a CREATE TABLE OR ALTER TABLE statement

57. Gaps in sequence values can occur when:

-A rollback occurs
- The system crashes
- A sequence is used in another table.

58. Two types of indexes can be created. One type is a unique index. The Oracle Server automatically creates this index when you define a column in a table to have a PRIMARY EKY or a UNIQUE key constraint. The name of the index is the name given to the constraint.

The other type of index is a nonunique inex, whcih a suer can create. For example, you can create a FOREIGN KEY column index for a join in a query to improve retrieval speed.

59. You should create an index if:

A Column contains a wide range of values
A column contains a large number of null values
One or more columns are frequently used toghether in a WHERE clause or a join condition
The table is large and most queries are expected to retieve less than 2 to 4% or the rows.

60. It is usually not worth creating an index if:

The table is small
The columns are not often used as a condtion in the query
Most queries are expected to retrieve more than 2 to 4% of the rows in the table
The table is updated frequently
The indexed columns are referenced as part of an expression.

The USER_INDEXES data dictionary contains the name of the index and its uniqueness
The USER_IND_COLUMNS view contains the index name, the table name, and the column name.

61. Function-based indexes defined with the UPPER(Column_Name) OR LOWER(Column_Name)
keywords allow case insensitive searches, For example the following index

CREATE INDEX Upper_last_name_idx ON employeees(UPPER(last_name));

Faciliates processing queries such as:

SELECT * FROM employee WHERE UPER(Last_Name) = 'KING';

62. Typical DBA privileges

CREATE USER: Grantee cab create other Oracle users( a privilege required for a DBA role)
DROP USER: Grantee can drop another user
DROP ANY TABLE: Grantee can drop table in any schema
BACKUP ANY TABLE: Grantee can back up any table in any schema with the export utility
SELECT ANY TABLE: Grantee can query tables, views or snapshot in any schema.
CREATE ANY TABLE: Grantee can create tables in any schema.

63. Creating USER

CREATE USER user IDENTIFIED BY password;

Once a user is created, the DBA can grant specific system privilages to a user

GRANT privilege [,privilege...] TO user [,user | role, PUBLIC...]

An application developer, for example, may have the following system privileges

CREATE SESSION - Connect to the database
CREATE TABLE - Create tables in the users schema
CREATE SEQUENCE - Create a sequence in the users schema
CREATE VIEW - Create a view in the users schema
CREATE PROCEDURE - Create a stored procedure, function or package in the users schema

Privilege - is the system privilege to be granted
user|role|PUBLIC - is the name of the user, the name fo the role, or PUBLIC designates that every user is granted the privilege

Current system privileges can be found in the dictionary view SESSION_PRIVS.

GRANT create session, create table,create sequence,create viee TO SCOTT;

ROLE

A role is a named group of related privileges that can be granted to the user. This method makes it easier it revoke and maintain privileges

CREATE ROLE role;

CREATE ROLE Manager;

GRANT create table, create view to manager

GRANT manager to Bhasker sarada ;

ALTER USER Scott IDENTIFIED by lion;

WITH GRANT OPTION : A privilege that is granted with the WITH GRANT OPTIoN caluse can be passed on to other users and roles by the grantee. Object privileges granted with the WITH GRANT OPTION clause are revoked when the grantors privilege is revoked.

The PUBLIC Keyword : An ownder of atable can grant access to all users by using the PUBLIC keyword.

ROLE_SYS_PRIVS - System privileges granted to roles
ROLE_TAB_PRIVS - Table privileges granted to roles
USER_ROLE_PRIVS - Roles accessible by the user
USER_TAB_PRIVS_MADE - object privileges granted on the users objects
USER_TAB_PRIVS_RECD - Object prvvileges granted to the user
USER_COL_PRIVS_MADE - Object privileges granted on the columns of the users objects
USER_COL_PRIVS_RECD - Object privileges granted to the user on specific columns
USER_SYS_PRIVS - Lists system privileges granted to the user.

REVOKE {privilege [,privilege...] | A::}
on object
FROM {user[, user....] |role|PUBLIC}
[CASCADE CONSTRAINT];

CASCADE : is required to remove any refrential integrity constraints made to the
CONSTRAINT : object by means of REFERENCE privilege

Privileges granted to others through the WITH GRANT OPTION clause are also revoked


64. A database link connection allows local user to access data on a remote database.
USER_DB_LINKS contains information on links to which a user has access

CREATE PUBLIC DATABASE LINK hq.acme.com USING 'sales;'

SELECT * FROM fred.emp@HQ.ACME.com;

65. SET Operators

UNION - All distinct rows selected by either query
UNION ALL - All rows selected by either query, including all duplicates
INTERSECT - All distinct rows selected by both queries
MINUS - All distinct rows that are selected by first SELECT statement abd that are not selected in the second SELECT statement.

66. Three new data types are added to DATE

TIMESTAMP
TIMESTAMP WITH TIME ZONE (TSTZ)
TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ)

Oracle 9i provides daylight savings support for datetime data types in the server.

CURRENT_DATE : function returns the current date in the sessions time zone. The return value is a date in the Gregorian calender.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ALTER SESSION SET TIME_ZONE = '-5:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM dual;

CURRENT_TIMESTAMP function returns the current date and time in the session time zone, as a value of the data type TIMESTAMP WITH TIME ZONE.

LOCALTIMESTAMP : function returns the current date and time in the session time zone in a value of data type TIMESTAMP.

DBTIMEZONE function returns the value of the database time zone.

SESSIONTIMEZONE function returns the value of the current session time zone.

EXTRACT expression extracts and returns the value of a specified datetime field from a datetime or interval value expression.

SELECT EXTRACT ([YEAR] [MONTH] [DAY] [HOUR] [MINUTE] [SECOND] TIMEZONE_HOUR] [TIMEZONE_MINUTE]
[TIMEZONE_REGION] [TIMEZONE_ABBR]
FROM [datetime_value_expression] [interval_value_expression]) ;

FROM_TZ function converts a time stamp value to a TIMESTAMP WITH TIME ZONE value.

TO_TimeSTAMP and TO_TimeSTAMP_TZ

The TO_TIMESTAMP function converts a string of CHAR,VARCHAR2, NCHAR or NVARCHAR2 data type to a value of TIMESTAMP data type. The syntax of the TO_TIMESTAMP function is

TO_TIMESTAMP (CHAR,[fmt],['nlsparam'])

TO_YMINTERVAL function convers a character string of CHAR,VARCHAR2,NCHAR or NVARCHAR2 data type to an INTERVAL YEAR TOMONTH data type.

TZ_OFFSET function returns the time zone offset corresponding to the value entered.



67.Guidelines for using GROUP functions

The data types for the arguments can be CHAR, VARCHAR2,NUMBER or DATE

All group functions except COUNT(*) ignores null values. To substitute a value for null values, use the NVL funcation. COUNT returns either a number of zero.

The Oracle Server implicitly sorts the result set in ascending order of the grouping columns specified, when you use a GROUP BY clause. To override this default ordering, you can use DESC in an ORDER BY clause.

68. Use the HAVING clause to specify which groups are to be displayed. You further restrict the groups on the basis of a limiting condition. The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical.

69. ROLLUP

The ROLLUP operator delivers aggreates and superaggregates for expression within a GROUP BY statement. The ROLLUP operator can be used by report writer to extract statistics and summary information from results sets. The cumulative aggregates can be used in reports, charts and graphs. The ROLLUP operator creates groupings by moving in one direction from right to left, along the list of columns specified in the GROUP BY clause. It then applies the aggregate function to these groupings.

To product subtotals in n dimensions(that is, n columns in the GROUP BY clause) without a ROLLUP operator, n+1 SELECT statements must be linked with UNION ALL. This makes the query execution inefficient becuase each of the SELECT statements cause table access. The ROLLUP operator gahters its results with just one table access. The ROLLUP operator is usdeful if there are many columns involved in producing the subtotals.

SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);

70. CUBE operator

The CUBE operator is an additional switch in the GROUP BY clause in a SELECT statement. The CUBE operator can be applied to all aggregate functions including AVG, SUM, MAX, MIN and COUNT. It is ued to produce results set that are typically used for cross-tabular reports. While ROLLUP produces only a fraction of possible subtotal combinations, CUBE produces subtotals for all possible combinations of groupings specified in the GROUP BY clause and a grand total.

The CUBE operator is used with an aggregate function to generate additional rows in a result set. Columns included in the GROUP BY clause are cross-referenced to produce a superset of groups. The aggregate function specified in the sleect list is applied to the groups to produce summary values for the additonal superaggregate rows. The number of extra groups in the results set is determined by the number of columns included in the GROUP BY clause.

SELECT department_id, job)id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY CUBE(department_id,Job_id)


71. GROUPING FUNCTION

The GROUPING function can be used with either the CUBE or ROLLUP operator to help you understand how a summary value has been obtained.

The GROUPING function uses a single column as its argument. The expr in the GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 0 or 1.

The values returned by the GROUPING function are useful to

Determine the level of aggregation of a given subtotal; that is the group or groups on whcih the subtotalis based.

Identify whether a NULL value in the expression column of a row of the result set indicates:
- A NULL value from the base table(stored NULL value)
- A NULL value created by ROLLUP/CUBE (as a result of a group function on that exression)

A value of 0 returned by the GROUPING function based on an expression indicates one of the following:
- The expression has been used to calculate the aggregate value.
- The NULL value in the expression column is a stored NULL value.

A value of 1 returned by the GROUPING function based on an expression indicates one of the following:
- The expression has not been used to calculate the aggregate value
- The NULL value in the expression column is created by ROLLUP or CUBE as a result of grouping.

SELECT department_id DEPTID, Job_id JOB, SUM(Salary),
GROUPING(department_id) grp_dept, GROUPING(Job_id) GRP_JOB
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, Job_id);


72. GROUPING SETS

GROUPING SETS are a further extension of the GROUP BY clause that let you specify multiple groupings of data. Doing so facilitates efficient aggregation and hence facilitates analysis of data across multiple dimensions.

A single SELECT statement can now be written using GROUPING SETS to specify various groupings(that can also include ROLLUP or CUBE operators), rather than multiple SELECT statements combined by UNION ALL operator.

73. Composite columns

A composite column is a collection of columns that are treated as a unit during the computation of groupings. You specify the columns in parentheses as in the following statement:

ROLLUP(a,(b,c),d)

Here (b,c) form a compositecolumn and are treated as a unit. In general composite columns are useful in ROLLUP,CUBE and GROUPING SETS. For example in CUBE or ROLLUP, composite columns would mean skipping aggregation across certain levels.

That is, GROUP BY ROLLUP(a,(b,c))

is equivalent to
GROUP BY a,b,c UNION ALL
GROUP BY a UNION ALL
GROUP BY()

HERE(B,c) are treated as a unit and rollup will not be applied across (b,c). It is as ir you have an alias, for example z, for (b,c) and the GROUP BY expression reduces to
GROUP BY ROLLUP(a,z)

SELECT department_id, job_id, manager_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id,(job_id,Manager_id));

74. Concatenated Columns

Concatenated groupings offer a concise way to generate useful combinations of groupings. The concatenated groupings are specified simply by listing multiple grouping sets, cubes and rollups and separating them with commas. Here is an example of concatenated grouping sets

GROUP BY GROUPING SETS(a.b), GROUPING SETS(C,d)

The preceding SQL defines the follwoing groupings:

(a,c), (a,d), (b,c), (b,d)

Concatenation of groupings sets is very helpful for these reasons:

Ease of query development you need not enumerate all groupings manually
Use by applications: SQL generated by OLAP application oftern involves concatenation of grouping sets
with eacn grouping set defining groupings needed for a dimension.

SELECT department_id,job_id, manager_id, SUM(salary)
FROM employees
GROUP BY department_id, ROLLUP(job_id), CUBE(Manager_id);


75. A scalar subquery expression is a subquery that returns exactly one column value from one row. In Oracle9i, scalar subqueries can be used in

- Condition and expression part od DECODE and CASE
- All clauses of SELECT except GROUP BY

76. The Oracle Server performs a correlated subquery when the subquery references a column from a table rererred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT,UPDATE or DELETE statement.

77. NESTED Subquery Execution

-The inner query executes first and finds a value
-The outer query executes once, using the value from the inner query.

Correlated subquery Execution
-Get a candidate row(fetched by the outer query)
-Execute the inner query using the value of the candidate row
-Use the values resulting from the inner query to qualify or disqualify the candidate
-Repeat until no candidate row remains


78. The WITH Clause

Using the WITH clause, you can define a query block before using it in a query. The WITH clause(formally known as subqyery_factoring_clause) enables you to reuse the same query block in a SELECT statement when it occurs more than once with in a complex query. This is particularly useful when a query has many references to the same query block and there are joins and aggregations.

Using the WITH clause, you can reuse the same query when it is high cost to evaluate the query block and it occurs more than once within a complex query. Using the WITH clause, the Oracle Server retrieves the results of a query block and stores it in the users temporary tablespace. This can improve performance

-- Makes the query easy tp read
-- Evaluates a clause only once, even if it appears multiple times in the query, therby enhancing performance.

WITH
dept_costs AS (
SELECT department_name, SUM(salary) AS dept_total
FROM employees, departments
WHERE employyes.department_id =
departments.department_id
GROUP BY department_name)
AVG_Cost AS
(SELECT SUM(dept_total) / COUNT(*) AS dept_avg
FROM dept_COSTS )
SELECT * FROM dept_COSTS
WHERE DEPT_total >
(SELECT FROM dept_avg)
ORDER BY department_name;

79. Hierarchical Queries

SELECT [LEVEL], column, expr....
FROM table
[WHERE condition(s) ]
[START WITH condition(s) ]
[CONNECT BY PRIOR condition(s)];

SELECT : is the standard SELECT clause
LEVEL : For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root rpw, 2 for a child of a root and so on.

FROM table :Specifies the table, view or snapshot containing the columns you can select from only one table.

WHERE : Restricts the rows returned by the query without affecting other rows of the hierarchy.

Condtion : is a comparision with expressions

START WITH : Specifed the root rows of the hierarchy(sher to start). This clause is rquired for a true hierarchical query.

CONNECT BY Prior : Specifieds the columns in which the relationship between parent and child rows exist. This clause is required for a hierarchical query.

The SELECT statement cannot contain a join or query from a view that contains a join.

The row or rows to be used as the root of the tree are determined by the START WITH clause. The START WITH clause can be used in conjunction with any valid condition.

Examples

Using the EMPLOYEES table,start with King, the president of the company

... START WITH manager_id IS NULL

Using the EMPLOYEES table, start with employee Kochhar. A START WITH condition can contain a subquery

... START WITH employee_id = (SELECT employee_id FROM employees WHERE last_name = 'Kochhar')

If the START WITH caluse is omitted, the tree walk is started with all of the rows in the table as root rows. If a WHERE clause is used, the walk is started with all the rows that satisfy the WHERE condition. This no longer reflects a true hierarchy.

The director of the query, whether it is from parent to child or from child to parent, is determined by the CONNECT BY PRIOR column placement. The PRIOR operator refers to the parent row. To find the children of a parent row, the Oracle Server evaluates the PRIOR expression for the parent row and the other expressions for each row in the table. Rows for whci the condition is ture are the children of the parent. The Oracle Server always selects children by evaluating the CONNECT BY condition with respect to a current parent row.

Examples

Walk from the top down using the EMPLOYEES table. Define a hierarchical relationship in whcih the EMPLOYEE_ID value of the parent row is equal to the MANAGER_ID value of the child row.

... CONNECT BY PRIOR employee_id = manager_id

Walk from the bottom up using the EMPLOYEES table

--- CONNECT BY PRIOR manager_id = employee_id

The PRIOR operator does not necessarily need to be coded immediately following th eCONNECT BY. Thus the following CONNECT BY PRIOR clause gives the same result as the one in the preceding example.

... CONNECT BY employee_id = PRIOR manager_id

Note: The CONNECT BY clause cannot contain subquery.

-- Understand more on hierarchy


80. MULTITABLE INSERT statement

In a multitable INSERT statement, you insert computed rows derived from the rows returned from the evaluation of a subquery into one or more table.

As with the existing INSERT .... SELECT statement, the new statement can be parallelized and used with the direct-load mechanism for faster performance.

Each record from any input stream, wuch as a nonrelational database table, can now be converted into multiple records for more relational table environment. To implement this functionality before Oracle9i you had to write multiple INSERT statements.

TYPES OF MULTITABLE INSERT Statements

Oracle 9i introduces the following types of multitable INSERT statements

Unconditional INSERT
Conditional ALL FIRST
Conditional FIRST INSERT
Pivoting INSERT

You use different clauses to indicate the type of INSERT to be executed.

SYNTAX

INSERT [ALL] [conditional_insert_clause]
[insert_into_clause Values_clause] (subquery)

Conditional_insert_clause

[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]


Unconditonal INSERT: ALL into_clause

specify ALL followed by multiple insert_into_clause to perform an unconditional multitable insert. The Oracle Server executes each insert_into_clause once for each row returned by the subquery.

Conditional INSERT : conditonal_insert_clause

Specify the conditional_insert_clause to perform a conditional multitable insert. The Oracle server filters each insert_into_clause through the corresponding WHEN condition, which determines whether that insert_into_clause is executed. A single multitable insert statement can contain up to 127 WHEN clause.

Conditional INSERT: ALL

if you specify ALL, the Oracle server evaluates each WHEN clause regardless of the results of the evaluation of any other WHEN clause. For each WHEN clause whose condition evaluates to true, the Oracle server executes teh corresponding INTO clause list.

Conditional FIRST: INSERT

if you specify FIRST, the Oracle Server evaluates each WHEN clause in the order in which it appears iin the statement. If the first WHN clause evaluates to true, the Oracle Server executes the corresponding INTO clause and skips subsequent WHEN clause for the given row.

Condtional INSERT: ELSE Clause

For a given row, if no WHEN clause evaluates to true:

If you have specified an ELSE clause the Oracle Server executes th INTO clause list associated with the ELSE clause.
If you do not specify an ELSE clause, the Oralce server takes no action for that row.

Restrictions on Multitable INSERT statements

You can perform multitable inserts on tables, not on views or materialized views
You cannot perform a multitable insert into a remote table.
You cannot specify a table collection expression when performing a multitable insert
In a multitable insert, all of the insert_into_clauses cannot combine to specify more than 999 target columns

Unconditional INSERT ALL

INSERT ALL
INTO sal_history VALUES(empid, hiredate,sal)
INTO mgr_history VALUES(empid,MGR,SAL)

SELECT employee_id, EMPID, hire_date HIREDATE,salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200

( 8 rows created)


The example in the slide inserts rows into both SAM_History and MGR_HISTORY tables. The SELECT statement retrieves the details of employee ID, hire date, salary and manager ID of those employees whose employee ID is greater than 200 from the EMPLOYEE table. The details of the employee ID, hire date and salary are inserted into the SAL_HISTORY table. The details of employee ID, manager ID and salary are inserted into the MGR_History table.

This INSERT statement is referred toas an unconditional INSERT, as no further restriction is applied to the rows that are retrieved by the SELECT statement. All the rows retrieved by the SELECT statement are inserted into the tow tables SAL_HISTORY and MRG_HISTORY. The VALUES clause in the INSERT statements specifies the columns fromt he SELECT statment that have to be inserted into each of the tables. Each row returned by the SELECT statment results in two inserts, one for the SAL_HISTORY table and one for the MRG_HISTORY table.


The feedback 8 rows created can be interpreted to mean that a ttal of eight inserts were performed on the base tables, SAL_HISTORY and MRG_HISTORY.

Conditional INSERT ALL

INSERT ALL
WHEN sal > 10000 THEN
INTO sal_hisotry VALUES(EMPID,MGR,SAL)
WHEN MRG > 200 THEN
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE,
Ssalary SAL, manager_id MGR
FROM employees
WHERE employee_id > 2000;
(4 rows created)

The example on the slide is similar to the example on the previous slide as its inserts rows into both the SAL_HISTORY and the MGR_HISTORY tables. The SELECT statement retrieves the details of employee ID,hire date, salary and Manager ID of these employees whose employee ID is greate then 200 fro the EMPLOYEEES table. The details of employee ID, hire date and salary are inserted into the SAL_HISTORY table. The details of employee ID, manager ID and salary are inserted into the MGR_HISTORY table.

This INSERT statement is referred to as a condtional ALL INSERT as a futhrer restrictions is applied to the rows that are retrieved by the SELECT statement. From the rows that are retrieved by the SELECT statement. From the rows that are retrieved by the SELECT statement, only those rows in whcih the value of the SAL column is more than 10000 are inserted int he SAL_HISOTRY table and similarly only those rows where the value of the MGR column is more than 200 are inserted in the MGR_HISOTRY table.

Observe that unlide the previos example, where eight rows were inserted into the tables, in this example only four rows are inserted.

The feedback 4 rows created can be interpreted to mean that a total of four inserts were performed on the base tables, SAL_HISTORY and MRG_HISTORY.

Condtional FIRST INSERT

INSERT FIRST
WHEN SAL > 25000 THEN
INTO special_sal VALUES(DEPTID,SAL)
WHEN HIREDATE LIKE ('%') THEN
INTO hiredate_history_oo VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN
INTO hiredate_hisotry_99 VALUES(DEPTID,HIREDATE)
ELSE
INTO hiredate_history VALYES(DEPTID,HIREDATE)
SELECT depart_id DEPTID, SUM(salary) SAL,
MAX(hireDATE) HIREDATE
FROM EMPLOYEES
GROUP BY department_id;
(8 rows created)

The example in the slide inserts rows into more than one table, using one single INSERT statement. The SELECT statement retrieves the details of deartment ID, total salary, and maximum hire date for every department in the EMPLOYEE table.

This INSERT statement is referred to as a conditional FIRST INSERT as an exception is made from the departments whose total salary is more than $25,000. The condition WHEN ALL > 25000 is evaluated first. If the total salary for a department is more then $25,000, then the record is inserted into the SPECIAL_SAL table irresective of the hire date. If this first WHEN clause evaluates to true, the Oracle server executes the corresponding INTO clause and skips subsequent WHEN clause for this row.
For the rows that do not satisfy the first WHEN conditon (WHEN SAL > 25000) the rest of the conditons are evaluated just as a conditonal INSERT statemnet and the records retrieved by the SELECT statement are inserted into the HIREDATE_HISTORY_00 or HIREDATE_HISTORY_99 or HIREDATE_HISTORY tables, based on the values in the HIREDATE column.

The feedback 8 rows created can be interpreted to mean that a total of eight INSERT statements where performed on the base tables, SPECIAL_SAL, HIREDATE_HISTORY_00, HIREDATE_HISTORY_99 and HIREDATE_HISTORY.

Pivoting INSERT

Pivoting is an operation in whcih you need to build a transformation such that each record from any input stream such as a nonrelational database table, must be converted into multiple records for a more relational database table environment.

In order to solve the problem mentioned in the slide, you need to build a tranformation wuch that each record from the original nonrelational database table, SALES_SOURCE_DATA is converted into five records for the data warehouse SALES_INFO table. This operation is commonly referred to as pivoting.

The problem statment for a pivoting INSERT statment is specified in the slide. The solution to the preceding problem is shown in the next page.

INSERT ALL
INTO sales_info VALUES (employee_id,week_id,sales_MON)
INTO sales_info VALUES (employee_id,week_id,sales_TUE)
INTO sales_info VALUES (employee_id,week_id,sales_WED)
INTO sales_info VALUES (employee_id,week_id,sales_THUR)
INTO sales_info VALUES (employee_id,week_id,sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_Mon,sales_TUE
sales_WED, sales_THUR,sales_FRI
FROM sales_Source_Date;
(5 rows Created)

EXTERNAL TABLE

An external table is a read-only table whose metdata is stred in the database but whose data is stored outside the database. Using the Oracle9 external table feature, you can use external data as virtual table. This dat can be queried and joined directly and in parallel without requiring the external data to be first loaded in the database. You can use SQL, PL/SQL and Hava to query the data in an external table.

The main difference between external tables and regular table sis that externally organized tables are read-only. NO DML operation (update.Insert.Delete) are possible and no indexes can be created on them.

This means of defining the metadata fro external tables is throgh CREATE tABLE .... ORGANIZATION EXTERNAL statement. This external table definition can be thought of as view that is used for running any SQL query against external data without requiring that the external data first be loaded into the database.

The Oracle Server provides tow major access drivers for external tables. One, the loader access driver or ORACLE_LOADER, is used for reading of data from external files using the ORACLE loader technology. This access driver allows the Oracle server to access data from any data source whose format can be interpreted by the SQL*Loader utility. The other Oracle proveded access driver, the import/export access driver or ORACLE_INTERNAL can be used for both the importing and exporting of data using a platform independent format.

Creating an external tables

You create external tables using the ORGANIZATION EXTERNAL clause of the CREATE TABLE statement. You are not in fact creating a table. Rather, you are creating metadata in the data dictionary that you can use to access external data. The ORGANIZATION clause lets you specify the order in which the data rows of the table are stored. By specifying EXTERNAL in the ORGANIZATION clause, you indicate that the table is a read-only table loacated outside the database.

TYPE access_driver_type indicates that access driver of the external table. The access driver is the Application Programming Interface(API) that interprests the external data for the database. If you do not specify TYPE, Oralce used the default access driver, ORACLE_Loader.

The REJECT LIMIT clause lets you specify how many conversion errors can occur during a query of the external data before an Oracle error is reutrned and the query is aborted. The defulat value is 0.

DEFAULT DIRECTORY let you specify one or more defulat directory objects corresponding to directories ion the file system where the external data sources may reside. Default directroies can also be used by the access driver to store auxiliary files such as error loges. Multiple defulats direcotries are permitted to facilitate load balanceing on multiple disk drivers.

The optional ACCESS PARAMETERS clause lets you assign values to the parameters of the specific access driver for the external table. Oracle does not interpret anything in this clause. It is up to the access driver to interpret this information in the context of the external data.

The LOCATION clause lets you specific one external locaotr for each external data source. Usually the location_specifier is a file, but it need not be. Oracle does not interpret this clause. It is up to the access drive to interpret this information in the context of the external data.

Example of creating an external table

Creat a Directory object that corresponds to the directyon the file system where the external data source resides

CREATE DIRECOTRY emp_id AS '/flat_files';

Use the CREATE DIRECTORY statement to create a directory object. A directory object specifies an alias for a directory on the server's file system where an external data source resides. You can use directory names when referring to an external data source, rahter than hard-code the operating system pathname, for greater file management flexibility.

You must have CREATE ANY DIRECTORY system privileges to create directories. When you create a directory, you are automatically granted the READ object privilege and can grant READ privileges to other users and roles. The DBA can also grant this privilege to the other users and roles.

Syntax

CREATE [OR REPLACE] DIRECOTRY AS 'path_name';

OR REPLACE : Specify OR REPLACE to re-create the direcotry databae object if it already exists. You can use this clause to change the definiton of an existing directory without dropping,re-creating and regranting database object privileges previuosly granted on the directory. Users who had previously been granted privileges on a redefined direcotry can still access the direcory without being regranted the privileges.

directory : Specify the name of the directory object to be created. The maximum lenght of the directory is 30 bytes. You cannot qualify a direcotry object with a scheme name.

'Path_name' : Specify the full pathname of the operating system directory on the server where the files are loacted. The single quotes are required withe the result that the path name is case sensitive.

CREATE TABLE oldemp (
empno Number, empname Char(20), birthdate DATE)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECOTRY emp_dir
ACCESS Parameters
(RECORDS DELIMITED BY NEWLINE
BADFILE 'bad emp'
LOGFILE 'log_emp'
FIELDS TERMINATED BY ','
(empno CHAR,
empName CHAR,
birthdate CHAR date_format date mask "dd-mon-YYY"))
LOCATION ('empl,txt'))
PARALLEL 5
REJECT LIMIT 2000;

Assume that there is a flat file that has records in the following format:
10,Jones,11-Dec-1934
20,smith,12-Jun-1972

Records are demimted by the new lines, and the fields are all terminated by a ",". The name fo the file is :
/flat_files/emp1.txt

To convert the files are the data soruce for an external table whoze metadata will resides int he database, you need to perform the following steps:

271

1. Create a directory object emp_dir as follows
CREATE DIRECTORY emp_dir AS '/flat_files';

2. Run the CREATE TABLE command shown in the slide

The example in the slide illustrates the table specification to create an external table for the file:

/flat_files/emp1.txt

In the example the TYPE specification is given only to illustrate its use. If not specified, ORACLE_LOADER is the default access driver. The ACCESS PARAMETERS provide values to parameters of the specific access driver and are interpreted by the access driver, not by the Oracle Server.

The PARALLEL clause enables five parallel execution servers to simultaneously scan the external data sources(files) when executing the INSERT INTO TABLE statement. For example if PARALLEL = 5 were specified, then more that one parallel execution server could be working on a data source. Beacuase external tables can be very large for performance reasons it is advisable to specify the PARALLEL clause, or a parallel hint for the query.

The REJECT LIMIT clause specifies that if nore than 200 conversion errors occur during the query of the external data, the query is aborted and an error returned. These conversion errors can arise when the access driver tries to transform the data in the data file to match the external table definition.

Querying External Table

An external table does not describe any data that is stored in the database. Nor does it describe how data is stored in the external source. Instead, it describes how the external tablelayer needs to present the data to the server. It is the responsibiliry of the access driver and the external table layer to do the necessary transformations required on the data in the data file so that is matches the external table definition.

When the database server needs to access data in an external soruce, it calls the appropriate access drive to get the data from an external source in a form that the database server expects.

It is important to remember that the dwcription of the data in the data source is separate from the definition of the external table. The source file can contain more or fewer fields than there are columns in the table. Also the data types for fields in the data source can be different from the columns in the table. The access driver takes care of ensuring the data from the data source is processed so that it matches the definition of the external table.

CREATE INDEX with CREATE TABLE statement

In the example in the slide, the CREATE INDEX clause is used with the CREATE TABLE statment to create a primary key index explicitly. This is an enhancement provided with Oracle 9i. You can now name your indexes at the time of PRIMARY key creation, unlike before where the Oracle Server would create an index, but you did not have any control over the name of the index. The following example illustrate this

CREATE TABLE EMP_UNNAMED_INDEX
(employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2 (25));

(Table Created)

SELECT INDEX_NAME, TABLE_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = 'EMP_UNNAMED_INDEX';

Observe that the Oracle server gives a name to the Index that it creates for the PRIMARY KEY column. But this name is cryptic and not easily understood. With Oracle9i, you can name your PRIMARY KEY column indexes as you create the table with the CREATE TABLE statement. However, proior to Oracle9i, if you named your primary key constraint at the time of constrainst creation, the index would also be creates with the same name as the constraint name.


Questions :

Refer to the SQL codes below:
SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees;
What has been achieved?

A. it calculates, for each employee in the employees table, the average
salary of the employees reporting to his/her respective manager

B. it calculates, for each employee in the employees table, the average
salary of the employees reporting to the same manager who were hired in
the range just before through just after the employee

C. because of a syntax problem, no row will be returned

D. it calculates, for each employee in the employees table, the average
salary of the employees reporting to his/her respective manager who were
hired just after the employee

E. it calculates, for each employee in the employees table, the average
salary of the employees reporting to his/her respective manager who were
hired just before the employee

ANS:B