Oracle 12c 1Z0-71 Study Guide Part 1

Math with Dates

* Date + number = adds the number of days to the date Ex) SELECT SYSDATE, SYSDATE + 3 FROM DUAL; * Date - number = subtracts the number of days from the date Ex) SELECT SYSDATE, SYSDATE - 3 FROM DUAL; * Date - Date = the number of days between the two dates Ex) SELECT employee_id, SYSDATE, hire_date, SYSDATE - hire_date FROM employees; * Date + (#/24) = the number of hours to a date - NOTE: adding floating point numbers to dates, like 3.25, is interpreted by Oracle as adding that that additional number of days to the date. The numbers after the decimal point are interpreted as hours

Substitution Variables (& or &&)

- && substitution variables will prompt for input only the 1st time the statement executes. The inputted variable will then remain set until either the session ends or an UNDEFINE command is issued for the variable. So && only prompts once - & substitution variables will prompt the user for an input every single time the statement is executed - NOTE: Substitution variables are allowed in any part of a SELECT statement - NOTE: Substitution variables can be used more than once within a single SELECT statement, so substitution variables do NOT have to be unique within a SELECT statement

Time Format

- AM or PM = Meridian Indicator - A.M. or P.M. = Meridian Indicator with periods - HH or HH12 = 12 hour format - HH24 = 24 hour format - MI = Minute (0-59) - SS = Second (0-59) - SSSSS = Seconds past midnight (0-86399)

Character Functions (SRF)

- Accept characters as input values and returns either character or numeric values - Two types of Character functions 1.) Case conversion function * LOWER, UPPER, INITCAP 2.) Character Manipulation function * SUBSTR, LENGTH, CONCAT, INSTR, TRIM, REPLACE, RPAD, LPAD

Single Row Functions (SRF)

- Accept one or more arguments and returns one value - Return one result per row - Can be used alone or nested - Columns or expressions (logical, arithmetic, etc) can be used as arguments - Data type returned can be different than input type - Can be used in SELECT, ORDER BY, or WHERE clauses - There are 5 five types of single row functions: * Numeric * Date * Conversion * Character * General

ADD_MONTHS

- Add calendar months to date - SYNTAX = ADD_MONTHS(date, #) where # is the number of months to add to the date - If # is a negative number, it will subtract that number of months from the date

Set Operators

- Allow the results of 2 or more queries to be combined into a single result - Set operators include the following: UNION, UNION ALL, INTERSECT, MINUS - SQL statements that include set operators are called compound queries - NOTE: Set operators of equal precedence are evaluated from LEFT to RIGHT unless you use parentheses to force the order of evaluation (like using parentheses in algebra) - NOTE: When using any of the set operators, the columns in the SELECT list must be the same in number and data type - NOTE: Duplicate values are automatically eliminated by default in all set operators EXCEPT for the UNION ALL operator - NOTE: You can only use the ORDER BY clause at the end of a compound query

CONCAT (CMF)

- Appends/Concatenates the first character value to the second character value, equivalent to concatenation operator || but only for 2 parameters or arguments - CONCAT ('Sql', 'Course') ==> SqlCourse - Syntax: CONCAT('arg1', 'arg2') - NOTE: CONCAT can only handle two parameters or arguments, it CANNOT handle more than two input columns

Character Manipulation Functions (CMF)

- Are used to alter or change the content or structure of characters SUBSTR, LENGTH, CONCAT, INSTR, TRIM, REPLACE, RPAD, LPAD are all CMFs

Case Conversion Functions (CCF)

- Are used to change characters to upper or lower case - LOWER: Converts all of the input characters to lowercase (asdf) - UPPER: Converts all of the input characters to uppercase (ASDF) - INITCAP: Converts the first character of each word to uppercase and puts the rest as lowercase (Asdf) - Ex) SELECT LOWER (first_name), UPPER (last_name), INITCAP (middle_name) FROM people; - Above query would return john, SMITH, Doe from a table

SET Operators

- Are used to combine the results of multiple queries into one single set of results - The list of SET operators includes: UNION, UNION ALL, INTERSECT, MINUS - Queries containing SET operators are called 'Compound Queries' - SET operators have equal precedence, so they are executed from left to right unless forced by using parentheses to contain them - The number of columns being selected must be the same - The data types of the columns being selected must be the same - The column names being selected DO NOT have to be same, so you can have different columns in each query - ORDER BY clause can only be used at the end of a compound query, so make sure that if you use ORDER BY that it is the last line in your compound query - All duplicates are removed with all SET operators except the UNION ALL operator which includes any duplicates

Functions in SQL

- Are used to easily reuse code that manipulates data and returns some values - Are created and stored as a name that can be called explicitly when needed - There are two types of functions in Oracle SQL: 1.) Single Row Functions 2.) Multiple Row Functions - Syntax for creating and storing functions is function_name [(arg1, arg2, ...)]

Non-Equijoins

- Are used to join tables containing something other than an equality operator. - Uses operators like <, <= , > , >= , BETWEEN, IN - - Values are not directly equal to each other i.e. highest_salary and lowest_salary are not going to be equal values - EX) SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal; So notice there is no equal sign in the query, it is a comparison. That is a dead give-away of a non-equijoin - Non-equijoins are used when there are not any corresponding columns that exist between the tables in the query, but a relationship still exists between two columns in the tables where the columns have compatible data types

Equijoins

- Are used to join to tables on columns where the values in both columns must be equal - Biggest distinguishing feature of the equijoin is that they contain the equality operator (=) and do not contain > , < or other nonequality operators - These types of joins involves primary key and foreign key complements - Syntax: SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; - If the tables have the same column name in each table, you MUST specify the table when selecting the column - You can use column aliases to distinguish columns that have identical names but reside in different tables - If a column is unique to a table, you do not HAVE to specify the table in the code, i.e you don't have to explicitly state TableName.ColumnName in your code, but it is not good practice to do so.

Transaction Control Language (TCL) Keyword

- COMMIT - ROLLBACK - SAVEPOINT

DDL (Data Definition Language) Keyword

- CREATE - ALTER - DROP - RENAME - TRUNCATE - DDL statements allow you to modify the attributes of a schema object without affecting database user's ability to access that object - DDL statements create, define, and drop schema objects - DDL statements start with the keywords ALTER, CREATE, or DROP

FLASHBACK Statement

- Cannot undue operations with the PURGE clause because the PURGE clause permanently erases all data pertaining to specified table - Cannot undue TRUNCATE operations because TRUNCATE modifies that structure of table

NULLIF Function

- Compares two expressions and returns null if they are equal, returns the expr1 if they are NOT equal - Syntax: NULLIF(expr1, expr2) - Compares expr1 and expr2. If they are equal, returns NULL. If they are not equal, returns expr1 - NOTE: expr1 and expr2 must be same data types

TO_NUMBER Function

- Converts a character string to a number value - Syntax: TO_NUMBER('string', 'fmt') where fmt is the specified format

NVL Function

- Converts a null value to an actual value - Syntax: NVL(expr1, expr2) where expr1 is the value being evaluated for NULL and expr2 is what is to be returned in the event of a NULL value for expr1 - If expr1 is NULL, then expr2 is returned - NOTE: Data types for both expr1 & expr2 MUST be the same i.e. both are char or number or varchar data types otherwise error occurs

NVL2 Function

- Converts a null value to an actual value based on the value of expr1 - Syntax: NVL2(expr1, expr2, expr3) - If expr1 is NOT null, NVL2 returns expr2 - If expr1 IS null, NVL2 return expr3. - The argument expr1 can have any data type. - Expr1 does not have to be the same data type with expr2 and expr3 - expr2 and expr3 MUST be same data type

Explicit Data Type Conversion

- Explicit data type conversions can be done using the TO_CHAR, TO_DATE, TO_NUMBER functions - A character value can be converted to a date value using the TO_DATE function - A date value can be converted to a character string using the TO_CHAR function - A number value can be converted to a character string using the TO_CHAR function

DCL (Data Control Language) Keyword

- GRANT - REVOKE

Multiple Row Operators

- Include the ANY, ALL, IN operators - Are used in sub queries that return multiple rows

Group Functions

- Include the MIN, MAX, AVG, COUNT, SUM functions - Are used to manipulate multiple rows and return one value - Ignore NULL values by default - DISTINCT operators can be used to handle NULL values - Multiple group functions can be used in one query

Single Row Operators

- Include the following: =, > , < , >= , <= , <> - Can only be used in sub queries that return only 1 row - NOTE: if you use a single row operator in a sub query that returns more than 1 row, an error will occur

Guidelines for Using Group Functions

- Including the DISTINCT keyword in a group function makes the function consider only non-duplicate values - Including the ALL keyword makes it consider every value including duplicates - The default value is ALL, and therefore does NOT have to be explicitly specified - The data types for the functions with an expr argument may be CHAR, VARCHAR2, NUMBER, or DATE - All group function ignore null values - To subsitute a value for the null values, use the NVL, NVL2, COALESCE, CASE, or DECODE functions - When nesting group functions, you can only nest 2 group functions - NOTE: Group functions cannot be used in the WHERE clause, they have to be used in SELECT, HAVING, ORDER BY clauses

SYSDATE

- Is a function that returns the current database server date and time - Has the same precision as any other Oracle column with a data type of DATE

ROW ID Data Type

- Is a hexadecimal string that represents the unique address of a row in a table - ROW ID values can be queried just like any other values in a table - ROW ID provides a unique identifier for each row in the table, so it can be used to locate a row in a table

Foreign Key

- Is a set of one or more columns in one table that references a matching set of columns in another table - Foreign keys can reference only primary key column or columns with a unique constraint - Foreign keys define relationships between tables or between different columns in the same table

INTERSECT (Set Operator)

- Is a set operator used in compound queries - INTERSECT returns values that are common to both queries - Ex) SELECT job_id FROM job_history INTERSECT SELECT job_id FROM employees WHERE department_id = 80; In the above example, the column job_id is present in both tables and have the same data type in both tables

MINUS (Set Operator)

- Is a set operator used in compound queries - MINUS returns only values that are present in the 1st table, but are not present in the second table - Ex) SELECT job_id FROM job_history MINUS SELECT job_id FROM employees WHERE department_id = 80; In the above example, the column job_id is present in both tables, if a job_id = manager IS in the job_history table, but in the 2nd table there IS NOT a job_id = manager then the result of the compound query would return just job_id = manager since it was in the 1st table but not the 2nd table - https://www.youtube.com/watch?v=rYq3X2KyB4M ^ best explanation I could find

UNION ALL (Set Operator)

- Is a set operator used in compound queries - UNION ALL returns values from both queries, but also includes all duplicate values as well - - Ex) SELECT job_id FROM job_history INTERSECT SELECT job_id FROM employees WHERE department_id = 80; In the above example, the column job_id is present in both tables and the query returns all entries for job_id from both tables.

UNION (Set Operator)

- Is a set operator used in compound queries - UNION returns values from both queries after it has eliminated any duplicated values - Ex) SELECT job_id FROM job_history INTERSECT SELECT job_id FROM employees WHERE department_id = 80; In the above example, the column job_id is present in both tables and the query returns each job_id only once. So it acts sort of like using DISTINCT, but for two queries at once.

COALESCE Function

- Is an advanced NVL function - Returns the first non-null expression in the expression list - If all expr values are NULL, then only the last expression in the list is returned - Syntax: COALESCE(expr1, expr2,...,exprn) - NOTE: All expressions must be of the same data type

<> Operator

- Is the NOT EQUAL operator - Is a single row operator, meaning that it can only be used with single row queries or inner queries that return only one row - Attempting to use the <> operator with a query that returns more than 1 row will result in an error

DISTINCT Operator

- Is used in conjunction with the SELECT keyword in order to eliminate duplicate rows so that only unique entries are returned by the query - i.e. SELECT DISTINCT names FROM people; if there are many people with the same name, i.e. 4 people with the name Jim, the query above will only return 1 result for the name Jim - NOTE: only 1 DISTINCT operator can be used per query; using more than DISTINCT statement in a query will result in an error - i.e. SELECT DISTINCT names, DISTINCT places FROM table; will result in error because there is more than 1 DISTINCT statement in the query

BETWEEN ___ AND ___ Operator

- Is used in conjunction with the WHERE clause - Retrieves data between the lower limit and upper limit of the selected range. - i.e. SELECT salary FROM employees WHERE salary BETWEEN 1000 AND 10000; - Above query would return only the salary numbers for employees who make 1000 up to 10000 - NOTE: the lower limit goes before the AND, the upper limit goes after the AND

LIKE Operator

- Is used in conjunction with the WHERE clause - Searches for strings of characters that match a predefined pattern specified in the query - Uses the % and _ characters as part of the process. - The % is used for including 0 or more characters from the value being searched for and acts as a general wildcard operator. Includes all characters before or after it in the search - The _ is used for including EXACTLY 1 character from the value being searched for. Includes only 1 character before or after where it is placed - Useful for finding values where the exact entry that needs to be found is vague or not specific enough to search for explicitly - i.e. SELECT name FROM people WHERE name LIKE '_ob'; - Above query would return Rob and Bob if those entries are in that table - SELECT name FROM people WHERE name LIKE '%an'; - Above query would return every name that ended with 'an' because the % operator acts as a wildcard, so it includes everything before the 'an' in the query - NOTE: LIKE operator will work without using the % or _ character, BUT it should be avoided since it is the same thing as using equal (=) and LIKE is slower than using equal

IN Operator

- Is used in conjunction with the WHERE clause - Returns only values that are contained in a specific list - i.e. SELECT first_name FROM people WHERE first_name IN ('Sarah', 'Steve', 'John'); - Above query would return ALL entries in the first_name column with Sarah, Steve, or John in them...it would have duplicate results if there is more than 1 person with the same name. So if there were 3 people with the name Sarah, and two people with the name John, the query would return Sarah, Sarah, Sarah, John, John - NOTE: order is not important when using the IN operator, the values for the list can be in any order

IS NULL Operator

- Is used in conjunction with the WHERE clause and searches specifically for values that are NULL - i.e. SELECT * FROM people WHERE name IS NULL; - Above query would return all entries in the people table where the name column has a NULL value - NOTE: IS NULL is not the same as = NULL, nothing and I mean nothing can be equal to NULL since NULL has no definition or value associated with it.

AS Keyword

- Is used to create aliases for column names - i.e. SELECT first_name AS name - NOTE: it is NOT required to use AS keyword to create aliases for column names. The following query would work just the same as the above SELECT last_name "Last Name" - Double quotes "" are required around the desired alias name if not using AS keyword

DECODE Function

- Provide the use of conditional queries in SQL similar to the IF-THEN-ELSE logic in other languages - The DECODE function is specific to Oracle SQL - Syntax: See Picture - DECODE is not as flexible as CASE since you can only evaluate one column per DECODE function

ESCAPE Operator

- Is used to identify an escape character when performing a LIKE operation in a WHERE clause so that special characters such as _ , & , @ , - , $ etc are interpreted as literals instead of the reserved characters that they are - NOTE: Performing a LIKE operation that contains reserved characters such $,@,& etc without specifying an escape character will result in an error - There are two types of ESCAPE operators 1.) The \ operator is used to ESCAPE a single character or symbol and the \ operator HAS to be placed in front of the special character or symbol being escaped. Only the character immediately following the \ is escaped 2.) The {} operator is used to escape a string of characters or symbols, everything included in the {} braces is considered part of the escape sequence EX1) SELECT last_name || ', ' || first_name, subject_id FROM students WHERE subject_id LIKE 'HST\_R%' ESCAPE '\'; The above query will return last_name, first_name, and subject_id columns from the students table where the subject_id is similar to HST_R, the \ in front of the _ forces the _ to be interpreted literally since _ is a reserved character in Oracle SQL

TRIM (CMF)

- Is used to remove the leading or trailing characters from a character string - LEADING, TRAILING, or BOTH are optional modifier words that are used to specify which characters to trim - Syntax: TRIM( LEADING/TRAILING/BOTH FROM source) - i.e. TRIM (LEADING '-' FROM '---1234') ==> 1234 so it removes the specified character from the front of the string - NOTE: If neither LEADING or TRAILING are specified when using the TRIM, by default it will remove BOTH - NOTE: You can only TRIM the LEADING or TRAIlING or BOTH, you CANNOT trim anything from the beginning of the string

WHERE Clause

- Is used to restrict or limit the results of a query by placing conditions on the data that must be met in order for the values to be included in the results - WHERE clause is used in conjunction with comparison operators such as <, >, =, <=, >=, <>, BETWEEN, LIKE, IN, NULL - WHERE clause is also used in conjunction with logical operators such as AND, OR, NOT - i.e. SELECT first_name FROM people WHERE first_name = 'Sarah'; - Above query would return all entries where the first_name is equal to Sarah - NOTE: Including a group or aggregate function, such as AVG, MAX, COUNT, SUM in a WHERE clause will cause an error without the HAVING clause added in with it

REPLACE (CMF)

- Is used to search a text expression for a character string and if a match is found, the original string is replaced with a specified replacement string - Syntax: REPLACE ('string', 'matching condition', 'replacement string') - i.e. REPLACE('sql course', 's', '3') ==> #ql cour#e ==> so all s characters are replaced with a #

ORDER BY Clause

- Is used to sort the results of a query in either ascending (ASC) or descending (DESC) order - The default order is ascending (ASC), i.e. from lowest to highest for numeric data, from earliest to latest for date data, and alphabetically for character data - i.e. SELECT salary FROM employees ORDER BY salary DESC; - The above query would return the salaries from the employees table and sort them from largest down to smallest - Multiple columns can be sorted in ASC or DESC order independently from one another in a query, i.e. you can run a query to search for starting_salaries in ASC order and also for ending_salaries in DESC order in the same query - i.e. SELECT starting_salary, ending_salary FROM employees ORDER by starting_salary ASC, ending_salary DESC; - Above query would return the starting_salary in order from smallest to largest and the ending_salary from largest to smallest - NOTE: NULL values are displayed last in ASC order and displayed first in DESC order - NOTE: In a character based sort, the values are case sensitive

Concatenation Operator

- Is used with SELECT statement to combine multiple columns entries into 1 single result when performing a query - Two vertical bars || are used to concatenate columns together. - i.e. SELECT first_name || last_name FROM users; - Above query would return JohnSmith for first_name = John and last_name = Smith...notice there is no space between the names - It is good practice to put a blank space in between the bars to increase readability, this can be done by the putting || ' ' || between the columns that you wish to combine - Use AS to create aliases in order to make query easier to read and understand when concatenating columns - i.e. SELECT first_name || ' ' || last_name AS Name FROM users; - Above query would pull entries from first_name and last_name column then combine them into a new column called Name. So first_name = John and last_name = Smith would return as Name = John Smith - NOTE: concatenating a column with a NULL value will still return the string values of the non-null columns - NOTE: Literal values MUST be enclosed in single quotes ' ' i.e. ' Previous Balance: ' || prev_balanc will concatenate the literal string Previous Balance: to the column values prev_balance

OUTER Joins

- Joins two tables together and returns both the matched and unmatched rows. The unmatched rows are NULL - Used to return entries with NULL or missing values since a regular natural join would not return entries with missing or NULL values - You can use an outer join to also see rows that do not meet the join condition - Syntax: SELECT table1.column1 = table2.column2 FROM table1, table2 WHERE table1.column1(+) = table2.column2; or SELECT table1.column1 = table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column2(+); The (+) is the outer join operator and you ALWAYS put the (+) on the side that has the missing/NULL values. - If the (+) operator is on the LEFT side of the equal sign, (+) = it is a RIGHT OUTER JOIN - If the (+) operator is on the RIGHT side of the equal sign, = (+) it is a LEFT OUTER JOIN - You use the (+) operator to force your query to return all values for the whichever table you designate as your main table

NUMBER Data Type

- Most useful when a fixed point number is not known - Represents all floating point numbers - NUMBER(p,s) specifies the floating point precision to use. p is the precision and s is the scale.

CREATE TABLE

- NOTE: Cannot used reserved words such as date for table names - NOTE: Table and columns names must begin with a letter only, no special characters or numbers - NOTE: Table and column names cannot contain the * character in them

SET Operator

- NOTE: Including more than 1 SET clause will generate an error - NOTE: Including the AND operator in a SET clause will generate an error

CROSS JOIN

- NOTE: Using an ON clause in a CROSS JOIN will generate an error

LISTAGG (group function)

- Orders data within each specified group in the ORDER_BY clause and then concatenates the values of the measure column

DESC or DESCRIBE Keyword

- Provides a description of the specified TABLE - Returns Columns, nullable or not, and also provides the data types of the column entries - Can be used with either DESC or DESCRIBE keyword

CASE Function (WHEN Clause)

- Provides the use of conditional queries in SQL similar to the IF-THEN-ELSE logic in other languages - The CASE function complies with the ANSI SQL - Syntax : See picture - Each WHEN clause is evaluated regardless of the outcome of the other WHEN clauses...with 1 exception, please see NOTE below. ****SPECIAL NOTE****: Using a WHEN clause inside of an INSERT clause requires the use of the ALL WHEN or FIRST WHEN ----If you specify INSERT ALL WHEN, the database evaluates each WHEN clause regardless of the results of the evaluation of any other WHEN clause. ----If you specify INSERT FIRST WHEN, the database evaluates each WHEN clause in the order in which it appears in the statement. The first WHEN clause that evaluates to true, the database executes the corresponding INTO clause and skips subsequent WHEN clauses for the given row. The default WHEN clause inside of INSERT is INSERT ALL WHEN ************************* *********************** NOTE: When using comparison operators such as < > etc in a WHEN clause, have the highest numbers evaluated first and evaluate the lowest numbers last. ^^^For example WHEN salary > 10000 THEN 'salary > 10000' WHEN salary > 5000 THEN 'salary > 5000' WHEN salary > 2000 THEN 'salary > 2000' If you do not evaluate from highest to lowest in a WHEN clause, Oracle will stop evaluating at the first WHEN clause that evaluates to true. This results in only the first THEN statement to be returned by END statement. ^^^ For example WHEN salary > 2000 THEN 'salary > 2000' WHEN salary > 5000 THEN 'salary > 5000' WHEN salary > 10000 THEN 'salary > 10000' Would only return 'salary > 2000' for all rows, because 2000<5000<10000 Oracle doesn't even look at the other WHEN clauses as they are all > 2000 - expr and comparison_expr must be same data type - Can be used in both SELECT and WHERE clauses

FULL OUTER JOIN

- Retrieves all of the columns from BOTH tables and NULLS from the unmatched rows of both tables - Syntax: SELECT table1.column1, table2.column2 FROM table1 FULL OUTER JOIN table2 ON (table1.column1 = table2.column2);

LEFT OUTER JOIN

- Retrieves all of the columns from the LEFT table and NULLS for the unmatched rows of the right table - Syntax: SELECT table1.column1, table2.column2 FROM table1 LEFT OUTER JOIN table2 ON (table1.column1 = table2.column2); It will retrieve all columns from table1 and return the matched row values and unmatched NULLS from table2 - NOTE: In the old syntax, if the (+) operator is on the RIGHT side of the equal sign, = (+) it is a LEFT OUTER JOIN NOTE: Outer joins cannot use the IN logical operator

RIGHT OUTER JOIN

- Retrieves all of the columns from the RIGHT table and NULLS for the unmatched rows of the left table - Syntax: SELECT table1.column1, table2.column2 FROM table1 RIGHT OUTER JOIN table2 ON (table1.column1 = table2.column2); It will retrieve all columns from table2 and return the matched row values and unmatched NULLS from table1 NOTE: In the old syntax, If the (+) operator is on the LEFT side of the equal sign, (+) = it is a RIGHT OUTER JOIN NOTE: Outer joins cannot use the IN logical operator

Multiple Column Subqueries

- Return more than one column in a row - Can be used with FROM, WHERE, or HAVING clauses - Are useful in writing more than 1 inner query in 1 inner query - Are used with the IN operator - Ex) SELECT first_name, last_name, job_id, salary FROM employees WHERE (salary, job_id) IN (SELECT MAX(salary), job_id FROM employees GROUP BY job_id) ORDER BY salary DESC;

Multiple Row Subqueries

- Return more than one row - Use the multiple row comparison operators IN, ANY, ALL - IN operator: equal to any value in the list - ANY operator: At least 1 value should match Returns true if at least 1 element exists in the result set. NOTE: Must be preceded by =, !=, >, <, <=, >= - ALL operator: All of the elements should match the condition of the main query. Returns true if all of the elements exist in the result set. NOTE: Must be preceded by =, !=, >, <, <=, >= - NOTE: Multiple row subqueries 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

UNION Operator

- Returns all rows from both queries after eliminating duplicate values - Use the UNION operator when you want to return all rows from multiple tables and eliminate any duplicate values - The number of columns being selected must be the same in all queries, so if you have 3 columns in the 1st query, then you need to have 3 columns in the 2nd query - The data types being selected must be the same - NULL values are not ignored during the duplicate checking, so duplicate NULL values will be removed - By default, the output is sorted in ascending order

Single Row Subqueries

- Returns only one row from the inner query - Used with single row comparison operators = , > , < , >= , <= , <> Ex.) SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE first_name = 'Michael' AND last_name = 'Harstein'); The above query will first select the salary for the employee with first name Michael and last name Harstein from the employees table. Michael Harstein's salary is then returned to the main query and the main query will return the first name, last name, and salary of all employees whose salary is greater than Michael Harstein's - Can use more than one subquery in one main query as long as only 1 row is returned...see following example EX) SELECT first_name, last_name, salary FROM employees WHERE salary < (SELECT salary FROM employees WHERE first_name = 'Michael' AND last_name = 'Harstein') AND department_id = (SELECT department_id FROM employees WHERE first_name='Michael' AND last_name='Harstein'); The above query will return the employees who have a salary value less than Michael Harstein and who are also in the same department as Michael Harstein

INTERSECT Operator

- Returns only rows that exist in both queries - The number of columns being selected must be the same in all queries, so if you have 3 columns in the 1st query, then you need to have 3 columns in the 2nd query - The data types being selected must be the same - The column names being selected DO NOT have to be same, so you can have different columns in each query - NOTE: Reversing the order of the intersected values does not alter the table result - INTERSECT does not ignore NULL values, so any duplicate NULL entries will be removed

SUBSTR (CMF)

- Returns specified characters starting at position m in the string and returns a value that is n characters long in length. If no n value specified, it will return all characters until the end of the string. - m is the position in the string at which you wish to start counting - n is how long the returned string is in length - If m is negative value, count will start from the end of the string and move towards the beginning of the string...so m = -1 would start the count at the last character of the string, m = -2 would start the count at the 2nd to last character in the string and so on - EX1) SUBSTR ('SQL course', 1, 3) ==> SQL ==> so it will start a position 1 which is = S and end at position 3 which is = l and return a string with a length of 3, which is SQL - EX2) SUBSTR('first name', 7, 3) ==> nam ==> so it will start at position 7 which is n and return the first 3 characters of the word name - Syntax: SUBSTR ('string', m,n) where 'string' is the string being evaluated, must be have ' ' around string, m is the starting position, and n is the length of the string to be returned

AVG (group function)

- Returns the average value of n - Ignores null values - You cannot use the AVG function on values with varchar (like names) or dates

MAX (group function)

- Returns the maximum value of the specified expr - Ignores null values

MIN (group function)

- Returns the minimum value of the specified expr - Ignores null values

LENGTH (CMF)

- Returns the number of characters in the expression - LENGTH ('Sql course') ==> 10 - NOTE: will include white space such as the space between words - Syntax: LENGTH ('string')

MONTHs_BETWEEN Function

- Returns the number of months between two dates - If date1 is less than date2, then the result will become negative SYNTAX = MONTHS_BETWEEN(date1,date2)

COUNT (group function)

- Returns the number of rows where expr evaluates to something other than null - COUNT(*) returns the number of rows in a table including duplicate rows and also includes null values - COUNT(1) is equivalent to COUNT(*), they do the same thing

INSTR (CMF)

- Returns the numeric position of a specified string - INSTR ('Sql course', 'r') ==> 8 so r is located 8 characters into the string - Syntax 1: INSTR ('string', '@') where @ = alphanumeric character being searched for - Syntax 2: INSTR ('string', '@', m, n) where @ = alphanumeric character being searched for, m is the position to start searching and n is the occurrence of n in the string EX) INSTR('boop', 'o', 1, 1) --> string = 'boop', @ = o, m = 1, n = 1 will start the search at position 1 in the string 'boop', which is the letter b. Then it will continue to search through the string until it sees the first instance of the letter o, which is at position 2. So the INSTR function will return a numeric value of 2 because the first instance of o in the string boop is at position 2. - NOTE: m and n are defaulted to 1

SUM (group function)

- Returns the sum of the values of n - Ignores null values - You cannot use SUM with varchar (like names) or dates values

ROUND Date

- Rounds the date to the specified unit of time i.e. day, month, etc - SYNTAX: ROUND (date, ['fmt']) where fmt is the unit of time - If the fmt is not specified, the default is day

DML (Data Manipulation Language) Keyword

- SELECT - INSERT - UPDATE - DELETE - MERGE - DML statements modify or query existing schema objects - DML statements do not change the structure of the database, they merely report on it's current state - A DML statement is executed when you * Add new rows to a table (INSERT) * Modify existing rows in a table (UPDATE) * Remove existing rows from a table (DELETE)

NATURAL JOIN (New Syntax)

- Syntax (New): SELECT column1, column2, column3, column4 FROM table1 NATURAL JOIN table2; - NOTE: WHERE clause goes after the NATURAL JOIN section in a query

Subquery

- Syntax: SELECT column_names FROM table WHERE expressions (SELECT column_names FROM table); - Inner query is the subquery and outer query is the main query - Subquery is evaluated before the main query and the result of the subquery is used as an input for the main query - Subqueries are enclosed with parentheses - Subqueries can be used with SELECT, WHERE, HAVING, and FROM clauses - There are 3 types of subqueries: 1.) Single-row subqueries 2.) Multiple-row subqueries 3.) Multiple-column subqueries - NOTE: Be careful when writing subqueries with single row operators such as <, > , = , >= , <=, <>, != as an error will result of the subquery returns more than one row

TO_CHAR Function With Dates

- Syntax: TO_CHAR (date ,'format_model') - The 'format model' argument: * MUST be enclosed in single quotation marks ' ' * Is case sensitive * Can include any valid date format element * Has an fm element to remove padded blanks or suppress leading zeros * Is separated from the date value by a comma See flashcard on Elements of the Date Format Model for more info on the format_model arg

TO_CHAR With Numbers

- Syntax: TO_CHAR(number, 'format_model') - Some of the format_model elements that can be used with TO_CHAR to display a number value as a character include: 9 = Represents a number 0 = Forces a zero to be displayed $ = Places a floating dollar sign L = Uses the floating local currency symbol . = Prints a decimal point , = Prints a comma as a thousands indicator

TO_DATE function

- Syntax: TO_DATE('date', 'format_model')

LTRIM (CMF)

- The LTRIM function removes characters from the left of a text expression or column object - Syntax: LTRIM ('text-exp', 'trim-exp') where text-exp = the column object or string to be trimmed and trim-exp = what characters or string to be trimmed from the selected object/string - i.e. LTRIM ('xyxxxyLast Word','xy') ==> LastWord so all x and all y characters are removed from the string - NOTE: the trim-exp is NOT just a literal string, it will remove ANY characters from the specified target regardless of how many times it appears or the order of appearance i.e. LTRIM ('4321123411223344LastWord', '1234') ==> LastWord, so all instances of 1, 2, 3, and 4 are removed from the expression - NOTE: you can also target columns to trim, i.e. LTRIM (product_name, 'Monitor') ==> would trim the word Monitor from the column product_name for all rows, so if there was a row entry product_name = Monitor 17/HR only the 17/HR would be returned.

RTRIM (CMF)

- The RTRIM function removes characters from the right of a text expression or column object - SYNTAX: RTRIM('text-exp', 'trim-exp') where text-exp = the column object or string to be trimmed and trim-exp= what characters or string to be trimmed from the selected object/string - i.e. RTRIM ('LastWordyxxxyx', 'xy') ==> LastWord so all x and y characters are removed from the string - NOTE: the trim-exp is NOT just a literal string, it will remove ANY characters from the specified target regardless of how many times it appears or the order of appearance i.e. RTRIM ('LastWord4321123411223344', '1234') ==> LastWord so all instances of 1,2,3,4 are removed from the expression - NOTE: you can also target columns to trim, i.e. LTRIM (product_name, 'Monitor') ==> would trim the word 'Monitor' from the column product_name for all rows, so if there was a row entry product_monitor = Monitor 17/HR, only the 17/HR would be returned

TRUNC Date

- Truncates the date to the specified unit of time, i.e. day, month, etc - SYNTAX: TRUNC (date, ['fmt']) where fmt is the unit of time - If the fmt is not specified, the default is day

GROUP BY clause

- Used for dividing returned rows into smaller groups - More than one column can be used in GROUP BY clause - You cannot make groups by using aliases - You cannot use the WHERE clause to restrict group functions in a query, instead use the HAVING clause to filter or restrict group functions in a query

IN Operator Subqueries

- Used in multiple row subqueries - Returns a list of elements to the main query which are then used to return matching pairs of elements found within both the main and sub queries - Acts like a list, where each item in the list is returned to the main query as a result EX) SELECT first_name, last_name, salary FROM employees WHERE salary IN (SELECT salary FROM employees WHERE department_id 90); The above query will first evaluate the IN subquery and return all of the salaries for employees who belong to department 90...so (24000, 17000, 17000) are values that could be returned. Then the main query will return the first name, last name, and salary for the employees whose salaries are in the list of salaries for the IN operator sub query. So for example Steven King 24000, Lex De Haan 17000, Neena Kochhar 17000 are values that could be returned - NOTE: IN is equivalent to = ANY - NOTE: NOT IN is equivalent to <> ALL , which means that if the subquery set contains even a single NULL value, the query will NOT return any results

LPAD (CMF)

- Used to insert the selected characters to the LEFT of the expression if and only if the column value is LESS than the length specified - Syntax: LPAD (column/expression/string, #, 'n') where # is the MAXIMUM length that the returned column value can have and n is the character that you want inserted if the value returned is less than the specified length. NOTE: n must be in single quotes ' ' - EX) LPAD (first_name, 3, '#' ) would return column value ERI for first_name = ERIC - EX) LPAD (first_name, 10, '#' ) would return ######ERIC NOTE: The above only applies when using LPAD for COLUMNS, a different set of rules applies when using LPAD on strings. When applying LPAD to a string, it simply inserts the specified character n times to the LEFT of the string

RPAD (CMF)

- Used to insert the selected characters to the RIGHT of the expression if and only if the column value is LESS than the length specified - - Syntax: RPAD (column/expression/string, #, 'n') where # is the MAXIMUM length that the returned column value can have and n is the character that you want inserted if the value returned is less than the specified length. NOTE: n must be in single quotes ' ' - EX) RPAD(first_name, 3, '#') would return column value ERI for first_name = ERIC - EX) RPAD(first_name, 10, '#') would return ERIC###### NOTE: The above only applies when using RPAD for COLUMNS, a different set of rules applies when using RPAD on strings. When applying RPAD to a string, it simply inserts the specified character n times to the RIGHT of the string

USING Clause

- Used to join 2 tables on a single column with the same name in both tables, but the data types don't match - NOTE: You can use the USING clause with multiple columns - NOTE: When writing the SELECT statements, DO NOT specify a table name or alias with columns that are contained in the USING function as it will generate an error - NOTE: An error will occur if you use the USING clause where the column names don't match

NATURAL JOIN

- Used to join two tables using all of the columns that have the same names in both tables - Syntax: SELECT * FROM table1 NATURAL JOIN table2; - EX) SELECT first_name, last_name, department_id FROM employees NATURAL JOIN departments; - Combine with WHERE clause to restrict data and results - NOTE: If two columns have same name, but different data types, an error will occur - NOTE: If the two tables have no columns with the same names, a cross product will be performed - NOTE: Natural joins can be implemented regardless of whether the tables being joined have primary and foreign keys defined - NOTE: Columns with different names cannot be joined using a natural join

ANY Operator Subqueries

- Used to return values to the main query that match the stated condition at least once within the list of elements returned by the subquery - At least 1 value should match. Returns true if at least 1 element exists in the result set. - NOTE: Must be preceded by =, !=, >, <, <=, >= < ANY returns all values that are less than the MAXIMUM value in the list = ANY means equal to one of the elements (same as using IN) > ANY returns all values that are more than the MINIMUM value in the list <= ANY returns all values that are less than or equal to the MAXIMUM value in the list >= ANY returns all values that are greater than or equal to the MINIMUM value in the list EX) SELECT first_name, last_name, salary FROM employees WHERE salary >= ANY (SELECT salary FROM employees WHERE department_id = 90); The above query will first evaluate the ANY subquery and return all of the salaries for employees in department 90...so (24000,17000,17000) are possible values. Then the main query will go through the employees table and return the first name, last name, and salary of all employees whose salaries are greater than or equal to the smallest value in the list returned by the subquery. So if our ANY subquery list contains the values (24000, 17000, 17000) then only employees whose salaries are greater than or equal to 17000 will be returned by the main query. - NOTE: Using the ANY operator without an equality, inequality, less than or greater than operator preceding it will cause the entire statement to fail

ALL Operator Subqueries

- Used to return values to the main query that match the stated condition for all values in the list of elements returned by the subquery - All of the elements should match the condition of the main query - NOTE: Must be preceded by =, !=, >, <, <=, >= < ALL returns all values that are less than the MINIMUM value in the list = ALL means nothing if there is more than 1 record returned, essentially return NULL > ALL returns all values that are more than the MAXIMUM value in the list <= ALL returns all values that are less than or equal to the MINIMUM value in the list >= ALL returns all values that are greater than or equal to the MAXIMUM value in the list

NEXT_DAY

- Used to select the next day in the date - SYNTAX1 = NEXT_DAY(date, 'day') where date is the selected date and day is a day of the week i.e Monday, Tuesday, Wednesday, etc...NOTE: the '' must enclose the specified day or an error will occur - SYNTAX2 = NEXT_DAY(date, #) where date is the selected date and # is a number between 1 and 7...NOTE 1 is Sunday and 7 is Saturday Ex) SELECT sysdate, NEXT_DAY(sysdate, 'Friday') FROM dual; would return today's sysdate in one column and the date of the next upcoming Friday in another column Ex) SELECT sysdate, NEXT_DSY(sysdate, 2) FROM dual; would return today's sysdate in one column and the date of the next upcoming Monday in another column

ON Clause

- Used with JOIN clause to join two tables with one or more columns where the column names can be different - Syntax: SELECT table1.column1, table2.column1 FROM table1 JOIN table2 ON (table1.column1 = table2.column1); - Essentially follows same syntax as old pre 1999 style by replacing the , in old style

UNION ALL Operator

- Works the same was as UNION except that it does not remove duplicate values - Returns rows of both queries, INCLUDING any duplicate values - UNION ALL is the only SET operator that does not remove duplicate values - Duplicate values are NOT eliminated - The output is not sorted by default

Elements of Date Format Model

- YYYY = Full year in numbers (i.e. 2019) - YEAR = Year spelled out in English (i.e. Two Thousand Nineteen) - MM = Two-digit value for the month (i.e. 12 for December) - MONTH = Full name of month (i.e. December) - MON = Three-letter abbreviation of the month (i.e. DEC for December) - DY = Three-letter abbreviation of the day of the week (i.e. Mon for Monday) - DAY = Full name of the day of the week (i.e. Monday) - DD = Numeric day of the week (i.e. 1 through 7 with 1 being Sunday and 7 being Saturday)

Rules of Precedence for Operators

1.) Arithmetic operators 2.) Concatenation operators 3.) Comparison operators 4.) IS [NOT] NULL, LIKE, [NOT] IN 5.) [NOT] BETWEEN 6.) Not equal to 7.) NOT logical operator 8.) AND logical operator 9.) OR logical operator These can be thought of like the PEMDAS order of operations in algebra, so actions will be performed from the top of the above list down to the bottom, with arithmetic being the first operation to be performed in a query -NOTE: Use parentheses to compartmentalize your operations to occur in the desired order

Default DATE format

DD-MM-RR - If RR value is between 50-99, it is a 19xx year - If RR value is between 0-49, it is a 20xx year DD-MM-YY This format will only interpret the current century of the current sysdate, so will almost always result in 20XX year

CROSS JOIN (New Syntax)

Syntax (New): SELECT column1, column2 FROM table1 CROSS JOIN table2;