dumpstool logo
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.
Which two statements are true about the WHERE and HAVING clauses in a SELECT statement?
The WHERE clause can be used to exclude rows after dividing them into groups
WHERE and HAVING clauses can be used in the same statement only if applied to different table columns.
The HAVING clause can be used with aggregating functions in subqueries.
Aggregating functions and columns used in HAVING clauses must be specified in these SELECT list of a query.
The WHERE clause can be used to exclude rows before dividing them into groups.
In SQL, the WHERE and HAVING clauses are used to filter records; the WHERE clause is applied before grouping the records, while the HAVING clause is used after grouping the records, particularly when using aggregation functions.
Statement D is true because the HAVING clause is used to filter groups based on the result of aggregate functions. Therefore, any column or aggregate function appearing in the HAVING clause must also appear in the SELECT list of the query, unless it is used as part of an aggregate function.
Statement E is true because the WHERE clause is designed to filter rows before they are grouped into aggregate groups in a GROUP BY clause. This is a fundamental aspect of SQL that optimizes query performance by reducing the number of rows to be processed in the aggregate phase.
Statements A, B, and C are incorrect based on the following:
A is incorrect because the WHERE clause does not operate on groups but on individual rows before grouping.
B is misleading; while WHERE and HAVING can be used in the same statement, their usage is not restricted to different columns. They perform different functions (row-level filtering vs. group-level filtering).
C is incorrect because subqueries using aggregate functions typically do not use HAVING clauses; rather, HAVING is used in the outer query to filter the results of aggregates.
Examine 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 this description of the EMP table:
You execute this query:
SELECT deptno AS "departments", SUM (sal) AS "salary"
FROM emp
GROUP | BY 1
HAVING SUM (sal)> 3 000;
What is the result?
only departments where the total salary is greater than 3000, returned in no particular order
all departments and a sum of the salaries of employees with a salary greater than 3000
an error
only departments where the total salary is greater than 3000, ordered by department
The query uses the syntax GROUP | BY 1 which is not correct. The pipe symbol | is not a valid character in the context of the GROUP BY clause. Additionally, when using GROUP BY with a number, it refers to the position of the column in the SELECT list, which should be written without a pipe symbol and correctly as GROUP BY 1. Since the syntax is incorrect, the database engine will return an error.
Examine this query which executes successfully;
Select job,deptno from emp
Union all
Select job,deptno from jobs_history;
What will be the result?
It will return rows from both select statements after eliminating duplicate rows.
It will return rows common to both select statements.
It will return rows both select statements including duplicate rows.
It will return rows that are not common to both select statements.
For the provided UNION ALL query:
Option C: It will return rows from both SELECT statements including duplicate rows.
UNION ALL is used to combine the results of two SELECT statements and does not eliminate duplicates.
Options A, B, and D are incorrect because:
Option A: UNION ALL does not eliminate duplicate rows, unlike UNION.
Option B: This would be true for INTERSECT, not UNION ALL.
Option D: This would be true for EXCEPT or MINUS, not UNION ALL.
Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database?
The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row
A TIMESTAMP data type column contains information about year, month, and day
The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
The CURRENT_TIMESTAMP function returns data without time zone information
A: True. The DBTIMEZONE function returns the database's time zone offset from UTC (Coordinated Universal Time). In Oracle 12c, DBTIMEZONE can return the time zone of the database in terms of a region name or as a numeric offset from UTC. This is stated in the Oracle documentation for managing time zones.
B: True. TIMESTAMP WITH LOCAL TIME ZONE is a data type that adjusts the data stored in the database to the time zone of the session that is querying or inserting the data. When data is stored, Oracle converts it from the session time zone to UTC, and upon retrieval, it converts it back to the session time zone. This is a feature designed to allow the same data to be viewed in different time zones automatically, making it highly useful in global applications.
D: True. SESSIONTIMEZONE function returns the time zone offset of the current session from UTC. This is useful for understanding and managing data conversions in applications that are used across different time zones. The time zone can be displayed as an offset from UTC or as a named region depending on the environment settings.
Examine the 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
What is true about non-equijoin statement performance?
The between condition always performs less well than using the >= and <= conditions.
The Oracle join syntax performs better than the SQL: 1999 compliant ANSI join syntax.
The join syntax used makes no difference to performance.
The between condition always performs better than using the >= and <= conditions.
Table aliases can improve performance.
Performance implications related to non-equijoin SQL statements in Oracle Database are often a topic of optimization:
C. The join syntax used makes no difference to performance: In Oracle Database, the performance of a query involving joins is typically more dependent on factors like the underlying data distribution, indexes, optimizer statistics, and system configuration rather than the syntax (ANSI vs Oracle traditional syntax). The optimizer in Oracle is sophisticated enough to interpret different syntactical expressions of joins and optimize them accordingly.
References:
Oracle Database Performance Tuning Guide 12c, which discusses the impact of different join syntaxes and how Oracle's optimizer handles them.
Choose the best answer.
Examine the description of the EMPLOYEES table:
Which query is valid?
SELECT dept_id, join_date,SUM(salary) FROM employees GROUP BY dept_id, join_date;
SELECT depe_id,join_date,SUM(salary) FROM employees GROUP BY dept_id:
SELECT dept_id,MAX(AVG(salary)) FROM employees GROUP BY dept_id;
SELECT dept_id,AVG(MAX(salary)) FROM employees GROUP BY dapt_id;
In Oracle 12c SQL, the GROUP BY clause is used to arrange identical data into groups with the GROUP BY expression followed by the SELECT statement. The SUM() function is then used to calculate the sum for each grouped record on a specific column, which in this case is the salary column.
Option A is valid because it correctly applies the GROUP BY clause. Both dept_id and join_date are included in the SELECT statement, which is a requirement when using these columns in conjunction with the GROUP BY clause. This means that the query will calculate the sum of salaries for each combination of dept_id and join_date. It adheres to the SQL rule that every item in the SELECT list must be either an aggregate function or appear in the GROUP BY clause.
Option B is invalid due to a typo in SELECT depe_id and also because it ends with a colon rather than a semicolon.
Option C is invalid because you cannot nest aggregate functions like MAX(AVG(salary)) without a subquery.
Option D is invalid for the same reason as option C, where it tries to nest aggregate functions AVG(MAX(salary)), which is not allowed directly in SQL without a subquery.
For further reference, you can consult the Oracle 12c documentation, which provides comprehensive guidelines on how to use the GROUP BY clause and aggregate functions like SUM():
Oracle Database SQL Language Reference, 12c Release 1 (12.1): GROUP BY Clause
Oracle Database SQL Language Reference, 12c Release 1 (12.1): Aggregate Functions
Which two are true about granting privilege on objects?
An object privilege can be granted to a role only by the owner of that object
An object privilege can be granted to other users only by the owner of that object
The owner of an object acquires all object privilege on that object by default
A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table
The WITH GRANT OPTION clause can be used only by DBA users
C. True, the owner of an object automatically has all object privileges for that object by default. This is an inherent property of the object owner in Oracle.D. True, to allow the creation of foreign key constraints that reference a particular table, the owner of the table must grant the REFERENCES privilege on the key columns to other users or roles.
A, B, and E are not correct because: A. Object privileges can be granted to a role by any user with the appropriate permissions, not only by the owner. B. Object privileges can also be granted by users who have been given the privileges with the WITH GRANT OPTION, not just by the owner. E. The WITH GRANT OPTION is not limited to DBA users; it can be used by any user who has the privilege to grant a specific object privilege.
References:
Oracle documentation on object privileges: Oracle Database SQL Language Reference
Oracle documentation on the REFERENCES privilege: Oracle Database Security Guide
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.
Which two will execute successfully?
SELECT COALESCR('DATE', SYSDATE) FROM DUAL;
SELECT NVL('DATE',SYSDATE) FROM DUAL;
SELECT COALESCE(O,SYSDATE) TRCH DUAL;
SELECT NVL('DATE',200) FROM (SELECT NULL AS “DATE” FROM DUAL);
SELECT COALESCE('DATE',SYSDATE) FROM (SELECT NULL AS “DATE” FROM DUAL) ;
D. True. The NVL function can replace a NULL value with a specified value, and it does not require the data types to match exactly, allowing implicit conversion where possible. Here, 'DATE' is a string literal, and 200 is a number, and since the selected column is NULL, NVL will return 200.
The other options are incorrect because the COALESCE function requires all arguments to be of the same data type or at least compatible types that Oracle can implicitly convert. In A and E, the use of COALESCE with a string literal 'DATE' and SYSDATE (which is a date type) is not compatible without explicit conversion. Option C has a typo (TRCH instead of FROM) and is mixing data types incorrectly.
Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER
Statement 1:
SELECT MAX (unit price*quantity) "Maximum Order FROM order items;
Statement 2:
SELECT MAX (unit price*quantity "Maximum order" FROM order items GROUP BY order id;
Which two statements are true?
Statement 2 returns only one row of output.
Both the statement given the same output.
Both statements will return NULL if either UNIT PRICE or QUANTITY contains NULL,
Statement 2 may return multiple rows of output.
Statement 1 returns only one row of output.
Analyzing the given SQL statements on the ORDER_ITEMS table:
D. Statement 2 may return multiple rows of output: Statement 2 groups the results by ORDER_ID, which means it calculates the maximum UNIT_PRICE * QUANTITY for each ORDER_ID, potentially returning multiple rows depending on the number of unique ORDER_IDs in the table.
E. Statement 1 returns only one row of output: Statement 1 computes the maximum product of UNIT_PRICE and QUANTITY across all entries in the ORDER_ITEMS table, returning a single row with the maximum value.
Incorrect options:
A: Since Statement 2 groups by ORDER_ID, it does not necessarily return just one row; it returns one row per ORDER_ID.
B: These statements do not yield the same output; Statement 1 returns a single maximum value, while Statement 2 returns the maximum value per ORDER_ID.
C: If either UNIT_PRICE or QUANTITY is NULL, the product for that row will be NULL, but the MAX function ignores NULL values in its calculation unless all rows are NULL, in which case it returns NULL.
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 SQL statement:
SELECT cust_id, cust_last_name "Last Name
FROM customers
WHERE countryid=10
UNION
SELECT custid CUSTNO, cust_last_name
FROM customers
WHERE countryid=30
Identify three ORDER BY clauses, any one of which can complete the query successfully.
ORDER BY“CUST NO"
ORDER BY 2, cust_id
ORDERBY2, 1
ORDER BY "Last Name"
ORDER BY CUSTNO
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.
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.
In your session, the NLS._DAE_FORMAT is DD- MM- YYYY.There are 86400 seconds in a day.Examine
this result:
DATE
02-JAN-2020
Which statement returns this?
SELECT TO_ CHAR(TO_ DATE(‘29-10-2019’) +INTERVAL ‘2’; MONTH + INTERVAL ‘5’; DAY -
INTERVAL ‘86410’ SECOND, ‘ DD-MON-YYYY’) AS "date"
FROM DUAL;
SELECT TO_ CHAR(TO_ DATE(‘29-10-2019’) + INTERVAL ‘3’ MONTH + INTERVAL ‘7’ DAY -
INTERVAL ‘360’ SECOND, ‘ DD-MON-YYYY’) AS "date"
FROM DUAL;
SELECT To CHAR(TO _DATE(‘29-10-2019’) + INTERVAL ‘2’ NONTH + INTERVAL ‘5’ DAY
INEERVAL ‘120’ SECOND, ‘ DD-MON-YYY) AS "date"
FROM DUAL;
SELECT-TO_CHAR(TO _DATE(‘29-10-2019’+ INTERVAL ‘2’ MONTH+INTERVAL ‘6’ DAYINTERVAL
‘120’ SECOND, ‘DD-MON-YY’) AS "daTe"
FROM DUAL;
SELECT-TO_CHAR(TO _DATE(‘29-10-2019’+ INTERVAL ‘2’ MONTH+INTERVAL ‘4’ DAYINTERVAL
‘120’ SECOND, ‘DD-MON-YY’) AS "daTe"
FROM DUAL;
To calculate the date from a given base date with intervals, Oracle allows you to add or subtract intervals from dates. Since the NLS_DATE_FORMAT is set to DD-MM-YYYY, the output is expected to be in that format.
Option B seems to calculate a date that is 3 months and 7 days ahead of October 29, 2019, and then subtracts 360 seconds (which is 6 minutes), resulting in a time that is still within the same day.
Here's how the calculation in option B would work out:
Start date: 29-10-2019
Add 3 months: 29-01-2020
Add 7 days: 05-02-2020
Subtract 360 seconds: Since it's only a few minutes, the date remains 05-02-2020.
However, this does not match the provided result of 02-JAN-2020. We would need to consider the exact amount of time being subtracted or added to find the correct answer.
But upon reviewing the options, they all have various syntax errors such as a missing TO_CHAR function, incorrect quotes, and date formats not matching the session's NLS_DATE_FORMAT. Therefore, we would need to correct these issues to find the right answer.
Which two are true about constraints?
Constraints are enforced only during INSERT operations.
A column with a foreign key constraint can never contain a null value.
All constraints can be defined at the table or column level.
A constraint can be disabled even if the constrained column contains data.
A column with a UNIQUE constraint can contain a NULL value.
A. False. Constraints are enforced during INSERT and UPDATE operations, and by the nature of their definition, they impact DELETE operations as well (in the case of referential constraints).
B. False. A column with a foreign key constraint can contain a NULL value unless it is also constrained to be NOT NULL.
C. False. Not all constraints can be defined at the column level. For example, some constraints such as FOREIGN KEY constraints are more commonly defined at the table level.
D. True. A constraint can be disabled regardless of whether the constrained column contains data. However, re-enabling the constraint requires that all data satisfy the constraint rules.
E. True. A column with a UNIQUE constraint can indeed contain a NULL value, as NULL is considered not equal to any value, including itself. This means that multiple rows with NULL values do not violate the UNIQUE constraint.
References:
Oracle Documentation on Constraints: https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52271
Oracle Documentation on Enabling and Disabling Constraints: https://docs.oracle.com/database/121/ADMIN/clustrct.htm#ADMIN13508
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;
Examining the execution of SELECT statements in Oracle SQL requires understanding correct syntax, especially regarding aliases and string literals:
Option A: Incorrect. Although the syntax might seem plausible, using DATE as an alias without quotes can lead to ambiguity because DATE is a reserved keyword in Oracle SQL. This might cause an error or unexpected behavior unless it is enclosed in double quotes.
Option B: Incorrect. Single quotes are used for string literals in SQL, not for aliasing column names. Using single quotes for aliasing, as in 'CUSTOMER-ID', 'DUES', is syntactically incorrect and will result in an error.
Option C: Correct. This statement uses valid syntax with no reserved keywords as aliases and no misuse of string literal notation. Aliases are provided without quotes, which is acceptable as long as they are not reserved words or contain special characters.
Option D: Correct. It correctly uses double quotes for aliasing, which is necessary when using reserved words like "DATE" or special characters. This is a proper use of SQL syntax for aliasing in Oracle.
Option E: Incorrect. This statement attempts to use aliases with hyphens without enclosing them in double quotes, which leads to syntax errors. Also, customer id seems to be missing an underscore or another connector to properly reference a column name, indicating a typo or error.
For these queries, ensuring correct alias syntax and avoiding reserved keywords without appropriate quoting are key elements for successful execution.
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.
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 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 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 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
Whith three statements are true about built in data types?
A VARCHAR2 blank pads column values only if the data stored is non numeric and contains no special characlers
A BFILE stores unstructured binary data in operating systerm files
A CHAR column definition does not require the length to be specified
The default length for a CHAR column is always one character
A VARCHAR2 column definition does not require the length to be specified
A BLOB stores unstructured binary data within the database
The true statements about built-in data types in Oracle are:
B: A BFILE is a data type in Oracle that allows for a read-only link to binary files stored outside the database in the operating system. This is correct as per Oracle's documentation.
D: The default length for a CHAR column, when not specified, is one character. This is according to the Oracle SQL standard.
F: A BLOB is used for storing binary data within the Oracle database, allowing for storage of large amounts of unstructured binary data.
The incorrect options are:
A: A VARCHAR2 column does not blank-pad values; it is CHAR that may blank-pad to the fixed length.
C: A CHAR column requires a length specification, although if omitted, the default is one character.
E: A VARCHAR2 column requires a length specification; without it, the statement will fail.
References:
Oracle Documentation on Data Types: Data Types
Oracle Documentation on LOBs: LOBs
Oracle Documentation on Character Data Types: Character Data Types
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.
You execute these commands:
SQL> DEFINE hiredate = ’01-APR -2011’;
SQL> SELECT employee_id, first_name, salary FROM employees WHERE hire date > &hiredate AND manager_id >&mgr_id;
For which substitution variables will you be prompted?
none
&hiredate and &mgr_id
only &hiredate
only &mgr_id
D. only &mgr_id: Since the hiredate variable is already defined before the SELECT statement, you will not be prompted for &hiredate again. However, &mgr_id has not been defined, so you will be prompted for this substitution variable.
Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?
Only the primary key can be defined at the column and table level.
The foreign key columns and parent table primary key columns must have the same names.
It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.
A table can have only one primary key but multiple foreign keys.
Primary key and foreign key constraints can be defined at both the column and table level.
A table can have only one primary key and one foreign key.
It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted
Primary and foreign key constraints are foundational to relational database design:
Option A: Incorrect. Both primary and foreign key constraints can be defined at the column or table level.
Option B: Incorrect. Foreign key columns do not need to have the same names as the corresponding primary key columns in the parent table. They must be of the same data type and size.
Option C: Incorrect. If a foreign key constraint is enforced without ON DELETE CASCADE, deleting the parent row will either prevent the delete (due to constraint) or require a prior deletion or update of the child rows.
Option D: Correct. A table can indeed have only one primary key, which uniquely identifies each row, but it can have multiple foreign keys linking to primary keys of different tables.
Option E: Correct. Both types of keys can be defined at either level, providing flexibility in how constraints are applied and enforced.
Option F: Incorrect. A table can have multiple foreign keys as stated, each referencing a different parent table.
Option G: Correct. If the ON DELETE CASCADE option is set for a foreign key, then deleting a parent row will automatically delete the corresponding child rows, maintaining referential integrity.
The STORES table has a column START_DATE of data type DATE, containing the datethe row was inserted.
You only want to display details of rows where START_DATEis within the last 25 months.which WHERE clause can be used?
WHERE TO_NUMBER(start_date - SYSDATE)<=25
WHERE ADD_MONTHS (start date , 25)<= SYSDATE
WHERE MONTHS_BETWEEN(SYSDATE, start_date)<=25
WHERE MONTHS_BETWEEN (start_date, SYSDATE)<=25
For filtering rows based on the START_DATE within the last 25 months:
C. WHERE MONTHS_BETWEEN(SYSDATE, start_date)<=25: This WHERE clause correctly uses the MONTHS_BETWEEN function to compute the number of months between the current date (SYSDATE) and the START_DATE column. It checks if the difference is 25 months or fewer, correctly applying the condition.
Incorrect options:
A: The TO_NUMBER usage is incorrect for date arithmetic in this context.
B: ADD_MONTHS used in this manner would incorrectly calculate dates in the future rather than comparing past dates.
D: The MONTHS_BETWEEN function should have SYSDATE as the first argument to calculate past months correctly.
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.
Evaluate the following SQL statement
SQL>SELECT promo_id, prom _category FROM promotions
WHERE promo_category=’Internet’ ORDER BY promo_id
UNION
SELECT promo_id, promo_category FROM Pomotions
WHERE promo_category = ‘TV’
UNION
SELECT promoid, promocategory FROM promotions WHERE promo category=’Radio’
Which statement is true regarding the outcome of the above query?
It executes successfully and displays rows in the descend ignore of PROMO CATEGORY.
It produces an error because positional, notation cannot be used in the ORDER BY clause with SBT operators.
It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.
It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.
C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement: The ORDER BY clause in a compound query using UNION should be placed at the very end of the final SELECT statement. Since it's located with the first SELECT, it will be ignored.
Examine this partial query:
SELECT ch.channel_type, t.month, co.country_code, SUM(s.amount_sold) SALES
FROM sales s, times t, channels ch, countries co
WHERE s.time_ id = t.time id
AND s.country_ id = co. country id
AND s. channel id = ch.channel id
AND ch.channel type IN ('Direct Sales', 'Internet')
AND t.month IN ('2000-09', '2000-10')
AND co.country code IN ('GB', 'US')
Examine this output:
Which GROUP BY clause must be added so the query returns the results shown?
GROUP BY ch.channel_type, t.month, co.country code;
GROUP BY ch.channel_type,ROLLUP (t month, co. country_ code) ;
GROUP BY CUBE (ch. channel_ type, t .month, co. country code);
GROUP BYch. channel_ type, t.month,ROLIUP (co. country_ code) ;
A. True. The GROUP BY clause needs to include all non-aggregated columns from the SELECT list to provide the correct grouping for the output. The output shown in the image indicates that the data is grouped by channel_type, month, and country_code.
B, C, and D are incorrect because:
B includes a ROLLUP which would introduce subtotals that are not reflected in the output shown.
C specifies a CUBE, which would produce all possible combinations of groupings including the grand total, which is not shown in the output.
D specifies a ROLLUP on country_code only, which would not correctly group by channel_type and month.
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.
Choose two
Examine the description of the PRODUCT DETALS table:
PRODUCT_ID can be assigned the PEIMARY KEY constraint.
EXPIRY_DATE cannot be used in arithmetic expressions.
EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it
PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it
PRODUCT_PRICE contains the value zero by default if no value is assigned to it.
PRODUCT_NAME cannot contain duplicate values.
A. PRODUCT_ID can be assigned the PRIMARY KEY constraint.
In Oracle Database 12c, a PRIMARY KEY constraint is a combination of a NOT NULL constraint and a unique constraint. It ensures that the data contained in a column, or a group of columns, is unique among all the rows in the table and not null. Given the PRODUCT_ID is marked as NOT NULL, it is a candidate for being a primary key because we can assume that it is intended to uniquely identify each product in the table.
Which three actions can you perform on an existing table containing date?
Add a new column as the table's first column.
Define a default value that is automatically inserted into a column containing nulls.
Add a new NOT NULL Column with a DEFAULT value.
Change a DATE Column containing data to a NUMBER data type.
Increase the width of a numeric column.
Change the default value of a column.
C: True. You can add a new NOT NULL column to an existing table, provided you specify a DEFAULT value for the column. This ensures that existing rows in the table can be updated with a default value, thus maintaining the NOT NULL constraint integrity.
E: True. It is permissible to increase the width of a numeric column, provided the change does not truncate existing data. This change is generally safe as it allows for greater values or more precision without affecting existing data.
F: True. The default value of a column can be changed. This action affects subsequent insert operations but does not alter the values of existing rows. Changing the default is useful for adapting tables to evolving business rules without impacting existing data.
Which two tasks require subqueries?
Display the total number of products supplied by supplier 102 which have a product status of obsolete.
Display suppliers whose PROD_LIST_PRICE is less than 1000.
Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.
Display the minimum PROD_LIST_PRICE for each product status.
Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable.
C: True. To display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE, you would need to use a subquery to first calculate the average PROD_LIST_PRICE and then use that result to compare each product’s list price to the average.
E: True. Displaying products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products and whose status is orderable would require a subquery. The subquery would be used to determine the average PROD_LIST_PRICE, and then this average would be used in the outer query to filter the products accordingly.
Subqueries are necessary when the computation of a value relies on an aggregate or a result that must be obtained separately from the main query, and cannot be derived in a single level of query execution.
References:Oracle's SQL documentation provides guidelines for using subqueries in scenarios where an inner query's result is needed to complete the processing of an outer query.
Which two statements are true about a self join?
The join key column must have an index.
It can be a left outer join.
It must be a full outer join.
It can be an inner join.
It must be an equijoin.
A self join is a regular join, but the table is joined with itself. This kind of join can take the form of an inner join, a left outer join, or even a full outer join depending on the requirement.
A. The join key column must have an index. (Incorrect)
While indexes can improve the performance of joins by reducing the cost of the lookup operations, they are not a requirement for a self join. A self join can be performed with or without an index on the join key columns.
B. It can be a left outer join. (Correct)
A self join can indeed be a left outer join. This is useful when you want to include all records from the 'left' side of the join (the table itself), even if the join condition does not find any matching record on the 'right' side (the table itself again).
Examine the description of the ENPLYEES table:
Which two queries return all rows for employees whose salary is greater than the average salary in their department?
SELECT ”
FROM employees
WHERE salary > ANY
SELECT AVG (salary)
EROM employees
GROUP BY department_ id);
SELECT
FROM employees
WHERE salary > AVG (salary) OVER (PARTITION BY department _ id);
SELECT”
FROM employees e1
WHERE salary >!
SELECT AVG (salary)
FROM employees e2
WHERE e1. Department _id = e2, department_ id
SELECT.
FROM
SELECT e.", AVG (salary) OVER (PARTITION BY department id) avg_ sal
FROM employees e
WHERE salary > avg_ sal;
SELECT”
FROM employees
WHERE salary >
( SELECT AVG
(salary) FROM
employees
GROUP BY department _ id
To return all rows for employees whose salary is greater than the average salary in their department, you would use either a subquery or an analytic function:
Option B:
SELECT ... FROM employees WHERE salary > AVG(salary) OVER (PARTITION BY department_id);
This uses the window function AVG with PARTITION BY to calculate the average salary per department, and it compares each employee’s salary to this average.
Option C:
SELECT ... FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);
This correlated subquery compares each employee's salary to the average salary in their department using a subquery to calculate the average salary for that department.
Options A, D, and E are incorrect because:
Option A: The use of ANY with the subquery does not ensure comparison with the average salary of their respective department.
Option D: This is syntactically incorrect; the subquery alias avg_sal is not accessible outside the subquery.
Option E: The subquery does not correlate with the outer query to ensure that each employee's salary is compared to the average salary of their respective department.
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.
Examine the description of the EMPLOYEES table:
Which statement will execute successfully, returning distinct employees with non-null first names?
SELECT DISTINCT * FROM employees WHERE first_ name IS NOT NULL;
SELECT first_ name, DISTNCT last_ name FROM employees WHERE first_ name IS NOT NULL;
SELECT Distinct * FROM employees WHERE first_ name < > NULL;
SELECT first_ name, DISTINCT last_ name FROM employees WHERE first_ name < > NULL;
Which four statements are true about constraints on Oracle tables?
A Column can have only one CHECK Constraint.
A NOT NULL Constraint can be defined at the table level.
A UNIQUE constraint permits NULLS.
A PRIMARY KEY Constraint can be added after a table has been created and populated.
A CHECK Constraint can refer to values in other rows.
A UNIQUE Constraint can use a pre-existing index on the constrained column or columns.
A FOREIGN KEY Column can contain NULLS.
C: True. A UNIQUE constraint in Oracle SQL allows for the inclusion of NULL values; specifically, it permits multiple NULLs in a column or set of columns but ensures that all non-NULL values are unique.
D: True. A PRIMARY KEY constraint can indeed be added to a table after it has been created and even after it has been populated, as long as the existing data does not violate the primary key constraint rules (i.e., all values must be unique and not NULL).
F: True. A UNIQUE constraint can utilize a pre-existing index on the columns it covers. If a suitable index already exists, Oracle can use this index to enforce the constraint, optimizing performance and resource utilization.
G: True. Columns that are part of a FOREIGN KEY constraint can contain NULL values. This is permissible under SQL standards and Oracle implementation, as a NULL foreign key value is considered to not refer to any row in the referenced table and thus does not violate referential integrity.
Which three items does a direction of a relationship contain?
an attribute
a cardinality
label
an optionality
a unique identifier
an entity
In data modeling and database design, the direction of a relationship typically includes:
Option B: a cardinality
Cardinality refers to the numerical relationships between two entities, indicating the number of instances of one entity that can or must be associated with each instance of another.
Option C: label
A label is often used to describe the role or purpose of the relationship in a way that clarifies the nature of the interaction between entities.
Option D: an optionality
Optionality indicates whether or not a relationship is mandatory or optional; in other words, it shows if an instance of an entity must be related to an instance of another entity or if it can exist without such a relationship.
Options A, E, and F are not part of the direction of a relationship:
Option A: An attribute is a property or characteristic of an entity, not the direction of a relationship.
Option E: A unique identifier uniquely identifies each instance of an entity, not a relationship's direction.
Option F: An entity is a thing with distinct and independent existence in a database, usually mapped to a table, not a part of the direction of a relationship.
Examine this partial command:
Which two clauses are required for this command to execute successfully?
the DEFAULT DIRECTORY clause
the REJECT LIMIT clause
the LOCATION clause
the ACCESS PARAMETERS clause
the access driver TYPE clause
In Oracle Database 12c, when creating an external table using the CREATE TABLE ... ORGANIZATION EXTERNAL statement, there are certain clauses that are mandatory for the command to execute successfully.
Statement C, the LOCATION clause, is required. The LOCATION clause specifies one or more external data source locations, typically a file or a directory that the external table will read from. Without this, Oracle would not know where to find the external data for the table.
Statement E, the access driver TYPE clause, is also required. The access driver tells Oracle how to interpret the format of the data files. The most common access driver is ORACLE_LOADER, which allows the reading of data files in a format compatible with the SQL*Loader utility. Another option could be ORACLE_DATAPUMP, which reads data in a Data Pump format.
Statements A, B, and D are not strictly required for the command to execute successfully, although they are often used in practice:
A, the DEFAULT DIRECTORY clause, is not mandatory if you have specified the full path in the LOCATION clause, but it is a best practice to use it to avoid hard-coding directory paths in the LOCATION clause.
B, the REJECT LIMIT clause, is optional and specifies the maximum number of errors to allow during the loading of data. If not specified, the default is 0, meaning the load will fail upon the first error encountered.
D, the ACCESS PARAMETERS clause, is where one would specify parameters for the access driver, such as field delimiters and record formatting details. While it is common to include this clause to define the format of the external data, it is not absolutely required for the command to execute; defaults would be used if this clause is omitted.
For reference, you can find more details in the Oracle Database SQL Language Reference for version 12c, under the CREATE TABLE statement for external tables.
Which two statements will do an implicit conversion?
SELECT * FROM customers WHERE customer_ id = 0001 ;
SELECT * FROM customers WHERE customer id = ‘0001’;
SELECT * FROM customers WHERE insert_ date = DATE ‘2019-01-01’;
SELECT * FROM customers WHERE insert date =’01-JAN-19’
SELECT * FROM customers WHERE TO_ CHAR (customer_ id) =’0001’;
A. True. This statement will work if customer_id is a character data type in the database. Oracle will implicitly convert the numeric literal 0001 to a string to compare with customer_id.
D. True. If the insert_date is of type DATE and the NLS_DATE_FORMAT matches 'DD-MON-YY', Oracle will implicitly convert the string literal '01-JAN-19' to a date type to compare with insert_date.
B is incorrect because if customer_id is a numeric data type, there is no need for implicit conversion. C is incorrect because using the DATE literal DATE '2019-01-01' is an explicit conversion. E is incorrect because TO_CHAR(customer_id) is an explicit conversion from a numeric to a string data type.
Which two statements are true about outer Joins?
The outer join operator (+) can be used on both sides of the join condition in an outer join.
An outer join is used to retrieve only the rows that do not meet the join condition.
The IN operator cannot be used in a condition that Involves an outer join.
A condition representing an outer join cannot be linked to another condition using the or logical operator.
The outer join operator (+) is used next to the column of the table without the matching rows.
Regarding the usage and rules of outer joins in SQL, specifically Oracle SQL:
D. A condition representing an outer join cannot be linked to another condition using the OR logical operator: In SQL, when using the Oracle-specific (+) notation for outer joins, it is not permitted to combine this condition with another using the OR operator. The use of (+) imposes restrictions to ensure the join logic is correctly interpreted.
E. The outer join operator (+) is used next to the column of the table without the matching rows: The (+) symbol in Oracle's SQL syntax denotes the table that should include "null" where data does not exist to satisfy the join condition, effectively including rows that do not have a match in the joined table.
Incorrect options:
A: The (+) operator cannot be used on both sides of a condition within the same join; it can only appear on one side to define which side of the join is the outer part.
B: An outer join is used to retrieve all rows from one table and the matched rows from the other table; it does not solely retrieve rows that do not meet the join condition.
C: The IN operator can be used in conditions involving an outer join, although specific rules and behaviors need to be considered depending on the SQL version and implementation.
What is true about non-equijoin statement performance?
The between condition always performs less well than using the >= and <= conditions.
The Oracle join syntax performs better than the SQL: 1999 compliant ANSI join syntax.
The join syntax used makes no difference to performance.
The between condition always performs better than using the >= and <= conditions.
Table aliases can improve performance.
When comparing the performance of different SQL join types and conditions, Oracle SQL optimizations generally ensure that performance is consistent across different syntactical forms of expressing the same logic:
Option A: False. The BETWEEN condition does not inherently perform worse than using >= and <=. Oracle's optimizer typically evaluates these conditions similarly, optimizing the underlying execution based on the data distribution and available indexes.
Option B: False. Oracle's optimizer is designed to handle both Oracle-specific join syntax and ANSI join syntax with equal competence. Performance differences would typically be negligible because the optimizer translates both into an optimal execution plan based on the same underlying mechanisms.
Option C: True. The join syntax used (whether Oracle's traditional syntax or ANSI standard syntax) generally does not affect the performance. Oracle's query optimizer is adept at translating different syntaxes into efficient execution plans.
Option D: False. The assertion that BETWEEN always performs better than >= and <= is incorrect. Performance depends more on factors like indexing, the specific data and distribution, and the Oracle optimizer's capabilities than on the mere choice of syntax.
Option E: False. While table aliases help improve query readability and can prevent ambiguity in SQL queries, they do not inherently improve performance. Their use is a best practice for code clarity and maintenance, not performance enhancement.
Examine the description of the BRICKS table;
Examine the description of the BRICKS_STAGE table;
Which two queries execute successfully?
SELECT shape,color,weight from bricks
MINUS
SELECT * FROM bricks_stage;
SELECT shape,color FROM bricks
MINUS
SELECT WEIGHT,color FROM bricks_stage;
select * from bricks
MINUS
select * from bricks_stage;
SELECT shape,color FROM bricks
MINUS
SELECT color,shape FROM bricks_stage;
SELECT brick_id,shape FROM bricks
MINUS
SELECT WEIGHT,COLOR from bricks_stage;
In Oracle SQL, when using the set operators like MINUS, the number of columns and their data types in the SELECT statements must match in sequence.
A. This query will not execute successfully because the SELECT * FROM bricks_stage will return all columns from the BRICKS_STAGE table, which are WEIGHT, SHAPE, and COLOR, but the first SELECT statement specifies only SHAPE and COLOR. The order and number of columns must match.
B. This query will not execute successfully. The SELECT statements have a different number of columns, and the data types of the columns in the same positions do not match between the two queries. The first column in the first SELECT is SHAPE (VARCHAR2), and in the second SELECT, it is WEIGHT (NUMBER).
C. This query will execute successfully. The SELECT * from both tables will ensure that the number of columns and their data types are the same, as SELECT * selects all columns from the table. As long as the two tables have the same column order and data types for those columns, the query will execute.
D. This query will not execute successfully. Even though the columns are of the same data types, their order in the SELECT statements must match for the set operator to work. The order of SHAPE and COLOR is switched between the two queries.
E. This query will not execute successfully. The number of columns in the SELECT statements is the same, but their data types do not match between the two queries. BRICK_ID (NUMBER) in the first query does not match WEIGHT (NUMBER) in the second, and SHAPE (VARCHAR2) does not match COLOR (VARCHAR2).
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Combining Queries with Set Operators"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "MINUS"
Examine this query which executes successfully:
SELECT job, deptno FROM emp
UNION ALL
SELECT job, deptno FROM jobs_ history;
What will be the result?
It will return rows common to both SELECT statements.
It will return rows from both SELECT statements after eliminating duplicate rows.
It will return rows that are not common to both SELECT statements.
It will return rows from both SELECT statements including duplicate rows.
The UNION ALL operator in SQL is used to combine the results of two or more SELECT statements:
Option D: It will return rows from both SELECT statements including duplicate rows.
UNION ALL does not eliminate duplicates; it simply combines all rows from the queries provided.
Options A, B, and C are incorrect as they describe behaviors of other set operators:
Option A: Describes the behavior of INTERSECT, not UNION ALL.
Option B: Describes the behavior of UNION, not UNION ALL.
Option C: Describes the behavior of EXCEPT or MINUS, not UNION ALL.
In the PROMOTIONS table, the PROMO_BEGTN_DATE column is of data type DATE and the default date format is DD-MON-RR.
Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query?
TO_NUMBER(PROMO_BEGIN_DATE)-5 will return number
TO_DATE(PROMO_BEGIN_DATE * 5) will return a date
PROMO_BEGIN_DATE-SYSDATE will return a number.
PROMO_BEGIN_DATE-5 will return a date.
PROMO_BEGIN_DATE-SYSDATE will return an error.
A. This statement is incorrect because TO_NUMBER expects a character string as an argument, not a date. Directly converting a date to a number without an intermediate conversion to a character string would result in an error. B. This statement is incorrect. Multiplying a date by a number does not make sense in SQL, and attempting to convert such an expression to a date will also result in an error. C. This statement is correct. Subtracting two dates in Oracle SQL results in the number of days between those dates, hence the result is a number. D. This statement is correct. Subtracting a number from a date in Oracle SQL will subtract that number of days from the date, returning another date. E. This statement is incorrect. As stated in C, subtracting a date from SYSDATE correctly returns the number of days between those two dates, not an error.
These concepts are explained in the Oracle Database SQL Language Reference, which details date arithmetic in SQL.
Which two 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.
Which two are true about self joins?
They are always equijoins.
They require the NOT EXISTS operator in the join condition.
They have no join condition.
They can use INNER JOIN and LEFT JOIN.
They require table aliases.
They require the EXISTS opnrator in the join condition.
Self joins in Oracle Database 12c SQL have these characteristics:
Option D: They can use INNER JOIN and LEFT JOIN.
Self joins can indeed use various join types, including inner and left outer joins. A self join is a regular join, but the table is joined with itself.
Option E: They require table aliases.
When a table is joined to itself, aliases are required to distinguish between the different instances of the same table within the same query.
Options A, B, C, and F are incorrect:
Option A is incorrect because self joins can be non-equijoins as well.
Option B is incorrect because self joins do not require the NOT EXISTS operator. They may require a condition, but NOT EXISTS is not a necessity.
Option C is incorrect because a join condition is needed to relate the two instances of the same table in a self join.
Option F is incorrect for the same reason as B; the EXISTS operator is not a requirement for self joins.
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.
Which three statements are true about the Oracle join and ANSI Join syntax?
The Oracle join syntax only supports right outer joins,
The Oracle join syntax supports creation of a Cartesian product of two tables.
The SQL:1999 compliant ANSI join syntax supports natural joins.
The Oracle join syntax supports natural joins.
The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
The Oracle join syntax performs less well than the SQL:1999 compliant ANSI Join Answer.
Regarding Oracle join and ANSI join syntax:
B. The Oracle join syntax supports the creation of a Cartesian product of two tables. This is true. In Oracle, if you list tables in the FROM clause without a join condition, it creates a Cartesian product.
C. The SQL:1999 compliant ANSI join syntax supports natural joins. This is true. ANSI syntax supports natural joins, which join tables based on columns with the same names in the joined tables.
F. The SQL:1999 compliant ANSI join syntax supports the creation of a Cartesian product of two tables. This is true. The ANSI standard allows for Cartesian products when tables are listed in the FROM clause without a join condition.
Options A, D, E, and G are incorrect:
A is incorrect because the Oracle join syntax supports all types of joins, including right outer joins.
D is incorrect because Oracle's proprietary join syntax does not use the term "natural join."
E is incorrect because there is no inherent performance difference between Oracle join syntax and ANSI join syntax; performance depends on how the query is written and how the database optimizer handles it.
G is incorrect for the same reason as E.
Examine the description of the PRODUCT_ DETAILS table:
Which two statements are true?
PRODUCT_ PRICE can be used in an arithmetic expression even if it has no value stored in it.
PRODUCT_ ID can be assigned the PRIMARY KEY constraint.
EXPIRY_ DATE cannot be used in arithmetic expressions.
EXPIRY_ DATE contains the SYSDATE by default if no date is assigned to it.
PRODUCT_ PRICE contains the value zero by default if no value is assigned to it.
PRODUCT_ NAME cannot contain duplicate values.
In reference to the attributes of the PRODUCT_DETAILS table:
A. This statement is false. If PRODUCT_PRICE has no value (i.e., it is NULL), it cannot be used directly in arithmetic expressions because any operation with NULL results in NULL.
B. This statement is true. PRODUCT_ID has the NOT NULL constraint and is of type NUMBER, making it eligible to be a PRIMARY KEY. Primary keys require all values to be unique and not null.
C. This statement is true. EXPIRY_DATE is of type DATE, and it cannot be used in arithmetic expressions directly without a date function that operates on dates.
D. This statement is false. By default, columns of type DATE do not have a default value unless explicitly assigned using the DEFAULT keyword in the column definition.
E. This statement is false. By default, numeric columns do not have a default value unless specified with the DEFAULT keyword.
F. This statement is false. The statement about PRODUCT_NAME not containing duplicate values would be true if it were a UNIQUE or PRIMARY KEY, but there is no such constraint indicated in the provided table description.
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.
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 three are true about subqueries?
A subquery can be used in a WHERE clause.
A subquery can be used in a HAVING clause.
=ANY can only evaluate the argument against a subcjuery if it returns two or more values.
A subquery cannot be used in a FROM clause.
< any returns true if the argument is less than the lowest value returned by the subquery.
A subquery cannot be used in the select list.
About the roles and behavior of subqueries in SQL:
A. A subquery can be used in a WHERE clause: Subqueries are often used in WHERE clauses to filter rows based on a condition evaluated against a set of returned values.
B. A subquery can be used in a HAVING clause: Similar to WHERE, subqueries can be used in HAVING clauses to filter groups based on aggregate conditions.
D. <ANY returns true if the argument is less than the highest value returned by the subquery: The
Incorrect options:
C: =ANY evaluates true if the argument matches any single value returned by the subquery, irrespective of the number of values.
E: A subquery can indeed be used in a FROM clause, known as a derived table or inline view.
F: G: A subquery can be used in a SELECT list, particularly when the subquery is designed to return a single value (scalar subquery).
Examine this business rule:
Each student can work on multiple projects and each project can have multiple students.
You must design an Entity Relationship(ER) model for optimal data storage and allow for generating reports in this format:
Which two statements are true?
An associative table must be created with a composite key of STUDENT_ID and PROJRCT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.
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.
The ER must have a many to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships.
STUDENT ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
Based on the business rule, the true statements regarding the Entity Relationship (ER) model are:
A. 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. This is true because, in a many-to-many relationship, an associative (junction) table is used to maintain the associations between two entities.
D. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships. This is correct because many-to-many relationships are resolved by introducing an associative table that breaks it down into two 1-to-many relationships.
Options B, C, and E are incorrect:
B is incorrect because PROJECT_ID should not be a foreign key in the STUDENTS entity in a many-to-many relationship.
C is incorrect as the rule indicates a many-to-many relationship, not 1-to-many.
E is incorrect because STUDENT_ID should not be a foreign key in the PROJECTS entity in a many-to-many relationship.
Examine this query:
SELECT employee_id, first_name, salary
FROM employees
WHERE hiredate > 61*
Which two methods should yours to prevent prompting for hire date value when this queries executed?
Execute the SET VERIFY ON command before executing the query.
Execute the SET VERIFY OFF command before executing the query.
Store the query in a script and pass the substitution value to the script when executing it.
Replace 's1' with &1'in the query:
Use the UNDEFINE command before executing the query.
Use the DEFINE command before executing the query
To prevent the prompting for a substitution variable value when executing a query:
B. Execute the SET VERIFY OFF command before executing the query: This command turns off the substitution variable prompting, which means that SQL*Plus will not prompt for a value and will use whatever value is currently set for that variable.
F. Use the DEFINE command before executing the query: By using the DEFINE command to set a value for the variable before the query execution, you ensure that SQL*Plus uses this predefined value instead of prompting for it.
References:
Oracle SQL*Plus User's Guide and Reference, which discusses how to manage substitution variables.
Which two statements cause changes to the data dictionary?
DELETE FROM scott. emp;
GRANT UPDATE ON scott. emp TO fin manager;
AITER SESSION set NLs. _DATE FORMAT = 'DD/MM/YYYY';
TRUNCATE TABLE emp:
SELECT * FROM user_ tab._ privs;
The data dictionary is a read-only set of tables that provides information about the database. Certain operations that modify the database structure will cause changes to the data dictionary:
Option B: GRANT UPDATE ON scott.emp TO fin_manager;
Granting privileges on a table will change the data dictionary as it records the new privilege.
Option D: TRUNCATE TABLE emp;
Truncating a table affects the data dictionary because it removes all rows from a table and may also reset storage parameters.
Options A, C, and E do not cause changes to the data dictionary:
Option A is incorrect because the DELETE command modifies data, not the data dictionary structure.
Option C is incorrect because altering a session parameter does not change the data dictionary; it is a temporary change for the session.
Option E is incorrect because a SELECT query does not change the database, it just retrieves information.
Examine this Statement which returns the name of each employee and their manager,
SELECT e.last name AS emp,,m.last_name AS mgr
FROM employees e JOIN managers m
ON e.manager_ id = m. employee_ id ORDER BY emp;
You want to extend the query to include employees with no manager. What must you add before JOIN to do this?
CROSS
FULL OUTER
LEFT OUTER
RIGHT OUTER
To include employees with no manager in the query results, a LEFT OUTER JOIN should be used. This type of join returns all records from the left table (employees), and the matched records from the right table (managers). The result is NULL from the right side if there is no match.
Here's the modified query:
SELECT e.last_name AS emp, m.last_name AS mgr FROM employees e LEFT OUTER JOIN managers m ON e.manager_id = m.employee_id ORDER BY emp;
This ensures that even if an employee does not have a manager (i.e., e.manager_id is NULL or there is no corresponding m.employee_id), that employee will still be included in the results.
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 statements are true about the ORDER BY clause?
Numeric values are displayed in descending order if they have decimal positions.
Only columns that are specified in the SELECT list can be used in the ORDER BY cause.
NULLS are not included in the sort operation.
Column aliases can be used In the ORDER BY cause.
Ina character sort, the values are case-sensitive.
The ORDER BY clause in Oracle SQL is used to sort the result set of a query by one or more columns, and it comes with its own set of rules and behaviors:
D. Column aliases can be used In the ORDER BY clause: True, column aliases that are specified in the SELECT clause can be used in the ORDER BY clause to refer to the columns to sort the results by.
E. In a character sort, the values are case-sensitive: Oracle sorts results in case-sensitive order by default when using the ORDER BY clause with character columns, unless otherwise specified by the NLS_SORT parameter.
References:
Oracle Database SQL Language Reference 12c, which provides details on sorting query results using ORDER BY.
Which three actions can you perform by using the ORACLE DATAPUMP access driver?
Create a directory object for an external table.
Read data from an external table and load it into a table in the database.
Query data from an external table.
Create a directory object for a flat file.
Execute DML statements on an external table.
Read data from a table in the database and insert it into an external table.
The Oracle Data Pump access driver allows for specific actions with external tables:
B. Read data from an external table and load it into a table in the database. Data Pump can be used to efficiently transfer data between external tables and internal database tables.
C. Query data from an external table. The Data Pump access driver supports querying data directly from external tables.
F. Read data from a table in the database and insert it into an external table. The Data Pump can also export data from database tables to external table formats.
Options A, D, and E are incorrect:
A and D are incorrect as the creation of a directory object is not specific to the Data Pump access driver but is a general external table requirement.
E is incorrect because DML operations directly on external tables are not supported; they are read-only.
Examine the description of the CUSTONERS table
CUSTON is the PRIMARY KEY.
You must derermine if any customers’derails have entered more than once using a different
costno,by listing duplicate name
Which two methode can you use to get the requlred resuit?
RIGHT OUTER JOIN with seif join
FULL OUTER JOIN with seif join
SUBQUERY
seif join
LEFT OUTER JOIN with seif join
To determine if customer details have been entered more than once using a different custno, the following methods can be used:
SUBQUERY: A subquery can be used to find duplicate names by grouping the names and having a count greater than one.
SELECT custname FROM customers GROUP BY custname HAVING COUNT(custname) > 1;
Self Join: A self join can compare rows within the same table to find duplicates in the custname column, excluding matches with the same custno.
SELECT c1.custname FROM customers c1 JOIN customers c2 ON c1.custname = c2.custname AND c1.custno != c2.custno;
These methods allow us to find duplicates in the custname column regardless of the custno. Options A, B, and E are not applicable methods for finding duplicates in this context.
References:
Oracle Documentation on Group By: Group By
Oracle Documentation on Joins: Joins
Examine the description or the CUSTOMERS table:
For Customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?
SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers
WHERE cust_income_level != NULL AND cust_credit_level != NULL;
SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers
WHERE cust_income_level <> NULL AND due_amount <> NULL;
SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers
WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;
SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers
WHERE cust_income_level IS NOT NULL AND due_amount IS NOT NULL;
SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers
WHERE cust_income_level != NULL AND due_amount != NULL;
In Oracle SQL, the correct way to check for non-null values is to use the IS NOT NULL condition. Using != NULL or <> NULL is incorrect because NULL represents the absence of any value, and you cannot use equality or inequality operators to check for NULL.
C. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL; This query will correctly filter out any customers with a NULL income level or credit limit and then calculate the due amount as 5% of their credit limit for the remaining customers.
References:
Oracle Database SQL Language Reference 12c, especially sections on conditions and expressions that deal with NULL values.
Which three are key components of an Entity Relationship Model?
a table
an attribute
a unique identifier
an activity
a relationship
an entity
Key components of an Entity-Relationship Model (ERM) include:
B. an attribute: Attributes are properties or characteristics of an entity, such as a person's name, date of birth, etc., and are essential in describing the data aspects of an entity in an ER model.
E. a relationship: Relationships describe how entities interact with each other within the database structure, such as a customer placing an order.
F. an entity: Entities are the key components of an ER model, representing objects or things within the domain that have a distinct existence, like 'Customer' or 'Order'.
Incorrect options:
A: A table is a database structure used to implement an entity in relational databases, not a component of the ER model itself.
C: While unique identifiers are crucial in database implementation (typically as primary keys), they are a specific attribute type, not a general component of ER models.
D: An activity is not a component of an ER model; activities relate more to process models or behavioral models in systems design.
Examine this statement which executes successfully:
Which statement will violate the CHECK constraint?
UPDATE emp80
SET department_id=90
WHERE department_id=80;
DELETE FROM emp80
WHERE department_id=90;
SELECT *
FROM emp80
WHERE department_id=80；
SELECT *
FROM emp80
WHERE department_id=90;
The CHECK constraint is generally used to limit the range of values that can be placed in a column. If a CHECK constraint exists on the department_id to only allow values such as 80 (assuming from the context provided):
A. UPDATE emp80 SET department_id=90 WHERE department_id=80: This UPDATE statement attempts to set the department_id to 90 for rows where it is currently 80. If there is a CHECK constraint preventing department_id from being anything other than 80, this statement would violate that constraint.
Incorrect options:
B: DELETE operations do not affect CHECK constraints as they do not involve modifying or setting column values.
C and D: SELECT statements do not modify data and thus cannot violate data integrity constraints like CHECK constraints.
Which two are true about the MERGE statement?
The WHEN NOT MATCHED clause can be used to specify the deletions to be performed.
The WHEN NOT MATCHED clause can be used to specify the inserts to be performed.
The WHEN MATCHED clause can be used to specify the inserts to be performed.
The WHEN NOT MATCHED clause can be used to specify the updates to be performed.
The WHEN MATCHED clause can be used to specify the updates to be performed.
The correct answers regarding the MERGE statement are:
B. The WHEN NOT MATCHED clause can be used to specify the inserts to be performed. This is true. When a row from the source does not match any row in the target, the WHEN NOT MATCHED clause is where you specify the insert operation.
E. The WHEN MATCHED clause can be used to specify the updates to be performed. This is true as well. The WHEN MATCHED clause is where you specify the update (or delete) operation to be performed when the source and target rows match.
Options A, C, and D are incorrect:
A is incorrect because WHEN NOT MATCHED does not handle deletions, it is for inserts.
C is incorrect as inserts are not specified in the WHEN MATCHED clause but in the WHEN NOT MATCHED clause.
D is incorrect because updates are specified in the WHEN MATCHED clause, not the WHEN NOT MATCHED clause.
An Oracle database server session has an uncommitted transaction in progress which updated 5000 rows
in a table.
In which three situations does the transact ion complete thereby committing the updates?
When the session logs out is successfully
When a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT
When a CREATE INDEX statement is executed successfully in same session
When a COMMIT statement is issued by the same user from another session in the same database instance
When a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session
When a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user, then issues a COMMIT
For situations where the transaction would complete by committing the updates:
A. When the session logs out successfully: When a user session logs out, Oracle automatically commits any outstanding transactions.
C. When a CREATE INDEX statement is executed successfully in the same session: Most DDL statements, including CREATE INDEX, cause an implicit commit before and after they are executed.
F. When a DBA issues a successful SHUTDOWN TRANSACTIONAL statement: This type of shutdown ensures that active transactions are either committed or rolled back. If a COMMIT is then issued explicitly, it would be redundant but emphasizes the transaction completion.
Incorrect options:
B: SHUTDOWN IMMEDIATE will roll back transactions, not commit them.
D: A COMMIT in one session cannot affect the transaction state of another session; each session is isolated in terms of transaction management.
E: If a CREATE TABLE AS SELECT statement executes unsuccessfully, no implicit commit is performed; the statement failure means the transaction state remains unchanged.
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.
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.
Examine these statements which execute successfully:
ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24 MI: SS’
ALTER SESSION SET TIME_ ZONE = ‘-5:00’;
SELECT DBTIMEZONE, SYSDATE FROM DUAL
Examine the result:
If LOCALTIMESTAMP was selected at the same time what would it return?
11-JUL-2019 6,00,00,00000000 AM – 05:00
11-JUL-2019 11,00,00,00000000 AM
11-JUL-2019 6,00,00,000000 AM
11-JUL-2019 11,00,00,000000AM -05:00
The LOCALTIMESTAMP function in Oracle Database returns the current date and time in the session time zone. Given that the session time zone was set to -5:00 with the ALTER SESSION SET TIME_ZONE command, LOCALTIMESTAMP will return the date and time adjusted to this session time zone.
The SYSDATE function, on the other hand, returns the current date and time from the operating system of the database server, which is not affected by the session time zone setting. In the result shown, SYSDATE returns 11-JUL-2019 11:00:00, which reflects the system time zone, not the session time zone.
The time difference between the DBTIMEZONE (+00:00) and the session time zone (-5:00) is 5 hours. Since SYSDATE shows the time as 11:00:00 in the system time zone, the LOCALTIMESTAMP adjusted to the session time zone would show the time as 06:00:00.
The correct answer is A: 11-JUL-2019 6:00:00.00000000 AM – 05:00. This represents the local timestamp in the session time zone, which is 5 hours behind the DBTIMEZONE.
Please note that the exact format returned by LOCALTIMESTAMP includes the fractional seconds up to 9 decimal places, and the time zone offset is specified as part of the timestamp.
Which two are true about the precedence of opertors and condtions
+ (addition) has a higher order of precedence than * (mliplpition)
NOT has a higher order of precedence than AND and OR in a condition.
AND and OR have the same order of precedence in a condition
Operators are evaluated before conditions.
|| has a higher order of precedence than +(addition)
Regarding the precedence of operators and conditions in Oracle Database 12c:
Option B: NOT has a higher order of precedence than AND and OR in a condition.
In logical operations, NOT is evaluated first, followed by AND and then OR.
Option E: || has a higher order of precedence than +(addition).
The string concatenation operator || has a higher precedence than the arithmetic + operator.
Options A, C, and D are incorrect because:
Option A: Multiplication (*) has a higher precedence than addition (+).
Option C: AND has a higher precedence than OR.
Option D: Conditions (which may contain operators) are evaluated according to the rules of operator precedence.
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 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 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 conditional INSERT ALL?
Each row returned by the subquery can be inserted into only a single target table.
It cannot have an ELSE clause.
The total number of rows inserted is always equal to the number of rows returned by the subquery
A single WHEN condition can be used for multiple INTO clauses.
Each WHEN condition is tested for each row returned by the subquery.
For conditional INSERT ALL in Oracle Database 12c:
D. A single WHEN condition can be used for multiple INTO clauses. This is true. A single WHEN condition in a multi-table insert can direct the insertion of a single row source into multiple target tables.
E. Each WHEN condition is tested for each row returned by the subquery. True, each row from the subquery is evaluated against each WHEN condition to determine into which table(s) the row should be inserted.
Options A, B, and C are incorrect:
A is incorrect because a row can indeed be directed into multiple tables based on the conditions.
B is incorrect as INSERT ALL can include an ELSE clause to handle rows that do not meet any of the specified conditions.
C is incorrect because not every row from the subquery necessarily results in a row insertion; it depends on the conditions being met.
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.
Which two statements are true about truncate and delete?
the result of a delete can be undone by issuing a rollback
delete can use a where clause to determine which row(s) should be removed.
TRUNCATE can use a where clause to determine which row(s) should be removed.
truncate leavers any indexes on the table in an UNUSABLE STATE.
the result of a truncate can be undone by issuing a ROLLBACK.
In the case of SQL commands TRUNCATE and DELETE:
A. the result of a delete can be undone by issuing a rollback: DELETE is a DML operation that affects rows individually and can be rolled back if it is performed within a transaction.
B. delete can use a where clause to determine which row(s) should be removed: DELETE operation allows the use of a WHERE clause to specify which rows should be deleted based on certain conditions.
Incorrect options are:
C: TRUNCATE does not support the use of a WHERE clause. It is designed to remove all rows from a table swiftly and cannot be conditional.
D: TRUNCATE does not leave indexes in an unusable state; it simply removes all rows.
E: TRUNCATE is a DDL command and its operation typically cannot be rolled back in many SQL database systems, including Oracle.
Which three statements about roles are true?
Roles are assigned to roles using the ALTER ROLE Statement
A role is a named group of related privileges that can only be assigned to a user
Roles are assigned to users using the ALTER USER statement
A single role can be assigned to multiple users.
A single user can be assigned multiple roles
Privileges are assigned to a role using the ALTER ROLE statement.
Privileges are assigned to a role using the GRANT statement.
Roles are named collections of privileges in Oracle databases.
A. False. Roles cannot be assigned to other roles using the ALTER ROLE statement.
B. False. Roles can be assigned to both users and other roles.
C. True. Roles are assigned to users using the ALTER USER statement, but this is not the only method.
D. True. A single role can be assigned to multiple users, simplifying the management of user privileges.
E. True. A single user can be assigned multiple roles.
F. False. Privileges are not assigned to a role using the ALTER ROLE statement.
G. True. Privileges are assigned to a role using the GRANT statement.
Which three statements are true regarding single row subqueries?
They must be placed on the left side of the comparison operator or condition.
They must return a row to prevent errors in the SQL statement.
A SQL statement may have multiple single row subquery blocks.
They can be used in the HAVING clause.
They must be placed on the right side of the comparison operator or condition.
They can be used in the clause.
C: True. A SQL statement may include multiple single row subqueries in different parts of the statement, such as in the SELECT list, WHERE clause, or HAVING clause. Each subquery must independently satisfy the requirement of returning a single row to avoid runtime errors.
D: True. Single row subqueries can be used in the HAVING clause. This allows for filtering groups based on conditions evaluated against individual or aggregated values returned by the subquery. The subquery must return a single value to be valid in this context.
E: True. Single row subqueries are often placed on the right side of the comparison operator in a SQL condition. This positioning is typical because the left side often references a column or an expression related to the main query, while the subquery on the right side dynamically provides a value for comparison.
Examine this statement:
SELECT1 AS id,‘ John’ AS first_name, NULL AS commission FROM dual
INTERSECT
SELECT 1,’John’ null FROM dual ORDER BY 3;
What is returned upon execution?[
2 rows
0 rows
An error
1 ROW
Regarding the provided SQL INTERSECT query:
D. 1 ROW: The INTERSECT operation will compare the two SELECT statements and return rows that are identical between them. Both queries are designed to return the same values ('1' for the ID, 'John' for the name, and NULL for the commission), hence one row that is identical between the two datasets will be returned.
Incorrect options:
A: Only one identical row exists between the two datasets.
B: There is an identical row; thus, it is not zero.
C: There is no error in the syntax or execution of the query.
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.
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.
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 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 statements are true about external tables?
Indexes can be created on them.
You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command.
DML statements cannot be used on them.
Their data can be retrieved by using only SQL or PL/SQL.
Their metadata and actual data are both stored outside the database.
For external tables in Oracle:
C. DML statements cannot be used on them: External tables are read-only in Oracle. They allow access to data in external sources as if it were within a table in the database, but they do not support DML operations (INSERT, UPDATE, DELETE).
D. Their data can be retrieved by using only SQL or PL/SQL: You can query the data in external tables using SQL or PL/SQL, just as you would with regular database tables.
Incorrect options are:
A: You cannot create indexes directly on external tables.
B: External tables cannot be populated using the CREATE TABLE AS SELECT command since they are primarily meant for accessing data stored outside the database.
E: Only the metadata of an external tables is stored in the database; the actual data remains in external files.
Which three statements are true about performing DML operations on a view with no Instead of triggers defined?
WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
Insert statements can always be done on a table through a view.
Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.
Delete statements can always be done on a table tough a view.
Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
When performing DML operations on a view without INSTEAD OF triggers:
Option C: 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.
This is true because the view would not provide values for NOT NULL columns without defaults, leading to an error.
Option 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.
Using DISTINCT in the view’s defining query can make the view non-updatable, as it may aggregate multiple rows into one.
Option E: Delete statements can always be done on a table through a view.
Deletes through a view are typically unrestricted as long as the view does not involve aggregates, DISTINCT, or similar constructs that would logically preclude determining which underlying rows should be deleted.
Examine the description of the PROMTIONS table:
You want to display the unique promotion costs in each promotion category.
Which two queries can be used?
SELECT promo_cost, | pxomo_category FROM promotions ORDER BY 1;
SELECT promo_category, DISTINCT promo_cost PROM promotions ORDER BY 2:
SELECT DISTINCT promo_category ||'has’|| promo_cost AS COSTS FROM promotions ORDER BY 1;
SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
SELECT DISTINCT promo_cost ||' in' II DISTINCT promo_category FROM promotions ORDER BY 1;
To display unique promotion costs in each promotion category, the correct queries that can be used are:
C. SELECT DISTINCT promo_category ||' has '|| promo_cost AS COSTS FROM promotions ORDER BY 1;This query concatenates the promo_category with the literal ' has ' and promo_cost, giving a unique string for each combination of promo_category and promo_cost, which is what we are interested in when we want to list unique costs per category.
D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;This query selects distinct combinations of promo_category and promo_cost, which is exactly what's required to display unique promotion costs in each category.
Options A, B, and E are incorrect:
A is incorrect because it does not use the DISTINCT keyword to ensure uniqueness.
B has incorrect syntax; the DISTINCT keyword should appear directly after SELECT and applies to all columns, not just one.
E is syntactically incorrect and would not execute because the DISTINCT keyword is not used correctly. It should not appear twice in the SELECT clause.
Which three are true about privileges and roles?
A role is owned by the user who created it.
System privileges always set privileges for an entire database.
All roles are owned by the SYS schema.
A role can contain a combination of several privileges and roles.
A user has all object privileges for every object in their schema by default.
PUBLIC can be revoked from a user.
PUBLIC acts as a default role granted to every user in a database
Roles and privileges in Oracle manage access and capabilities within the database:
Option A: False. Roles are not "owned" in the traditional sense by the user who created them. They exist independently within the Oracle database and are assigned to users.
Option B: False. System privileges can be very granular, affecting specific types of operations or database objects, not always the entire database.
Option C: False. Roles are not owned by the SYS schema but are managed by database security and can be created by any user with sufficient privileges.
Option D: True. A role can indeed contain a combination of several privileges, including other roles, allowing for flexible and layered security configurations.
Option E: True. By default, a user has all object privileges for objects they own (i.e., objects in their schema).
Option F: False. PUBLIC is a special designation that applies to all users; individual privileges granted to PUBLIC cannot be revoked from a single user without revoking them from all users.
Option G: True. PUBLIC is a role granted by default to every user in an Oracle database, providing basic privileges necessary for general usability of the database.
TESTED 23 Dec 2024
Copyright © 2014-2024 DumpsTool. All Rights Reserved