Tags: mastering_oracle_sql databases sql oracle data_science ch3 ch6 ch14 ch5 ch12 ch11 ch8 ch4 ch13 ch10 programming ch7 ch9 ch1 oracle_db_sql_language_ref_analytic_functions ch15 ch2 appendix ch17 ch18

Q: Pseudocolumns (Oracle SQL)
A: Columns that don't exist in tables but are visible during query execution as helpers (e.g. ROWID)

Q: ROWID (Oracle SQL)
A: Pseudocolumn in Oracle SQL representing the physical location of a row.

Q: Oracle SQL statement to create a column consisting of 1 (colname num) and 'abc' (colname str) with the same number of rows as 'customer' table.
A: SELECT 1 num, 'abc' str
FROM customer;

Q: INSERT ALL (describe what it does in words, Oracle SQL)
A: Oracle SQL command to insert data from a single source into multiple tables or into the same table multiple times.

Q: Use a subquery to delete all employees in the accounting department (two tables: employee, department, linked by dept_id) (Oracle SQL)
A: DELETE FROM employee
WHERE dept_id =
  (SELECT dept_id
   FROM department
   WHERE name = 'ACCOUNTING')

Q: Use the UPDATE statement to give every employee (in employee table) a raise of 10% if they make less than $40,000 (salary variable).
A: UPDATE employee
SET salary = salary * 1.1
WHERE salary < 40000;

Q: Two ways to modify more than one column in a table with the UPDATE clause: [...], or [...]. (Oracle SQL)
A: provide set of column/value pairs separated by commas; provide a set of columns and a subquery

Q: How do you decide whether to use = / != or IN / NOT IN when testing whether a value is in a subquery?
A: If you're not sure whether the subquery might return more than one row, use the IN / NOT IN operator.

Q: Oracle query to delete all customer orders (cust_order table) with order date (order_dt) falling within date1 and date2, without using comparison operators >= / <=
A: DELETE FROM cust_order
WHERE order_dt BETWEEN date1 AND date2;

Q: Oracle SQL code to find all strings from instructions column in cust_order table that contain the percentage sign %.
A: SELECT instructions
FROM cust_order
WHERE instructions LIKE '%\%%' ESCAPE '\';

Q: Function in Oracle SQL (as of 10g release) that lets you use regular expressions in conditions.
A: REGEXP_LIKE('Insert Regex String Here')

Q: (Oracle SQL) When working with NULL in conditions, equality (=) doesn't apply. What to use instead?
A: IS NULL

Q: Oracle SQL built-in function that substitutes NULL records with a specified value.
A: NVL(colname, replacement_value)
(Oracle SQL)

Q: Describe contents of FROM clause in Oracle SQL query that joins tables part and supplier on the supplier_id field
A: FROM part INNER JOIN supplier ON part.supplier_id = supplier.supplier_id

Q: Join Condition (Oracle SQL)
A: Condition specified in join query that relates rows of one table to rows of another table.

Q: Total # join conditions in a query always equals what?
A: Total # tables - 1

Q: Two Oracle SQL expressions for specifying [...]: [...] and [...]
A: join conditions; ON; USING

Q: Describe how the USING clause affects the semantics of the SELECT clause (SQL Oracle)
A: Can't qualify join column name in SELECT list using alias / table name (Oracle merges the two columns and recognizes only one with given name)

Q: NATURAL JOIN clause (Oracle SQL)
A: Oracle SQL command relating rows from two tables based on all pairs of columns (one from each table) with matching names.

Q: Do you need a join condition in the NATURAL JOIN clause? (Oracle SQL)
A: No

Q: Cross Join (Oracle SQL)
A: Joins without a join condition, the result being a cartesian product.

Q: Equi-Join (SQL Oracle)
A: A join where the join condition uses the = operator.

Q: Non-Equi-Join (SQL Oracle)
A: A join where the join condition uses any operator other than = to relate rows of the two table.

Q: Partition Outer Join (Oracle SQL)
A: Join that divides result set into partitions and repeats the same outer join for each of these groups. (Oracle SQL)

Q: In Mastering Oracle SQL, what do they say is the main use case for partition outer joins?
A: Generating missing rows

Q: The number of rows in a cross join = ...
A: Product of the number of rows in each table

Q: What happens when you use CROSS JOIN statement with a join condition in Oracle?
A: Oracle throws an error - because cross joins are joins without conditions

Q: In Oracle SQL, what is the alternative to the INNER JOIN statement?
A: You can just specify JOIN, and Oracle assumes it's an inner join.

Q: Oracle SQL query to get inventory class for each part name, where a part is in an inventory class when its unit_cost is between low_cost and high_cost.
Tables: part, inventory_class
part variables: name, unit_cost
inventory_class variables: low_cost, high_cost, inv_class
A: SELECT part.name, inventory_class.inv_class
FROM part JOIN inventory_class
ON part.unit_cost BETWEEN c.low_cost AND c.high_cost

Q: Oracle SQL query to get pairs of team names in team table to produce a table of all pairs of teams once.
Team table variables: name, team_id
A: SELECT t1.name, t2.name
FROM team t1 JOIN team t2
ON t1.team_id < t2.team_id
Note: != operator doesn't quite work becuase you get duplicates rows: (team1, team2) (team2, team1)

Q: [...] are useful for [...] (which is a fancy way of saying [...]).
A: Partition outer joins; data densification; filling in rows that do not exist

Q: SELECT <insert existing columns>, COUNT(*)
OVER (PARTITION BY dept_id) dept_count
FROM employee
What does OVER do here?
A: Allows you to aggregate without using a GROUP BY and repeats the aggregated value for each record.
e.g. if the statement was just COUNT(*) OVER () it would give the total count over all the records and repeat it for each row.

Q: SELECT <insert existing columns>, COUNT(*)
OVER (PARTITION BY dept_id) dept_count
FROM employee
What does PARTITION BY do here?
A: Tells OVER that you want it to aggregate (i.e. find the COUNT) for by department

Q: In a join, a table is called [...] if [...].
A: key-preserved; every key of the table can also be a key of the resultant join results

Q: In "Mastering Oracle SQL", what does the author say is the most important requirement for a join view to be modifiable?
A: The table you want to modify contained in the join must be key-preserved

Q: What happens when you delete a record from a key preserved table in a join view (other joined table not key preserved)?
A: Only deletes the record from the key preserved table - record from other joining table not touched.

Q: What happens when you delete a record from a join view when both joining tables are key-preserved?
A: Only the first table in the join will have row deleted (even though both tables key-preserved).

Q: Oracle command to get the column definitions / data types for table named "my_table"
A: DESCRIBE my_table
(Oracle SQL Command)

Q: Oracle SQL built-in table showing modifiable columns in all tables / views in user's schema.
A: USER_UPDATABLE_COLUMNS
(Oracle SQL)

Q: Oracle SQL optional clause in CREATE VIEW statement that prevents changes to data in view that would cause rows to not be included in view.
A: WITH CHECK OPTION
(Oracle SQL)

Q: Within what statement do you use the WITH CHECK OPTION statement? (Oracle SQL)
A: CREATE VIEW

Q: General syntax for aggregate function (Oracle SQL)
A: aggregate_function([DISTINCT | ALL] expression)

Q: General syntax for aggregate function (Oracle SQL): aggregate_function([DISTINCT | ALL] expression)
What is default, DISTINCT or ALL?
A: ALL

Q: General syntax for aggregate function (Oracle SQL): aggregate_function([DISTINCT | ALL] expression)
What does DISTINCT do?
A: Says that agregate functino should only consider distinct values of expression.

Q: General syntax for aggregate function (Oracle SQL): aggregate_function([DISTINCT | ALL] expression)
What does ALL do?
A: Says that agregate functinon should consider all values of expression.

Q: In Oracle SQL, only two aggregate functions don't ignore NULLs: [...] and [...].
A: COUNT(*); GROUPING

Q: Does COUNT(sale_price) ignore NULLs? (Oracle SQL)
A: Yes
Aside: COUNT(*) doesn't ignore NULLs, becuase it counts rows as a whole.

Q: Does COUNT(ALL sale_price) ignore NULLs? (Oracle SQL)
A: Yes
Aside: COUNT(*) doesn't ignore NULLs, becuase it counts rows as a whole.

Q: What must you include in your query if your SELECT list includes mix of aggregate and nonaggregate expressions? (Oracle SQL)
A: GROUP BY (Oracle returns error if you fail to do so)

Q: What is the one case where you can write aggregate expressions in SELECT list without also writing GROUP BY clause? (Oracle SQL)
A: When SELECT list consists only of aggregate expressions (aggregate expressions apply to entire result set)

Q: What happens when you have a nonaggregate and aggregate expressions in SELECT list but one nonaggregate expression not included in GROUP BY? (Oracle SQL)
A: Oracle returns an error

Q: What happens when you GROUP BY a column that contains NULL for some rows? (Oracle SQL)
A: Rows with NULL values placed in a single group and presented as one summary row in the output.

Q: Option to ORDER BY clause that lets you put NULL at the beginning or end of the result set (your choice). (Oracle SQL)
A: NULLS FIRST
NULLS LAST
(Oracle SQL)

Q: When you use a WHERE clause alongside GROUP BY, which clause is applied first?
A: WHERE

Q: When you use a WHERE clause alongside GROUP BY, which clause must appear first in the query?
A: WHERE

Q: HAVING (Oracle SQL Clause)
A: Puts filter on the groups created by GROUP by clause (Oracle SQL Clause)

Q: Poor use of HAVING clause: referencing only non-aggregate variables. Where should you reference these non-aggregate values instead?
A: Filter the non-aggregate variables in advance from within the WHERE clause.

Q: Expressions used in HAVING clause must have of the following properties (Oracle SQL):
- [...]
- [...]
A: must exist in the SELECT clause; must be an aggregate function

Q: What comes first in Oracle SQL query: HAVING or GROUP BY?
A: Either one - order doesn't matter.

Q: By convention, what usually comes first in Oracle SQL statement - HAVING or GROUP BY?
A: GROUP BY first, then HAVING

Q: Query using nested group operation to find the max money spent by any department on employee salaries (Oracle SQL)
Table: employees
Variables: dept_id, salary
A: SELECT MAX(SUM(salary))
FROM employee
GROUP BY dept_id;

Q: How many records will one group function enclosing another always return? e.g. MAX(SUM(salary)) (Oracle SQL)
A: 1 record

Q: Containing Statement (Oracle SQL - Subqueries)
A: The SQL statement that contains a subquery.

Q: What is executed first, subqueries, or the containing statement? (Oracle SQL)
A: Subqueries

Q: What happens to the subquery after the containing statement finished executing? (Oracle SQL)
A: Discarded (can think of subquery like a temporary table with statement scope)

Q: Correlated Subquery (Oracle SQL)
A: Subquery that references one or more columns from the containing statement. (Oracle SQL)

Q: Noncorrelated Subquery (Oracle SQL)
A: Subquery that doesn't reference any columns from the containing statement. (Oracle SQL)

Q: Inline View (Oracle SQL)
A: Subquery that appears in the FROM clause (Oracle SQL)

Q: Scalar Subqueries (Oracle SQL)
A: Subqueries that return a single row with a single column. (Oracle SQL)

Q: What type(s) of subqueries can the FROM clause contain? (Oracle SQL)
A: Noncorrelated Subqueries

Q: What type(s) of subqueries can the ORDER BY clause contain? (Oracle SQL)
A: Scalar subqueries

Q: What type(s) of subqueries can the GROUP BY clause contain? (Oracle SQL)
A: None - cannot contain subqueries

Q: What type(s) of subqueries can the SET clause of an UPDATE statement contain? (Two types) (Oracle SQL)
A: Scalar Subqueries
Single Row, multiple-column subqueries

Q: What type(s) of subqueries can the VALUES clause within the INSERT clause contain? (Oracle SQL)
A: Scalar subqueries

Q: What type(s) of subqueries can the WITH clause contain? (Oracle SQL)
A: Noncorrelated subquery

Q: Oracle SQL query to find the employee with the highest salary (use subquery with ALL statement).
Table: employee
Variables: name, salary
A: SELECT name
FROM employee
WHERE salary >= ALL
  (SELECT salary
   FROM employee);

Q: Oracle SQL query to find the employee with the highest salary (use subquery with ANY statement).
Table: employee
Variables: name, salary
A: SELECT name
FROM employee
WHERE NOT salary < ANY
  (SELECT salary
   FROM employee);

Q: Three operator clauses you may use to work with multi-row subqueries: [...], [...], and [...]. (Oracle SQL)
A: ANY; ALL; IN

Q: What is another way of writing
= ANY (<subquery>)
(Oracle SQL)
A: IN (<subquery>)

Q: What is another way of writing
IN (<subquery>)
(Oracle SQL)
A: = ANY (<subquery>)

Q: What is another way of writing
!= ALL (<subquery>)
(Oracle SQL)
A: NOT IN (<subquery>)

Q: What is another way of writing
NOT IN (<subquery>)
(Oracle SQL)
A: != ALL (<subquery>)

Q: Anti-Join (SQL)
A: Finding members of one table that do not exist in another table.

Q: WITH clause (Oracle SQL)
A: Oracle SQL clause that gives subquery a name that can be referenced multiple times in the containing statement.

Q: Where does the WITH clause appear in Oracle SQL queries?
A: Top line

Q: Syntax of WITH clause (Oracle SQL)
A: WITH <subquery_name> AS (<insert subquery>)

Q: Semi-Join (SQL)
A: Join that includes rows in table A for which corresponding data found one or more times in table B.

Q: Write SQL query to get a count of employees for each department in a department table, using a correlated subquery in SELECT clause.
Tables: employee, department
Variables: dept_id
A: SELECT d.dept_id,
  (SELECT COUNT(*) FROM employee e
   WHERE e.dept_id = d.dept_id) empl_cnt
FROM department d;

Q: Syntax for creating a Global Inline View (Oracle SQL)
A: WITH <name_of_view> AS (<insert view query>)
<Insert query where name_of_view will be used>

Q: Three data types representing temporal data in Oracle DB: [...], [...], [...]
A: DATE; TIMESTAMP; INTERVAL

Q: What's the difference between DATE and TIMESTAMP data types in Oracle?
A: TIMESTAMP includes fractional seconds (i.e. it's more precise - DATE only includes details to the second)

Q: Syntax for specifying TIMESTAMP data type in Oracle.
A: TIMESTAMP[(precision for fractional seconds)]

Q: Syntax for specifying timestamp with time zone data type in Oracle.
A: TIMESTAMP[(precision for fractional seconds)] WITH TIME ZONE

Q: What is the difference between TIMESTAMP and TIMESTAMP WITH TIME ZONE? (Oracle SQL)
A: The latter includes a time zone displacement - time difference in hours and minutes between local time and UTC.

Q: Why might you want to store temporal data as  TIMESTAMP WITH TIME ZONE rather than just TIMESTAMP? (Oracle SQL)
A: Values can be translated into any target time zone desired by your system's users.

Q: What does INTERVAL YEAR TO MONTH data type represent? (Oracle SQL)
A: Period of Time Expressed as # years and months

Q: Syntax for INTERVAL YEAR TO MONTH data type (Oracle SQL)
A: INTERVAL YEAR[(precision for year)] TO MONTH

Q: INTERVAL YEAR[(precision for year)] TO MONTH
What does precision for year specify?
A: # digits in year field
E.g. setting this = 2 allows for max interval of 99 years 11 months.

Q: What does NUMTOYMINTERVAL(<number>, <interval_unit>) do?
A: Converts a number to an INTERVAL YEAR TO MONTH value.

Q: NUMTOYMINTERVAL(<number>, <interval_unit>)
What values can <interval_unit> take?
A: 'YEAR' or 'MONTH'

Q: Syntax for INTERVAL DAY TO SECOND data type.
A: INTERVAL DAY[(precision for day)] TO SECOND[(precision for fractional seconds)]

Q: NUMTODSINTERVAL(<number>, <interval_unit>)
What values can <interval_unit> take?
A: 'DAY', 'HOUR', 'MINUTE', or 'SECOND'

Q: What does NUMTODSINTERVAL(<number>, <interval_unit>) do?
A: Converts a number to an INTERVAL DAY TO SECOND value.

Q: Syntax for Oracle DATE literals (specified by SQL Standard)
A: DATE 'YYYY-MM-DD'

Q: Syntax for Oracle TIMESTAMP literals (specified by SQL Standard)
A: TIMESTAMP 'YYYY-MM-DD HH:MI:SS.xxxxxxxxx'

Q: Oracle SQL function to convert a character string into a DATE (describe full syntax)
A: TO_DATE(string [,format])
(Oracle SQL)

Q: Oracle SQL function to convert a character string into a TIMESTAMP (describe full syntax)
A: TO_TIMESTAMP(string [,format])
(Oracle SQL)

Q: Oracle SQL function to convert a character string into a TIMESTAMP WITH TIME ZONE (describe full syntax)
A: TO_TIMESTAMP_TZ(string [,format])
(Oracle SQL)

Q: TO_CHAR function (Oracle SQL)
A: Oracle SQL function that converts a DATE / TIMESTAMP into a string of characters.

Q: SYSDATE (Oracle SQL)
A: Oracle built-in statement that returns current date / time for operating system holding the database.

Q: EXTRACT(element FROM temporal_value) (Oracle SQL)
A: Oracle SQL built in function that returns specified element from date, timestamp, or interval.
Valid elements include YEAR, MONTH, DAY, HOUR, and more.

Q: What does the expression SYSDATE+3 do? (Oracle SQL)
A: Takes the current system time and adds 3 days.

Q: What is the one arithmetic operation allowed between two Oracle DATEs/TIMESTAMPs?
A: Subtraction (- operator)

Q: What does subtracting one DATE from another DATE return? (Oracle SQL)
A: Number of days between those two DATEs.

Q: What does subtracting one TIMESTAMP from another TIMESTAMP return? (Oracle SQL)
A: INTERVAL DAY TO SECOND

Q: What does subtracting a number from a DATE return? (Oracle SQL)
A: DATE that number of days in the past

Q: ADD_MONTHS(<date column>, <# months>) (Oracle SQL)
A: Oracle SQL function to add a certain number of months to a date.

Q: How do you subtract a certain number of months from a date? (Oracle SQL)
A: Use ADD_MONTHS with a negative number e.g. ADD_MONTHS(SYSDATE, -6)

Q: MONTHS_BETWEEN(<DATE1>, <DATE2>) (Oracle SQL)
A: Oracle SQL function to get the number of months between two DATEs (i.e. DATE1 - DATE2)

Oracle SQL supports the following four set operations: UNION ALL, UNION, MINUS, INTERSECT

Q: Compound Queries (Oracle SQL)
A: SQL statements containing set operators.

Q: Component Query (Oracle SQL)
A: A SELECT statement in a compound query.

Source: Oracle SQL
Q: Two SELECT statements can be combined into a compound query by a set operation only if they satisfy 2 conditions:
1) [...]
2) [...]
A: Results of both queries must have same # columns; Data type of each column in each result set must match
Q: Two SELECT statements can be combined into a compound query by a set operation only if they satisfy 2 conditions:
[...]
A: 1) Results of both queries must have same # columns
2) Data type of each column in each result set must match

Q: General syntax of query involving a set operation (Oracle SQL)
A: component_query
{UNION | UNION ALL | MINUS | INTERSECT}
component_query

Q: What's the difference between UNION and UNION ALL?
A: Both combine the results of two SELECT statements into one result set, except UNION  eliminates any duplicate rows from the result set.

Q: MINUS (Oracle SQL)
A: Oracle SQL command that takes the result set of one SELECT statement, removes those rows also returned by second SELECT statement.

Q: INTERSECT (Oracle SQL)
A: Oracle SQL command that returns only those rows that are reurned by each of two SELECT statements.

Q: Does UNION ALL remove duplicates? (Oracle SQL)
A: No

Q: Does UNION remove duplicates? (Oracle SQL)
A: Yes

Q: Which command is faster, UNION or UNION ALL, and describe why? (Oracle SQL)
A: UNION ALL faster - doesn't remove duplicates. UNION sorts result set to remove duplicates.

Q: Self-Referential Integrity Constraint (Oracle SQL)
A: Foreign key referencing another column of the same table (e.g. managerID referencing employeeID in employee table).

Q: Briefly describe the example table used in Mastering Oracle SQL to show hierarchical queries.
A: Employee table. There's employee id and manager id columns, where manager id is a foreign key reference to employee id (points to the employee the employee reports to, i.e. the employee's manager).

Q: Describe the query to find all the root nodes in Mastering Oracle SQL Employee hierarchical table.
A: SELECT *
FROM employee
WHERE manager_empid IS NULL;

Q: Describe the query to find all the node's immediate parent (i.e. show all employees and their managers) in Mastering Oracle SQL Employee hierarchical table.
A: SELECT e.name, m.name
FROM employee e, employee m
WHERE e.manager_emp._id = m.emp_id

Q: Describe the query to find all the leaf nodes (i.e. show all employees that do not manage anyone) in Mastering Oracle SQL Employee hierarchical table.
A: SELECT *
FROM employee e
WHERE emp_id NOT IN
(SELECT manager_emp_id FROM employee
 WHERE manager_emp_id IS NOT NULL)
Note: you need IS NOT NULL, since NULLs can't be compared to any data value. If you leave out IS NOT NULL, you get "No rows selected".

Oracle provides three main clauses to perform hierarchical queries: START WITH...CONNECT BY clause, PRIOR operator, and LEVEL pseudocolumn.

Q: Syntax for START WITH...CONNECT BY clause (Oracle SQL)
A: [START WITH condition1] CONNECT BY condition2

Q: [[START WITH condition1] CONNECT BY condition2]
What does START WITH condition1 do? (Oracle SQL)
A: Specifies the root row(s) of the hierarchy - all rows that satisfy condition1 considered root rows.

Q: [[START WITH condition1] CONNECT BY condition2]
What happens if you don't specify START WITH condition1? (Oracle SQL)
A: All rows considered root rows (usually not what you want)

Q: [[START WITH condition1] CONNECT BY condition2]
What does CONNECT BY condition2 do? (Oracle SQL)
A: Specifies relationship between parent rows and child rows in hierarchy.

Q: [[START WITH condition1] CONNECT BY condition2]
What operator must condition2 contain? (Oracle SQL)
A: PRIOR

Q: What does PRIOR operator specify? (Oracle SQL)
A: Tells you which column refers to the parent row in a heirarchical query.

Q: Where is the PRIOR operator used? (Oracle SQL)
A: In the condition associted with CONNECT BY clause in a hierarchical query.

Q: Query listing all employees working under manager(s) with last_name='JONES' in Mastering Oracle SQL Employee hierarchical table (entire 'tree' - people working under JONES, people working under those people, etc.)
Table: employee
Variables: last_name, emp_id, manager_emp_id
A: SELECT last_name, emp_id, manager_emp_id
FROM employee
START WITH last_name = 'JONES'
CONNECT BY manager_emp_id = PRIOR emp_id;

Q: Under what conditions can you use the LEVEL pseudocolumn in Oracle SQL?
A: Whenever you use the START WITH...CONNECT BY clauses in a hierarchical query.

Q: What does the LEVEL pseudocolumn in Oracle SQL do?
A: Returns the level number for each record in hierarchical query using the START WITH...CONNECT BY clause (i.e. the level in the hierarchical tree)

Q: Query listing # employees at each "level" of theMastering Oracle SQL Employee hierarchical table.
Table: employee
Variables: emp_id, manager_emp_id
A: SELECT LEVEL, COUNT(emp_id)
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY  PRIOR emp_id = manager_emp_id
GROUP BY LEVEL;

Q: Can the WHERE clause be placed before or after the START WITH...CONNECT BY clause? (Oracle SQL)
A: Before

Q: Provide the Oracle SQL hierarchical query to get a table of all the employees and the sum of salaries of all the employees in that employees "tree".
A: SELECT t2.lname, t2.salary,
(SELECT SUM(t1.salary) FROM employee t1
 START WITH t1.lname = t2.lname
 CONNECT BY t1.manager_emp_id = PRIOR t1.emp_id) sum_salary
from employee t2;

Q: SYS_CONNECT_BY_PATH(column, char) (Oracle SQL)
A: Oracle SQL function used with hierarchical queries, returns a list of each value of a column from root node to current node separating values by the character string you provide.

Q: NOCYCLE (Oracle SQL)
A: Oracle SQL keyword used in the CONNECT BY clause to ignore any cycles in the hierarchical query.

Q: CONNECT_BY_ISCYCLE (Oracle SQL)
A: Oracle SQL pseudocolumn used to identify any cycles in a hierarchical query.

Q: What statement must CONNECT_BY_ISCYCLE be used with? (Hint - NOT START WITH...CONNECT BY, although that is correct) (Oracle SQL)
A: NOCYCLE

Q: In what statement must NOCYCLE expression appear in Oracle SQL?
A: CONNECT BY

Q: CONNECT_BY_ISLEAF (Oracle SQL)
A: Oracle SQL pseudocolumn that returns 1 if current row is leaf, 0 if current row is not a leaf (applies only to hierarchical queries).

Q: Oracle SQL Function Syntax: [...]
Logic Equivalent: [...]
A: DECODE(E1, E2, E3, E4); IF E1=E2 THEN E3 ELSE E4

Q: Oracle SQL Function Syntax: [...]
Logic Equivalent: [...]
A: NULLIF(E1, E2); IF E1=E2 THEN NULL ELSE E1

Q: Oracle SQL Function Syntax: [...]
Logic Equivalent: [...]
A: NVL(E1, E2); IF E1 IS NULL THEN E2 ELSE E1

Q: Oracle SQL Function Syntax: [...]
Logic Equivalent: [...]
A: NVL2(E1, E2, E3); IF E1 IS NULL THEN E3 ELSE E2

Q: The CASE expression enables conditional logic within a SQL statement, much like DECODE. Does the Mastering Oracle SQL author recommend using CASE or DECODE?
A: CASE

Q: Two main types of [...] expressions in Oracle SQL: [...] and [...].
A: CASE; searched; simple

Q: Oracle SQL Searched Case Expression Syntax
A: CASE
   WHEN C1 THEN R1
   WHEN C2 THEN R2
   ...
   WHEN CN THEN RN
   ELSE RD
END
(Oracle SQL)

Q: Oracle SQL Searched Case Expression Syntax:
CASE
   WHEN C1 THEN R1
   WHEN C2 THEN R2
   ...
   WHEN CN THEN RN
   ELSE RD
END
What does C1, ..., CN represent?
A: Conditions

Q: Oracle SQL Searched Case Expression Syntax:
CASE
   WHEN C1 THEN R1
   WHEN C2 THEN R2
   ...
   WHEN CN THEN RN
   ELSE RD
END
What does R1, ..., RN represent?
A: Results

Q: Oracle SQL Simple Case Expression Syntax:
CASE E0
   WHEN E1 THEN R1
   WHEN E2 THEN R2
   ...
   WHEN EN THEN RN
   ELSE RD
END
Describe how this works.
A: Each expression E1...EN compared to expression E0. If match found, corresponding result returned, otherwise, default result (RD) returned.

Q: Oracle SQL Simple Case Expression Syntax
A: CASE E0
   WHEN E1 THEN R1
   WHEN E2 THEN R2
   ...
   WHEN EN THEN RN
   ELSE RD
END
(Oracle SQL)

Q: Global Index (Oracle SQL)
A: An index that is kept intact across all partitions in a partitioned table.

Q: Local Index (Oracle SQL)
A: An index that is split into pieces corresponding to partitions in a partitioned table.

Q: Range Partitioning (Oracle SQL)
A: Partitioning a table over ranges of values for one or more columns of the table. (Oracle SQL)

Q: What does Mastering Oracle SQL say is the "simplest and most widely implemented form of range partitioning"?
A: Partition using a single date column

Q: Hash Partitioning (Oracle SQL)
A: Type of partitioning that allows you to specify the number of partitions and the partition columns (partition key) but leaves the allocation of rows to partitions up to Oracle.

Q: As you add new data to a hash partitioned table, how does Oracle decide which partition to assign the data to?
A: Attempts to evenly spread the data across the partitions by applying a hashing function to teh data in the partition key.

Q: What kind of columns are best to assign as the partition key in hash partitioned tables? (Mastering Oracle SQL)
A: High cardinality columns, i.e. # distinct values large compared to size of table.

Q: Why is it best to have high cardinality columns as the partition key in hash partitioned tables? (Mastering Oracle SQL)
A: Ensures even distribution across partitions - otherwise, partitions can become unbalanced / cause performance unpredictable and making administration difficult.

Q: Composite Range-Hash Partitioning (Oracle SQL)
A: Type of partitioning that allows you to create multiple range partitions, each of which contains 2 or more hash subpartitions (Oracle SQL).

Q: In what situations do you typically want to use Composite Range-Hash Partitioning? (Mastering Oracle SQL)
A: When range partitioning appropriate for type of data stored in table, but you want finer granularity of partitioning than range partitioning alone.

Q: List Partitioning (Oracle SQL)
A: Type of partitioning that allows table to be partitioned by one or more distinct values of a particular column (Oracle SQL).

Q: Composite Range-List Partitioning (Oracle SQL)
A: Type of partitioning that allows you to partition by data range, then subpartition via a list (Oracle SQL).

Q: Partition Pruning (Oracle SQL)
A: Database optimizer using information in the WHERE clause to eliminate some of the partitions from consideration.

Q: [...] is also known as [...].
A: Partition pruning; partition elimination

Q: Is PL/SQL declarative or procedural? (Oracle SQL)
A: Procedural

Q: [...] stored in the database can be one of two types: [...] or [...] (Oracle SQL).
A: PL/SQL routines; stored procedures; stored functions

Q: PL/SQL stored functions and procedures are essentially identical, but what is the difference? (Oracle SQL)
A: Functions have a return type, procedures do not.

Q: In PL/SQL, stored functions and procedures can be complied individually, or they may be grouped together into packages. What do the Mastering Oracle SQL authors recommend you always do?
A: Always store in packages - you'll never be sorry you bundled your PL/SQL code into packages, but you'll eventually be sorry if you don't.

Q: PL/SQL packages consist of two distinct parts: [...] and [...] (Oracle SQL).
A: package specification; package body

Q: PL/SQL packages consist of two distinct parts: i) package specification - WHAT IS THIS? (Oracle SQL).
A: Defines signatures of the package's public procedures and functions.

Q: PL/SQL packages consist of two distinct parts: ii) package body - WHAT IS THIS? (Oracle SQL).
A: Contains code for public procedures / functions and also any code for private functions / procedures not included in package specification.

Q: What kind of PL/SQL functions can be marked as DETERMINSTIC in the function declaration? (Oracle SQL)
A: Functions that always return same result for any given input parameter (i.e. don't depend on data stored in database or in package variables)

Q: If a PL/SQL function always returns same result for any given input parameter, what keyword should you add to the function declaration? (Oracle SQL)
A: DETERMINISTIC

Q: Why is it a good idea to define deterministic functions in PL/SQL as DETERMINISTIC in the function definition? (Oracle SQL)
A: Allows Oracle to perform optimizations

Q: What is the Oracle SQL concept called that specifies whether a stored function might have unintended consequences when called from a SQL statement?
A: Purity Level

Q: What does RNDS refer to in Oracle SQL?
A: Reads no database state (purity level)

Q: What does RNPS refer to in Oracle SQL?
A: Reads no package state (purity level)

Q: What does WNDS refer to in Oracle SQL?
A: Writes no database state (purity level)

Q: What does WNPS refer to in Oracle SQL?
A: Writes no package state (purity level)

Q: What does purity level refer to in Oracle SQL?
A: Specifies whether a stored function might have unintended consequences when called from a SQL statement

Q: How do you specify purity levels of packaged functions in Oracle SQL?
A: Adding a pragma, or compiler directive to the package spec.
E.g.
CREATE OR REPLACE PACKAGE my_pkg AS
  FUNCTION my_func(arg1 IN NUMBER) RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES(my_func, RNPS, WNPS, WNDS);
END my_pkg

Q: Pragma (aka directive)
A: A language construct that specifies how a compiler (or other translator) should process its input

Q: Object Type (Oracle SQL)
A: User defined data type combining data and related methods to model complex entities (Oracle SQL)

Q: How are Oracle SQL object types different than the class definitions in C++ or Java?
A: Built-in persistence - a table can be defined to store object type in database, and can be directl manipulated via SQL.

Q: What type of statement is this? (Oracle SQL)
CREATE TYPE equity AS OBJECT (
  issuer_id NUMBER,
  outstanding_shares NUMBER,
MEMBER PROCEDURE
  apply_split(split_ratio in VARCHAR2)
)
NOT FINAL;
A: Object type definition

Q: What does NOT FINAL do?
CREATE TYPE equity AS OBJECT (
  issuer_id NUMBER,
  outstanding_shares NUMBER,
MEMBER PROCEDURE
  apply_split(split_ratio in VARCHAR2)
)
NOT FINAL;
A: Allows for subtypes to be defined in the future

Q: What clause would you use to define the body of the apply_split procedure? (Oracle SQL)
CREATE TYPE equity AS OBJECT (
  issuer_id NUMBER,
  outstanding_shares NUMBER,
MEMBER PROCEDURE
  apply_split(split_ratio in VARCHAR2)
)
NOT FINAL;
A: CREATE TYPE BODY

Q: What does UNDER equity mean? (Oracle SQL)
CREATE TYPE preferred_stock UNDER equity (
  last_dividend_date DATE,
  last_dividend_amount NUMBER,
CONSTRUCTOR FUNCTION preferred_stock(last_div_dt DATE,
  last_div_amt NUMBER, eq equity) RETURN SELF AS RESULT
)
FINAL;
A: Means preferred_stock is a subtype of equity object type

Q: Two options you have if you want to store instance of object types in a database (Oracle SQL):
1) [...]
2) [...]
A: Create column in database; Create special type of table "object table" to store instances of object type.

Q: Suppose you have equity object type in Oracle SQL and you create an object table for it. How would you get the object type's attributes from the table?
A: Directly, just as you would with an ordinary table:
SELECT issuer_id, ticker
FROM equities;

Q: Suppose you have equity object type in Oracle SQL and you create an object table for it. How do you retrieve data in the equities table as an instance of an equity object rather than a set of attributes?
A: Use VALUE function:
SELECT VALUE(e)
FROM equities e
WHERE e.ticker = 'ACMW';
Aside - you must use table alias with VALUE function - cannot pass table name to VALUE.

Q: Two [...] data types available in Oracle SQL:
1) [...]
2) [...]
A: collection; Variable arrays; Nested tables

Q: Variable Arrays (Oracle SQL)
A: Collection data type that contains data that is ordered (accessed positionally) and bounded (max size) (Oracle SQL)

Q: Oracle SQL syntax to create a variable array called resupply_dates that contains up to 100 DATEs.
A: CREATE TYPE resupply_dates AS VARRAY(100) OF DATE;
(Oracle SQL)

Q: Nested Tables (Oracle SQL)
A: Collection data type that contains data that is not ordered (not accessed positionally) and unbounded (no max size) (Oracle SQL)

Q: Oracle SQL syntax to create a nested table called resupply_dates that contains DATEs.
A: CREATE TYPE resupply_dates IS TABLE OF DATE;
(Oracle SQL)

Q: How do you instantiate collections (variable arrays and nested tables)? (Oracle SQL)
A: Constructor function - system generated function that has the same name as the collection

Q: What command is ROLLUP an extension of? (Oracle SQL)
A: GROUP BY

Q: ROLLUP can only appear in a query with a [...] clause (Oracle SQL)
A: GROUP BY

Q: ROLLUP syntax (Oracle SQL)
A: SELECT ...
FROM ...
GROUP BY ROLLUP (ordered list of grouping columns)

Q: Describe in words what ROLLUP does (Oracle SQL)
A: Groups selected rows based on expressions in the GROUP BY clause and prepares summary row for each group.

Q: CUBE can only appear in a query with a [...] clause (Oracle SQL)
A: GROUP BY

Q: Describe in words what the CUBE operation does (Oracle SQL)
A: Generates subtoals for all possible combinations of the grouping columns.

Q: Syntax for CUBE operation (Oracle SQL)
A: SELECT ...
FROM ...
GROUP BY CUBE (list of grouping columns)

Q: Does ROLLUP(a, b) produce the same result as ROLLUP(b, a)? (Oracle SQL)
A: No

Q: Does CUBE(a, b) produce the same result as CUBE(b, a)? (Oracle SQL)
A: Yes

Q: The [...] function is meant to be used in conjunction with either a [...] or a [...] operation (Oracle SQL)
A: GROUPING; ROLLUP; CUBE

Q: Describe in words what the GROUPING function does (Oracle SQL)
A: Takes grouping column name as input, returns 1 if column's value is NULL as result of aggregation (ROLLUP or CUBE); otherwise 0.

Q: Describe in words what the GROUPING function is typically used for (Oracle SQL)
A: Improve readability of query output when using CUBE and ROLLUP by filling in NULL values resulting from these aggregations with something meaningful

Q: GROUPING SETS is an extension of the [...] clause (Oracle SQL)
A: GROUP BY

Q: Describe what GROUPING SETS does in words (Oracle SQL)
A: Generates summary information similar to ROLLUP / CUBE without including all rows produced by regular GROUP BY operation.

Q: GROUPING SETS syntax (Oracle SQL)
A: SELECT ...
FROM
GROUP BY GROUPING SETS (list of grouping columns)

Q: Composite Column (Oracle SQL)
A: Collection of 2 or more columns with their values treated as one for grouping computation (Oracle SQL)

Q: What is the difference between ROW_NUMBER, DENSE_RANK, and RANK functions in Oracle SQL?
A: How they handle ties / duplicates:
ROW_NUMBER: numbers arbitrarily assigned to duplicates
DENSE_RANK: same ranking assigned to duplicates
RANK: same ranking assigned to duplicates and gap appears in sequence of rankings

Q: In ROW_NUMBER, DENSE_RANK, and RANK functions in Oracle SQL, which one assigns numbers arbitrarily to duplicates?
A: ROW_NUMBER

Q: In ROW_NUMBER, DENSE_RANK, and RANK functions in Oracle SQL, which one(s) assigns the same ranking to duplicates?
A: DENSE_RANK and RANK

Q: In ROW_NUMBER, DENSE_RANK, and RANK functions in Oracle SQL, which one(s) assigns the same ranking to duplicates and gap appears in sequence of rankings?
A: RANK

Q: Equiheight Buckets (Oracle SQL)
A: Subdividing table so each "bucket" contains the same # rows.

Q: NTILE (Oracle SQL)
A: Oracle SQL function to create equiheight buckets

Q: WIDTH_BUCKET (Oracle SQL)
A: Oracle SQL function to create equiwidth buckets

Q: Equiwidth Buckets (Oracle SQL)
A: Subdividing table rows into groups where each "bucket" distributes the range of values of the data evenly. (hint: not Partitioning)

Q: Describe difference between equiheight and equiwidth buckets (Oracle SQL)
A: Equiheight buckets divide data so each bucket has same # rows
Equiwidth buckets divide data so each bucket contains evenly distributed range of values (e.g. 1-10, 11-20, 21-30.) - each bucket could contain a different number of records depending on values in data.

Q: CUME_DIST (Oracle SQL)
A: Oracle SQL ranking function that calculates (# rows with <= ranking) / (total # rows in the partition)

Q: PERCENT_RANK (Oracle SQL)
A: Oracle SQL ranking function that calculates (row ranking #) / (total # rows in the partition)

Q: What ranking function does CUME_DIST use for its ranking? (Oracle SQL)
A: DENSE_RANK

Q: What ranking function does PERCENT_RANK use for its ranking? (Oracle SQL)
A: DENSE_RANK

Q: Window Function (SQL)
A: Performs aggregate-like operation on set of query rows, but instead of grouping rows into single result row, it produces a result for each query row.

Q: What's the difference between an analytic function and an aggregate function? (Oracle SQL)
A: Analytic function - returns multiple rows per group
Aggregate function - returns a single row per group

Analytic function syntax (Oracle SQL)

Q: Analytic Clause syntax (Oracle SQL)
A: (Oracle SQL)

Q: Query Partition Clause syntax (Oracle SQL)
A: (Oracle SQL)

Q: Oracle SQL Windowing Clause that specifies an "unbounded" window (performs analytic function over entire result set)
A: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
(Oracle SQL)

Q: Oracle SQL Analytic Function that identifies the maximum sales for a month, up to and including the current month.
A: MAX(sales) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Q: Oracle SQL Analytic Function that provides the cumulative sum of sales for each month.
A: SUM(sales) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Q: Oracle SQL Analytic Function that provides the rolling average of sales that averages the month before, current month, and month after for each given month (each row represents a month in the table).
A: AVG(sales) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

Q: Oracle SQL Analytic Function that provides the rolling average of sales within 2 days before or after the current row (variables include sales, and order_date).
A: AVG(sales) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND INTERVAL '2' DAY FOLLOWING)
(Oracle SQL)

Q: FIRST_VALUE (Oracle SQL)
A: Oracle SQL analytic function that can be used to to identify the first value in the window.

Q: LAST_VALUE (Oracle SQL)
A: Oracle SQL analytic function that can be used to to identify the last value in the window.

Q: Use the Oracle SQL LAG function to add a column "previous_month_sales" in a monthly sales table.
A: SELECT month, sales, LAG(sales, 1) OVER (ORDER BY month) previous_month_sales
FROM ...
...

Q: Rewrite this query to is uses EXISTS instead of DISTINCT - finds names of customers that have orders (Oracle SQL)
SELECT DISTINCT c.customer_number, c.name
FROM customer c JOIN customer_order o
ON c.customer_number = o.customer_number;
A: SELECT c.customer_number, c.name
FROM customer c
WHERE EXISTS (SELECT 1 FROM customer_order o WHERE c.customer_number = o.customer_number)

Q: When you can write a query using EXISTS or DISTINCT, what should you use?
A: EXISTS - it's faster

Q: When should you use table aliases? (Oracle SQL Best Practices)
A: Always - qualify all columns in a query with table aliases, even those that are not ambiguous.

Q: Why should you qualify all columns in a query with table aliases, even those that are not ambiguous? (Oracle SQL)
A: Oracle avoids extra search when parsing the SQL statement.

Q: Oracle SQL data type for storing XML documents in the database
A: XMLType (Oracle SQL)

Q: [...] is the regular expression equivalent to the [...] predicate (Oracle SQL)
A: REGEXP_LIKE; LIKE

Q: Greediness (in context of Regular Expressions)
A: Term describing the fact that a regular expression will always match as much text as possible

Q: MODEL (Oracle SQL)
A: Oracle SQL clause that lets you treat relational data as a multidimensional array for performing spreadsheet-like operations.

Q: Rewrite the following SQL query using Oracle's old inner join syntax.
SELECT d.name, l.regional_group
FROM department d JOIN location l
ON d.location_id = l.location_ID
A: SELECT d.name, l.regional_group
FROM department d, location l
WHERE d.location_id = l.location_id;

Q: Rewrite the following SQL query using Oracle's old inner join syntax.
SELECT d.name, l.regional_group
FROM department d LEFT OUTER JOIN location l
ON d.location_id = l.location_id;
A: SELECT d.name, l.regional_group
FROM department d, location l
WHERE d.location_id = l.location_id (+);

Q: Old Oracle SQL outer join operator
A: (+)
(Oracle SQL)

Q: Where do you use the old Oracle SQL outer join operator (+) in a query?
A: In a join condition in the WHERE  clause following the field name from the table you want to be the "optional table"

Q: What Oracle SQL data type lets you internalize parent-child relationships in the parent table?
A: Collection

Q: What kind of relationship does an Oracle SQL Collection help you represent?
A: Parent-child (or "one to many")

Q: Does the Oracle SQL Collection data type internalize parent-child relationships in the parent table or child table?
A: Parent table

Q: Collection (Computer Science)
A: Object that groups multiple items together as a single unit (Computer Science)

Q: Oracle SQL query to add dept_count column to employee table which gives total # employees in each employee's department, without using correlated subquery (departments identified with dept_id in employee table).
A: SELECT <existing columns>, COUNT(*)
OVER (PARTITION BY dept_id) dept_count
FROM employee

Source: Oracle SQL
Q: Objects can be stored in two types of tables: [...] and [...].
A: object tables; relational tables
Q: Objects can be stored in two types of tables: [...] and [...].
A: object tables; relational tables

Q: What's the difference between object tables and relational tables? (Oracle SQL)
A: Object tables store only objects
Relational tables store objects with other table data

Q: Oracle SQL syntax for creating [...]:
[...]
A: object table; CREATE TABLE <table name> OF <object type>

Q: What does each row represent in an object table? (Oracle SQL)
A: An object