Term
|
Definition
| Returns all rows selected by either querying including all duplicates |
|
|
Term
|
Definition
| return all distinct rows selected by either queries |
|
|
Term
|
Definition
A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE
statement or inside another subquery. A subquery can return a set of rows or just one
row to its parent query. A scalar subquery is a query that returns exactly one value:
a single row, with a single column. Scalar subqueries can be used in most places in
a SQL statement where you could use an expression or a literal value. |
|
|
Term
| types of problems that subqueries can solve |
|
Definition
- comparison purposes
- star transformation
- Generate values for Projection
- generate rows to be passed to a DML Statement
- |
|
|
Term
| List of types of subqueries |
|
Definition
-Single-row
-Multiple-row
-Correlated |
|
|
Term
comparison operators
valid for single-row subqueries |
|
Definition
|
|
Term
comparison operators
valid for multiple-row subqueries |
|
Definition
|
|
Term
|
Definition
| A correlated subquery is a subquery that contains a join to an outer query. The join may be to the immediate outer query or the outermost query. |
|
|
Term
| The Capabilities of SQL SELECT statement |
|
Definition
projection - refers to the restriction of attributes selected from a relation or table.
selection- refers to the restriction of the tuples or rows selected from a relation(table).
joining - a relational concept, refers to the interaction of tables with each other in a query. |
|
|
Term
|
Definition
| extends the Select statement by providing the language to restrict rows returned based on one or more conditions. |
|
|
Term
|
Definition
| facilitates range-based comparison to test whether a column value lies between two values |
|
|
Term
|
Definition
| tests set memberships, so a row is returned if the column value tested in the condition is a member of a set of literals |
|
|
Term
|
Definition
| allowing components of character column data to be matched to literals conforming to a specific |
|
|
Term
|
Definition
| which returns rows where the column value contains a null value |
|
|
Term
|
Definition
| merges conditions into one larger condition to which a row must conform to be included in the result set. |
|
|
Term
|
Definition
| separates multiple conditions, at least one of which must be satisfied by the row selected to warrant inclusion in the result set. |
|
|
Term
|
Definition
| negates conditional operators |
|
|
Term
|
Definition
| to transform the output of a query into more practical, user friendly sorted data |
|
|
Term
| single ampersand substitution |
|
Definition
| The ampersand(&) character is the symbol chosen to designate a substitution variable in a statement and precedes the variable name with no space between them. |
|
|
Term
| double ampersand substitution |
|
Definition
| requires user input only once per occurrence of a substitution variable, since it defines a session-persistent variable with the given input value. |
|
|
Term
|
Definition
| session-persistent variable may be set explicitly |
|
|
Term
|
Definition
| controls whether SQL*Plus displays old and new versions of statements lines which contain substitution variable |
|
|
Term
|
Definition
| Returns the ASCII decimal equivalent of a character |
|
|
Term
|
Definition
| Returns the character given the decimal equivalent |
|
|
Term
|
Definition
| Concatenates two strings; same as the operator || |
|
|
Term
|
Definition
| Returns with the first letter of each word in uppercase |
|
|
Term
|
Definition
| FINDS the numeric starting position of a string within a string |
|
|
Term
|
Definition
| same as INSTR but counts the bytes instead of characters |
|
|
Term
|
Definition
| returns the length of a string in characters |
|
|
Term
|
Definition
| returns the length of a string in bytes |
|
|
Term
|
Definition
| converts a string to all lowercase |
|
|
Term
|
Definition
| left-fills a string to a set length using a specified character |
|
|
Term
|
Definition
| Strips leading characters from a string |
|
|
Term
|
Definition
| performs substring search and replace |
|
|
Term
|
Definition
| right-fills a string to a set length using a specified character |
|
|
Term
|
Definition
| strips trialing character from a string |
|
|
Term
|
Definition
| returns a phonetic representation of a string |
|
|
Term
|
Definition
| returns a section of the specified string, specified by numeric character positions |
|
|
Term
|
Definition
| returns a section of the specified sting, specified by numeric byte positions |
|
|
Term
|
Definition
| performs character search and replace |
|
|
Term
|
Definition
| strips leading , trailing or both leading and trailing character from a string |
|
|
Term
|
Definition
| converts a string to all uppercase |
|
|
Term
|
Definition
| returns the absolute value |
|
|
Term
|
Definition
|
|
Term
|
Definition
|
|
Term
|
Definition
|
|
Term
|
Definition
| returns the arc tangent; takes two inputs |
|
|
Term
|
Definition
| return the result of a bitwise AND on two inputs |
|
|
Term
|
Definition
| returns the next higher integer |
|
|
Term
|
Definition
|
|
Term
|
Definition
| returns the hyperbolic cosine |
|
|
Term
|
Definition
| returns the base of natural logarithms raised to a power. |
|
|
Term
|
Definition
| returns the next smaller integer |
|
|
Term
|
Definition
| returns the natural logarithm |
|
|
Term
|
Definition
|
|
Term
|
Definition
| returns the modulo(remainder) of a division operation |
|
|
Term
|
Definition
| returns an alternate number if the value is Not a number |
|
|
Term
|
Definition
| returns a number raised to an arbitrary power |
|
|
Term
|
Definition
| Adds a number of months to a date |
|
|
Term
|
Definition
| returns the current date and time in a DATE datatype |
|
|
Term
|
Definition
| Returns the database's time zone |
|
|
Term
|
Definition
| returns a component of a data/time expression |
|
|
Term
|
Definition
| Returns a timestamp with a time zone for a given timestamp |
|
|
Term
|
Definition
| returns the last day of a month |
|
|
Term
|
Definition
| returns the current data and time in the session time zone |
|
|
Term
|
Definition
| returns the number of months between two dates |
|
|
Term
|
Definition
| returns the data/time in a different time zone |
|
|
Term
|
Definition
| returns the next day of a week following a given date |
|
|
Term
|
Definition
|
|
Term
|
Definition
| returns the time zone for the current session |
|
|
Term
|
Definition
| returns the UTC(GMT) for a timestamp with a time zone |
|
|
Term
|
Definition
| returns the current data and time in the DATE datatype |
|
|
Term
|
Definition
| returns the current timestamp in the TIMESTAMP datatype |
|
|
Term
|
Definition
| TUNRcates a date to a given granularity |
|
|
Term
|
Definition
| returns the offset from UTC for a time zone name |
|
|
Term
|
Definition
| Converts characters to ASCII |
|
|
Term
|
Definition
| Converts a string of bits to a number |
|
|
Term
|
Definition
|
|
Term
|
Definition
| casts a character to the ROWID datatype |
|
|
Term
|
Definition
|
|
Term
|
Definition
| Decomposes a Unicode string |
|
|
Term
|
Definition
| converts from one character set to another |
|
|
Term
|
Definition
| cast a hexidecimal to raw |
|
|
Term
|
Definition
| converts a number value to an interval day to second literal |
|
|
Term
|
Definition
| converts a number value to an interval year to month literal |
|
|
Term
|
Definition
| Casts a raw to a hexidecimal |
|
|
Term
|
Definition
| casts a ROWID to a character |
|
|
Term
|
Definition
| converts an SCN to corresponding timstamp of the change |
|
|
Term
|
Definition
| converts timestamp to an SCN |
|
|
Term
|
Definition
| converts input into a Binary_DOUBLE number |
|
|
Term
|
Definition
| converts input into a BINARY_FLOAT number |
|
|
Term
|
Definition
| converts and formats a date into a string |
|
|
Term
|
Definition
| converts character input or NCLOB input to CLOB |
|
|
Term
|
Definition
| converts a string to a data, specifying the format |
|
|
Term
|
Definition
| converts a character string value to an interval data to second literal |
|
|
Term
|
Definition
| converts LONG or LONG RAW values to CLOB or BLOB datatype |
|
|
Term
|
Definition
| converts a single-byte character to its corresponding multibyte equivalent |
|
|
Term
|
Definition
| converts a string to a number, specifying the format |
|
|
Term
|
Definition
| converts a multibyte character to its corresponding single_byte equivalent |
|
|
Term
|
Definition
| converts character string to a TIMESTAMP value |
|
|
Term
|
Definition
| converts character string to a TIMESTAMP WITH TIME ZINE value |
|
|
Term
|
Definition
| converts a character string value to an interval year to month literal |
|
|
Term
|
Definition
|
|
Term
|
Definition
|
|
Term
|
Definition
| Epoch inticator with periods |
|
|
Term
|
Definition
|
|
Term
|
Definition
| Meridian indicator with periods |
|
|
Term
|
Definition
|
|
Term
|
Definition
|
|
Term
|
Definition
|
|
Term
|
Definition
|
|
Term
|
Definition
| when the source and target tables share identically named columns |
|
|
Term
|
Definition
| when there are identical columns names in the source and target tables and you want yo exclude as join columns |
|
|
Term
|
Definition
| allows join columns to be explicitly stated. the most widely used natural join format |
|
|
Term
|
Definition
| where at least one row in the target table is joins |
|
|
Term
|
Definition
| this join creates an output for every combination of the source and target table rows |
|
|
Term
|
Definition
| match column values from different tables based on the inequality expression |
|
|
Term
|
Definition
| associating rows with each other based on a hierarchical relationship require oracle to join a table to itself |
|
|
Term
|
Definition
If a left outer join
is performed then rows excluded by the
inner join, to the left of the JOIN keyword,
are also returned. |
|
|
Term
|
Definition
If a right outer join is
performed then rows excluded by the inner
join, to the right of the JOIN keyword, are
returned as well. |
|
|
Term
|
Definition
| returns the combined results of a left and right outer join. |
|
|
Term
|
Definition
| A value coded in base 64 that is the pointer to the location of row in a table. encrypted. Within it is the exact physical address. ROWID is an Oracle proprietary data type, not visible unless specifically selected. |
|
|
Term
|
Definition
| Like LONG, but binary data that will not be converted by Oracle Net. Any LONG RAW columns should be converted to BLOBs |
|
|
Term
|
Definition
| Character data in the database character set, up to 2GB. All the functionality of Long(and more) is provided by CLOB; LONG should not be used in a modern database and if your database has any columns of this type they should be converted to CLOB |
|
|
Term
|
Definition
| A locator pointing to a file stored on the operating system of the database server. THe of the files is limited to 4GB. |
|
|
Term
|
Definition
| Like CLOB, but binary data that will not undergo character set conversion by Oracle net. |
|
|
Term
|
Definition
| like CLOB, but the data is stored in the alternative national langage character set, one of the permitted Unicode character sets. |
|
|
Term
|
Definition
| Character data in the database character set, size effectively unlimited:4GB multiplied by the database block size |
|
|
Term
|
Definition
| used for recording a period in days and seconds between two Dates or TIMESTAMPs |
|
|
Term
|
Definition
| used for recording a period in years and months between two DATEs and TIMESTAMPs |
|
|
Term
| Timestamp with local Timezone |
|
Definition
| like TIMESTAMP, but the data is normalized to the database time zone on saving. When |
|
|
Term
|
Definition
| like, TIMESTAMP but the data is stored with a record kept of the time zone to which is refers. The length may be up to 13 bytes, depending on precision, This data type lets Oracle determine the difference between two times by normalizing then to UTC even if the times are for different time zones. |
|
|
Term
|
Definition
| this length zero if the column is empty or up to 11 bytes depending on the precision of up to 9 decimal places for the seconds, 6 places by default. |
|
|
Term
|
Definition
| This is either length zero, if the column is empty or 7 empty. All data includes century, year, month, day, hour, minute, and second. |
|
|
Term
|
Definition
| equivalent to Number, with scale zero |
|
|
Term
|
Definition
| this an ANSI data type, floating-point number with precision of 126 binary(or 38 decimal). Oracle also provides Binary_FLOAT and Binary_Double |
|
|
Term
|
Definition
| numeric data for which you can specify precision and scale |
|
|
Term
|
Definition
| variable-length binary data, from 1 byte to 4KB. UNlike to CHAR and VARCHAR2 data, RAW is not converted |
|
|
Term
|
Definition
| fixed-length character data , from byte to 2KB, the database character set, if the data is not the length of the column that is will be padded with spaces |
|
|
Term
|
Definition
| the data is stored in the alternative national language character set, one of the permitted Unicode character sets. |
|
|
Term
|
Definition
| variable-length character data, from 1 byte to 4KB. The data is stored in the database character set. |
|
|
Term
|
Definition
| nominates a columns for which the value must be different for every row in teh table |
|
|
Term
|
Definition
| this forces values to be entered into the key column. |
|
|
Term
|
Definition
| is the the means of locating a single row in a table. |
|
|
Term
|
Definition
| is defined on the child table in a parent-child relationship |
|
|
Term
|
Definition
| can be used to enforce simple rules, such as that the value entered in a column must be within a range of values |
|
|
