SQL BOOK NOTES

IN operator

The IN operator test whether an item is a member of a set of literal values.

LAST_DAY

The LAST_DAY function returns the date of the last day in the month a specified day belongs to.

LAST_DAT(date1)

The LAST_DAY(date1) function returns the last day of the month that the specified date falls into. EX:last_day('01-FEB-2008','01-JAN-2008')= 29-FEB-2008

Date Arithmetic

date1-date2=num1 date1-num1=date2 date1=date2+num1

LOWER

makes selected text lowercase EX: lower('SQL') = sql

LENGTH(string)

The LENGTH(string) function uses a character string as an input parameter and returns a numeric value representing the number of characters present in that string. EX: length ('A short string') = 14

LIKE operator

The LIKE operator is designed exclusively for character data and provides a powerful mechanism for searching for letter or words.

MOD(dividend, divisor)

The MOD(dividend,divisor) returns the remainder of a division operation: mod(42,10) = 2

MOTHS_BETWEEN(date1, date2)

The MONTHS_BEWEEN(date1, date2) function retursn the number of monhts between two dates. EX: months_between('01-FEB-2008','01-JAN-2008)=1

NEXT_DAY(date1, day of the week)

The NEXT_DAY(date 1, day of the week) returns the date on which the next specified day of the week falls after the given date. EX: next_day('01-FEB-2008','Friday') = 08-FEB-2008

DESCRIBE command syntax

DESC[RIBE]<SCHEMA>.tablename

LPAD(string, length after padding, padding string)

The LPAD(string, length after padding, padding string) functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

NOT Operator

The NOT operator negates conditional operators.

NULLIF

The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested.

NVL

The NVL function evaluates whether a column or expression of any data type is null or not. If the term is null, an alternative not null value is returned; otherwise, the initial term is returned.

NVL2

The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or express of any data type is null or not.

OR operator

The OR operator separates multiple conditions, at least one of which must be satisfied by the row selected to warrant inclusion in the results set.

REPLACE(string, search item, replacement item)

The REPLACE(string, search item, replacement item) function locates the search item in a given string and replaces it with the replacement item, returning a string with replaced values. EX:replace('#PASSWORD#','WORD','PORT') = #PASSPORT#

SUBSTR(string, start position, number of characters)

The SUBSTR(string, start position, number of characters) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specificed start position: EX: substr('http://www.domain.com' , 12,6)= domain

SYSDATE

The SYSDATE function takes no parameters and returns the current system date and tiem according ot the database server.

TO_CHAR

The TO_CHAR function returns an item of data type VARCHAR2.

TRIM

The TRIM function removes character form the beginning or end of character literal, columns or expressions to yield one potentially shorter character item.

TRUNC(number, decimal precision)

The TRUNC(number, decimal precision) function drops off or truncates the number given a decimal precision value. EX: trunc(42.39,1)=42.3

VERIFY

The VERIFY command controls whether the substitution variable submitted is displayed onscreen so you can verify that the correct substitution has occurred.

WHERE clause

The WHERE clause extends the SELECT statement by providing the language to restrict rows returned based on one or more conditions.

Single Ampersand Substitution

The ampersand character (&) is the symbol chosen to designate a substitution variable in a statement and precedes the variable name with no spaces between them.

To explore your database environment further, you would like a list of tables, owned by your current schema, available for you to query. How do you interrogate the database dictionary to provide this metadata?

The data dictionary is a set of tables and views of other tables that can be queried via SQL. The statement SELECT TABLE_NAME from USER_TABLES; queries that database dictionary for a list of tables names that belong to the current user.

searched CASE expression

The searched CASE expression lists a separate condition for each comparison expression.

simple CASE expression

The simple CASE expression lists the conditional search item once, and equality to the search item is tested by each comparison expression

DESCRIBE COMMAND

The structural metadata of a table may be obtained by querying the database for the list of columns that comprise it using the DESCRIBE command.

When working with character literal values that include single quotation marks, how should you specify these literals in the SELECT clause without raising an error?

There are two mechanism available. The more common approach is to replace each naturally occurring single quote with two single quotes. The other approach is to make use of the alternate quote operator to specify an alternate pair of characters with which to enclose character literals.

NVL syntax

NVL(original, ifnull)

You want to construct and execute queries against tables stored in an Oracle database. Are you confined to using SQL*Plus or SQL developer?

No.

Are nested functions evaluated from the outermost level to the innermost level?

No. Nested functions are resolved from the innermost nested level moving outward.

Must all functions in a nested expression return the same data type?

No. The data types of the parameters of nested functions may be different from each other. It is important to ensure that the correct data types are always supplied to functions to avoid errors.

You have a complex query with multiple conditions. Is there a restriction on the number of conditions you can specify in the WHERE clause? Is there a limit to the number of comparison operators you can use in a single query?

No. You may specificity any number of conditions in the WHERE clause separated by the Boolean operators. There is no limit when using the comparison operators, and they may be specified multiple times if necessary in a single query.

!=

Not equal to

<>

Not equal to

ROUND(date, date precision format)

ROUND(date, date precision format) rounds a given date value to the nearest date precision format.

SELECT statement

Retrieves information from a database table.

DECODE

The DECODE function implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal an optionally returns another term if they are not.

Order of Precedence

() / * - + || =,<,>,<=,>= [NOT] LIKE, IS [NOT] NULL, [NOT] IN [NOT] BETWEEN !=,<> NOT AND OR

When querying the JOBS table for every row containing just the JOB_ID and MAX_SALARY columns, is a projection, select, or join being performed?

A projection is performed since the columns in the JOBS table have been restricted to the JOB_ID and MAX_SALARY columns.

By restricting the rows returned form he JOBS table to those which contain the value SA_REP in the JOB_ID column, is a projection, selection or join performed?

A selection is performed since the rows are restricted.

Ascending & Descending Sorting

Ascending id the default of the ORDER BY clause, Descending can be achieved by appending the keyword DESC to the ORDER BY clause

COALESCE syntax

COALESCE(expr1. expr2, ... , exprn)

DEFINE command

Can be used to retrieve a list of all the variables currently defined in your SQL session; also be used to explicitly define a value for a variable referenced as a substitution variable by one or more statements during the lifetime of that session.

TO_NUMBER

Converts characters to numbers.

DATE FORMATS

DD-Day of the month MON-Month of the year YY-Two-digit year YYYY-Four-digit year RR-Two-digit year (Year 200-compliant) CC-Two-digit century HH-Hours with AM and PM HH24-Twenty-four-hour time MI-Minutes SS-Seconds

DECODE syntax

DECODE(expr1, comp1, iftrue1, [comp2,ifture2...]...[ compN, iftrue]], [iffalse])

DEFINE syntax

DEFINE; DEFINE variable=value;

Alternative Quote Operator

Double Quote marks as column heading

Operator: >

Greater than

Operator: >=

Greater than or equal to

INSTR(source string, search iteam, [start position],[nth occurrence of search item])

INSTR(source string, search iteam, [start position],[nth occurrence of search item]) function returns a number that represents the position in the source string, beginning form the given start position, where the nth occurrence of the search item begins. EX:instr('http://www.domain.com','.',1,2)=18

An alias provides a mechanism to rename as column or an expression. Under what conditions should you enclose an alias in doubles quotes?

If an alias contains more that one word or if the case of an alias must be preserved, then it should be enclosed in double quotation marks. Failure to double quote a multi worded alias will raise an Oracle error. Failure to double quote a single-word alias will result in the alias being returned in uppercase.

Operator: <

Less than

Operator: <=

Less than or equal to

INITCAP

Makes first character of selected text uppercase. EX: initcap('sql') = Sql

UPPER

Makes selected text uppercase EX: upper('sql') = SQL

ORDER BY clause

Mechanism used to sort the retrieved data set.

basic SELECT statement syntax

SELECT * |{[DISTINCT] column | expression [alias],...} FROM table;

UNDEFINE command

Session variables can be removed with the UNDEFINE command.

TO_CHAR syntax

TO_CHAR(number1, [format], [nls_paramter])

TO_NUMBER syntax

TO_NUMBER (string1, [format], [nls_parameter])

TRUNC(date, date precision format)

TRUNC(date, date precision format) truncates a given date value to the nearest date precision format.

ADD_MONTHS(date1, number of months)

The ADD_MONTHS(date1, number of months) returns the date resulting from adding a specified number of months to a date. EX:add_months('01-JAN-2008', 1) = 01-FEB-2008

AND operator

The AND operator merges conditions into one larger condition to which a row must conform to be included in the results set.

BETWEEN operator

The BETWEEN operator tests whether a column or expression value falls within a range of two boundary values.

COALESCE

The COALESCE function returns the first nonnull value from its parameter list. If all its parameters are null, then null is returned.

CONCAT(string1, string2)

The CONCAT (string1, string2) function takes two strings and concatenates or joins them in the same way that the concatenation operator || does. EX: concat('SQL is','eary to learn.') = SQL is easy to learn.

You are constructing an arithmetic expression that calculates taxable income based on an employee's SALARY and COMMISSION_PCT columns, both of which are nullable. Is it possible to convert the null values in either column to zero to always return a numeric taxable income.

Yes.

Is there a simpler way to display the SALARY information from the EMPLOYEES table in the form $13,000 without using the following statement? SELECT '$'|| SUBSTR(SALARY,1, MOD(LENGTH(SALARY),3))|| ', '|| SUBSTR(SALARY, MOD (LENGTH(SALARY),3)+1)

Yes. A simple and elegant solution is to use the TO_CHAR function with the '$99G999' format mask SELECT TO_CHAR(SALARY, '$99G999') FROM EMPLOYEES;

You are asked to input past employee data into the JOB_HISTORY table from a paper-based source, but the start date information is only available as the year the employee started. Can this value be converted into the first of January of the year?

Yes. Consider the conversion function call TO_ DATE('2000','YYYY') for an employee who started in the year 2000. If this date is extracted as follows, the character string 01/01/2000 is returned TO_CHAR(TO_DATE('2000','YYYY'), 'MM/DD/YYYY')

You are tasked with identifying the date the end of year staff bonus will be paid. Bonuses are usually paid on the last Friday in December. Can the bonus date be computed using the NEXT_DAY function?

Yes. If the NEXT_DAY function is called with the start date parameter set to the last day in December and the search day set to Friday, then the first Friday in January is returned. Subtracting seven days from this date yields the date of the last Friday in December. Consider the following query for the year 2009: SELECT NEXT_DAY('31-DEC-2009', 'Friday') -7 FROM DUAL;

You have been tasked to locate rows in the EMPLOYEES table where the SALARY values contain the numbers 8 and 0 adjacent to each other. The SALARY column has a NUMBER data type. Is it possible to use the LIKE comparison operator with numeric data?

Yes. Oracle automatically casts the data into the required data type, if possible. In this case, the numeric SALARY values are momentarily "changed" into character data allowing the use of the LIKE operator to locate matching patterns. The following query locates the required rows: SELECT * FROM EMPLOYEES WHERE SALARY LIKE '%80%';

You would like to extract a consistent 10-character string based on the SALARY column in the EMPLOYEES table. If the SALARY value is less than 10 characters long, zeros must be added to the left of the value to yield a 10-character string. Is this possible?

Yes. The LPAD function may be used as follows: SELECT LPAD(SALARY,10,0) FROM EMPLOYEES;

You have been tasked to retrieve the LAST_NAME and DEPARTMENT_ID values for all rows in the EMPLOYEES table. The output must be sorted by the nullable DEPARTMENT_ID column, and all rows with NULL DEPARTMENT_ID values must be listed last. Is it possible to provide the results as requested?

Yes. The ORDER BY clause provides for sorting by columns that potentially contain NULL values by permitting the modifiers NULLS FIRST or NULLS LAST to be specified. The following query locates the required rows: SELECT LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES ORDER BY DEPARTMENT_ID NULLS LAST;

Employees working in the IT department have moved to new offices and, although the last four digits of their phone numbers are the same, the set of the three digits 423 is changed to 623. A typical phone number of an IT staff member is 590-423- 4567. You are required to provide a list of employees' names with their old and new phone numbers. Can this list be provided?

Yes. The REPLACE function is used. To replace every 4 with a 6 will change digits that should not be changed as well, so the string to be replaced must be uniquely specified. The following query provides the list: SELECT FIRST_NAME, LAST_NAME, REPLACE( PHONE_NUMBER, '.423.','.623.') FROM EMPLOYEES WHERE DEPARTMENT_ ID=60

You have been asked to extract the last three characters from the LAST_NAME column in the EMPLOYEES table. Can such a query be performed without using the LENGTH function?

Yes. The SUBSTR(source string, start position, number of characters) function takes three parameters. If the start position is set to -3, and the number of characters parameter is set to three or is omitted, the last three characters of the LAST_NAME column data is retrieved. The following query may be used: SELECT SUBSTR(LAST_NAME,-3) FROM EMPLOYEES;

You wish to retrieve the duration of employment in days for each employee. Is it possible to perform such a calculation?

Yes. The SYSDATE function may be used to obtain the current system date. The following query computes the duration by subtracting the HIRE_DATE column from the value returned by the SYSDATE function: SELECT SYSDATE-HIRE_DATE FROM EMPLOYEES;

Your task is to extract the day and month portion of a date column and compare it with the corresponding components of the current system date. Can such a comparison be performed?

Yes. The TO_CHAR function used on a date item with a format mask like 'DD-MON' causes the day and month component to be isolated. This value can be compared with the current system date using the following expression: TO_CHAR(SYSDATE, 'DD-MON'

A report of profit and loss is required with the results displayed as follows: if the amount is negative, it must be enclosed in angle brackets. The amount must be displayed with a leading dollar sign. Can results be retrieved in the specified format?

Yes. The numeric amount must be converted into a character string using the TO_CHAR function with a format mask that encloses it in angle brackets if it is negative and precedes it with a dollar sign. The following function call retrieves the results in the required format: TO_CHAR(AMOUNT, '$999999PR')

You would like to search for a character string stored in the database. The case in which it is stored is unknown and there are potentially leading and trailing spaces surrounding the string. Can such a search be performed?

Yes. The simplest solution is to first TRIM the leading and trailing spaces from the column and then convert the column data using a case conversion function like LOWER, UPPER, or INITCAP to simplify the number of comparisons required in the WHERE clause condition.

Ampersand substitution variables support reusability of repetitively executed SQL statements. If a substituted value is to be used multiple times at different parts of the same statement, is it possible to be prompted to submit a substitution value just once and for that value to automatically be substituted during subsequent references to the same variable?

Yes. The two methods that may be used are double ampersand substitution or the DEFINE command. Both methods result in the user providing input for a specific substitution variable once. This value remains bound to the variable for the duration of the session unless it is explicitly UNDEFINED.

The SELECT list of a query contains a single column, Is it possible to sort the results retrieved by this query by another column?

Yes. Unless positional sorting is used, the ORDER BY clause is independent of the SELECT clause in a statement.