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"
Which three statements are true about a self join?
It must be an inner join.
It must be an equijoin.
The query must use two different aliases for the table.
The on clause can be used.
The on clause must be used.
It can be an outer join.
C. True, when performing a self join, aliases are used to differentiate the same table in different contexts within the same query. This helps in comparing different rows within the same table as if they were in two separate tables.D. True, the ON clause can be used in self joins to specify the conditions on which the join is based. This is a standard method for specifying join conditions in SQL.F. True, a self join can be an outer join, allowing for the inclusion of rows that do not have a matching row in the same table according to the join condition. This enhances the flexibility of data analysis and reporting.
References:
Oracle documentation on joins: Oracle Database SQL Language Reference
Details on self joins and outer joins: Oracle Joins Tutorial
Examine the description of the SALES table:
The SALES table has 5,000 rows.
Examine this statement:
CREATE TABLE sales1 (prod id, cust_id, quantity_sold, price)
AS
SELECT product_id, customer_id, quantity_sold, price
FROM sales
WHERE 1=1
Which two statements are true?
SALES1 is created with 1 row.
SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table.
SALES1 Is created with 5,000 rows.
SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.
When creating a new table with a query, the statements that are true are:
A. SALES1 is created with 1 row.This is incorrect actually; the given query with WHERE 1=1 will retrieve all rows from the sales table, not just one row. Therefore, SALES1 will be created with the same number of rows as in the SALES table, assuming there are no WHERE clause conditions limiting the rows.
D. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.This is true. When a table is created using a CREATE TABLE AS SELECT statement, the NOT NULL constraints on the columns in the selected columns are preserved in the new table.
Options B and C are incorrect:
B is incorrect because the primary key and unique constraints are not carried over to the new table when using the CREATE TABLE AS SELECT syntax.
C is incorrect in the context of this scenario. However, if interpreted as a standalone statement (ignoring A), C would be the correct description of the outcome since WHERE 1=1 will not filter out any rows.
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 performing Data Manipulation Language (DML) operations on a view In an Oracle Database?
Insert statements can always be done on a table through a view.
The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
Views cannot be used to query rows from an underlying table if the table has a PRIPOARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.
Views cannot be used to add on modify rows in an underlying table if the defining query of the view contains aggregating functions.
Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
When performing DML operations on a view, certain restrictions apply:
D. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword: If the defining query of a view contains the DISTINCT keyword, it cannot be used for certain DML operations, such as INSERT and UPDATE, because the set of rows it represents is not directly modifiable.
E. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions: Aggregating functions create a result that summarises multiple rows and cannot be reversed to point to a single row for modification.
F. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view: If a column with a NOT NULL constraint is not included in the view's defining query, and it does not have a default value, it is not possible to insert through the view because it would violate the NOT NULL constraint.
References:
Oracle Database SQL Language Reference 12c, particularly the sections on views and the restrictions on DML operations through views.
Which three statements are true about a self join?
It must be an inner join.
It can be an outer join.
The ON clause must be used.
It must be an equijoin.
The query must use two different aliases for the table.
The ON clause can be used.
A self-join is used to join a table to itself, and here's why the selected answers are correct:
Option B: It can be an outer join.A self-join can indeed be either an inner or an outer join, allowing for more flexibility in how records are matched and returned, especially useful in hierarchical or sequential data scenarios.
Option E: The query must use two different aliases for the table.When performing a self-join, aliases are necessary to distinguish between the different instances of the same table in the query.
Option F: The ON clause can be used.In SQL, the ON clause specifies the conditions that match rows in a self-join, offering a clear and structured way to define how the join works.
Other options are not universally true:
Option A: It must be an inner join. Incorrect because, as explained, outer joins are also possible.
Option C: The ON clause must be used. Incorrect because the WHERE clause might also be used to specify the join condition.
Option D: It must be an equijoin. Incorrect because non-equijoins (like non-equality comparisons) can also be used in self-joins.
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 the description of the transactions table:
Which two SQL statements execute successfully?
SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount+100 "DUES" from transactions;
SELECT customer_id AS 'CUSTOMER-ID',transaction_date AS DATE, amount+100 'DUES' from transactions;
SELECT customer_id CUSTID, transaction_date TRANS_DATE，amount+100 DUES FROM transactions;
SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM transactions;
SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 "DUES AMOUNT" FROM transactions;
A. True, this statement will execute successfully. In Oracle SQL, column aliases can be given in double quotes which allows the use of special characters like a hyphen. Additionally, you can perform arithmetic operations such as amount+100 directly in the SELECT clause to return increased values of amount.
C. True, this statement is also valid. It does not use any special characters or reserved keywords as aliases that would require double quotes. The arithmetic operation is the same as in option A, which is permissible.
For B, D, and E, the reasons for failure are: B. In Oracle SQL, single quotes are used for string literals, not for column aliases. Therefore, using single quotes around 'CUSTOMER-ID' and 'DUES' will result in an error. D. The word "DATE" is a reserved keyword in Oracle SQL. When used as a column alias without double quotes, it will lead to an error. In this statement, although "DATE" is in double quotes, it's a best practice to avoid using reserved keywords as aliases. E. There are syntax errors in the statement. "customer id" should be "customer_id", and the alias "CUSTOMER-ID" should be enclosed in double quotes if special characters are used. Also, "DUES AMOUNT" as an alias should be in double quotes to be valid because it contains a space.
References:
Oracle documentation on column aliases: Oracle Database SQL Language Reference
Oracle reserved words: Oracle Database SQL Language Reserved Words
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 this SQL statement:
DELETE FROM employees e
WHERE EXISTS
(SELECT'dummy'
FROM emp_history
WHERE employee_id = e.employee_id)
Which two are true?
The subquery is executed for every row in the EMPLOYEES table.
The subquery is not a correlated subquery.
The subquery is executed before the DELETE statement is executed.
All existing rows in the EMPLOYEE table are deleted.
The DELETE statement executes successfully even if the subquery selects multiple rows.
The provided DELETE statement uses a correlated subquery to determine which rows should be deleted from the EMPLOYEES table.
A. The subquery is indeed executed for every row in the EMPLOYEES table. This is because it references e.employee_id, which is a column from the outer query, making it a correlated subquery.
B. The subquery is a correlated subquery, as explained above.
C. The subquery is executed for each row, not before the DELETE statement.
D. Not all existing rows in the EMPLOYEES table are deleted, only those that have a corresponding employee_id in the EMP_HISTORY table.
E. The DELETE statement executes successfully even if the subquery selects multiple rows because the EXISTS condition only checks for the presence of rows, not their count.
References:
Oracle Database SQL Language Reference 12c Release 1 (12.1), DELETE Statement
Oracle Database SQL Language Reference 12c Release 1 (12.1), Subquery Factoring
Oracle Database SQL Language Reference 12c Release 1 (12.1), Correlated Subqueries
Which two are true about external tables that use the ORACLE _DATAPUMP access driver?
Creating an external table creates a directory object.
When creating an external table, data can be selected only from a table whose rows are stored in database blocks.
When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks.
Creating an external table creates a dump file that can be used by an external table in the same or a different database.
Creating an external table creates a dump file that can be used only by an external table in the same database.
External tables using the ORACLE_DATAPUMP access driver have specific characteristics:
C. Creating an external table using the ORACLE_DATAPUMP access driver allows you to select data from other tables, including another external table or a regular table whose rows are stored in database blocks.
A, B, D, and E are incorrect. Specifically:
A is incorrect because creating an external table does not automatically create a directory object; the directory object must exist prior to or be created separately.
B is incorrect as it limits the creation to tables stored in database blocks, which is not a restriction.
D is incorrect because creating an external table does not create a dump file; it reads from an existing dump file created by Data Pump.
E is also incorrect because the dump file is not created as part of the external table creation process.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CREATE TABLE" for External Tables
Which two statements are true regarding non equijoins?
The ON clause can be used.
The USING clause can be used.
The SQL:1999 compliant ANSI join syntax must be used.
Table aliases must be used.
The Oracle join syntax can be used.
Non-equi joins are joins where the join condition is based on something other than equality. In Oracle SQL, you can perform non-equi joins using various clauses:
A. The ON clause can be used: True, the ON clause can specify any condition for the join, including non-equijoins. It is not limited to equijoins.
E. The Oracle join syntax can be used: The traditional Oracle join syntax, which uses the WHERE clause to specify the join condition, can be used for all types of joins, including non-equijoins.
References:
Oracle Database SQL Language Reference 12c, especially the sections on join clauses including the ON clause and Oracle's proprietary join syntax.
Which two queries only return CUBE?
SELECT shape FROM bricks JOIN boxes ON weight >= min_weight AND weight < max_weight;
SELECT shape FROM bricks JOIN boxes ON weight > min_weight;
SELECT shape FROM bricks JOIN boxes ON weight BETWEEN min_weight AND max_weight;
SELECT shape FROM bricks JOIN boxes ON weight < max_weight;
SELECT shape FROM bricks JOIN boxes ON NOT (weight > max_weight);
Based on the table structure given in the image, to return the value 'CUBE' from the 'bricks' table when joined with 'boxes', the condition must ensure that the weight of the bricks is within the allowed weight range specified in the 'boxes' table for a 'SMALL' box size.
A. True. Since MAX_WEIGHT is 0, a comparison using >= min_weight AND weight < max_weight will only return rows where the weight is less than 0, which is impossible for actual weight values, suggesting there might be a mistake in the data provided or the comparison logic.
E. True. NOT (weight > max_weight) effectively translates to 'where weight is less than or equal to max_weight'. However, since MAX_WEIGHT is 0, this condition would only be true if the weight is not greater than 0, which can only happen if the weight is 0 or less. This seems to indicate an anomaly where either the data is incorrect, or the condition is meant to handle a case where the weight is zero or possibly a negative placeholder value.
Both B and D will potentially return more than just 'CUBE' if there are bricks with weights greater than MIN_WEIGHT. C is incorrect because BETWEEN is inclusive, and there are no weights that are both greater than or equal to MIN_WEIGHT and less than or equal to MAX_WEIGHT when MAX_WEIGHT is 0.
Which two are true about scalar subquery expressions?
You cannot correlate them with a table in the parent statement
You can use them as a default value for a column.
.You must enclose them in parentheses.
They can return at most one row.
They can return two columns.
Scalar subquery expressions in Oracle SQL have specific rules:
Option C: You must enclose them in parentheses.
Scalar subqueries must be enclosed in parentheses. This is a requirement for syntax clarity and to distinguish the subquery from the rest of the SQL statement.
Option D: They can return at most one row.
Scalar subqueries are designed to return exactly one row containing one column. If a scalar subquery returns more than one row, Oracle will throw an error, ensuring that the subquery either returns a single value or no value (NULL).
Options A, B, and E are incorrect based on Oracle SQL functionalities:
Option A is incorrect because scalar subqueries can indeed be correlated with the parent query.
Option B is true but not in the context of default constraints for table columns in the CREATE TABLE statement.
Option E is incorrect because scalar subqueries can only return a single column by definition.
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.
Examine the description of the CUSTOMERS table:
You want to display details of all customers who reside in cities starting with the letter D followed by at least two character.
Which query can be used?
SELECT * FROM customers WHERE city ='D_%';
SELECT * FROM customers WHERE city ='%D_';
SELECT * FROM customers WHERE city LIKE'D %';
SELECT * FROM customers WHERE city LIKE'D_';
The LIKE operator is used in SQL to search for a specified pattern in a column. To find all customers residing in cities starting with 'D' followed by at least two characters, you need to use the LIKE operator with the appropriate wildcard pattern:
A. SELECT * FROM customers WHERE city LIKE 'D_%'; The underscore () wildcard character in SQL represents a single character. The percent (%) wildcard character represents zero or more characters. So 'D%' will match any city starting with 'D' followed by at least one character (as there is one underscore).
References:
Oracle Database SQL Language Reference 12c, specifically the section on pattern matching with the LIKE condition.
Examine the description of the PRODUCT_INFORMATION table:
SELECT (COUNT(list_price) FROM Product_intormation WHERE list_price=NULL;
SELECT count(nvl( list_price,0)) FROM product_information WHERE list_price is null;
SELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price is null.
BELECT COUNT(list_price) FROM product_information where list_price is NULL;
In SQL, when you want to count occurrences of null values using the COUNT function, you must remember that COUNT ignores null values. So, if you want to count rows with null list_price, you have to replace nulls with some value that can be counted. This is what the NVL function does. It replaces a null value with a specified value, in this case, 0.
A, C, and D options attempt to count list_price directly where it is null, but this will always result in a count of zero because COUNT does not count nulls. Option B correctly uses the NVL function to convert null list_price values to 0, which can then be counted. The WHERE list_price IS NULL clause ensures that only rows with null list_price are considered.
The SQL documentation confirms that COUNT does not include nulls in its count and NVL is used to substitute a value for nulls in an expression. So option B will give us the correct count of rows with a null list_price.
Examine these statements executed in a single Oracle session:
CREATE TABLE product (pcode NUMBER(2),pname VARCHAR2(20));
INSERT INTO product VALUES(1,'pen');
INSERT INTO product VALUES (2,'pencil');
INSERT INTO product VALUES(3,'fountain pen');
SAVEPOINT a;
UPDATE product SET pcode=10 WHERE pcode =1;
COMMIT;
DELETE FROM product WHERE pcode =2;
SAVEPOINT b;
UPDATE product SET pcode=30 WHERE pcode =3;
SAVEPOINT c;
DELETE FROM product WHERE pcode =10;
ROLLBACK TO SAVEPOINT b;
COMMIT;
Which three statements are true？
The code for pen is 10.
There is no row containing fountain pen.
There is no row containing pencil.
The code for pen is 1.
The code for fountain pen is 3
There is no row containing pen
After creation and initial inserts, the pcode for 'pen' is updated to 10, and then committed.
The 'pencil' row is deleted and not yet committed.
A savepoint b is set after the deletion of the 'pencil' row.
The 'fountain pen' pcode is updated to 30, followed by setting savepoint c.
The 'pen' row (now with pcode 10) is deleted.
A rollback to savepoint b reverts the deletion of 'pen' and the update to 'fountain pen', but not the deletion of 'pencil', which was committed earlier due to the scope of the savepoint.
Therefore, after the final commit:
A: The code for 'pen' is 10, since the update was committed and the subsequent delete was rolled back.
C: There is no row containing 'pencil' because its deletion was committed.
F: There is a row containing 'pen' because the deletion was rolled back to savepoint b which was set after the deletion of 'pencil'.
Which two statements are true about the data dictionary?
Views with the prefix dba_ display only metadata for objects in the SYS schema.
Views with the prefix all_ display metadata for objects to which the current user has access.
The data dictionary is accessible when the database is closed.
Views with the prefix all_, dba_ and useb_ are not all available for every type of metadata.
The data dictionary does not store metadata in tables.
The data dictionary contains metadata (data about data) about the database and its objects.
A. False. DBA_ views display metadata for objects accessible to the database administrator, not only for the SYS schema.
B. True. ALL_ views display information about all the objects that the current user has access to. This does not necessarily mean the user has privileges on these objects, only that the user can see them.
C. False. The data dictionary is not accessible when the database is closed because it requires the database to be open in order to access the system tables.
D. True. Not all types of metadata are available in all three prefixes (ALL_, DBA_, USER_). Some specific metadata might only be available in one or two of these view types.
E. False. The data dictionary stores metadata in tables. The various views (ALL_, DBA_, USER_, etc.) provide different perspectives on this data.
References:
Oracle Documentation on Data Dictionary and Dynamic Performance Views: https://docs.oracle.com/cd/B28359_01/server.111/b28310/datadict.htm
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.
Examine the data in the CUST NAME column of the CUSTOMERS table:
CUST_NAME
------------------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two WHERE clauses give the required result?
WHERE INITCAP (SUBSTR(cust_name, INSTR(cust_name,'') +1)) IN ('MC%','Mc%)
WHERE UPPER (SUBSTR(cust_name, INSTR(cust_name, '') +1)) LIKE UPPER('MC%')
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,'') +1)) LIKE'Mc%'
WHERE SUBSTR(cust_name,INSTR(cust_name,'') +1) LIKE'Mc%' OR'MC%'
WHERE SUBSTR(cust_name, INSTR(cust_name,'') +1) LIKE'Mc%'
To find customers whose last names start with "Mc" or "MC", we need to ensure our SQL query correctly identifies and compares these prefixes regardless of case variations. Let's analyze the given options:
Option B: WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE UPPER('MC%')This clause uses UPPER to convert both the extracted substring (starting just after the first space, assuming it indicates the start of the last name) and the comparison string 'MC%' to uppercase. This ensures case-insensitive comparison. The LIKE operator is used to match any last names starting with "MC", which will correctly capture both "Mc" and "MC". This option is correct.
Option C: WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'Mc%'This clause applies INITCAP to the substring, which capitalizes the first letter of each word and makes other letters lowercase. The result is compared to 'Mc%', assuming only the last name follows the space. This approach will match last names starting with "Mc" (like "McEwen"), but not "MC". However, considering we're looking for "Mc" specifically, this clause works under the assumption that "Mc" is treated as proper capitalization for these last names. Thus, it can also be considered correct, albeit less inclusive than option B.
The other options either use incorrect syntax or apply case-sensitive matches without ensuring that both "Mc" and "MC" are captured:
Option A: Contains syntax errors (unmatched quotes and wrong use of IN).
Option D: Uses case-sensitive match without combining both "Mc" and "MC".
Option E: Only matches "Mc", which is too specific.
Examine the data in the INVOICES table:
Examine the data in the CURRENCIES table:
CURRENCY_CODE
-------------
JPY
GPB
CAD
EUR
USD
Which query returns the currencies in CURRENCIES that are not present in INVOICES?
SELECT currency_ code FROM currencies
MINUS
SELECT currency_ code FROM invoices;
SELECT * FROM currencies
WHERE NOT EXISTS (
SELECT NULL FROM invoices WHERE currency_ code = currency_ code);
SELECT currency_ code FROM currencies
INTERSECT
SELECT currency_ code FROM invoices;
SELECT * FROM currencies
MINUS
SELECT * FROM invoices;
To calculate the length of employment, you would compare the current date to the HIRE_DATE and calculate the difference in years.
A. This is the correct answer. The expression calculates the number of days between the current date (SYSDATE) and the HIRE_DATE, then divides by 365 to convert it to years, and checks if it is greater than 5.
B. SYSTIMESTAMP includes a time component including fractional seconds and time zone information, making this comparison incorrect.
C. There are typos in this option (CUARENT_DATE should be CURRENT_DATE and hire_data should be hire_date). Even with the correct function and column names, CURRENT_DATE returns the current date in the session time zone, not in years.
D. There are typos in this option (SYSNAYW should be SYSDATE). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.
E. This is a repetition of option D with the same issues.
F. There are typos in this option (CUNACV_DATE should be CURRENT_DATE and hire_data should be hire_date). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Date Functions"
Which two are true about the data dictionary?
Base tables in the data dictionary have the prefix DBA_.
All user actions are recorded in the data dictionary.
The data dictionary is constantly updated to reflect changes to database objects, permissions, and data.
All users have permissions to access all information in the data dictionary by default
The SYS user owns all base tables and user-accessible views in the data dictionary.
C. True, the data dictionary is constantly updated to reflect changes to the metadata of the database objects, permissions, and structures, among other things.E. True, the SYS user owns all base tables in the data dictionary. These base tables underlie all data dictionary views that are accessible by the users.
A, B, and D are not correct because: A. Base tables do not necessarily have the prefix DBA_; instead, DBA_ is a prefix for administrative views that are accessible to users with DBA privileges. B. The data dictionary records metadata about the actions, not the actions themselves. D. Not all users have access to all information in the data dictionary. Access is controlled by privileges.
References:
Oracle documentation on data dictionary and dynamic performance views: Oracle Database Reference
Understanding the Oracle data dictionary: Oracle Database Concepts
Which two statements are true about the results of using the intersect operator in compound queries?
intersect ignores nulls.
Reversing the order of the intersected tables can sometimes affect the output.
Column names in each select in the compound query can be different.
intersect returns rows common to both sides of the compound query.
The number of columns in each select in the compound query can be different.
C. True, the names of the columns in each SELECT statement of an INTERSECT query do not need to match, as long as the data types and order of the columns correspond.D. True, the INTERSECT operator returns only the rows that are common to both SELECT statements, effectively acting as a set intersection of the results from both queries.
References:
Oracle documentation on INTERSECT operator: Oracle Database SQL Language Reference
Detailed behavior of INTERSECT: Oracle Compound Queries
Examine the data in the PRODUCTS table:
Examine these queries:
1. SELECT prod name, prod list
FROM products
WHERE prod 1ist NOT IN(10，20) AND category _id=1;
2. SELECT prod name, | prod _ list
FROM products
WHERE prod list < > ANY (10，20) AND category _id= 1;
SELECT prod name, prod _ list
FROM products
WHERE prod_ list <> ALL (10， 20) AND category _ id= 1;
Which queries generate the same output?
1 and 3
1, 2 and 3
2 and 3
1 and 2
Based on the given PRODUCTS table and the SQL queries provided:
Query 1: Excludes rows where prod_list is 10 or 20 and category_id is 1.
Query 2: Includes rows where prod_list is neither 10 nor 20 and category_id is 1.
Query 3: Excludes rows where prod_list is both 10 and 20 (which is not possible for a single value) and category_id is 1.
The correct answer is A, queries 1 and 3 will produce the same result. Both queries exclude rows where prod_list is 10 or 20 and include only rows from category_id 1. The NOT IN operator excludes the values within the list, and <> ALL operator ensures that prod_list is not equal to any of the values in the list, which effectively excludes the same set of rows.
Query 2, using the <> ANY operator, is incorrect because this operator will return true if prod_list is different from any of the values in the list, which is not the logic represented by the other two queries.
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 of the EMPLOYEES table：
Which two queries return the highest salary in the table?
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;
SELECT MAX (salary)
FROM employees;
SELECT MAX (salary)
FROM employees
GROUP BY department_id;
SELECT MAX (salary)
FROM employees
GROUP BY department_id
HAVING MAX (salary) = MAX (MAX (salary));
SELECT MAX (MAX (salary))
FROM employees
GROUP BY department_id;
Query B will return the highest salary in the table without grouping by department. It simply selects the maximum value for the salary column across the entire table.
Query C and D are incorrect because the GROUP BY clause will return the highest salary for each department, not the single highest salary in the entire table.
Query E is incorrect because MAX(MAX(salary)) is not a valid use of aggregate functions and will result in an error.
References:
Oracle Documentation on Aggregate Functions: Aggregate Functions
Which two are true about virtual columns?
They can be referenced In the where clause of an update or debete statement.
They can be referenced in the set clause of an update statement as the name of the column To be updated.
They can be indexed.
They cannot have a data type explicitly specified.
They can be referenced in the column expression of another virtxial column.
Regarding the properties and capabilities of virtual columns in Oracle Database:
C. They can be indexed: In Oracle, virtual columns can indeed be indexed. This allows for performance optimization on queries that utilize these columns, despite the fact that their values are derived and not stored physically.
D. They cannot have a data type explicitly specified: The data type of a virtual column is derived from the expression used to define it. Therefore, it is not specified explicitly but is inferred from the expression itself.
Incorrect options:
A: Virtual columns cannot be referenced in the WHERE clause of UPDATE or DELETE statements because they are not stored and cannot be individually modified.
B: Virtual columns cannot be referenced in the SET clause of an UPDATE statement since they are derived from other column values and cannot be updated directly.
E: Virtual columns cannot reference other virtual columns in their expressions due to the potential for recursive and complex dependencies.
Examine the description of the 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 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 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.
The SYSDATE function displays the current Oracle Server date as:
21 -MAY-19
You wish to display the date as:
MONDAY, 21 MAY, 201 9
Which statement will do this?
SELECT TO _ CHAR (SYSDATE, ' FMDAY, DD MONTH, YYYY') FROM DUAL;
SELECT TO _ DATE (SYSDATE, ' FMDAY, DD MONTH, YYYY') FROM DUAL;
SELECT TO_ CHAR (SYSDATE, ' FMDD, DAY MONTH, YYYY') FROM DUAL;
SELECT TO_ CHAR (SYSDATE, ' FMDAY, DDTH MONTH, YYYY') FROM DUAL;
To format a date in Oracle SQL, TO_CHAR function is used to convert date types to string with a specified format:
Option A: SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;
This correctly applies the date format model 'FMDAY, DD MONTH, YYYY', which will display the date as requested: "MONDAY, 21 MAY, 2019". FM modifier is used to remove padding blanks or leading zeros.
Options B, C, and D do not achieve the desired output:
Option B is incorrect because TO_DATE converts a string to a date, not format a date.
Option C and D have incorrect format strings that do not match the required output format.
Examine this statement:
Which two things must be changed for it to execute successfully?
The word CONSTRAINT in the foreign key constraint on DEPT_ID must be changed to FOREIGN KEY.
The foreign key constraint on DEPT_ID must be defined at the table level instead of the column level.
One of the LONG columns must be changed to a VARCHAR2 or CLOB.
The NOT NULL constraint on ENAME must be defined at the column level instead of the table level.
The primary key constraint on BMP_ID must have a name.
The statement is trying to create a table with columns of different data types and constraints. Here's what needs to be corrected:
C: In Oracle, the LONG data type is used for character data of variable length up to 2 Gigabytes, but it is deprecated, and you should use CLOB or VARCHAR2 instead. Furthermore, a table cannot have more than one LONG column.
D: The NOT NULL constraint should be specified at the column level, not at the table level. The correct syntax for creating a table with a NOT NULL constraint is to define it inline with the column definition, like this:
ename VARCHAR2(15) CONSTRAINT ename_nn NOT NULL,
The other options are incorrect:
A: The foreign key constraint syntax is correct; the word CONSTRAINT is followed by the constraint name and then the REFERENCES clause.
B: The foreign key constraint can be defined at the column level.
E: While it's a good practice to name constraints, it is not mandatory for the primary key constraint to have a name; Oracle will generate one if it's not provided.
References:
Oracle Documentation on CREATE TABLE: SQL Language Reference - CREATE TABLE
Oracle Documentation on Data Types: SQL Language Reference - Data Types
Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?
SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;
SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;
SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;
SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;
The LISTAGG function is used in Oracle to aggregate strings from data in a group specified by the GROUP BY clause, producing a single row of concatenated values. The correct syntax also specifies an ORDER BY clause within the WITHIN GROUP parenthesis to sort the values in the concatenated list.
The correct query is:
SELECT deptno, LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;
This statement will return a comma-separated list of employee names (ename) in alphabetical order for each department (deptno) in the EMP table.
Examine 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.
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;
Table HR.EMPLOYEES contains a row where the EMPLOYEES _ID is 109.
User ALICE has no privileges to access HR.EMPLOYEES.
User ALICE starts a session.
User HR. starts a session and successfully executes these statements:
GRANT DELETE ON employees TO alice;
UPDATE employees SET salary = 24000 WHERE employee_id = 109;
In her existing session ALICE then executes:
DELETE FROM hr.employees WHERE employee_id = 109;
What is the result?
The DELETE command will immediately delete the row.
The DELETE command will wait for HR's transaction to end then delete the row.
The delete command will wait for HR's transaction to end then return an error.
The delete command will immediately return an error.
Examine the description of the sales table.
The sales table has 55,000 rows.
Examine this statements:
Which two statements are true?
SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table.
SALES1 created with 55, 000 rows
SALES1 created with no rows.
SALES1 created with 1 row.
SALES1 has NOT NULL constraints on any I selected columns which had those constraints I in the SALES table.
Assuming the statement involves creating a new table SALES1 from the existing SALES table:
B. SALES1 created with 55,000 rows: If the statement involved creating SALES1 by selecting all rows from SALES (such as with a CREATE TABLE AS SELECT operation), then all 55,000 rows would be copied if no WHERE clause limited the selection.
E. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table: When creating a table using the CREATE TABLE AS SELECT syntax, any NOT NULL constraints on columns in the original table will also apply to the new table.
Incorrect options:
A: PRIMARY KEY and UNIQUE constraints are not automatically copied with a CREATE TABLE AS SELECT; they must be explicitly redefined.
C: The table is created with rows if the original SELECT statement (assumed here) selected rows.
D: The statement about creating a table with 1 row is incorrect based on the assumed full selection of the original table's rows.
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.
Examine this description of the PRODUCTS table:
Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode. Which three commands execute successfully on PRODUCTS?
ALTER TAELE products DROP COLUMN expiry_date;
CREATE INDEX price_idx on products (price);
ALTER TABLE products SET UNUSED(expiry_date);
TRUNCATE TABLE products;
ALTER TABLE products DROP UNUSED COLUMNS
DROP TABLE products
B. CREATE INDEX price_idx on products (price); E. ALTER TABLE products DROP UNUSED COLUMNS; F. DROP TABLE products.
Comprehensive and Detailed Explanation WITH all References:
When a table is in read-only mode, most types of modifications are prohibited. However, certain operations can still be performed.
A. Incorrect. You cannot drop a column from a table that is in read-only mode, as it is a modifying operation. B. Correct. You can create an index on a read-only table. Creating an index does not modify the actual rows within the table; it builds a separate structure used for faster access. C. Incorrect. The SET UNUSED statement marks one or more columns as unused so they can be dropped when the database is not busy. This operation is considered a modifying operation and therefore is not allowed on a read-only table. D. Incorrect. Truncate is a DDL operation that would delete all rows from a table. This operation is not allowed on a read-only table. E. Correct. The ALTER TABLE ... DROP UNUSED COLUMNS statement is used to drop columns that have been previously marked as unused using the SET UNUSED statement. This operation is allowed because it only affects previously marked unused columns, not actively used data. F. Correct. Dropping a table is allowed even if it's in read-only mode because it is a DDL operation that does not operate on the rows of the table but rather on the object itself.
The behavior of read-only tables and the operations that can be performed on them are detailed in the Oracle Database SQL Language Reference and Oracle Database Administrator's Guide.
Which two actions can you perform with object privileges?
Create roles.
Delete rows from tables in any schema except sys.
Set default and temporary tablespaces for a user.
Create FOREIGN KEY constraints that reference tables in other schemas.
Execute a procedure or function in another schema.
Regarding object privileges in an Oracle database:
B. Delete rows from tables in any schema except sys: Object privileges include DELETE on tables, which can be granted by the owner of the table or a user with adequate privileges, excluding system schemas like SYS due to their critical role.
E. Execute a procedure or function in another schema: EXECUTE is a specific object privilege that can be granted on procedures and functions, allowing users to run these objects in schemas other than their own.
Incorrect options:
A: Creation of roles is related to system privileges, not object privileges.
C: Setting default and temporary tablespaces for a user involves system-level operations, not object-level privileges.
D: Creation of foreign key constraints involves referencing rights, which, while related, are not directly granted through object privileges but need appropriate REFERENCES permission.
Examine the data in the COLORS table:
Examine the data in the BRICKS table:
Which two queries return all the rows from COLORS?
In SQL, to return all rows from one table in a join regardless of whether they match rows in the other table, a LEFT JOIN is used when we want all rows from the left table, or a RIGHT JOIN when we want all rows from the right table. Here’s an explanation for each query provided in the context of the COLORS and BRICKS tables:
A. This query uses a LEFT JOIN and will return all the rows from the COLORS table (c) because it is on the left side of the join. Additionally, it joins the BRICKS table (b) on the condition that the color_rgb_hex_value matches. The condition in the WHERE clause (b.brick_id > 0) will not exclude any COLORS rows because it only filters the rows from the right table (BRICKS) after the join.
B. This query uses a RIGHT JOIN, and while it does return all rows from the COLORS table, it doesn't place it on the left side of the join, which is not what is typically expected from a RIGHT JOIN to return all rows from the right side table.
C. This query is a plain JOIN (also known as an inner join), which only returns rows that have matching values in both tables, so it will not return all the rows from the COLORS table if there is no match in the BRICKS table.
D. This query uses a FULL JOIN, which returns all rows when there is a match in one of the tables. Hence, it will return all the rows from both the COLORS and BRICKS tables. While this does return all rows from the COLORS table, it also includes all rows from the BRICKS table, which may not be desired if the requirement was only to return all rows from COLORS.
E. This query is similar to option A and uses the USING clause to specify the join condition. The USING clause is used when both tables have a column with the same name and is meant to simplify the syntax. However, in this case, the columns do not have the same name in both tables (RGB_HEX_VALUE in COLORS and COLOR_RGB_HEX_VALUE in BRICKS), so this query will not execute successfully.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Joins"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "LEFT OUTER JOIN"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "RIGHT OUTER JOIN"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "FULL OUTER JOIN"
Based on the explanations above, the correct answers that return all rows from the COLORS table are A and D. However, D includes all rows from BRICKS as well, which may not be the intended requirement if the question specifies only rows from COLORS should be returned. Thus, A is the most accurate answer to the question as it adheres to the standard expectation of a LEFT JOIN.
Which 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"
BOOK_SEQ is an existing sequence in your schema.
Which two CREATE TABLE commands are valid?
CREATE TABLE bookings (
bk_id NUMBER(4) NOT NULL PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE DEFAULT SYSDATE);
CREATE TABLE bookings (
bk_id NUMBER(4) NOT NULL DEFAULT book_seq.CURRVAL,
start_date DATE NOT NULL,
end_date DATE DEFAULT SYSDATE);
CREATE TABLE bookings (
bk_id NUMBER(4) DEFAULT book_seq.CURRVAL,
start_date DATE DEFAULT SYSDATE,
end_date DATE DEFAULT start date);
CREATE TABLE bookings ( bk_id NUMBER(4),
start_date DATE DEFAULT SYSDATE,
end_date DATE DEFAULT (end_date >= start_date));
CREATE TABLE bookings (
bk_id NUMBER(4) DEFAULT book_seq.NEXTVAL PRIMARY KEY,
start_date DATE DEFAULT SYSDATE,
end_date DATE DEFAULT SYSDATE NOT NULL);
For creating tables and utilizing sequences in Oracle 12c SQL, certain rules apply:
Option A: This statement is valid. It creates a table with a primary key and dates, where the default value for end_date is SYSDATE. There are no constraints on sequences, and all syntax is correct.
Option B: This option is invalid because it attempts to use CURRVAL of a sequence as a default value for a primary key. CURRVAL cannot be used until the sequence's NEXTVAL has been called in the session.
Option C: Invalid syntax because 'start date' is not a recognized default expression in Oracle SQL. If the intention was to use 'start_date' as a reference, it still would not work correctly without proper syntax for conditional default values.
Option D: This syntax is invalid in Oracle SQL because the DEFAULT clause does not support relational conditions directly within the table definition.
Option E: Valid. It correctly uses NEXTVAL for a sequence in the DEFAULT clause for the primary key, ensuring that each new record has a unique primary key generated from the sequence. The start_date and end_date are assigned defaults of SYSDATE, which is syntactically correct.
Which two are true?
CONCAT joins two or more character strings together.
FLOOR returns the largest integer less than or equal to a specified number.
CONCAT joins two character strings together.
INSTR finds the offset within a string of a single character only.
INSTR finds the offset within a character string, starting from position 0.
FLOOR returns the largest positive integer less than or equal to a specified number.
The CONCAT function and FLOOR function in Oracle SQL have specific behaviors:
A. CONCAT function joins two or more character strings into one string, making this statement true.
B. FLOOR function returns the largest integer that is less than or equal to the specified number, making this statement true.
C. While CONCAT can join two strings together, this statement is incomplete as it can join more than two.
D. INSTR can find the offset of a substring within a string, not just a single character.
E. INSTR starts searching the string from position 1 in Oracle SQL, not position 0.
F. FLOOR does return the largest integer less than or equal to the specified number, but it can be any integer, not just positive ones.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Single-Row Functions"
Which three are true aboutprivileges and roles?
System prilgese always set prilge for an entire database.
PUBLIC acts as a default role granted to every user in a database.
A user has all object privileges for every object in their schema by default.
A role can contain a combination of several privileges and roles.
A role is owned by the user who created it.
All roles are owned by the sYs schema.
PUBIIC can be revoked from a user.
In Oracle Database 12c, the following are true about privileges and roles:
Option B: PUBLIC acts as a default role granted to every user in a database.
The PUBLIC role is a special role that is automatically granted to all users. It can be used to provide privileges to every user.
Option C: A user has all object privileges for every object in their schema by default.
By default, a user has all privileges on the objects they own, i.e., the objects in their schema.
Option D: A role can contain a combination of several privileges and roles.
Roles are designed to group together privileges and other roles, making it easier to manage and assign security settings to users.
Options A, E, F, and G are incorrect because:
Option A: System privileges can be set for specific schemas or objects, not always for the entire database.
Option E: Roles are not "owned" by users in the traditional sense of ownership; they are managed by the database and can be granted to any user.
Option F: Not all roles are owned by the SYS schema; roles are a database security concept and do not have an "owner".
Option G: The PUBLIC role is not something that can be revoked from a user; it is a default role that exists for all users.
Examine this statement which executes successfully:
Which three are true?
Regardless of salary,only if the employee id is less than 125,insert EMPLOYEE_ID,NANAGER_ID,SALARY into the MGR_HISTORY table.
If the salary is more than 20000 and the employee is less than 125,insert EMPLOYEE_ID and SALARY into the SPECIAL_SAL table.
Only if the salary is 20000 or less and the employee id is less than 125,insert EMPLOYEE_ID,MANAGER_ID,and SALARY into the MGR_HISTORY table.
Regardless of salary and employee id,insert EMPLOYEE_ID,MANAGER_ID,and SALARY into the MGR_HISTORY table.
If the salary is 20000 or less and the employee id is less than 125,insert EMPLOYEE_ID,HIRE_DATE,and SALARY into the SAL_HISTORY table.
Only if the salary is 20000 or less and the employee id is 125 or higher,insert EMPLOYEE_ID,MANAGER_ID,and SALARY into the MDR_HISTORY table.
The question describes a scenario with multiple conditional statements about data manipulation based on certain criteria related to employee_id and salary. The options detail different conditions and corresponding actions (insertions into various tables). Given the conditions specified in each option, here are the accurate answers based on logical deduction as Oracle SQL does not directly define these scenarios; instead, it provides the mechanisms (like IF conditions, WHERE clauses, etc.) to implement such logic:
Option A: Regardless of salary, only if the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, SALARY into the MGR_HISTORY table.
This is logically plausible as it specifies a simple condition on the employee ID without regard to salary. If true, it directs an insertion of specific columns into a history table, which is a common practice for recording details of a subset of employees based on certain criteria (like employee_id in this case).
Option C: Only if the salary is 20000 or less and the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.
Similar to option A, this statement combines two conditions (on salary and employee_id), focusing on a specific subset of employees for history recording. The combined conditionality aligns with typical SQL practices for managing and logging specific data subsets based on multiple criteria.
Option E: If the salary is 20000 or less and the employee id is less than 125, insert EMPLOYEE_ID, HIRE_DATE, and SALARY into the SAL_HISTORY table.
This condition again deals with specific attributes (salary and employee_id) to determine which data (including the HIRE_DATE) goes into another history table. The inclusion of HIRE_DATE suggests tracking changes or states over time, which is common in employee management systems.
Examine the description of the CUSTOMERS table:
You need to display last names and credit limits of all customers whose last name starts with A or B In lower or upper case, and whose credit limit is below 1000.
Examine this partial query:
SELECT cust_last_nare, cust_credit_limit FROM customers
Which two WHERE conditions give the required result?
WHERE UPPER(cust_last_name) IN ('A%', 'B%') AND cust_credit_limit < 1000:
WHERE (INITCAP(cust_last_name) LIKE ‘A%' OR ITITCAP(cust_last_name) LIKE ‘B%') AND cust_credit_limit < 1000
WHERE UPPER(cust_last_name) BETWEEN UPPER('A%' AND 'B%’) AND ROUND(cust_credit_limit) < 1000;
WHERE (UPPER(cust_last_name) LIKE 'A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND ROUND(cust_credit_limit) < 1000;
WHERE (UPPER(cust_last_name) like INITCAP ('A') OR UPPER(cust_last_name) like INITCAP('B')) AND ROUND(cust_credit_limit) < ROUND(1000) ;
The SQL query must find all customers with last names starting with A or B, regardless of case, and a credit limit below 1000:
B. WHERE (INITCAP(cust_last_name) LIKE ‘A%' OR INITCAP(cust_last_name) LIKE ‘B%') AND cust_credit_limit < 1000: The INITCAP function initializes the first letter to uppercase for comparison. However, it should be noted that using INITCAP is not necessary when using the LIKE operator with a wildcard % following a single character, because it will not correctly filter all last names that start with an upper or lower case A or B.
D. WHERE (UPPER(cust_last_name) LIKE 'A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND cust_credit_limit < 1000: This correctly filters last names beginning with A or B in any case and includes only those with a credit limit below 1000. The UPPER function is used to convert cust_last_name to uppercase before comparison.
References:
Oracle Database SQL Language Reference 12c, especially sections on string functions and conditions.
Which three statements are true about single row functions?
They can be used only in the where clause of a select statement.
They can accept only one argument.
They return a single result row per table.
The argument can be a column name, variable, literal or an expression.
They can be nested to any level.
The date type returned can be different from the data type of the argument.
Single-row functions in SQL operate on each row independently and can modify the returned value:
Option A: Incorrect. Single row functions can be used in multiple parts of a SELECT statement, including SELECT, WHERE, and ORDER BY clauses.
Option B: Incorrect. Single row functions can accept more than one argument, such as the CONCAT function, which can accept multiple string arguments.
Option C: Incorrect. They return one result for each row processed, not per table.
Option D: Correct. Single row functions can take various types of arguments including column names, literals, variables, and other expressions.
Option E: Correct. Functions can be nested within other functions, allowing complex expressions and calculations.
Option F: Correct. The data type of the result can differ from the arguments’ data types, such as the SUBSTR function returning a VARCHAR2 even when used on a number after converting it to a string.
Which two statements are true about Oracle synonyms?
A synonym can have a synonym.
All private synonym names must be unique in the database.
Any user can create a PUBLIC synonym.
A synonym can be created on an object in a package.
A synonym has an object number.
Synonyms in Oracle Database are aliases for database objects.
A. Correct. A synonym can be created for another synonym, essentially chaining synonyms. B. Incorrect. Private synonyms must be unique within a schema. However, because each user has their own schema, multiple users can have private synonyms with the same name, each pointing to objects in their respective schemas. C. Correct. Any user with the necessary privileges can create a PUBLIC synonym, which is accessible to all users. D. Incorrect. A synonym cannot be created for an object within a package, but it can be created for the package itself. E. Incorrect. Synonyms, like views, do not have object numbers because they do not occupy space in the database as tables do.
References can be found in the Oracle Database SQL Language Reference documentation, which details the rules and functionality of synonyms.
Examine this query:
SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL; What is the result?
16
160
150
200
100
The query uses two functions: ROUND and TRUNC. The ROUND function will round the number 156.00 to the nearest hundred because of the -2 which specifies the number of decimal places to round to. This will result in 200. Then the TRUNC function truncates this number to the nearest 10, due to the -1 argument, which will give us 200 as the result since truncation does not change the rounded value in this case.
A. 16 (Incorrect)
B. 160 (Incorrect)
C. 150 (Incorrect)
D. 200 (Incorrect)
E. 100 (Incorrect)
.No user-defined locks are used in your database.
Which three are true about Transaction Control Language (TCL)?
COMMIT erases all the transaction’s savepoints and releases its locks.
COMMIT ends the transaction and makes all its changes permanent.
ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not release its locks.
ROLLBACK to SAVEPOTNT undoes the transaction's changes made since the named savepoint and then ends the transaction.
ROLLBACK without the TO SAVEPOINT clause undoes alt the transaction's changes, releases its locks, and erases all its savepoints.
ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints.
For Transaction Control Language (TCL) operations:
A. COMMIT erases all the transaction’s savepoints and releases its locks: When a COMMIT operation is executed, it finalizes all changes made during the transaction, removes any savepoints that have been set during the transaction, and releases any locks held on the database objects by the transaction.
B. COMMIT ends the transaction and makes all its changes permanent: A COMMIT command completes the transaction, making all data modifications permanent and visible to other sessions.
E. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes, releases its locks, and erases all its savepoints: The ROLLBACK command, when used without specifying a SAVEPOINT, undoes all changes made during the transaction, releases any locks held, and removes any savepoints that were established during the transaction.
Incorrect options:
C: ROLLBACK, without any specification, not only undoes all the transaction's changes but also releases all locks held by the transaction.
D: ROLLBACK to a SAVEPOINT only undoes changes up to the named savepoint and does not end the transaction; the transaction continues until a COMMIT or a full ROLLBACK is issued.
F: ROLLBACK without a TO SAVEPOINT clause also erases all savepoints created during the transaction.
Which two are true about granting privilege on objects?
The owner of an object acquires all object privilege on that object by default.
The WITH GRANT OPTION clause can be used only by DBA users.
A table owner must grant the references privilege to allow other users to create FOREIGN KEY constraints using that table.
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 object.
A. True, the owner of an object in Oracle automatically has all privileges on that object. This means they can perform any operation on the object, including SELECT, INSERT, UPDATE, DELETE, and so forth.C. True, the REFERENCES privilege must be granted explicitly by the table owner to another user or role to allow the grantee to define foreign key constraints that reference the primary or unique key of the owner's table. This is crucial in scenarios involving relational integrity constraints across tables owned by different users.
References:
Oracle documentation on object privileges: Oracle Database SQL Language Reference
Explanation of REFERENCES privilege: Oracle Database Security Guide
Examine this query:
SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3;
What is returned upon execution?
0 rows
an error
8 rows
6 rows
1 row
3 rows
The given query is using the CROSS JOIN clause, which produces a Cartesian product of the tables involved. The DUAL table in Oracle is a special one-row, one-column table present by default in all Oracle database installations. When you cross join the DUAL table with itself multiple times without any where clause to limit the rows, the result is a multiplication of the row count for each cross-joined instance.
Since DUAL has a single row, cross-joining it with itself any number of times will still result in a single row being returned. The SELECT 2 part of the query simply dictates that the number 2 will be the value in the column of this single row.
So, the result of this query will be one row with a single column containing the value 2.
References:
Oracle Database SQL Language Reference 12c, especially sections on join operations and the DUAL table.
Viev the Exhibit and examine the structure of the PRODUCT INFORMATION and INVENTORIEStables.
You have a requirement from the supplies department to give a list containing PRODUCT _ID,SUPPLIER ID, and QUANTITY_ON HAND for all the products where in QUANTITY ON HAND is lessthan five.
Which two SQL statements can accomplish the task? (Choose two)
SELECT product id, quantity on hand, supplier id
FROM product information
NATURAL JOIN inventories AND quantity .on hand < 5;
SELECT i. product id, i. quantity .on hand, pi. supplier_id
FROM product_information pi JOIN inventories i
ON (pi. product. id=i. product id) AND quantity on hand < 5;
SELECT i. product_id, i. quantity_on hand, pi. supplier id
FROM product information pi JOIN inventories i USING (product id) AND quantity .on hand < 5;
SELECT i.product id, i. quantity on hand, pi. supplier id
FROM product information pi JOIN inventories i
ON (pi.product id=i. product id)WHERE quantity on hand < 5;
Given the requirement to list PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for products with QUANTITY_ON_HAND less than five:
B. This query correctly joins the two tables on PRODUCT_ID and filters rows where QUANTITY_ON_HAND is less than five. However, the syntax presented here might be incorrect due to improper placement of the condition within the ON clause; it should be in a WHERE clause.
D. This query correctly joins the two tables on PRODUCT_ID and applies the condition in a WHERE clause, which is the proper way to filter rows after performing the join.
Incorrect options:
A: The syntax uses NATURAL JOIN and AND incorrectly; also, conditions should be in a WHERE clause, not combined with the JOIN clause.
C: Similar to A, this option incorrectly places a condition directly in the JOIN clause without using a WHERE clause.
Which two statements about INVISIBLE indexes are true?
an INVISIBLE Index consumes no storage
You can only create one INVISIBLE index on the same column list
The query optimizer never considers INVISIBLE Indexes when determining execution plans
You use AlTER INDEX to make an INVISIBLE Index VISIBLE
All INSERT, UPDATE, and DELETE statements maintain entries in the index
INVISIBLE indexes are a feature in Oracle Database 12c and later versions that allow an index to be maintained but not used by the optimizer unless explicitly hinted.
A. False. An INVISIBLE index still consumes storage space as it is maintained in the background.
B. False. There is no such restriction. You can create multiple INVISIBLE indexes on the same column list.
C. False. The optimizer can consider INVISIBLE indexes if they are hinted at in the query.
D. True. You can alter the visibility of an index using the ALTER INDEX command to make an INVISIBLE index VISIBLE.
E. True. Even though they are invisible to the optimizer by default, all DML operations such as INSERT, UPDATE, and DELETE continue to maintain the index as they would with a VISIBLE index.
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).
In the PROMOTIONS table, the PROMO_ BEGIN_DATE column is of data type and the default date format is DD-MON-RR
Which two statements are true about expressions using PROMO_ BEGIN_DATE in a query?
TONUMBER (PROMO BEGIN_DATE) - 5 will return a number
PROMO_ BEGIN_DATE - 5 will return a date
PROMO_ BEGIN_DATE - SYSDATE will return a number
PROMO_ BEGIN_DATE - SYSDATE will return an error
TODATE(PROMO BEGIN_DATE *5) will return a date
In Oracle SQL, when you subtract a number from a date, the result is a date. When you subtract one date from another, the result is the number of days between the two dates.
B: PROMO_BEGIN_DATE - 5 will subtract 5 days from the PROMO_BEGIN_DATE, resulting in a new date that is 5 days earlier than PROMO_BEGIN_DATE.
C: PROMO_BEGIN_DATE - SYSDATE will return the number of days between the PROMO_BEGIN_DATE and the current date (SYSDATE).
The incorrect options are:
A: TONUMBER(PROMO_BEGIN_DATE) - 5 will not return a number because PROMO_BEGIN_DATE is a date, and TONUMBER is not a valid function to convert dates to numbers in Oracle.
D: PROMO_BEGIN_DATE - SYSDATE will not return an error; it will return the number of days between the two dates as explained above.
E: TODATE(PROMO_BEGIN_DATE * 5) will not return a date because PROMO_BEGIN_DATE * 5 is not a valid operation in Oracle SQL as you cannot multiply a date by a number, and TODATE is not a valid function. The correct function name is TO_DATE.
References:
Oracle Documentation on Date Arithmetic: Database SQL Language Reference - Datetime Functions
Which three are true about the MERGE statement？
It can merge rows only from tables.
It can use views to produce source rows.
It can combine rows from multiple tables conditionally to insert into a single table.
It can use subqueries to produce source rows.
It can update the same row of the target table multiple times.
It can update, insert, or delete rows conditionally in multiple tables.
B: True. The Oracle Database MERGE statement can use a subquery that involves views to generate the source data for the merge operation. This allows for greater flexibility in specifying the data to be merged.
C: True. MERGE can conditionally combine rows from one or more source tables (or views, or the results of a subquery) to insert or update rows in a single target table based on whether or not a match exists.
D: True. MERGE can indeed use subqueries to produce source rows. The source data for a merge operation can be a table, view, or the result of a subquery.
The MERGE statement is a powerful SQL operation in Oracle that allows for conditional INSERT or UPDATE operations in one statement, often referred to as an "upsert" operation. It can handle complex scenarios where the decision to insert or update is based on whether the data matches between the target and source datasets.
References:Oracle SQL documentation on the MERGE statement provides information on how it can use different data sources, including subqueries and views, and perform conditional operations on the target table.
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 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.
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 statements are true regarding primary and foreign key constraints and the effect they can have on table data?
A table can have only one primary key but multiple foreign keys.
It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.
Primary key and foreign key constraints can be defined at both the column and table level.
Only the primary key can be defined the column and table level.
It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted.
The foreign key columns and parent table primary key columns must have the same names.
A table can have only one primary key and one foreign key.
Regarding primary and foreign key constraints:
A. A table can have only one primary key but multiple foreign keys. This is true. A table is constrained to have only one primary key, which can consist of multiple columns, but can have several foreign keys referencing primary keys in other tables.
C. Primary key and foreign key constraints can be defined at both the column and table level. True. Constraints can be defined inline with the column definition or separately at the end of the table definition.
E. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted. This is also true if the foreign key is defined with the ON DELETE CASCADE option.
Options B, D, F, and G are incorrect:
B is incorrect because if a parent row is deleted, the child rows cannot remain without violating the integrity unless the foreign key is defined with ON DELETE SET NULL or similar behavior.
D is incorrect because both primary and foreign key constraints can be defined at both levels.
F is incorrect as the names of the foreign key columns do not need to match the primary key column names.
G is incorrect as a table can have multiple foreign keys.
Which two are true about rollbacks?
The ROLLBACK statement does not release locks resulting from table updates.
Data Control L anguage (DCL) statements, such as GRANT and REVOKE, can be rolled back.
A transaction interrupted by a system failure is automatically rolled back.
If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted .
Data consistency is not guaranteed after a rollback.
C. True. A transaction that is interrupted by a system failure is automatically rolled back by Oracle to ensure data consistency. This is part of the ACID (Atomicity, Consistency, Isolation, Durability) properties that Oracle adheres to.
D. True. A ROLLBACK without specifying a savepoint rolls back the entire transaction and removes all savepoints within that transaction. This restores the data to the state it was in at the beginning of the transaction.
Which two 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.
You and your colleague Andrew have these privileges on the EMPLOYEE_RECORDS table:
1. SELECT
2. INSERT
3. UPDATE
4. DELETE
You connect to the database instance an perform an update to some of the rows in
EMPLOYEE_RECORDS, but don’t commit yet.
Andrew connects to the database instance and queries the table
No othet user are accessing the table
Which two statements ate true at this point?
Andrew will be able to modify any rows in the table that have not been modified by your transaction
Andrew will be unable to see the changes you have made
Andrew will be able to see the changes you habe made
Andrew will be unable to perform any INSERT, UPDATE of DELETE on the teble
Andrew will be able to SELECT from the table, but be unable to modify any existing rows.
In Oracle Database, when a transaction is not committed, the changes it makes are not visible to other sessions. This is due to Oracle's read consistency model, which means that Andrew will not be able to see the changes you have made until they are committed.
Option A is correct because, in Oracle, another session can modify rows that have not been locked by an uncommitted transaction.
Option C is incorrect because, as per Oracle's read consistency, the changes made by an uncommitted transaction are not visible to other users.
Option D is incorrect because Andrew can perform INSERT, UPDATE, or DELETE operations on the rows that you have not modified.
Option E is incorrect because, while Andrew will be able to SELECT from the table, he will still be able to modify rows that are not locked by your uncommitted update.
References:
Oracle Documentation on Transactions: Transactions
Oracle Documentation on Read Consistency: Read Consistency
Examine these statements:
CREATE TABLE dept (
deptno NUMBER PRIMARY KEY,
diname VARCHAR2(10) ,
mgr NUMBER ,
CONSTRAINT dept_fkey FOREIGN KEY(mgr) REFERENCES emp (empno));
CREATE TABLE emp (
Empno NUMBER PRIMARY KEY,
Ename VARCHAR2 (10) ,
deptno NUMBER,
CONSTRAINT emp_fkey FOREIGN KEY (deptno) REFERENCES dept (deptno) DISABLE);
ALTER TABLE emp MODIFY CONSTRAINT emp_fkey ENABLE;
Which two are true?
The MGR column in the DEPT table will not be able to contain NULL values.
The CREATE TABLE EMP statement must precede the CREATE TABLE DEPT statement for all threestatements to execute successfully.
Both foreign key constraint definitions must be removed from the CREATE TABLE statements, andbe added with ALTER TABLE statements once both tables are created, for the two CREATE TABLEstatements to
execute successfully in the order shown.
The DEFT FKEY constraint definition must be removed from the CREATE TABLE DEF statement.and be added with an AITER TABLE statement once both tables are created, for the two CREATE TABLE statements
to execute successfully in the order shown.
The Deptno column in the emp table will be able to contain nulls values.
All three statements execute successfully in the order shown
E. True. The deptno column in the emp table can contain null values unless explicitly constrained not to, which is not the case here.
F. True. The three statements can execute successfully in the order shown. The foreign key constraint in the dept table is referring to the emp table which does not yet exist, but this is not a problem because it is not immediately enabled. The emp table is created next with a disabled foreign key constraint referencing the already created dept table. Finally, the foreign key constraint on emp is enabled after both tables are in place.
A is incorrect because the MGR column can contain NULL values as there is no NOT NULL constraint applied to it. B is incorrect because the CREATE TABLE EMP statement contains a disabled foreign key, so it can be created before CREATE TABLE DEPT. C and D are incorrect because foreign key constraints do not need to be removed and added later for the tables to be created successfully; they can be managed as shown in the provided statements.
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.
Which two statements are true regarding the UNION ALL operators?
NULLS are not ignored during duplicate checking.
Duplicates are eliminated automatically by the UNION ALL operator
The names of columns selected in each SELECT statement must be identical.
The number of columns selected in each SELECT statement must be identical
The output is sorted by the UNION ALL operator.
The UNION ALL operator in SQL combines the results of two or more SELECT statements into a single result set:
Option A: True. Since UNION ALL does not eliminate duplicates, it does not ignore NULLs during any duplicate checking; however, it's worth noting that UNION ALL does not perform duplicate checking at all.
Option B: False. Unlike the UNION operator, UNION ALL does not eliminate duplicates; it includes all duplicates in the output.
Option C: False. The column names do not need to be identical in each SELECT statement; however, the result set will use the column names from the first SELECT statement.
Option D: True. For UNION ALL to work correctly, the number of columns and their data types in each SELECT statement must match, but the column names can differ.
Option E: False. UNION ALL does not inherently sort the output; it merely combines the results as they are retrieved. Any sorting would need to be explicitly specified using an ORDER BY clause.
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 statement fails to execute successfully?
A)
B)
C)
D)
Option A
Option B
Option C
Option D
In Oracle SQL, when performing a JOIN operation, the ON clause is used to specify the condition that relates the two tables being joined. The WHERE clause can be used to further filter the result set.
A) This is a valid join condition using the WHERE clause to filter the rows after the join has been made.
B) This statement will fail because the ON clause should only contain conditions that relate the two tables. The condition for filtering the departments table should be in the WHERE clause, not in the ON clause. This is a common mistake when writing JOIN statements.
C) This is a correct statement. The ON clause specifies how the tables are related and the WHERE clause specifies an additional filtering condition for the query.
D) This statement is also correct. It's similar to the first statement (A) and properly places the department_id filter in the ON clause, which is acceptable though not typically best practice as it can be less readable than using a WHERE clause for non-join conditions.
When the JOIN operation is executed, the database first pairs rows from the joined tables that meet the join condition specified by the ON clause. Then, it filters the result of the JOIN operation based on the condition specified in the WHERE clause.
References:
Oracle Documentation on Joins: https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52359
The ORDERS table has a primary key constraint on the ORDER_ID column.
The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table.
The constraint is defined with on DELETE CASCADE.
There are rows in the ORDERS table with an ORDER_TOTAL less than 1000.
Which three DELETE statements execute successfully?
DELETE FROM orders WHERE order_total<1000;
DELETE * FROM orders WHERE order_total<1000;
DELETE orders WHERE order_total<1000;
DELETE FROM orders;
DELETE order_id FROM orders WHERE order_total<1000;
In Oracle 12c SQL, the DELETE statement is used to remove rows from a table based on a condition. Given the constraints and the information provided, let's evaluate the options:
A. DELETE FROM orders WHERE order_total<1000;: This statement is correctly formatted and will delete rows from the ORDERS table where ORDER_TOTAL is less than 1000. If there is a DELETE CASCADE constraint, corresponding rows in the ORDER_ITEMS table will also be deleted.
B. DELETE * FROM orders WHERE order_total<1000;: This syntax is incorrect. The asterisk (*) is not used in the DELETE statement.
C. DELETE orders WHERE order_total<1000;: This statement is also correctly formatted and is a shorthand version of the DELETE statement without the FROM clause.
D. DELETE FROM orders;: This statement will delete all rows from the ORDERS table, and with DELETE CASCADE, it will also delete all related rows in the ORDER_ITEMS table.
E. DELETE order_id FROM orders WHERE order_total<1000;: This syntax is incorrect because you cannot specify a column after the DELETE keyword.
References:
Oracle Database SQL Language Reference 12c Release 1 (12.1), DELETE Statement
Which two statements are true regarding a SAVEPOINT?
Rolling back to a SAVEPOINT can undo a CREATE INDEX statement.
Only one SAVEPOINT may be issued in a transaction.
A SAVEPOINT does not issue a COMMIT
Rolling back to a SAVEPOINT can undo a TRUNCATE statement.
Rolling back to a SAVEPOINT can undo a DELETE statement
Regarding the use of SAVEPOINT in transactions:
C. A SAVEPOINT does not issue a COMMIT: A SAVEPOINT in Oracle SQL is used to mark a point within a transaction. It does not commit the transaction; rather, it allows partial rollbacks to the point of the SAVEPOINT without affecting other uncommitted changes outside it.
E. Rolling back to a SAVEPOINT can undo a DELETE statement: SAVEPOINT allows you to rollback DML changes like INSERT, UPDATE, and DELETE that occur after the SAVEPOINT was established, but before a COMMIT.
Incorrect options:
A: CREATE INDEX is a DDL statement, and like most DDL statements, it implicitly commits the transaction, making it impossible to rollback using a SAVEPOINT.
B: Multiple SAVEPOINTS can be defined within a single transaction, each with unique or reused names.
D: TRUNCATE is a DDL statement that also implicitly commits the transaction; hence it cannot be rolled back to a SAVEPOINT.
Examine this SQL statement
DELETE FROM employees e
WHERE EXISTS
(SELECT' dummy'
FROM emp history
WHERE employee_ id= e. employee id);
Which two are true?
The subquery is not a correlated subquery.
The subquery is executed before the DELETE statement is executed.
All existing rows in the EMPLOYEES table are deleted,
The DELETE statement executes successfully even if the subquery selects multiple rows.
The subquery is executed for every row in the EMPLOYEES table.
For the provided DELETE statement with an EXISTS clause:
Option B: The subquery is executed before the DELETE statement is executed.
Subqueries with EXISTS are typically executed before the outer DELETE statement to determine which rows of the outer query satisfy the condition.
Option D: The DELETE statement executes successfully even if the subquery selects multiple rows.
The EXISTS condition is used to check for the existence of rows returned by the subquery, regardless of how many rows there are. It returns TRUE if the subquery returns at least one row.
Options A, C, and E are incorrect because:
Option A: This statement is incorrect; the subquery is indeed a correlated subquery because it references the employee_id from the outer query (employees).
Option C: This is incorrect because not all existing rows in the EMPLOYEES table will be deleted, only those for which an associated record exists in the emp_history table.
Option E: While technically the subquery may be evaluated multiple times, it is only executed for those rows in EMPLOYEES that satisfy the condition of the EXISTS clause.
You execute this command:
TRUNCATE TABLE depts;
Which two are true?
It retains the indexes defined on the table.
It drops any triggers defined on the table.
A Flashback TABLE statement can be used to retrieve the deleted data.
It retains the integrity constraints defined on the table.
A ROLLBACK statement can be used to retrieve the deleted data.
It always retains the space used by the removed rows
The TRUNCATE TABLE command in Oracle SQL is used to quickly delete all rows from a table:
Option A:
It retains the indexes defined on the table. TRUNCATE does not affect the structure of the table, including its indexes.
Option D:
It retains the integrity constraints defined on the table. TRUNCATE does not remove or disable integrity constraints, except for unenforced foreign keys.
Options B, C, E, and F are incorrect because:
Option B: TRUNCATE does not drop triggers; it only removes all rows.
Option C: Flashback Table cannot be used after a TRUNCATE because TRUNCATE is a DDL operation that does not generate undo data for flashback.
Option E: A ROLLBACK cannot be used after a TRUNCATE because TRUNCATE is a DDL command that implicitly commits.
Option F: TRUNCATE may deallocate the space used by the table, depending on the database version and specific options used with the TRUNCATE command.
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.
You must find the number of employees whose salary is lower than employee 110.
Which statement fails to do this?
SELECT COUNT (*)
FROM employees
JOIN employees a
ON e. salary< a. salary
WHERE a. employee_ id= 110;
SELECT COUNT (* )
FROM employees
WHERE salary < (SELECT salary FROM employees WHERE employee 业id =
110) ;
SELECT COUNT (*)
FROM employees e
JOIN (SELECT salary FROM employees WHERE employee_ id= 110) a
ON e. salary< a. salary;
SELECT COUNT (* )
FROM employees e
WHERE e. salary < (SELECT a. salary FROM employees a WHERE e. employee_ id = 110);
In this context, the correct answers will provide a count of employees with a salary less than that of employee with ID 110. Let's evaluate the provided statements:
A: This statement is incorrect due to a syntax error; it uses alias 'e' without defining it, and 'a' is also used incorrectly in the JOIN clause. It should have been written with proper aliases.
B: This statement is correct as it uses a subquery to find the salary of employee ID 110 and compares it to the salaries of all employees to get the count.
C: This statement is also correct as it creates a derived table with the salary of employee ID 110 and compares it with the salaries of the 'employees' table using a JOIN.
D: This is the statement that fails. It is incorrect because the subquery references 'e.employee_id = 110', which will compare the salary of each employee to themselves if their ID is 110, not to the salary of employee 110 for all employees.
Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)?
There must be an equal number of columns in each SELECT list.
The name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.
Each SELECT statement in the query can have an ORDER BY clause.
None of the set operators can be used when selecting CLOB columns.
The FOR UPDATE clause cannot be specified.
A. True. When using set operators, the number and order of columns must be the same in all SELECT statements involved in the query. The data types of those columns must also be compatible.
E. True. The FOR UPDATE clause cannot be specified in a subquery or a SELECT statement that is combined with another SELECT statement by using a set operator.
B is incorrect because the column names do not need to match; only the number and data types of the columns must be compatible. C is incorrect because only the final SELECT statement in the sequence of UNION/INTERSECT/MINUS operations can have an ORDER BY clause. D is incorrect because, as of Oracle 12c, UNION ALL can be used with CLOB columns, but UNION, INTERSECT, and MINUS cannot.
Which two statements are true about substitution variables?
A substitution variable used to prompt for a column name must be endorsed in single quotation marks.
A substitution variable used to prompt for a column name must be endorsed in double quotation marks.
A substitution variable prefixed with & always prompts only once for a value in a session.
A substitution variable can be used with any clause in a SELECT statement.
A substitution variable can be used only in a SELECT statement.
A substitution variable prefixed with 6 prompts only once for a value in a session unless is set to undefined in the session.
Substitution variables in Oracle are used to replace a value dynamically during the execution of SQL statements. The behavior of these variables is well-documented:
C. A substitution variable prefixed with & always prompts only once for a value in a session: This is true. In a session, when you use a single ampersand (&), SQL*Plus or SQL Developer will prompt for the value the first time the variable is encountered. The value for this variable will then be reused for the remainder of the session unless it is redefined.
D. A substitution variable can be used with any clause in a SELECT statement: Substitution variables can be placed in any part of a SQL statement, including the SELECT, WHERE, GROUP BY, ORDER BY, etc. They are not limited to any specific clause.
References:
Oracle SQL*Plus User's Guide and Reference, which discusses substitution variables.
Which two statements are true about the COUNT function?
It can only be used for NUMBER data types.
COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column
COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.
A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.
The COUNT function is one of the most commonly used aggregate functions in SQL for determining the number of items in a group. Here's why the correct answers are B and C:
A: Incorrect. COUNT can be used with any data type, not just NUMBER. It counts rows without regard to data type.
B: Correct. COUNT(DISTINCT inv_amt) counts the number of unique non-null values in the column inv_amt. It excludes duplicates and ignores NULL values.
C: Correct. COUNT(*) counts all rows in a table, including those with duplicates and those with NULLs in any column. It is a total row count regardless of content.
D: Incorrect. You can use a WHERE clause with COUNT. The WHERE clause filters rows before counting, which is standard in SQL.
E: Incorrect. COUNT(inv_amt) counts the rows where inv_amt is not NULL. Rows with NULL in the inv_amt column are not counted.
You have been asked to create a table for a banking application.
One of the columns must meet three requirements:
1: Be stored in a format supporting date arithmetic without using conversion functions
2: Store a loan period of up to 10 years
3: Be used for calculating interest for the number of days the loan remains unpaid Which data type should you use?
TIMESTAMP WITH TIMEZONE
TIMESTAMP
TIMESTAMP WITH LOCAL TIMEZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
The requirements specify that the data type must support date arithmetic, store a specific period (up to 10 years in terms of days), and be used for day-level calculations. The appropriate data type is:
A: Incorrect. TIMESTAMP WITH TIMEZONE stores date and time with time zone information, which is not specifically needed for arithmetic or interval storage.
B: Incorrect. TIMESTAMP stores date and time without considering time zone or providing interval arithmetic functionality.
C: Incorrect. TIMESTAMP WITH LOCAL TIMEZONE adjusts the time zone but does not inherently support interval arithmetic or storage.
D: Incorrect. INTERVAL YEAR TO MONTH is used for storing periods in terms of years and months, not suitable for daily calculations.
E: Correct. INTERVAL DAY TO SECOND allows storage of periods in terms of days, hours, minutes, and seconds, meeting all the given requirements for precision and functionality in date arithmetic related to daily calculations.
Examine the command to create the BOOKS table.
SQL> create table books(book id CHAR(6) PRIMARY KEY,
title VARCHAR2(100) NOT NULL,
publisher_id VARCHAR2(4)，
author_id VARCHAR2 (50));
The BOOK ID value 101 does not exist in the table.
Examine the SQL statement.
insert into books (book id title, author_id values
(‘101’，’LEARNING SQL’,’Tim Jones’)
It executes successfully and the row is inserted with a null PLBLISHER_ID.
It executes successfully only if NULL is explicitly specified in the INSERT statement.
It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the INSERT statement.
It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and NULL is explicitly specified In the INSERT statement.
A. It executes successfully and the row is inserted with a null PUBLISHER_ID: The SQL statement does not specify the publisher_id, and since there is no NOT NULL constraint on this column, Oracle will insert the row with a NULL value for publisher_id. The statement is syntactically correct, assuming the column names and values are properly specified and formatted.
Which three statements are true about dropping and unused columns in an Oracle database?
A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
A DROP COLUMN command can be rolled back.
An UNUSED column's space is remained automatically when the block containing that column is next queried.
An UNUSED column's space is remained automatically when the row containing that column is next queried.
Partition key columns cannot be dropped.
A column that is set to NNUSED still counts towards the limit of 1000 columns per table.
A. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option. (Incorrect)
Primary key columns that are referenced by foreign keys cannot be dropped as it would violate referential integrity. The CASCADE option does not apply in this context.
B. A DROP COLUMN command can be rolled back. (Correct)
Dropping a column from a table is a transactional operation. If it's not committed, it can be rolled back.
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.
Examine these statements:
CREATE TABLE alter_test (c1 VARCHAR2(10), c2 NUMBER(10));
INSERT INTO alter_test VALUES ('123'， 123);
COMMIT;
Which is true ahout modifyIng the columns in AITER_TEST?
c1 can be changed to NUMBER(10) and c2 can be changed to VARCHAN2 (10).
c2 can be changed to NUMBER(5) but c1 cannot be changed to VARCHAN2 (5).
c2 can be changed to VARCHAR2(10) but c1 cannot be changed to NUMBER (10).
c1 can be changed to NUMBER(10) but c2 cannot be changed to VARCHAN2 (10).
c1 can be changed to VARCHAR2(5) and c2 can be changed to NUMBER (12,2).
In Oracle SQL, the ALTER TABLE statement is used to modify the definition of a table after it has been created. Specific changes are allowed depending on the current data type of the column and the data it contains.
A. You cannot change a VARCHAR2 column to NUMBER if the table contains rows with non-numeric values, which is the case with '123' as a VARCHAR2; it is a string, not a number.
B. You can reduce the size of a NUMBER column if the data will still fit within the new precision, but you cannot change a VARCHAR2 column to a smaller size if it contains values that exceed the new size limit.
C. You cannot change a NUMBER column to VARCHAR2 if the table contains rows because the existing data type of NUMBER does not match the new data type of VARCHAR2.
D. Similar to A, you cannot change the data type from VARCHAR2 to NUMBER if non-numeric data is present.
E. You can change a VARCHAR2(10) column to VARCHAR2(5) if all existing rows have values that fit within the new size, and you can change a NUMBER column to have a different scale (NUMBER(12,2)).
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ALTER TABLE"
Examine these requirements:
1. Display book titles for books purchased before January 17, 2007 costing less than 500 or more than 1000.
2. Sort the titles by date of purchase, starting with the most recently purchased book.
Which two queries can be used?
SELECT book_title FROM books WHERE (price< 500 OR >1000) AND (purchase date< '17-JAN-2007') ORDER BY purchase date DESC;
SELECT book_title FROM books WHERE (price IN (500, 1000)) AND (purchase date < '17-JAN-2007') ORDER BY purchase_date ASC;
SELECT book_title FROM books WHERE (price NOT BETWEEN 500 AND 1000) AND (purchase_date< '17-JAN-2007') ORDER BY purchase_date DESC;
SELECT book_title FROM books WHERE (price BETWEEN 500 AND 1000) AND (purchase_date<'17-JAN-2007') ORDER BY purchase_date;
The requirements specify that we need to select book titles based on a price condition and a date condition, and then sort the results by the date of purchase.
A. This query will not execute successfully because there is a syntax error in the price condition. It should be price < 500 OR price > 1000.
B. This query is incorrect. The requirement specifies that the price must be less than 500 or more than 1000, not in a list of values.
C. This query is correct. It selects books where the price is not between 500 and 1000 (thus, less than 500 or more than 1000) and the purchase date is before January 17, 2007, ordered by purchase date in descending order.
D. This query is incorrect because it selects books with prices between 500 and 1000, which is not what the requirement specifies.
Which three statements are true about Data Manipulation Language (DML)?
delete statements can remove multiple rows based on multiple conditions.
insert statements can insert nulls explicitly into a column.
insert into. . .select. . .from statements automatically commit.
DML statements require a primary key be defined on a table.
update statements can have different subqueries to specify the values for each updated column.
Data Manipulation Language (DML) operations in Oracle 12c are critical for handling data within tables. Here’s the validity of each statement based on Oracle SQL documentation:
Option A: True. The DELETE statement can remove multiple rows from a table and supports the use of multiple conditions in the WHERE clause to specify which rows should be removed. This allows for flexibility in managing data deletion based on various criteria.
Option B: True. The INSERT statement can explicitly insert NULL values into a column, assuming there are no constraints (like NOT NULL) preventing such entries. This is useful for representing the absence of data or when data is unknown.
Option C: False. DML statements like INSERT INTO...SELECT...FROM do not automatically commit in Oracle. Transactions in Oracle require explicit COMMIT commands to finalize changes, unless in an autocommit mode set by a client tool or environment, which is generally not the default behavior for server-side operations.
Option D: False. DML statements do not require a primary key to be defined on a table. While having a primary key is a best practice for identifying each row uniquely, it's not a prerequisite for performing DML operations like INSERT, UPDATE, or DELETE.
Option E: True. The UPDATE statement can include different subqueries within the SET clause to determine the values for each column being updated. This allows for complex calculations and data retrieval to be part of an update, increasing the power and flexibility of data modification.
Which two statements will return the names of the three employees with the lowest salaries?
SELECT last_ name, salary
FROM employees
FETCH FIRST 3 ROWS ONLY
ORDER BY salary;
SELECT last name, salary
FROM employees
ORDER BY salary
FETCE FIRST 3 RONS ONLY;
SELECT last_ name, salary
FBOM employees
WEERE
ORDER BY SELECT
ROINUM <= 3
salary FROM
employees);
SELECT last_ name, salary
FROM
(SELECT” FROM employees ORDER BY salary)
WHERE ROWNUM <=3
SELECT last_ name, salary
FROM employees
WHERE ROWNUM <=3
ORDER BY salary
A: This statement is correct. It orders the employees by salary and fetches the first 3 rows.
B: This statement has a typo with "FETCE" and "RONS" which should be "FETCH" and "ROWS". Hence, it will not execute successfully.
C: This statement will not execute successfully due to syntactical errors and incorrect use of the ORDER BY clause.
D: This statement is correct. It uses a subquery to order employees by salary and then limits the results to the first 3 using the ROWNUM pseudo-column.
E: This statement will not execute successfully because ROWNUM is evaluated before the ORDER BY clause, so it would return the first 3 rows based on the table's natural order, not the lowest salaries.
The behavior of ROWNUM and the FETCH FIRST syntax are explained in the Oracle Database SQL Language Reference 12c.
You own table DEPARTMENTS, referenced by views, indexes, and synonyms.
Examine this command which executes successfully:
DROP TABLE departments PURGE;
Which three statements are true?
Neither can it be rolled back nor can the DEPARTMENTS table be recovered.
It will remove all views that are based on the DEPARTMENTS table.
It will delete all rows from the DEPARTMENTS table, but retain the empty table.
It will remove the DE PARTMENTS table from the database.
It will remove all synonyms for the DEPARTMENTS table.
It will drop all indexes on the DEPARTMENTS table.
A: This is true. Using PURGE in the DROP TABLE command will permanently remove the table and its dependent objects so that it cannot be recovered, and the action cannot be rolled back.
B: This statement is true. Dropping the base table will result in the removal of all views that are based on the DEPARTMENTS table.
C: This statement is false. The PURGE option removes the table and does not just delete rows.
D: This statement is true. The DROP TABLE command will remove the DEPARTMENTS table from the database.
E: This statement is true. When a table is dropped, all synonyms for that table are also removed.
F: This statement is true. Dropping a table will automatically drop all indexes associated with the table.
In your session NLS_ DATE_ FORMAT is set to DD–MON_RR.
Which two queries display the year as four digits?
SELECT TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY'),'MM/DD/YYYY') FROM DUAL;
SELECT TO_CHAR (ADD_MONTHS (SYSDATE,6)) FROM DUAL;
SELECT TO_DATE (SYSDATE, 'RRRR-MM-DD') FROM DUAL;
SELECT TO_DATE (ADD_MONTHS(SYSDATE,6), 'dd-mon-yyyy') FROM DUAL;
SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') FROM DUAL;
SELECT TO_CHAR (ADD_MONTHS (SYSDATE, 6), 'dd-mon-yyyy') FROM DUAL;
A. True. The TO_CHAR function will convert SYSDATE to a string using the format 'MM/DD/YYYY', which displays the year as four digits. Then TO_DATE will convert this string back to a date without changing its appearance because the format matches.
E. True. The TO_CHAR function with the format 'MM/DD/YYYY' will display the date with the year as four digits, regardless of the NLS_DATE_FORMAT setting.
B, C, D, and F are incorrect because they either do not specify the format model needed to display the year as four digits or use TO_DATE which does not format the output but rather interprets the input.
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.
The ORDERS table has a column ORDER_DATE of date type DATE The default display format for a date is DD-MON-RR
Which two WHERE conditions demonstrate the correct usage of conversion functions?
WHERE ordet_date> TO_CHAR(ADD_MONTHS(SYSDATE, 6),'MON DD YYYY')
WHERE TO_CHAR(order_date,'MON DD YYYY') ='JAN 20 2019';
WHERE order_date> TO_DATE('JUL 10 2018','MON DD YYYY');
WHERE order_date IN (TO_DATE ('Oct 21 2018','MON DD YYYY'), TO_CHAR('Nov 21 2018','MON DD YYYY'));
WHERE order_date> TO_DATE(ADD_MONTHS(SYSDATE,6),'MON DD YYYY');
In SQL, the correct usage of conversion functions is crucial when performing operations on dates. Oracle uses the TO_DATE function to convert a string to a date, and the TO_CHAR function to convert dates or numbers to strings.
Statement C is correct: WHERE order_date > TO_DATE('JUL 10 2018','MON DD YYYY'); is a proper use of the TO_DATE function. It converts the string 'JUL 10 2018' to a date type, with the format 'MON DD YYYY', which is then used to compare with the order_date.
Statements A, B, D, and E are incorrect or misuse conversion functions:
A is incorrect because TO_CHAR is used to convert dates or numbers to strings, not the other way around, and therefore should not be compared with order_date.
B is incorrect because order_date is of type DATE, and you should not compare a DATE with a string without converting it; the TO_CHAR here should be TO_DATE.
D is incorrect because it mixes TO_DATE and TO_CHAR in the same IN list, which should contain date types only.
E is incorrect because TO_DATE should take a string as an argument, not a date returned by ADD_MONTHS.
Which three statements are true about the DESCRIBE command?
It can be used from SQL Developer.
It can be used to display the structure of an existing view.
It can be used only from SQL*Plus.
It displays the NOT NULL constraint for any columns that have that constraint.
It displays all constraints that are defined for each column.
It displays the PRIMARY KEY constraint for any column or columns that have that constraint.
A: True. The DESCRIBE command can indeed be used from SQL Developer as well as other Oracle database tools such as SQL*Plus. This command is used to display the structure of a table, view, or other object, showing information such as column names, data types, and whether a column is nullable.
B: True. The DESCRIBE command can be used to display the structure of an existing view, showing similar information as it would for a table. This includes the columns, their data types, and other pertinent details.
D: True. When DESCRIBE is used, it does display the NOT NULL constraints for columns that have this constraint. This is part of the basic information about the structure of a table or view that can help developers understand the requirements of the data stored therein.
Examine this statement:
SELECT last name
FROM employees
ORDER BY CASE WHEN salary = (SELECT MAX(salary) FROM employees)
THEN ‘A’
ELSE last_ name
END ,last_name DESC;
Which two statements are true?
The names of employees earning the maximum salary will appear first in descending order.
The names of employees earning the maximum salary will appear first In ascending order.
All remaining employee names will appear in ascending order.
All remaining employee names will appear in an unspecified order.
All remaining employee names will appear in descending order.
The names of employees earning the maximum salary will appear first in an unspecified order.
The ORDER BY clause with a CASE statement allows for conditional sorting based on the results of the CASE expression.
A. This is not true, as the 'A' in the THEN clause would cause the employees earning the maximum salary to be sorted at the top, but it does not determine the order in which their last names would appear.
B. This is true, the 'A' in the THEN clause will result in all maximum salary earners to be sorted at the top, and since 'A' comes before any last names alphabetically, they will be in ascending order.
C. This is not true, as the ELSE clause sorts the remaining employees by last_name but does not specify an order; hence, it defaults to ascending which contradicts the second part of the ORDER BY clause which specifies DESC.
D. This is not true, as the order of the remaining employee names is specified by the ORDER BY clause, which states last_name DESC.
E. This is true, after the employees with the maximum salary are sorted, the remaining employees' names will appear in descending order due to the last_name DESC in the ORDER BY clause.
F. This statement is not true as the specified 'A' in the CASE statement provides a definite order for employees earning the maximum salary.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ORDER BY Clause"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CASE Expression"
Which two statements execute successfully?
SELECT TO_ DATE('2019-DEC-25 15:30'， 'YYYY-MON-DD HH24:MI', 'NLS_ DATE_ LANGUAGE
=AMERICAN' ) FROM DUAL;
SELECT TO_CHAR('2019-DEC-25 15:30"， YY-MON-D HH24:M2', 'NLS_DATE LANGUAGE =
AMERICAN')
FROM DUAL;
SELECT TO _DATE (TO_ CHAR (‘2019-DEC-25 03:30’, ‘YYYY-MON-DD HH12:MI’))
FROM DUAL;
SELECT TO _ CHAR (TO_ DATE (‘2019-DEC-25 03:30’,’YYYY-MON-DD HH12:MI’))
FROM DUAL
SELECT TO _ CHAR (‘2019-DEC-25 15:30’.’YYYY-MON-DD HH24:MI’)
FROM DUAL
A: This statement is correct. It uses the TO_DATE function with a proper date string, format mask, and NLS_DATE_LANGUAGE setting.
B: This statement will not execute successfully because the syntax of the TO_CHAR function is incorrect. The date string should be a DATE data type when used with TO_CHAR, and the format mask and NLS parameter are incorrectly specified.
C: This statement will not execute successfully because it is redundant to use TO_CHAR and then immediately convert it back to a date with TO_DATE without specifying a proper format mask.
D: This statement is correct. It converts a string to a DATE using TO_DATE and then back to a string with TO_CHAR, without specifying a format which defaults to the session’s NLS_DATE_FORMAT.
E: This statement will not execute successfully because TO_CHAR is used incorrectly; the first argument must be of DATE data type when you're using a date format mask.
References for the TO_DATE and TO_CHAR functions and their proper usage can be found in the Oracle Database SQL Language Reference 12c documentation.
The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.
The table has two rows whose COST_LAST_MANE values are Anderson and Ausson.
Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the second?
SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;
SELECT REPLACE (TRIM(TRALING'son' FROM cust_last_name),'An','O') FROM customers;
SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM customers;
SELECT REPLACE (SUBSTR(cust_last_name,-3),'An','O') FROM customers;
The REPLACE function in Oracle SQL is used to replace occurrences of a specified substring with another substring. In this query, the inner REPLACE function call REPLACE(cust_last_name, 'son', '') removes the substring 'son' from cust_last_name. The outer REPLACE function call then replaces the substring 'An' with 'O'. For the given data, 'Anderson' would first be transformed to 'Ander' by the inner REPLACE, and then 'Ander' would be transformed to 'Oder' by the outer REPLACE. Similarly, 'Ausson' would first change to 'Aus' by the inner REPLACE, which is unaffected by the outer REPLACE.
Reference can be found in the Oracle Database SQL Language Reference documentation, which details the functionality of string functions, including REPLACE.
Examine the description of the employees table:
Examine these requirements:
1- Display the last name, date of hire and the number of years of service for each employee.
2. If the employee has been employed 5 or more years but less than 10, display -5+ years of service".
3. If the employee has been employed 10 or more years but less than 15, display "10+ years of
service".
4. If the employee has been employed 15 or more years, display "15-*- years of service".
5. If none of these conditions matches, display "<5 years of service".
6. Sort the results by the hire_date column.
Which statement satisfies all the requirements?
A)
B)
C)
D)
Option A
Option B
Option C
Option D
Option D is the correct SQL statement that satisfies all the requirements mentioned. The CASE statement correctly compares the hire_date to date intervals subtracted from the current date (SYSDATE) to determine the number of years of service. This CASE statement is also appropriately ordered to ensure that the first condition matched is the one returned, preventing overlapping of the conditions.
Here is how Option D works according to the requirements:
It selects the last_name and hire_date from the employees table.
The CASE statement is used to calculate the number of years of service and to display the appropriate message according to the intervals defined.
The ORDER BY hire_date clause ensures the results are sorted by the date of hire.
In Option D, the intervals are defined in the correct order, ensuring that the first true condition is the one that is used:
The first WHEN checks if the hire date is 15 or more years ago.
The second WHEN checks if it is 10 or more years ago.
The third WHEN checks if it is 5 or more years ago.
The ELSE clause covers any hire dates less than 5 years ago.
The correct syntax for the intervals is SYSDATE - hire_date >= TO_YMINTERVAL('15-0') and similarly for the 10 and 5 years intervals.
Options A, B, and C are incorrect because they have various issues, such as incorrect ordering of the CASE statement's conditions, which could lead to incorrect results due to overlapping intervals, or the use of the TO_YMINTERVAL function that may not properly cover the intended date ranges.
References:
Oracle Documentation on CASE Expressions: SQL Language Reference - CASE Expression
Oracle Documentation on TO_YMINTERVAL Function: SQL Language Reference - TO_YMINTERVAL
Oracle Documentation on ORDER BY Clause: SQL Language Reference - ORDER BY Clause
Therefore, Option D is the statement that fulfills all the requirements for displaying the number of years of service based on the employee's hire date and ordered by the hire date.
Copyright © 2014-2025 Certensure. All Rights Reserved