1Z0-071 ORACLE SQL (part 1)

Examine this statement: Select cust_id, cust_last_name "Last name" FROM customers WHERE country_id = 10 UNION SELECT cust_id CUST_NO, cust_last_name FROM customers WHERE country_id = 30. ORDER BY "Last name". ORDER BY 2, cust_id. ORDER BY CUST_NO. ORDER BY 2, 1. ORDER BY "CUST_NO".

Which statement is true regarding external tables?. The CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the database from an external table. The data and metadata for an external table are stored outside the database. The default REJECT LIMIT for external tables is UNLIMITED. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.

Which two statements are true regarding roles? (Choose two.). A role can be granted to itself. A role can be granted to PUBLIC. A user can be granted only one role at any point of time. The REVOKE command can be used to remove privileges but not roles from other users. Roles are named groups of related privileges that can be granted to users or other roles.

Evaluate the following statement. Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.

You issue the following command to drop the PRODUCTS table: SQL > DROP TABLE products; Which three statements are true about the implication of this command? (Choose three.). All data along with the table structure is deleted. A pending transaction in the session is committed. All indexes on the table remain but they are invalidated. All views and synonyms on the table remain but they are invalidated. All data in the table is deleted but the table structure remains.

View the Exhibit and examine the structure of CUSTOMERS table. Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed. Which SQL statement would produce the required result?. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;. SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT".

Evaluate the following two queries: Which statement is true regarding the above two queries?. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column. There would be no change in performance. Performance would degrade in query 2. Performance would improve in query 2.

Examine the business rule: Each student can work on multiple projects and each project can have multiple students. You must design an Entity Relationship (ER) model for optimal data storage and allow for generating reports in this format: STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true? (Choose two.). The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.

The BOOKS_TRANSACTIONS table exists in your database. SQL>SELECT * FROM books_transactions ORDER BY 3; What is the outcome on execution?. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name. Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column. Rows are displayed in the order that they are stored in the table only for the first three rows. Rows are displayed sorted in ascending order of the values in the third column in the table.

View the exhibit and examine the structure of the EMPLOYEES table. You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees. Which SQL statement would you execute?. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE m.manager_id = 100;. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE e.manager_id = 100;. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON e.employee_id = m.manager_id WHERE m.manager_id = 100;. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e WHERE m.employee_id = e.manager_id AND e.manager_id = 100.

Which three statements are true about multiple-row subqueries?. They can contain a subquery within a subquery. They can return multiple columns as well as rows. They cannot contain a subquery within a subquery. They can return only one column but multiple rows. They can contain group functions and GROUP BY and HAVING clauses. They can contain group functions and the GROUP BY clause, but not the HAVING clause.

Evaluate the following SQL statements that are issued in the given order: CREATE TABLE emp (emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, ename VARCHAR2(15), salary NUMBER (8,2), mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no)); ALTER TABLE emp DISABLE CONSTRAINT emp_emp_no_pk CASCADE; ALTER TABLE emp ENABLE CONSTRAINT emp_emp_no_pk; What would be the status of the foreign key EMP_MGR_PK?. It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it. It would remain disabled and has to be enabled manually using the ALTER TABLE command. It would be automatically enabled and immediate. It would be automatically enabled and deferred.

Which statement is true regarding the default behavior of the ORDER BY clause?. In a character sort, the values are case-sensitive. NULL values are not considered at all by the sort operation. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.

Examine the structure of the MEMBERS table. Which query can be used to display the last names and city names only for members from the states MO and MI?. SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';. SELECT last_name, city FROM members WHERE state LIKE 'M%';. SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');. SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';.

Which statement is true about an inner join specified in a query’s WHERE clause?. It only applies for equijoin conditions. It applies for equijoin and nonequijoin conditions. It requires column names to be the same in all tables being joined. It must have primary-key and foreign-key constraints defined on the join columns.

Examine the structure of the BOOKS_TRANSACTIONS table: You want to display the member IDs, due date, and late fee as $2 for all transactions. Which SQL statement must you execute?. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS;. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS;. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;.

Evaluate the following SQL statement: SELECT product_name || 'it's not available for order' FROM product_information WHERE product_status = 'obsolete'; You received the following error while executing the above query: ERROR ORA-01756: quoted string not properly terminated What would you do to execute the query successfully?. Remove the single quotation marks enclosing the character literal string in the SELECT clause. Use the escape character to negate the single quotation mark within the literal character string in the SELECT clause. Enclose the character literal string in the SELECT clause within double quotation marks. Use the Oracle (q) operator and delimiter to allow the use of a single quotation mark within the literal character string in the SELECT clause.

View the exhibit and examine the ORDERS table. The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?. ALTER TABLE orders MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);. ALTER TABLE orders ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);. ALTER TABLE orders MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);. ALTER TABLE orders ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;.

View the exhibit and examine the description of the PRODUCT_INFORMATION table. Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?. SELECT COUNT (DISTINCT list_price) FROM product_information WHERE list_price is NULL. SELECT COUNT (NVL(list_price, 0)) FROM product_information WHERE list_price is NULL. SELECT COUNT (list_price) FROM product_information WHERE list_price i= NULL. SELECT COUNT (list_price) FROM product_information WHERE list_price is NULL.

Which three tasks can be performed using SQL functions built into Oracle Database?. displaying a date in a nondefault format. finding the number of characters in an expression. substituting a character string in a text expression with a specified string. combining more than two columns or expressions into a single column in the output.

You are designing the structure of a table in which two columns have the specifications: COMPONENT_ID – must be able to contain a maximum of 12 alphanumeric characters and must uniquely identify the row EXECUTION_DATETIME – contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components. Which two options define the data types that satisfy these requirements most efficiently? (Choose two.). The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type. The EXECUTION_DATETIME must be of TIMESTAMP data type. The EXECUTION_DATETIME must be of DATE data type. The COMPONENT_ID must be of ROWID data type. The COMPONENT_ID must be of VARCHAR2 data type. The COMPONENT_ID column must be of CHAR data type.

You want to display the date for the first Monday of the next month and issue the following command: What is the outcome?. In generates an error because rrrr should be replaced by rr in the format string. It executes successfully but does not return the correct result. It executes successfully and returns the correct result. In generates an error because TO_CHAR should be replaced with TO_DATE. In generates an error because fm and double quotation marks should not be used in the format string.

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables: You want to generate a list of all department IDs along with any course IDs that may have been assigned to them. Which SQL statement must you use?. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id);. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id);. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d. department_id);. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d. department_id);.

QUESTION 27 Which statement correctly grants a system privilege?. GRANT CREATE VIEW ON table1 TO user1;. GRANT ALTER TABLE TO PUBLIC;. GRANT CREATE TABLE TO user1, user2;. GRANT CREATE SESSION TO ALL;.

Which statement is true about transactions?. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction. Each Data Definition Language (DDL) statement executed forms a single transaction. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.

View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables. You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order. Which CREATE VIEW statement would create the view successfully?. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;. CREATE OR REPLACE VIEW ord_vu (order_id, order_date) AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) || "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date WHITH CHECK OPTION;.

Which statement is true about an inner join specified in the WHERE clause of a query?. It must have primary-key and foreign-key constraints defined on the columns used in the join condition. It requires the column names to be the same in all tables used for the join conditions. It is applicable for equijoin and nonequijoin conditions. It is applicable for only equijoin conditions.

Which statement is true regarding the INTERSECT operator?. The names of columns in all SELECT statements must be identical. It ignores NULL values. Reversing the order of the intersected tables alters the result. The number of columns and data types must be identical for all SELECT statements in the query.

The first DROP operation is performed on PRODUCTS table using this command: DROP TABLE products PURGE; Then a FLASHBACK operation is performed using this command: FLASHBACK TABLE products TO BEFORE DROP; Which is true about the result of the FLASHBACK command?. It recovers only the table structure. It recovers the table structure, data, and the indexes. It recovers the table structure and data but not the related indexes. It is not possible to recover the table structure, data, or the related indexes.

Evaluate the following query: SQL> SELECT TRUNC (ROUND(156.00, -1),-1) FROM DUAL; What would be the outcome?. 150. 200. 160. 16. 100.

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 need to display customers' second names where the second name starts with "Mc" or "MC". Which query gives the required output?. SELECT SUBSTR(cust_name, INSTR (cust_name, ' ')+1) FROM customers WHERE SUBSTR(cust_name, INSTR (cust_name, ' ')+1) LIKE INITCAP ('MC%');. SELECT SUBSTR(cust_name, INSTR (cust_name, ' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) = 'Mc';. SELECT SUBSTR(cust_name, INSTR (cust_name, ' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) LIKE 'Mc%';. SELECT SUBSTR(cust_name, INSTR (cust_name, ' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) = INITCAP ('MC%');.

Which two statements are true regarding the USING and ON clauses in table joins?. Both USING and ON clauses can be used for equijoins and nonequijoins. A maximum of one pair of columns can be joined between two tables using the ON clause. The ON clause can be used to join tables on columns that have different names but compatible data types. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.

Which three statements are true regarding group functions? (Choose three.). They can be used on columns or expressions. They can be passed as an argument to another group function. They can be used only with a SQL statement that has the GROUP BY clause. They can be used on only one column in the SELECT clause of a SQL statement. They can be used along with the single-row function in the SELECT clause of a SQL statement.

The BOOKS_TRANSACTIONS table exists in your schema in this database. You execute this SQL statement when connected to your schema in your database instance. SQL> SELECT * FROM books_transactions ORDER BY 3; What is the result?. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name. All table rows are displayed sorted in ascending order of the values in the third column. The first three rows in the table are displayed in the order that they are stored. Only the three rows with the lowest values in the key column are displayed in the order that they are stored.

Which statement is true about Data Manipulation Language (DML)?. DML automatically disables foreign ley constraints when modifying primary key values in the parent table. Each DML statement forms a transaction by default. A transaction can consist of one or more DML statements. DML disables foreign key constraints when deleting primary key values in the parent table, only when the ON DELETE CASCADE option is set for the foreign key constraint.

View the exhibit and examine the structure of the PROMOTIONS table. You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the ‘INTERNET’ category. Which query would give you the required output?. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date) FROM promotions) AND promo_category= ‘INTERNET’;. SELESELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category= ‘INTERNET’);. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = ‘INTERNET’);. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date> ANY (SELECT promo_begin_date FROM promotions WHERE promo_category= ‘INTERNET’);.

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result?. SELECT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers. SELECT DISTINCT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS: You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses. Which SQL statement must you use?. SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id). SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN .department_details d ON (c.depatrment_id=d.department_id). SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=d. department_id). SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>d. department_id).

Evaluate the following statement. Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?. Each row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent when clauses. All rows are evaluated by all the three WHEN clauses. Each row is evaluated by the first WHEN clause and if the condition is true, then the row would be evaluated by the subsequent when clauses. The INSERT statement will return an error because the ELSE clause is missing.

Which two statements are true regarding the SQL GROUP BY clause? (Choose two.). You can use a column alias in the GROUP BY clause. Using the WHERE clause after the GROUP BY clause excludes rows after creating groups. The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause. Using the WHERE clause before the GROUP BY clause excludes rows before creating groups. If the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause.

You execute the SQL statement: What is the outcome?. It fails because the NOT NULL and DEFAULT options cannot be combined for the same column. It It succeeds and CITY can contain only ‘SEATTLE’ or null for all rows. It fails because the condition for the CNAMES constraint is not valid. It succeeds and an index is created for CITIZEN_ID.

Evaluate the following CREATE TABLE commands: CREATE_TABLE orders (ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY, ord_date DATE, cust_id NUMBER (4) ); CREATE TABLE ord_items (ord _no NUMBER (2), item_no NUMBER(3), qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200), expiry_date date CHECK (expiry_date> SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no), CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) ); The above command fails when executed. What could be the reason?. SYSDATE cannot be used with the CHECK constraint. The BETWEEN clause cannot be used for the CHECK constraint. The CHECK constraint cannot be placed on columns having the DATE data type. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Examine these SQL statements that are executed in the given order: What will be the status of the foreign key EMP_MGR_FK?. It will be enabled and immediate. It will be enabled and deferred. It will remain disabled and can be re-enabled manually. It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

View the Exhibit and examine the description of the ORDERS table. Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.). WHERE order_date_IN ( TO_DATE('OCT 21 2003','MON DD YYYY'), TO_CHAR('NOV 21 2003','MON DD YYYY') ). WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY'). WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY').

Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.). Finding the lowest value. Raising to a power. Finding the quotient. Subtraction. Addition.

You must create a SALES table with these column specifications and data types: (Choose the best answer.) SALESID: Number STOREID: Number ITEMID: Number QTY: Number, should be set to 1 when no value is specified SLSDATE: Date, should be set to current date when no value is specified PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified Which statement would create the table?. CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT = 1, slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT = "CASH");. CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT 1, slsdate DATE DEFAULT 'SYSDATE', payment VARCHAR2(30) DEFAULT CASH);. CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT = 1, slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT = "CASH");. CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT 1,slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT 'CASH');.

View the Exhibit and examine the details of the PRODUCT_INFORMATION table. Evaluate this SQL statement: SELECT TO_CHAR (list_price, '$9,999') From product_information; Which two statements are true regarding the output? (Choose two.). A row whose LIST_PRICE column contains value 11235.90 would be displayed as #######. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,123. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124. A row whose LIST_PRICE column contains value 11235.90 would be displayed as $1,123.

Examine the structure of the ORDERS table: You want to find the total value of all the orders for each year and issue this command: SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders GROUP BY TO_CHAR(order_date, 'yyyy'); Which statement is true regarding the result? (Choose the best answer.). It executes successfully but does not give the correct output. It executes successfully and gives the correct output. It returns an error because the TO_CHAR function is not valid. It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

View the Exhibit and examine the structure of the ORDER_ITEMS table You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table. Which query would produce the desired result?. SELECT order_id FROM order_items GROUP BY order_id HAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity)) FROM order_items GROUP BY order_id);. SELECT order_id FROM order_items WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) GROUP BY order_id;. SELECT order_id FROM order_items WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id;. SELECT order_id FROM order_items WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP BY order_id).

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. (Choose two.) Evaluate this SQL statement: ALTER TABLE emp DROP COLUMN first_name; Which two statements are true?. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement. The FIRST_NAME column would be dropped provided at least one column remains in the table. The FIRST_NAME column would be dropped provided it does not contain any data. The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.

Which two statements best describe the benefits of using the WITH clause? (Choose two.). It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query. It enables sessions to store a query block permanently in memory and use it to create complex queries. It enables sessions to store the results of a query permanently.

Which three statements are true regarding subqueries? (Choose three.). The ORDER BY Clause can be used in a subquery. A subquery can be used in the FROM clause of a SELECT statement. If a subquery returns NULL, the main query may still return rows. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

Which two statements are true regarding single row functions? (Choose two.). MOD : returns the quotient of a division. TRUNC : can be used with NUMBER and DATE values. CONCAT : can be used to combine any number of values. SYSDATE : returns the database server current date and time. INSTR : can be used to find only the first occurrence of a character in a string. TRIM : can be used to remove all the occurrences of a character from a string.

View the Exhibit and examine the structure of the ORDERS table. You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101. Which query would give you the desired result?. SELECT order_id, order_date FROM orders WHERE order_date > ANY (SELECT order_date FROM orders WHERE customer_id = 101);. SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT MAX(order_date) FROM orders ) AND customer_id = 101;. SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101);. SELECT order_id, order_date FROM orders WHERE order_date > IN (SELECT order_date FROM orders WHERE customer_id = 101);.

Which two statements are true regarding subqueries? (Choose two.). A subquery can appear on either side of a comparison operator. Only two subqueries can be placed at one level. A subquery can retrieve zero or more rows. A subquery can be used only in SQL query statements. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Which two statements are true regarding the execution of the correlated subqueries? (Choose two.). The nested query executes after the outer query returns the row. The nested query executes first and then the outer query executes. The outer query executes only once for the result returned by the inner query. Each row returned by the outer query is evaluated for the results returned by the inner query.

Which two statement are true regarding table joins available in the Oracle Database server? (Choose two.). You can use the ON clause to specify multiple conditions while joining tables. You can explicitly provide the join condition with a NATURAL JOIN. You can use the JOIN clause to join only two tables. You can use the USING clause to join tables on more than one column.

View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best answer.) The PROJ_TASK_DETAILS table stores information about project tasks and the relation between them. The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of other tasks. You must generate a report listing all task IDs, the task ID of any task upon which it depends and the name of the employee in charge of the task upon which it depends. Which query would give the required result?. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.task_id = d.task_id);. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.based_on = d.task_id);. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);.

View the exhibit and examine the description of the EMPLOYEES table. (Choose two.) You executed this SQL statement: SELECT first_name, department_id, salary FROM employees ORDER BY department_id, first_name, salary desc; Which two statements are true regarding the result? (Choose two.). The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column. The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column. The values in all columns would be returned in descending order. The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

Which two statements are true regarding constraints? (Choose two.). All constraints can be defined at the table or column level. A constraint can be disabled even if the constrained column contains data. A column with a UNIQUE constraint can contain a NULL value. A column with a FOREIGN KEY constraint can never contain a NULL value. Constraints are enforced only during INSERT operations.

Examine the structure of the PROMOTIONS table: (Choose the best answer.) Management requires a report of unique promotion costs in each promotion category. Which query would satisfy this requirement?. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1. SELECT promo_category, DISTINCT promo_cost FROM promotions. SELECT DISTINCT promo_cost, promo_category FROM promotions. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;.

You must create a table for a banking application. One of the columns in the table has these requirements: 1: A column to store the duration of a short team loan 2: The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions. 3: The maximum loan period is 30 days. 4: Interest must be calculated based on the number of days for which the loan remains unpaid. Which data type would you use?. DATE. NUMBER. TIMESTAMP. INTERVAL DAY TO SECOND. INTERVAL YEAR TO MONTH.

Examine the structure of the CUSTOMERS table CUSTNO is the PRIMARY KEY. You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names. Which two methods can you use to get the required result? (Choose two.). Subquery. Self-join. Self-join. Left outer-join with self-join. Right outer-join with self-join.

Which two are the minimal requirements for a self-join? (Choose two.). Only equijoin conditions may be used in the query. Outer joins must not be used in the query. There must be a condition on which the self-join is performed. No other condition except the self-join may be specified. The table used for the self-join must have two different alias names in the query.

Examine the SQL statement used to create the TRANSACTION table. SQL > CREATE TABLE transaction (trn_id char(2) primary key, Start_date date DEFAULT SYSDATE, End_date date NOT NULL); The value 'A1' does not exist for trn_id in this table. Which SQL statement successfully inserts a row into the table with the default value for START_DATE?. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10)). INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10')). INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014'). INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-2014').

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.). SELECT TO_CHAR (1890.55, '$99G999D00') FROM DUAL. SELECT TO_CHAR (1890.55, '$9,999V99') FROM DUAL;. SELECT TO_CHAR (1890.55, '$0G000D00') FROM DUAL;. SELECT TO_CHAR (1890.55, '$99,999D99') FROM DUAL;. SELECT TO_CHAR (1890.55, '$99G999D99') FROM DUAL.

You must write a query that prompts users for column names and conditions every time it is executed. The user must be prompted only once for the table name. Which statement achieves those objectives?. SELECT &col1, '&col2' FROM &table WHERE &&condition = '&cond';. SELECT &col1, &col2 FROM "&table" WHERE &condition = &cond;. SELECT &col1, &col2 FROM &&table WHERE &condition = &cond;. SELECT &col1, &col2 FROM &&table WHERE &condition = &&cond.

Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.). The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query. The WHERE clause can be used to exclude rows after dividing them into groups. The HAVING clause can be used with aggregate functions in subqueries. The WHERE clause can be used to exclude rows before dividing them into groups.

You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and LOGIN_ID must be unique and not null. Which two SQL statements would create the required table? (Choose two.). CREATE TABLE employees (employee_id NUMBER, login_id NUMBER, employee_name VARCHAR2(100), hire_date DATE, CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id));. (employee_id NUMBER, login_id NUMBER, employee_name VARCHAR2(25), hire_date DATE, CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));. CREATE TABLE employees (employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY, login_id NUMBER UNIQUE, employee_name VARCHAR2(25), hire_date DATE);. CREATE TABLE employees (employee_id NUMBER, login_id NUMBER, employee_name VARCHAR2(100), hire_date DATE, CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id); CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id));. CREATE TABLE employees (employee_id NUMBER CONSTRAINT emp_id_nn NOT NULL, login_id NUMBER CONSTRAINT login_id_nn NOT NULL, employee_name VARCHAR2(100),hire_date DATE, CONSTRAINT emp_num_id_uk UNIQUE (employee_id, login_id));.

Examine the types and examples of relationship that follow: 1 One-to-one a) teacher to Student 2 One-to-many b) Employees to Manager 3 Many-to-one c) Person to SSN 4 Many-to-many d) Customers to Products Which option indicates correctly matched relationships?. 1-d, 2-b, 3-a, and 4-c. 1-c, 2-d, 3-a, and 4-b. 1-a, 2-b, 3-c, and 4-d. 1-c, 2-a, 3-b, and 4-d.

Which three statements are true reading subqueries? (Choose three.). A Main query can have many subqueries. A subquery can have more than one main query. The subquery and main query must retrieve date from the same table. The subquery and main query can retrieve data from different tables. Only one column or expression can be compared between the subquery and main query. Multiple columns or expressions can be compared between the subquery and main query.

Which two statements are true regarding multiple-row subqueries? (Choose two.). They can contain group functions. They always contain a subquery within a subquery. They use the < ALL operator to imply less than the maximum. They can be used to retrieve multiple rows from a single table only. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.

View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables. The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company. You need to find those customers who have never changed their address. Which SET operator would you use to get the required output?. INTERSECT. UNION ALL. MINUS. UNION.

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables. You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered: SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id; What would happen when the above statement is executed?. The statement would execute successfully to produce the required output. The statement would not execute because inline views and outer joins cannot be used together. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query. The statement would not execute because the GROUP BY clause cannot be used in the inline.

Which statement is true regarding the UNION operator?. By default, the output is not sorted. Null values are not ignored during duplicate checking. Names of all columns must be identical across all select statements. The number of columns selected in all select statements need not be the same.

QUESTION 84 Examine the create table statements for the stores and sales tables. SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE); SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id)); You executed the following statement: SQL> DELETE from stores WHERE store_id=900; The statement fails due to the integrity constraint error: ORA-02292: integrity constraint (HR.STORE_ID_FK) violated Which three options ensure that the statement will execute successfully? (Choose three.). Disable the primary key in the STORES table. Use CASCADE keyword with DELETE statement. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table. Disable the FOREIGN KEY in SALES table and then delete the rows. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.

In the customers table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'Abigail'. Evaluate the following query: What would be the outcome?. Abigail PA. Abigail Pa. Abigail IS. An error message.

Which two statements are true regarding constraints?. A foreign key column cannot contain null values. A column with the UNIQUE constraint can contain null values. A constraint is enforced only for INSERT operation on the table. A constraint can be disabled even if the constraint column contains data. All constraints can be defined at the column level and at the table level.

On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command: $> sqlldr hr/hr@pdb table=employees Which two statements are true regarding the command? (Choose two.). It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database. It fails because no SQL *Loader data file location is specified. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege. It fails because no SQL *Loader control file location is specified.

Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?. By default, EM express is available for a database after database creation. You can use EM express to manage multiple databases running on the same serv. You can perform basic administrative tasks for pluggable databases by using the EM express interface. You cannot start up or shut down a database Instance by using EM express. You can create and configure pluggable databases by using EM express.

You need to produce a report where each customer’s credit limit has been incremented by $1000. In the output, the customer’s last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase. Which statement would accomplish this requirement?. SELECT cust_last_name AS “Name”, cust_credit_limit + 1000 AS “New Credit Limit” FROM customers;. SELECT cust_last_name AS Name, cust_credit_limit + 1000 AS New Credit Limit FROM customers;. SELECT cust_last_name AS Name, cust_credit_limit + 1000 “New Credit Limit” FROM customers;. SELECT INITCAP (cust_last_name) “Name”, cust_credit_limit + 1000 INITCAP (“NEW CREDIT LIMIT”) FROM customers;.

SCOTT is a user in the database. Evaluate the commands issued by the DBA: Which statement is true regarding the execution of the above commands?. Statement 1 would not execute because the WITH GRANT option is missing. Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command. Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement. Statement 1 would not execute because the IDENTIFIED BY <password> clause is missing.

Examine the structure proposed for the TRANSACTIONS table: Which two statements are true regarding the storage of data in the above table structure? (Choose two.). The CUST_CREDIT_VALUE column would allow storage of positive and negative integers. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.

Examine the structure of the MARKS table: Which two statements would execute successfully? (Choose two.). SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) FROM marks WHERE subject1 > subject2;. SELECT student_name subject1 FROM marks WHERE subject1 > AVG(subject1);. SELECT SUM(subject1+su bject2+subject3) FROM marks WHERE student_name IS NULL;. SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE ‘R%’;.

LEFT OUTER JOIN. JOIN. NATURAL JOIN. RIGHT OUTER JOIN. FULL OUTER JOIN.

In the EMPLOYEES table there are 1000 rows and employees are working in the company for more than 10 years. Evaluate the following SQL statement: What would be the result?. It executes successfully but no rows updated. It executes successfully and updates the records of those employees who have been working in the company for more than 600 days. It gives an error because multiple NVL functions are used in an expression. It gives an error because NVL function cannot be used with UPDATE.

Which statement is true regarding the SESSION_PRIVS dictionary view?. It contains the object privileges granted to other users by the current user session. It contains the system privileges granted to other users by the current user session. It contains the current object privileges available in the user session. It contains the current system privileges available in the user session.

Which three statements indicate the end of a transaction? (Choose three.). after a CREATE statement is issued. after a SAVEPOINT is issued. after a SELECT statement is issued. after a ROLLBACK is issued. after a COMMIT is issued.

View the Exhibit and examine the structure of the CUSTOMERS table. Which statement is true regarding the outcome of the above query?. It returns an error because the BETWEEN operator cannot be used in the HAVING clause. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column. It executes successfully.

View the Exhibit and examine the details of the ORDER_ITEMS table. Evaluate the following SQL statements: Statement 1: SELECT MAX(unit_price*quantity) “Maximum Order” FROM order_items; Statement 2: SELECT MAX(unit_price*quantity) “Maximum Order” FROM order_items GROUP BY order_id; Which statements are true regarding the output of these SQL statements? (Choose all that apply.). Statement 2 would return multiple rows of output. Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns. Statement 1 would not return give the same output. Both the statements would give the same output. Statement 1 would return only one row of output.

Examine the description of the EMP_DETAILS table given below: Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL TABLE?. An EMP_IMAGE column cannot be included in the ORDER BY clause. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column. You cannot add a new column to the table with LONG as the data type. An EMP_IMAGE column can be included in the GROUP BY clause.

The PRODUCTS table has the following structure. Evaluate the following two SQL statements: SQL>SELECT prod_id, NVL2 (prod_expiry_date, prod_expiry_date + 15, ‘ ‘) FROM products; SQL>SELECT prod_id, NVL (prod_expiry_date, prod_expiry_date + 15) FROM products; Which statement is true regarding the outcome?. Both the statements execute and give different results. Only the second SQL statement executes successfully. Both the statements execute and give the same result. Only the first SQL statement executes successfully.

You executed the following CREATE TABLE statement that resulted in an error: SQL> CREATE TABLE employees(emp_id NUMBER(10) PRIMARY KEY, ename VARCHAR2(20), email NUMBER(3) UNIQUE, address VARCHAR2 (500), phone VARCHAR2(20), resume LONG, hire_date DATE, remarks LONG, dept_id NUMBER(3) CONSTRAINT emp_dept_id_fk REFERENCES departments (dept_id), CONSTRAINT ename_nn NOY NULL(ename)); Identify two reasons for the error. The NOT NULL constraint on the ENAME column must be defined as the column level. FOREIGN KEY defined on the DEPT_ID column must be at the table level only. Only one LONG column can be used per table. The FOREIGN KEY keyword is missing in the constraint definition. The PRIMARY KEY constraint in the EMP_ID column must have a name and must be defined at the table level only.

View the Exhibit and examine the structure of the CUSTOMERS table. You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10,000. Evaluate the following two queries: SQL> SELECT cust_last_name, cust_credit_limit FROM customers WHERE (UPPER(cust_last_name) LIKE ‘A%’ OR UPPER (cust_last_name) LIKE ‘B%’ OR UPPER (cust_last_name) LIKE ‘C%’) AND cust_credit_limit < 10000; SQL>SELECT cust_last_name, cust_credit_limit FROM customers WHERE UPPER (cust_last_name) BETWEEN ‘A’ AND ‘C’ AND cust_credit_limit < 10000; Which statement is true regarding the execution of the above queries?. Only the second query gives the correct result. Both execute successfully but do not give the required result. Only the first query gives the correct result. Both execute successfully and give the same result.

Evaluate the following CREATE TABLE commands: CREATE_TABLE orders (ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY, ord_date DATE, cust_id NUMBER (4)); CREATE TABLE ord_items (ord _no NUMBER (2), item_no NUMBER(3), qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200), expiry_date date CHECK (expiry_date> SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no), CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) ); Why would the ORD_ITEMS table not get created?. SYSDATE cannot be used with the CHECK constraint. The BETWEEN clause cannot be used twice for the same table. The CHECK constraint cannot be placed on columns having the DATE data type. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

View the Exhibit and examine the structure of the PRODUCT table. Which two tasks would require subqueries? (Choose two.). display all products whose minimum list price is more than the average list price of products having the status ‘orderable’. display the total number of products supplied by supplier 102 and have product status as ‘OBSOLETE’. display the number of products whose list prices are more than the average list price. display all suppliers whose list price is more than 1000. display the minimum list price for each product status.

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cust_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?. INSERT INTO orders VALUES(1, ’10-mar-2007’, ‘direct’, (SELECT customer_id FROM customers WHERE cust_last_name=’Roberts’ AND credit_limit=600), 1000);. INSERT INTO orders (order_id, order_data, order_mode, (SELECT customer_id FROM customers WHERE cust_last_name=’Roberts’ AND credit_limit=600), order_total) VALUES(1, ’10-mar-2007’, ‘direct’, &&customer_id, 1000);. INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name=’Roberts’ AND c.credit_limit=600 ) VALUES (1, ’10-mar-2007’, ‘direct’, (SELECT customer_id FROM customers WHERE cust_last_name=’Roberts’ AND credit_limit=600), 1000);. INSERT INTO orders (order_id, order_data, order_mode, (SELECT customer_id FROM customers WHERE cust_last_name=’Roberts’ AND credit_limit=600), order_total) VALUES (1, ’10-mar-2007’, ‘direct’, &customer_id, 1000).

Examine the structure of the SHIPMENTS table: You want to generate a report that displays the PO_ID and the penalty amount to be paid if the SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day. Evaluate the following two queries: Which statement is true regarding the above commands?. Both execute successfully and give correct results. Only the first query executes successfully but gives a wrong result. Only the first query executes successfully and gives the correct result. Only the second query executes successfully but gives a wrong result. Only the second query executes successfully and gives the correct result.

View the Exhibit and examine the data in the PRODUCTS table. Which statement would add a column called PRICE, which cannot contain NULL?. ALTER TABLE products ADD price NUMBER(8,2) NOT NULL;. ALTER TABLE products ADD price NUMBER(8,2) DEFAULT NOT NULL;. ALTER TABLE products ADD price NUMBER(8,2) DEFAULT 0 NOT NULL;. ALTER TABLE products ADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL.

Which statement is true about the Oracle SQL, DELETE and TRUNCATE statements?. DELTE and TRUNCATE statements can have a rollback done to restore data into a table. DELETE and TRUNCATE statements remove all indexes for the tables on which they are performed. DELETE but not TRUNCATE statement can be used to remove data from selective columns and rows of a table. DELETE but not TRUNCATE statement can be used to selectively remove rows from a table.

View the Exhibit and examine the structure of the ORDERS table. The columns ORDER_MODE and ORDER_TOTAL have the default values ‘direct’ and 0 respectively. Which two INSERT statements are valid? (Choose two.). INSERT INTO orders VALUES (1,’09-mar-2007’, ‘online’,’’, 1000);. INSERT INTO orders (order_id,order_date,order_mode, (customer_id,order_total) VALUES (1,TO_DATE(NULL), ‘online’, 101, NULL);. INSERT INTO (SELECT order_id,order_date,customer_id FROM orders) VALUES (1,’09-mar-2007’, 101);. INSERT INTO orders VALUES (1,’09-mar-2007’, DEFAULT, 101, DEFAULT);. INSERT INTO orders (order_id,order_date,order_mode,order_total) VALUES (1,’10-mar-2007’,‘online’,1000);.

Which two statements are true? (Choose two.). The USER_SYNONYMS view can provide information about private synonyms. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary. All the dynamic performance views prefixed with v$ are accessible to all the database users. The USER_OBJECTS view can provide information about the tables and views created by the user who queries the view. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.

What is the primary difference between the relational database (RDB) and object-oriented database (OODB) models?. OODB supports multiple objects in the same database, whereas RDB supports only tables. RDB supports only E.F. Codd’s rules, whereas OODB does not support them. OODB incorporates methods with data structure definition, whereas RDB does not allow this. RDB allows the definition of relationships between different tables, whereas OODB does not allow this.

Examine the command to create the BOOKS table. It executes successfully and the row is inserted with a rule PUBLISHER_ID. It executes successfully only if NULL is explicitly specified in the INSERT statement. It executes successfully only if the PUBLISHER_ID column name is added to the columns list in the INSERT statement. It executes successfully only if the PUBLISHER_ID column name is added to the columns list and NULL is explicitly specified in the INSERT statement.

You need to list the employees in DEPARTMENT_ID 20 days in a single row, ordered by HIRE_DATE. Examine the sample output: Which query will provide the required output?. SELECT LISTAGG(last_name) WITHIN GROUP ORDER BY (hire_date) “Emp_list”, MIN(hire_date) “Earliest” FROM employees WHERE department_id = 30;. SELECT LISTAGG(last_name, '; ') WITHIN GROUP ORDER BY (hire_date) “Emp_list”, MIN(hire_date) “Earliest” FROM employees WHERE department_id = 30;. SELECT LISTAGG(last_name, '; ') “Emp_list”, MIN(hire_date) “Earliest” FROM employees WHERE department_id = 30; WITHIN GROUP ORDER BY (hire_date);. SELECT LISTAGG(last_name, '; ') “Emp_list”, MIN(hire_date) “Earliest” FROM employees WHERE department_id = 30; ORDER BY (hire_date);.

Examine the structure of the DEPARTMENTS table. Synonyms existing of the DEPARTMENTS table would have to be re-created. Unique key constraints defined on the COUNTRY column are removed. Views created in the DEPARTMENTS table that include the COUNTRY column are automatically modified and remain valid. Indexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is executed. A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command.

View the exhibit and examine the description of SALES and PROMOTIONS tables. You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values. Which three DELETE statements are valid? (Choose three.). DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promo_name = ‘blowout sale’) AND promo_id = (SELECT promo_id FROM promotions WHERE promo_name = ‘everyday low price’) FROM promotions WHERE promo_name = ‘everyday low price’);. DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = ‘blowout sale’) OR promo_id = (SELECT promo_id FROM promotions WHERE promo_name = ‘everyday low price’). DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = ‘blowout sale’) OR promo_name = ‘everyday low price’);. DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name IN = ‘blowout sale’,‘everyday low price’));.

You need to display the first names of all customers from the CUSTOMERS table that contain the character ‘e’ and have the character ‘a’ in the second last position. Which query would give the required output?. SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, ‘e’)<>0 AND SUBSTR(cust_first_name, -2, 1)=’a’;. SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, ‘e’)<>'' AND SUBSTR(cust_first_name, -2, 1)=’a’;. SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, ‘e’)IS NOT NULL AND SUBSTR(cust_first_name, 1, -2)=’a’;. SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, ‘e’)<>0 AND SUBSTR(cust_first_name, LENGTH(cust_first_name), -2)=’a’;.

Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.). DELETE can be used to remove rows from only one table in one statement. DELETE can be used to remove rows from multiple tables in one statement. DELETE can be used to remove rows only for tables that are parents for a child table that has a referential integrity constraint referring to the parent. DELETE can be used to remove data from specific columns as well as complete rows. DELETE and TRUNCATE can be used for tables that are parents for a child table that has a referential integrity constraint having an ON DELETE rule.

Which three statements are true about views in an Oracle Database? (Choose three.). Views can join tables only if they belong to the same schema. A view can be created that refers to a non-existent table in its defining query. Views have no object number. Views have no segment. Rows inserted into a table using a view are retained in the table if the view is dropped. A SELECT statement cannot contain a WHERE clause when querying a view containing a WHERE clause in its defining query.