Exact2Pass
Which two statements are true about Oracle synonyms?
A synonym can have a synonym.
A synonym has an object number.
Any user can create a public synonym.
All private synonym names must be unique in the database.
A synonym can be created on an object in a package.
Oracle synonyms are used to simplify the referencing of complex schema objects:
Option A: Incorrect. A synonym cannot have another synonym; it directly references the base object.
Option B: Incorrect. A synonym does not have an object number as it is merely an alias for another object.
Option C: Correct. Any user with sufficient privileges can create a public synonym, which is accessible to all users in the database.
Option D: Incorrect. All private synonym names must be unique within a schema but not across the entire database.
Option E: Correct. Synonyms can be created for objects within packages, such as procedures or functions, simplifying the referencing of these objects without needing to specify the full package name.
Which three statements are true?
The COMMISSION column can contain negative values .
The MANAGER column is a foreign key referencing the EMPNO column.
The SALARY column must have a value .
An index is created automatically in the MANAGER column.
The DEPTNO column in the EMP table can contain the value 1.
The DEPTNO column in the EMP table can contain NULLS .
The DNAME column has a unique constraint.
A. The constraint commission < salary implies that COMMISSION must be less than SALARY, so it can have a negative value if the salary is greater than zero, making this statement true.
B. The constraint named CR_MGR is listed as a Referential (R) constraint on the MANAGER column, which implies it is a foreign key that would reference the EMPNO column, making this statement true.
C. The SALARY column is part of a Check (C) constraint CC_COMM that requires the SALARY to be greater than 100, which means it must have a value and cannot be NULL, making this statement true.
D. The presence of a foreign key constraint does not automatically create an index on the foreign key column. While Oracle may implicitly create indexes for primary keys, it does not do so for foreign keys.
E. The constraint deptno > 9 prevents DEPTNO from containing values less than or equal to 9, so it cannot contain the value 1, making this statement false.
F. There is no NOT NULL constraint listed on the DEPTNO column in the constraints image, so it can contain NULL values, making this statement false.
G. There is no indication of a unique constraint on the DNAME column in the constraints image; the constraint DNAME IS NOT NULL only implies that the column cannot contain NULL values, making this statement false.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Constraints"
Oracle Database Concepts, 12c Release 1 (12.1): "Indexes and Index-Organized Tables"
Examine this query:
SELECT SUBSTR (SYSDATE,1,5) ‘Result’ FROM DUAL
Which statement is true?
It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)
It fails unless the expression is modified to SUBSTR (TO_ CHAR(SYSDATE),1,5)
It fails unless the expression is modified to SUBSTR (TO_ CHAR(TRUNC(SYSDATE)),1,5)
It executes successfully with an implicit data type conversion
In Oracle Database 12c, the SYSDATE function returns a date value in the current session's date format. The SUBSTR function is used to extract a substring from a string. However, SYSDATE is a DATE data type and not a string, so you cannot directly use SUBSTR on it. Therefore, SYSDATE must first be converted to a character data type using the TO_CHAR function before applying the SUBSTR function.
Therefore, the correct form of the query should be:
SELECT SUBSTR(TO_CHAR(SYSDATE), 1, 5) AS "Result" FROM DUAL;
This converts the current date and time to a string using the default date format and then extracts the first 5 characters from it.
Which two statements are true about an Oracle database?
A table can have multiple primary keys.
A table can have multiple foreign keys.
A NUMBER column without data has a zero value.
A column definition can specify multiple data types.
A VARCHAR2 column without data has a NULL value.
A: This statement is false. A table can only have one primary key, although the primary key can consist of multiple columns (composite key).
B: This statement is true. A table can have multiple foreign keys referencing the primary keys of other tables or the same table.
C: This statement is false. A NUMBER column without data is NULL, not zero.
D: This statement is false. A column definition must specify exactly one data type.
E: This statement is true. A VARCHAR2 column without data defaults to NULL, not an empty string.
Examine the description of the PRODUCT_INFORMATION table:
SELECT (COUNT(list_price) FROM Product_intormation WHERE list_price=NULL;
SELECT count(nvl( list_price,0)) FROM product_information WHERE list_price is null;
SELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price is null.
BELECT COUNT(list_price) FROM product_information where list_price is NULL;
In SQL, when you want to count occurrences of null values using the COUNT function, you must remember that COUNT ignores null values. So, if you want to count rows with null list_price, you have to replace nulls with some value that can be counted. This is what the NVL function does. It replaces a null value with a specified value, in this case, 0.
A, C, and D options attempt to count list_price directly where it is null, but this will always result in a count of zero because COUNT does not count nulls. Option B correctly uses the NVL function to convert null list_price values to 0, which can then be counted. The WHERE list_price IS NULL clause ensures that only rows with null list_price are considered.
The SQL documentation confirms that COUNT does not include nulls in its count and NVL is used to substitute a value for nulls in an expression. So option B will give us the correct count of rows with a null list_price.
Which two statements are true? (Choose two.)
The USER SYNONYMS view can provide information about private synonyms.
The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
All the dynamic performance views prefixed with V$ are accessible to all the database users.
The USER OBJECTS view can provide information about the tables and views created by the user only.
DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.
A. The USER SYNONYMS view can provide information about private synonyms: This view contains information about private synonyms that have been created by the user. It lists all synonyms that are accessible to the user in their schema.
E. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access: This view lists the names of all data dictionary views available to the user, providing a directory of useful database metadata.
Incorrect options:
B: The SYSTEM user does not own all base tables of the data dictionary; some are owned by SYS.
C: Not all users have access to dynamic performance views prefixed with V$; access is restricted based on privileges.
D: The USER_OBJECTS view lists all objects owned by the user, not just tables and views but also other types of schema objects like procedures and functions.
Which two statements are true about CURRENT_TIMEITAMP?
The date is in the time zone of DBTIMEZONE.
The value varies depending on the setting of SESSIONTIMEZONE.
It returns the same date as CURRENT_DATE.
The time is in the time zone of DBTIMEZONE.
It returns a value of data type TIMESTAMP
It always returns the same value as SYSTIMESTAMP
B. True. CURRENT_TIMESTAMP returns the current date and time in the session time zone, which is determined by the SESSIONTIMEZONE setting.
E. True. CURRENT_TIMESTAMP returns a value of the data type TIMESTAMP WITH TIME ZONE.
A and D are incorrect because CURRENT_TIMESTAMP reflects the session time zone, not DBTIMEZONE. C is incorrect because CURRENT_DATE returns the current date with no time component, whereas CURRENT_TIMESTAMP includes both date and time. F is incorrect because SYSTIMESTAMP returns the current date and time from the system clock in the time zone of the database server, which can differ from the session's time zone affecting CURRENT_TIMESTAMP.
Which two actions can you perform with object privileges?
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.
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.
Examine this query:
SELECT employee_id,first_name,salary
FROM employees
WHERE hire_date>'&1';
Which two methods should you use to prevent prompting for a hire date value when this query is executed?
Use the DEFINE command before executing the query.
Store the query in a script and pass the substitution value to the script when executing it.
Replace'&1' with'&&1' in the query.
Execute the SET VERIFY OFF command before executing the query.
Use the UNDEFINE command before executing the query.
Execute the SET VERIFY ON command before executing the query.
In Oracle SQL, substitution variables (&1) can be used to dynamically replace a value in a SQL statement at runtime. To avoid being prompted for a value when executing a query that includes a substitution variable:
A. Use the DEFINE command before executing the query: By setting a value for a substitution variable using the DEFINE command, Oracle will use this value instead of prompting the user for input.
D. Execute the SET VERIFY OFF command before executing the query: The SET VERIFY OFF command suppresses the prompt for substitution variables by turning off the verification feature. This makes Oracle use the currently defined value for the variable.
References:
Oracle Database SQL Language Reference 12c, particularly the sections on substitution variables, the DEFINE command, and the SET command.
Examine the data in the INVOICES table:
Examine the data in the CURRENCIES table:
CURRENCY_CODE
-------------
JPY
GPB
CAD
EUR
USD
Which query returns the currencies in CURRENCIES that are not present in INVOICES?
SELECT currency_ code FROM currencies
MINUS
SELECT currency_ code FROM invoices;
SELECT * FROM currencies
WHERE NOT EXISTS (
SELECT NULL FROM invoices WHERE currency_ code = currency_ code);
SELECT currency_ code FROM currencies
INTERSECT
SELECT currency_ code FROM invoices;
SELECT * FROM currencies
MINUS
SELECT * FROM invoices;
To calculate the length of employment, you would compare the current date to the HIRE_DATE and calculate the difference in years.
A. This is the correct answer. The expression calculates the number of days between the current date (SYSDATE) and the HIRE_DATE, then divides by 365 to convert it to years, and checks if it is greater than 5.
B. SYSTIMESTAMP includes a time component including fractional seconds and time zone information, making this comparison incorrect.
C. There are typos in this option (CUARENT_DATE should be CURRENT_DATE and hire_data should be hire_date). Even with the correct function and column names, CURRENT_DATE returns the current date in the session time zone, not in years.
D. There are typos in this option (SYSNAYW should be SYSDATE). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.
E. This is a repetition of option D with the same issues.
F. There are typos in this option (CUNACV_DATE should be CURRENT_DATE and hire_data should be hire_date). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Date Functions"
Examine the data in the COLORS table:
Examine the data in the BRICKS table:
Which two queries return all the rows from COLORS?
SELECT.
FROM bricks b
RIGHT JOIN colors c
ON b. color _rgb_ hex_ value = c. rgb hex_ value;
SELECT
EROM colors C
LEFT JOIN bricks 上
USING (rgb _ hex_ value) ;
SELECT
FROM bricks b
FULL JOIN colors C
ON b. color rgb _ hex_ value = c. rgb _hex_ value;
SELECT *
EROM bricks | b
JOIN colors C
ON b. color_ rgb_ hex_ value =c. rgb _hex value;
SELECT
EROM colors C
LEET JOIN bricks b
ON b. color_ rgb_ hex value = c. rgb. hex.
value
WHERE b. brick_ id > 0;
The queries that will return all the rows from the COLORS table are those that ensure every record from COLORS is selected, regardless of whether there's a matching record in the BRICKS table:
Option A:
SELECT * FROM bricks b RIGHT JOIN colors c ON b.color_rgb_hex_value = c.rgb_hex_value;
A right join will return all the rows from the right table (COLORS), with the matching rows from the left table (BRICKS). If there is no match, NULL will be returned for columns from BRICKS.
Option B:
SELECT * FROM colors c LEFT JOIN bricks b USING (rgb_hex_value);
A left join will return all the rows from the left table (COLORS), with the matching rows from the right table (BRICKS). If there is no match, NULL will be returned for columns from BRICKS. The USING clause indicates a join condition where columns with the same names are compared for equality.
Options C, D, and E will not return all the rows from the COLORS table:
Option C: The full join will return all rows from both tables, but it is not restricted to only the rows from COLORS.
Option D: An inner join will return only the matching rows between both tables, not all rows from COLORS.
Option E: This is a left join, which would typically return all rows from COLORS, but the WHERE clause restricts the result set to only those rows from COLORS that have a matching BRICK_ID in BRICKS which is greater than 0, potentially excluding rows from COLORS.
Examine the description of the ORDER_ITEMS table:
Examine this incomplete query:
SELECT DISTINCT quantity * unit_price total_paid FROM order_items ORDER BY
Which two can replace
quantity
quantity, unit_price
total_paid
product_id
quantity * unit_price
In a SELECT statement with DISTINCT, the ORDER BY clause can only order by expressions that are part of the SELECT list.
A. quantity alone is not sufficient to replace <clause> as it is not included in the SELECT list after DISTINCT.
B. This option can successfully replace <clause> because both quantity and unit_price are used in the SELECT expression, and thus their individual values are valid for the ORDER BY clause.
C. total_paid is an alias for the expression quantity * unit_price, but it cannot be used in the ORDER BY clause because Oracle does not allow aliases of expressions in DISTINCT queries to be used in ORDER BY.
D. product_id is not included in the SELECT list after DISTINCT and thus cannot be used in ORDER BY.
E. The expression quantity * unit_price is exactly what is selected, so it can replace <clause> and the query will complete successfully.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ORDER BY Clause"
Which two statements are true about the results of using the INTERSECT operator in compound queries?
Reversing the order of the intersected tables can sometimes affect the output.
Column names in each SELECT in the compound query can be different.
INTERSECT returns rows common to both sides of the compound query.
The number of columns in each SELECT in the compound query can be different.
INTERSECT ignores NULLs
In Oracle Database 12c, the INTERSECT operator is used in compound queries to return only the rows that are common to both SELECT statements involved in the query. Here's a breakdown of why the correct answer is C:
A: Incorrect. The order of the tables in an INTERSECT operation does not affect the output because INTERSECT returns only the common elements between the two queries. The operation is symmetric.
B: Incorrect. While the column names themselves can differ between the two SELECT statements, the data types and the number of columns must match for INTERSECT to function correctly.
C: Correct. INTERSECT returns the intersection of the results of the two queries, which includes only rows that are found in both result sets.
D: Incorrect. The number of columns and their data types in each SELECT statement of the INTERSECT must be the same to avoid errors and to correctly perform the comparison.
E: Incorrect. INTERSECT does not ignore NULLs; rows with NULL values in corresponding columns must exactly match (both sides having NULLs in the same column) to be included in the output.
Which two are true about virtual columns?
They can be referenced In the where clause of an update or debete statement.
They can be referenced in the set clause of an update statement as the name of the column To be updated.
They can be indexed.
They cannot have a data type explicitly specified.
They can be referenced in the column expression of another virtxial column.
Regarding the properties and capabilities of virtual columns in Oracle Database:
C. They can be indexed: In Oracle, virtual columns can indeed be indexed. This allows for performance optimization on queries that utilize these columns, despite the fact that their values are derived and not stored physically.
D. They cannot have a data type explicitly specified: The data type of a virtual column is derived from the expression used to define it. Therefore, it is not specified explicitly but is inferred from the expression itself.
Incorrect options:
A: Virtual columns cannot be referenced in the WHERE clause of UPDATE or DELETE statements because they are not stored and cannot be individually modified.
B: Virtual columns cannot be referenced in the SET clause of an UPDATE statement since they are derived from other column values and cannot be updated directly.
E: Virtual columns cannot reference other virtual columns in their expressions due to the potential for recursive and complex dependencies.
Examine the description of the EMPLOYEES table:
NLS_DATE FORMAT is DD-MON-RR.
Which two queries will execute successfully?
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;
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 about GLOBAL TEMPORARY TABLES?
GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.
A TRUNCATE command issued in a session causes all rows In a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.
A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.
A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions.
Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.
GLOBAL TEMPORARY TABLE space allocation occurs at session start.
Global temporary tables in Oracle Database 12c have unique characteristics, primarily around their visibility and lifespan which is session-specific:
B. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted: This is accurate as TRUNCATE in the context of a global temporary table only affects the rows inserted during the session that issues the command. The effect is isolated to the session.
D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions: The definition (i.e., the structure of the table such as column names, data types, etc.) of a global temporary table is persistent and visible across sessions. However, the data within is session-specific.
E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted: True, as the data in a global temporary table is designed to be temporary for the duration of a session. When the session ends, the data is automatically deleted.
References:
Oracle Database Concepts and SQL Language Reference 12c, especially sections on temporary tables.
Examine these statements:
CREATE TABLE alter_test (c1 VARCHAR2(10), c2 NUMBER(10));
INSERT INTO alter_test VALUES ('123'， 123);
COMMIT;
Which is true ahout modifyIng the columns in AITER_TEST?
c1 can be changed to NUMBER(10) and c2 can be changed to VARCHAN2 (10).
c2 can be changed to NUMBER(5) but c1 cannot be changed to VARCHAN2 (5).
c2 can be changed to VARCHAR2(10) but c1 cannot be changed to NUMBER (10).
c1 can be changed to NUMBER(10) but c2 cannot be changed to VARCHAN2 (10).
c1 can be changed to VARCHAR2(5) and c2 can be changed to NUMBER (12,2).
In Oracle SQL, the ALTER TABLE statement is used to modify the definition of a table after it has been created. Specific changes are allowed depending on the current data type of the column and the data it contains.
A. You cannot change a VARCHAR2 column to NUMBER if the table contains rows with non-numeric values, which is the case with '123' as a VARCHAR2; it is a string, not a number.
B. You can reduce the size of a NUMBER column if the data will still fit within the new precision, but you cannot change a VARCHAR2 column to a smaller size if it contains values that exceed the new size limit.
C. You cannot change a NUMBER column to VARCHAR2 if the table contains rows because the existing data type of NUMBER does not match the new data type of VARCHAR2.
D. Similar to A, you cannot change the data type from VARCHAR2 to NUMBER if non-numeric data is present.
E. You can change a VARCHAR2(10) column to VARCHAR2(5) if all existing rows have values that fit within the new size, and you can change a NUMBER column to have a different scale (NUMBER(12,2)).
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ALTER TABLE"
Which two statements are true about * _TABLES views?
You must have ANY TABLE system privileges, or be granted object privilges on the table, to viewa tabl e in DBA TABLES.
USER TABLES displays all tables owned by the current user.
You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in USER_TABLES.
ALL TABLES displays all tables owned by the current user.
You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in ALL_TABLES.
All users can query DBA_TABLES successfully.
In Oracle, *_TABLES views provide information about tables.
B. USER_TABLES displays all tables owned by the current user, making this statement true. No additional privileges are required to see your own tables.
D. ALL_TABLES displays all tables that the current user has access to, either through direct ownership or through privileges, making this statement true.
A, C, E, and F are incorrect. Specifically:
A and E are incorrect because you do not need ANY TABLE system privileges to view tables in DBA_TABLES or ALL_TABLES; you need the SELECT_CATALOG_ROLE or equivalent privileges.
C is incorrect because as a user, you do not need additional privileges to see your own tables in USER_TABLES.
F is incorrect because not all users can query DBA_TABLES; this requires specific privileges or roles.
References:
Oracle Database Reference, 12c Release 1 (12.1): "Static Data Dictionary Views"
Which two are true about transactions in the Oracle Database?
DDL statements automatically commit only data dictionary updates caused by executing the DDL.
A DDL statement issued by a session with an uncommitted transation automaticall commits that transaction.
An uncommitted transaction is automatically committed when the user exits SQL*PLUS
DML statements always start new transactions.
A session can see uncommitted updates made by the same user in a different session
B. True. DDL (Data Definition Language) statements in Oracle Database are auto-commit statements. This means that if a DDL statement is issued, any uncommitted transactions in the same session will automatically be committed.
D. False. This is a common misconception. DML (Data Manipulation Language) statements do not automatically start new transactions. In Oracle, a transaction begins when the first DML statement (INSERT, UPDATE, DELETE, MERGE, SELECT FOR UPDATE) is encountered after a previous transaction has been committed or rolled back.
A is incorrect because DDL statements automatically commit the entire transaction, not just the data dictionary updates. C is incorrect because an uncommitted transaction is not automatically committed when a user exits SQL*Plus; instead, it is rolled back. E is incorrect because a session cannot see uncommitted updates made by other sessions.
Examine the description of the SALES1 table:
SALES2 is a table with the same description as SALES1,
Some sales data is duplicated In both tables.
You want to display the rows from the SALES1 table which are not present in the SALIES2 table.
Which set operator generates the required output?
SUBTRACT
INTERSECT
UNION ALL
MINUS
UNION
Which two statements are true about Oracle databases and SQL?
Updates performed by a database user can be rolled back by another user by using the ROLLBACK command.
The database guarantees read consistency at select level on user-created tablers.
When you execute an UPDATE statement, the database instance locks each updated row.
A query can access only tables within the same schema.
A user can be the owner of multiple schemas In the same database.
B. True. Oracle databases guarantee read consistency at the transaction level, meaning that each query can only see data that has been committed before the query began executing.
C. True. When an UPDATE statement is executed, Oracle locks each row as it is updated to prevent other transactions from modifying it until the transaction is committed or rolled back.
A is incorrect because updates made by one user cannot be rolled back by another user. D is incorrect because a query can access tables in other schemas if proper permissions are granted. E is incorrect because a user can own only one schema, which has the same name as the user in an Oracle database.
Examine the data in the ENPLOYEES table:
Which statement will compute the total annual compensation tor each employee？
SECECT last_namo, (menthy_salary + monthly_commission_pct) * 12 AS annual_comp
FROM employees;
SELCECT last_namo, (monthly_salary * 12) + (monthly_commission_pct * 12) AS annual_comp
FROM employees
SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * NVL
(monthly_commission_pct, 0)) AS annual_comp FROM employees
SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * monthly_commission_pct)
AS annual_comp FROM employees
The correct statement for computing the total annual compensation for each employee is option C. This is because the monthly commission is a percentage of the monthly salary (indicated by the column name MONTHLY_COMMISSION_PCT). To calculate the annual compensation, we need to calculate the annual salary (which is monthly_salary * 12) and add the total annual commission to it.
Here's the breakdown of the correct statement, option C:
(monthly_salary * 12) computes the total salary for the year.
NVL(monthly_commission_pct, 0) replaces NULL values in the monthly_commission_pct column with 0, ensuring that the commission is only added if it exists.
(monthly_salary * 12 * NVL(monthly_commission_pct, 0)) computes the annual commission by first determining the monthly commission (which is a percentage of the monthly salary), and then multiplying it by 12 to get the annual commission.
Finally, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) adds the annual salary and the annual commission to get the total annual compensation.
The other options are incorrect:
Option A is incorrect because it adds the monthly_commission_pct directly to the monthly_salary, which does not consider that monthly_commission_pct is a percentage.
Option B is incorrect because it adds the commission percentage directly without first applying it to the monthly salary.
Option D is incorrect because it does not handle the NULL values in the commission column, which would result in a NULL total annual compensation whenever the monthly_comission_pct is NULL.
References:
Oracle Documentation on NVL function: NVL
Oracle Documentation on Numeric Literals: Numeric Literals
Examine the description of the EMPLOYEES table:
Which two queries return rows for employees whose manager works in a different department?
SELECT emp. *
FROM employees emp
WHERE manager_ id NOT IN (
SELECT mgr.employee_ id
FROM employees mgr
WHERE emp. department_ id < > mgr.department_ id
);
SELECT emp.*
FROM employees emp
WHERE NOT EXISTS (
SELECT NULL
FROM employees mgr
WHERE emp.manager id = mgr.employee_ id
AND emp.department_id<>mgr.department_id
);
SELECT emp.*
FROM employees emp
LEFT JOIN employees mgr
ON emp.manager_ id = mgr.employee_ id
AND emp. department id < > mgr. department_ id;
SELECT emp. *
FROM employees emp
RIGHT JOIN employees mgr
ON emp.manager_ id = mgr. employee id
AND emp. department id <> mgr.department_ id
WHERE emp. employee_ id IS NOT NULL;
SELECT emp. *
FROM employees emp
JOIN employees mgr
ON emp. manager_ id = mgr. employee_ id
AND emp. department_ id<> mgr.department_ id;
To find employees whose manager works in a different department, you can use a subquery or a join that compares the DEPARTMENT_ID of the employee with the DEPARTMENT_ID of their manager.
A. This query is incorrect because the NOT IN subquery incorrectly attempts to compare EMPLOYEE_ID with MANAGER_ID, and the correlation condition inside the subquery is incorrect.
B. This query is correct. The NOT EXISTS clause correctly identifies employees whose MANAGER_ID matches the EMPLOYEE_ID of another employee (mgr) and where the DEPARTMENT_ID differs from that manager's DEPARTMENT_ID.
C. This query is incorrect because the LEFT JOIN will return all employees, and there is no WHERE clause to filter out those employees whose managers are in the same department.
D. This query is incorrect. The RIGHT JOIN does not ensure that the resulting rows are for employees whose manager works in a different department. It also returns all managers, which is not the requirement.
E. This query is correct. The JOIN ensures that the returned rows are for employees (emp) whose MANAGER_ID matches the EMPLOYEE_ID of managers (mgr), and it correctly filters to include only those employees whose DEPARTMENT_ID is different from their manager's DEPARTMENT_ID.
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?
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.
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 three statements are true about sequences in a single instance Oracle database?
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.
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 three statements are true about performing Data Manipulation Language (DML) operations on a view In an Oracle Database?
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.
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.
Examine the description of the transactions table:
Which two SQL statements execute successfully?
SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount+100 "DUES" from transactions;
SELECT customer_id AS 'CUSTOMER-ID',transaction_date AS DATE, amount+100 'DUES' from transactions;
SELECT customer_id CUSTID, transaction_date TRANS_DATE，amount+100 DUES FROM transactions;
SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM transactions;
SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 "DUES AMOUNT" FROM transactions;
A. True, this statement will execute successfully. In Oracle SQL, column aliases can be given in double quotes which allows the use of special characters like a hyphen. Additionally, you can perform arithmetic operations such as amount+100 directly in the SELECT clause to return increased values of amount.
C. True, this statement is also valid. It does not use any special characters or reserved keywords as aliases that would require double quotes. The arithmetic operation is the same as in option A, which is permissible.
For B, D, and E, the reasons for failure are: B. In Oracle SQL, single quotes are used for string literals, not for column aliases. Therefore, using single quotes around 'CUSTOMER-ID' and 'DUES' will result in an error. D. The word "DATE" is a reserved keyword in Oracle SQL. When used as a column alias without double quotes, it will lead to an error. In this statement, although "DATE" is in double quotes, it's a best practice to avoid using reserved keywords as aliases. E. There are syntax errors in the statement. "customer id" should be "customer_id", and the alias "CUSTOMER-ID" should be enclosed in double quotes if special characters are used. Also, "DUES AMOUNT" as an alias should be in double quotes to be valid because it contains a space.
References:
Oracle documentation on column aliases: Oracle Database SQL Language Reference
Oracle reserved words: Oracle Database SQL Language Reserved Words
Examine this business rule:
Each student can work on multiple projects and earth project can have multiple students.
You must decide an Entity Relationship (ER) model for optional data storage and allow generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true?
An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.
The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships.
PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.
STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
For the described business rule, the relationship between the students and projects entities is a many-to-many relationship, meaning that each student can be involved in multiple projects and each project can have multiple students.
Statement A is true because to implement a many-to-many relationship in a relational database, an associative (junction) table is typically used. This table will contain the primary keys from both related entities as foreign keys in the associative table, making a composite primary key consisting of STUDENT_ID and PROJECT_ID. This setup allows the database to effectively manage the relationships between students and projects.
Statement B is true as it accurately describes the solution to managing a many-to-many relationship. A direct many-to-many relationship cannot be physically implemented in a relational database. It is instead resolved into two 1-to-many relationships using an associative table as described in A. This is a fundamental relational database design principle aimed at normalizing the database and avoiding redundancy.
Statements C, D, and E are incorrect as they misrepresent the nature of the relationships and key constraints necessary for this scenario. Specifically, C and E incorrectly suggest that one entity's primary key should serve as a foreign key in another, which does not align with the many-to-many relationship requirement described. Statement D incorrectly suggests a 1-to-many relationship directly between students and projects, which does not meet the business rule requirement.
Which two are true about scalar subquery expressions?
You cannot correlate them with a table in the parent statement
You can use them as a default value for a column.
.You must enclose them in parentheses.
They can return at most one row.
They can return two columns.
Scalar subquery expressions in Oracle SQL have specific rules:
Option C: You must enclose them in parentheses.
Scalar subqueries must be enclosed in parentheses. This is a requirement for syntax clarity and to distinguish the subquery from the rest of the SQL statement.
Option D: They can return at most one row.
Scalar subqueries are designed to return exactly one row containing one column. If a scalar subquery returns more than one row, Oracle will throw an error, ensuring that the subquery either returns a single value or no value (NULL).
Options A, B, and E are incorrect based on Oracle SQL functionalities:
Option A is incorrect because scalar subqueries can indeed be correlated with the parent query.
Option B is true but not in the context of default constraints for table columns in the CREATE TABLE statement.
Option E is incorrect because scalar subqueries can only return a single column by definition.
Examine the description of the EMPLOYEES table：
Which two queries return the highest salary in the table?
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;
SELECT MAX (salary)
FROM employees;
SELECT MAX (salary)
FROM employees
GROUP BY department_id;
SELECT MAX (salary)
FROM employees
GROUP BY department_id
HAVING MAX (salary) = MAX (MAX (salary));
SELECT MAX (MAX (salary))
FROM employees
GROUP BY department_id;
Query B will return the highest salary in the table without grouping by department. It simply selects the maximum value for the salary column across the entire table.
Query C and D are incorrect because the GROUP BY clause will return the highest salary for each department, not the single highest salary in the entire table.
Query E is incorrect because MAX(MAX(salary)) is not a valid use of aggregate functions and will result in an error.
References:
Oracle Documentation on Aggregate Functions: Aggregate Functions
Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)?
There must be an equal number of columns in each SELECT list.
The name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.
Each SELECT statement in the query can have an ORDER BY clause.
None of the set operators can be used when selecting CLOB columns.
The FOR UPDATE clause cannot be specified.
A. True. When using set operators, the number and order of columns must be the same in all SELECT statements involved in the query. The data types of those columns must also be compatible.
E. True. The FOR UPDATE clause cannot be specified in a subquery or a SELECT statement that is combined with another SELECT statement by using a set operator.
B is incorrect because the column names do not need to match; only the number and data types of the columns must be compatible. C is incorrect because only the final SELECT statement in the sequence of UNION/INTERSECT/MINUS operations can have an ORDER BY clause. D is incorrect because, as of Oracle 12c, UNION ALL can be used with CLOB columns, but UNION, INTERSECT, and MINUS cannot.
Examine the description of the CUSTONERS table:
CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?
LEFT OUTER JOIN with self join
PULL OUTER JOIN with self join
subquery
RIGHT OUTER JOIN with self join
self Join
To find duplicate customer details entered under different CUSTNO values, one would typically use methods that compare records within the same table:
C. Subquery: A subquery can be used to compare customer details in the CUSTOMERS table and identify duplicates. This subquery can look for matches in the customer details while having different CUSTNO.
E. Self Join: A self join can also be used for this purpose. This involves joining the CUSTOMERS table to itself on the columns containing customer details to identify rows that have the same information but different CUSTNO.
References:
Oracle Database SQL Language Reference 12c, particularly the sections on self joins and subqueries, as these can be utilized to find duplicate rows based on specified criteria.
Which three statements are true?
A customer can exist in many countries.
The statement will fail if a row already exists in the SALES table for product 23.
The statement will fail because subquery may not be I contained in a values clause.
The SALES table has five foreign keys.
The statement will execute successfully and a new row will be inserted into the SALES table.
A product can have a different unit price at different times.
A. A customer can exist in many countries. This is true as customers can have multiple addresses or operations in different countries, and a database design can reflect this by allowing multiple country entries for a single customer1.
C. The statement will fail because subquery may not be I contained in a values clause. In Oracle Database 12c, a subquery cannot be used within the VALUES clause of an INSERT statement. The correct approach would be to use the subquery in conjunction with the INSERT INTO … SELECT syntax if multiple rows are derived from a subquery2.
F. A product can have a different unit price at different times. It is common for products to have different unit prices at different times due to various factors such as promotions, discounts, or changes in cost price. This can be represented in a database by having a price history table or a similar mechanism to track the changes in price over time1.
Note: The other options are incorrect because:
B. The statement about the SALES table failing if a row already exists for product 23 is not necessarily true. Oracle allows for multiple rows with the same product ID if the table is designed to handle such cases, like having a composite primary key or no constraints preventing duplicates.
D. Without specific information about the SALES table’s design, we cannot verify the number of foreign keys it has.
E. The statement about the successful execution and insertion of a new row into the SALES table is too vague without the context of the actual SQL statement being referred to.
Which two statements about INVISIBLE indexes are true?
an INVISIBLE Index consumes no storage
You can only create one INVISIBLE index on the same column list
The query optimizer never considers INVISIBLE Indexes when determining execution plans
You use AlTER INDEX to make an INVISIBLE Index VISIBLE
All INSERT, UPDATE, and DELETE statements maintain entries in the index
INVISIBLE indexes are a feature in Oracle Database 12c and later versions that allow an index to be maintained but not used by the optimizer unless explicitly hinted.
A. False. An INVISIBLE index still consumes storage space as it is maintained in the background.
B. False. There is no such restriction. You can create multiple INVISIBLE indexes on the same column list.
C. False. The optimizer can consider INVISIBLE indexes if they are hinted at in the query.
D. True. You can alter the visibility of an index using the ALTER INDEX command to make an INVISIBLE index VISIBLE.
E. True. Even though they are invisible to the optimizer by default, all DML operations such as INSERT, UPDATE, and DELETE continue to maintain the index as they would with a VISIBLE index.
A session's NLS_DATE_FORMAT is set to DD Mon YYYY .
Which two queries return the value 1 Jan 2019?
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;
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.
Examine the description of the CUSTOMERS table:
You want to display details of all customers who reside in cities starting with the letter D followed by at least two character.
Which query can be used?
SELECT * FROM customers WHERE city ='D_%';
SELECT * FROM customers WHERE city ='%D_';
SELECT * FROM customers WHERE city LIKE'D %';
SELECT * FROM customers WHERE city LIKE'D_';
The LIKE operator is used in SQL to search for a specified pattern in a column. To find all customers residing in cities starting with 'D' followed by at least two characters, you need to use the LIKE operator with the appropriate wildcard pattern:
A. SELECT * FROM customers WHERE city LIKE 'D_%'; The underscore () wildcard character in SQL represents a single character. The percent (%) wildcard character represents zero or more characters. So 'D%' will match any city starting with 'D' followed by at least one character (as there is one underscore).
References:
Oracle Database SQL Language Reference 12c, specifically the section on pattern matching with the LIKE condition.
Which three statements are true about GLOBAL TEMPORARY TABLES?
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
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 statement is true about TRUNCATE and DELETE?
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.
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.
Examine these two queries and their output:
SELECT deptno, dname FROM dept;
SELECT ename, job, deptno FROM emp ORDER BY deptno;
Now examine this query:
SELECT ename, dname
FROM emp CROSS JOIN dept WHERE job = 'MANAGER'
AND dept.deptno IN (10, 20) ;
64
6
3
12
In a CROSS JOIN (also known as a Cartesian join), each row from the first table (emp) is joined to every row of the second table (dept). When we apply the filter for job = 'MANAGER' and dept.deptno IN (10, 20), we are restricting the results to managers in departments 10 and 20.
From the given data:
There are 2 managers in department 10 (CLARK and KING).
There is 1 manager in department 20 (JONES).
With a cross join, each of these emp records will join with each dept record where deptno is 10 or 20. Since there are two departments (10 and 20) in the dept table, each employee will match with two departments.
Therefore, the result set will be:
CLARK with accounting (dept 10) and research (dept 20)
KING with accounting (dept 10) and research (dept 20)
JONES with accounting (dept 10) and research (dept 20)
That gives us a total of 6 rows, which means the correct answer is B: 6.
Which two statements are true about single row functions?
CONCAT: can be used to combine any number of values
MOD: returns the quotient of a division operation
CEIL: can be used for positive and negative numbers
FLOOR: returns the smallest integer greater than or equal to a specified number
TRUNC: can be used with NUMBER and DATE values
Single-row functions operate on single rows and return one result per row. Let's look at each option in the context of Oracle 12c SQL:
A. CONCAT: This function can only combine two values at a time. If you need to concatenate more than two values, you have to nest CONCAT functions or use the || operator.
B. MOD: The MOD function returns the remainder of a division operation, not the quotient.
C. CEIL: This function returns the smallest integer that is greater than or equal to a specified number. It works with both positive and negative numbers.
D. FLOOR: It returns the largest integer that is less than or equal to the specified number, not greater than or equal to.
E. TRUNC: This function can indeed be used with both NUMBER and DATE values to truncate them to a specified number of decimal places or to a particular component of a date.
References:
Oracle Database SQL Language Reference 12c Release 1 (12.1), Functions
Oracle Database SQL Language Reference 12c Release 1 (12.1), CONCAT Function
Oracle Database SQL Language Reference 12c Release 1 (12.1), MOD Function
Oracle Database SQL Language Reference 12c Release 1 (12.1), CEIL and FLOOR Functions
Oracle Database SQL Language Reference 12c Release 1 (12.1), TRUNC Function
Examine 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?
WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1)) LIKE UPPER('MC%')
WHERE SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1) LIKE 'Mc%’ OR 'MC%’
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1)) IN (‘MC%’,’Mc%’)
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ') + 1)) LIKE ‘Mc%’
WHERE SUBSTR(cust_name, INSTR(cust_name,’ ‘) + 1) LIKE ‘Mc%’
To find customer names where the last name starts with Mc or MC, the correct queries are:
A. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'MC%'This query converts the substring of cust_name that comes after the first space (which should correspond to the last name) to uppercase and checks if it starts with 'MC', which will match both 'Mc' and 'MC'.
E. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1) LIKE 'Mc%'This query checks if the substring of cust_name after the first space starts with 'Mc'. However, this query will only match last names that start with 'Mc' and not 'MC' unless the database is using a case-insensitive collation.
Options B, C, and D are incorrect:
B is incorrect because the syntax is not valid in Oracle SQL; the LIKE clause cannot be used to match multiple patterns in that way.
C is incorrect because INITCAP would not only capitalize the first letter of 'mc' or 'Mc' but would lowercase all other letters, which is not the intended action.
D is incorrect because, like option C, INITCAP is not the appropriate function for this use case and it will not correctly identify names that start with 'MC'.
Examine this partial statement:
SELECT ename, sal,comm FROM emp
Now examine this output:
WHICH ORDER BY clause will generate the displayed output?
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
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 description of the BOOKS_TRANSACTIONS table:
Examine this partial SQL statement:
SELECT * FROM books_transactions
Which two WHERE conditions give the same result?
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');
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 this SQL statement:
SELECT cust_id, cus_last_name "Last Name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses, any one of which can complete the query successfully.
ORDERBY 2, 1
ORDER BY "CUST_NO"
ORDER BY 2,cust_id
ORDER BY CUST_NO
ORDER BY "Last Name"
In SQL, the ORDER BY clause can refer to columns by their alias defined in the SELECT clause or by their positional number in the SELECT list. It's important to understand that after a UNION, the column names in the ORDER BY clause refer to the first SELECT statement's column names and aliases:
Option A: ORDER BY 2, 1This is correct because it refers to the second and first columns in the first SELECT clause, which correspond to the aliases "Last Name" and cust_id, respectively.
Option E: ORDER BY "Last Name"This is correct because "Last Name" is a valid alias defined in the first SELECT clause.
The other options fail for the following reasons:
Option B: "CUST_NO" is not recognized in the ORDER BY clause because it's not an alias used in the first SELECT statement.
Option C: Incorrect because 2, cust_id mixes positional reference with a column name that doesn't apply to both SELECT statements consistently.
Option D: ORDER BY CUST_NO fails because CUST_NO is not an alias in the first SELECT clause.
SELECT *
FROM bricks,colors;
Which two statements are true?
You can add an ON clause with a join condition.
You can add a WHERE clause with filtering criteria.
It returns the number of rows in BRICKS plus the number of rows in COLORS.
You can add a USING clause with a join condition.
It returnsthe same rows as SELECT * FROM bricks CROSS JOIN colors.
For the SELECT statement from two tables without a JOIN clause:
Option B: You can add a WHERE clause with filtering criteria.
A WHERE clause can be added to a query to filter the results based on specified conditions.
Option E: It returns the same rows as SELECT * FROM bricks CROSS JOIN colors.
A comma-separated list of tables in a FROM clause is equivalent to a CROSS JOIN, resulting in a Cartesian product of the tables.
Options A, C, and D are incorrect because:
Option A: You cannot add an ON clause without changing the comma-separated FROM clause to a proper JOIN.
Option C: It returns the Cartesian product, not the sum, of the rows from BRICKS and COLORS.
Option D: A USING clause is not applicable without changing the syntax to a proper JOIN.
You issued this command: DROP TABLE hr. employees;
Which three statements are true?
ALL constraints defined on HR, EMPLOYEES are dropped.
The HR. EMPLOYEES table may be moved to the recycle bin.
Synonyms for HR EMPLOYEES are dropped.
Sequences used to populate columns in the HR. EMPLOYEES table are dropped.
All indexes defined on HR, EMPLOYEES are dropped.
Views referencing HR, EMPLOYEES are dropped.
Regarding the DROP TABLE command:
A. ALL constraints defined on HR.EMPLOYEES are dropped: Dropping a table will automatically drop all constraints associated with that table.
B. The HR.EMPLOYEES table may be moved to the recycle bin: In Oracle, unless explicitly using PURGE, dropped tables go to the recycle bin, allowing recovery.
E. All indexes defined on HR, EMPLOYEES are dropped: When a table is dropped, all its indexes are also automatically dropped.
Incorrect options:
C: Synonyms are not automatically dropped when the table is dropped; they become invalid.
D: Sequences are independent objects and are not dropped when a table is dropped.
F: Views are not dropped; however, they will return errors upon being queried if their base table is dropped.
Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
SELECT last name,
(monthly salary*12) + (monthly_commission_pct * 12) AS
annual comp
FROM employees
;
SELECT last_ name (monthly_ salary+ monthly_ commission _ pct) *12 AS annual_
FROM employees ;
SELECT last name, (monthly_ salary *12) + (monthly_ salary * 12 * NVL
(monthly commission pct,0) ) As annual _ comp
FROM employees;
SELECT last_ name, monthly_ salary*12) + (monthly_ salary * 12 * Monthly commission _Pct) AS
annual_ comp
FROM employees;
To calculate the total annual compensation, you need to sum the annual salary with the annual commission. The annual commission is the monthly commission percentage times the monthly salary times 12 (months). The NVL function is used to replace NULL with 0 for commission percentage when calculating the commission.
A: This statement is incorrect because it does not handle NULL values for 'monthly_commission_pct', which will result in NULL for the entire expression if 'monthly_commission_pct' is NULL.
B: This statement is syntactically incorrect because it lacks commas and proper parentheses to separate the column name 'last_ name' from the calculation.
C: This is the correct statement as it uses NVL to replace NULL with 0 and calculates the total annual compensation correctly.
D: This statement is incorrect because, like in option A, it does not handle NULL values in 'monthly_commission_pct', resulting in NULL if 'monthly_commission_pct' is NULL.
You execute this query:
SELECT TO CHAR (NEXT_DAY(LAST_DAY(SYSDATE),’MON’ ),’ dd“Monday for” fmMonth rrr’) FROM DUAL;
What is the result?
It executes successfully but does not return any result.
It returns the date for the first Monday of the next month.
It generates an error.
It returns the date for the last Monday of the current month.
The query uses TO_CHAR and NEXT_DAY functions to format and determine dates:
B. It returns the date for the first Monday of the next month: The function NEXT_DAY(LAST_DAY(SYSDATE), 'MON') finds the next Monday after the last day of the current month, effectively giving the first Monday of the next month. The TO_CHAR formatting is used to return this in a readable format.
Which three are true about the CREATE TABLE command?
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.
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 a full outer join?
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.
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 list of queries:
Which two statements are true?
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.
Which two are true about using constraints?
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.
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: https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52271
Oracle Documentation on NOT NULL Constraints: https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52162
Which three are true about granting object privileges on tables, views, and sequences?
UPDATE can be granted only on tables and views.
DELETE can be granted on tables, views, and sequences.
REFERENCES can be granted only on tables and views.
INSERT can be granted on tables, views, and sequences.
SELECT can be granted only on tables and views.
ALTER can be granted only on tables and sequences.
In Oracle Database, object privileges are rights to perform a particular action on a specific object in the database. Here's why the other options are incorrect:
A. UPDATE can be granted on tables, views, and materialized views, but not sequences. B. DELETE cannot be granted on sequences because sequences do not store data that can be deleted. D. INSERT cannot be granted on sequences; sequences are used to generate numbers, not to be inserted into directly. C. REFERENCES allows the grantee to create a foreign key that references the table or the columns of the table. It is applicable only to tables and views. E. SELECT can indeed only be granted on tables and views (including materialized views). F. ALTER is an object privilege that can be granted on tables and sequences but not views.
For more details, one may refer to the Oracle Database SQL Language Reference documentation, which specifies the types of object privileges and the objects they apply to.
Examine the description of the EMPLOYEES table:
Which statement increases each employee's SALARY by the minimum SALARY for their DEPARTM
ENT_ID?
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);
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"
which three statements are true about indexes and their administration in an Oracle database?
The same table column can be part of a unique and non-unique index
A DESCENDING INDEX IS A type of function-based index
A DROP INDEX statement always prevents updates to the table during the drop operation
AN INVISIBLE INDEX is not maintained when DML is performed on its underlying table.
AN INDEX CAN BE CREATED AS part of a CREATE TABLE statement
IF a query filters on an indexed column then it will always be used during execution of query
A. True, a single table column can be included in both a unique index and a non-unique index, provided they are different indexes.
D. True, an invisible index is maintained during DML operations, which means it is updated when insert, update, or delete operations are performed on the table. However, it is not used by the optimizer by default for query execution unless explicitly hinted or the session is altered to consider invisible indexes.
E. True, an index can be specified in the CREATE TABLE statement using the INDEX clause to create an index on a specific column at the time of table creation.
B, C, and F are not correct: B. A descending index is simply an index that is sorted in descending order, not a type of function-based index. C. A DROP INDEX
Which three statements are true about inner and outer joins?
A full outer join returns matched and unmatched rows.
A full outer join must use Oracle syntax.
Outer joins can be used when there are multiple join conditions on two tables.
Outer joins can only be used between two tables per query.
An inner join returns matched rows.
A left or right outer join returns only unmatched rows.
A: True. A full outer join does indeed return both matched and unmatched rows from both tables involved in the join. It combines the results of both left and right outer joins.
E: True. An inner join, by definition, returns rows that have matching values in both tables. Rows from both tables that do not match are not returned in an inner join result set.
Inner joins match rows from the joined tables based on the join condition, while outer joins include all rows from one or both tables regardless of whether a matching row exists in the other table.
References:The Oracle SQL documentation explains different types of joins, including inner joins, left and right outer joins, and full outer joins, clarifying how they differ in the result sets they produce.
Which statement will execute successfully?
SELECT 1, 2 FROM DUAL
UNION
SELECT 3, 4 FROM DUAL
ORDER BY 1, 2;
SELECT 3 FROM DUAL
UNION
SELECT 4 FROM DUAL
ORDER BY 3 ;
SELECT 1, 2 FROM DUAL
UNION
SELECT 3, 4 FROM DUAL
ORDER BY 3, 4;
SELECT 1 FROM DUAL
UNION
SELECT 2 FROM DUAL
ORDER BY 1, 2;
B. True. This statement will execute successfully because it has a single column in the SELECT statements combined with UNION, and the ORDER BY clause is referencing a valid column in the result set.
A is incorrect because it uses an ORDER BY clause with two columns, which is not allowed when the SELECT statements have only one column each. C is incorrect for the same reason as A; it references columns that do not exist in the result set. D is incorrect because it attempts to ORDER BY a second column, which does not exist in the result of the union.
Which two statements are true about the rules of precedence for operators?
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.
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
Which two statements are true about the SET VERIFY ON command?
It displays values for variables created by the DEFINE command.
It can be used in SQL Developer and SQL*Plus.
It can be used only in SQL*plus.
It displays values for variables prefixed with &&.
It displays values for variables used only in the WHERE clause of a query.
The SET VERIFY ON command is related to how SQL*Plus and SQL Developer display information about substitution variables:
A. It displays values for variables created by the DEFINE command: When VERIFY is set to ON, SQL*Plus and SQL Developer will display the old and new values of a substitution variable when it is redefined using the DEFINE command or when a new value is provided for it during the session.
B. It can be used in SQL Developer and SQL*Plus: While traditionally associated with SQL*Plus, the SET VERIFY command is also supported in SQL Developer, allowing you to control the display of substitution variable values in both environments.
References:
Oracle SQL*Plus User's Guide and Reference, especially the section on the SET command and substitution variables.
Which two queries will result in an error?
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;
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"
Which statement is true about the INTERSECT operator used in compound queries?
It processes NULLS in the selected columns.
INTERSECT is of lower precedence than UNION or UNION ALL.
It ignores NULLS.
Multiple INTERSECT operators are not possible in the same SQL statement.
For the question about the INTERSECT operator in SQL:
A. It processes NULLS in the selected columns: The INTERSECT operator compares two SELECT statements and returns rows that exist in both queries. It considers NULLs during this process, and NULLs in corresponding columns must match for rows to be considered equal. This means if both selected columns in the intersecting queries have NULLs, those rows will be included in the output.
Incorrect options:
B: INTERSECT has higher precedence than UNION and UNION ALL, not lower.
C: It does not ignore NULLs; rather, it processes them, as explained.
D: Multiple INTERSECT operators are indeed possible in the same SQL statement, allowing for complex compound queries.
Which statements is true about using functions in WHERE and HAVING?
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
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.
Examine this statement which executes successfully:
CREATE view emp80 AS
SELECT
FROM employees
WHERE department_ id = 80
WITH CHECK OPTION;
Which statement will violate the CHECK constraint?
DELETE FROM emp80
WHERE department_ id = 90;
SELECT
FROM emp80
WHERE department_ id = 90;
SELECT
FROM emp80
WHERE department. id = 80;
UPDATE emp80
SET department. 1d =80;
WHERE department_ id =90;
In Oracle SQL, the WITH CHECK OPTION clause in a CREATE VIEW statement ensures that all data manipulation statements performed through the view must conform to the view's defining query.
Option A: DELETE FROM emp80 WHERE department_id = 90;
This will violate the CHECK constraint because the WHERE clause condition does not meet the view's restriction (department_id = 80). This statement attempts to delete rows that the view is not supposed to have access to (since it should only include rows where department_id is 80).
Options B, C, and D do not violate the CHECK constraint because:
Option B: A SELECT statement does not modify data, so it does not violate the CHECK OPTION.
Option C: Selecting rows with department_id = 80 is consistent with the view’s definition.
Option D: There is a syntax error, but assuming it meant to set department_id to 80 where it is currently 90, it still would not violate the CHECK constraint because the CHECK OPTION on a view does not prevent updates that do not change the rows to fall outside the view's filter. However, since department_id is supposed to be 80 as per the view definition, this update operation doesn't make logical sense, as there should be no rows with department_id = 90 to update.
Examine thee statements which execute successfully:
CREATE USER finance IDENTIFIED BY pwfin;
CREATE USER fin manager IDENTIETED BY pwmgr;
CREATE USER fin. Clerk IDENTIFIED BY pwclerk;
GRANT CREATE SESSON 20 finance, fin clerk;
GRANT SELECT ON scott. Emp To finance WITH GRANT OPTION;
CONNECT finance/pwfin
GRANT SELECT ON scott. emp To fin_ _clerk;
Which two are true?
Dropping user FINANCE will automatically revoke SELECT on SCOTT. EMP from user FIN _ CLERK
Revoking SELECT on SCOTT. EMP from user FINANCE will also revoke the privilege from user FIN_ CLERK.
User FINANCE can grant CREATE SESSION to user FIN MANAGER.
User FIN CLERK can grant SELECT on SCORT, ENP to user FIN MANAGER.
User FINANCE is unable to grant ALL on SCOTT.ENP to FIN MANAGER.
Regarding privileges in Oracle:
Option A: Dropping user FINANCE will automatically revoke SELECT on SCOTT.EMP from user FIN_CLERK.
Dropping a user will revoke all privileges that user has granted to others.
Option B: Revoking SELECT on SCOTT.EMP from user FINANCE will also revoke the privilege from user FIN_CLERK.
Since FINANCE granted SELECT on SCOTT.EMP to FIN_CLERK with grant option, revoking the privilege from FINANCE will also cascade and revoke it from FIN_CLERK.
Options C, D, and E are incorrect because:
Option C: User FINANCE can grant privileges it owns, but CREATE SESSION is a system privilege; unless FINANCE has the 'WITH ADMIN OPTION' for CREATE SESSION, it cannot grant this privilege.
Option D: User FIN_CLERK can only grant privileges to others if it has been given those privileges with the 'WITH GRANT OPTION' and it was not given in this case.
Option E: User FINANCE can grant privileges on SCOTT.EMP to other users only if it has those privileges with 'WITH GRANT OPTION'. If the privilege was just SELECT with GRANT OPTION, it cannot grant ALL privileges.
You execute these commands successfully:
CREATE GLOBAL TEMPORARY TABLE invoices _ gtt
( customer id INTEGER,
invoice_ total NUMBER (10, 2)
) ON COMMIT PRESERVE ROWS;
INSERT INTO invoices_ gtt VALUES (1， 100);
COMMIT;
Which two are true?
You can add a foreign key to the table.
When you terminate your session, the row will be deleted.
To drop the table in this session, you must first truncate it.
You can add a column to the table in this session.
Other sessions can view the committed row.
A: This statement is false. You cannot add a foreign key to a global temporary table because the data in a global temporary table is session-specific, while a foreign key constraint implies a permanent relationship.
B: This statement is false. The ON COMMIT PRESERVE ROWS clause specifies that the data in the table should be preserved until the end of the session, not deleted at the end of the transaction.
C: This statement is false. You can drop a global temporary table without truncating it first.
D: This statement is true. You can alter a global temporary table to add columns within a session.
E: This statement is false. Data in a global temporary table with the ON COMMIT PRESERVE ROWS clause is private to the session that inserted the data, and cannot be seen by other sessions.
Which two are true about the WITH GRANT OPTION clause?
The grantee can grant the object privilege to any user in the database, with of without including this option.
The grantee must have the GRANT ANY OBJECT PRIVILEGE system prvilege to use this option.
It can be used when granting privileges to roles.
It can be used for system and object privileges.
It cannot be used to pass on privileges to PUBLIC by the grantee.
It can be used to pass on privileges to other users by the grantee.
The WITH GRANT OPTION clause in Oracle SQL allows the grantee to grant the privilege they have received to another user or role.
E. It cannot be used to pass on privileges to PUBLIC by the grantee: The WITH GRANT OPTION does not allow the grantee to pass on privileges to PUBLIC. Only the object's owner or a user with the GRANT ANY OBJECT PRIVILEGE system privilege can grant privileges to PUBLIC.
F. It can be used to pass on privileges to other users by the grantee: This is true. When a user receives privileges with the WITH GRANT OPTION, they can grant that privilege to another user or role.
References:
Oracle Database SQL Language Reference 12c, specifically sections on user privileges and the WITH GRANT OPTION.
Examine this incomplete query:
SELECT DATA’2019-01-01’+
FROM DUAL;
Which three clauses can replace
INTERVAL ‘12:00’
INTERVAL’0,5’DAY
INTERVAL’12’ HOUR
INTERVAL’720’MINUTE
INTERVAL’0 12’DAY TO HOUR
INTERVAL’11:60’HOUR TO MINUTE
In Oracle SQL, adding hours to a date can be done using the INTERVAL keyword with appropriate clauses:
Option C: INTERVAL '12' HOUR
Adds exactly 12 hours to a date.
Option D: INTERVAL '720' MINUTE
Since 720 minutes equal 12 hours, this correctly adds 12 hours to a date.
Option F: INTERVAL '11:60' HOUR TO MINUTE
This interval represents 11 hours and 60 minutes, which effectively adds up to 12 hours.
Options A, B, and E contain incorrect syntax or do not match the required addition of 22 hours as specified in the question.
Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?
The first column in the last SELECT of the compound query
The first NUMBER column in the first SELECT of the compound query
The first VARCHAR2 column in the first SELECT of the compound query
The first column in the first SELECT of the compound query
The first NUMBER or VARCHAR2 column in the last SELECTof the compound query
For the sorting of output in compound queries (INTERSECT, UNION, etc.):
D. The first column in the first SELECT of the compound query: By default, Oracle does not automatically sort the results of SET operations unless an ORDER BY clause is explicitly stated. However, if an ORDER BY is implied or specified without explicit columns, the default sorting would logically involve the first column specified in the first SELECT statement of the compound query.
Which two are SQL features?
providing graphical capabilities
providing variable definition capabilities.
providing database transaction control
processing sets of data
providing update capabilities for data in external files
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 two statements are true about the order by clause when used with a sql statement containing a set operator such as union?
column positions must be used in the order by clause.
The first column in the first select of the compound query with the union operator is used by default to sort output in the absence of an order by clause.
Each select statement in the compound query must have its own order by clause.
only column names from the first select statement in the compound query are recognized.
Each select statement in the compound query can have its own order by clause.
A. True, when using the ORDER BY clause with set operators like UNION, you can refer to the results by column positions. This allows for consistent sorting behavior across potentially heterogeneous SELECT statements.D. True, only the column names or positions from the first SELECT statement are recognized in the ORDER BY clause when used with set operators like UNION, as the result set is treated as if it originated from the first SELECT structure.
References:
Oracle documentation on ORDER BY with set operators: Oracle Database SQL Language Reference
Explanation of ORDER BY usage: Oracle SQL Tips
Top of Form
Which two statements are true about * TABLES views?
You must have SELECT privileges on a table to view it in ALL TABLES.
You must have SELECT privileges on a table to view it in DBA TABLES.
USER TABLES displays all tables owned by the current user.
All TABLES displays all tables owned by the current user.
You must have SELECT privileges on a table to view it in USER TABLES.
All users can query DBA TABLES successfully.
For the *TABLES views in Oracle:
Option C: USER_TABLES displays all tables owned by the current user.
USER_TABLES is an Oracle data dictionary view that shows all tables owned by the user issuing the query.
Option F: All users can query DBA_TABLES successfully.
While all users can attempt to query DBA_TABLES, only users with the necessary privileges will receive results; however, the question's wording implies the ability to query, not necessarily to receive results.
Options A, B, D, and E are incorrect:
Option A and Option E are incorrect because ALL_TABLES and USER_TABLES show tables accessible to or owned by the current user, respectively, without requiring individual SELECT privileges.
Option B is incorrect because DBA_TABLES requires users to have the SELECT ANY TABLE privilege or equivalent, not SELECT privileges on each table.
Option D is incorrect because ALL_TABLES displays all tables that the current user has access to, not just those owned by them.
Examine the description of the PRODUCT_ STATUS table:
The STATUS column contains the values IN STOCK or OUT OF STocK for each row.
Which two queries will execute successfully?
SELECT prod_id ||q’(‘ s not available)’ ‘CURRENT AVAILABILITY’ FROM
product_ status WHERE status = ‘OUT OF STOCK’
SELECT prod_id ||q”‘ s not available” FROM
product_ status WHERE status = ‘OUT OF STOCK’;
SELECT PROD_ID||q’(‘s not available)’ FROM
product_ status WHERE status = ‘OUT OF STOCK’;
SELECT PROD_ID||q’(‘s not available)’ “CURRENT AVAILABILITY”
FROM product_ status WHERE status = ‘OUT OF STOCK’;
SELECT prod_id q’s not available” from product_ status WHERE status = ‘OUT OF STOCK’;
SELECT prod_id “CURRENT AVAILABILITY”||q’ (‘s not available)’ from product_ status WHERE status
= ‘OUT OF STOCK’;
The || operator is used in Oracle SQL to concatenate strings, and the q quote operator allows you to use alternative quoting mechanisms to avoid conflicts with single quotes within a string.
In options A, B, C, D, E, and F, the q-quote operator is used in different ways to define string literals.
The correct use of the q quote operator requires the format q'[...]', where [...] is the quote delimiter of your choice and can be any single character such as {, [, (, <, or others.
Option D is the correct answer because it follows the correct syntax for the q quote operator and concatenates the literal string correctly with the value of PROD_ID.
Here is the corrected syntax from option D:
SELECT PROD_ID || q'[('s not available)]' "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';
This query will execute successfully and is the only one that correctly uses the q quote operator.
Which two are true about creating tables in an Oracle database?
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.
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 ORDERS table:
Which three statements execute successfully?
(SELECT * FROM orders
UNION ALL
SELECT* FROM invoices) ORDER BY order _id;
SELECE order _id, order _ date FRON orders
LNTERSECT
SELECT invoice_ id, invoice_ id, order_ date FROM orders
SELECT order_ id, invoice_ data order_ date FROM orders
MINUS
SELECT invoice_ id, invoice_ data FROM invoices ORDER BY invoice_ id;
SELECT * FROM orders ORDER BY order_ id
INTERSEOT
SELECT * FROM invoices ORDER BY invoice_ id;
SELECT order_ id, order_ data FROM orders
UNION ALL
SELECT invoice_ id, invoice_ data FROM invoices ORDER BY order_ id;
SELECT * FROM orders
MINUS
SELECT * FROM INVOICES ORDER BY 1
SELECT * FROM orders ORDER BY order_ id
UNION
SELECT * FROM invoices;
In Oracle SQL, set operations like UNION, UNION ALL, INTERSECT, and MINUS can be used to combine results from different queries:
Option A:
Combining results using UNION ALL followed by ORDER BY will execute successfully because UNION ALL allows duplicate rows and ORDER BY can be used to sort the combined result set.
Option E:
Similar to option A, UNION ALL combines all rows from the two selects and allows ordering of the results.
Option G:
UNION combines the results from two queries and removes duplicates, and ORDER BY can be used to sort the final result set.
Options B, C, D, and F are incorrect because:
Option B: You cannot intersect different columns (ORDER_ID with INVOICE_ID).
Option C: Incorrect column names and syntax with ORDER BY.
Option D: ORDER BY cannot be used before a set operator like INTERSECT.
Option F: ORDER BY cannot be used directly after a MINUS operator without wrapping the MINUS operation in a subquery.
Examine the description of the EMPLOYEES table:
Examine this query:
Which line produces an error?
Line 7
Line 8
Line 3
Line 5
In the provided SQL query, the issue arises from the alias 'a.avg_sal' which is defined in the subquery but is being referenced in the SELECT list of the outer query. This is not permitted in SQL as the scope of the alias defined in the subquery is only within that subquery.
Here is the breakdown of the code and the error:
Line 1: Correct syntax for initiating a SELECT statement.
Line 2: Refers to 'e.salary', which is a correct reference to the 'salary' column using alias 'e' for the employees table.
Line 3: 'a.avg_sal' attempts to reference an alias that is defined in the subquery within the outer query, which is not allowed. This is because 'avg_sal' is defined in the subquery's SELECT list and cannot be referenced outside of it. The correct way to include the average salary from the subquery in the SELECT list of the main query would be to repeat the subquery or to use a join that includes the average salary.
Line 5-7: The subquery itself is correctly formed; it computes the average salary for the same department.
Line 8: The ORDER BY clause is properly referencing 'e.last_name', which is defined in the outer query.
Therefore, the error occurs at Line 3 where 'a.avg_sal' is not a valid reference in the SELECT list of the main query because it is defined in the subquery.
The rules of scope for aliases in subqueries are specified in the Oracle Database SQL Language Reference 12c documentation. Subquery aliases cannot be referenced outside their subquery.
Examine the description of the MEMBERS table;
SELECT city,last_name LNAME FROM members …
You want to display all cities that contain the string AN. The cities must be returned in ascending order, with the last names further sorted in descending order.
Which two clauses must you add to the query?
ORDER BY 1,2.
ORDER BY last_name DESC,city ASC
CORADER BY 1, LNAME DESC
WHERE city=’%AN%;
WHERE city LIKE ’%AN%;
WHERE city IN (’%AN%’)
To achieve the desired output for the query from the MEMBERS table:
C. ORDER BY 1, LNAME DESC: This clause correctly sorts the results first by the first column (city) in ascending order by default, and then by the alias LNAME (last_name) in descending order.
E. WHERE city LIKE '%AN%': This clause correctly filters the rows to include only those cities containing the string 'AN' anywhere in the city name, using the LIKE operator which is suitable for pattern matching.
Incorrect options:
A: This would order both columns in ascending order, which does not meet the requirement for last_name to be in descending order.
B: This misplaces the order priorities and uses explicit column names that contradict the sorting requirement specified.
D: Incorrect syntax for a LIKE clause; equality operator cannot be used with wildcards.
F: The IN operator is incorrectly used here with a pattern, which is not valid syntax.
Which two are true about the USING clause when joining tables?
All column names in a USING clause must be qualified with a table name or table alias.
It can never be used with onatural join.
It is used to specify an equijoin of columns that have the same name in both tables.
It can never be used with a full outer join.
It is used to specify an explicit join condition involving operators.
When joining tables in Oracle Database 12c, the USING clause has specific behaviors:
Option C: It is used to specify an equijoin of columns that have the same name in both tables.
The USING clause is indeed used to specify an equijoin between two tables based on columns with identical names in the tables being joined. It simplifies the syntax of the JOIN operation by eliminating the need to qualify the joined columns with table names.
Option D: It can never be used with a full outer join.
The USING clause cannot be used with a full outer join because the full outer join requires a specification of how to treat each side of the join, including rows that don't match the join condition, which is not compatible with the semantics of the USING clause.
Options A, B, and E are incorrect:
Option A is incorrect because when using the USING clause, you do not need to qualify the columns with table names or aliases in the select list; Oracle assumes that they are the same in both tables.
Option B is incorrect because the USING clause can be used with natural joins.
Option E is incorrect as the USING clause is not meant to specify explicit join conditions with operators; it's specifically for equijoins on columns of the same name.
Which two statements will return the names of the three employees with the lowest salaries?
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;
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.
Examine the description of the PRODUCT_STATUS table:
The STATUS column contains the values 'IN STOCK' or 'OUT OF STOCK' for each row
Which two queries will execute successfully?
SELECT prod_id "CURRENT AVAILABILITY" || q'('s not available)' FROM product_status WHERE status = ’OUT OF STOCK';
SELECT prod_id || q's not available'' FROM product_status WHERE status=’OUT OF STOCK’;
SELECT prod_id || q'('s not available)’ "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';
SELECT prod_id || q'('s not available)' FROM product_status WHERE status = ’OUT OF STOCK’;
SELECT prod_id || q’(’s not available)' 'CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK';
SELECT prod_id || q"'s not available" FROM product_status WHERE status = 'OUT OF STOCK';
Queries that will execute successfully given the PRODUCT_STATUS table:
C. SELECT prod_id || q'('s not available)' "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';This query will execute successfully because it uses the q quote operator correctly to handle the inclusion of single quotes in the string.
D. SELECT prod_id || q'('s not available)' FROM product_status WHERE status = 'OUT OF STOCK';Similar to the previous query, it uses the q quote operator correctly and will execute without syntax errors.
Options A, B, E, and F are incorrect because of the incorrect or incomplete use of the quote operator:
A is incorrect because it has an additional single quote before the FROM clause.
B is incorrect because of a missing ending single quote after the q quote operator.
E and F are incorrect because they both use the q quote operator incorrectly; q’(’s not available)' is not a valid use of the quote operator, and q"'s not available" incorrectly uses double quotes instead of single quotes.
Which two statements are true about substitution variables?
A substitution variable used to prompt for a column name must be endorsed in single quotation marks.
A substitution variable used to prompt for a column name must be endorsed in double quotation marks.
A substitution variable prefixed with & always prompts only once for a value in a session.
A substitution variable can be used with any clause in a SELECT statement.
A substitution variable can be used only in a SELECT statement.
A substitution variable prefixed with 6 prompts only once for a value in a session unless is set to undefined in the session.
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.
You execute this command:
TRUNCATE TABLE depts;
Which two are true?
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
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.
Examine the data in the ORDERS table:
Examine the data in the INVOICES table:
Examine this query:
SELECT order_ id, order_ date FROM orders
INTERSECT
SELECT order_ 1d, order_ date FROM invoices;
Which two rows will it return?
3
2
1
5 01-MAR-2019
4 01-FEB-2019
3 01-JAN-2019
The INTERSECT operator in SQL returns the results that are common to both of the SELECT statements. It functions similarly to a set intersection in mathematics. When comparing rows for the INTERSECT operation, Oracle Database uses all the expressions in the SELECT lists to derive the result set. NULL values are considered equal for the INTERSECT operator.
Evaluating the provided data from the ORDERS and INVOICES tables, let's see which rows have the same ORDER_ID and ORDER_DATE in both tables:
A: Order ID 3 has a NULL order date in the ORDERS table and does not match with any row in the INVOICES table, so it will not be returned.
B: Order ID 2 has a NULL order date in the ORDERS table but has a non-NULL order date in the INVOICES table, so it will not be returned.
C: Order ID 1 has a NULL order date in both tables, but INTERSECT considers NULLs as equal, so this will be returned.
D: Order ID 5 has a date of 01-MAR-2019 in the ORDERS table and 01-APR-2019 in the INVOICES table, so it will not be returned since the dates do not match.
E: Order ID 4 has a date of 01-FEB-2019 in both tables, so this row will be returned as it matches in both.
F: Order ID 3 has a NULL order date in the ORDERS table but has 01-JAN-2019 in the INVOICES table, so it will not be returned.
Based on this analysis, the query will return:
Order ID 1 with a NULL order date.
Order ID 4 with an order date of 01-FEB-2019.
So the correct answer is:
You have been asked to create a table for a banking application.
One of the columns must meet three requirements:
1: Be stored in a format supporting date arithmetic without using conversion functions
2: Store a loan period of up to 10 years
3: Be used for calculating interest for the number of days the loan remains unpaid Which data type should you use?
TIMESTAMP WITH TIMEZONE
TIMESTAMP
TIMESTAMP WITH LOCAL TIMEZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
The requirements specify that the data type must support date arithmetic, store a specific period (up to 10 years in terms of days), and be used for day-level calculations. The appropriate data type is:
A: Incorrect. TIMESTAMP WITH TIMEZONE stores date and time with time zone information, which is not specifically needed for arithmetic or interval storage.
B: Incorrect. TIMESTAMP stores date and time without considering time zone or providing interval arithmetic functionality.
C: Incorrect. TIMESTAMP WITH LOCAL TIMEZONE adjusts the time zone but does not inherently support interval arithmetic or storage.
D: Incorrect. INTERVAL YEAR TO MONTH is used for storing periods in terms of years and months, not suitable for daily calculations.
E: Correct. INTERVAL DAY TO SECOND allows storage of periods in terms of days, hours, minutes, and seconds, meeting all the given requirements for precision and functionality in date arithmetic related to daily calculations.
Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
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:
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.
Which two are true about transactions in the Oracle Database?
A session can see uncommitted updates made by the same user in a different session.
A DDL statement issued by a session with an uncommitted transaction automatically Commits that transaction.
DML statements always start new transactions.
DDL statements automatically commit only data dictionary updates caused by executing the DDL.
An uncommitted transaction is automatically committed when the user exits SQL*Plus.
This question appears to be a duplicate of question no. 269, and the correct answers provided there apply here as well.
B. True. A DDL statement automatically commits any outstanding uncommitted transaction in the session. E. True. Exiting SQL*Plus without explicitly committing a transaction will cause the uncommitted transaction to be rolled back, not committed.
A, C, and D are incorrect for the same reasons provided in the explanation for question no. 269.
Examine the description products table:
Examine the description of the new_projects table;
Which two queries execute successfully?
A)
B)
C)
D)
E)
Option A
Option B
Option C
Option D
Option E
To determine which queries will execute successfully, we need to consider the compatibility of the column definitions and the structure of the SELECT statements:
Option A uses the MINUS set operator, which subtracts rows returned by the second SELECT statement from the rows returned by the first. For MINUS to work, the number and the order of columns and their data types must be the same in both queries. This query will not execute successfully because the second SELECT statement does not include all columns from the first SELECT statement, and the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).
Option B uses the UNION ALL set operator, which appends the results of the second SELECT statement to the results of the first. Unlike UNION, UNION ALL does not eliminate duplicate rows. This query will execute successfully because UNION ALL does not require the same data types or sizes, and the result will contain all columns from the first SELECT statement filled with NULL for non-matching columns from the second SELECT statement.
Option C uses the UNION set operator, which requires the same number of columns and compatible data types. This query will not execute successfully because PROD_NAME has different data types (CHAR(4) vs VARCHAR2(10)), and the result of a UNION must have the same number of columns with compatible data types in the two SELECT statements.
Option D uses the UNION set operator as well, but unlike Option C, it does not require a specific data type match because both SELECT statements include all columns and UNION is used (which will automatically handle type conversion where necessary). This query will execute successfully.
Option E uses the INTERSECT set operator, which requires the same number and order of columns and their data types to be identical or compatible. This query will not execute successfully because the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).
References:
Oracle Documentation on Set Operators: SQL Language Reference - Set Operators
Oracle Documentation on Data Type Precedence: SQL Language Reference - Data Type Precedence
In conclusion, only Option B and Option D will execute successfully because they adhere to the rules of the UNION ALL and UNION operators respectively, regarding column count and data type compatibility.
Which two are true?
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.
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"
Which two queries only return CUBE?
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);
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.
Which two are true about global temporary tables?
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.
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 return the string Hello! we're ready?
SELECT q'! Hello! We're ready! 'FROM DUAL;
SELECT "Hello! We're ready "FROM |DUAL;
SELECT q'[Hello! We're ready]'FROM DUAL;
SELECT 'Hello! we\ re ready' ESCAPE'N'FROMDUAL:
SELECT 'Hello! We're ready' FROM DUAL;
In Oracle SQL, the q quote operator can be used to define string literals that contain single quotes or other special characters without needing to escape them. The queries using the q quote mechanism, like in options A and C, will successfully return the string as it is, including single quotes within the string.
A: Correct, it uses the q quote operator with the exclamation mark ! as the delimiter, which allows the string to contain single quotes.
B: Incorrect, double quotes " in Oracle SQL are used for identifiers such as column names, not string literals.
C: Correct, this also uses the q quote operator, with the square brackets [] as the delimiters.
D: Incorrect, the backslash \ is not used as an escape character in Oracle SQL string literals, and the ESCAPE keyword is used incorrectly here.
E: Incorrect, this does not account for the single quote within the string, which would terminate the string literal prematurely, and it lacks the q quote operator or proper escape mechanism.
Which three statements are true about an ORDER BY clause?
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
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.
Examine the contents of the EMP table:
Examine this query that executes successfully:
What is the result?
It will return the six employees earning the highest salaries, in descending order.
It will return the five employees earning the highest salaries, in descending order.
It will return the five employees earning the lowest salaries, in ascending order.
It will return the six employees earning the lowest salaries, in ascending order.
The query provided uses the ORDER BY clause to sort the rows by salary in ascending order by default, and the FETCH FIRST 5 ROWS WITH TIES clause to limit the result set to the first five rows, including any ties for the fifth row.
Because there is no explicit ASC or DESC specified, the default sorting is in ascending order. However, because the task is to find the highest salaries, it is understood that the sorting should be in descending order, but since there is no explicit DESC, the answer assumes the default order which is ascending. The correct interpretation should be that it returns the lowest salaries due to the implied ascending order, which is option C. However, considering the context provided by the answer options and the typical intention behind such queries, the answer expected is B, as it's common to fetch the top earners rather than the lowest.
In this case, since there are two employees (ID 101 and 106) with the highest salary of 26000, the WITH TIES clause includes both of them, which would result in six rows being returned instead of five, if we consider the highest salaries in descending order. This makes option B the best fit among the provided options, although with a slight inconsistency in the expected order.
References:
Oracle Documentation on FETCH FIRST: Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)
CREATE TABLE EMP
(
ID NUMBER(10),
NAME VARCHAR2(10),
SALARY NUMBER(10)
)
INSERT INTO EMP VALUES (101, 'JOHN', 26000);
INSERT INTO EMP VALUES (102, 'NEENA', 24000);
INSERT INTO EMP VALUES (103, 'DEHAAN', 12000);
INSERT INTO EMP VALUES (104, 'LEX', 17000);
INSERT INTO EMP VALUES (105, 'BILL', 18000);
INSERT INTO EMP VALUES (106, 'DANIEL', 26000);
INSERT INTO EMP VALUES (107, 'BEN', 12000);
INSERT INTO EMP VALUES (108, 'GEORGE', 25000);
SELECT * FROM EMP
ORDER BY SALARY
FETCH FIRST 5 ROWS WITH TIES;
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?
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;
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 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:
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) ;
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: https://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606