C993 SQL
What is a literal?
A fixed data value. Also called a constant
What is an overloaded function?
A fucntion that shares a name with one or more functions, but where each is differentiated by their respective parameter lists
The output of a function may be used:
A function cannot replace a keyword within a SQL statement. Having said that, it is not uncommon to write a query that produces output that is itself SQL code and then execute those dynamically generated SQL statements. The original query uses string concatenation and other features to generate output that is itself a series of SQL statements. But process requires a combination of SQL and SQL*Plus features used together to produce output that is SQL code free of output headings, titles, and other extraneous content so that the output is composed of purely SQL statements. The process can "spool" output to a file that is executable and execute that file with a SQL*Plus statement to automatically execute those statements. But that approach includes features of SQL*Plus and it is outside the scope of this book.
What is a group function?
A function that returns one value for each set of zero or more rows it encounters. (the two types are aggregate and analytic functions).
What is a regular expression?
A language of pattern matching. Not to be confused with expressions. Oracle's support for regular expressions is consistent with the POSIX and Unicode standards.
What are pattern variables?
A pattern variable is a name you create and associate with a condition you specify. Pattern variables are used to search for matches by comparing data across multiple rows of data with comparison operators
What is a hierarchical query?
A query that specifies multiple levels of relationship. Typically built on a self-join. Note that a typical join of two tables in a parent-child relationship can be said to be a two-level hierarchy; technically that would be accurate. But the term hierarchical query in Oracle SQL is generally understood to indicate a particular type of query based on a data model that is capable of supporting more than two levels
LOB
Large Objects, large data types that cannot be primary keys.
What is another name for an aggregate function?
a multirow function
What are NLS parameters
They're Variables that customize the behavior of the database in accordance with a given locale. NLS_SORT is an example
NCLOB
Unicode Character Large Object (unicode, the alternative to ASCII)
What should be used to create user defined data types?
"CREATE TYPE"
What are the 2 characteristics of a primary key?
"Unique" and "Not Null"
BLOB
Binary Large Object (images or files)
What is a single-row function?
It's a function that returns one value for each one row it encounters. Also known as a scalar function
Define an explicit conversion
The use of a data type conversion function
"ON DELETE"
used with foreign keys
What are the 4 places group functions can be called from in a SELECT statement?
1 - the ORDER BY clause 2 - the select list 3 - GROUP BY clause 4 - HAVING clause
Consider the following: SELECT MOD(5,3), REMAINDER(5,3) FROM DUAL; Which of the following will be the result?
2, -1 :MOD divides the first number by the second and returns the remainder relative to the first number. REMAINDER does the same thing but returns the remainder relative to the second number.
What are the 2 major built-in functions of SQL?
>> Built-in functions are those that are part of the SQL language. They are available with every standard implementation of SQL. >> User-defined functions are those that are created by users. They are written with features that go beyond the capabilities of SQL, using languages such as PL/SQL. Their construction is beyond the scope of the exam and therefore this book. This book—and the exam—will deal only with built-in functions.
What are the 3 additional rules for automatic data type conversions?
>> Numeric values will generally not convert automatically to dates. >> Numeric and text data will generally not convert automatically to very large sized types, such as LOB, CLOB, RAW, and others. >> Very large types will generally not automatically convert to each other, such as from BLOB to CLOB, for example. Some will, but many won't.
Describe the DATE function.
>> Parameters: None >> Process: Returns the current date and time according to the operating system on which the Oracle database server is installed. In other words, if your SQL statement is running on an Oracle server instance from a remote location, then regardless of the location of you or your client, SYSDATE will return the date and time of the operating system on which the server resides. Time information is contained within SYSDATE but doesn't display by default; however, it can be extracted by way of the TO_CHAR conversion function, which we will formally discuss in Lesson 6, but which we include in some of the examples throughout this lesson. (Note: It can also be altered by changing the NLS_DATE_FORMAT session parameter.) Output: Date. >> Example: Show the current date according to the operating system where the Oracle server is installed.
Describe the function ADD_MONTHS
>> Syntax: ADD_MONTHS(d, n) Parameters: d is a date, required; n is a whole number, required. >> Process: Adds n months to d and returns a valid date value for the result. Output: Date. >> Example: Add one month to January 31, 2017, and four months to November 1, 2017.
What are the parameters and syntax for CEIL?
>> Syntax: CEIL(n) >> Parameters: n is required and is any numeric data type. >>Process: CEIL returns the smallest integer that is greater than or equal to n.
What are the parameters and syntax for FLOOR?
>> Syntax: FLOOR(n) >> Parameters: n is required and is any numeric data type. ?? Process: FLOOR returns the largest integer that is less than or equal to n.
Describe the function LAST_DAY
>> Syntax: LAST_DAY(d) Parameters: d is a date, required. >> Process: Returns the last day of the month in which d falls. Output: Date. >> Example: Show the last days of February in 2020 and 2021.
What are the parameters and syntax for MOD?
>> Syntax: MOD(n1, n2) >> Parameters: n1 and n2 are numbers. Both are required. >> Process: Performs the same task as REMAINDER, except MOD uses FLOOR instead of ROUND in its equation. Output: Numeric. >> Example: Get the MOD of the same three number pairs we tested with REMAINDER. Note the results in the third example—this might be what you would've expected with REMAINDER and didn't get—but you do get it with MOD.
Describe the function MONTHS_BETWEEN
>> Syntax: MONTHS_BETWEEN(d1, d2) Parameters: d1 and d2 are dates, required. >> Process: Determines the number of months between the two dates. The result does not round off automatically; if the result is a partial month, MONTHS_BETWEEN shows a real number result. Whole months are counted according to the calendar months involved; if the time spans, say, a February that has 29 days, then the one month time span for that time period will be 29 days. In other words:
Describe the function NEXT_DAY
>> Syntax: NEXT_DAY(d, c) >> Parameters: d is a date, required; c is a text reference to a day of the week, required. >> Process: Returns a valid date representing the first occurrence of the c day following the date represented in d. Output: Date. >>Example: Show the first occurrence of a Saturday following May 31, 2019.
What are the parameters and syntax for REMAINDER?
>> Syntax: REMAINDER(n1, n2) >> Parameters: n1 and n2 are numbers. Both are required. >> Process: Identifies the multiple of n2 that is nearest to n1 and returns the difference between those two values. Output: Numeric. >> Example: Test REMAINDER using three sequential numbers such as 9, 10, and 11, and compare each against the number 3. Since the first number (9) is a multiple of 3, there is no remainder, so the answer will be 0. The second number (10) represents one more number than the multiple, so the remainder is 1. Notice what happens with the third number (11)—the function doesn't return a 2 as you might expect. Instead, it returns a negative 1, because the nearest integer that's divisible by 3 is 12, which is closer to the 11 than the 9. In other words, REMAINDER identifies the closest multiple of n2. If the multiple is higher, REMAINDER returns a negative number to indicate that the closest multiple of n2 is higher than n1.
What are the parameters and syntax for ROUND?
>> Syntax: ROUND (n, i) >> Parameters: n is required, is any number, and can include decimal points. i is an integer and is optional—if omitted, it will default to 0. Process: n is rounded depending on the value of i. If i is zero, n is rounded off to the nearest whole number, in other words, zero decimal points. If i is a positive number, n is rounded to i places to the right of the decimal point. If i is a negative number, n is rounded to i places to the left of the decimal point. The number 5 is rounded away from zero. >> Output: If i is omitted, ROUND returns a value in the same numeric data type as n. If i is specified, ROUND returns a data type of NUMBER. >> Example: Round off 12.355143 to two significant digits to the right of the decimal, and also round off 259.99 to the nearest "tens," in other words, one digit to the left of the decimal.
Describe the ROUND (date) function.
>> Syntax: ROUND(d, i) Parameters: d is a date (required); i is a format model (optional). Process: d is rounded off to the nearest date value, at a level of detail specified by i. d is required and specifies a value of the DATE data type. i is a format model and specifies the level of detail to which the DATE value will be rounded—in other words, to the nearest day, nearest hour, nearest year, and so on. i is optional; if i is omitted, ROUND will default to a format model that returns a value of d rounded to the nearest whole day. Values are biased toward rounding up. For example, when rounding off time, 12 noon rounds up to the next day. Format models are covered in Lesson 6 when we discuss the TO_CHAR conversion function. But we'll include one in the example that follows to give you an idea of what a format model is. Output: Date. >> Example: This example shows a SELECT statement with three expressions in the select list. The first is SYSDATE, which returns the current date. The second is the same date, rounded to the nearest month, as specified by the 'MM' format model. The third is the same date rounded to the nearest year, as specified by the 'RR' format model.
What are the syntax and parameters of SOUNDEX(s)?
>> Syntax: SOUNDEX(s) >> Parameters: s = the source string, required. Process: Translates a source string into its SOUNDEX code. >> Output: Character string. >> Notes: SOUNDEX is a coding scheme for translating English words into sound-alike patterns. A single SOUNDEX value is relatively worthless. But two combined can be surprisingly helpful. The reason is that similar-sounding words tend to generate the same SOUNDEX pattern.
What are the syntax and parameters of SUBSTR (the function sub-string)?
>> Syntax: SUBSTR(s, pos, len) >> Parameters: s = a character string, required; pos = a number, required; len = a number, optional. Process: Extracts a substring from s, starting at the pos character of s and continuing for len number of characters. If len is omitted, then the substring starts as pos and runs through the end of s. If pos is negative, then the function starts at the end of the string and moves backward. Output: Character string. >> Example: Starting with a source string of 'Name: MARK KENNEDY', extract a substring out of it, beginning at the seventh position and running to the end of the string.
Describe the function TRUNC (Date)
>> Syntax: TRUNC(d, i) >> Parameters: d is a date (required); i is a format model (optional). >> Process: Performs the same task as ROUND for dates, except TRUNC always rounds down. Output: Date.
What are the parameters and syntax for TRUNC?
>> Syntax: TRUNC(n, i) >> Parameters: n is required, is any number, and can include decimal points. i is an integer and is optional—if omitted, it will default to 0. Process: TRUNC "rounds" toward zero; in other words, it truncates the numbers. >> Output: If i is omitted, TRUNC returns a value in the same numeric data type as n. If i is specified, TRUNC returns a data type of NUMBER. >> Example: Using the same numbers we just used with the ROUND example, truncate them instead.
What are the 3 sql function characteristics?
>> They may accept incoming values, or parameters (note that a few functions take no parameters). >> They incorporate parameter data into some sort of process; in other words, they perform some sort of task on the incoming data, such as a calculation or some other activity. >> They return one single answer as a result.
What are the 3 categories of SQL functions on the exam?
>> scalar, analytical, and aggregate functions. >>Scalar functions return one answer for every one row processed. >>Aggregate functions return one answer for a set of zero to multiple rows. >>Analytical functions fall somewhere in between these. They have the ability to return multiple rows from within a group of rows
What are the parameters of the TRIM function?
>> trim_info is one of these keywords: LEADING, TRAILING, BOTH—if omitted, defaults to BOTH. >> trim_char is a single character to be trimmed—if omitted, assumed to be a blank. >> trim_source is the source string—if omitted, the TRIM function will return a NULL.
What are the syntax and parameters of INSTR (the function in-string)?
>>Syntax: INSTR(s1, s2, pos, n) >>Parameters: s1 is the source string (required); s2 is the substring (required); pos is the starting position in s1 to start looking for occurrences of s2 (optional, default is 1); n is the occurrence of s2 to locate (optional, default is 1). If pos is negative, the search in s1 for occurrences of s2 starts at the end of the string and moves backward.
Functions can be called from anyplace that an expression can be called such as...
A SELECT statement's select list and WHERE clause An INSERT statement's list of values An UPDATE statement's SET clause and WHERE clause A DELETE statement's WHERE clause ... and more.
What is a function?
A set of code that performs a particular task and returns a single result. A SQL function can be used within a SQL expression. A function is one type of subprogram, also known as a program unit.
What is an escape character?
A single character that can take on an alternative purpose separate from its character's standard meaning. For example, a single quotation mark is an escape character when preceding another single quotation mark in a text string delimited by single quotes so that strings such as 'O''Brian' will be correctly interpreted as O'Brian in the database, rather than the truncated string 'O' followed by the characters Brian'—which would be meaningless in any SQL statement and would result in a syntax error
What is an escape character?
A single character that can take on an alternative purpose separate from its character's standard meaning. For example, a single quotation mark is an escape character when preceding another single quotation mark in a text string delimited by single quotes so that strings such as 'O''Brian' will be correctly interpreted as O'Brian in the database, rather than the truncated string 'O' followed by the characters Brian'—which would be meaningless in any SQL statement and would result in a syntax error.
In a rare situation where a row contains nothing but NULL values in its columns, will COUNT* still count that row?
Although the COUNT function counts occurrences of data, ignoring NULL values, the COUNT* (with an asterisk) function will still count a row with a NULL value.
Analytic functions are processed:
Analytic functions are the last set of operations performed before the ORDER BY. For this reason, they must be specified in the SELECT list or ORDER BY only, not the WHERE, GROUP BY, or HAVING clauses
Where do Analytic functions belong the query?
Analytic functions are the last set of operations performed in a query prior to the ORDER BY clause, which is the final processing step. For this reason, you cannot include analytic functions anywhere other than the SELECT list or the ORDER BY clause. In other words, analytics are forbidden in the WHERE, HAVING, and GROUP BY clauses.
What are some of the more commonly used aggregate functions?
COUNT, SUM, MIN, MAX, AVG and MEDIAN
The PERCENTILE_CONT function:
Can be used with PARTITION BY to specify groups of data within a single SELECT statement
CLOB
Character Large Object(Large Text Data Elements)
What are 2 limitations to Conversion functions?
Conversion functions cannot change the data type of a column to something else. They cannot create user-defined data types.
Which function is often referred to as the IF-THEN-ELSE of Oracle SQL
DECODE
Which functions are often referred to as the IF-THEN-ELSE of Oracle SQL?
DECODE, and also CASE
"NOT NULL"
Ensures where applied, will always be provided with a value
What is considered the "heart and sole" of relational DB's?
Foreign Keys
What are analytic functions?
Functions that operate on a group of rows (known as a window) and return multiple rows for each group. Sometimes called analytical functions
Define implicit conversion
Implicit conversion occurs automatically in circumstances where SQL will determine on its own that a data type conversion is required but not specified. When this happens, SQL will perform the data type conversion automatically.
What happens if there are 2 NULL values in DECODE?
In DECODE, two NULL values are considered to be equivalent. NULL compared to NULL will produce a TRUE result and send the corresponding value back if required. In other words, the use of NULL within DECODE is interpreted as a comparison of IS NULL as opposed to = NULL. Remember that = NULL will always return a false since NULL is unknown; think of it as "I don't know."
What does the function NULLIF do?
It compares a subject value with a candidate value and returns NULL if both values are identical. This is useful in reports analyzing discrepancies.
What is a Primary Key?
It defines 1 or more columns in a table that will form the unique identifier for each row of data added to the table.
What does the term NULL describe?
It describes the lack of a value
What is the DUAL table?
It isn't a function issue per se but is helpful to our purposes here. It is not a "SQL" thing, nor is it necessarily a "function" thing, but it's an "Oracle" thing you should know before we continue. The DUAL table is present in every Oracle database. This special table contains just one column. The column is named DUMMY, and it has a data type of VARCHAR2(1). The DUAL table contains only one row. That row has a value in DUMMY of 'X'. The purpose of DUAL is simple—to have something to run a SELECT statement against when you don't want to retrieve any data in particular but instead simply want to run a SELECT statement to get some other task accomplished, such as obtaining the output of a function. For example, you'll soon see in this section that there's an Oracle SQL function called SYSDATE that displays the current date according to the operating system of the server on which the Oracle database is installed. If you want to get the value of SYSDATE without wading through a bunch of table data from your application, you can simply execute the following SQL statement:
How does the NVL function work?
It takes two parameters that are both expressions. If the first expression evaluates to a non-NULL value, it is returned as is, but if it evaluates to NULL, the second expression is returned
What is the most important portion of a foreign key?
It's not the "Foreign Key" its the REFERENCES
Describe a USER function
Its a standard function that takes no parameters and simply returns the value showing the name of the current user account.
Which format mask returns the local currency symbol?
L is the local currency symbol format mask.
WHAT IS NULL - 4?
NULL
What is 14 + NULL?
NULL
Which function is good for comparing multiple rows wherein an older and newer version of a particular value exist and you want to cull out those that are still not updated or perhaps have already been updated?
NULLIF
What function is useful to returning rows of data that might contain NULL values where you want the NULL to be replaced with something more relevant to your immediate purposes, perhaps a numeric zero, for example.
NVL (NULL VALUE)
You are tasked to create a SELECT statement to subtract five months from the hired date of each employee in the EMPLOYEES table. What function will you use?
None of these. The function you want is ADD_MONTHS so that you can add negative five months to the current date.
What categories do most data types fall into?
Numeric (NUMBER), text (VARCHAR2), and dates and/or times (DATE, TIMESTAMP, etc.)
Describe Numeric Functions
Some numeric functions are pretty simple such as ABS, which takes a single numeric value and returns its absolute value. There's also SQRT, which takes a single numeric value and returns its square root. Not all of the input parameters to numeric functions are necessarily numeric, but the overall intent of these functions is to perform numeric analysis and perform the sort of tasks typically associated with numeric manipulation and data processing.
Describe the function NUMTOYMINTERVAL
Syntax: NUMTOYMINTERVAL (n, interval_unit) Parameters: n = number (required). interval_unit = one of the following values: 'YEAR' or 'MONTH'. Process: Converts date information in numeric form into an interval value of time. Output: A value in the INTERVAL YEAR TO MONTH data type. Example: The following example takes the number 27 and transforms it into a value representing a time interval of 27 months, which equates to 2 years and 3 months, in the INTERVAL YEAR TO MONTH data type. The 2-3 value shows 2 years and 3 months is the amount of time that results.
You need to determine the day of the week for a particular date in the future. Which function will reveal this information?
TO_CHAR
Describe the function NUMTODSINTERVAL
Syntax: NUMTODSINTERVAL (n, interval_unit) Parameters: n = number (required). interval_unit = one of the following: 'DAY', 'HOUR', 'MINUTE', or 'SECOND'. Process: Converts date information in numeric form into an interval value of time. Output: A value of the data type INTERVAL DAY TO SECOND. Example: The following example translates 36 hours into its formal representation of 1 day and 12 hours in the data type INTERVAL DAY TO SECOND, which displays a single number for day, followed by hours, minutes, seconds, and fractional seconds.
What are the most commonly used conversion functions?
TO_NUMBER, TO_CHAR, and TO_DATE
The LEAD function returns data from:
The LEAD function returns data from the row specified by the LEAD function's offset. The offset defaults to 1 but may be specified within the LEAD function
The ORDER BY in an OVER clause:
The ORDER BY in an OVER clause operates independently of the ORDER BY in the SELECT statement.
Review this SQL statement: SELECT SUBSTR('2009',1,2) || LTRIM('1124','1') FROM DUAL; What will be the result of the SQL statement?
The SUBSTR function will extract data from the string ('2009') starting at the first position (1) and lasting for 2 characters (2), resulting in an answer of '20'. The LTRIM function will trim off all occurrences of the 1 from the left side of '1124', resulting in 24. The two results are concatenated together for a final result of 2024.
If you want to display a numeric value with dollar signs and commas, which of the following is the best approach to take?
The TO_CHAR function with a format model
Review this SQL statement: SELECT MONTHS_BETWEEN(LAST_DAY('15-JAN-12')+1,'01-APR-12')FROM DUAL; What will result from this query?
The answer will be -2. First, the LAST_DAY function will transform the value of '15-JAN-12' to '31-JAN-12', and then the result of that will be added to 1, so that the first of February will result: '01-FEB-12'. The difference between that date and '01-APR-12' will be a negative 2.
Which functions are used to convert interval data types INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH?
The functions TO_DSINTERVAL and TO_YMINTERVAL
What is the purpose of the NVL function?
The purpose of the NVL function is to replace any NULL values with something you specify. For example, you might query a table of names and want to replace any missing middle names with the expression "NMN" (for no middle name). NVL is ideal for queries in which some values might return a NULL but you prefer instead to return an alternative value.
Review this SQL statement: SELECT TRUNC(ROUND(ABS(-1.7),2)) FROM DUAL; What will be the result of the SQL statement?
The result will be 1. The ABS function determines absolute value. As the innermost function, it will be evaluated first, resulting in an answer of 1.7. The ROUND function will process the value of 1.7 and round it to the nearest two digits to the right of the decimal point, and the result will still be 1.7. Finally, TRUNC will truncate the value down to a one.
What is true of functions?
They always return a single value.
What can number functions do?
They can perform mathematical analysis. SQL comes with many functions for determining sine (SIN, ASIN, SINH), cosine (COS, ACOS, COSH), and tangent (TAN, ATAN, ATAN2, TANH). You can determine absolute value (ABS) or determine whether a given number is positive or negative (SIGN).
What do Conversion functions perform?
They perform data type transformation from one general type such as numeric to another such as text. Conversion functions don't change values; they change the value's data type.
What are Foreign keys?
They work in conjunction with the table's primary keys and help ensure that two tables can "relate" to each other.
When are aggregate functions typically used for?
They're typically used with a SELECT statement that selects many rows, where the aggregate function scans a set of rows and returns a single answer for them all.
What are Character functions used for?
They're used to manipulate text. They can be used to perform many jobs on a given string such as analyze its length (LENGTH), pad it with extra characters (RPAD, LPAD), trim off unwanted characters (RTRIM, LTRIM, TRIM), locate a given string within a larger string (INSTR), extract a smaller string from a larger string (SUBSTR), and replace text within a string (REPLACE). It's even possible to search for strings that aren't necessarily spelled the same but that sound alike (SOUNDEX), and more. When these are combined, the possibilities are theoretically endless.
What are the Character functions UPPER and LOWER used for?
They're useful when you are doing a text search and aren't sure whether the data in the table is in uppercase, lowercase, or both. You can use UPPER to force a conversion of all data in the table and then compare that result to an uppercase literal value, thus eliminating any chance of missing mixed-case text in the table.
How can Foreign keys help with "referential integrity"?
When a Foreign key is used, any future values added to that Foreign Key in the child are already stored in the corresponding parent table columns.
What are Nested Functions?
When a function is placed within an expression in such a way that its output becomes the parameter value for another function of that same expression, the original function is said to be nested. When one function is nested within another, the nested function executes first. The nested function is also considered the inner function, as opposed to the outer function, which receives the output of the inner function as an input parameter. When functions are nested at multiple levels, the innermost functions execute first. You can nest multiple functions and at multiple levels—so you can nest a function within a function, and nest the combination within yet another function.
What is standard deviation?
When used with numeric analysis, standard deviation is a quantified degree of variation of individual values within a large set of values. The formula for standard deviation takes the square root of the variance. See variance for more information
what is a variance?
When used with numeric analysis, variation quantifies the degree of variation of individual values within a large set of values. Variance is determined by averaging a set of values, subtracting each value from the average, squaring each difference, and summing the result
What are some features of Date manipulation?
You can obtain the current date and time (SYSDATE, SYSTIMESTAMP), "round" off dates to varying degrees of detail (ROUND), and otherwise abbreviate them (TRUNC). You can calculate the differences between two or more dates in many ways. Simple arithmetic operators will help determine the differences between two dates in terms of days, meaning that if you subtract one date from another, the resulting answer will be a number representing the difference in terms of days. But what if you want something else, like—the difference in terms of months? (The answer is MONTHS_BETWEEN.) There are functions that will assist in managing such tasks. You can add or subtract an entire month and account for spans of time that encompass years (ADD_MONTHS with a positive number to add or a negative number to subtract—we'll see this later). What if you have a particular date and want to know whether that date falls on, say, a Saturday? This sort of feature can be accomplished with "conversion" functions, which we discuss in the next lesson. Other Functions
Null
absence of information (in SQL) or unknown
What do the letters stand for in Number(n, m)?
n = precision (# of significant digits) and m = scale (# of digits right of the decimal)
How many primary keys can a table have?
only 1
