Transcript DATABASE LINK

SQL Workshop
Day 2
Copyright © 2011 Accenture All Rights Reserved. Accenture, its logo, and High Performance Delivered are trademarks of Accenture.
Day 2 Agenda
SQL data selection
• SQL
•
•
•
•
•
•
•
Data type
Grouping functions
Sets
Oracle functions
Practice
DB link
Practice
© Accenture 2011. All Rights Reserved.
2
Selected data types
VARCHAR2(size [BYTE | CHAR])
NUMBER[(precision [, scale]])
DATE
© Accenture 2011. All Rights Reserved.
Variable-length character string having maximum length size bytes or characters.
Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character.
You must specify size for VARCHAR2.
BYTE indicates that the column will have byte length semantics; CHAR indicates
that the column will have character semantics.
Number having precision p and scale s. The precision p can range from 1 to 38. The
scale s can range from -84 to 127.
Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default
format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly
by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype
contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
It does not have fractional seconds or a time zone.
3
Group functions
SELECT
FROM
[WHERE
[GROUP
[ORDER
[column,] group_function(column), …
table
condition]
BY column]
BY column]
• Group function returns one result per record group
• Considers all values (DISTINCT can be used)
• Ignores NULL values (use nvl)
SELECT deptno, count(*), MAX(sal)
FROM emp
GROUP BY deptno
© Accenture 2011. All Rights Reserved.
4
Group functions
HAVING clause
SELECT [column,] group_function(column), …
FROM table
[WHERE condition]
[GROUP BY column]
[HAVING group_condition]
[ORDER BY column]
• Use HAVING clause to restrict grouping results
SELECT
FROM
GROUP
HAVING
© Accenture 2011. All Rights Reserved.
deptno, count(*), MAX(sal)
emp
BY deptno
count(*) > 2
5
Practice SQL_3
Please display list of the employees (name, salary and department
number) which have the highest salary in department with more
than 2 persons.
© Accenture 2011. All Rights Reserved.
6
Sets
Sets allow us to process the results of two or more queries as if they
were one.
We can combine the results from the different queries using the
normal mathematical set operations.
These are:
union
union all
minus
intersect
© Accenture 2011. All Rights Reserved.
7
Sets
Query 1
Query1
Union all
Query2
© Accenture 2011. All Rights Reserved.
Query 2
Query1
Minus
Query2
Query1
Intersect
Query2
8
Single row functions
characters
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
ASCII Returns the ASCII decimal equivalent of a character
CHR Returns the character given the decimal equivalent
CONCAT Concatenates two strings; same as the operator ||
INITCAP Returns the string with the first letter of each word in uppercase
INSTR Finds the numeric starting position of a string within a string
INSTRB Same as INSTR, but counts bytes instead of characters
LENGTH Returns the length of a string in characters
LENGTHB Returns the length of a string in bytes
LOWER Converts a string to all lowercase
LPAD Left-fills a string to a set length using a specified character
LTRIM Strips leading characters from a string
RPAD Right-fills a string to a set length using a specified character
RTRIM Strips trailing characters from a string
REPLACE Performs substring search and replace
SUBSTR Returns a section of the specified string, specified by numeric
character positions
• SUBSTRB Returns a section of the specified string, specified by numeric
byte positions
• TRIM Strips leading, trailing, or both leading and trailing characters from a
string
• UPPER Converts a string to all uppercase
© Accenture 2011. All Rights Reserved.
9
Single row functions
numeric
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
ABS Returns the absolute value
ACOS Returns the arc cosine
ASIN Returns the arc sine
ATAN Returns the arc tangent
ATAN2 Returns the arc tangent; takes two inputs
BITAND Returns the result of a bitwise AND on two inputs
CEIL Returns the next higher integer
COS Returns the cosine
COSH Returns the hyperbolic cosine
EXP Returns the base of natural logarithms raised to a power
FLOOR Returns the next smaller integer
LN Returns the natural logarithm
LOG Returns the logarithm
MOD Returns the modulo (remainder) of a division operation
POWER Returns a number raised to an arbitrary power
ROUND Rounds a number
SIGN Returns an indicator of sign: negative, positive, or zero
SIN Returns the sine
SINH Returns the hyperbolic sine
SQRT Returns the square root of a number
TAN Returns the tangent
TANH Returns the hyperbolic tangent
TRUNC Truncates a number
© Accenture 2011. All Rights Reserved.
10
Single row functions
date
• ADD_MONTHS Adds a number of months to a date
• CURRENT_DATE Returns the current date (same as SYSDATE)
• CURRENT_TIMESTAMP Returns the current date and time in a
TIMESTAMP datatype
• DBTIMEZONE Returns the database’s time zone
• EXTRACT Returns a component of a date/time expression
• FROM_TZ Returns a timestamp with time zone for a given timestamp
• LAST_DAY Returns the last day of a month
• LOCALTIMESTAMP Returns the current date and time in the session
time zone
• MONTHS_BETWEEN Returns the number of months between two dates
• NEW_TIME Returns the date/time in a different time zone
• NEXT_DAY Returns the next day of a week following a given date
• ROUND Rounds a date/time
• SESSIONTIMEZONE Returns the time zone for the current session
• SYS_EXTRACT_UTC Returns the UTC (GMT) for a timestamp with a
time zone
• SYSDATE Returns the current date/time
• SYSTIMESTAMP Returns the current timestamp
• TRUNCATE Truncates a date to a given granularity
• TZ_OFFSET Returns the offset from UTC for a time zone name
© Accenture 2011. All Rights Reserved.
11
Single row functions
conversion
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
ASCIISTR Converts characters to ASCII
BIN_TO_NUM Converts a string of bits to a number
CAST Converts datatypes
CHARTOROWID Casts a character to ROWID datatype
COMPOSE Converts to Unicode
CONVERT Converts from one character set to another
DECOMPOSE Decomposes a Unicode string
HEXTORAW Casts a hexadecimal to a raw
NUMTODSINTERVAL Converts a number to an interval day to second
NUMTOTMINTERVAL Converts a number to an interval year to month
RAWTOHEX Casts a raw to a hexadecimal
ROWIDTOCHAR Casts a ROWID to a character
TO_CHAR Converts and formats a date into a string
TO_DATE Converts a string to a date, specifying the format
TO_DSINTERVAL Converts a string to an interval day to second
TO_MULTIBYTE Converts a single-byte character to its corresponding multibyte
equivalent
TO_NUMBER Casts a numeric string to a number, specifying the format
TO_SINGLE_BYTE Converts a multibyte character to its corresponding singlebyte equivalent
TO_YMINTERVAL Converts a string to an interval year to month
UNISTR Converts UCS2 Unicode
Copyright
© 2010 2011.
Accenture
All Rights
Reserved.Copyright © 2010 Accenture All Rights Reserved.
Reserved.
© Accenture
All Rights
Reserved.
12
Single row functions miscellaneous
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
BFILENAME Returns a BFILE locator for the specified file and directory
COALESCE Returns the first non-NULL in a list
DECODE Inline case statement (an IF…THEN…ELSE function)
DUMP Returns a raw substring in the specified encoding
(octal/hex/character/decimal).
EMPTY_BLOB Returns an empty BLOB locator
EMPTY_CLOB Returns an empty CLOB locator
GREATEST Sorts the arguments and returns the largest
LEAST Sorts the arguments and returns the smallest
NULLIF Returns NULL if two expressions are equal
NVL Returns second parameter if first is null
SYS_CONNECT_BY_PATH Returns root-to-node values in a CONNECT
BY query
SYS_CONTEXT Returns various session attributes, such as IP address,
terminal, and current user
UID Returns the numeric user ID for the current session
USER Returns the username for the current session
USERENV Deprecated in favor of SYS_CONTEXT
VSIZE Returns the internal size in bytes for an expression
© Accenture 2011. All Rights Reserved.
13
Aggregating Data using Group Functions
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
AVG Returns the statistical mean
CORR Returns the coefficient of correlation of number pairs
COUNT Returns the number of non-NULL rows
COVAR_POP Returns the population covariance of number pairs
COVAR_SAMP Returns the sample covariance of number pairs
CUME_DIST Returns the cumulative distribution of values within groupings
DENSE_RANK Returns the ranking of rows within an ordered group, without skipping ranks on ties
FIRST Modifies other aggregate functions to return expressions based on ordering of the second column expression
GROUP_ID Returns a group identifier used to uniquely identify duplicate groups
GROUPING Returns 0 for nonsummary rows or 1 for summary rows
KEEP Modifies other aggregate functions to return the first or last value in a grouping
LAST Modifies other aggregate functions to return expressions based on ordering of the secondary column expression
MAX Returns the largest value
MIN Returns the smallest value
PERCENT_RANK Returns the percentile ranking of the specified value
PERCENTILE_CONT Returns the interpolated value that would fall in the specified percentile position using a continuous model
PERCENTILE_DISC Returns the interpolated value that would fall in the specified percentile position using a discrete model
RANK Returns the ranking of rows within an ordered group, skipping ranks when ties occur
REGR_AVGX Returns average x value in non-NULL (y,x) pairs
REGR_AVGY Returns average y value in non-NULL (y,x) pairs
REGR_COUNT Returns number of non-NULL (y,x) pairs
REGR_INTERCEPT Returns the linear regression y intercept
REGR_R2 Returns the linear regression coefficient of determination
REGR_SLOPE Returns the linear regression slope
REGR_SXX Returns the sum of the squares of the independent variable expression
REGR_SXY Returns the sum of the products of the independent variable expression and the dependent variable expression
REGR_SYY Returns the sum of the squares of the dependent variable expression
STDDEV Returns the standard deviation
STDDEV_POP Returns the population standard deviation
STDDEV_SAMP Returns the sample standard deviation
SUM Adds all values and returns the result
VAR_POP Returns the population variance
VAR_SAMP Returns the sample variance
VARIANCE Returns the sample variance or 1 for sample size 1
© Accenture 2011. All Rights Reserved.
14
Practice SQL_4
Please write the SQL statement which displays departments name
and maximum earnings in these departments for which maximum
earnings are smaller than average earnings of all workers and
sorted ascending by name of departments.
© Accenture 2011. All Rights Reserved.
15
Practice SQL_5
Please write the SQL statement which displays currency rate valid
for a currency and day given in a parameter.
Currency rates are kept in EXCHANGE_RATE table listed below
EXCHANGE_RATE
#
CURRENCY
VARCHAR2(3)
#
VALID_FROM
DATE
VALUE
NUMBER
Please note that not all dates are reflected in the table. Despite that
the query shall work aslo for days when no data in the table exists.
© Accenture 2011. All Rights Reserved.
16
Database Link
A database link is a schema object in one database that enables
you to access objects on another database.
Local DataBase
user
SELECT *
FROM emp@rem_db
Local
DataBase
DB link:
rem_db
Remote
DataBase
SQL*Net
EMP Table
DataBase link
SQL*Net
© Accenture 2011. All Rights Reserved.
17
Database Link
To create a private database link, you must have
the CREATE DATABASE LINK system privilege.
To create a public database link, you must have the CREATE PUBLIC
DATABASE LINK system privilege.
You must have the CREATE SESSION system privilege on the remote Oracle
database.
CREATE [PUBLIC] DATABASE LINK dblink_name
CONNECT TO oracle_username
IDENTIFIED BY password
USING 'database_sid’
© Accenture 2011. All Rights Reserved.
18
Database Link
Example:
$ sqlplus jones/lion@SOL3
SQL> CREATE DATABASE LINK sol1.world
CONNECT TO scott IDENTIFIED BY tiger
USING 'SOL1';
With this DB-Link, you (jones/lion) can connect to the remote
database SOL1 as user scott/tiger. This user must exist on the
remote database SOL1.
© Accenture 2011. All Rights Reserved.
19
Database Link
After you have created a database link, you can use it in SQL
statements to refer to tables and views on the other database by
appending @dblink_name to the table or view name.
SELECT *
FROM emp@dblink_name
It is possible to use in one SQL query tables from local and remote
database.
© Accenture 2011. All Rights Reserved.
20
Source code view
All the PLSQL code (procedure, package, …) stored in the database
is avaiable by a database view.
TIP use user_source view.
Useful columns of user_source view:
name
type
line
text
© Accenture 2011. All Rights Reserved.
– the name of the object
– the type of the object
– line of the source
– content of the line
Practice SQL_6
Please write the SQL statement (or set of statments) which will
compare stored PLSQL code in 2 database schemas – local
schema and schema in remote database.
© Accenture 2011. All Rights Reserved.
22