1Z0-071 essentials with examples and notes
second rule of normalization
if a table has a compound pk, and one or more fields depend on only part of the pk for that table, move them to a separate table along with that part of the key
third rule of normalization
if one or more fields in a table do not depend at all on the pk for that table (or any part of it), move them to a separate table along with copies of the fields on which they depend
DISABLE CONSTRAINT... CASCADE - notes
if you do ENABLE CONSTRAINT after DISABLE CONSTRAINT... CASCADE, the things disabled as a result of the cascade will need to be re-enabled manually
ALL_ data dictionary view prefix
objects owned by any user in the database to which the current user has privileges
USER_ data dictionary view prefix
objects owned by the current user accessing the view note: USER views lack the OWNER column that exists in the DBA_ and ALL_ views, since that information is redundant
V$NLS_PARAMETERS
the NLS parameters that are in effect for the session querying this view
multiple-row subquery - notes
- they can return multiple columns as well as rows - they can contain a subquery within a subquery - they can contain group functions and GROUP BY and HAVING clauses they should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery
CONNECT BY
Specifies the relationship between parent rows and child rows of a hierarchical query
first rule of normalization
a table shall contain no repeating groups
V$DATABASE
the view that lets you access information about the database (such as archivelog status)
conditional/unconditional insert
(related to multitable inserts) The following keywords are used in 'conditional insert first' statements: INSERT, FIRST, WHEN, THEN. The following keywords are used in 'unconditional insert all' or 'pivot insert' statements: INSERT, ALL. The following keywords are used in 'conditional insert all' statements: INSERT, ALL, WHEN, THEN.
list of aggregate functions - and notes
**Aggregate functions return one value for each group of rows in the SELECT statement.*** You can nest aggregate functions two levels deep. That means an aggregate function can be passed as a parameter into a second aggregate function, provided that the second aggregate function is not passed as a parameter to a third aggregate function. ***************************************************************** commonly used: COUNT, SUM, MIN, MAX, AVG;* MEDIAN variance and standard deviation: VARIANCE, VAR_POP, VAR_SAMP, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP* ranking functions and associated keywords: RANK, DENSE_RANK, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST, FIRST, LAST* grouping functions for use with GROUP_BY... ROLLUP and CUBE: GROUP_ID, GROUPING, GROUPING_ID {*means preceding list may also be used as analytic functions}
transitive dependency
A condition in which an attribute is dependent on another attribute that is not part of the primary key.
useful views on schema objects
**USER_ views listed below; there are DBA_ and ALL_ equivalents** USER_INDEXES - describes all indexes in the current schema USER_IND_COLUMNS - describes the columns of indexes on all tables in the current schema USER_SEQUENCES - describes all sequences in the current schema USER_SYNONYMS - describes all synonyms in the current schema USER_SYS_PRIVS - describes system privileges granted to the current user USER_TAB_COLUMNS - describes columns of all tables, views, and clusters in the current schema USER_TAB_COLS - describes the columns of the tables, views, and clusters owned by the current user. This view differs from "USER_TAB_COLUMNS" in that hidden columns are not filtered out USER_TAB_PRIVS - describes all object grants in the current schema USER_TABLES - describes all relational tables in the current schema USER_OBJECTS - describes all objects in the current schema - shows the status of a view
list of analytic (AKA window) functions but tbh you don't really need to know these
--CUME_DIST Calculate the cumulative distribution of a value in a set of values --DENSE_RANK Calculate the rank of a row in an ordered set of rows with no gaps in rank values. --FIRST_VALUE Get the value of the first row in a specified window frame. --LAG Provide access to a row at a given physical offset that comes before the current row without using a self-join. --LAST_VALUE Get the value of the last row in a specified window frame. --LEAD Provide access to a row at a given physical offset that follows the current row without using a self-join. --NTH_VALUE Get the Nth value in a set of values. --NTILE Divide an ordered set of rows into a number of buckets and assign an appropriate bucket number to each row. --PERCENT_RANK Calculate the percent rank of a value in a set of values. --RANK Calculate the rank of a value in a set of values --ROW_NUMBER Assign a unique sequential integer starting from 1 to each row in a partition or in the whole result --(plus most of the aggregate functions)
Order of processing in select
1.FROM 2.JOIN 3.WHERE 4.GROUP BY 5.HAVING 6.SELECT 7.DISTINCT 8.ORDER BY 9.FETCH
2NF
1NF and no partial dependencies
normalization forms in plain english
1NF is the most basic of normal forms - each cell in a table must contain only one piece of information, and there can be no duplicate rows. 2NF and 3NF are all about being dependent on the primary key. Recall that a primary key can be made up of multiple columns. "The data depends on the key [1NF], the whole key [2NF] and nothing but the key [3NF] (so help me Codd)." --someone on Github
3NF
2NF and no transitive dependencies
cascading (revoke) on privileges
3 types of privileges. System DML Privileges, System DDL Privileges, and Object Privileges. revoke... System DML privs do cascade. System DDL privs do not cascade. Object privs do cascade. GRANT CREATE TABLE WITH GRANT OPTION - system priv ddl - No cascade GRANT SELECT ALL WITH GRANT OPTION - system priv dml - cascades GRANT INSERT, DELETE ON HR.EMPLOYEES - object priv - cascades S ystem D D L No Cascade; S ystem D M L Yes Cascade, O bject Yes Cascade "Shannen Doherty - No, Steve Martin - Yes, Oprah - Yes"... (people you would invite to a party)
single-row operators
=, >,<, >=,<=, <>, != query must return one thing (must be a single-row subquery)
HAVING clause
A clause of the SELECT statement. Filters the results grouped by a GROUP BY clause. Can only reference aggregate functions or columns that are also specified in the GROUP BY clause. The select list can include any columns that are already grouped in the GROUP BY clause, or aggregate functions. Nothing else can appear in the select list.
CONSTRAINT clause
A CONSTRAINT clause is an optional part of a CREATE TABLE statement or ALTER TABLE statement. A constraint is a rule to which data must conform. Constraint names are optional. A CONSTRAINT can be one of the following: a column-level constraint Column-level constraints refer to a single column in the table and do not specify a column name (except check constraints). They refer to the column that they follow. a table-level constraint Table-level constraints refer to one or more columns in the table. Table-level constraints specify the names of the columns to which they apply. Table-level CHECK constraints can refer to 0 or more columns in the table. Column constraints include: NOT NULL Specifies that this column cannot hold NULL values (constraints of this type are not nameable). PRIMARY KEY Specifies the column that uniquely identifies a row in the table. The identified columns must be defined as NOT NULL. {Note: If you attempt to add a primary key using ALTER TABLE and any of the columns included in the primary key contain null values, an error will be generated and the primary key will not be added. See ALTER TABLE statement for more information.} UNIQUE Specifies that values in the column must be unique. FOREIGN KEY Specifies that the values in the column must correspond to values in a referenced primary key or unique key column or that they are NULL. CHECK Specifies rules for values in the column. Table constraints include: PRIMARY KEY Specifies the column or columns that uniquely identify a row in the table. NULL values are not allowed. UNIQUE Specifies that values in the columns must be unique. FOREIGN KEY Specifies that the values in the columns must correspond to values in referenced primary key or unique columns or that they are NULL. Note: If the foreign key consists of multiple columns, and any column is NULL, the whole key is considered NULL. The insert is permitted no matter what is on the non-null columns. CHECK Specifies a wide range of rules for values in the table. Column constraints and table constraints have the same function; the difference is in where you specify them. Table constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY constraint definition. Column-level constraints (except for check constraints) refer to only one column. A constraint operates with the privileges of the owner of the constraint. See "Using SQL standard authorization" and "Privileges on views, triggers, and constraints" in the Java DB Developer's Guide for details.
ROLLBACK
A SQL command that restores the database table contents to the condition that existed after the last COMMIT statement. A ROLLBACK is the opposite of a COMMIT Ends a transaction but does not result in COMMIT
nonequijoin
A join condition containing something other than an equality operator; values in a column in one table must be conditional to but not equal to a value(s) in another table. A non-equijoin is used when no corresponding columns exist between the tables in the query, but rather a relationship exists between two columns having compatible data types. Several conditions can be used to define a non-equijoin, including <, <=, >, >=, BETWEEN, and IN. The not-equal signs != and <> are not what is intended with a non-equijoin. Remember, these are not "not-equal" joins but rather "non-equijoins." an example: select b.ename, b.sal, a.dname from scott.dept a, scott.emp b where a.deptno=b.deptno and a.sal > 1000;
alternative quoting mechanism
A method allowing you to define your own string delimiters to remove the need to double up any single quotes. Any character that is not present in the string can be used as the delimiter. syntax: { Q | q } 'quote_delimiter c [ c ]... quote_delimiter' Q or q indicates that the alternative quoting mechanism will be used. This mechanism allows a wide range of delimiters for the text string. The outermost ' ' are two single quotation marks that precede and follow, respectively, the opening and closing quote_delimiter. c is any member of the user's character set. You can include quotation marks (") in the text literal made up of c characters. You can also include the quote_delimiter, as long as it is not immediately followed by a single quotation mark. quote_delimiter is any single- or multibyte character except space, tab, and return. The quote_delimiter can be a single quotation mark. However, if the quote_delimiter appears in the text literal itself, ensure that it is not immediately followed by a single quotation mark. If the opening quote_delimiter is one of [, {, <, or (, then the closing quote_delimiter must be the corresponding ], }, >, or ). In all other cases, the opening and closing quote_delimiter must be the same character. Here are some valid text literals using the alternative quoting mechanism: q'!name LIKE '%DBMS_%%'!' q'<'So,' she said, 'It's finished.'>' q'{SELECT * FROM employees WHERE last_name = 'Smith';}' nq'ï Ÿ1234 ï' q'"name like '['"'
multi-column subquery
A multi-Column Subquery is simply a subquery that returns more than one column. Here is a working example of a multi-column subquery: SQL> select 2 book_key 3 from 4 sales 5 where 6 (store_key, 7 order_date) in (select 8 store_key, 9 max(order_date) 10 from 11 sales join store using (store_key) 12 where 13 store_state = 'SC' 14 group by store_key); BOOK_K ------ B111 B109 The query lists the books in the latest order from stores in South Carolina. The subquery returns two columns, a store_key and the date of the latest order. The comparison is a pair-wise comparison, meaning that the column values are compared as a pair and not individually. Both must match for the filter to return TRUE.
natural join
A relational operation that yields a new table composed of only the rows with common values in their common attribute(s); a join that is the same as an equi-join except that one of the duplicate columns is eliminated in the result table. Natural joins join two tables by all columns with the same name. Example scenario (in relation to attached graphic) These are the same: SELECT faculty_name, COUNT(student_id) FROM student JOIN faculty USING (faculty_id, location_id) GROUP BY faculty_name; SELECT faculty_name, COUNT(student_id) FROM student NATURAL JOIN faculty GROUP BY faculty_name; It can be seen from the structure of the two tables that the fields with the same name are faculty_name and location_id, and using uses these two fields. So the first join is equivalent to the second, natural join. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The NATURAL keyword must be specified. Neither an ON nor a USING clause can be used with the NATURAL JOIN syntax. You can *not* explicitly provide the join condition with a NATURAL JOIN NATURAL JOIN and USING clause ARE MUTUALLY EXCLUSIVE!!!! If natural join specified but the tables have no columns with the same name, a cross product is produced.
ON DELETE SET NULL
A setting when creating a foreign key that says if a record in the parent table is deleted, then the corresponding records in the child table have their key value set to NULL.
ampersand substitution facts
A substitution variable is a user variable name preceded by one or two ampersands (&). When SQL*Plus encounters a substitution variable in a command, SQL*Plus executes the command as though it contained the value of the substitution variable, rather than the variable itself. DEFINE list all existing variables defined for the session SET DEFINE Defines the substitution character (by default the ampersand "&") and turns substitution on and off. SHOW DEFINE displays the current state of the DEFINE system variable. SHOW ALL lists all system variables SET DEFINE OFF Disables variable substitution
1NF
A table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key, is said to be in 1NF.
MULTISET
A variable-length and unordered collection of elements MULTISET is similar to the table datatype.
ADD_MONTHS
ADD_MONTHS returns the date date plus integer months. The date argument can be a datetime value or any value that can be implicitly converted to DATE. The integer argument can be an integer or any value that can be implicitly converted to an integer. The return type is always DATE, regardless of the datatype of date. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date. Examples The following example returns the month after the hire_date in the sample table employees: SELECT TO_CHAR( ADD_MONTHS(hire_date,1), 'DD-MON-YYYY') "Next month" FROM employees WHERE last_name = 'Baer'; Next Month ----------- 07-JUL-1994
The database administrator has determined that the Sex column in the Customer table is not relevant and has not been used on any reports. You need to ensure the following: Applications that query the table do not display the sex of the customer in a report. The column is still physically stored in the table. What should you run?
ALTER TABLE Customer SET UNUSED Sex;
primary key - removing
ALTER TABLE DEMO DROP PRIMARY KEY; ALTER TABLE DEMO DROP CONSTRAINT PK_DEMO; Any constraint (except NOT NULL) can be dropped using the syntax in given statement.
AUDIT/NOAUDIT
AUDIT - Sets up auditing for a specific schema object. NOAUDIT - Stops auditing defined by a prior AUDIT statement for schema objects.
methods to alter table
Add Column ALTER TABLE tablename ADD columnname datatype; Drop Column ALTER TABLE tablename DROP COLUMN columnname; Modify Column ALTER TABLE tablename MODIFY columnname newdatatype; Rename Column ALTER TABLE tablename RENAME COLUMN currentname TO newname; Add Constraint ALTER TABLE tablename ADD CONSTRAINT constraintname constrainttype (columns); Drop Constraint ALTER TABLE tablename DROP CONSTRAINT constraintname; ALTER TABLE tablename DROP constraint_type constraintname; Rename Table ALTER TABLE tablename RENAME TO newtablename;
LPAD
Adds the specified number and type of characters to the left of the specified value. LPAD( expr, length [, pad_expr] )
RPAD
Adds the specified number and type of characters to the right of the specified value. RPAD( expr, length [, pad_expr] )
CASE
Allows the usage of IF-THEN-ELSE logic in an SQL statement. CASE [expression] WHEN condition_1 THEN result_1WHEN condition_2 THEN result_2...WHEN condition_n THEN result_nELSE resultEND case_name
INTERVAL DAY TO SECOND
Allows time to be stored as an interval of days to hours, minutes, and seconds. interval d-s syntax (matches attached graphic): INTERVAL '{ integer | integer time_expr | time_expr }' { { DAY | HOUR | MINUTE } [ (leading_precision) ] | SECOND [ (leading_precision [, fractional_seconds_precision ]) ] } [ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ] integer specifies the number of days. If this value contains more digits than the number specified by the leading precision, then Oracle returns an error. time_expr specifies a time in the format HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n], where n specifies the fractional part of a second. If n contains more digits than the number specified by fractional_seconds_precision, then n is rounded to the number of digits specified by the fractional_seconds_precision value. You can specify time_expr following an integer and a space only if the leading field is DAY. leading_precision is the number of digits in the leading field. Accepted values are 0 to 9. The default is 2. fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 1 to 9. The default is 6. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Restriction on the Leading Field: If you specify a trailing field, it must be less significant than the leading field. For example, INTERVAL MINUTE TO DAY is not valid. As a result of this restriction, if SECOND is the leading field, the interval literal cannot have any trailing field. The valid range of values for the trailing field are as follows: HOUR: 0 to 23 MINUTE: 0 to 59 SECOND: 0 to 59.999999999 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~` examples: Form of Interval Literal - Interpretation INTERVAL '4 5:12:10.222' DAY TO SECOND(3) - 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second. INTERVAL '4 5:12' DAY TO MINUTE - 4 days, 5 hours and 12 minutes. INTERVAL '400 5' DAY(3) TO HOUR - 400 days 5 hours. INTERVAL '400' DAY(3) - 400 days. INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) - 11 hours, 12 minutes, and 10.2222222 seconds. INTERVAL '11:20' HOUR TO MINUTE - 11 hours and 20 minutes. INTERVAL '10' HOUR - 10 hours. INTERVAL '10:22' MINUTE TO SECOND - 10 minutes 22 seconds. INTERVAL '10' MINUTE - 10 minutes. INTERVAL '4' DAY - 4 days. INTERVAL '25' HOUR - 25 hours. INTERVAL '40' MINUTE - 40 minutes. INTERVAL '120' HOUR(3) - 120 hours. INTERVAL '30.12345' SECOND(2,4) - 30.1235 seconds. The fractional second '12345' is rounded to '1235' because the precision is 4. INTERVAL '1 2:11:11.234' DAY TO SECOND(3) INTERVAL '555 2' DAY(3) TO HOUR INTERVAL '123 DAY(3) ***BAD*** INTERVAL '3 3-11' DAY TO MINUTE - Error. A colon, not dash, should be used.
INTERVAL YEAR TO MONTH facts
Allows time to be stored as an interval of years and months. To specify literal values for the INTERVAL YEAR TO MONTH data type, you use the following format: INTERVAL 'year[-month]' leading (precision) TO trailing Where leading and trailing can be YEAR or MONTH. 'year[-month]' are integers for the leading and trailing fields of the interval. If leading is YEAR and trailing is MONTH, then the month field ranges from 0 to 11. The trailing field must be less than the leading field. For example, INTERVAL '1-2' MONTH TO YEAR is invalid because trailing is YEAR which is greater than the leading field which is MONTH. precision is the maximum number of digits in the leading field. The precision ranges from 0 to 9 and its default value is 2. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ examples: Form of Interval Literal - Interpretation INTERVAL '123-2' YEAR(3) TO MONTH - An interval of 123 years, 2 months. You must specify the leading field precision if it is greater than the default of 2 digits. INTERVAL '123' YEAR(3) - An interval of 123 years 0 months. INTERVAL '300' MONTH(3) - An interval of 300 months. INTERVAL '4' YEAR - Maps to INTERVAL '4-0' YEAR TO MONTH and indicates 4 years. INTERVAL '50' MONTH - Maps to INTERVAL '4-2' YEAR TO MONTH and indicates 50 months or 4 years 2 months. INTERVAL '123' YEAR - Returns an error, because the default precision is 2, and '123' has 3 digits.
LISTAGG
Allows you to aggregate strings from data in columns. LISTAGG( measure_expr [, delimiter]) WITHIN GROUP (order_by_clause) [OVER query_partition_clause] The purpose of the Oracle LISTAGG function is to allow you to perform aggregation of strings from data in columns. Usage of the LISTAGG Function There are a few ways you can use this function. If you use it without any grouping, LISTAGG operates on all rows and returns a single row. If you use it with grouping, LISTAGG operates on and returns a row for each group defined by the GROUP BY clause. If you use it as an analytic function, LISTAGG partitions the query result set into groups, based on one or more expressions in the query_partition_clause. Example 1 This example uses LISTAGG to aggregate all values in the table SELECT LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) as LISTAGG_OUTPUT FROM customers; Result: LISTAGG_OUTPUTAllan,Brown,Cooper,Jones,Manson,Smith,Thompson It shows all values of last_name in the table, separated by a single comma character. Example 2 This example shows you how to use LISTAGG on a subset of records using the WHERE clause. SELECT LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) as LISTAGG_OUTPUT FROM customers WHERE country = 'USA'; LISTAGG_OUTPUTCooper,Jones,Smith As you can see, it shows a single row, but only those last_names where the country is USA. Example 3 This example uses LISTAGG in Oracle to aggregate the last_name values for each country. Because LISTAGG is an aggregate function, any other columns listed here need to have a GROUP BY. It can also use the PARTITION BY, but we'll cover that later. SELECT country, LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) as LISTAGG_OUTPUT FROM customers GROUP BY country; COUNTRYLISTAGG_OUTPUTCanadaBrownFranceMansonUKAllanUSACooper,Jones,Smith(null)Thompson Each country is listed, along with the last_name only for the records that match the country. Example 4 This example shows what happens if we don't specify the ORDER BY. SELECT country, LISTAGG(last_name, ',') WITHIN GROUP (last_name) as LISTAGG_OUTPUT FROM customers GROUP BY country; Result: ORA-30491: missing ORDER BY clause 30491. 00000 - "missing ORDER BY clause" You'll get this error if you leave out the words ORDER BY, or the entire clause that's inside the brackets. Example 5 This example shows what happens using the default delimiter, using the example above for each country. SELECT country, LISTAGG(last_name) WITHIN GROUP (ORDER BY last_name) as LISTAGG_OUTPUT FROM customers GROUP BY country; Result: COUNTRYLISTAGG_OUTPUTCanadaBrownFranceMansonUKAllanUSACooperJonesSmith(null)Thompson The result is the same, but the data is not separated by a comma. The default delimiter is nothing, so there is nothing in between each value. Example 6 This is the same query as above, but using two characters as a delimiter. SELECT country, LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) as LISTAGG_OUTPUT FROM customers GROUP BY country; COUNTRYLISTAGG_OUTPUTCanadaBrownFranceMansonUKAllanUSACooper, Jones, Smith(null)Thompson This result is a little more readable, as we have a comma and a space in between the values. Example 7 This example shows how the PARTITION BY is used. I've added a few extra columns to make more sense of it. SELECT first_name, last_name, country, LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) OVER (PARTITION BY country) as LISTAGG_OUTPUT FROM customers; FIRST_NAMELAST_NAMECOUNTRYLISTAGG_OUTPUTSteveBrownCanadaBrownPeterMansonFranceMansonMarkAllanUKAllanAdamCooperUSACooper,Jones,SmithSallyJonesUSACooper,Jones,SmithJohnSmithUSACooper,Jones,SmithJoshThompson(null)Thompson(null)(null)(null)Thompson Each row from the customers table is shown. For each row, we have the LISTAGG function, which shows an aggregation of last_name values. Notice how we did not use a GROUP BY. The GROUP BY is not needed if you use the OVER PARTITION BY clause. The PARTITION BY clause tells you how the values inside the LISTAGG are split. You can see that the last_name values that are shown are those for the country, and in the case of records with a country of USA, they are repeated. Example 8 This example is the same as above, but the PARTITION BY clause is on the last_name field. SELECT first_name, last_name, country, LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) OVER (PARTITION BY last_name) as LISTAGG_OUTPUT FROM customers FIRST_NAMELAST_NAMECOUNTRYLISTAGG_OUTPUTMarkAllanUKAllanSteveBrownCanadaBrownAdamCooperUSACooperSallyJonesUSAJonesPeterMansonFranceMansonJohnSmithUSASmithJoshThompson(null)Thompson(null)(null)(null)(null) As you can see, the LISTAGG function doesn't really do much. This is because it's partitioned on the same value that is being aggregated. Example 9 What if we didn't want a partition and wanted to show the other data as well? SELECT first_name, last_name, country, LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) as LISTAGG_OUTPUT FROM customers Result: ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function" Oracle treats this as using an aggregate function, and as a result, you need to use a GROUP BY.
ORACLE_LOADER
An access driver that can be used to access any data stored in any format that can be loaded by SQL*Loader. The ORACLE_LOADER access driver is the default. It cannot cannot write to the file, only read, and the data must come from a text file. It loads data from external tables to internal tables. The data must come from text data files. (The ORACLE_LOADER access driver cannot perform unloads; that is, it cannot move data from an internal table to an external table.) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The ORACLE_LOADER and ORACLE_DATAPUMP access drivers support similar functionality in the SQL*Loader and Data Pump utilities. The metadata for an external table created with either access driver is stored inside the database, but in the case of the ORACLE_DATAPUMP driver, it's also stored as XML in the external dump file itself. You cannot create any type of index on an external table defined with either access driver. The file created with the ORACLE_LOADER access driver can be read with the Oracle Data Pump impdp command. An external table defined with the ORACLE_LOADER driver only has the data outside of the database.
ORACLE_DATAPUMP
An access driver, a method whereby you can load an external table using an Oracle facility by the same name. The ORACLE_DATAPUMP access driver can read from and write to external binary dump files. The ORACLE_DATAPUMP access driver can perform both loads and unloads. The data must come from binary dump files. Loads to internal tables from external tables are done by fetching from the binary dump files. Unloads from internal tables to external tables are done by populating the binary dump files of the external table. The ORACLE_DATAPUMP access driver can write dump files only as part of creating an external table with the SQL CREATE TABLE AS SELECT statement. Once the dump file is created, it can be read any number of times, but it cannot be modified (that is, no DML operations can be performed). ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The ORACLE_LOADER and ORACLE_DATAPUMP access drivers support similar functionality in the SQL*Loader and Data Pump utilities. The metadata for an external table created with either access driver is stored inside the database, but in the case of the ORACLE_DATAPUMP driver, it's also stored as XML in the external dump file itself. You cannot create any type of index on an external table defined with either access driver.
equijoin - notes
An equijoin joins two tables by a column that contains a matching value. Several methods exist for performing equijoins. In this situation, you could use one of two statements: -a statement that implements a natural join -a statement that implements a simple join containing a USING clause with no table alias SELECT product_name, supplier_name FROM product NATURAL JOIN supplier ORDER BY product_name; SELECT product_name, supplier_name FROM product JOIN supplier USING (supplier_id) ORDER BY product_name;
indexes - notes
An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ A non-deferrable PRIMARY KEY or UNIQUE constraint in a table automatically attempts to create a unique index. When a table is dropped, the corresponding indexes are automatically dropped. For each DML operation performed, the corresponding indexes are automatically updated. FALSE: Indexes should be created on columns that are frequently referenced as part of any expression. ------------------------------------------------------------- Q: Which of these would you do to make the optimizer never use an index when accessing data by default? A: When creating the index, use the INVISIBLE keyword. When an index already exists, issue an ALTER INDEX command, and use the INVISIBLE keyword. ------------------------------------------------------------- An index may improve the performance of an UPDATE statement that uses a WHERE clause, if the WHERE clause performs an equality comparison on an indexed column in a table An index can potentially speed up the WHERE clause of any DML statement, including the UPDATE statement. Comparisons of equality are ideal. An index is more likely to be used by the optimizer if it is based on a column with high selectivity (a large variation in values) ------------------------------------------------------ With CREATE TABLE: UNIQUE constraint - an index is automatically created to support it. PRIMARY KEY - is the combination of the NOT NULL and UNIQUE constraints, and therefore automatically also gets its own index to support it. FOREIGN KEYs do not automatically result in the creation of an index, although you may choose to create one. CHECK constraints do not automatically result in the creation of an index.
interval literals
An interval literal specifies a period of time. You can specify these differences in terms of years and months, or in terms of days, hours, minutes, and seconds. Oracle Database supports two types of interval literals, YEAR TO MONTH and DAY TO SECOND. Each type contains a leading field and may contain a trailing field. The leading field defines the basic unit of date or time being measured. The trailing field defines the smallest increment of the basic unit being considered. For example, a YEAR TO MONTH interval considers an interval of years to the nearest month. A DAY TO MINUTE interval considers an interval of days to the nearest minute. If you have date data in numeric form, then you can use the NUMTOYMINTERVAL or NUMTODSINTERVAL conversion function to convert the numeric data into interval values.
analytic (AKA window) functions - what is the point?
Analytic functions extend the power of SQL, enabling you carry out rather complex analysis of your data alongside your plain queries. There is nothing that analytic functions do that you could not achieve with complex self-joins and subqueries or PL/SQL. But you'll find that often with a single line of an analytic function you can save yourself a hundred lines of code and a migraine. Aggregate functions - like MAX, AVG, COUNT, SUM and the ilk - are great for presenting a summary view of your data in a single row or, if paired with a GROUP BY clause, in a relatively small number of records. But if all we had was aggregate functions we would constantly be forced to make a choice: view the raw data, or view the aggregated summaries? Analytic functions, on the other hand, allow us to have our cake and eat it too. Using analytic functions we can, over multiple rows, view our data and pair it with a running analysis. To use a sports analogy, the difference between analytic functions and aggregate functions is the difference between watching the game or simply reading the match report. Here's an ordinary, everyday aggregate function that tells us the average salary at our small company: SELECT AVG(sal) FROM emp; AVG(SAL) 2073.21 However, if we asked an aggregate function to show us how the average salary has changed over the years with each new member of staff it would probably burst into tears and run off to suck its thumb in the corner. We need an analytic function for that. SELECT ename, job, hiredate, sal, AVG(sal) OVER (ORDER BY hiredate) AS running_average FROM emp ORDER BY hiredate; Name JOB HIREDATE SALARY RUNNING_AVERAGE SMITH CLERK 12/17/1980 800 800 ALLEN SALESMAN 2/20/1981 1600 1200 WARD SALESMAN 2/22/1981 1250 1216.67 JONES MANAGER 4/2/1981 2975 1656.25 BLAKE MANAGER 5/1/1981 2850 1895 CLARK MANAGER 6/9/1981 2450 1987.5 TURNER SALESMAN 9/8/1981 1500 1917.86 MARTIN SALESMAN 9/28/1981 1250 1834.38 KING PRESIDENT 11/17/1981 5000 2186.11 FORD ANALYST 12/3/1981 3000 2147.73 JAMES CLERK 12/3/1981 950 2147.73 MILLER CLERK 1/23/1982 1300 2077.08 SCOTT ANALYST 12/9/1982 3000 2148.08 ADAMS CLERK 1/12/1983 1100 2073.21 source: https://www.red-gate.com/simple-talk/sql/oracle/introduction-to-analytic-functions-part-1-2/
datatype and related limits
BFILE Maximum size: 4 GB BLOB Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) Number of LOB columns per table Limited only by the maximum number of columns per table (that is, 1000, well, in theory). CHAR Maximum size: 2000 bytes CHAR VARYING Maximum size: 4000 bytes CLOB Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) Literals (characters or numbers in SQL or PL/SQL) Maximum size: 4000 characters LONG Maximum size: 2 GB - 1 Number of LONG columns per table Only one NCHAR Maximum size: 2000 bytes NCHAR VARYING / NVARCHAR2 Maximum size: 4000 bytes NCLOB Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) NUMBER 999...(38 9's) x10-125 maximum value, -999...(38 9's) x10-125 minimum value; can be represented to full 38-digit precision (the mantissa) Precision 38 significant digits RAW Maximum size: 2000 bytes VARCHAR Maximum size: 4000 bytes VARCHAR2 Maximum size: 4000 bytes
Oracle binary datatypes
BLOB, BFILE, RAW, LONG RAW
Global Temporary Tables
Basically, A temporary table is a table that holds data only for the duration of a session or transaction. Oracle introduced the global temporary table concept in version 8i. Unlike temporary tables from other database products such as MySQL and SQL Server, global temporary tables in Oracle are permanent database objects that store data on disk and visible to all sessions. However, the data stored in the global temporary table is private to the session. In other words, each session can only access its own data in the global temporary table. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To create a global temporary table, you use the CREATE GLOBAL TEMPORARY TABLE statement as follows: CREATE GLOBAL TEMPORARY TABLE table_name ( column_definition, ..., table_constraints ) ON COMMIT [DELETE ROWS | PRESERVE ROWS]; The ON COMMIT clause specifies whether data in the table is transaction-specific or session-specific: The ON COMMIT DELETE ROWS clause specifies that the global temporary table is transaction-specific. It means that Oracle truncates the table (remove all rows) after each commit. The ON COMMIT PRESERVE ROWS clause specifies that the global temporary table is session-specific, meaning that Oracle truncates the table when you terminate the session, not when you commit a transaction. Oracle uses the ON COMMIT DELETE ROWS option by default if you omit the ON COMMIT clause.
external tables - overview and notes
By default, the access driver of an external table is ORACLE_LOADER, which implies that data can be loaded from the external table into an existing table in the database. If you want to write data to the external table, you need to specify the access driver as ORACLE_DATAPUMP. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The external tables feature is a complement to existing SQL*Loader functionality. It enables you to access data in external sources as if it were in a table in the database. Prior to Oracle Database 10g, external tables were read-only. However, as of Oracle Database 10g, external tables can also be written to. Note that SQL*Loader may be the better choice in data loading situations that require additional indexing of the staging table External tables are created using the CREATE TABLE... ORGANIZATION EXTERNAL statement. Once created, external tables act in most ways like an internal table. There is no special syntax when querying them via a SELECT statement. It's not possible to create indexes on them and every query against them effectively performs a full-table scan, so performance can be an issue with large files. Even when performing a specific query against what would normally be considered a 'primary key' field, Oracle must scan every single row in the file before the query is complete. Because the files making up the external table are not really part of the database, transferring them between databases is easy. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables. No user in the database can access an external table unless the Oracle OS user can read and, in the case of the ORACLE_DATAPUMP driver, also write to the file in the Oracle directory object's location. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~` Example: CREATE TABLE emp_load (employee_number CHAR(5), employee_dob CHAR(20), employee_last_name CHAR(20), employee_first_name CHAR(15), employee_middle_name CHAR(15), employee_hire_date DATE) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY def_dir1 ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS (employee_number CHAR(2), employee_dob CHAR(20), employee_last_name CHAR(18), employee_first_name CHAR(11), employee_middle_name CHAR(11), employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy" ) ) LOCATION ('info.dat') );
Oracle character datatypes
CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2, LONG, CLOB, NCLOB CHAR max size 2 kB VARCHAR, VARCHAR2 max size 4 kB CLOB max size (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)
longer list of datetime format model elements
CC Century SCC Century BC prefixed with - YYYY Year with 4 numbers SYYY Year BC prefixed with - IYYY ISO Year with 4 numbers YY Year with 2 numbers RR Year with 2 numbers with Y2k compatibility YEAR Year in characters SYEAR Year in characters, BC prefixed with - BC / AD BC/AD Indicator * Q Quarter in numbers (1,2,3,4) MM Month of year 01, 02...12 MONTH Month in characters (i.e. January) MON JAN, FEB WW Weeknumber (i.e. 2) W Weeknumber of the month (i.e. 3) IW Weeknumber of the year in ISO standard. DDD Day of year in numbers (i.e. 234) DD Day of the month in numbers (i.e. 28) D Day of week in numbers(i.e. 7) DAY Day of the week in characters (i.e. Monday DY Day of the week in short character description (i.e. SUN) J Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle) HH Hour number of the day (1-12) HH12 Hour number of the day (1-12) HH24 Hour number of the day with 24Hours notation (1-24) AM AM or PM PM AM or PM MI Number of minutes (i.e. 59) SS Number of seconds (i.e. 59) SSSS Number of seconds this day. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The RR date format automatically calculates century from the SYSDATE function but allows the session user to enter the century.
add comment to data dictionary about column of table
COMMENT ON COLUMN MARINA.PIER IS 'Number of piers'; The correct syntax is to use the keywords COMMENT ON COLUMN, followed by the table name and column name, separated by a period, and the keyword IS, followed by the string.
Create Temporary Table:
CREATE GLOBAL TEMPORARY TABLE tname (colname data_type) ON COMMIT DELETE ROWS;
Create Index:
CREATE INDEX indexname ON tablename (cols);
CREATE TABLE syntax
CREATE TABLE tablename ( column_name data_type );
Create Table With Constraints:
CREATE TABLE tablename ( column_name data_type NOT NULL, CONSTRAINT pkname PRIMARY KEY (col), CONSTRAINT fkname FOREIGN KEY (col) REFERENCES other_table(col_in_other_table), CONSTRAINT ucname UNIQUE (col), CONSTRAINT ckname CHECK (conditions) );
CURRENT_DATE
CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE. Examples The following example illustrates that CURRENT_DATE is sensitive to the session time zone: ALTER SESSION SET TIME_ZONE = '-5:0'; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; SESSIONTIMEZONE CURRENT_DATE --------------- -------------------- -05:00 29-MAY-2000 13:14:03 ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; SESSIONTIMEZONE CURRENT_DATE --------------- -------------------- -08:00 29-MAY-2000 10:14:33 Be aware of the difference between CURRENT_DATE and SYSDATE
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE. The time zone offset reflects the current local time of the SQL session. If you omit precision, then the default is 6. The difference between this function and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value. syntax: CURRENT_TIMESTAMP [ (precision) ] In the optional argument, precision specifies the fractional second precision of the time value returned. Examples The following example illustrates that CURRENT_TIMESTAMP is sensitive to the session time zone: ALTER SESSION SET TIME_ZONE = '-5:0'; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SESSIONTIMEZONE CURRENT_TIMESTAMP --------------- ------------------------------------------ -05:00 04-APR-00 01.17.56.917550 PM -05:00 ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SESSIONTIMEZONE CURRENT_TIMESTAMP --------------- --------------------------------------- -08:00 04-APR-00 10.18.21.366065 AM -08:00 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If you use the CURRENT_TIMESTAMP with a format mask, take care that the format mask matches the value returned by the function. For example, consider the following table: CREATE TABLE current_test (col1 TIMESTAMP WITH TIME ZONE); The following statement fails because the mask does not include the TIME ZONE portion of the type returned by the function: INSERT INTO current_test VALUES (TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM')); The following statement uses the correct format mask to match the return type of CURRENT_TIMESTAMP: INSERT INTO current_test VALUES (TO_TIMESTAMP_TZ (CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM'));
alias - notes
Can be referenced in ORDER BY but nowhere else. It cant be used in WHERE, HAVING, or GROUP BY.
CHECK constraint - notes
Cannot be used with the CHECK constraint: i) References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudo columns ii) Calls to SYSDATE, UID, USER, and USERENV functions iii) Queries that refer to other values in other rows
Single-row functions: Character Functions Returning Character Values
Character functions that return character values return values of the following data types unless otherwise documented: If the input argument is CHAR or VARCHAR2, then the value returned is VARCHAR2. If the input argument is NCHAR or NVARCHAR2, then the value returned is NVARCHAR2. The length of the value returned by the function is limited by the maximum length of the data type returned. For functions that return CHAR or VARCHAR2, if the length of the return value exceeds the limit, then Oracle Database truncates it and returns the result without an error message. For functions that return CLOB values, if the length of the return values exceeds the limit, then Oracle raises an error and returns no data. The character functions that return character values are: CHR CONCAT INITCAP LOWER LPAD LTRIM NCHR NLS_INITCAP NLS_LOWER NLS_UPPER NLSSORT REGEXP_REPLACE REGEXP_SUBSTR REPLACE RPAD RTRIM SOUNDEX SUBSTR TRANSLATE TRANSLATE ... USING TRIM UPPER
single-row functions: character functions returning number values
Character functions that return number values can take as their argument any character data type. The character functions that return number values are: ASCII INSTR LENGTH REGEXP_COUNT REGEXP_INSTR
TO_CHAR
Converts a date or number to a string. **side note: passing a string to to_char, even though why would you do this, does not cause an error; it is okay: to_char('TRUE') is a valid statement. It will return 'TRUE'
TO_TIMESTAMP_TZ
Converts a string to a TIMESTAMP WITH TIME ZONE type.
TO_DATE
Converts a string to a date value.
TO_TIMESTAMP
Converts a string to a timestamp value.
TO_DSINTERVAL
Converts a string to an INTERVAL DAY TO SECOND type.
TO_YMINTERVAL
Converts a string to an INTERVAL YEAR TO MONTH type. TO_YMINTERVAL ( ' { [+|-] years - months | ym_iso_format } ' ) TO_YMINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type. TO_YMINTERVAL accepts argument in one of the two formats: SQL interval format compatible with the SQL standard (ISO/IEC 9075:2003) ISO duration format compatible with the ISO 8601:2004 standard In the SQL format, years is an integer between 0 and 999999999, and months is an integer between 0 and 11. Additional blanks are allowed between format elements. Examples The following example calculates for each employee in the sample hr.employees table a date one year two months after the hire date: SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') "14 months" FROM employees; HIRE_DATE 14 months --------- --------- 17-JUN-87 17-AUG-88 21-SEP-89 21-NOV-90 13-JAN-93 13-MAR-94 03-JAN-90 03-MAR-91 21-MAY-91 21-JUL-92 . . .
TO_NUMBER
Converts a string value to a number. TO_NUMBER converts expr to a value of NUMBER data type. The expr can be a number value of CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY_FLOAT, or BINARY_DOUBLE data type. If you specify an expr of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type, then you can optionally specify the format model fmt. If you specify an expr of BINARY_FLOAT or BINARY_DOUBLE data type, then you cannot specify a format model because a BINARY_FLOAT or BINARY_DOUBLE can be interpreted only by its internal representation. **side note: passing a number to to_number, even though why would you do this, does not cause an error; it is okay: to_number(1) is a valid statement. It will return 1.
CAST
Converts one data type to another. It allows for more functionality than the TO_NUMBER, TO_CHAR and TO_DATE functions, as it allows you to specify the actual data types you want to convert to, rather than just use the defaults of those functions. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ syntax: CAST({ expr | MULTISET (subquery) } AS type_name) CAST converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value. CAST lets you convert built-in datatypes or collection-typed values of one type into another built-in datatype or collection type. You can cast an unnamed operand (such as a date or the result set of a subquery) or a named collection (such as a varray or a nested table) into a type-compatible datatype or named collection. The type_name must be the name of a built-in datatype or collection type and the operand must be a built-in datatype or must evaluate to a collection value. For the operand, expr can be either a built-in datatype, a collection type, or an instance of an ANYDATA type. If expr is an instance of an ANYDATA type, CAST will try to extract the value of the ANYDATA instance and return it if it matches the cast target type, otherwise, null will be returned. MULTISET informs Oracle Database to take the result set of the subquery and return a collection value. Table 5-1 shows which built-in datatypes can be cast into which other built-in datatypes. (CAST does not support LONG, LONG RAW, or the Oracle-supplied types.) CAST does not directly support any of the LOB datatypes. When you use CAST to convert a CLOB value into a character datatype or a BLOB value into the RAW datatype, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target datatype. If the resulting value is larger than the target type, then the database returns an error. When you use CAST ... MULTISET to get a collection value, each select list item in the query passed to the CAST function is converted to the corresponding attribute type of the target collection element type. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ CAST does not support any of the LOB data types. If you try to CAST a CLOB into a character data type, for example, the LOB value is converted to a character value and then converted to the target data type. If the resulting value is larger than the target type, then you'll get an error. There are two main ways you can run this query. You can run it using a single result data type, which would be: CAST( expr AS type_name ) Or, you can run it using a subquery, which would be: CAST( MULTISET (subquery) AS type_name ) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Example 1 - Oracle CAST String to DATE This example shows how to CAST from a string value that contains a date, to a date data type. SELECT '30-APRIL-2015', CAST('30-APRIL-2015' AS DATE) AS OUTPUT_VALUE FROM dual; '30-Apr-15′ OUTPUT_VALUE 30-Apr-15 30/APR/15 Notice how the date in the output value is displayed in the date format for Oracle. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Example 2 - Oracle CAST NUMBER to String This example shows how to cast a number to a CHAR data type. SELECT 41.522, CAST(41.522 AS CHAR(10)) AS OUTPUT_VALUE FROM dual; 41.522 OUTPUT_VALUE 41.522 41.522 It can be hard to see in this example but the alignment is different in the SQL Developer tool, which is done for different data types. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Example 3 - Oracle CAST VARCHAR to Number This example shows how to convert a VARCHAR to a NUMBER. SELECT ' 2093 ', CAST(' 2093 ' AS NUMBER) AS OUTPUT_VALUE FROM dual; '2093' OUTPUT_VALUE 2093 2093 I've put spaces around the first number value, which have been removed when it has converted to a NUMBER data type. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Example 4 - Oracle CAST DATE to TIMESTAMP This example shows how to cast a DATE value to a TIMESTAMP value. For this example, I've used the CUSTOMERS table shown above. SELECT start_date, CAST(start_date AS TIMESTAMP) AS OUTPUT_VALUE FROM customers; START_DATE OUTPUT_VALUE 12/APR/10 12/APR/10 12:00:00.000000000 AM 04/JUL/11 04/JUL/11 12:00:00.000000000 AM 21/MAR/09 21/MAR/09 12:00:00.000000000 AM 01/FEB/01 01/FEB/01 12:00:00.000000000 AM (null) (null) 10/FEB/12 10/FEB/12 12:00:00.000000000 AM 16/OCT/12 16/OCT/12 12:00:00.000000000 AM 16/OCT/12 16/OCT/12 12:00:00.000000000 AM Each of the DATE value has been converted to a TIMESTAMP which includes hours, minutes, seconds, and fractional seconds. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Example 5 - Oracle Cast NUMBER to VARCHAR2 This example shows how to convert a NUMBER value to a VARCHAR2 data type. SELECT 9834, CAST(9834 AS VARCHAR2(30)) AS OUTPUT_VALUE FROM dual; 9834 OUTPUT_VALUE 9834 9834 This is similar to Example 2. It can be hard to see in this example but the alignment is different in the SQL Developer tool, which is done for different data types.
NUMTODSINTERVAL
Converts the specified number to an INTERVAL DAY TO SECOND value. syntax: NUMTODSINTERVAL(n, 'interval_unit') NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal. The argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value. The argument interval_unit can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype. The value for interval_unit specifies the unit of n and must resolve to one of the following string values: 'DAY' 'HOUR' 'MINUTE' 'SECOND' interval_unit is case insensitive. Leading and trailing values within the parentheses are ignored. By default, the precision of the return is 9. example: select numtodsinterval(150, 'DAY') from dual; result: NUMTODSINTERVAL(150,'DAY') ------------------------------------- +000000150 00:00:00.000000000
NUMTOYMINTERVAL
Converts the specified number to an INTERVAL YEAR TO MONTH value. NUMTOYMINTERVAL(n, 'interval_unit') NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. The argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value. The argument interval_unit can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype. The value for interval_unit specifies the unit of n and must resolve to one of the following string values: 'YEAR' 'MONTH' interval_unit is case insensitive. Leading and trailing values within the parentheses are ignored. By default, the precision of the return is 9. Example: SELECT NUMTOYMINTERVAL(15, 'YEAR') AS YMTEST FROM dual; Result: YMTEST +15-00 This example uses an input value and an interval type of "year", which is negative. SELECT NUMTOYMINTERVAL(-4, 'YEAR') AS YMTEST FROM dual; Result: YMTEST -04-00
INTERSECT set operator - notes
Does NOT ignore NULL values The number of columns and data types must be identical for all SELECT statements in the query. INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates. The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.
Oracle date datatypes
DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
useful views on non-schema objects
DBA_USERS - information about all users of the database DICTIONARY - description of data dictionary tables and views DICT_COLUMNS - description of columns in data dictionary tables and views GLOBAL_NAME - displays the global database name NLS_DATABASE_PARAMETERS - permanent NLS parameters of the database SESSION_PRIVS - displays all system privileges available in the current session
DBTIMEZONE
DBTIMEZONE returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement. The following example assumes that the database time zone is set to UTC time zone: SELECT DBTIMEZONE FROM DUAL; DBTIME ------ +00:00 Time zone is set during database creation or using CREATE DATABASE. DBTIMEZONE and SESSIONTIMEZONE are different in their operational scope. DBTIMEZONE shows the database time zone, while SESSIONTIMEZONE shows it for the session. It implies if the time zone is altered at session level, only SESSIONTIMEZONE will change and not the DBTIMEZONE.
DELETE:
DELETE FROM tablename WHERE condition;
SELECT Keywords
DISTINCT: Removes duplicate results BETWEEN: Matches a value between two other values (inclusive) IN: Matches a value to one of many values LIKE: Performs partial/wildcard matches
Drop Index:
DROP INDEX indexname;
DROP TABLE ... PURGE
DROP TABLE tablename PURGE; -It is not possible to recover the table structure, data, or the related indexes.
DROP TABLE
DROP TABLE tablename; The table is moved to the recycle bin All indexes and constraints defined on the table being dropped are also dropped. All views and synonyms on the table remain but they are invalidated. The table cannot be recovered with ROLLBACK.
DCL facts
Data Control Language (DCL) DCL is the language that enables or disables a user's ability to perform tasks within Oracle. DCL commands: GRANT REVOKE ****cause automatic commits (DCL considered DDL in this aspect)**** A user account without privileges has no functionality. The user cannot even log on to Oracle. This requires a privilege called CREATE SESSION. There are two types of privileges: 1. System privileges: Allow a user to perform DDL commands (e.g., CREATE TABLE, DROP INDEX) 2. Object privileges: Allow a user to issue DML commands (e.g., INSERT, UPDATE)
DDL facts
Data definition language (DDL) statements let you perform these tasks: Create, alter, and drop schema objects Grant and revoke privileges and roles Analyze information on a table, index, or cluster Establish auditing options Add comments to the data dictionary Oracle Database implicitly commits the current transaction before and after every DDL statement. DDL statements: ALTER ... (All statements beginning with ALTER, except ALTER SESSION and ALTER SYSTEM) COMMENT CREATE ... (All stmts beginning w CREATE) DROP ... (All stmts beginning w DROP) FLASHBACK ... (All stmts beginning w FLASHBACK) GRANT PURGE RENAME REVOKE TRUNCATE
DML facts
Data manipulation language (DML) statements access and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction. DML statements: DELETE INSERT MERGE SELECT UPDATE
V$TIMEZONE_NAMES
Database Time zones can be queried from V$TIMEZONE_NAMES dictionary view.
Implicit Data Type Conversion
Database automatically converts a value from one datatype to another when such a conversion makes sense. A VARCHAR2 or CHAR value can be implicitly converted to NUMBER or DATE type value by Oracle. Similarly, a NUMBER or DATA type value can be automatically converted to character data by Oracle server. Note that the impicit interconversion happens only when the character represents the a valid number or date type value respectively. For example, examine the below SELECT queries. Both the queries will give the same result because Oracle internally treats 15000 and '15000' as same. Query-1 SELECT employee_id,first_name,salary FROM employees WHERE salary > 15000; Query-2 SELECT employee_id,first_name,salary FROM employees WHERE salary > '15000'; Implicit Data Conversion Examples Text Literal Example: The text literal '10' has datatype CHAR. Oracle implicitly converts it to the NUMBER datatype if it appears in a numeric expression as in the following statement: SELECT salary + '10' FROM employees; Character and Number Values Example: When a condition compares a character value and a NUMBER value, implicitly converts the character value to a NUMBER value, rather than converting the NUMBER value to a character value. In the following statement, Oracle implicitly converts '200' to 200: SELECT last_name FROM employees WHERE employee_id = '200'; Date Example: In the following statement, implicitly converts '03-MAR-97' to a DATE value using the default date format 'DD-MON-YY': SELECT last_name FROM employees WHERE hire_date = '03-MAR-97'; Rowid Example: In the following statement,implicitly converts the text literal 'AAAGH6AADAAAAFGAAN' to a rowid value. (Rowids are unique within a database, so to use this example you must know an actual rowid in your database.) SELECT last_name FROM employees WHERE ROWID = 'AAAGH6AADAAAAFGAAN';
single-row functions: datetime functions
Datetime functions operate on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values. Some of the datetime functions were designed for the Oracle DATE data type (ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY). If you provide a timestamp value as their argument, then Oracle Database internally converts the input type to a DATE value and returns a DATE value. The exceptions are the MONTHS_BETWEEN function, which returns a number, and the ROUND and TRUNC functions, which do not accept timestamp or interval values at all. The remaining datetime functions were designed to accept any of the three types of data (date, timestamp, and interval) and to return a value of one of these types. All of the datetime functions that return current system datetime information, such as SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP, and so forth, are evaluated once for each SQL statement, regardless how many times they are referenced in that statement. The datetime functions are: ADD_MONTHS CURRENT_DATE CURRENT_TIMESTAMP DBTIMEZONE EXTRACT (datetime) FROM_TZ LAST_DAY LOCALTIMESTAMP MONTHS_BETWEEN NEW_TIME NEXT_DAY NUMTODSINTERVAL NUMTOYMINTERVAL ORA_DST_AFFECTED ORA_DST_CONVERT ORA_DST_ERROR ROUND (date) SESSIONTIMEZONE SYS_EXTRACT_UTC SYSDATE SYSTIMESTAMP TO_CHAR (datetime) TO_DSINTERVAL TO_TIMESTAMP TO_TIMESTAMP_TZ TO_YMINTERVAL TRUNC (date) TZ_OFFSET
EXTRACT
EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation. When you extract any of the other values, the value returned is in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view.
types of joins in Oracle SQL
Equijoins (aka inner joins, aka simple joins) Non-Equijoins Self-Joins Cross Join (aka Cartesian Product) Outer Joins: Left Outer Join (aka left join), Right Outer Join (aka right join), Full Outer Join (aka full join) Natural Join Antijoins Semijoins
CREATE TABLE...ORGANIZATION EXTERNAL
External tables are created using the SQL CREATE TABLE...ORGANIZATION EXTERNAL statement. To create an external table, you must know the file format and record format of the data source that will be used for the table. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ When you create an external table, you specify the following attributes: TYPE - specifies the type of external table. The two available types are the ORACLE_LOADER type and the ORACLE_DATAPUMP type. Each type of external table is supported by its own access driver. The ORACLE_LOADER access driver is the default. It cannot cannot write to the file, only read, and the data must come from a text file. It loads data from external tables to internal tables. The data must come from text data files. (The ORACLE_LOADER access driver cannot perform unloads; that is, it cannot move data from an internal table to an external table.) The ORACLE_DATAPUMP access driver can read from and write to external binary dump files. The ORACLE_DATAPUMP access driver can perform both loads and unloads. The data must come from binary dump files. Loads to internal tables from external tables are done by fetching from the binary dump files. Unloads from internal tables to external tables are done by populating the binary dump files of the external table. The ORACLE_DATAPUMP access driver can write dump files only as part of creating an external table with the SQL CREATE TABLE AS SELECT statement. Once the dump file is created, it can be read any number of times, but it cannot be modified (that is, no DML operations can be performed). DEFAULT DIRECTORY - specifies the default directory to use for all input and output files that do not explicitly name a directory object. The location is specified with a directory object, not a directory path. You must create the directory object before you create the external table; otherwise, an error is generated. See "Location of Data Files and Output Files" for more information. ACCESS PARAMETERS - describe the external data source and implement the type of external table that was specified. Each type of external table has its own access driver that provides access parameters unique to that type of external table. Access parameters are optional. See "Access Parameters". LOCATION - specifies the data files for the external table. The files are named in the form directory:file. The directory portion is optional. If it is missing, then the default directory is used as the directory for the file. You can use wildcards in the file name: an asterisk (*) signifies multiple characters, a question mark (?) signifies a single character. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Example: CREATE TABLE emp_load (employee_number CHAR(5), employee_dob CHAR(20), employee_last_name CHAR(20), employee_first_name CHAR(15), employee_middle_name CHAR(15), employee_hire_date DATE) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY def_dir1 ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS (employee_number CHAR(2), employee_dob CHAR(20), employee_last_name CHAR(18), employee_first_name CHAR(11), employee_middle_name CHAR(11), employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy" ) ) LOCATION ('info.dat') );
SYS_EXTRACT_UTC
Extracts the UTC from a datetime value with a timezone offset.
VARIANCE
Returns the variance of the specified expression. VARIANCE ( [ DISTINCT | ALL ] expression) [ OVER ( analytic_clause ) ]
AVG
Finds the average of all the values provided. There are two ways you can run the AVG function - as an aggregate function, or an analytic function. The syntax of AVG as an aggregate function is: AVG ( [DISTINCT/ALL] expr ) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Example 1 - Simple Example This is a simple example using a number field. SELECT AVG(fees_paid) AS avg_fees_paid FROM student; AVG_FEES_PAID 190 Example 2 - AVG Date This example uses a few functions to find an average of a date field. SELECT TO_DATE(ROUND(AVG(TO_NUMBER(TO_CHAR(enrolment_date, 'J')))), 'J') AS avg_date FROM student; **that J up there is a format mask and the J stands for Julian day*** AVG_DATE 07-Feb-15 Example 3 - GROUP BY This example uses a GROUP BY clause to group the averages by the gender field. SELECT gender, AVG(fees_paid) AS avg_fees_paid FROM student GROUP BY gender; GENDER AVG_FEES_PAID M 235 F 100 Example 4 - HAVING This example uses the HAVING clause to restrict the rows returned after the GROUP BY has been applied. SELECT gender, AVG(fees_paid) AS avg_fees_paid FROM student GROUP BY gender HAVING AVG(fees_paid) > 200; GENDER AVG_FEES_PAID M 235
MAX
Finds the highest value of all the values provided. The syntax of Oracle MAX as an aggregate function is: MAX ( [DISTINCT/ALL] expr) Example 1 This example shows a simple use of the MAX on a number field. SELECT MAX(fees_paid) AS MAX_TEST FROM student; MAX_TEST 410 The maximum value in this set is 410. Example 2 This example shows how a MAX function would work on a text column (a VARCHAR). SELECT MAX(first_name) AS MAX_TEST FROM student; MAX_TEST Tom The result is Tom. MAX on a VARCHAR column will find the last value as if they were sorted alphabetically. Example 3 This example performs a MAX on a date field. SELECT MAX(enrolment_date) AS MAX_TEST FROM student; MAX_TEST 09/03/2015 The date of March 9 is shown as that is the last date in the column. It works similar to using a number field, where it finds the latest or last date in the supplied data. Example 4 This example uses the GROUP BY clause with MAX, just like with any other aggregate function. SELECT TO_CHAR(enrolment_date, 'MON') AS ENROLMENT_MONTH, MAX(fees_paid) as MAX_FEES FROM student GROUP BY TO_CHAR(enrolment_date, 'MON'); ENROLMENT_MONTH MAX_FEES MAR 400 FEB 100 JAN 410 This shows the months of enrolment, along with the MAX of fees paid.
MIN
Finds the lowest value of all the values provided. see MAX
MONTHS_BETWEEN
Finds the number of months between the two specified dates. MONTHS_BETWEEN (date1, date2) Remember: if higher number on left -> positive result value; if smaller number on left -> negative result value (just like math!) MONTHS_BETWEEN returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.
SUM
Finds the sum total of all the values provided. The syntax of Oracle SUM as an aggregate function is: SUM ( [DISTINCT/ALL] expr) The syntax of Oracle SUM as an aggregate function is: SUM ( [DISTINCT/ALL] expr) OVER (analytic_clause) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Example 1 - Simple SUM This example shows a simple SUM function on the entire table SELECT SUM(fees_paid) AS SUM_FEES_PAID FROM student; SUM_FEES_PAID 1710 This is the total of all fees_paid values in the table. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Example 2 - SUM with WHERE This example uses the WHERE clause to only shoe the SUM of fees_paid where it is not equal to fees_required. SELECT SUM(fees_paid) AS SUM_FEES_PAID FROM student WHERE fees_paid <> fees_required; SUM_FEES_PAID 1410 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Example 3 - SUM with GROUP BY This example shows how to use the SUM function with GROUP BY. SELECT EXTRACT(MONTH FROM enrolment_date) AS ENROLMENT_MONTH, SUM(fees_paid) AS SUM_FEES_PAID FROM student GROUP BY EXTRACT(MONTH FROM enrolment_date); ENROLMENT_MONTH SUM_FEES_PAID 1 810 2 100 3 800 It uses the EXTRACT function to get the enrolment month, and then group by this value. It finds the SUM of fees_paid for each enrolment month. Example 4 - SUM with a Formula This example uses a formula within the SUM function to find out how much tax is needed, based on a 10% tax rate. SELECT SUM(fees_paid) AS SUM_FEES_PAID, SUM(fees_paid * 0.1) AS SUM_TAX FROM student; SUM_FEES_PAID SUM_TAX 1710 171 Example 5 - SUM with Multiple Columns This example uses the SUM function on multiple columns SELECT SUM(fees_required - fees_paid) AS FEES_OWING FROM student; FEES_OWING 1050 Example 7 - SUM DISTINCT This example shows how you can use the SUM function with DISTINCT. SELECT SUM(fees_paid) AS SUM_ALL_FEES, SUM(DISTINCT fees_paid) AS SUM_DISTINCT_FEES FROM student; SUM_ALL_FEES SUM_DISTINCT_FEES 1710 1460 The results show the SUM function with and without the DISTINCT keyword.
SQL logical order of execution / operations / processing
For FROM Johnny JOIN Would WHERE Grow GROUP BY Having HAVING Selected SELECT Dinner DISTINCT Over ORDER BY Fun FETCH (LIMIT) Tips & Cautions Aliases created in the SELECT list cannot be used by earlier steps. This restriction is imposed because the column value may not yet have been determined when the clauses that appear before the SELECT clause are evaluated (such as the WHERE clause). Expression aliases cannot be used by other expressions within the same SELECT list. This is because the logical order in which the expressions are evaluated does not matter and is not guaranteed. For example, this SELECT clause might not work as expected, and is therefore, not supported: SELECT a + 1 AS x, x + 1 AS y When using an INNER JOIN, it doesn't matter if you specify your logical expressions in the WHERE clause or the ON clause. This is true because there's no logical difference between the ON and WHERE (except for when using an OUTER JOIN or GROUP BY ALL option). The DISTINCT clause is redundant when GROUP BY is used. Therefore, it would not remove any rows from the recordset.
ROWNUM
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. You can use ROWNUM to limit the number of rows returned by a query, as in this example: SELECT * FROM employees WHERE ROWNUM < 10; If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, then Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example: SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;
number format model elements
Format element - Description , - Returns a comma in the specified position it is possible to specify multiple commas. . - Returns a period in the specified position. $ - Returns a leading dollar sign. 0 - Returns leading or trailing zeros (0). 9 - Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. B - Returns blanks for the integer part of a fixed point number when the integer part is zero. C - Returns in the specified position the ISO currency symbol. Default currency value is set in the NLS_ISO_CURRENCY parameter. D - Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period. EEEE - Returns a value in scientific notation. FM - Returns a value without blanks (leading and trailing). G - Returns in the specified position the group separator. The defoult value is set in the NLS_NUMERIC_CHARACTER parameter. You can specify multiple group separators in a number format model. L - Returns in the specified position the local currency symbol. Default currency value is set in the NLS_CURRENCY parameter. Format element for Oracle currency formatting. MI - Returns negative value with a trailing minus (-) sign. Returns positive value with a trailing blank. PR - Returns negative value in <>. Returns positive value with a leading and trailing blank. RN - Returns a value as Roman numerals in uppercase. Allowed value is an integer between 1 and 3999. rn - Returns a value as Roman numerals in lowercase. Allowed value is an integer between 1 and 3999. S - Returns value with a leading or trailing minus (-) or plus (+) sign. TM - The text minimum number format model returns the smallest number of characters possible. This element is case insensitive. U - Returns in the specified position the default currency symbol. The current value of the NLS_DUAL_CURRENCY parameter. V - Returns a value multiplied by 10n and if necessary, round it up. X -Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, Oracle Database will round it to an integer.
GV_$ and GV$ global dynamic performance view prefixes
GV_$ = for views GV$ = for public synonyms global dynamic performance views
things to know for exam - notes from other students
Global Temporary Tables. Alternative Quote Operator Working with CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP Understanding and Using Cartesian Products * Set operators and JOINs * Does the result have duplicated rows * How to treat NULL * What is restrictions(ex. Data types, which one can be implicitly converted?) * Where you can use ORDER BY(each table or subquery?) * How is it sorted * How to cross join with natural join. * Difference between ANSI and Oracle JOIN syntax. * Subqueries * Where you can and cannot use? * Difference between subquery and correlated subquery. * How correlated subquery works * Difference between subquery with EXISTS and query with EXISTS * Where you can use expressions * You need to remember all restrictions. * What you can use in ORDER BY? * Where you cannot use column alias? * Can you use aggregation functions in WHERE? * Functions * Can NVL/NVL2/COALEACE return/use different data type or NULL? * Can you recall all format for TO_CHAR, TO_NUMBER? * Can you tell situations that TO_CHAR or TO_NUMBER fail(or display)? * DUAL table * Who owns it * Who can use it * Managing Constraints * What constrains can be copied with CREATE AS? * What expression you can use for CHECK? (SYSDATE?, other columns?) * INDEX, SYSNONYMS, SEQUENCE * When those are deleted? * Who can use them? * Views * Can you use view with multitable INSERT/MERGE? * When you can or cannot UPDATE/INSERT underlying table? * TIMESTAMP/DATE * What does CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSDATE return? * What is difference all TIMESTAMP/DATE? --------------------------------------------- diff btw flashback and rollback --------------------------------------------- implicit data conversions, especially when a date does not match the current NLS Date Format set for the database i.e: Does '01-January-2019' implicitly convert to a date if the NLS format is DD-MM-RR? system privileges vs object privileges and what is a system privilege vs an object privilege. cascading privileges where can you use a HAVING clause vs. a WHERE clause. The NVL parameters must be of the same data type NVL2 requires 3 parameters You can only use % wildcard with LIKE not IN Understand your operator precedence and how that impacts the results of a where clause Understand the CURRENT_DATE and CURRENT_TIMESTAMP and how those differ from SYSDATE and SYSTIMESTAMP Understand the difference between the various data types: DATE TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE INTERVAL DAY TO SECOND INTERVAL YEAR TO MONTH
EXISTS vs IN
IN operator is less efficient than the EXISTS operator
methods for modifying data
INSERT INSERT From Table UPDATE DELETE TRUNCATE UPDATE with Join INSERT Multiple Rows
INSERT Multiple Rows:
INSERT INTO tablename (col1, col2) VALUES (valA1, valB1) INTO tablename (col1, col2) VALUES (valA2, valB2) SELECT * FROM dual;
INSERT From Table:
INSERT INTO tablename (col1, col2...) SELECT col1, col2...
INSERT:
INSERT INTO tablename (col1, col2...) VALUES (val1, val2);
INSERT vs INSERT INTO SELECT syntax
INSERT: INSERT INTO table_name (column_list) VALUES( value_list); INSERT INTO SELECT: INSERT INTO target_table (col1, col2, col3) SELECT col1, col2, col3 FROM source_table WHERE condition;
sequences - notes
In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To remove a sequence from database, use DROP SEQUENCE (!!NOT DELETE!!) CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement FALSE: When a database instance shuts down abnormally, the sequence numbers that have been cached but not used are available again when the instance is restarted. TRUE: You cannot use the .NEXTVAL sequence value as a DEFAULT value for a column CYCLE specifies whether the sequence will repeat a range once it reaches the end of the range. MAXVALUE specifies one end of the range. INCREMENT specifies the number by which the sequence will increment.
index organized table
In a DBMS, a type of table storage organization that stores end-user data and index data in consecutive locations in permanent storage. Also known as cluster-indexed table. CREATE TABLE employee_info ( emp_id NUMBER(5), dept_id NUMBER(5), designation VARCHAR(30), salary NUMBER(5), CONSTRAINT emp_pk PRIMARY KEY (emp_id)) ORGANIZATION INDEX INCLUDING dept_id OVERFLOW TABLESPACE employee_tablespace;
repeating group
In a relation, a characteristic describing a group of multiple entries of the same type for a single key attribute occurrence. For example, a car can have multiple colors for its top, interior, bottom, trim, and so on.
partial dependency
In normalization, a condition in which an attribute is dependent on only a portion (subset) of the primary key.
LOCALTIMESTAMP
LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP. The difference between this function and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value. syntax: LOCALTIMESTAMP [ (timestamp_precision) ] The optional argument timestamp_precision specifies the fractional second precision of the time value returned. This example illustrates the difference between LOCALTIMESTAMP and CURRENT_TIMESTAMP: ALTER SESSION SET TIME_ZONE = '-5:00'; SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; CURRENT_TIMESTAMP LOCALTIMESTAMP -------------------------------------------------------------- 04-APR-00 01.27.18.999220 PM -05:00 04-APR-00 01.27.19 PM ALTER SESSION SET TIME_ZONE = '-8:00'; SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; CURRENT_TIMESTAMP LOCALTIMESTAMP ----------------------------------- ------------------------- 04-APR-00 10.27.45.132474 AM -08:00 04-APR-00 10.27.451 AM ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If you use the LOCALTIMESTAMP with a format mask, take care that the format mask matches the value returned by the function. For example, consider the following table: CREATE TABLE local_test (col1 TIMESTAMP WITH LOCAL TIME ZONE); The following statement fails because the mask does not include the TIME ZONE portion of the return type of the function: INSERT INTO local_test VALUES (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF')); The following statement uses the correct format mask to match the return type of LOCALTIMESTAMP: INSERT INTO local_test VALUES (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
ESCAPE syntax (for use with LIKE operator)
Let's say you wanted to search for a % or a _ character in the Oracle LIKE condition. You can do this using an Escape character. Please note that you can only define an escape character as a single character (length of 1). For example: SELECT * FROM suppliers WHERE supplier_name LIKE 'Water!%' ESCAPE '!'; This Oracle LIKE condition example identifies the ! character as an escape character. This statement will return all suppliers whose name is Water%.
DECODE
Looks at one value, and returns different values depending on what it is checked against. Similar to CASE.. DECODE ( expression, search, result [, search, result]... [,default] ) DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null. The arguments can be any of the numeric types (NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or character types. If expr and search are character data, then Oracle compares them using nonpadded comparison semantics. expr, search, and result can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as the first result parameter. If the first search-result pair are numeric, then Oracle compares all search-result expressions and the first expr to determine the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype. The search, result, and default values can be derived from expressions. Oracle Database uses short-circuit evaluation. That is, the database evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle never evaluates a search if a previous search is equal to expr. Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2. In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null. The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255. Example This example decodes the value warehouse_id. If warehouse_id is 1, then the function returns 'Southlake'; if warehouse_id is 2, then it returns 'San Francisco'; and so forth. If warehouse_id is not 1, 2, 3, or 4, then the function returns 'Non domestic'. SELECT product_id, DECODE (warehouse_id, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non domestic') "Location of inventory" FROM inventories WHERE product_id < 1775; Another Example SELECT DECODE (CONCAT ('1', '2'), '12', '9', NULL) FROM DUAL; -> returns '9'
multiple-row operators
Multiple-row operators include the IN, ANY, and ALL operators.
constraints - syntax and notes
NOT NULL constraint cannot be created with out-of-line syntax foreign key constraint is created in the child table a constraint can be disabled even if the constraint column contains data a column with the UNIQUE constraint *can* contain nulls how to drop: ALTER TABLE DEMO DROP CONSTRAINT PK_DEMO; UNIQUE and PRIMARY KEY constraint require indexes. There is no index created to support the NOT NULL constraint.
NULLIF
NULLIF (expr1, expr2) NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1. (but you can for expr2, even though why would you do this) If both arguments are numeric datatypes, then Oracle Database determines the argument with the higher numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype. If the arguments are not numeric, then they must be of the same datatype, or Oracle returns an error. The NULLIF function is logically equivalent to the following CASE expression: CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END The following example selects those employees from the sample schema hr who have changed jobs since they were hired, as indicated by a job_id in the job_history table different from the current job_id in the employees table: SELECT e.last_name, NULLIF(j.job_id, e.job_id) "Old Job ID" FROM employees e, job_history j WHERE e.employee_id = j.employee_id ORDER BY last_name; LAST_NAME Old Job ID ------------------------- ---------- De Haan IT_PROG Hartstein MK_REP Kaufling ST_CLERK Kochhar AC_ACCOUNT Kochhar AC_MGR Raphaely ST_CLERK Taylor SA_MAN Taylor Whalen AC_ACCOUNT Whalen
Oracle numeric datatypes
NUMBER, BINARY_FLOAT, BINARY_DOUBLE, BOOLEAN, PLS_INTEGER, BINARY_INTEGER, INTEGER, FLOAT, DECIMAL
Single-row functions: Numeric
Numeric Functions Numeric functions accept numeric input and return numeric values. Most numeric functions return NUMBER values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits. The numeric functions are: ABS ACOS ASIN ATAN ATAN2 BITAND CEIL COS COSH EXP FLOOR LN LOG MOD NANVL POWER REMAINDER ROUND (number) SIGN SIN SINH SQRT TAN TANH TRUNC (number) WIDTH_BUCKET
numeric precedence
Numeric precedence determines, for operations that support numeric datatypes, the datatype Oracle uses if the arguments to the operation have different datatypes. BINARY_DOUBLE has the highest numeric precedence, followed by BINARY_FLOAT, and finally by NUMBER. Therefore, in any operation on multiple numeric values: "Busy Dogs Bite Fancy Nobles" binary double > binary float > number If any of the operands is BINARY_DOUBLE, then Oracle attempts to convert all the operands implicitly to BINARY_DOUBLE before performing the operation. If none of the operands is BINARY_DOUBLE but any of the operands is BINARY_FLOAT, then Oracle attempts to convert all the operands implicitly to BINARY_FLOAT before performing the operation. Otherwise, Oracle attempts to convert all the operands to NUMBER before performing the operation. If any implicit conversion is needed and fails, then the operation fails. Refer to Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion. In the context of other datatypes, numeric datatypes have lower precedence than the datetime/interval datatypes and higher precedence than character and all other datatypes. "Do Not Curse" datetime > number > character
row-limiting clause (OFFSET/FETCH)
OFFSET {NUMBER} {ROW|ROWS} FETCH {FIRST|NEXT} {<empty>|NUMBER [PERCENT]} {ROW|ROWS} {ONLY|WITH TIES} In 12c, retrieving Top-N query results for top/bottom rows simplified and become straight forward with the new FETCH FIRST | NEXT | PERCENT clauses. There are two flavors of pulling out the data from the result set. FETCH FIRST 10 ROWS ONLY - Retrieve fixed number of rows FETCH 5 PERCENT ONLY - Retrieve a percentage of rows from the result set If one needs to retrieve the data set after excluding certain number of rows from the top, OFFSET clause can be used. If more than one row in the set satisfy the fetch condition, retrieve all the rows obeying the boundaries using WITH TIES clause. The below SQL query fetches top-5 employees sorted by their salary, in the company SQL> SELECT employee_id, last_name FROM employees ORDER BY salary FETCH FIRST 5 ROWS ONLY; The below SQL query fetches top 5% employees sorted by their salary and includes the those with the same salary. SQL> SELECT employee_id, last_name, salary FROM employees ORDER BY salary FETCH FIRST 5 PERCENT ROWS WITH TIES; OFFSET: OFFSET is an optional clause, which tells Oracle how many rows should be skipped from the beginning of the result set. The number of the rows is required and must be followed by either of the keywords: ROW or ROWS. We may not specify a percentage of rows to be skipped. It must be a number of rows. The OFFSET part of the row limiting clause does not require the FETCH part to be present. If this is the case, all rows Starting with the row at OFFSET +1 position will be returned. Below will skip first three rows: SQL> SELECT * FROM employees OFFSET 3 ROW; Below query will skip the first three rows in an ordered set SQL> SELECT * FROM employees ORDER BY salary OFFSET 3 ROWS; More facts on Top - N queries The keywords ROW and ROWS can be used interchangeably, as can the FIRST and NEXT keywords. If the offset is not specified it is assumed to be 0. Negative values for the offset, rowcount or percent are treated as 0. Null values for offset, rowcount or percent result in no rows being returned. Fractional portions of offset, rowcount or percent are truncated. If the offset is greater than or equal to the total number of rows in the set, no rows are returned. If the rowcount or percent are greater than the total number of rows after the offset, all rows are returned. The row limiting clause cannot be used with the FOR UPDATE clause, CURRVAL and NEXTVAL sequence pseudocolumns or in an fast refresh materialized view.
precedence rules
Operations with higher precedence are applied first. Operators with the same precedence are applied in their text order: You can change the execution order by using parentheses. If the expression includes parentheses, the execution starts with the innermost pair. important for exam: All set operators (UNION, MINUS, etc) have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order. boolean operators: NOT > AND > OR ("Not Again, Oprah!") remember this - in absence of parentheses grouping otherwise, AND has greater precedence than OR, so component statements connected by AND will be compared first
AS OF clause
Oracle Flashback Query: Recovering at the Row Level In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 12:30 PM, an employee 'JOHN' had been deleted from your EMP table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database. Querying the past state of the table is achieved using the AS OF clause of the SELECT statement. For example, the following query retrieves the state of the employee record for 'JOHN' at 9:30AM, April 4, 2005: SELECT * FROM EMP AS OF TIMESTAMP TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = 'JOHN'; Restoring John's information to the table EMP requires the following update: INSERT INTO EMP (SELECT * FROM EMP AS OF TIMESTAMP TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = 'JOHN'); The missing row is re-created with its previous contents, with minimal impact to the running database.
Invisible Index - notes
Oracle allows you to turn off the index (make it invisible), yet continue to maintain the index (during any DML—INSERT/UPDATE/DELETE) in case you need to turn it back on quickly. You can do this by making the index visible or invisible: ALTER INDEX idx1 INVISIBLE; ALTER INDEX idx1 VISIBLE; CREATE INDEX . . . INVISIBLE; The index is updated for any DELETE statements performed on the table. Invisible indexes are still maintained, even though they are invisible. An invisible index has nothing to do with invisible columns in the table. The use of a SELECT statement never results in a maintenance action of an index, regardless of the visibility of the index.
POWER
POWER Returns the first provided number raised to the power of the second provided number. POWER ( n2, n1 )
Selection vs. Projection
Projection is simply the fields (columns) you wish to use when building a SELECT query. Selection is simply the tuples(rows) you wish to use when building a SELECT query
Order of clauses in a select
SELECT col1, col2 FROM table WHERE condition GROUP BY cols HAVING condition ORDER BY col; "Such Fun! Why Go Home, Oprah?"
NULL and sorting
Remember that NULL values sort higher than NOT NULL values when ORDER BY sorts on a column containing NULL values. (However, while that is true, only NOT NULL values are considered by the MAX and other aggregate functions.)
TRIM
Removes the leading and trailing characters from a string. TRIM ( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] trim_source )
LTRIM
Removes the specified characters from the left of the provided value. LTRIM( input_string, [trim_string] )
RTRIM
Removes the specified characters from the right of the provided value. RTRIM ( input_string, [trim_character])
ALTER TABLE...MODIFY - what things can you do?
Rename a table Add a constraint to a table Add a column to a table Drop a column from a table Drop an existing constraint from a table Modify a column - Rename a column Modify the column's visibility Change the nullability constraint for a column Add constraint Shorten or widen the size of the column Change the default value of a column
REPLACE
Replaces one string with another string in the provided value. REPLACE ( whole_string, string_to_replace, [replacement_string])
SUBSTR
Returns a part of the provided value, based on the provided position and length. SUBSTR (string, start_position, [length] )
SOUNDEX
Returns a string containing a phonetic representation of the provided value. SOUNDEX (string )
LEAD analytical function
Returns a value from a row further down in your result set. LEAD ( expression [, offset [, default] ] ) OVER ( [ query_partition_clause ] order_by_clause )
LAG analytical function
Returns a value from a row further up in your result set. LAG ( expression [, offset [, default] ] ) OVER ( [ query_partition_clause ] order_by_clause )
LOWER
Returns a value where all characters in the provided string are in lowercase. LOWER ( input_string )
UPPER
Returns a value where all characters in the provided string are in uppercase. UPPER ( input_string )
INITCAP
Returns a value where the first letter in each word is uppercase, and all others are lowercase. INITCAP ( input_string )
CONCAT
Returns a value where the provided strings have been concatenated. CONCAT( string1, string2 ) **note CONCAT command can accept 2 parameters only; use the || operator if you need to concatenate more
ABS
Returns the absolute value of the provided number.
SYSDATE
Returns the current date and time (db time)
SYSTIMESTAMP
Returns the current date and time, including fractional seconds and timezone. (db time)
LAST_DAY
Returns the date of the last day of the month containing the specified date. LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the datatype of date. Examples The following statement determines how many days are left in the current month. SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL; SYSDATE Last Days Left --------- --------- ---------- 30-MAY-01 31-MAY-01 1 The following example adds 5 months to the hire date of each employee to give an evaluation date: SELECT last_name, hire_date, TO_CHAR( ADD_MONTHS(LAST_DAY(hire_date), 5)) "Eval Date" FROM employees; LAST_NAME HIRE_DATE Eval Date ------------------------- --------- --------- King 17-JUN-87 30-NOV-87 Kochhar 21-SEP-89 28-FEB-90 De Haan 13-JAN-93 30-JUN-93 Hunold 03-JAN-90 30-JUN-90 Ernst 21-MAY-91 31-OCT-91 Austin 25-JUN-97 30-NOV-97 Pataballa 05-FEB-98 31-JUL-98 Lorentz 07-FEB-99 31-JUL-99 . . .
COALESCE
Returns the first non-NULL value. COALESCE (expr [, expr ]...) COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null. Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL. You can also use COALESCE as a variety of the CASE expression. For example, COALESCE (expr1, expr2) is equivalent to: CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END Similarly, COALESCE (expr1, expr2, ..., exprn), for n>=3 is equivalent to: CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE COALESCE (expr2, ..., exprn) END The following example uses the sample oe.product_information table to organize a clearance sale of products. It gives a 10% discount to all products with a list price. If there is no list price, then the sale price is the minimum price. If there is no minimum price, then the sale price is "5": SELECT product_id, list_price, min_price, COALESCE(0.9*list_price, min_price, 5) "Sale" FROM product_information WHERE supplier_id = 102050 ORDER BY product_id, list_price, min_price, "Sale"; PRODUCT_ID LIST_PRICE MIN_PRICE Sale ---------- ---------- ---------- ---------- 1769 48 43.2 1770 73 73 2378 305 247 274.5 2382 850 731 765 3355 5
NEXT_DAY
Returns the first specified weekday after a specified date. NEXT_DAY(date, char) NEXT_DAY returns the date of the first weekday named by char that is later than the date date. The return type is always DATE, regardless of the datatype of date. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date. Examples This example returns the date of the next Tuesday after February 2, 2001: SELECT NEXT_DAY('02-FEB-2001','TUESDAY') "NEXT DAY" FROM DUAL; NEXT DAY ----------- 06-FEB-2001
FLOOR
Returns the largest whole number (integer) which is less than or equal to the provided number.
LENGTH
Returns the length of the provided string. LENGTH ( string_value )
REMAINDER
Returns the remainder of the first provided number (n2) divided by the second provided number (n1). BUT with reference to the nearest integer (greater than OR less than) n2/n1, not like mod where it is always in reference to the nearest integer less than n2/n1 (floor function) REMAINDER ( n2, n1 ) important: the calculation for REMAINDER (n2, n1) is: n2 - (n1*N), where N is the integer nearest to n2/n1. The syntax of Oracle REMAINDER is: REMAINDER ( n2, n1 ) The parameters of the Oracle REMAINDER function are: n2 (mandatory): This is the number that is divided into. If you think of it as a fraction, this is the number on top, or the numerator. n1 (mandatory): This is the number that is used to divide into the other number. If you think of it as a fraction, this is the number on the bottom, or the denominator. Some other points to note about this function: The n2 and n1 inputs can be any numeric data type. The return type is a numeric data type, depending on the input data types. If n1=0, or n2=infinity, then Oracle will return an error if the input types are NUMBER, or NaN if the input types are BINARY_FLOAT or BINARY_DOUBLE. If n1 is not 0, then the remainder is n2 - (n1*N), where N is the integer nearest to n2/n1. If n2 is a floating point number and the remainder is 0, then the sign of the remainder is the sign of n2. to reiterate, the calculation for REMAINDER (n2, n1) is: n2 - (n1*N), where N is the integer nearest to n2/n1. Example 1 This example should show a positive result. SELECT REMAINDER(24, 7) AS REMTEST FROM dual; Result: REMTEST3 The result is 3, because 7*3 is 21, and there is 3 left over. Example 2 This example demonstrates a remainder of 0. SELECT REMAINDER(24, 6) AS REMTEST FROM dual; Result: REMTEST0 The result is 0, because 6 goes into 24 exactly 4 times, with no remainder. Example 3 This example demonstrates a negative remainder. SELECT REMAINDER(24, 5) AS REMTEST FROM dual; Result: REMTEST-1 This result is -1, because 5*5 is closer to 24 than 5*4 (25 vs 20), so 25 is used for the calculation. 24-25 is -1. what result of 5 times something is closest to 24? subtract that from 24, and get your answer the calculation for REMAINDER (n2, n1) is: n2 - (n1*N), where N is the integer nearest to n2/n1. Example 4 This is an example using larger numbers. SELECT REMAINDER(627, 101) AS REMTEST FROM dual; Result: REMTEST21 The result is 21, because 101*6 is 606, and 627-606 is 21. Example 5 This example uses two negative numbers as inputs. SELECT REMAINDER(-58, -10) AS REMTEST FROM dual; Result: REMTEST-58 The result is 2, because -10*6 = -60, and there is a remainder of 2.
MOD
Returns the remainder of the first provided value divided by the second provided value. MOD ( numerator, denominator ) The classical modulus can be expressed using the MOD function with this formula: m - n * FLOOR(m/n) ---compare to REMAINDER: m - n * ROUND(m/n)--- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ NOTA BENE: This function behaves differently from the classical mathematical modulus function when m is negative. (Basically if m is negative, mod is negative.) (see attached graphic) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Example 6 This example is a basic use of the MOD function. SELECT MOD(15, 4) AS MODTEST FROM dual; MODTEST 3 The result is 3 because there is 3 left over after you perform 15/4. Example 7 This example uses a decimal as the first parameter. SELECT MOD(15.5, 4) AS MODTEST FROM dual; MODTEST 3.5 The result is 3.5, which works in a similar way to Example 1. There is 3.5 left over after the 15.5/4 operation. Example 8 This example uses decimals for both parameters. SELECT MOD(15.5, 4.5) AS MODTEST FROM dual; Result: MODTEST 2 The result is 2, because 4.5 goes into 15.5 twice. Example 9 This example uses 0 as the second parameter. SELECT MOD(15, 0) AS MODTEST FROM dual; Result: MODTEST 15 The result is 15, because the MOD function returns the first parameter if the second parameter is 0. Example 10 This example uses a number stored as a character value. SELECT MOD(15, '6') AS MODTEST FROM dual; Result: MODTEST 3 The result is 3. The function performs the same way, even though the number is stored as a char type. Example 11 This example is another basic example with different numbers. SELECT MOD(22, 8) AS MODTEST FROM dual; Result: MODTEST 6 The result is 6, as that it what is left over after you perform 22/8.
CEIL
Returns the smallest whole number (integer) which is greater than or equal to the provided number.
SQRT
Returns the square root of the provided number SQRT ( number )
STDDEV
Returns the standard deviation of a set of numbers. STDDEV ( [DISTINCT | ALL] expression ) STDDEV ( [DISTINCT | ALL] expression ) [OVER (analytical_clause) ]
EXP
Returns the standard value of e raised to the power of the provided number. EXP ( number )
TZ_OFFSET
Returns the timezone offset (from UTC) from the value specified. TZ_OFFSET returns the time zone offset corresponding to the argument based on the date the statement is executed. You can enter a valid time zone name (e.g. 'Europe/London'), a time zone offset from UTC (which simply returns itself), or the keyword SESSIONTIMEZONE or DBTIMEZONE. For a listing of valid values for time_zone_name, query the TZNAME column of the V$TIMEZONE_NAMES dynamic performance view. The following example returns the time zone offset of the US/Eastern time zone from UTC: SELECT TZ_OFFSET('US/Eastern') FROM DUAL; TZ_OFFS ------- -04:00
Joins - basic syntax and types
SELECT t1.*, t2.* FROM t1 join_type t2 ON t1.col = t2.col; INNER JOIN: show all matching records in both tables. LEFT JOIN: show all records from left table, and any matching records from right table. RIGHT JOIN: show all records from right table, and any matching records from left table. FULL JOIN: show all records from both tables, whether there is a match or not. CROSS JOIN: show all combinations of records from both tables. SELF JOIN: join a table to itself. Used for hierarchical data. SELECT p.*, c.* FROM yourtable p INNER JOIN yourtable c ON p.id = c.parent_id;
SESSIONTIMEZONE
SESSIONTIMEZONE returns the time zone of the current session. The return type is a time zone offset (a character type in the format '[+|]TZH:TZM') or a time zone region name, depending on how the user specified the session time zone value in the most recent ALTER SESSION statement. DBTIMEZONE and SESSIONTIMEZONE are different in their operational scope. DBTIMEZONE shows the database time zone, while SESSIONTIMEZONE shows it for the session.
view facts
SQL view is nothing but a logical table or a virtual table stored in a database. We can also define a VIEW as SELECT Statement with a name which is stored in a database as though it were a table. All the DML commands which you can perform on a table can be performed on a view also.
SQL*Loader overview
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads. SQL*Loader (sqlldr) is the utility to use for high performance data loads. The data can be loaded from any text file and inserted into the database.
REGEXP_INSTR
Searches for a value within another value, using regular expressions. REGEXP_INSTR ( source_char, pattern [, position [, occurrence [, return_option [, match_pattern [, subexpression ] ] ] ] ] )
INSTR
Searches for a value within another value. INSTR( string, substring, [start_position], [occurrence] )
CASE Statement - simple case, searched case
Simple Case: CASE name WHEN 'John' THEN 'Name John' WHEN 'Steve' THEN 'Name Steve' ELSE 'Unknown' END Searched Case: CASE WHEN name='John' THEN 'Name John' WHEN name='Steve' THEN 'Name Steve' ELSE 'Unknown' END
subqueries - syntax
Single Row: SELECT id, last_name, salary FROM employee WHERE salary = ( SELECT MAX(salary) FROM employee ); Multi Row: SELECT id, last_name, salary FROM employee WHERE salary IN ( SELECT salary FROM employee WHERE last_name LIKE 'C%' );
list of single-row functions
Single-Row Functions Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses. Numeric Functions ABS, ACOS, ASIN, ATAN, ATAN2, BITAND, CEIL, COS, COSH, EXP, FLOOR, LN, LOG, MOD, NANVL, POWER, REMAINDER, ROUND (number), SIGN, SIN, SINH, SQRT, TAN, TANH, TRUNC (number), WIDTH_BUCKET Character Functions Returning Character Values CHR, CONCAT, INITCAP, LOWER, LPAD, LTRIM, NCHR, NLS_INITCAP, NLS_LOWER, NLS_UPPER, NLSSORT, REGEXP_REPLACE, REGEXP_SUBSTR, REPLACE, RPAD, RTRIM, SOUNDEX, SUBSTR, TRANSLATE, TRANSLATE ... USING, TRIM, UPPER Character Functions Returning Number Values ASCII, INSTR, LENGTH, REGEXP_COUNT, REGEXP_INSTR Character Set Functions The character set functions return information about the character set. The character set functions are: NLS_CHARSET_DECL_LENNLS_CHARSET_IDNLS_CHARSET_NAME Datetime Functions ADD_MONTHS, CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, EXTRACT (datetime), FROM_TZ, LAST_DAY, LOCALTIMESTAMP, MONTHS_BETWEEN, NEW_TIME, NEXT_DAY, NUMTODSINTERVAL, NUMTOYMINTERVAL, ORA_DST_AFFECTED, ORA_DST_CONVERT, ORA_DST_ERROR, ROUND (date), SESSIONTIMEZONE, SYS_EXTRACT_UTC, SYSDATE, SYSTIMESTAMP, TO_CHAR (datetime), TO_DSINTERVAL, TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_YMINTERVAL, TRUNC (date), TZ_OFFSET ~~~~~~~~~~~~~~~~~~~~~~`` General Comparison Functions The general comparison functions determine the greatest and or least value from a set of values. The general comparison functions are: GREATESTLEAST Conversion Functions Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention datatype TO datatype. The first data type is the input data type. The second data type is the output data type. The SQL conversion functions are: ASCIISTRBIN_TO_NUMCASTCHARTOROWIDCOMPOSECONVERTDECOMPOSEHEXTORAWNUMTODSINTERVALNUMTOYMINTERVALRAWTOHEXRAWTONHEXROWIDTOCHARROWIDTONCHARSCN_TO_TIMESTAMPTIMESTAMP_TO_SCNTO_BINARY_DOUBLETO_BINARY_FLOATTO_BLOBTO_CHAR (character)TO_CHAR (datetime)TO_CHAR (number)TO_CLOBTO_DATETO_DSINTERVALTO_LOBTO_MULTI_BYTETO_NCHAR (character)TO_NCHAR (datetime)TO_NCHAR (number)TO_NCLOBTO_NUMBERTO_SINGLE_BYTETO_TIMESTAMPTO_TIMESTAMP_TZTO_YMINTERVALTREATUNISTR Large Object Functions The large object functions operate on LOBs. The large object functions are: BFILENAMEEMPTY_BLOB, EMPTY_CLOB Collection Functions The collection functions operate on nested tables and varrays. The SQL collection functions are: CARDINALITYCOLLECTPOWERMULTISETPOWERMULTISET_BY_CARDINALITYSET Hierarchical Functions Hierarchical functions applies hierarchical path information to a result set. The hierarchical function is: SYS_CONNECT_BY_PATH Data Mining Functions... XML Functions... JSON Functions... Encoding and Decoding Functions The encoding and decoding functions let you inspect and decode data in the database. The encoding and decoding functions are: DECODEDUMPORA_HASHSTANDARD_HASHVSIZE NULL-Related Functions The NULL-related functions facilitate null handling. The NULL-related functions are: COALESCELNNVLNANVLNULLIFNVLNVL2 Environment and Identifier Functions The environment and identifier functions provide information about the instance and session. The environment and identifier functions are: CON_DBID_TO_IDCON_GUID_TO_IDCON_NAME_TO_IDCON_UID_TO_IDORA_INVOKING_USERORA_INVOKING_USERIDSYS_CONTEXTSYS_GUIDSYS_TYPEIDUIDUSERUSERENV
USING INDEX clause
Specifying the Index Associated with a Constraint If you require more explicit control over the indexes associated with UNIQUE and PRIMARY KEY constraints, the database lets you: Specify an existing index that the database is to use to enforce the constraint Specify a CREATE INDEX statement that the database is to use to create the index and enforce the constraint These options are specified using the USING INDEX clause. The following statements present some examples. Example 1: CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1))); Example 2: CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi); Example 3: CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;
Datetime Format Element Suffixes
Suffix - Meaning - Example Element - Example Value TH - Ordinal Number - DDTH -4TH SP - Spelled Number - DDSP - FOUR SPTH or THSP - Spelled, ordinal number - DDSPTH - FOURTH Notes on date format element suffixes: When you add one of these suffixes to a datetime format element, the return value is always in English. Datetime suffixes are valid only to format output. You cannot use them to insert a date into the database.
list of system and object privileges
System Privilege Description CREATE SESSION Connect to the database. CREATE TABLE Create a table in your user account. Includes the ability to use ALTER and DROP TABLE. Also includes the ability to use CREATE, ALTER, and DROP INDEX on objects. CREATE VIEW Create a view in your user account. Includes ALTER and DROP. CREATE SEQUENCE Create a sequence in your user account. Includes ALTER and DROP. CREATE SYNONYM Create a synonym in your user account. Includes ALTER and DROP. Does not include PUBLIC synonyms (see CREATE PUBLIC SYNONYM). CREATE ROLE Create a role. Includes ALTER and DROP. CREATE PUBLIC SYNONYM Create a synonym in the PUBLIC account. Does not include DROP, which is separate. DROP PUBLIC SYNONYM Drop a synonym from the PUBLIC account. CREATE ANY TABLE Create a table within any user account. ALTER ANY TABLE Alter a table within any user account. DELETE ANY TABLE Delete from any table within any user account. DROP ANY TABLE Drop or truncate any table within any user account. INSERT ANY TABLE Insert into any table within any user account. SELECT ANY TABLE Select from any table within any user account. UPDATE ANY TABLE Update any table within any user account. CREATE ANY VIEW Create a view in any user account. DROP ANY VIEW Drop a view from any user account. CREATE ANY INDEX Create an index in any user account. ALTER ANY INDEX Alter an index in any user account. DROP ANY INDEX Drop an index from any user account. CREATE ANY SEQUENCE Create a sequence in any user account. ALTER ANY SEQUENCE Alter a sequence in any user account. DROP ANY SEQUENCE Drop a sequence from any user account. SELECT ANY SEQUENCE Select from a sequence in any user account. CREATE ANY SYNONYM Create a synonym in any user account. DROP ANY SYNONYM Drop a synonym from any user account. CREATE ANY DIRECTORY Create a directory in any user account. DROP ANY DIRECTORY Drop a directory from any user account. ALTER ANY ROLE Alter a role in the database. DROP ANY ROLE Drop any role in the database. GRANT ANY ROLE Grant any role in the database. FLASHBACK ANY TABLE Perform flashback operations on any table in the database. CREATE USER Create a user account. ALTER USER Alter a user account. DROP USER Drop a user account. GRANT ANY PRIVILEGE Grant any system privilege to any user account in the database. GRANT ANY OBJECT PRIVILEGE Grant, to any other user account in the database, any object privilege that the object's owner is also able to grant. -------------------------------------------------- Here is a list of object privileges that are assigned directly to roles or users. Privilege Description SELECT Privilege to perform SELECT statements on the table. INSERT Privilege to perform INSERT statements on the table. UPDATE Privilege to perform UPDATE statements on the table. DELETE Privilege to perform DELETE statements on the table. REFERENCES Privilege to create a constraint that refers to the table. ALTER Privilege to perform ALTER TABLE statements to change the table definition. INDEX Privilege to create an index on the table with the create index statement. ALL All privileges on table.
TIMESTAMP WITH LOCAL TIME ZONE Datatype
TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP. It differs from TIMESTAMP WITH TIME ZONE as follows: data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When users retrieve the data, Oracle returns it in the users' local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time, formerly Greenwich Mean Time). Specify the TIMESTAMP WITH LOCAL TIME ZONE datatype as follows: TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND datetime field. There is no literal for TIMESTAMP WITH LOCAL TIME ZONE, but TIMESTAMP literals and TIMESTAMP WITH TIME ZONE literals can be inserted into a TIMESTAMP WITH LOCAL TIME ZONE column. The default date format for TIMESTAMP WITH LOCAL TIME ZONE is determined by the value of the NLS_TIMESTAMP_FORMAT initialization parameter.
TIMESTAMP WITH TIME ZONE Datatype
TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone offset or time zone region name in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time, formerly Greenwich Mean Time). Specify the TIMESTAMP WITH TIME ZONE datatype as follows: TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND datetime field. You can specify TIMESTAMP WITH TIME ZONE as a literal as follows: TIMESTAMP '1997-01-31 09:26:56.66 +02:00' Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instant in UTC, regardless of the TIME ZONE offsets stored in the data. For example, the following expressions have the same value: TIMESTAMP '1999-01-15 8:00:00 -8:00' TIMESTAMP '1999-01-15 11:00:00 -5:00' You can replace the UTC offset with the TZR (time zone region) format element. The following expression specifies US/Pacific for the time zone region: TIMESTAMP '1999-01-15 8:00:00 US/Pacific' To eliminate the ambiguity of boundary cases when the time switches from Standard Time to Daylight Saving Time, use both the TZR format element and the corresponding TZD format element. The TZD format element is an abbreviation of the time zone region with Daylight Saving Time information included. Examples are PST for US/Pacific standard time and PDT for US/Pacific daylight time. The following specification ensures that a Daylight Saving Time value is returned: TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT' The default date format for the TIMESTAMP WITH TIME ZONE datatype is determined by the value of the NLS_TIMESTAMP_TZ_FORMAT initialization parameter.
TRUNCATE TABLE
TRUNCATE TABLE tablename; TRUNCATE is used to remove all the rows from a table. By default, it releases all the storage space used by the table.
creating and using temporary tables
Temporary Tables In addition to permanent tables, Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session. The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table that can be transaction-specific or session-specific. For transaction-specific temporary tables, data exists for the duration of the transaction. For session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table, because each session has its own private data. A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session. It does not truncate the data of other sessions that are using the same table. DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination, either when the user logs off or when the session terminates abnormally such as during a session or instance failure. You can create indexes for temporary tables using the CREATE INDEX statement. Indexes created on temporary tables are also temporary, and the data in the index has the same session or transaction scope as the data in the temporary table. You can create views that access both temporary and permanent tables. You can also create triggers on temporary tables. Oracle utilities can export and import the definition of a temporary table. However, no data rows are exported even if you use the ROWS clause. Similarly, you can replicate the definition of a temporary table, but you cannot replicate its data. Segment Allocation Temporary tables use temporary segments. Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, then the table appears to be empty. You can perform DDL statements (ALTER TABLE, DROP TABLE, CREATE INDEX, and so on) on a temporary table only when no session is currently bound to it. A session gets bound to a temporary table when an INSERT is performed on it. The session gets unbound by a TRUNCATE, at session termination, or by doing a COMMIT or ROLLBACK for a transaction-specific temporary table. Temporary segments are deallocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables. Parent and Child Transactions Transaction-specific temporary tables are accessible by user transactions and their child transactions. However, a given transaction-specific temporary table cannot be used concurrently by two transactions in the same session, although it can be used by transactions in different sessions. If a user transaction does an INSERT into the temporary table, then none of its child transactions can use the temporary table afterward. If a child transaction does an INSERT into the temporary table, then at the end of the child transaction, the data associated with the temporary table goes away. After that, either the user transaction or any other child transaction can access the temporary table.
REFERENCES object privilege
The REFERENCES object privilege allows a user to create FOREIGN KEY constraints that reference a specified table. This privilege can be granted to a user but not to a role.
ALTER VIEW... COMPILE
The COMPILE keyword directs Oracle Database to recompile the view. note there is NO RECOMPILE keyword...don't be tricked
CREATE TABLE AS SELECT
The CREATE TABLE AS SELECT allows you to create a table from the results of a SELECT statement. example: CREATE TABLE suppliers AS (SELECT * FROM companies WHERE company_id < 5000); A table created with CTAS can use any table as its source, whether it be a table inside the database or external table. Null constraints are carried over to new table When you copy a table from a subquery, only the NOT NULL constraint will transfer unless you explicitly state otherwise in the new table creation. (-source bchapman) Foreign key constraints not carried over to new table
DATE Datatype
The DATE datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, date, hour, minute, and second. You can specify a date value by: Specifying the date value as a literal Converting a character or numeric value to a date value with the TO_DATE function A date can be specified as an ANSI date literal or as an Oracle date value. An ANSI date literal contains no time portion and must be specified in exactly the following format: DATE 'YYYY-MM-DD' The following is an example of an ANSI date literal: DATE '1998-12-25' Alternatively, you can specify an Oracle date value as shown in the following example: TO_DATE('1998-DEC-25 17:30','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN') The default date format for an Oracle date value is derived from the NLS_DATE_FORMAT and NLS_DATE_LANGUAGE initialization parameters. The date format in the example includes a two-digit number for the day of the month, an abbreviation of the month name, the last two digits of the year, and a 24-hour time designation. The specification for NLS_DATE_LANGUAGE is included because 'DEC' is not a valid value for MON in all locales. Oracle automatically converts character values that are in the default date format into date values when they are used in date expressions. If you specify a date value without a time component, then the default time is midnight. (!!!) If you specify a date value without a date, then the default date is the first day of the current month. (!!!) Oracle DATE columns always contain fields for both date and time. If your queries use a date format without a time portion, then you must ensure that the time fields in the DATE column are set to midnight. You can use the TRUNC (date) SQL function to ensure that the time fields are set to midnight, or you can make the query a test of greater than or less than (<, <=, >=, or >) instead of equality or inequality (= or !=). Otherwise, Oracle may not return the query results you expect. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The default internal storage of dates is in numeric format.
DEFAULT constraint
The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' );
DUAL table facts
The DUAL table has one column named DUMMY whose data type is VARCHAR2() and contains one row with a value X. Belongs to the schema of the user SYS but is accessible to all users.
Format Model Modifiers
The FM and FX modifiers, used in format models in the TO_CHAR function, control blank padding and exact format checking. ***A modifier can appear in a format model more than once. In such a case, each subsequent occurrence toggles the effects of the modifier. Its effects are enabled for the portion of the model following its first occurrence, and then disabled for the portion following its second, and then reenabled for the portion following its third, and so on.*** FM Fill mode. Oracle uses blank characters to fill format elements to a constant width equal to the largest element for the relevant format model in the current session language. For example, when NLS_LANGUAGE is AMERICAN, the largest element for MONTH is SEPTEMBER, so all values of the MONTH format element are padded to 9 display characters. This modifier suppresses blank padding in the return value of the TO_CHAR function: In a datetime format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading zeroes for subsequent number elements (such as MI) in a date format model. Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM, which suppresses blank padding, the length of the return value may vary. In a number format element of a TO_CHAR function, this modifier suppresses blanks added to the left of the number, so that the result is left-justified in the output buffer. Without FM, the result is always right-justified in the buffer, resulting in blank-padding to the left of the number. FX Format exact. This modifier specifies exact matching for the character argument and datetime format model of a TO_DATE function: Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model. The character argument cannot have extra blanks. Without FX, Oracle ignores extra blanks. Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without FX, numbers in the character argument can omit leading zeroes. When FX is enabled, you can disable this check for leading zeroes by using the FM modifier as well. If any portion of the character argument violates any of these conditions, then Oracle returns an error message.
NOT EXISTS vs NOT IN
The NOT EXISTS and NOT IN behave differently when there are null values involved. NOT EXISTS usually a better method. IN operator is less efficient than the EXISTS operator The following expression returns a NULL value if any row in the result set of the subquery is NULL: id NOT IN (subquery) In contrast, NULL does not affect the result of the NOT EXIST operator because the NOT EXISTS operator solely checks the existence of rows in the subquery.
MINUS set operator - notes
The Oracle MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement
NCHAR
The Oracle NCHAR datatype is used to store fixed-length Unicode character data. The character set of NCHAR can only be AL16UTF16 or UTF8, which is specified at the database creation time as the national character set. When you create a table with an NCHAR column, the maximum size of the NCHAR column is always in the character length semantics, for example: CREATE TABLE nchar_demo ( description NCHAR(10) ); In this example, the maximum length of the description column is 10 characters. It is not possible to use the byte length for maximum size of the NCHAR columns like the following: description NCHAR(10 BYTE) -- not possible The maximum byte length of a NCHAR column depends on the current national character set. It is the product of the maximum character length and the maximum number of bytes in each character.
NVL
The Oracle/PLSQL NVL function lets you substitute a value when a null value is encountered. Syntax The syntax for the NVL function in Oracle/PLSQL is: NVL( string1, replace_with ) *The first parameter of NVL() determines the datatype. The second parameter should match the first's datatype.*
NVL2
The Oracle/PLSQL NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered. Syntax The syntax for the NVL2 function in Oracle/PLSQL is: NVL2( string1, value_if_not_null, value_if_null )
TIMESTAMP Datatype
The TIMESTAMP datatype is an extension of the DATE datatype. It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the DATE datatype. Specify the TIMESTAMP datatype as follows: TIMESTAMP [(fractional_seconds_precision)] fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND datetime field. It can be a number in the range 0 to 9. The default is 6. For example, '26-JUN-02 09:39:16.78' shows 16.78 seconds. The fractional seconds precision is 2 because there are 2 digits in '78'. You can specify the TIMESTAMP literal in a format like the following: TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF' Using the example format, specify TIMESTAMP as a literal as follows: TIMESTAMP '1997-01-31 09:26:50.12' The value of NLS_TIMESTAMP_FORMAT initialization parameter determines the timestamp format when a character string is converted to the TIMESTAMP datatype. NLS_DATE_LANGUAGE determines the language used for character data such as MON.
UNION ALL set operator - notes
The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows: ORDER BY clause needs to be in last select statement; else, error.
UNION set operator - notes
The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows: ORDER BY clause needs to be in last select statement; else, error. Null values are not ignored during duplicate checking will sort (because it will filter, so it will be sorted) the number of merged query columns need to be equal, the type must be equal or implicit conversion Long type is not supported
V$
The V$ prefix is related to dynamic performance views for the local database. 1. System and session paraameters 2. Memory usage and allocation 3. File states (including RMAN backup files) 4. Progress of jobs and tasks 5. SQL execution 6. Statistics and metrics
single-row functions: character set functions
The character set functions return information about the character set. The character set functions are: NLS_CHARSET_DECL_LEN NLS_CHARSET_ID NLS_CHARSET_NAME
data dictionary overview and facts
The data dictionary is a collection of database tables and views. It is automatically built and populated by the Oracle database. The information stored in the data dictionary includes the full description of all the database objects you create as part of your application, including tables, views, indexes, constraints, sequences, and more. In other words, the result of each Data Definition Language (DDL) statement you've studied in this book is recorded in the dictionary, and the information is automatically maintained by the Oracle system in real time as you change database objects and their structures. The information stored in the data dictionary includes (but is not limited to): --The names of database objects, their owners, and when they were created --The names of each table's columns, along with data types, precision, and scale --Any constraints --Views, indexes, and sequences
FLASHBACK - notes
The following DDL operations change the structure of a table, so that you cannot subsequently use the TO SCN or TO TIMESTAMP clause to flash the table back to a time preceding the operation: upgrading, moving, or truncating a table; adding a constraint to a table, ... modifying or dropping a column... and more complicated things I'm not interested in ~~~~~~~~~~~~~~~~~~~ Use the alter database flashback on command to enable flashback database mode. The database must be in ARCHIVELOG mode to use the flashback database command.
COUNT
The purpose of the COUNT function is to count the number of rows returned in a SELECT statement. Syntax The syntax of the Oracle COUNT function is: COUNT ( [ * | [ DISTINCT | ALL ] expression) [OVER (analytic_clause) ] remember for exam: COUNT(*) - returns duplicates and nulls COUNT (expression) - returns duplicates, does not return nulls COUNT(DISTINCT expression) - does not return duplicates, does not return nulls COUNT(1) - is same as COUNT(*) COUNT returns the number of rows returned by the query. You can use it as an aggregate or analytic function. If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed. If you specify expr, then COUNT returns the number of rows where expr is not null. You can count either all rows, or only distinct values of expr. If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls. COUNT never returns null. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Now, there's a lot of square brackets and conditions in that function. Basically, you can call it in a few ways: COUNT(*) - returns duplicates and nulls COUNT (expression) - returns duplicates, does not return nulls COUNT(DISTINCT expression) - does not return duplicates, does not return nulls COUNT(ALL expression) COUNT(*) OVER (analytic_clause) COUNT(DISTINCT expression) OVER (analytic_clause) COUNT(ALL expression) OVER (analytic_clause) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ A few things to note about this function: COUNT(*) is the most common way to use this function. Because COUNT is an aggregate function, any columns in the SELECT clause that are not aggregated need to be in the GROUP BY clause. See the examples section below for more information. If you specify the expression, then COUNT returns the number of rows where expression is not null. You can count all rows (using ALL), or distinct values of the expression (using DISTINCT). If you specify the asterisk character (*), then COUNT returns all rows, including duplicates and nulls. So, COUNT can return a few different values (in highest to lowest order): COUNT(*) - all rows, including duplicates and nulls. COUNT(expression) - all rows excluding null. COUNT(DISTINCT expression) - all rows excluding duplicates and nulls. The fact that COUNT(expr) excludes NULL values means you should be careful as to which column or expression you use in this function. Different expressions may return different results. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Example 1 This example uses a basic COUNT(*) function. SELECT COUNT(*) FROM student; COUNT(*) 10 The result is 10 because there are 10 records in the table. Example 2 This example counts the unique values for the gender column SELECT COUNT(DISTINCT gender) FROM student; COUNT(DISTINCTGENDER) 2 The result is 2 as there are 2 unique values. Example 3 This example uses the COUNT and DISTINCT in a different order. SELECT DISTINCT COUNT(gender) FROM student; COUNT(GENDER) 10 The result is 10, because the query actually performs the count of gender, then finds all distinct rows. Because the only row returned has a value of 10, it is already unique. Example 4 This example uses a column and the COUNT function. SELECT gender, count(*) FROM student GROUP BY gender; GENDER COUNT(*) M 6 F 4 You can see that there are 6 students with an M value and 4 students with an F value. Example 5 This example finds the number of students who have more than or equal to 100 in fees_paid. SELECT COUNT(*) FROM student WHERE fees_paid >= 100; Result: COUNT(*) 8 The result is 8, because two students have less than 100 in fees_paid. Example 6 This example finds the number of students that have paid more than or equal to 100 in fees, and splits them by gender. SELECT gender, count(*) FROM student WHERE fees_paid >= 100 GROUP BY gender; GENDER COUNT(*) M 5 F 3 You can see there are 3 with the value of F and 5 with the value of M.
WITH clause (subquery factoring clause)
The with clause, also known as the subquery factoring clause, is used to define a query that becomes a temporary result set (much like a global temporary table) that can be used by the remaining query. For example, if you wanted to create a list of all employee numbers from the employee table, and then later join that list to the employee_transaction_history table you might write a query like this: With employees_list as (select empid from employee) Select * from employee_transaction_history a, employees_list b Where a.empid=b.empid; In this case, the with clause will collect all empid's from the employee table. In most cases, Oracle will actually create a temporary table that stores this rowset, much like a global temporary table. Then, Oracle will join the employees_list result set with the query against the employee_transaction_history table to get the final result set. --------------------------------------- WITH clause retrieves the results of a query block and stores it in the user's temporary tablespace and thereby improves performance. It is mostly used in computing aggregations. It is also useful for writing recursive queries. Syntax: WITH <query_name> As (SQL query) SELECT * FROM <query_name> ; Sample Query using Subquery: SELECT empno, sal/cnt_dpt.num_emp FROM emp e, (SELECT deptno, count(1) num_emp FROM emp GROUP BY deptno)cnt_dpt WHERE e.deptno = cnt_dpt.deptno Same Query using WITH clause: WITH cnt_dpt AS ( SELECT deptno, COUNT(1)num_emp FROM emp GROUP BY deptno) SELECT empno, sal/num_emp FROM emp e, cnt_dpt c WHERE e.deptno =c.deptno If the contents of the WITH clause is sufficiently complex, Oracle may decide to resolve the result of the subquery into a Global Temporary Table(GTT). This can make multiple references to the subquery more efficient.
difference between REMAINDER and MOD
They are both pretty similar. The difference is that REMAINDER uses ROUND in its calculation, and MOD uses the FLOOR function. ROUND can go up or down, but FLOOR always goes down. The main differences are when you use negative numbers.
multitable Insert - too many notes
This feature allows the INSERT . . . SELECT statement to use a number of tables as targets. In addition, it can distribute data among target tables based on logical attributes of the new rows. Using this DML operative thus enables a single scan and transformation of source data to insert data into multiple tables, sharply increasing performance. A multitable INSERT can have many WHEN conditions. INSERT ALL - Sometimes, you may want to insert multiple rows into a table or multiple tables. In this case, you use the Oracle INSERT ALL statement, which is also referred to as a multitable insert statement. In an INSERT FIRST statement, the first WHEN condition that evaluates to true is the only condition that is executed. All others are ignored. A multitable INSERT can be conditional, or unconditional. It is based on one subquery. The keyword ALL is used in both conditional and unconditional multitable INSERT. In a multitable INSERT, the SELECT statement at the end will specify the population from which each INSERT may draw values, but each INSERT is free to specify its own unique set of expressions. Any error in the INSERT will cause the statement to roll back all changes made to the database as a result of that particular execution of the statement. Oracle Corporation formally advises against using a sequence generator in a multitable INSERT statement. While it will be rejected in the subquery, a sequence generator may be included in the VALUES clause of the INTO statement but with potentially unpredictable or undesirable results. unconditional multitable insert syntax: INSERT ALL INTO table_name(col1,col2,col3) VALUES(val1,val2, val3) INTO table_name(col1,col2,col3) VALUES(val4,val5, val6) INTO table_name(col1,col2,col3) VALUES(val7,val8, val9) Subquery; Insert multiple rows into multiple tables Besides inserting multiple rows into a table, you can use the INSERT ALL statement to insert multiple rows into multiple tables as shown in the following syntax: INSERT ALL INTO table_name1(col1,col2,col3) VALUES(val1,val2, val3) INTO table_name2(col1,col2,col3) VALUES(val4,val5, val6) INTO table_name3(col1,col2,col3) VALUES(val7,val8, val9) Subquery; conditional multitable insert syntax: INSERT [ ALL | FIRST ] WHEN condition1 THEN INTO table_1 (column_list ) VALUES (value_list) WHEN condition2 THEN INTO table_2(column_list ) VALUES (value_list) ELSE INTO table_3(column_list ) VALUES (value_list) Subquery
NLS_PARAMETER explanation
This parameter is used to add extra information to the function in order for the value to be displayed. It works similar to the TO_CHAR function but has a few differences. The expression added here will represent the thousand group marker, decimal marker, and the currency symbols to be used. The expression for this parameter can contain one or more of the following parameters, separated by commas: NLS_CURRENCY symbol NLS_ISO_CURRENCY territory NLS_NUMERIC_CHARACTERS dg With these statements: symbol represents the local currency symbol and must be less than or equal to 10 characters territory is a text expression that identifies the territory whose ISO currency symbol is used dg represents two characters, which are single-byte characters, used for the decimal marker and thousands group marker.
syntax to alter table to add DEFAULT value on column
To create a DEFAULT constraint 'X' on a column when the table is already created, use the following: ALTER TABLE tblName MODIFY colName DEFAULT 'X';
CREATE GLOBAL TEMPORARY TABLE
To create a global temporary table, you use the CREATE GLOBAL TEMPORARY TABLE statement as follows: CREATE GLOBAL TEMPORARY TABLE table_name ( column_definition, ..., table_constraints ) ON COMMIT [DELETE ROWS | PRESERVE ROWS]; The syntax of creating a global temporary table and a permanent table are the same except for the keyword GLOBAL TEMPORARY and the clause ON COMMIT [DELETE ROWS | PRESERVE ROWS]. The ON COMMIT clause specifies whether data in the table is transaction-specific or session-specific: The ON COMMIT DELETE ROWS clause specifies that the global temporary table is transaction-specific. It means that Oracle truncates the table (remove all rows) after each commit. The ON COMMIT PRESERVE ROWS clause specifies that the global temporary table is session-specific, meaning that Oracle truncates the table when you terminate the session, not when you commit a transaction. Oracle uses the ON COMMIT DELETE ROWS option by default if you omit the ON COMMIT clause.
dropping tables and consequences
To drop a table that you no longer need, use the DROP TABLE statement. The table must be contained in your schema or you must have the DROP ANY TABLE system privilege. the consequences of doing so: Dropping a table removes the table definition from the data dictionary. All rows of the table are no longer accessible. All indexes and triggers associated with a table are dropped. All views and PL/SQL program units dependent on a dropped table remain, yet become invalid (not usable). All synonyms for a dropped table remain, but return an error when used.
TCL facts
Transaction control statements manage changes made by DML statements. The transaction control statements are: COMMIT ROLLBACK ***does not cause automatic commit** SAVEPOINT
TRUNC
Truncates the provided number to specified decimal places, or returns the date portion of a datetime value. date TRUNC ( date, fmt ) number: TRUNC(n1 [, n2 ]) The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If you omit n2, then the function returns the same datatype as the numeric datatype of the argument. If you include n2, then the function returns NUMBER.
RANGE UNBOUNDED PRECEDING (a restriction on the ORDER BY clause of an analytic function)
UNBOUNDED PRECEDING specify UNBOUNDED PRECEDING to indicate that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification.
set operators
UNION: Shows unique rows from two result sets. UNION ALL: Shows all rows from two result sets. INTERSECT: Shows rows that exist in both result sets. MINUS: Shows rows that exist in the first result set but not the second. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ "NULL is treated differently by the set operators UNION, EXCEPT, and INTERSECT than it is in search conditions. This difference is one of the main reasons to use set operators. When comparing rows, set operators treat NULL values as equal to each other. In contrast, when NULL is compared to NULL in a search condition the result is unknown (not true)." source: http://dcx.sybase.com/1200/en/dbusage/sorting-s-4872731.html
UPDATE with Join:
UPDATE t SET col1 = val1 FROM tablename t INNER JOIN table x ON t.id = x.tid WHERE condition;
UPDATE:
UPDATE tablename SET col1 = val1 WHERE condition; notes: All required to form syntactically correct statement: UPDATE, SET, WHERE
namespace facts and diagram
USER and ROLE objects are in their own collective namespace. PUBLIC SYNONYM objects are in their own namespace. TABLE, VIEW, SEQUENCE, PRIVATE SYNONYM, and user-defined TYPE objects have their own collective unique namespace within a given schema. **so the names for each of these objects must be unique within that namespace** INDEX objects have their own namespace within a given schema. CONSTRAINT objects have their own namespace within a given schema.
USER_TAB_COLUMNS
USER_TAB_COLUMNS describes the columns of the tables, views, and clusters owned by the current user. Its columns (except for OWNER) are the same as those in "ALL_TAB_COLUMNS".
USER_TAB_PRIVS
USER_TAB_PRIVS describes the object grants for which the current user is the object owner, grantor, or grantee. Its columns are the same as those in DBA_TAB_PRIVS.
USER_TAB_PRIVS_RECD
USER_TAB_PRIVS_RECD describes the object grants for which the current user is the grantee. Its columns (except for GRANTEE) are the same as those in ALL_TAB_PRIVS_RECD.
updatable view - restrictions
Updatable views cannot be built on a hierarchical query.
subqueries - notes
Use subqueries for the following purposes: To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement To define the set of rows to be included in a view or materialized view in a CREATE VIEW or CREATE MATERIALIZED VIEW statement To define one or more values to be assigned to existing rows in an UPDATE statement To provide values for conditions in a WHERE clause, HAVING clause, or START WITH clause of SELECT, UPDATE, and DELETE statements To define a table to be operated on by a containing query You do this by placing the subquery in the FROM clause of the containing query as you would a table name. You may use subqueries in place of tables in this way as well in INSERT, UPDATE, and DELETE statements. Subqueries so used can employ correlation variables, but only those defined within the subquery itself, not outer references. Please refer to table_collection_expression for more information. Scalar subqueries, which return a single column value from a single row, are a valid form of expression. You can use scalar subquery expressions in most of the places where expr is called for in syntax. Please refer to "Scalar Subquery Expressions" for more information. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ there are single-row and multi-row subqueries (see elsewhere in these study notes for syntax); single column and multi-column subqueries; multiset subquery? !!! need more research !!! how many levels deep can a nested subquery be in the WHERE clause of the top-level query? 255 Subquery can be nested to an unlimited depth in a FROM clause but to only 255 levels in a WHERE clause. Subqueries can appear in a SELECT statement, in these areas only: SELECT, FROM, WHERE, and HAVING. what clauses are always found in a subquery? SELECT, FROM (a subquery can also have a WHERE and a GROUP BY clause, but they are optional)
ALTER INDEX
Use the ALTER INDEX statement to change or rebuild an existing index. To alter an index, your schema must contain the index or you must have the ALTER ANY INDEX system privilege. With the ALTER INDEX statement, you can: Rebuild or coalesce an existing index Deallocate unused space or allocate a new extent Specify parallel execution (or not) and alter the degree of parallelism Alter storage parameters or physical attributes Specify LOGGING or NOLOGGING Enable or disable key compression Mark the index unusable Make the index invisible Rename the index Start or stop the monitoring of index usage You cannot alter index column structure. It is not possible to change the type (B*tree or bitmap), columns (add, remove) or uniqueness. examples: The following statement rebuilds the existing index emp_name: ALTER INDEX emp_name REBUILD; To make a visible index invisible, issue this statement: ALTER INDEX index INVISIBLE; To make an invisible index visible, issue this statement: ALTER INDEX index VISIBLE; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To find out whether an index is visible or invisible, query the dictionary views USER_INDEXES, ALL_INDEXES, or DBA_INDEXES. For example, to determine if the index IND1 is invisible, issue the following query: SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = 'IND1'; INDEX_NAME VISIBILITY ---------- ---------- IND1 VISIBLE
MERGE - notes
Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements. MERGE INTO target_table USING source_table ON search_condition WHEN MATCHED THEN UPDATE SET col1 = value1, col2 = value2,... WHERE <update_condition> [DELETE WHERE <delete_condition>] WHEN NOT MATCHED THEN INSERT (col1,col2,...) values(value1,value2,...) WHERE <insert_condition>; To execute the MERGE statement, you must have the INSERT and UPDATE object privileges on the source tables. If you use the DELETE clause, you must also have the DELETE object privilege on the target table. The USING clause is not optional; it is required in the MERGE statement. USING can identify a table, view, or subquery. An inline view is also acceptable. It identifies the source of data to be merged; the source data remains unchanged after the MERGE statement is executed. You cannot MERGE into two or more tables. Only one is permitted. You can MERGE into a view provided the view is updateable. The USING clause can reference two or more tables by way of a join or subquery. You cannot change the values of a join criteria during the join, so you cannot update columns that are referenced in an ON clause. The MERGE statement is not required to include a DELETE clause, but if included, it belongs within the WHEN MATCHED clause, after UPDATE, and not the WHEN NOT MATCHED clause. If one of the INTO clauses executed on a table and resulted in a constraint violation on that table, what would result? A: The row would not be inserted, the INSERT statement would stop, and all rows affected by the INSERT statement would be rolled back, as if the INSERT statement had never been executed.
REVOKE - overview and notes
Use the REVOKE statement to: Revoke system privileges from users and roles Revoke roles from users, roles, and program units. Revoke object privileges for a particular object from users and roles
Flashback Version Query
Use to retrieve the different versions of specific rows that existed during a given time interval. To use Oracle Flashback Query, use a SELECT statement with an AS OF clause. Oracle Flashback Query retrieves data as it existed at some time in the past. Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A new row version is created whenever a COMMIT statement is executed. Use Oracle Flashback Transaction Query to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval.
outer join operator (+) - notes
Using Oracle proprietary syntax, you cannot include the outer join operator (+) on both sides of the join condition. (To implement a full outer join you must use two SELECT statements, one performing a left outer join and the other performing a right outer join, and combine the results of these SELECT statements using the UNION operator.)
V_$ and V$ dynamic performance view prefixes
V_$ = for views V$ = for public synonyms **the views themselves actually have names that begin with V_$, but they each have matching public synonyms that start with V$** V_$ and V$ pertain to dynamic performance views, each of which has a public synonym counterpart. Store information about the local database instance dynamic performance views are populated in memory and do not get saved to the datafiles after a database restart. They contain status information about the current state of the database -- everything from overall instance status to current wait events for all user processes currently connected to the database
NCHAR varying / NVARCHAR2
Variable-length single-byte or National Character string,<= 4000 bytes. The NVARCHAR2 is Unicode data type that can store Unicode characters. The character set of the NVARCHAR2 is national character set specified at the database creation time. To find the character set of the NVARCHAR2 in your database, you use the following query: SELECT * FROM nls_database_parameters WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET'; ~~~~~~~~~ VARCHAR2 vs. NVARCHAR2 First, the maximum size of VARCHAR2 can be in either bytes or characters, whereas the maximum size of NVARCHAR2 is only in characters. In addition, the maximum byte length of an NVARCHAR2 depends on the configured national character set. Second, a VARCHAR2 column only can store characters in the default character set while the NVARCHAR2 can store virtually any characters.
updatable view
View that can be used in an INSERT, UPDATE, or DELETE statement to modify the contents of a base table that the view refers to
with clause - syntax
WITH queryname AS ( SELECT col1, col2 FROM firsttable) SELECT col1, col2.. FROM queryname...;
system vs object privileges
What is the difference between system privileges and object privileges in Oracle? System privileges allow a user to perform a particular database operation or class of database operations. For example, to create a table, the user needs the create table privilege. Objects have privileges associated with them, such as insert, update and delete a table.
invisible column in table
When this Column is defined, it will not appear in generic queries (select * from). It will need to be explicitly referred to in the SQL statement or condition. Invisible columns are still available for all actions, provided they are named explicitly. CREATE TABLE tab1 ( id NUMBER, description VARCHAR2(50) INVISIBLE ); External and temporary tables can not have invisible columns. User-defined types can not contain invisible attributes.
does CONCAT or || implicitly convert date or number types?
YES - implicitly converts all data types to string
common datetime format masks and some info
YYYY: 4 digit year YY: 2 digit year MM: Month (01 to 12) MON: Abbreviated month name MONTH: Name of month in characters (e.g. May)* D: Day of week (1 to 7) DAY: Name of day in characters (e.g. Monday)* DD: Day of month (01 to 31) DY: Abbreviated day name HH: Hour of day (01 to 12) MI: Minute of hour (00 to 59) SS: Second of minute (00 to 59) th/TH: ordinal number format (*its resultant capitalization in 'th' or 'TH' depends on associated data it is formatting, so e.g. mmTH='09th', MMTH = '09TH') sp: spells out data involved spth or thsp: spelled-out ordinal number format (example Ddspth = 'Fourteenth", ddspth = 'fourteenth') ----------------------------------------------- fm format prefix: Oracle will ignore padded blank characters and leading zeros (*names of days and months in output are automatically padded with blanks) ------------------------------------------------ The RR date format automatically calculates century from the SYSDATE function but allows the session user to enter the century.
REVOKE - notes
You cannot remove column specific privileges with a REVOKE statement. First, you must revoke the object privilege of all columns of the table or view, and then grant the column-specific privilege that you need.
Database Trigger
a block of PL/SQL code that runs automatically when a particular database event occurs A database trigger is tied to a database table or view and is implicitly fired by the database system when that table or view is affected with the associated DML action. deleting child table rows has no direct effect on the parent row unless you use triggers to enforce business logic
views - notes
a view needs to have column aliases for any constants or expressions in the view if re-creating a view, use CREATE OR REPLACE VIEW rows cannot be deleted through a view if the view definition contains the DISTINCT keyword an INSERT will not work with a view if it consists of aggregate rows, as defined by the GROUP BY clause in the view's SELECT statement. In other words, there is no way to add single-row values through the view since the view's access to the tables is, by definition, at the aggregate level. Scalar subqueries are okay. There are many reasons why any given VIEW may reject attempts to execute the INSERT, UPDATE, or DELETE statement. For example, if the VIEW object does not contain sufficient access to its underlying tables, then it might not provide all the column access required to satisfy the underlying constraint restrictions that may exist for any INSERT, UPDATE, or DELETE statement. While many views allow all of these SQL statements to work, it's entirely possible to create a VIEW that does not. ~~~~~~~~~~~~~~~~ FALSE: The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view
FLASHBACK TABLE ... TO BEFORE DROP
after a DROP TABLE... PURGE, it is not possible to recover the table structure, data, or the related indexes with FLASHBACK TABLE... TO BEFORE DROP
DBA_ data dictionary view prefix
all objects in the database
FROM_TZ
converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value. syntax: FROM_TZ (timestamp_value, time_zone_value) time_zone_value is a character string in the format 'TZH:TZM' or a character expression that returns a string in TZR with optional TZD format. The TIMEZONE_HOUR value refers to the current hour in the database's configured time zone. Note that the TIMEZONE_HOUR (and TIMEZONE_MINUTE) values can only be extracted from a timestamp with a time zone datatype. TIMEZONE_HOUR and TIMEZONE_MINUTE are specified together and interpreted as an entity in the format +|- hh:mm, with values ranging from -12:59 to +14:00 The following example returns a timestamp value to TIMESTAMP WITH TIME ZONE: SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', '3:00') FROM DUAL; FROM_TZ(TIMESTAMP'2000-03-2808:00:00','3:00') -------------------------------------------------------------- 28-MAR-00 08.00.00 AM +03:00
create invisible index - syntax
create index order_lines_inv on order_lines(ATTRIBUTE7) INVISIBLE; create index ix_emp on scott.emp(job) invisible tablespace index; --this syntax is also correct
create a view on a table called employee, where the hire_date column is invisible
create view vw_employee (empid, hd invisible) as select employee_id, hire_date from scott.employee;
DBA_TAB_PRIVS
data dictionary view DBA_TAB_PRIVS allows a user to see privileges that have been granted to itself or by itself to others.
Analytic Functions - syntax
function_name ( arguments ) OVER ( [query_partition_clause] [ORDER BY order_by_clause [windowing_clause] ] ) Example using RANK, showing the student details and their rank according to the fees_paid, grouped by gender: SELECT student_id, first_name, last_name, gender, fees_paid, RANK() OVER (PARTITION BY gender ORDER BY fees_paid) AS rank_val FROM student;
GV$
global dynamic performance views for multiple instances when using Real Application Clusters
ON DELETE CASCADE
goes with the foreign key constraint in the *child* table e.g. CONSTRAINT FK_ORD REFERENCES CUST_ORDER(ORD_ID) ON DELETE CASCADE
inline vs out-of-line syntax for constraints
in-line: declaration of the constraint is included with the column definition. not null constraints can only be declared inline ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ out-of-line: define a constraint within a CREATE TABLE statement after the columns have been created. Out of line constraints can have names specified So, why do we need to assign a name to a constraint? Having named constraints can be helpful in several situations. Without specifying the name, Oracle automatically generates a name for the constraint that it does for all inline constraints. Usually, this name does not provide any useful information. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ CHECK Constraints Can Refer to Multiple Columns with out-of-line syntax If you create a CHECK inline constraint, it can only refer to the column it's being created on. However, if you create a CHECK constraint as out of line, it can refer to multiple columns. Create the employee table with the CHECK constraint as shown below: CREATE TABLE employee ( emp_id NUMBER(10), first_name VARCHAR2(200) CHECK (LENGTH(first_name) > 10), last_name VARCHAR2(200), dept_id NUMBER(10) ); This constraint shows that first_name must exceed 10 characters long. However, what if we wanted to specify that the combination of first name and last name must exceed 10 characters? To do this, re-write the code as an out-of-line constraint: CREATE TABLE employee ( emp_id NUMBER(10), first_name VARCHAR2(200),, last_name VARCHAR2(200), dept_id NUMBER(10), CONSTRAINT ck_fullname_len CHECK (LENGTH(first_name || last_name) > 10) ); We can see that this rule can be implemented only with an out-of-line constraint.
V$PARAMETER
lists the name-value pairs of the init.ora file (or their default, if not in the init.ora file)
GRANT statement
main mechanism for providing access to a database ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Use the GRANT statement to grant: System privileges to users and roles. Table 18-1 lists the system privileges (organized by the database object operated upon). Roles to users, roles, and program units. The granted roles can be either user-defined (local or external) or predefined. For a list of predefined roles, refer to Oracle Database Security Guide. Object privileges for a particular object to users and roles. Table 18-2 lists the object privileges (organized by the database object operated upon). ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Valid statement for granting a session privilege: GRANT CREATE TABLE TO user1, user2; system privileges and object privileges cannot be granted together in a single GRANT statement FALSE: Role and system privileges cannot be granted together in a single GRANT statement using GRANT ALL, the keyword PRIVILEGES is not required for Object Privileges, but it is required for System ones.
transactions - notes
main points: -DDL and DCL commands are autocommitted -DML commands need to be managed -Transaction begins with first DML statement -Transaction ends when *COMMIT OR ROLLBACK is issued *DDL or DCL command issued *Command editor exits *System crashes SAVEPOINT can be placed in the transaction for ROLLBACK A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database. Oracle Database assigns every transaction a unique identifier called a transaction ID. All Oracle transactions obey the basic properties of a database transaction, known as ACID properties. ACID is an acronym for the following: Atomicity All tasks of a transaction are performed or none of them are. There are no partial transactions. For example, if a transaction starts updating 100 rows, but the system fails after 20 updates, then the database rolls back the changes to these 20 rows. Consistency The transaction takes the database from one consistent state to another consistent state. For example, in a banking transaction that debits a savings account and credits a checking account, a failure must not cause the database to credit only one account, which would lead to inconsistent data. Isolation The effect of a transaction is not visible to other transactions until the transaction is committed. For example, one user updating the hr.employees table does not see the uncommitted changes to employees made concurrently by another user. Thus, it appears to users as if transactions are executing serially. Durability Changes made by committed transactions are permanent. After a transaction completes, the database ensures through its recovery mechanisms that changes from the transaction are not lost. The use of transactions is one of the most important ways that a database management system differs from a file system. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ misc notes: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Each DDL statement forms a single transaction.
JOIN with USING clause
mutually exclusive to NATURAL JOIN!!! you can use the USING clause with multiple columns (per Khaled video clarification note)
list of mnemonics
order of syntax - "Such Fun, Why Go Home Oprah? Fudge!" SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, FETCH order of processing - "For Jack Would Grow Having Selected Dinner Over Fun." FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, FETCH operator precedence - "Not Again, Oprah!" NOT > AND > OR
list of non schema objects
public synonyms; roles, users
cumulative distribution
represents the probability that a variable falls within a certain range. Specifically, the cumulative distribution of x measures the probability that a variable is less than or equal to x. Cumulative distributions, as they are progressing through the data should approach 1.0 or 100% of its data.
What SQL statement will display all records in the EMP table as they looked at noon on 11/3/2017?
select * from scott.emp as of timestamp to_timestamp('11/03/2017 12:00:00','mm/dd/yyyy hh24:mi:ss');
correlated subquery - notes
sequence of events 1. The candidate row is fetched from the table specified in the outer query. 2. Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. 3. The WHERE clause of the outer query is evaluated. 4. This is repeated for the subsequent rows of the table, until all the rows are processed..
ROUND (date)
syntax: ROUND(date [, fmt ]) ROUND returns date rounded to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is rounded to the nearest day. The date expression must resolve to a DATE value. The following example rounds a date to the first day of the following year: SELECT ROUND (TO_DATE ('27-OCT-00'),'YEAR') "New Year" FROM DUAL; New Year --------- 01-JAN-01
ROUND (number)
syntax: ROUND(n [, integer ]) ROUND returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to 0 places. The argument integer can be negative to round off digits left of the decimal point. n can be any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The argument integer must be an integer. If you omit integer, then the function returns the same datatype as the numeric datatype of the argument. If you include integer, then the function returns NUMBER.
V$SQLTEXT_WITH_NEWLINES
this view can be used to construct the entire text for each session's actual SQL statement
V$DATAFILE
this view contains an entry for each datafile of the database
datetime math
timestamp - interval = timestamp
V$VERSION
use this view to find out the specific version of the database components
CREATE OR REPLACE VIEW
use when re-creating a view
CREATE TYPE
used to create a user-defined data type or an alias data type.
privileges - notes
using GRANT ALL, the keyword PRIVILEGES is not required for Object Privileges, but it is required for System ones.
ORDER BY clause - notes
with set operators, can only ORDER BY columns in first set with set operators if an alias is set up on a column, the column name can not be used in ORDER BY, must use the alias (or position number) -- for regular query this is not the case, can use either column name or the alias position number, alias/column names can be mixed up in ORDER BY can use columns not specified in SELECT in ORDER BY