Oracle Database 12c SQL Questions and Answers
Which three are true about subqueries?
Options:
A subquery can be used in a WHERE clause.
A subquery can be used in a HAVING clause.
=ANY can only evaluate the argument against a subcjuery if it returns two or more values.
A subquery cannot be used in a FROM clause.
< any returns true if the argument is less than the lowest value returned by the subquery.
A subquery cannot be used in the select list.
Answer:
A, B, DExplanation:
About the roles and behavior of subqueries in SQL:
A. A subquery can be used in a WHERE clause: Subqueries are often used in WHERE clauses to filter rows based on a condition evaluated against a set of returned values.
B. A subquery can be used in a HAVING clause: Similar to WHERE, subqueries can be used in HAVING clauses to filter groups based on aggregate conditions.
D. <ANY returns true if the argument is less than the highest value returned by the subquery: The
Incorrect options:
C: =ANY evaluates true if the argument matches any single value returned by the subquery, irrespective of the number of values.
E: A subquery can indeed be used in a FROM clause, known as a derived table or inline view.
F: G: A subquery can be used in a SELECT list, particularly when the subquery is designed to return a single value (scalar subquery).
Which two are true about multiple table INSERT statements?
Options:
They always use subqueries.
They can transform a row from a source table into multiple rows in a target table.
The conditional INSERT FIRST statement always inserts a row into a single table.
The conditional INSERT ALL statement inserts rows into a single table by aggregating source rows.
The unconditional INSERT ALL statement must have the same number of columns in both the source and target tables.
Answer:
B, EExplanation:
B: True. Multiple table insert statements, specifically the conditional INSERT ALL, can transform a single row from the source table into multiple rows in one or more target tables depending on the conditions specified in the WHEN clauses.
E: True. The unconditional INSERT ALL statement will insert rows into multiple target tables without any conditions. However, it does not require the same number of columns in both the source and target tables. The INSERT ALL syntax allows you to specify different columns for each target table into which rows will be inserted.
Multiple table insert operations allow for complex insert scenarios, where based on the data, one can insert into different tables or multiple times into the same table based on different conditions.
References:Oracle SQL reference details the use of INSERT ALL and INSERT FIRST clauses for inserting into multiple tables based on conditions specified.
You want to return the current date and time from the user session, with a data type of TIMESTAMP WITH TIME ZONE.
Which function will do this?
Options:
CURRENT DATE
CURRENT_ TIMESTAMP
SYSDATE
LOCALTIMESTAMP
Answer:
DExplanation:
A: CURRENT_DATE returns the current date in the session time zone, not with a TIMESTAMP WITH TIME ZONE data type.
B: CURRENT_TIMESTAMP returns the current date and time in the session time zone with a TIMESTAMP WITH TIME ZONE data type. This is correct.
C: SYSDATE returns the current date and time from the operating system of the database server in the DATE data type.
D: LOCALTIMESTAMP returns the current date and time in the session time zone with a TIMESTAMP data type, without the TIME ZONE.
The TIMESTAMP WITH TIME ZONE data type and relevant functions are documented in the Oracle Database SQL Language Reference 12c.
Which three statements are true about defining relations between tables in a relational database?
Options:
Foreign key columns allow null values.
Unique key columns allow null values
Primary key columns allow null values.
Every primary or unique key value must refer to a matching foreign key value.
Every foreign key value must refer to a matching primary or unique key value.
Answer:
A, EExplanation:
A. Correct. Foreign key constraints can be nullable. This allows the possibility of a row not having a link to another table. B. Incorrect. Unique key constraints do not allow multiple rows to have null values unless the constraint is defined on multiple columns. C. Incorrect. Primary key columns must be NOT NULL and unique across the table. D. Incorrect. Primary or unique key values do not refer to foreign keys; it's the foreign keys that refer to primary or unique keys. E. Correct. Foreign key constraints enforce referential integrity by requiring that each foreign key value matches a primary or unique key value in the related table.
These are standard rules in relational databases, which can be confirmed in the Oracle Database Concepts Guide.
Which three queries execute successfully?
Options:
SELECT (SYSDATE-DATE '2019-01-01') / 1 FROM DUAL;
SELECT 1 / SYSDATE - DATE '2019-01-01' FROM DUAL;
SELECT SYSDATE / DATE '2019-01-01' - 1 FROM DUAL
SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL;
SELECT 1 – SYSDATE- DATE '2019-01-01' FROM DUAL;
SELECT SYSDATE - 1 - DATE'2019-01-01' EROM DUAL;
Answer:
A, D, FExplanation:
In Oracle SQL, date arithmetic can be performed directly, subtracting one date from another to get the number of days between them:
Option A:
SELECT (SYSDATE - DATE '2019-01-01') / 1 FROM DUAL; This query successfully calculates the difference between the current date and a specific date.
Option D:
SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL; This query subtracts a specific date from the current date and then subtracts 1 more day, which will execute successfully.
Option F:
SELECT SYSDATE - 1 - DATE '2019-01-01' FROM DUAL; Similar to D, this will successfully compute the date difference minus one day.
Options B, C, and E are incorrect because:
Option B and Option C: You cannot divide by a date or divide a date by a number.
Option E: You cannot subtract a date from a number.
Examine the description of the EMPLOYEES table:
Which statement will execute successfully, returning distinct employees with non-null first names?
Options:
SELECT DISTINCT * FROM employees WHERE first_ name IS NOT NULL;
SELECT first_ name, DISTNCT last_ name FROM employees WHERE first_ name IS NOT NULL;
SELECT Distinct * FROM employees WHERE first_ name < > NULL;
SELECT first_ name, DISTINCT last_ name FROM employees WHERE first_ name < > NULL;
Answer:
A
For each employee in department 90 you want to display:
1. their last name
2. the number of complete weeks they have been employed
The output must be sorted by the number of weeks, starting with the longest serving employee
first.Which statement will accomplish this?
Options:
SELECT last_name, TRUNC( (SYSDATE - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_ id = 90
ORDER BY tenure ;
SELECT last_name, ROUND( (SYSDATE - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_ id = 90
ORDER BY tenure ;
SELECT last_name, ROUND( (SYSDATE - hire_ date) 17) AS tenure
FROM employees
WHERE department_ id = 90
ORDER BY tenure DESC;
SELECT last_name, TRUNC ( (SYSDATE - - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_id = 90
ORDER BY tenure DESC;
Answer:
DExplanation:
D. True. The TRUNC function, when applied to the difference between SYSDATE and hire_date divided by 7, will return the number of complete weeks of employment. The ORDER BY tenure DESC will sort the result in descending order, starting with the longest-serving employee.
A is incorrect because it does not include the DESC keyword necessary to start with the longest-serving employee. B is incorrect because ROUND could result in rounding up to the next week, which does not give the number of complete weeks. C is incorrect because the division is done by 17 instead of 7, which is likely a typo, and ROUND is used instead of TRUNC.
Which two statements are true about a full outer join?
Options:
It includes rows that are returned by an inner join.
The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.
It includes rows that are returned by a Cartesian product.
It returns matched and unmatched rows from both tables being joined.
It returns only unmatched rows from both tables being joined.
Answer:
A, DExplanation:
In Oracle Database 12c, regarding a full outer join:
A. It includes rows that are returned by an inner join. This is true. A full outer join includes all rows from both joined tables, matching wherever possible. When there's a match in both tables (as with an inner join), these rows are included.
D. It returns matched and unmatched rows from both tables being joined. This is correct and the essence of a full outer join. It combines the results of both left and right outer joins, showing all rows from both tables with matching rows from the opposite table where available.
Options B, C, and E are incorrect:
B is incorrect because the Oracle join operator (+) is used for syntax in older versions and cannot implement a full outer join by using (+) on both sides. Proper syntax uses the FULL OUTER JOIN keyword.
C is incorrect as a Cartesian product is the result of a cross join, not a full outer join.
E is incorrect because it only describes the scenario of a full anti-join, not a full outer join.
Examine this description of the EMP table:
You execute this query:
SELECT deptno AS "departments", SUM (sal) AS "salary"
FROM emp
GROUP | BY 1
HAVING SUM (sal)> 3 000;
What is the result?
Options:
only departments where the total salary is greater than 3000, returned in no particular order
all departments and a sum of the salaries of employees with a salary greater than 3000
an error
only departments where the total salary is greater than 3000, ordered by department
Answer:
CExplanation:
The query uses the syntax GROUP | BY 1 which is not correct. The pipe symbol | is not a valid character in the context of the GROUP BY clause. Additionally, when using GROUP BY with a number, it refers to the position of the column in the SELECT list, which should be written without a pipe symbol and correctly as GROUP BY 1. Since the syntax is incorrect, the database engine will return an error.
You execute this command:
TRUNCATE TABLE depts;
Which two are true?
Options:
It retains the indexes defined on the table.
It drops any triggers defined on the table.
A Flashback TABLE statement can be used to retrieve the deleted data.
It retains the integrity constraints defined on the table.
A ROLLBACK statement can be used to retrieve the deleted data.
It always retains the space used by the removed rows
Answer:
A, DExplanation:
The TRUNCATE TABLE command in Oracle SQL is used to quickly delete all rows from a table:
Option A:
It retains the indexes defined on the table. TRUNCATE does not affect the structure of the table, including its indexes.
Option D:
It retains the integrity constraints defined on the table. TRUNCATE does not remove or disable integrity constraints, except for unenforced foreign keys.
Options B, C, E, and F are incorrect because:
Option B: TRUNCATE does not drop triggers; it only removes all rows.
Option C: Flashback Table cannot be used after a TRUNCATE because TRUNCATE is a DDL operation that does not generate undo data for flashback.
Option E: A ROLLBACK cannot be used after a TRUNCATE because TRUNCATE is a DDL command that implicitly commits.
Option F: TRUNCATE may deallocate the space used by the table, depending on the database version and specific options used with the TRUNCATE command.
Which three statements are true about performing DML operations on a view with no INSTEAD OF triggers defined?
Options:
Insert statements can always be done on a table through a view.
The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
Delete statements can always be done on a table through a view.
Views cannot be used to add rows to an underlying table If the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the primary key columns are not referenced in the defining query of the view.
Views cannot be used to add or modify rows in an underlying table If the defining query of the view contains the DISTINCT keyword.
Answer:
D, FExplanation:
A: Insert statements can be done through a view only if all NOT NULL constraints without default values of the base table are included in the view. Therefore, statement A is incorrect.
B: The WITH CHECK OPTION ensures that all DML operations performed through the view result in data that conforms to the view’s defining query. It affects DELETE as well as other DML operations, making statement B incorrect.
C: Similar to inserts, DELETE operations can be done through a view unless the view contains constructs that inherently do not support it, such as certain joins or set operations. Statement C is generally incorrect.
D: If a view does not include all NOT NULL columns without defaults of the underlying table, it cannot be used to add rows because the missing columns will lack values. This makes statement D correct.
E: This statement is incorrect as primary keys do not affect querying through views; they affect insert and update operations where uniqueness and non-nullability are enforced.
F: If the defining query of a view includes DISTINCT, the view generally cannot be used to perform update or insert operations as it may not be able to uniquely determine rows. This makes statement F correct.
Examine the description of the EMPLOYEES table:
Which statement will fail?
Options:
SELECT department_id, COUNT (*)
FROM employees
HAVING department_ id <> 90 AND COUNT(*) >= 3
GROUP BY department_id;
SELECT department_id, COUNT (*)
FROM employees
WHERE department_ id <> 90 AND COUNT(*) >= 3
GROUP BY department_id;
SELECT department_id, COUNT(*)
FROM employees
WHERE department_id <> 90 HAVING COUNT(*) >= 3
GROUP BY department_id;
SELECT department_id, COUNT(*)
FROM employees
WHERE department_id <> 90 GROUP BY department_id
HAVING COUNT(*) >= 3;
Answer:
BExplanation:
The statement that will fail is B. In Oracle SQL, the WHERE clause cannot contain aggregate functions directly. The HAVING clause is used instead to apply conditions that involve aggregates, but it is applied after the GROUP BY clause.
A. While the HAVING clause is used before the GROUP BY clause which is not standard SQL syntax, Oracle SQL may still execute it successfully due to its flexibility in syntax.
B. This statement will fail because it uses an aggregate function, COUNT(*), in the WHERE clause, which is not allowed. The correct approach is to use the HAVING clause to filter the results of aggregate functions after the GROUP BY clause.
C. This statement is correct; it places the HAVING clause after the GROUP BY clause, applying the filter on the aggregated count.
D. This is a correctly constructed statement with the WHERE clause filtering individual records before grouping, and the HAVING clause filtering groups based on the aggregate function.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "WHERE Clause"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "HAVING Clause"
Evaluate the following SQL statement
SQL>SELECT promo_id, prom _category FROM promotions
WHERE promo_category=’Internet’ ORDER BY promo_id
UNION
SELECT promo_id, promo_category FROM Pomotions
WHERE promo_category = ‘TV’
UNION
SELECT promoid, promocategory FROM promotions WHERE promo category=’Radio’
Which statement is true regarding the outcome of the above query?
Options:
It executes successfully and displays rows in the descend ignore of PROMO CATEGORY.
It produces an error because positional, notation cannot be used in the ORDER BY clause with SBT operators.
It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.
It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.
Answer:
CExplanation:
C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement: The ORDER BY clause in a compound query using UNION should be placed at the very end of the final SELECT statement. Since it's located with the first SELECT, it will be ignored.
Examine this partial query:
SELECT ch.channel_type, t.month, co.country_code, SUM(s.amount_sold) SALES
FROM sales s, times t, channels ch, countries co
WHERE s.time_ id = t.time id
AND s.country_ id = co. country id
AND s. channel id = ch.channel id
AND ch.channel type IN ('Direct Sales', 'Internet')
AND t.month IN ('2000-09', '2000-10')
AND co.country code IN ('GB', 'US')
Examine this output:
Which GROUP BY clause must be added so the query returns the results shown?
Options:
GROUP BY ch.channel_type, t.month, co.country code;
GROUP BY ch.channel_type,ROLLUP (t month, co. country_ code) ;
GROUP BY CUBE (ch. channel_ type, t .month, co. country code);
GROUP BYch. channel_ type, t.month,ROLIUP (co. country_ code) ;
Answer:
AExplanation:
A. True. The GROUP BY clause needs to include all non-aggregated columns from the SELECT list to provide the correct grouping for the output. The output shown in the image indicates that the data is grouped by channel_type, month, and country_code.
B, C, and D are incorrect because:
B includes a ROLLUP which would introduce subtotals that are not reflected in the output shown.
C specifies a CUBE, which would produce all possible combinations of groupings including the grand total, which is not shown in the output.
D specifies a ROLLUP on country_code only, which would not correctly group by channel_type and month.
Examine the description of the EMPLOYEES table：
You write this failing statement:
SELECT dept_no AS department_id, MAX (salary) As max_sal
FROM employees
WHERE salary >10000
GROUP BY department_id
ORDER BY max_sal;
Which clause causes the error?
Options:
ORDER BY
WHERE
GROUP BY
SELECT
Answer:
CExplanation:
In the SQL statement provided, the error is caused by the GROUP BY clause. In Oracle SQL, when using GROUP BY, the expression in the GROUP BY clause must match exactly the expression in the SELECT list. The GROUP BY clause should reference the column name dept_no as mentioned in the SELECT list, not the alias department_id which is defined within the same SELECT statement.
The corrected SQL statement should be:
SELECT dept_no AS department_id, MAX(salary) As max_sal FROM employees WHERE salary > 10000 GROUP BY dept_no -- Changed from 'department_id' to 'dept_no' ORDER BY max_sal;
References:
Oracle Documentation on GROUP BY clause: GROUP BY Clause
Which two are true?
Options:
CONCAT joins two or more character strings together.
FLOOR returns the largest integer less than or equal to a specified number.
CONCAT joins two character strings together.
INSTR finds the offset within a string of a single character only.
INSTR finds the offset within a character string, starting from position 0.
FLOOR returns the largest positive integer less than or equal to a specified number.
Answer:
A, BExplanation:
The CONCAT function and FLOOR function in Oracle SQL have specific behaviors:
A. CONCAT function joins two or more character strings into one string, making this statement true.
B. FLOOR function returns the largest integer that is less than or equal to the specified number, making this statement true.
C. While CONCAT can join two strings together, this statement is incomplete as it can join more than two.
D. INSTR can find the offset of a substring within a string, not just a single character.
E. INSTR starts searching the string from position 1 in Oracle SQL, not position 0.
F. FLOOR does return the largest integer less than or equal to the specified number, but it can be any integer, not just positive ones.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Single-Row Functions"
In the PROMOTIONS table, the PROMO_BEGTN_DATE column is of data type DATE and the default date format is DD-MON-RR.
Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query?
Options:
TO_NUMBER(PROMO_BEGIN_DATE)-5 will return number
TO_DATE(PROMO_BEGIN_DATE * 5) will return a date
PROMO_BEGIN_DATE-SYSDATE will return a number.
PROMO_BEGIN_DATE-5 will return a date.
PROMO_BEGIN_DATE-SYSDATE will return an error.
Answer:
C, DExplanation:
A. This statement is incorrect because TO_NUMBER expects a character string as an argument, not a date. Directly converting a date to a number without an intermediate conversion to a character string would result in an error. B. This statement is incorrect. Multiplying a date by a number does not make sense in SQL, and attempting to convert such an expression to a date will also result in an error. C. This statement is correct. Subtracting two dates in Oracle SQL results in the number of days between those dates, hence the result is a number. D. This statement is correct. Subtracting a number from a date in Oracle SQL will subtract that number of days from the date, returning another date. E. This statement is incorrect. As stated in C, subtracting a date from SYSDATE correctly returns the number of days between those two dates, not an error.
These concepts are explained in the Oracle Database SQL Language Reference, which details date arithmetic in SQL.
Which two queries only return CUBE?
Options:
SELECT shape FROM bricks JOIN boxes ON weight >= min_weight AND weight < max_weight;
SELECT shape FROM bricks JOIN boxes ON weight > min_weight;
SELECT shape FROM bricks JOIN boxes ON weight BETWEEN min_weight AND max_weight;
SELECT shape FROM bricks JOIN boxes ON weight < max_weight;
SELECT shape FROM bricks JOIN boxes ON NOT (weight > max_weight);
Answer:
A, EExplanation:
Based on the table structure given in the image, to return the value 'CUBE' from the 'bricks' table when joined with 'boxes', the condition must ensure that the weight of the bricks is within the allowed weight range specified in the 'boxes' table for a 'SMALL' box size.
A. True. Since MAX_WEIGHT is 0, a comparison using >= min_weight AND weight < max_weight will only return rows where the weight is less than 0, which is impossible for actual weight values, suggesting there might be a mistake in the data provided or the comparison logic.
E. True. NOT (weight > max_weight) effectively translates to 'where weight is less than or equal to max_weight'. However, since MAX_WEIGHT is 0, this condition would only be true if the weight is not greater than 0, which can only happen if the weight is 0 or less. This seems to indicate an anomaly where either the data is incorrect, or the condition is meant to handle a case where the weight is zero or possibly a negative placeholder value.
Both B and D will potentially return more than just 'CUBE' if there are bricks with weights greater than MIN_WEIGHT. C is incorrect because BETWEEN is inclusive, and there are no weights that are both greater than or equal to MIN_WEIGHT and less than or equal to MAX_WEIGHT when MAX_WEIGHT is 0.
Examine the description of the CUSTOMERS table:
Which two SELECT statements will return these results:
CUSTOMER_ NAME
--------------------
Mandy
Mary
Options:
SELECT customer_ name FROM customers WHERE customer_ name LIKE ' % a % ’ ;
SELECT customer_ name FROM customers WHERE customer name LIKE 'Ma%' ;
SELECT customer_ name FROM customers WHERE customer_ name='*Ma*';
SELECT customer_ name FROM customers WHERE UPPER (customer_ name ) LIKE 'MA*. ;
SELECT customer_ name FROM customers WHERE customer name LIKE 'Ma*';
SELECT customer_ name FROM customers WHERE UPPER (customer name) LIKE 'MA&';
SELECT customer_ name FROM customers WHERE customer_ name KIKE .*Ma*';
Answer:
B, DExplanation:
The SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Here are the evaluations of the options:
A: The pattern ' % a % ’ will match any customer names that contain the letter 'a' anywhere in the name, not necessarily those starting with 'Ma'.
B: This is correct as 'Ma%' will match any customer names that start with 'Ma'.
C: In SQL, the wildcard character is '%' not '*', therefore, the pattern 'Ma' is incorrect.
D: This is the correct syntax. 'UPPER (customer_ name ) LIKE 'MA%'' will match customer names starting with 'Ma' in a case-insensitive manner.
E: Again, '*' is not a valid wildcard character in SQL.
F: The character '&' is not a wildcard in SQL.
G: The operator 'KIKE' is a typo, and '.Ma' is not valid SQL pattern syntax.
Which three statements are true about views in an Oracle database?
Options:
The WITH CHECK clause prevents certain rows from being displayed when querying the view.
The WITH CHECK clause prevents certain rows from being updated or inserted.
Tables in the defining query of a view must always exist in order to create the view.
Date Manipulation Language (DML) can always be used on views.
Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error.
Views can be updated without the need to re-grant privileges on the view.
Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error.
Answer:
B, E, GExplanation:
For question 138 regarding Oracle views, the correct answers are B, E, and G:
B. The WITH CHECK OPTION clause prevents certain rows from being updated or inserted: This clause ensures that all data modifications performed through the view result in data that conforms to the view's defining query.
E. Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error: Views that involve aggregate functions, GROUP BY clauses, or DISTINCT cannot be directly modified with DML operations such as DELETE, because the underlying data manipulation would be ambiguous.
G. Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error: Similarly to DELETE, INSERT operations into a view that uses a GROUP BY clause are generally not allowed because it's unclear how the new rows should be aggregated into the existing groups.
Other options are incorrect because:
A: The WITH CHECK OPTION clause does not prevent rows from being displayed; it restricts DML operations.
C: Views can be created with non-existent underlying tables or views using the FORCE option in Oracle.
D: Not all views are updatable, especially those involving joins, group functions, or distinct aggregation.
F: Updating a view does not impact the granted privileges unless the definition of the view changes.
Which two statements are true about selecting related rows from two tables based on entity relationship diagram (ERD)?
Options:
Relating data from a table with data from the same table is implemented with a self join.
An inner join relates rows within the same table.
Rows from unrelated tables cannot be joined.
Implementing a relationship between two tables might require joining additional tables.
Every relationship between the two tables must be implemented in a Join condition.
Answer:
A, DExplanation:
Understanding the functionality of SQL joins and their application in a relational database:
Option A: Correct. A self join is used to relate or compare rows within the same table. It typically involves using table aliases to distinguish the different instances of the table in the query.
Option B: Incorrect. An inner join relates rows between two different tables based on a common condition, not within the same table.
Option C: Incorrect. SQL allows joining of rows from any tables, whether directly related by foreign keys or not, as long as there is a logical condition that can relate their columns.
Option D: Correct. Sometimes establishing a relationship between two specific tables might require joining through one or more intermediary tables, particularly in cases involving complex relationships or normalization.
Option E: Incorrect. While joins are often used to implement relationships, not every logical relationship necessitates a join condition, especially if data from only one table is required or if multiple relations are possible but not relevant to the specific query.
Examine the description of the EMPLOYEES table:
Which statement increases each employee's SALARY by the minimum SALARY for their DEPARTM
ENT_ID?
Options:
UPDATE employees e1
SET salary =(SELECT e2. salary + MIN(e2.salary)
FROM employees e2
WHERE e1.department_ id = e2. department_id GROUP BY e2. department_id) ;
UPDATE employees e1
SET salary = salary +
(SELECT MIN(e1. salary)
FROM employees e2
WHERE e1.department_id = e2 .department_id);
UPDATE employees e1
SET salary = salary+(SELECT MIN (salary)
FROM employees e2) ;
UPDATE employees e1
SET salary=
(SELECT e1.salary + MIN(e2.salary)
FROM employees e2
WHERE e1. department_ id = e2.department_id);
Answer:
DExplanation:
The correct statement to increase each employee's salary by the minimum salary for their department is:
D. This option correctly increases each employee's salary by the minimum salary in their department. The subquery calculates the minimum salary for the department of the current row being updated (e1) in the outer UPDATE statement. The subquery does not use GROUP BY since it returns a single value - the minimum salary for the matching department.
A, B, and C are incorrect. Specifically:
A uses a subquery with an aggregate function combined with GROUP BY, which is not needed and will result in an error.
B incorrectly references MIN(e1.salary) within the subquery, which is incorrect since e1 is the alias for the outer query's table and should not be used within the subquery.
C sets the salary to itself plus the minimum salary across all employees, not filtered by the department.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "UPDATE"
The ORDERS table has a primary key constraint on the ORDER_ID column.
The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table.
The constraint is defined with on DELETE CASCADE.
There are rows in the ORDERS table with an ORDER_TOTAL less than 1000.
Which three DELETE statements execute successfully?
Options:
DELETE FROM orders WHERE order_total<1000;
DELETE * FROM orders WHERE order_total<1000;
DELETE orders WHERE order_total<1000;
DELETE FROM orders;
DELETE order_id FROM orders WHERE order_total<1000;
Answer:
A, C, DExplanation:
In Oracle 12c SQL, the DELETE statement is used to remove rows from a table based on a condition. Given the constraints and the information provided, let's evaluate the options:
A. DELETE FROM orders WHERE order_total<1000;: This statement is correctly formatted and will delete rows from the ORDERS table where ORDER_TOTAL is less than 1000. If there is a DELETE CASCADE constraint, corresponding rows in the ORDER_ITEMS table will also be deleted.
B. DELETE * FROM orders WHERE order_total<1000;: This syntax is incorrect. The asterisk (*) is not used in the DELETE statement.
C. DELETE orders WHERE order_total<1000;: This statement is also correctly formatted and is a shorthand version of the DELETE statement without the FROM clause.
D. DELETE FROM orders;: This statement will delete all rows from the ORDERS table, and with DELETE CASCADE, it will also delete all related rows in the ORDER_ITEMS table.
E. DELETE order_id FROM orders WHERE order_total<1000;: This syntax is incorrect because you cannot specify a column after the DELETE keyword.
References:
Oracle Database SQL Language Reference 12c Release 1 (12.1), DELETE Statement
Which three statements are true about sequences in a single instance Oracle database?
Options:
A sequence's unallocated cached values are lost if the instance shuts down.
Two or more tables cannot have keys generated from the same sequence.
A sequence number that was allocated can be rolled back if a transaction fails.
A sequence can issue duplicate values.
Sequences can always have gaps.
A sequence can only be dropped by a DBA.
Answer:
A, C, EExplanation:
Sequences are database objects used to generate unique numeric identifiers. Here's the correct understanding of sequences in Oracle:
A: Correct. Cached sequence values that are not yet used are lost if the database instance shuts down, as the cache is held in memory.
B: Incorrect. The same sequence can be used to generate keys for more than one table. There is no such limitation.
C: Correct. If a transaction using a sequence number is rolled back, the sequence number that was used or allocated is not reused.
D: Incorrect. By their nature and configuration, sequences are designed to avoid issuing duplicate values unless explicitly designed to cycle.
E: Correct. Sequences may have gaps, which can occur due to caching, sequence increment settings, or if a transaction using a sequence number is rolled back.
F: Incorrect. A sequence can be dropped by any user with adequate privileges, not just a DBA.
Which two statements will convert the string Hello world to ello wozid?
Options:
SELECT LOWER (SUBSTR(‘Hello World, 2, 1)) FROM DUAL;
SELECT LOWER (SUBSTR(‘Hello World’, 2)) FROM DUAL;
SELECT LOWER(TRIM(‘H’ FROM ‘Hello World’)) FROM DUAL;
SELECT SUBSTR(‘Hello world’, 2) FROM DUAL;
SELECT INITCAP(TRIM(‘H’ FROM ‘Hello World’)) FROM DUAL;
Answer:
B, DExplanation:
To convert the string "Hello World" to "ello wozid":
Option B: SELECT LOWER(SUBSTR('Hello World', 2)) FROM DUAL;
This will remove the first character and convert the rest to lowercase, but it will not change the 'rld' to 'zid'.
Option D: SELECT SUBSTR('Hello world', 2) FROM DUAL;
This will remove the first character, but the rest of the string remains unchanged and not converted to lowercase.
Options A, C, and E are incorrect because:
Option A: SUBSTR is incorrect here because it is only getting one character instead of the entire string minus the first character.
Option C: While TRIM removes the 'H', LOWER converts all characters to lowercase, but it doesn't address the 'rld' to 'zid' change.
Option E: INITCAP and TRIM will not achieve the desired conversion of 'rld' to 'zid'.
Which three are true about the CREATE TABLE command?
Options:
It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint.
The owner of the table should have space quota available on the tablespace where the table is defined.
It implicitly executes a commit.
It implicitly rolls back any pending transactions.
A user must have the CREATE ANY TABLE privilege to create tables.
The owner of the table must have the UNLIMITED TABLESPACE system privilege.
Answer:
B, C, EExplanation:
A. False - The CREATE TABLE command cannot include a CREATE INDEX statement within it. Indexes to enforce constraints like primary keys are generally created automatically when the constraint is defined, or they must be created separately using the CREATE INDEX command.
B. True - The owner of the table needs to have enough space quota on the tablespace where the table is going to be created, unless they have the UNLIMITED TABLESPACE privilege. This ensures that the database can allocate the necessary space for the table. Reference: Oracle Database SQL Language Reference, 12c Release 1 (12.1).
C. True - The CREATE TABLE command implicitly commits the current transaction before it executes. This behavior ensures that table creation does not interfere with transactional consistency. Reference: Oracle Database SQL Language Reference, 12c Release 1 (12.1).
D. False - It does not implicitly roll back any pending transactions; rather, it commits them.
E. True - A user must have the CREATE ANY TABLE privilege to create tables in any schema other than their own. To create tables in their own schema, they need the CREATE TABLE privilege. Reference: Oracle Database Security Guide, 12c Release 1 (12.1).
F. False - While the UNLIMITED TABLESPACE privilege allows storing data without quota restrictions on any tablespace, it is not a mandatory requirement for a table owner. Owners can create tables as long as they have sufficient quotas on the specific tablespaces.
Which two statements are true about the rules of precedence for operators?
Options:
Arithmetic operators with equal precedence are evaluated from left to right within an expression.
Multiple parentheses can be used to override the default precedence of operators in an expression.
The + binary operator has the highest precedence in an expression in a SQL statements.
NULLS influence the precedence of operators in an expression.
The concatenation operator || is always evaluated before addition and subtraction in an expression.
Answer:
A, BExplanation:
A. True, arithmetic operators of equal precedence are evaluated from left to right within an expression, according to the standard SQL operator precedence. B. True, multiple parentheses can be used in an expression to change the order of operations and override the default precedence of operators.
C, D, and E are not correct because: C. The + binary operator does not have the highest precedence; multiplication and division have higher precedence in SQL. D. NULLS do not influence the precedence of operators in an expression; they may affect the result of an operation but not the order in which operators are evaluated. E. The concatenation operator (||) has lower precedence than arithmetic operators in SQL expressions.
References:
Oracle documentation on operator precedence: Oracle Database SQL Language Reference
Examine the data in the NEW_EMPLOYEES table:
Examine the data in the EMPLOYEES table:
You want to:
1. Update existing employee details in the EMPLOYEES table with data from the NEW EMPLOYEES
table.
2. Add new employee detail from the NEW_ EMPLOYEES able to the EMPLOYEES table.
Which statement will do this:
Options:
MERGE INTO employees e
USING new employees ne
WHERE e.employee_id = ne.employee_ id
WHEN MATCHED THEN
UPDATE SET e.name = ne.name, e.job_id = ne.job_id,e.salary =ne. salary
WHEN NOT MATCHED THEN
INSERT VALUES (ne. employee_id,ne.name, ne.job_id,ne.salary) ;
MERGE INTO employees e
USING new_employees n
ON (e.employee_id = ne.employee_id)
WHEN MATCHED THEN
UPDATE SET e.name = ne.name, e.job id = ne.job_id,e.salary =ne. salary
WHEN NOT MATCHED THEN
INSERT VALUES (ne. employee_id,ne.name,ne.job_id,ne.salary);
MERGE INTO employees e
USING new employees ne
ON (e.employee_id = ne.employee_id)
WHEN FOUND THEN
UPDATE SET e.name =ne.name, e.job_id=ne.job_id, e.salary =ne.salary
WHEN NOT FOUND THEN
INSERT VALUES (ne.employee_id,ne.name,ne.job_id,ne.salary) ;
MERGE INTO employees e
USING new_employees n
WHERE e.employee_id = ne.employee_id
WHEN FOUND THEN
UPDATE SET e.name=ne.name,e.job_id =ne.job_id, e.salary=ne.salary
WHEN NOT FOUND THEN
INSERT VALUES (ne.employee_ id,ne.name,ne.job id,ne.salary) ;
Answer:
BExplanation:
The correct answer to perform the specified update and insert operations is to use the MERGE statement, which is designed to facilitate such "upsert" operations (update or insert).
A. The syntax is incorrect; the WHERE clause is not used with MERGE.
B. This is the correct syntax and use of the MERGE statement. It uses the ON clause to specify the join condition, and it provides actions for WHEN MATCHED and WHEN NOT MATCHED situations.
C. The keywords FOUND and NOT FOUND are not valid in the context of the MERGE statement in Oracle SQL.
D. Similar to option A, the syntax is incorrect because MERGE uses an ON clause, not a WHERE clause, and the keywords FOUND and NOT FOUND are incorrect.
The correct syntax of a MERGE statement includes defining a condition using the ON clause to determine how rows from the source and target tables are matched, then specifying the actions for when rows match (WHEN MATCHED) and when they do not match (WHEN NOT MATCHED).
References:
Oracle Documentation on MERGE:
Which two statements are true about dropping views?
Options:
Views referencing a dropped view become invalid.
Read only views cannot be dropped.
Data selected by a view's defining query is deleted from its underlying tables when the view is dropped.
The creator of a view to be dropped must have the drop any view privilege.
CASCADE CONSTRAINTS must be specified when referential integrity constraints on other objects refer to primary or unique keys in the view to be dropped.
Answer:
A, DWhat is true about non-equijoin statement performance?
Options:
The between condition always performs less well than using the >= and <= conditions.
The Oracle join syntax performs better than the SQL: 1999 compliant ANSI join syntax.
The join syntax used makes no difference to performance.
The between condition always performs better than using the >= and <= conditions.
Table aliases can improve performance.
Answer:
CExplanation:
Performance implications related to non-equijoin SQL statements in Oracle Database are often a topic of optimization:
C. The join syntax used makes no difference to performance: In Oracle Database, the performance of a query involving joins is typically more dependent on factors like the underlying data distribution, indexes, optimizer statistics, and system configuration rather than the syntax (ANSI vs Oracle traditional syntax). The optimizer in Oracle is sophisticated enough to interpret different syntactical expressions of joins and optimize them accordingly.
References:
Oracle Database Performance Tuning Guide 12c, which discusses the impact of different join syntaxes and how Oracle's optimizer handles them.
Which two queries execute successfully?
Options:
SELECT INTERVAL '1' DAY - SYSDATE FROM DUAL;
SELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;
SELECT INTERVAL '1' DAY - INTERVAL '1' MINUTE FROM DUAL;
select INTERVAL '1' DAY +INTERVAL '1' MONTH FROM DUAL;
SELECT SYSDATE “INTERRVAL '1' DAY FROM DUAL;
Answer:
B, CExplanation:
A: This statement will not execute successfully because you cannot subtract a DAY interval from a DATE directly. SYSDATE needs to be cast to a TIMESTAMP first.
B: This statement is correct. It adds an interval of '1' DAY to the current TIMESTAMP.
C: This statement is correct. It subtracts an interval of '1' MINUTE from '1' DAY.
D: This statement will not execute successfully. In Oracle, you cannot add intervals of different date fields (DAY and MONTH) directly.
E: This statement has a syntax error with the quotes around INTERVAL and a misspelling, it should be 'INTERVAL'.
Oracle Database 12c SQL supports interval arithmetic as described in the SQL Language Reference documentation.
Which two statements are true about single row functions?
Options:
CONCAT: can be used to combine any number of values
FLOOR: returns the smallest integer greater than or equal to a specified number
CEIL: can be used for positive and negative numbers
TRUNC: can be used with NUMBER and DATE values
MOD: returns the quotient of a division operation
Answer:
C, DExplanation:
Regarding single row functions in Oracle Database 12c:
C. CEIL: can be used for positive and negative numbers. This is true. The CEIL function rounds a number up to the nearest integer, and it works for both positive and negative values.
D. TRUNC: can be used with NUMBER and DATE values. This is correct. The TRUNC function can truncate numbers or dates to a specified precision.
Options A, B, and E are incorrect:
A is incorrect because CONCAT function typically concatenates two strings; to concatenate more, you must nest CONCAT calls or use the || operator.
B is incorrect because FLOOR returns the largest integer less than or equal to the specified number, not greater.
E is incorrect because MOD returns the remainder of a division operation, not the quotient.
Which three are true?
Options:
LAST_DAY returns the date of the last day of the current ,month onlyu.
CEIL requires an argument which is a numeric data type.
ADD_MONTHS adds a number of calendar months to a date.
ADD_MONTHS works with a character string that can be implicitlyt converted to a DATE data type.
LAST_DAY return the date of the last day the previous month only.
CEIL returns the largest integer less than or equal to a specified number.
LAST_DAY returns the date of the last day of the month for the date argument passed to the function.
Answer:
C, D, GExplanation:
A: LAST_DAY does not only return the last day of the current month; it returns the last day of the month based on the date argument passed to it, which may not necessarily be the current month. Thus, statement A is incorrect.
B: CEIL requires a numeric argument and returns the smallest integer greater than or equal to that number. Thus, statement B is incorrect.
C: ADD_MONTHS function adds a specified number of calendar months to a date. This statement is correct as per the Oracle documentation.
D: ADD_MONTHS can work with a character string if the string can be implicitly converted to a DATE, according to Oracle SQL data type conversion rules. Therefore, statement D is correct.
E: LAST_DAY does not specifically return the last day of the previous month; it returns the last day of the month for any given date. Thus, statement E is incorrect.
F: CEIL returns the smallest integer greater than or equal to the specified number, not the largest integer less than or equal to it. Hence, statement F is incorrect.
G: LAST_DAY returns the last day of the month for the date argument passed to the function, which aligns with the definition in Oracle's SQL reference. Therefore, statement G is correct.
Which two are SQL features?
Options:
providing graphical capabilities
providing variable definition capabilities.
providing database transaction control
processing sets of data
providing update capabilities for data in external files
Answer:
C, DExplanation:
SQL (Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system.
A. False. SQL does not have graphical capabilities; it is a textual language for database interaction.
B. False. SQL supports variable definition, but it is not a core feature of the language. Variables are more commonly defined in procedural extensions to SQL, such as PL/SQL in Oracle.
C. True. SQL provides database transaction control through statements like COMMIT, ROLLBACK, and SAVEPOINT.
D. True. SQL is designed for processing sets of data, allowing for operations such as selection, projection, and joins on sets of rows.
E. False. SQL does not provide capabilities to update data in external files. It operates on data within the database.
Which statement is true about TRUNCATE and DELETE?
Options:
For large tables TRUNCATE is faster than DELETE.
For tables with multiple indexes and triggers is faster than TRUNCATE.
You can never TRUNCATE a table if foreign key constraints will be violated.
You can never tows from a table if foreign key constraints will be violated.
Answer:
AExplanation:
A: True. TRUNCATE is generally faster than DELETE for removing all rows from a table because TRUNCATE is a DDL (Data Definition Language) operation that minimally logs the action and does not generate rollback information. TRUNCATE drops and re-creates the table, which is much quicker than deleting rows one by one as DELETE does, especially for large tables. Also, TRUNCATE does not fire triggers.
References:
Oracle documentation specifies that TRUNCATE is faster because it doesn't generate redo logs for each row as DELETE would.
TRUNCATE cannot be rolled back once executed, since it is a DDL command and does not generate rollback information as DML commands do.
Which three are true about system and object privileges
Options:
WITH GRANT OPTION can be used when granting an object privilege to both users and roles
WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC
Revoking a system privilege that was granted with the WITH ADMIN OPTION has a cascading effect.
Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect
Adding a primary key constraint to an existing table in another schema requires a system privilege
Adding a foreign key constraint pointing to a table in another schema requires the REFERENCEs object privilege
Answer:
A, D, FExplanation:
A: True. The WITH GRANT OPTION allows the grantee to grant the object privileges they received to another user or role. This can be used for both system and object privileges, thereby extending the privilege chain.
D: True. When an object privilege granted with the WITH GRANT OPTION is revoked, it also revokes the privileges that the grantee had re-granted to others. This is referred to as a cascading effect, which can impact multiple users and roles depending on the extent of re-granting.
F: True. To add a foreign key constraint that references a table in another schema, the user must have the REFERENCES object privilege on the target table in the other schema. This privilege is specific and necessary for the integrity constraints involving foreign keys.
Examine the description of the BOOKS_TRANSACTIONS table:
Examine this partial SQL statement:
SELECT * FROM books_transactions
Which two WHERE conditions give the same result?
Options:
WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101','A102');
WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');
WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');
WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));
WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');
Answer:
A, DExplanation:
When writing SQL statements with multiple conditions in the WHERE clause, it's important to understand how logical operators like AND and OR work. These operators are used to filter records based on more than one condition:
The AND operator displays a record if all the conditions separated by AND are TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
The precedence of these operators is also important: AND operations are always evaluated before OR operations unless parentheses are used to explicitly define the order of operations. Therefore, conditions enclosed in parentheses are evaluated first as per the standard SQL operator precedence.
Given this understanding, let's evaluate the options:
Option A uses parentheses to group the borrowed_date and transaction_type conditions together, ensuring these are evaluated first before the OR operator. This means the query will return records that meet both of these conditions or records with member_id 'A101' or 'A102', regardless of other conditions.
Option D does not use parentheses around the borrowed_date and transaction_type conditions but does use them to group the member_id conditions. Since the AND operator has higher precedence than the OR, this query will first evaluate the borrowed_date and transaction_type conditions, then evaluate the grouped member_id conditions. The outcome is records that have a borrowed_date of SYSDATE, a transaction_type of 'RM', and a member_id of either 'A101' or 'A102'.
The results of Options A and D are effectively the same, even though the use of parentheses is different. This is because in both cases, the evaluation of the borrowed_date and transaction_type conditions will be performed first due to their grouping by parentheses in Option A and the precedence of AND over OR in Option D. Therefore, they will both return all records where borrowed_date equals SYSDATE and transaction_type equals 'RM', plus any records where member_id is either 'A101' or 'A102'.
For further details on SQL operator precedence and logical operators, you can refer to Oracle Database SQL Language Reference 12c documentation, specifically the sections on conditional expressions.
Examine the BRICKS table:
You write this query:
SELECT
FROM bricks b1 CROSS JOIN bricks b2
WHERE b1. Weight < b2. Weight:
How many rows will the query return?
Options:
1
16
10
6
4
0
Answer:
DExplanation:
A: Incorrect because the CROSS JOIN of the BRICKS table with itself will produce more than one row.
B: Incorrect because CROSS JOINing a table with four rows with itself produces 16 rows, but the WHERE condition filters these down.
C: Incorrect because while a CROSS JOIN of a table with four rows with itself produces 16 rows, this answer does not take into account the WHERE condition.
D: Correct. The CROSS JOIN will result in 16 combinations (4x4), but the WHERE condition b1.weight < b2.weight will only be true for combinations where the second weight is greater than the first. Since there are 4 distinct weights, there are (42)=6(24)=6 combinations where one weight is less than the other.
E: Incorrect because this does not account for the conditions specified in the WHERE clause.
F: Incorrect because the query will return some rows due to the condition specified.
You execute these commands:
SQL> DEFINE hiredate = ’01-APR -2011’;
SQL> SELECT employee_id, first_name, salary FROM employees WHERE hire date > &hiredate AND manager_id >&mgr_id;
For which substitution variables will you be prompted?
Options:
none
&hiredate and &mgr_id
only &hiredate
only &mgr_id
Answer:
DExplanation:
D. only &mgr_id: Since the hiredate variable is already defined before the SELECT statement, you will not be prompted for &hiredate again. However, &mgr_id has not been defined, so you will be prompted for this substitution variable.
You execute this command:
ALTER TABLE employees SET UNUSED (department_id);
Which two are true?
Options:
A query can display data from the DEPARTMENT_ID column.
The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.
The DEPARTMENT_ID column is set to null for all tows in the table
A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.
No updates can be made to the data in the DEPARTMENT_ID column.
The DEPARTMENT_ID column can be recovered from the recycle bin
Answer:
D, EExplanation:
D. True, after setting the DEPARTMENT_ID column to UNUSED, you can add a new column with the name DEPARTMENT_ID to the EMPLOYEES table. The UNUSED clause does not delete the column, it only marks it as no longer being used.E. True, once a column is marked as UNUSED, you cannot make updates to it. It becomes inaccessible for DML operations.
A, B, C, and F are not correct because: A. Once a column is set to UNUSED, it is not available for queries. B. The storage space is not immediately released after issuing a COMMIT; instead, the actual removal and space reclamation happen when you subsequently issue the DROP UNUSED COLUMNS operation. C. The DEPARTMENT_ID column is not set to null; instead, it's marked as UNUSED, which means it is no longer available for use. F. UNUSED columns are not placed into the recycle bin; they are just marked for deletion, and space can be reclaimed with the DROP UNUSED COLUMNS command.
References:
Oracle documentation on ALTER TABLE: Oracle Database SQL Language Reference
Understanding ALTER TABLE SET UNUSED: Oracle Database Administrator’s Guide
Which two are true about creating tables in an Oracle database?
Options:
A create table statement can specify the maximum number of rows the table will contain.
The same table name can be used for tables in different schemas.
A system privilege is required.
Creating an external table will automatically create a file using the specified directory and file name.
A primary key constraint is manadatory.
Answer:
B, CExplanation:
Regarding creating tables in an Oracle database:
B. The same table name can be used for tables in different schemas: In Oracle, a schema is essentially a namespace within the database; thus, the same table name can exist in different schemas without conflict, as each schema is distinct.
C. A system privilege is required: To create tables, a user must have the necessary system privileges, typically granted explicitly or through roles such as CREATE TABLE or administrative privileges depending on the environment setup.
Incorrect options for all three repeated questions:
A: Oracle SQL does not allow specifying the maximum number of rows directly in a CREATE TABLE statement; this is controlled by storage allocation and database design rather than table creation syntax.
D: Creating an external table does not create the physical file. It merely creates a table structure that allows access to data stored in an external file specified in the directory; the file itself must already exist or be managed outside of Oracle.
E: A primary key constraint is not mandatory for creating tables. While it is a common practice to define a primary key to enforce entity integrity, it is not required by the Oracle SQL syntax for table creation.
These answers and explanations are aligned with Oracle Database 12c SQL documentation and standard practices.
Examine the description of the EMPLOYEES table:
NLS_DATE_FORMAT is set to DD-MON-YY.
Which query requires explicit data type conversion?
Options:
SELECT salary + 120.50 FROM employees;
SELECT SUBSTR(join date, 1, 2)- 10 FROM employees;
SELECT join date 11.’11 salary FROM employees;
SELECT join date FROM employees where join date > *10-02-2018*;
SELECT join date + 20 FROM employees;
Answer:
DExplanation:
For the EMPLOYEES table and given that NLS_DATE_FORMAT is set to DD-MON-YY, the query that requires explicit data type conversion is:
D. SELECT join_date FROM employees where join_date > '10-02-2018'; This query compares the JOIN_DATE column of type DATE with a string '10-02-2018'. The string must be explicitly converted to a DATE using the TO_DATE function with a format mask that matches the NLS_DATE_FORMAT or the string literal.
Options A, B, C, and E do not necessarily require explicit data type conversion:
A does not require conversion because adding a number to a number column is a valid operation.
B is syntactically incorrect; the correct function to extract a substring is SUBSTR(join_date, 1, 2) and that would require an explicit conversion since JOIN_DATE is a DATE and SUBSTR expects a string, but it seems to be a typo.
C uses an invalid concatenation operation with the pipe symbols, which could be a typo or misunderstanding, but would not typically require a data type conversion.
E requires implicit data type conversion because adding a number to a date is valid in Oracle, which adds the number as days to the date.
Which two statements will return the names of the three employees with the lowest salaries?
Options:
SELECT last_name, salary
FROM employees
WHERE ROWNUM<=3
SELECT last_name,salary
FROM employees
ORDER BY salary
FETCH FIRST 3 ROWS ONLY;
SELECT last_name,salary
FROM employees
WHERE ROWNUM<=3
ORDER BY (SELECT salary FROM employees);
SELECT last_name,salary
FROM (SELECT * FROM employees ORDER BY salary)
SELECT last_name,salary
FROM employees
FETCH FIRST 3 ROWS ONLY
ORDER BY salary;
Answer:
BExplanation:
To retrieve the names of the three employees with the lowest salaries, the correct SQL syntax and logic are crucial:
Option B:
SELECT last_name, salary FROM employees ORDER BY salary FETCH FIRST 3 ROWS ONLY;
This query correctly sorts employees by their salary in ascending order and fetches the first three rows only. The FETCH FIRST n ROWS ONLY syntax is a standard way to limit the result set in SQL.
Options A, C, D, and E do not correctly implement the logic for fetching the lowest three salaries due to misuse of ROWNUM or incorrect placement of ORDER BY and FETCH clauses.
The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE
You want to display the date of the first Monday after the completion of six months since hiring.
The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week Which query can be used?
Options:
SELECT emp_id,NEXT_DAY(ADD_MONTHS(hite_date,6),'MONDAY') FROM employees;
SELECT emp_id,ADD_MONTHS(hire_date,6), NEXT_DAY('MONDAY') FROM employees;
SELECT emp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6) FROM employees;
SELECT emp_id,NEXT_DAY(ADD_MONTHS(hire_date,6),1) FROM employees;
Answer:
AExplanation:
The function ADD_MONTHS(hire_date, 6) adds 6 months to the hire_date. The function NEXT_DAY(date, 'day_name') finds the date of the first specified day_name after the date given. In this case, 'MONDAY' is used to find the date of the first Monday after the hire_date plus 6 months.
Option A is correct as it accurately composes both ADD_MONTHS and NEXT_DAY functions to fulfill the requirement.
Options B, C, and D do not provide a valid use of the NEXT_DAY function, either because of incorrect syntax or incorrect logic in calculating the required date.
The Oracle Database SQL Language Reference for 12c specifies how these date functions should be used.
Which three statements are true about GLOBAL TEMPORARY TABLES?
Options:
A GLOBAL TEMPORARY TABLE cannot have PUBLIC SYNONYM.
A GLOBAL TEMPORARY TABLE can have multiple indexes
A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view.
Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO.
A GLOBAL TEMPORARY TABLE can have only one index.
A trigger can be created on a GLOBAL TEMPORARY TABLE
Answer:
B, C, DExplanation:
A. Incorrect. There is no such restriction in Oracle that a global temporary table cannot have a public synonym. B. Correct. Global temporary tables can have more than one index, just like permanent tables. C. Correct. Global temporary tables can be referenced in the defining query of a view. However, any data selected by the view will be session-specific if it comes from the global temporary table. D. Correct. DML operations on global temporary tables do not generate redo log entries for the data changes; however, undo information for these tables is stored in the undo tablespace and can generate redo entries. E. Incorrect. As stated in B, global temporary tables can have more than one index. F. Correct. Triggers can be created on global temporary tables.
These properties are documented in the Oracle Database SQL Language Reference and Oracle Database Concepts Guide.
Which three statements are true about performing Data Manipulation Language (DML) operations on a view In an Oracle Database?
Options:
Insert statements can always be done on a table through a view.
The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
Views cannot be used to query rows from an underlying table if the table has a PRIPOARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.
Views cannot be used to add on modify rows in an underlying table if the defining query of the view contains aggregating functions.
Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
Answer:
D, E, FExplanation:
When performing DML operations on a view, certain restrictions apply:
D. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword: If the defining query of a view contains the DISTINCT keyword, it cannot be used for certain DML operations, such as INSERT and UPDATE, because the set of rows it represents is not directly modifiable.
E. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions: Aggregating functions create a result that summarises multiple rows and cannot be reversed to point to a single row for modification.
F. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view: If a column with a NOT NULL constraint is not included in the view's defining query, and it does not have a default value, it is not possible to insert through the view because it would violate the NOT NULL constraint.
References:
Oracle Database SQL Language Reference 12c, particularly the sections on views and the restrictions on DML operations through views.
Which two actions can you perform with object privileges?
Options:
Create roles.
Delete rows from tables in any schema except sys.
Set default and temporary tablespaces for a user.
Create FOREIGN KEY constraints that reference tables in other schemas.
Execute a procedure or function in another schema.
Answer:
B, EExplanation:
Regarding object privileges in an Oracle database:
B. Delete rows from tables in any schema except sys: Object privileges include DELETE on tables, which can be granted by the owner of the table or a user with adequate privileges, excluding system schemas like SYS due to their critical role.
E. Execute a procedure or function in another schema: EXECUTE is a specific object privilege that can be granted on procedures and functions, allowing users to run these objects in schemas other than their own.
Incorrect options:
A: Creation of roles is related to system privileges, not object privileges.
C: Setting default and temporary tablespaces for a user involves system-level operations, not object-level privileges.
D: Creation of foreign key constraints involves referencing rights, which, while related, are not directly granted through object privileges but need appropriate REFERENCES permission.
Evaluate these commands which execute successfully CREATE SEQUENCE ord_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 100000
CYCLE
CACHE 5000;
Create table ord_items(
ord_no number(4) default ord_seq.nextval not null,
Item_no number(3),
Qty number(3),
Expiry_date date,
Constraint it_pk primary key(ord_no,item_no),
Constraint ord_fk foreign key (ord_no) references orders(ord_no));
Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?
Options:
Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.
Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.
Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
IF sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.
Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.
Answer:
B, DExplanation:
Sequences and default values in Oracle play a crucial role in providing unique values for table columns.
Statement B is correct: When a row is inserted into ORD_ITEMS without an explicit value for ORD_NO, the ORD_NO column gets the next number from the ORD_SEQ sequence due to the DEFAULT ord_seq.nextval clause.
Statement D is correct: If the ORD_SEQ sequence is dropped, Oracle would not be able to get the next value for ORD_NO from ORD_SEQ, and unless another default is specified, the default for ORD_NO would be NULL.
Statements A, C, and E are incorrect for the following reasons:
A is incorrect because the use of the sequence in the default clause of the table definition automatically grants the necessary permissions to use the sequence when inserting into the table.
C is incorrect because the sequence is defined to cycle when it reaches its MAXVALUE and not after every 5000 numbers. It is set to cycle, but the cycling event is triggered by the MAXVALUE limit.
E is incorrect because while a sequence is designed to produce unique numbers, if it cycles, it can potentially generate the same number again after reaching the MAXVALUE. This statement would only be true if there was no cycling.
Which three are key components of an Entity Relationship Model?
Options:
a table
an attribute
a unique identifier
an activity
a relationship
an entity
Answer:
B, E, FExplanation:
Key components of an Entity-Relationship Model (ERM) include:
B. an attribute: Attributes are properties or characteristics of an entity, such as a person's name, date of birth, etc., and are essential in describing the data aspects of an entity in an ER model.
E. a relationship: Relationships describe how entities interact with each other within the database structure, such as a customer placing an order.
F. an entity: Entities are the key components of an ER model, representing objects or things within the domain that have a distinct existence, like 'Customer' or 'Order'.
Incorrect options:
A: A table is a database structure used to implement an entity in relational databases, not a component of the ER model itself.
C: While unique identifiers are crucial in database implementation (typically as primary keys), they are a specific attribute type, not a general component of ER models.
D: An activity is not a component of an ER model; activities relate more to process models or behavioral models in systems design.
Which two statements will return the names of the three employees with the lowest salaries?
Options:
SELECT last_ name, salary
FROM employees
FETCH FIRST 3 ROWS ONLY
ORDER BY salary;
SELECT last name, salary
FROM employees
ORDER BY salary
FETCE FIRST 3 RONS ONLY;
SELECT last_ name, salary
FBOM employees
WEERE
ORDER BY SELECT
ROINUM <= 3
salary FROM
employees);
SELECT last_ name, salary
FROM
(SELECT” FROM employees ORDER BY salary)
WHERE ROWNUM <=3
SELECT last_ name, salary
FROM employees
WHERE ROWNUM <=3
ORDER BY salary
Answer:
A, DExplanation:
A: This statement is correct. It orders the employees by salary and fetches the first 3 rows.
B: This statement has a typo with "FETCE" and "RONS" which should be "FETCH" and "ROWS". Hence, it will not execute successfully.
C: This statement will not execute successfully due to syntactical errors and incorrect use of the ORDER BY clause.
D: This statement is correct. It uses a subquery to order employees by salary and then limits the results to the first 3 using the ROWNUM pseudo-column.
E: This statement will not execute successfully because ROWNUM is evaluated before the ORDER BY clause, so it would return the first 3 rows based on the table's natural order, not the lowest salaries.
The behavior of ROWNUM and the FETCH FIRST syntax are explained in the Oracle Database SQL Language Reference 12c.
Which statements are true regarding primary and foreign key constraints and the effect they can have on table data?
Options:
A table can have only one primary key but multiple foreign keys.
It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.
Primary key and foreign key constraints can be defined at both the column and table level.
Only the primary key can be defined the column and table level.
It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted.
The foreign key columns and parent table primary key columns must have the same names.
A table can have only one primary key and one foreign key.
Answer:
A, C, EExplanation:
Regarding primary and foreign key constraints:
A. A table can have only one primary key but multiple foreign keys. This is true. A table is constrained to have only one primary key, which can consist of multiple columns, but can have several foreign keys referencing primary keys in other tables.
C. Primary key and foreign key constraints can be defined at both the column and table level. True. Constraints can be defined inline with the column definition or separately at the end of the table definition.
E. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted. This is also true if the foreign key is defined with the ON DELETE CASCADE option.
Options B, D, F, and G are incorrect:
B is incorrect because if a parent row is deleted, the child rows cannot remain without violating the integrity unless the foreign key is defined with ON DELETE SET NULL or similar behavior.
D is incorrect because both primary and foreign key constraints can be defined at both levels.
F is incorrect as the names of the foreign key columns do not need to match the primary key column names.
G is incorrect as a table can have multiple foreign keys.
Which three statements are true about single row functions?
Options:
They can be used only in the where clause of a select statement.
They can accept only one argument.
They return a single result row per table.
The argument can be a column name, variable, literal or an expression.
They can be nested to any level.
The date type returned can be different from the data type of the argument.
Answer:
D, E, FExplanation:
Single-row functions in SQL operate on each row independently and can modify the returned value:
Option A: Incorrect. Single row functions can be used in multiple parts of a SELECT statement, including SELECT, WHERE, and ORDER BY clauses.
Option B: Incorrect. Single row functions can accept more than one argument, such as the CONCAT function, which can accept multiple string arguments.
Option C: Incorrect. They return one result for each row processed, not per table.
Option D: Correct. Single row functions can take various types of arguments including column names, literals, variables, and other expressions.
Option E: Correct. Functions can be nested within other functions, allowing complex expressions and calculations.
Option F: Correct. The data type of the result can differ from the arguments’ data types, such as the SUBSTR function returning a VARCHAR2 even when used on a number after converting it to a string.
Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?
Options:
Only the primary key can be defined at the column and table level.
The foreign key columns and parent table primary key columns must have the same names.
It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.
A table can have only one primary key but multiple foreign keys.
Primary key and foreign key constraints can be defined at both the column and table level.
A table can have only one primary key and one foreign key.
It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted
Answer:
D, E, GExplanation:
Primary and foreign key constraints are foundational to relational database design:
Option A: Incorrect. Both primary and foreign key constraints can be defined at the column or table level.
Option B: Incorrect. Foreign key columns do not need to have the same names as the corresponding primary key columns in the parent table. They must be of the same data type and size.
Option C: Incorrect. If a foreign key constraint is enforced without ON DELETE CASCADE, deleting the parent row will either prevent the delete (due to constraint) or require a prior deletion or update of the child rows.
Option D: Correct. A table can indeed have only one primary key, which uniquely identifies each row, but it can have multiple foreign keys linking to primary keys of different tables.
Option E: Correct. Both types of keys can be defined at either level, providing flexibility in how constraints are applied and enforced.
Option F: Incorrect. A table can have multiple foreign keys as stated, each referencing a different parent table.
Option G: Correct. If the ON DELETE CASCADE option is set for a foreign key, then deleting a parent row will automatically delete the corresponding child rows, maintaining referential integrity.
Examine these statements executed in a single Oracle session:
CREATE TABLE product (pcode NUMBER(2),pname VARCHAR2(20));
INSERT INTO product VALUES(1,'pen');
INSERT INTO product VALUES (2,'pencil');
INSERT INTO product VALUES(3,'fountain pen');
SAVEPOINT a;
UPDATE product SET pcode=10 WHERE pcode =1;
COMMIT;
DELETE FROM product WHERE pcode =2;
SAVEPOINT b;
UPDATE product SET pcode=30 WHERE pcode =3;
SAVEPOINT c;
DELETE FROM product WHERE pcode =10;
ROLLBACK TO SAVEPOINT b;
COMMIT;
Which three statements are true？
Options:
The code for pen is 10.
There is no row containing fountain pen.
There is no row containing pencil.
The code for pen is 1.
The code for fountain pen is 3
There is no row containing pen
Answer:
A, C, FExplanation:
After creation and initial inserts, the pcode for 'pen' is updated to 10, and then committed.
The 'pencil' row is deleted and not yet committed.
A savepoint b is set after the deletion of the 'pencil' row.
The 'fountain pen' pcode is updated to 30, followed by setting savepoint c.
The 'pen' row (now with pcode 10) is deleted.
A rollback to savepoint b reverts the deletion of 'pen' and the update to 'fountain pen', but not the deletion of 'pencil', which was committed earlier due to the scope of the savepoint.
Therefore, after the final commit:
A: The code for 'pen' is 10, since the update was committed and the subsequent delete was rolled back.
C: There is no row containing 'pencil' because its deletion was committed.
F: There is a row containing 'pen' because the deletion was rolled back to savepoint b which was set after the deletion of 'pencil'.
You want to write a query that prompts for two column names and the WHERE condition each time It is executed in a session but only prompts for the table name the first time it is executed. The variables used in your
query are never undefined in your session . Which query can be used?
Options:
SELECT &col1, &col2
FROM &&table
WHERE &condition;
SELECT &col1, &col2
FROM “&table”
WHERE &condition;
SELECT &&col1,&&col2
FROM &table
WHERE &&condition= &&cond;
SELECT'&co11','&&co12'
FROM &table
WHERE'&&condition' ='&cond';
SELECT&&col1, &&col2
FROM &table
WHERE &&condition;
Answer:
AExplanation:
The scenario requires prompting for column names and WHERE condition each time the query is executed, but only prompting for the table name once. This behavior is achievable through the use of substitution variables in SQL*Plus or SQL Developer:
A.
SELECT &col1, &col2 FROM &&table WHERE &condition;
This query uses & for columns and condition, which will prompt every time the query is run. &&table will prompt for the table name the first time and reuse the same value in the same session without re-prompting.
Options B, C, D, and E use incorrect syntax or variable types, leading to various errors or undesired behaviors, such as not maintaining the value of the table name across executions or incorrect usage of single quotes and comparison in SQL.
Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
Options:
SELECT last _ NAME (monthly_ salary + monthly _commission _ pct) * 12 AS annual_ comp FROM employees;
select last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 *monthly_ commission_ pct) AS annual_ camp FROM employees
SELECT last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 * NVL (monthly_ commission _pct, 0)) AS annual _comp
SELECT last _ name, (monthly _ salary * 12) + (monthly_ commission _ pct * 12) AS FROM employees:
Answer:
CExplanation:
The correct way to compute the total annual compensation, which includes the monthly salary and the monthly commission (if any), is:
Option C: SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) AS annual_comp FROM employees;
This statement takes the monthly salary and multiplies it by 12 to get the annual salary, and then adds the annual commission which is the monthly salary multiplied by the commission percentage (if any, else 0) and then by 12.
Options A, B, and D are incorrect because:
Option A: Does not handle the case where the commission percentage is NULL which would result in NULL for the entire expression when added to the monthly salary.
Option B: Does not consider that the commission percentage might be NULL which could lead to incorrect calculations (or NULL values if commission is NULL).
Option D: Incorrectly adds the monthly commission percentage directly to the annual salary without considering that the percentage needs to be applied to the salary.
Examine the description of the CUSTOMERS table:
Which three statements will do an implicit conversion?
Options:
SELECT * FROM customers WHERE TO_ CHAR (customer_ id) = '0001';
SELECT * FROM customers WHERE customer id = '0001';
SELECT * FROM customers WHERE customer_ id = 0001;
SELECT FROM customers WHERE insert date = '01-JAN-19';
SELECT. FROM customers WHERE insert_ date = DATE *2019-01-01';
SELECT. FRON customers WE TO DATE (Insert _ date) = DATE ‘2019-01-01’;
Answer:
B, D, EExplanation:
A: No implicit conversion is done here because TO_CHAR is an explicit conversion from a numeric to a string data type.
B: Implicit conversion happens here because the '0001' string will be automatically converted to a numeric data type to match the CUSTOMER_ID field.
C: No implicit conversion is needed because 0001 is already a numeric literal.
D: Implicit conversion occurs here because the string '01-JAN-19' will be converted to a date type to compare with INSERT_DATE.
E: No implicit conversion; DATE is explicitly specified using the DATE keyword.
F: Incorrect syntax, it has typographical errors and also TO_DATE is an explicit function, not an implicit conversion.
References for SQL functions, conversions, and the CROSS JOIN behavior can be found in the Oracle Database SQL Language Reference 12c documentation.
Which two are true about rollbacks?
Options:
The ROLLBACK statement does not release locks resulting from table updates.
Data Control L anguage (DCL) statements, such as GRANT and REVOKE, can be rolled back.
A transaction interrupted by a system failure is automatically rolled back.
If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted .
Data consistency is not guaranteed after a rollback.
Answer:
C, DExplanation:
C. True. A transaction that is interrupted by a system failure is automatically rolled back by Oracle to ensure data consistency. This is part of the ACID (Atomicity, Consistency, Isolation, Durability) properties that Oracle adheres to.
D. True. A ROLLBACK without specifying a savepoint rolls back the entire transaction and removes all savepoints within that transaction. This restores the data to the state it was in at the beginning of the transaction.
Which two queries will result in an error?
Options:
SELECT FIRST_NAME LAST_NAME FROM EMPLOYEES;
SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES;
SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE ANNUAL_SALARY > 100000
ORDER BY 12 * SALARY ;
SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE 12 * SALARY > 100000
ORDER BY ANNUAL_SALARY;
SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE 12 * SALARY > 100000
ORDER BY 12 * SALARY;
SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE ANNUAL_SALARY > 100000
ORDER BY ANNUAL_SALARY;
Answer:
A, CExplanation:
In Oracle SQL, the following syntactical rules apply:
A. This query will result in an error because there is no comma separating the column names FIRST_NAME and LAST_NAME. The correct syntax should include a comma to separate the column names in the SELECT list.
B. This query is correctly formatted with a comma separating the column names, so it will not result in an error.
C. This query will result in an error because an alias defined in the SELECT list (ANNUAL_SALARY) cannot be used in the WHERE clause of the same query level. It must be repeated in the WHERE clause as 12 * SALARY.
D. This query will execute successfully because 12 * SALARY is directly used in the WHERE clause, and ANNUAL_SALARY is used in the ORDER BY clause, which is allowed.
E. This query is correct and will not result in an error. It uses 12 * SALARY in both the WHERE and ORDER BY clauses.
F. Similar to option C, this query will execute successfully because ANNUAL_SALARY is correctly used in the ORDER BY clause, and the WHERE clause does not attempt to reference the alias.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Database Object Names and Qualifiers"
Examine the description of the BOOKS table:
The table has 100 rows.
Examine this sequence of statements issued in a new session;
INSERT INTO BOOKS VALUES (‘ADV112’ , ‘Adventures of Tom Sawyer’, NULL, NULL);
SAVEPOINT a;
DELETE from books;
ROLLBACK TO SAVEPOINT a;
ROLLBACK;
Which two statements are true?
Options:
The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.
The second ROLLBACK command does nothing.
The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.
The second ROLLBACK command replays the delete.
The second ROLLBACK command undoes the insert.
Answer:
B, EExplanation:
B: True. The second ROLLBACK command would not do anything because the first ROLLBACK TO SAVEPOINT a; already undid the delete operation, and there was no other DML operation between the first and the second ROLLBACK.
E: True. The second ROLLBACK command undoes the insert because after the first ROLLBACK TO SAVEPOINT a; there are no savepoints defined, so a subsequent ROLLBACK would undo all transactions to the beginning of the current session or last COMMIT.
The ROLLBACK command is used to undo transactions that have not yet been committed. Rolling back to a savepoint only undoes transactions up to that savepoint, and a subsequent ROLLBACK without a savepoint name will undo all uncommitted changes since the last COMMIT.
References:Oracle SQL documentation on the ROLLBACK statement provides details on how it interacts with savepoints and the effects it has on the transactions within a session.
Examine the data in the EMP table:
You execute this query:
SELECT deptno AS "Department", AVG(sal) AS AverageSalary, MAX(sal) AS "Max Salary"
FROM emp
WHERE sal >= 12000
GROUP BY "Department "
ORDER BY AverageSalary;
Why does an error occur?
Options:
An alias name must not be used in an ORDER BY clause.
An allas name must not contain space characters.
An alias name must not be used in a GROUP BY clause.
An alias name must always be specified in quotes.
Answer:
CExplanation:
C. True. The error occurs because the alias "Department" used in the GROUP BY clause is enclosed in double quotes, which makes it case-sensitive. However, the column deptno is not originally created with double quotes in the table definition, so you cannot refer to it with a case-sensitive alias in the GROUP BY clause. Oracle interprets "Department" as a string literal, not a column alias, in the GROUP BY clause.
A is incorrect because you can use an alias in an ORDER BY clause. B is incorrect because an alias can contain space characters if it is quoted. D is incorrect because an alias does not always have to be specified in quotes, only when it includes special characters or spaces or if it is case-sensitive.
Which two tasks require subqueries?
Options:
Display the total number of products supplied by supplier 102 which have a product status of obsolete.
Display suppliers whose PROD_LIST_PRICE is less than 1000.
Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.
Display the minimum PROD_LIST_PRICE for each product status.
Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable.
Answer:
C, EExplanation:
C: True. To display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE, you would need to use a subquery to first calculate the average PROD_LIST_PRICE and then use that result to compare each product’s list price to the average.
E: True. Displaying products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products and whose status is orderable would require a subquery. The subquery would be used to determine the average PROD_LIST_PRICE, and then this average would be used in the outer query to filter the products accordingly.
Subqueries are necessary when the computation of a value relies on an aggregate or a result that must be obtained separately from the main query, and cannot be derived in a single level of query execution.
References:Oracle's SQL documentation provides guidelines for using subqueries in scenarios where an inner query's result is needed to complete the processing of an outer query.
Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?
Options:
SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;
SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;
SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;
SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;
Answer:
DExplanation:
The LISTAGG function is used in Oracle to aggregate strings from data in a group specified by the GROUP BY clause, producing a single row of concatenated values. The correct syntax also specifies an ORDER BY clause within the WITHIN GROUP parenthesis to sort the values in the concatenated list.
The correct query is:
SELECT deptno, LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;
This statement will return a comma-separated list of employee names (ename) in alphabetical order for each department (deptno) in the EMP table.
Examine this SQL statement
DELETE FROM employees e
WHERE EXISTS
(SELECT' dummy'
FROM emp history
WHERE employee_ id= e. employee id);
Which two are true?
Options:
The subquery is not a correlated subquery.
The subquery is executed before the DELETE statement is executed.
All existing rows in the EMPLOYEES table are deleted,
The DELETE statement executes successfully even if the subquery selects multiple rows.
The subquery is executed for every row in the EMPLOYEES table.
Answer:
B, DExplanation:
For the provided DELETE statement with an EXISTS clause:
Option B: The subquery is executed before the DELETE statement is executed.
Subqueries with EXISTS are typically executed before the outer DELETE statement to determine which rows of the outer query satisfy the condition.
Option D: The DELETE statement executes successfully even if the subquery selects multiple rows.
The EXISTS condition is used to check for the existence of rows returned by the subquery, regardless of how many rows there are. It returns TRUE if the subquery returns at least one row.
Options A, C, and E are incorrect because:
Option A: This statement is incorrect; the subquery is indeed a correlated subquery because it references the employee_id from the outer query (employees).
Option C: This is incorrect because not all existing rows in the EMPLOYEES table will be deleted, only those for which an associated record exists in the emp_history table.
Option E: While technically the subquery may be evaluated multiple times, it is only executed for those rows in EMPLOYEES that satisfy the condition of the EXISTS clause.
Which two are true about using constraints?
Options:
A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parenttable must have the same names.
A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.
A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.
PRIMARY KEY and FOREIGNY constraints can be specified at the column and at the table level
A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.
NOT NULL can be specified at the column and at the table level.
Answer:
D, EExplanation:
In Oracle Database 12c, it is important to understand the behavior and properties of constraints.
A. This statement is false. A FOREIGN KEY column in a child table does not need to have the same name as the referenced PRIMARY KEY column in the parent table. What is required is that the data type is the same and that the values in the FOREIGN KEY column correspond to values in the PRIMARY KEY column of the parent table.
B. This statement is false. A table cannot have multiple PRIMARY KEY constraints. By definition, a PRIMARY KEY is a unique identifier for a row in a table, and there can only be one such identifier.
C. This statement is false for the same reasons as B; a table can have only one PRIMARY KEY. However, it can have multiple FOREIGN KEY constraints that reference PRIMARY KEYS in other tables.
D. This is true. PRIMARY KEY and FOREIGN KEY constraints can indeed be specified at the column level with the column definition or at the table level with the ALTER TABLE statement.
E. This is true. A table can have only one PRIMARY KEY constraint because it defines a unique row identifier. However, it can have multiple FOREIGN KEY constraints referencing keys in other tables, allowing for complex relational structures.
F. This statement is false. NOT NULL constraints are always defined at the column level, as they apply to individual columns. They cannot be specified at the table level.
References:
Oracle Documentation on Constraints:
Oracle Documentation on NOT NULL Constraints:
Examine the description of the BOOKS_TRANSACTIONS table:
Which two WHERE conditions give the same result?
Options:
WHERE borrowed_date = SYSDATE AND (transaction_type ='RM' OR member_id IN ('A101','A102'));
WHERE borrowed_date = SYSDATE AND transaction_type ='RM' OR member_id IN ('A101','A102');
WHERE borrowed_date = SYSDATE AND (transaction_type ='RM' AND member_id='A101' OR member_id ='A102'));
WHERE (borrowed_date = SYSDATE AND transaction_type ='RM') OR member_id IN ('A101','A102');
WHERE borrowed_date = SYSDATE AND (transaction_type ='RM' AND (member_id ='A101' OR member_id ='A102') );
Answer:
A, EExplanation:
The WHERE clause in SQL filters the rows returned by the SELECT statement. The result of logical operators and conditions can change significantly depending on the use of parentheses.
Options A and E both use parentheses to ensure that borrowed_date = SYSDATE is evaluated with transaction_type ='RM' as one group and the member_id conditions as another group. The parentheses ensure that both conditions within each set of parentheses must be true for the rows to be included.
Option B is incorrect because it does not use parentheses to enforce the grouping of conditions, leading to potentially different results due to the way logical OR works.
Option C is incorrect because it has a syntax error; it is missing a parenthesis.
Option D is incorrect because it will return rows where either borrowed_date = SYSDATE AND transaction_type ='RM' is true or member_id IN ('A101','A102') is true, which is a broader condition than what's specified in options A and E.
Which three statements are true about an ORDER BY clause?
Options:
An ORDER BY clause always sorts NULL values last.
An ORDER BY clause can perform a binary sort
An ORDER BY clause can perform a linguistic sort
By default an ORDERBY clause sorts rows in ascending order
An ORDR BY clause will always precede a HAVI NG clause if both are used in the same top-level
Answer:
B, C, DExplanation:
In Oracle Database 12c, the behavior of the ORDER BY clause is guided by several rules:
Option B: An ORDER BY clause can perform a binary sort.
A binary sort is the default sorting mechanism in Oracle Database, which is based on the binary representation of the data.
Option C: An ORDER BY clause can perform a linguistic sort.
Oracle supports linguistic sorting through the use of the NLS_SORT parameter, which can be set to various linguistic and cultural norms.
Option D: By default, an ORDER BY clause sorts rows in ascending order.
If no ASC or DESC keyword is specified, Oracle will sort the results in ascending order by default.
Options A and E are incorrect:
Option A is not universally true; the position of NULL values in the sort order can be controlled by settings or specific SQL syntax (NULLS FIRST or NULLS LAST).
Option E is incorrect as the HAVING clause filters groups after data has been grouped by the GROUP BY clause and cannot be logically placed before ORDER BY in processing order.
Which two statements are true about the ORDER BY clause?
Options:
Numeric values are displayed in descending order if they have decimal positions.
Only columns that are specified in the SELECT list can be used in the ORDER BY clause.
In a character sort, the values are case-sensitive.
Column aliases can be used in the ORDER BY clause.
NULLS are not included in the sort operation.
Answer:
C, DExplanation:
C: True. In Oracle Database 12c, character sorts are case-sensitive by default, meaning that it differentiates between uppercase and lowercase letters when sorting data. This behavior aligns with the binary collation rules typically used, unless explicitly overridden by using different collation settings.
D: True. Column aliases can indeed be used in the ORDER BY clause in Oracle SQL. This allows for more readable and maintainable code, as you can define a column alias in the SELECT list and refer to that alias in the ORDER BY clause. This usage promotes clarity, especially when dealing with complex calculations or function applications in the select list.
In which three situations does a new transaction always start?
Options:
When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session
When issuing a CREATE INDEX statement after a CREATE TABLE statement completed unsuccessfully in the same session
When issuing a TRUNCATE statement after a SELECT statement was issued in the same session
When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session
When issuing the first Data Manipulation Language (OML) statement after a COMMIT or ROLLBACK statement was issued in the same session
When issuing a DML statement after a DML statement filed in the same session.
Answer:
A, C, EExplanation:
Substitution variables in Oracle are used to replace a value dynamically during the execution of SQL statements. The behavior of these variables is well-documented:
C. A substitution variable prefixed with & always prompts only once for a value in a session: This is true. In a session, when you use a single ampersand (&), SQL*Plus or SQL Developer will prompt for the value the first time the variable is encountered. The value for this variable will then be reused for the remainder of the session unless it is redefined.
D. A substitution variable can be used with any clause in a SELECT statement: Substitution variables can be placed in any part of a SQL statement, including the SELECT, WHERE, GROUP BY, ORDER BY, etc. They are not limited to any specific clause.
References:
Oracle SQL*Plus User's Guide and Reference, which discusses substitution variables.
Which statement will return the last sequence number generated by the EMP_ SEQ sequence?
Options:
SELECT NEXTVAL FROM emp_ seq;
SELECT CURRVAL FROM emp_ seq;
SELECT emp_ seq. CURRVAL FROM DUAL;
SELECT emp_ seq . NEXTVAL FROM DUAL;
Answer:
BExplanation:
A: NEXTVAL is used to increment the sequence and return the next value; it does not give the last number generated.
B: CURRVAL returns the current value of the sequence, which is the last value generated in the user's current session. However, CURRVAL cannot be queried unless NEXTVAL has been called at least once in that session.
C: CURRVAL is used correctly, but the syntax 'sequence.CURRVAL' is not correct in Oracle SQL.
D: NEXTVAL is used to generate the next sequence number, not to retrieve the last one generated.
Which three are true about scalar subquery expressions?
Options:
A scalar subquery expression that returns zero rows evaluates to zoro
They cannot be used in the values clause of an insert statement*
They can be nested.
A scalar subquery expression that returns zero rows evaluates to null.
They cannot be used in group by clauses.
They can be used as default values for columns in a create table statement.
Answer:
C, D, FExplanation:
Scalar subquery expressions are used in Oracle SQL to return a single value from a subquery.
Option C: They can be nested.
Scalar subqueries can indeed be nested within another scalar subquery, provided each subquery returns a single value.
Option D: A scalar subquery expression that returns zero rows evaluates to null.
According to Oracle documentation, if a scalar subquery returns no rows, the result is a NULL value, not zero or any other default.
Option F: They can be used as default values for columns in a create table statement.
Scalar subqueries can be specified in the DEFAULT clause of a column in a CREATE TABLE statement to dynamically assign default values based on the result of the subquery.
Options A, B, and E are incorrect based on Oracle SQL standards and functionalities.
Examine the description of the EMPLOYEES table:
NLS_DATE FORMAT is DD-MON-RR.
Which two queries will execute successfully?
Options:
SELECT dept_ id, AVG (MAX(salary)) FROM employees GROUP By dept_id HAVING hire_date> ' O1-JAN-19';
SELECT dept_ id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;
SELECT dept id, MAX (SUM(salary)) FROM employees GROUP BY dept_id;
SELECT dept_ iD, sum(salary) FROM employees WHERE hire_date > '01-JAN-9' GROUP BY dept_id;
SELECT AVG(MAX(salary)) FROM employees GROUP BY salary;
Answer:
DExplanation:
In Oracle SQL, aggregation functions such as AVG and MAX cannot be nested directly inside each other and must be used in conjunction with GROUP BY on the column(s) that are not included in the aggregate function. Also, the HAVING clause filters groups after aggregation is applied.
A. This query will not execute successfully because it improperly nests MAX inside AVG. Oracle SQL does not allow this type of nested aggregation without a subquery.
B. This query will not execute successfully for the same reason as A, it improperly nests MAX inside AVG.
C. Similar to A and B, this query improperly nests SUM inside MAX, which is not allowed without a subquery.
D. This query will execute successfully. It filters rows based on the HIRE_DATE using a correct date format (assuming '9' refers to '09' or '1999' due to the NLS_DATE_FORMAT being 'DD-MON-RR'), then groups the remaining rows by DEPT_ID and calculates the sum of SALARY for each department.
E. This query will not execute successfully because it improperly nests MAX inside AVG without a subquery, and it incorrectly attempts to GROUP BY SALARY, which is already being aggregated.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Aggregate Functions"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "GROUP BY Clause"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "HAVING Clause"
Which three statements are true regarding single row subqueries?
Options:
They must be placed on the left side of the comparison operator or condition.
They must return a row to prevent errors in the SQL statement.
A SQL statement may have multiple single row subquery blocks.
They can be used in the HAVING clause.
They must be placed on the right side of the comparison operator or condition.
They can be used in the clause.
Answer:
C, D, EExplanation:
C: True. A SQL statement may include multiple single row subqueries in different parts of the statement, such as in the SELECT list, WHERE clause, or HAVING clause. Each subquery must independently satisfy the requirement of returning a single row to avoid runtime errors.
D: True. Single row subqueries can be used in the HAVING clause. This allows for filtering groups based on conditions evaluated against individual or aggregated values returned by the subquery. The subquery must return a single value to be valid in this context.
E: True. Single row subqueries are often placed on the right side of the comparison operator in a SQL condition. This positioning is typical because the left side often references a column or an expression related to the main query, while the subquery on the right side dynamically provides a value for comparison.
Which two are true about global temporary tables?
Options:
They can be created only by a user with the DBA role,but can be accessed by all users who can create a session.
Backup and recovery operations are available for these tables.
If the ON COMMIT clause is session-specific,the table is dropped when the session is terminated.
Their data is always stored in the default temporary tablespace of the user who created them.
Indexes can be created on them.
If the ON COMMIT clause Is transaction-specific, all rows in the table are deleted alter each COMMIT or ROLLBACK.
Answer:
E, FExplanation:
E. True. Indexes can be created on global temporary tables just like they can on permanent tables. The indexes on global temporary tables are also temporary and only exist for the duration of the session or transaction, based on how the table was defined.
F. True. If the ON COMMIT clause is specified as DELETE ROWS, then all rows in the global temporary table are deleted after each COMMIT or ROLLBACK within the transaction. This clause defines the scope of the data's persistence within the global temporary table.
Which two queries execute successfully?
Options:
SELECT NULLIF(100, 100) FROM DUAL；
SELECT COALESCE(100, NULL, 200) FROM DUAL；
SELECT NULLIF(100, 'A') FROM DUAL；
SELECT NULLIF(NULL, 100) FROM DUAL；
SELECT CO ALESCE(100, 'A' ) FROM DUAL；
Answer:
A, B, D, EExplanation:
The NULLIF and COALESCE functions in Oracle SQL serve specific purposes:
Option A: Executes successfully. NULLIF returns NULL if the two arguments are equal; otherwise, it returns the first argument.
Option B: Also correct. COALESCE returns the first non-null value among its arguments.
Option C: This will not execute successfully because NULLIF requires both arguments to be of comparable data types. Comparing an integer to a character ('100' to 'A') is invalid.
Option D: Executes successfully. NULLIF returns NULL because it compares NULL to a number, which is valid (though always yields NULL).
Option E: Executes successfully. COALESCE accepts any data type as it returns the first non-null value, irrespective of type consistency among the arguments.
Examine this list of queries:
Which two statements are true?
Options:
1 and 4 give the same result.
2 returns the value 20.
2 and 3 give the same result.
3 returns an error.
1 and 4 give different results.
Answer:
A, BExamine the data in the CUST NAME column of the CUSTOMERS table:
CUST_NAME
------------------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two WHERE clauses give the required result?
Options:
WHERE INITCAP (SUBSTR(cust_name, INSTR(cust_name,'') +1)) IN ('MC%','Mc%)
WHERE UPPER (SUBSTR(cust_name, INSTR(cust_name, '') +1)) LIKE UPPER('MC%')
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,'') +1)) LIKE'Mc%'
WHERE SUBSTR(cust_name,INSTR(cust_name,'') +1) LIKE'Mc%' OR'MC%'
WHERE SUBSTR(cust_name, INSTR(cust_name,'') +1) LIKE'Mc%'
Answer:
B, CExplanation:
To find customers whose last names start with "Mc" or "MC", we need to ensure our SQL query correctly identifies and compares these prefixes regardless of case variations. Let's analyze the given options:
Option B: WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE UPPER('MC%')This clause uses UPPER to convert both the extracted substring (starting just after the first space, assuming it indicates the start of the last name) and the comparison string 'MC%' to uppercase. This ensures case-insensitive comparison. The LIKE operator is used to match any last names starting with "MC", which will correctly capture both "Mc" and "MC". This option is correct.
Option C: WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'Mc%'This clause applies INITCAP to the substring, which capitalizes the first letter of each word and makes other letters lowercase. The result is compared to 'Mc%', assuming only the last name follows the space. This approach will match last names starting with "Mc" (like "McEwen"), but not "MC". However, considering we're looking for "Mc" specifically, this clause works under the assumption that "Mc" is treated as proper capitalization for these last names. Thus, it can also be considered correct, albeit less inclusive than option B.
The other options either use incorrect syntax or apply case-sensitive matches without ensuring that both "Mc" and "MC" are captured:
Option A: Contains syntax errors (unmatched quotes and wrong use of IN).
Option D: Uses case-sensitive match without combining both "Mc" and "MC".
Option E: Only matches "Mc", which is too specific.
Examine the command to create the BOOKS table.
SQL> create table books(book id CHAR(6) PRIMARY KEY,
title VARCHAR2(100) NOT NULL,
publisher_id VARCHAR2(4)，
author_id VARCHAR2 (50));
The BOOK ID value 101 does not exist in the table.
Examine the SQL statement.
insert into books (book id title, author_id values
(‘101’，’LEARNING SQL’,’Tim Jones’)
Options:
It executes successfully and the row is inserted with a null PLBLISHER_ID.
It executes successfully only if NULL is explicitly specified in the INSERT statement.
It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the INSERT statement.
It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and NULL is explicitly specified In the INSERT statement.
Answer:
AExplanation:
A. It executes successfully and the row is inserted with a null PUBLISHER_ID: The SQL statement does not specify the publisher_id, and since there is no NOT NULL constraint on this column, Oracle will insert the row with a NULL value for publisher_id. The statement is syntactically correct, assuming the column names and values are properly specified and formatted.
In the PROMOTIONS table, the PROMO_ BEGIN_DATE column is of data type and the default date format is DD-MON-RR
Which two statements are true about expressions using PROMO_ BEGIN_DATE in a query?
Options:
TONUMBER (PROMO BEGIN_DATE) - 5 will return a number
PROMO_ BEGIN_DATE - 5 will return a date
PROMO_ BEGIN_DATE - SYSDATE will return a number
PROMO_ BEGIN_DATE - SYSDATE will return an error
TODATE(PROMO BEGIN_DATE *5) will return a date
Answer:
B, CExplanation:
In Oracle SQL, when you subtract a number from a date, the result is a date. When you subtract one date from another, the result is the number of days between the two dates.
B: PROMO_BEGIN_DATE - 5 will subtract 5 days from the PROMO_BEGIN_DATE, resulting in a new date that is 5 days earlier than PROMO_BEGIN_DATE.
C: PROMO_BEGIN_DATE - SYSDATE will return the number of days between the PROMO_BEGIN_DATE and the current date (SYSDATE).
The incorrect options are:
A: TONUMBER(PROMO_BEGIN_DATE) - 5 will not return a number because PROMO_BEGIN_DATE is a date, and TONUMBER is not a valid function to convert dates to numbers in Oracle.
D: PROMO_BEGIN_DATE - SYSDATE will not return an error; it will return the number of days between the two dates as explained above.
E: TODATE(PROMO_BEGIN_DATE * 5) will not return a date because PROMO_BEGIN_DATE * 5 is not a valid operation in Oracle SQL as you cannot multiply a date by a number, and TODATE is not a valid function. The correct function name is TO_DATE.
References:
Oracle Documentation on Date Arithmetic: Database SQL Language Reference - Datetime Functions
Examine this statement,which executes successfully:
In which order are the rows displayed?
Options:
sorted by DEPARTMENT_NAME
sorted by DEPARTMENT_NAME and AVGSAL
sorted by DEPARTMENT_NAME and MAXSAL
sorted by AVGSAL
Sorted by MAXSAL
Answer:
BExplanation:
Since the statement seems to imply that a sort operation involving department names and an average salary (AVGSAL) was executed, the best logical inference, given typical SQL query behavior and assuming a typical structure, is:
B. sorted by DEPARTMENT_NAME and AVGSAL: Generally, when SQL queries involve grouping and aggregation, the ORDER BY clause (if explicitly mentioned or implied in your statement details) would sort by the grouping column first (DEPARTMENT_NAME) and then by any aggregated column such as average salary (AVGSAL). This order ensures that within each department, the rows are sorted according to the average salary.
Examine this data in the EMPLOYERS table：
Which statement will execute successfully?
Options:
SELECT dept_id, MAX (Last_name), SUM (salary) FROM employees GROUP BY dept_id
SELECT dept_id, LENGTH (last_name), SUM (salary) FROM employees GROUP BY dept_id
SELECT dept_id, STDDEV (last_name), SUM (salary) FROM employees GROUP BY dept_id
SELECT dept_id, INSTR (last_name,'A'), SUM (salary) FROM employees GROUP BY dept_id
Answer:
AExplanation:
In SQL, the GROUP BY clause is used in conjunction with aggregate functions to group the result-set by one or more columns.
A. This statement will execute successfully. MAX() is an aggregate function that can be used to return the highest value of the selected column, and SUM() is an aggregate function used to sum the values. Both are valid in the SELECT statement with a GROUP BY clause grouping the results by dept_id.
B. This statement will not execute successfully when used with GROUP BY because LENGTH(last_name) is not an aggregate function and it doesn't appear in the GROUP BY clause. All selected columns that are not under an aggregate function must be included in the GROUP BY clause.
C. This statement will not execute successfully because STDDEV() is an aggregate function that calculates the standard deviation of a set of numbers, and it can only be used on numeric data types. last_name is not a numeric column.
D. This statement will not execute successfully for the same reason as B: INSTR(last_name, 'A') is not an aggregate function and must appear in the GROUP BY clause if it's in the SELECT clause.
Examine the description of the PRODUCTS table:
Which two statements execute without errors?
Options:
MERGE INTO new_prices n
USING (SELECT * FROM products) p
WHEN MATCHED THEN
UPDATE SET n.price= p.cost* 01
WHEN NOT MATCHED THEN
INSERT(n.prod_id, n.price) VALUES(p.prod_id, cost*.01)
WHERE(p.cost<200);
MERGE INTO new_prices n
USING (SELECT * FROM products WHERE cost>150) p
ON (n.prod_id= p.prod_id)
WHEN MATCHED THEN
UPDATE SET n.price= p.cost*.01
DELETE WHERE (p.cost<200);
MERGE INTO new_prices n
USING products p
ON (p.prod_id =n.prod_id)
WHEN NOT MATCHED THEN
INSERT (n.prod _id, n.price) VALUES (p.prod_id, cost*.01)
WHERE (p.cost<200);
MERGE INTO new_prices n
USING (SELECT * FROM products WHERE cost>150) p
ON (n.prod_id= p.prod_id)
WHEN MATCHED THEN
DELETE WHERE (p.cost<200)
Answer:
BExplanation:
B: True. This MERGE statement should execute without errors. It uses a conditionally filtered selection from the products table as a source to update or delete rows in the new_prices table based on whether the prod_id matches and the cost is greater than 150. The delete operation within a MERGE statement is allowed in Oracle when a WHEN MATCHED clause is specified.
The MERGE statement is correctly structured with a USING clause that includes a subquery with a valid WHERE condition, an ON condition that specifies how to match rows between the source and the target, and a WHEN MATCHED THEN clause that specifies the update and delete operations based on the cost condition.
References:Oracle SQL documentation specifies that within a MERGE statement, you can specify a WHEN MATCHED clause to update and/or delete rows in the target table based on the condition specified after the DELETE keyword.
Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database?
Options:
The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row
A TIMESTAMP data type column contains information about year, month, and day
The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
The CURRENT_TIMESTAMP function returns data without time zone information
Answer:
A, B, DExplanation:
A: True. The DBTIMEZONE function returns the database's time zone offset from UTC (Coordinated Universal Time). In Oracle 12c, DBTIMEZONE can return the time zone of the database in terms of a region name or as a numeric offset from UTC. This is stated in the Oracle documentation for managing time zones.
B: True. TIMESTAMP WITH LOCAL TIME ZONE is a data type that adjusts the data stored in the database to the time zone of the session that is querying or inserting the data. When data is stored, Oracle converts it from the session time zone to UTC, and upon retrieval, it converts it back to the session time zone. This is a feature designed to allow the same data to be viewed in different time zones automatically, making it highly useful in global applications.
D: True. SESSIONTIMEZONE function returns the time zone offset of the current session from UTC. This is useful for understanding and managing data conversions in applications that are used across different time zones. The time zone can be displayed as an offset from UTC or as a named region depending on the environment settings.
Examine the description of the PROMOTIONS TABLE:
You want to display the unique is promotion costs in each promotion category.
Which two queries can be used?
Options:
SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
SELECT promo_cost, promo_category FROM promotions ORDER BY 1
SELECT promo_category, DISTINCT promo_cost FROM promotiong ORDER BY 2:
select DISTINCT promo_categoryIl ‘has’||promol_cost as COSTS FROM promotions ORDER BY 1:
SELECT DISTINCT promo_cost ||’in’IIDISTINCT promo_category promotions ORDER BY1:
Answer:
A, DExplanation:
For displaying unique promotion costs in each promotion category from the PROMOTIONS table:
A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1: This query correctly retrieves unique combinations of promo_category and promo_cost, sorting the results based on the promo_category.
D. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1: This query correctly combines the category and cost into a single string for each unique combination, using string concatenation and the DISTINCT keyword to ensure uniqueness, sorting by the concatenated string.
Incorrect options:
B: This does not ensure that the combinations of promo_cost and promo_category are unique because DISTINCT was not specified for both columns together.
C: The syntax is incorrect; DISTINCT cannot be applied to a single column partway through a SELECT clause.
E: This is syntactically incorrect as DISTINCT cannot be used twice in the way shown; it must apply to all columns if used at the beginning of a SELECT statement.
Which two statements are true about transactions in the Oracle Database server?
Options:
An uncommitted transaction commits automatically if the user exits SQL*Plus
Data Manipulation Language (DML) statements always start a new transaction.
A user can always see uncommitted updates made by the same user in a different session.
A Data Definition Language (DDL) statement does a commit automatically only for the data dictionary updates caused by the DDL
A session can always see uncommitted updates made by itself.
If a session has an uncommitted transaction, then a DDL statement issue a COMMIT before starting a new transaction.
Answer:
B, EExplanation:
A. Incorrect. An uncommitted transaction is not automatically committed if the user exits SQL*Plus. It is rolled back unless otherwise specified. B. Correct. A DML statement such as INSERT, UPDATE, or DELETE will implicitly start a new transaction if there is no current transaction running in the session. C. Incorrect. A user cannot see uncommitted updates made by another session; a session can only see its own uncommitted changes. D. Incorrect. A Data Definition Language (DDL) statement automatically commits any outstanding transactions in the session, not just the changes to the data dictionary. E. Correct. A session can always see its own uncommitted updates. This is because Oracle uses a multiversion consistency model, allowing each user to see a consistent view of the data including their own changes. F. Incorrect but close. If a session has an uncommitted transaction, then a DDL statement does issue a COMMIT before executing and starting a new implicit transaction, not just for the data dictionary updates but for all pending changes in the session.
This information can be verified in the Oracle Database SQL Language Reference and Oracle Database Concepts documentation, which discuss transaction management and the behavior of DML and DDL statements within transactions.
Examine this partial statement:
SELECT ename, sal,comm FROM emp
Now examine this output:
WHICH ORDER BY clause will generate the displayed output?
Options:
ORDER BY NVL(enam,0) DESC, ename
ORDER BY NVL(comm,0) ASC NULLS FIRST, ename
ORDER BY NVL(comm,0) ASC NULLS LAST, ename
ORDER BY comm DESC NULLS LAST, ename
Answer:
BExplanation:
The ORDER BY clause is used in a SELECT statement to sort the returned rows by one or more columns.
A. This clause would attempt to order by ename as if it were a numeric column, which is not correct since ename is not numeric.
B. This is the correct answer. The NVL function replaces nulls with the specified value, in this case, 0. This clause sorts by comm, with nulls considered as 0 and appearing first, followed by the actual values, and then sorts by ename.
C. This clause is incorrect because it would place null values at the end, but in the output, rows with null comm appear at the beginning.
D. This clause would sort the comm in descending order with the nulls at the end, which is not consistent with the output shown.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ORDER BY Clause"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "NVL Function"
Please note that the correct formatting of SQL statements and clauses is crucial for the successful execution of queries.
Examine the data in the COLORS table:
Examine the data in the BRICKS table:
Which two queries return all the rows from COLORS?
Options:
Answer:
A, DExplanation:
In SQL, to return all rows from one table in a join regardless of whether they match rows in the other table, a LEFT JOIN is used when we want all rows from the left table, or a RIGHT JOIN when we want all rows from the right table. Here’s an explanation for each query provided in the context of the COLORS and BRICKS tables:
A. This query uses a LEFT JOIN and will return all the rows from the COLORS table (c) because it is on the left side of the join. Additionally, it joins the BRICKS table (b) on the condition that the color_rgb_hex_value matches. The condition in the WHERE clause (b.brick_id > 0) will not exclude any COLORS rows because it only filters the rows from the right table (BRICKS) after the join.
B. This query uses a RIGHT JOIN, and while it does return all rows from the COLORS table, it doesn't place it on the left side of the join, which is not what is typically expected from a RIGHT JOIN to return all rows from the right side table.
C. This query is a plain JOIN (also known as an inner join), which only returns rows that have matching values in both tables, so it will not return all the rows from the COLORS table if there is no match in the BRICKS table.
D. This query uses a FULL JOIN, which returns all rows when there is a match in one of the tables. Hence, it will return all the rows from both the COLORS and BRICKS tables. While this does return all rows from the COLORS table, it also includes all rows from the BRICKS table, which may not be desired if the requirement was only to return all rows from COLORS.
E. This query is similar to option A and uses the USING clause to specify the join condition. The USING clause is used when both tables have a column with the same name and is meant to simplify the syntax. However, in this case, the columns do not have the same name in both tables (RGB_HEX_VALUE in COLORS and COLOR_RGB_HEX_VALUE in BRICKS), so this query will not execute successfully.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Joins"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "LEFT OUTER JOIN"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "RIGHT OUTER JOIN"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "FULL OUTER JOIN"
Based on the explanations above, the correct answers that return all rows from the COLORS table are A and D. However, D includes all rows from BRICKS as well, which may not be the intended requirement if the question specifies only rows from COLORS should be returned. Thus, A is the most accurate answer to the question as it adheres to the standard expectation of a LEFT JOIN.
which is true about the round,truncate and mod functions>?
Options:
ROUND(MOD(25,3),-1) IS INVALID
ROUND(MOD(25,3),-1) AND TRUNC(MOD(25,3),-1) ARE BOTH VALID AND GIVE THE SAME RESULT.
ROUND(MOD(25,3),-1) AND TRUNC(MOD(25,3),-1) ARE BOTH VALID AND GIVE THE DIFFERENT RESULTS.
TRUNC(MOD(25,3),-1) IS INVALID.
Answer:
CExplanation:
Both ROUND and TRUNC functions can be applied to numbers, and MOD is a function that returns the remainder of a division. The ROUND function rounds a number to a specified number of decimal places, which can be positive, zero, or negative. The TRUNC function truncates a number to a specified number of decimal places.
ROUND(MOD(25,3),-1) rounds the result of MOD(25,3), which is 1, to tens place, which results in 0. TRUNC(MOD(25,3),-1) truncates the result of MOD(25,3), which is 1, to tens place, which also results in 0.
Both are valid, but in this specific case, they give the same result because the remainder (1) when rounded or truncated to tens place (-1) will be 0.
A session's NLS_DATE_FORMAT is set to DD Mon YYYY .
Which two queries return the value 1 Jan 2019?
Options:
SELECT to_date(' 2019-01-01 ', 'YYYY -MM-DD' ) FROM DUAL;
SELECT DATE '2019-01-01' FROM DUAL ;
SELECT TO_CHAR('2019-01-01') FROM DUAL; 2019-01-01
SELECT '2019-01-01' FROM DUAL ; 2019-01-01
SELECT TO_ DATE('2019-01-01') FROM DUAL;
Answer:
BExplanation:
When the NLS_DATE_FORMAT is set to DD Mon YYYY, the database expects the date string to be in the format of day, abbreviated month name, and full year.
B. The query SELECT DATE '2019-01-01' FROM DUAL; correctly returns the value 1 Jan 2019 because the ANSI date literal DATE 'YYYY-MM-DD' is independent of the NLS_DATE_FORMAT parameter.
Option A is incorrect because the TO_DATE function requires the format model to match the string literal, which it does not in this case.
Option C is incorrect because TO_CHAR is used to convert a date to a string, not a string to a date.
Option D is incorrect because without specifying that the string is a date, the result is just a string and not a date value.
Option E is incorrect because TO_DATE without a format model relies on the NLS_DATE_FORMAT to interpret the string, and YYYY-MM-DD does not match DD Mon YYYY.
Which two are true about granting privilege on objects?
Options:
An object privilege can be granted to a role only by the owner of that object
An object privilege can be granted to other users only by the owner of that object
The owner of an object acquires all object privilege on that object by default
A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table
The WITH GRANT OPTION clause can be used only by DBA users
Answer:
C, DExplanation:
C. True, the owner of an object automatically has all object privileges for that object by default. This is an inherent property of the object owner in Oracle.D. True, to allow the creation of foreign key constraints that reference a particular table, the owner of the table must grant the REFERENCES privilege on the key columns to other users or roles.
A, B, and E are not correct because: A. Object privileges can be granted to a role by any user with the appropriate permissions, not only by the owner. B. Object privileges can also be granted by users who have been given the privileges with the WITH GRANT OPTION, not just by the owner. E. The WITH GRANT OPTION is not limited to DBA users; it can be used by any user who has the privilege to grant a specific object privilege.
References:
Oracle documentation on object privileges: Oracle Database SQL Language Reference
Oracle documentation on the REFERENCES privilege: Oracle Database Security Guide
Examine the description of the ENPLYEES table:
Which two queries return all rows for employees whose salary is greater than the average salary in their department?
Options:
SELECT ”
FROM employees
WHERE salary > ANY
SELECT AVG (salary)
EROM employees
GROUP BY department_ id);
SELECT
FROM employees
WHERE salary > AVG (salary) OVER (PARTITION BY department _ id);
SELECT”
FROM employees e1
WHERE salary >!
SELECT AVG (salary)
FROM employees e2
WHERE e1. Department _id = e2, department_ id
SELECT.
FROM
SELECT e.", AVG (salary) OVER (PARTITION BY department id) avg_ sal
FROM employees e
WHERE salary > avg_ sal;
SELECT”
FROM employees
WHERE salary >
( SELECT AVG
(salary) FROM
employees
GROUP BY department _ id
Answer:
B, CExplanation:
To return all rows for employees whose salary is greater than the average salary in their department, you would use either a subquery or an analytic function:
Option B:
SELECT ... FROM employees WHERE salary > AVG(salary) OVER (PARTITION BY department_id);
This uses the window function AVG with PARTITION BY to calculate the average salary per department, and it compares each employee’s salary to this average.
Option C:
SELECT ... FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);
This correlated subquery compares each employee's salary to the average salary in their department using a subquery to calculate the average salary for that department.
Options A, D, and E are incorrect because:
Option A: The use of ANY with the subquery does not ensure comparison with the average salary of their respective department.
Option D: This is syntactically incorrect; the subquery alias avg_sal is not accessible outside the subquery.
Option E: The subquery does not correlate with the outer query to ensure that each employee's salary is compared to the average salary of their respective department.
Which two statements are true about date/time functions in a session where NLS_DATE_PORMAT is set to DD-MON-YYYY SH24:MI:SS
Options:
SYSDATE can be used in expressions only if the default date format is DD-MON-RR.
CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.
CURRENT_DATE returns the current date and time as per the session time zone
SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server.
CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of functional seconds.
SYSDATE can be queried only from the DUAL table.
Answer:
C, DExplanation:
In Oracle Database 12c SQL, regarding date/time functions and considering a session where NLS_DATE_FORMAT is set to DD-MON-YYYY SH24:MI:SS:
C. CURRENT_DATE returns the current date and time as per the session time zone. This is correct as CURRENT_DATE returns the current date and time in the time zone of the current SQL session, as set by the ALTER SESSION command.
D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. This is partially correct. SYSDATE returns the current date and time from the operating system of the database server. However, CURRENT_DATE returns the date and time set for the client's operating system environment, adjusted to the session time zone.
Options A, B, E, and F are incorrect based on Oracle's documentation:
A is incorrect because SYSDATE is independent of the NLS_DATE_FORMAT setting.
B is incorrect because CURRENT_TIMESTAMP includes time zone information, which can differ from CURRENT_DATE.
E is incorrect because CURRENT_TIMESTAMP differs from SYSDATE by including fractional seconds and time zone.
F is incorrect as SYSDATE can be queried in any SELECT statement, not just from DUAL.
Which two statements are true about the WHERE and HAVING clauses in a SELECT statement?
Options:
The WHERE clause can be used to exclude rows after dividing them into groups
WHERE and HAVING clauses can be used in the same statement only if applied to different table columns.
The HAVING clause can be used with aggregating functions in subqueries.
Aggregating functions and columns used in HAVING clauses must be specified in these SELECT list of a query.
The WHERE clause can be used to exclude rows before dividing them into groups.
Answer:
D, EExplanation:
In SQL, the WHERE and HAVING clauses are used to filter records; the WHERE clause is applied before grouping the records, while the HAVING clause is used after grouping the records, particularly when using aggregation functions.
Statement D is true because the HAVING clause is used to filter groups based on the result of aggregate functions. Therefore, any column or aggregate function appearing in the HAVING clause must also appear in the SELECT list of the query, unless it is used as part of an aggregate function.
Statement E is true because the WHERE clause is designed to filter rows before they are grouped into aggregate groups in a GROUP BY clause. This is a fundamental aspect of SQL that optimizes query performance by reducing the number of rows to be processed in the aggregate phase.
Statements A, B, and C are incorrect based on the following:
A is incorrect because the WHERE clause does not operate on groups but on individual rows before grouping.
B is misleading; while WHERE and HAVING can be used in the same statement, their usage is not restricted to different columns. They perform different functions (row-level filtering vs. group-level filtering).
C is incorrect because subqueries using aggregate functions typically do not use HAVING clauses; rather, HAVING is used in the outer query to filter the results of aggregates.
Examine this SQL statement:
DELETE FROM employees e
WHERE EXISTS
(SELECT'dummy'
FROM emp_history
WHERE employee_id = e.employee_id)
Which two are true?
Options:
The subquery is executed for every row in the EMPLOYEES table.
The subquery is not a correlated subquery.
The subquery is executed before the DELETE statement is executed.
All existing rows in the EMPLOYEE table are deleted.
The DELETE statement executes successfully even if the subquery selects multiple rows.
Answer:
A, EExplanation:
The provided DELETE statement uses a correlated subquery to determine which rows should be deleted from the EMPLOYEES table.
A. The subquery is indeed executed for every row in the EMPLOYEES table. This is because it references e.employee_id, which is a column from the outer query, making it a correlated subquery.
B. The subquery is a correlated subquery, as explained above.
C. The subquery is executed for each row, not before the DELETE statement.
D. Not all existing rows in the EMPLOYEES table are deleted, only those that have a corresponding employee_id in the EMP_HISTORY table.
E. The DELETE statement executes successfully even if the subquery selects multiple rows because the EXISTS condition only checks for the presence of rows, not their count.
References:
Oracle Database SQL Language Reference 12c Release 1 (12.1), DELETE Statement
Oracle Database SQL Language Reference 12c Release 1 (12.1), Subquery Factoring
Oracle Database SQL Language Reference 12c Release 1 (12.1), Correlated Subqueries
Which statements is true about using functions in WHERE and HAVING?
Options:
using single-row functions in the WHERE clause requires a subquery
using single-row functions in the HAVING clause requires a subquery
using aggregate functions in the WHERE clause requires a subquery
using aggregate functions in the HAVING clause requires a subquery
Answer:
CExplanation:
Single-row functions can be used in the WHERE and HAVING clauses without requiring a subquery. However, aggregate functions, which operate on many rows to give one result per group, cannot be used in a WHERE clause unless a subquery is used because the WHERE clause is processed before the individual rows are aggregated into groups.
A. False. Single-row functions can be directly used in the WHERE clause.
B. False. Single-row functions can be directly used in the HAVING clause.
C. True. Aggregate functions cannot be used in the WHERE clause without a subquery because the WHERE clause filters individual rows before they are aggregated.
D. False. Aggregate functions are intended to be used in the HAVING clause, which is specifically for filtering groups of rows after they have been aggregated, and they do not require a subquery to be used there.
Choose the best answer.
Examine the description of the EMPLOYEES table:
Which query is valid?
Options:
SELECT dept_id, join_date,SUM(salary) FROM employees GROUP BY dept_id, join_date;
SELECT depe_id,join_date,SUM(salary) FROM employees GROUP BY dept_id:
SELECT dept_id,MAX(AVG(salary)) FROM employees GROUP BY dept_id;
SELECT dept_id,AVG(MAX(salary)) FROM employees GROUP BY dapt_id;
Answer:
AExplanation:
In Oracle 12c SQL, the GROUP BY clause is used to arrange identical data into groups with the GROUP BY expression followed by the SELECT statement. The SUM() function is then used to calculate the sum for each grouped record on a specific column, which in this case is the salary column.
Option A is valid because it correctly applies the GROUP BY clause. Both dept_id and join_date are included in the SELECT statement, which is a requirement when using these columns in conjunction with the GROUP BY clause. This means that the query will calculate the sum of salaries for each combination of dept_id and join_date. It adheres to the SQL rule that every item in the SELECT list must be either an aggregate function or appear in the GROUP BY clause.
Option B is invalid due to a typo in SELECT depe_id and also because it ends with a colon rather than a semicolon.
Option C is invalid because you cannot nest aggregate functions like MAX(AVG(salary)) without a subquery.
Option D is invalid for the same reason as option C, where it tries to nest aggregate functions AVG(MAX(salary)), which is not allowed directly in SQL without a subquery.
For further reference, you can consult the Oracle 12c documentation, which provides comprehensive guidelines on how to use the GROUP BY clause and aggregate functions like SUM():
Oracle Database SQL Language Reference, 12c Release 1 (12.1): GROUP BY Clause
Oracle Database SQL Language Reference, 12c Release 1 (12.1): Aggregate Functions
Examine the description of the PRODCTS table which contains data:
Which two are true?
Options:
The PROD ID column can be renamed.
The PROD_ ID column data type can be changed to VARCHAR2 (2).
The EXPIRY DATE column data type can be changed to TIME STAMP.
The EXPIRY DATE column cannot be dropped.
The PROD NAME column cannot have a DEFAULT clause added to it.
Answer:
A, CExplanation:
A: True, the name of a column can be changed in Oracle using the ALTER TABLE ... RENAME COLUMN command.
B: False, you cannot change a column's data type from NUMBER to VARCHAR2 if the table contains data, unless the change does not result in data loss or inconsistency.
C: True, it is possible to change a DATE data type column to TIMESTAMP because TIMESTAMP is an extension of DATE that includes fractional seconds. This operation is allowed if there is no data loss.
D: False, any column that is not part of a primary key or does not have a non-deferrable constraint can generally be dropped unless it contains data that does not allow for such a change.
E: False, the DEFAULT clause can be added to a column provided there is no data that contradicts the default value or it doesn't violate any constraints.
These statements are verified against the Oracle Database 12c SQL documentation, specifically the sections on data types, the ALTER TABLE command, and the use of literals in SQL expressions.
