How to pass Oracle 1z0-071 exam with the help of dumps?

Question # 1

Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY.Only the EMPLOYEE_ID column is indexed.Rows exist for employees 100 and 200.Examine this statement:Which two statements are true? (Choose two.)

A. Employees 100 and 200 will have the same JOB_ID as before the update command
B. Employees 100 will have JOB_ID set to the same value as the JOB_ID of employee 200
C. Employees 100 and 200 will have the same SALARY as before the update command
D. Employee 200 will have SALARY set to the same value as the SALARY of employee 100
E. Employee 100 will have SALARY set to the same value as the SALARY of employee 200
F. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100

Show Answer

Question # 2

Which three statements are true about single-row functions? (Choose three.)

A. They can be nested to any level
B. The data type returned can be different from the data type of the argument
C. They can accept only one argument
D. The argument can be a column name, variable, literal or an expression
E. They can be used only in the WHERE clause of a SELECT statement
F. They return a single result row per table

Show Answer

Question # 3

The ORDERS table a primary key constrain 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 CASCADS.There are rows in the ORDERS table with an ORDER_TOTAL of less than 1000.Which three DELETE statements execute successfully?

A. DELETE * FROM orders WHERE order_total < 1000;
B. DELETE FROM orders;
C. DELETE order_id FROM orders WHERE order_total < 1000;
D. DELETE orders WHERE order_total < 1000;
E. DELETE FROM orders WHERE order_total < 1000;

Show Answer

Question # 4

Which two statements are true about views?

A. Views can be updated without the need to re-grant privileges on the view.
B. Views can be indexed.
C. The with check clause prevents certain rows from being displayed when querying the view.
D. The with check clause prevents certain rows from being updated or inserted in the underlying table through the view.
E. Tables in the defining query of a view must always exist in order to create the view.

Show Answer

Question # 5

Which three are true about the CREATE TABLE command? (Choose three.)

A. It can include the CREATE..INDEX statement for creating an index to enforce the primary key constraint
B. It implicitly executes a commit
C. A user must have the CREATE ANY TABLE privilege to create tables
D. It implicitly rolls back any pending transactions
E. The owner of the table should have space quota available on the tablespace where the table is defined
F. The owner of the table must have the UNLIMITED TABLESPACE system privilege

Show Answer

Question # 6

Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)? (Choose two.)

A. Implementing a relationship between two tables might require joining additional tables
B. Relating data from a table with data from the same table is implemented with a self join
C. Rows from unrelated tables cannot be joined
D. Every relationship between the two tables must be implemented in a join condition
E. An inner join relates rows within the same table

Show Answer

Question # 7

Which two statements are true about external tables?

A. Their data can be retrieved by using only SQL or PL/SQL.
B. Their metadata and actual data are both stoned outside the database.
C. Indexes can be created on them.
D. You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command.
E. DML statements cannot be used on them.

Show Answer

Question # 8

Examine the description of the PRODUCTS table:Which three queries use valid expressions? (Choose three.)

A. SELECT product_id, (expiry_date – delivery_date) * 2 FROM products;
B. SELECT product_id, unit_price | | 5 “Discount”, unit_price + surcharge – discount FROM products;
C. SELECT product_id, unit_price, 5 “Discount”, unit_price + surcharge – discount FROM products;
D. SELECT product_id, unit_price, unit_price + surcharge FROM products;
E. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
F. SELECT product_id, expiry_date * 2 FROM products;

Show Answer

Question # 9

Which three statements are true regarding indexes? (Choose three.)

A. A SELECT statement can access one or more indices without accessing any tables
B. An update to a table can result in no updates to any of the table’s indexes
C. A table belonging to one user can have an index that belongs to a different user
D. A UNIQUE index can be altered to be non-unique
E. An update to a table can result in updates to any or all of the table’s indexes
F. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped

Show Answer

Question # 10

View the Exhibits and examine the structure of the costs and promotions tables.You want to display prod_ids whose promotion cost is less than the highest cost PR0D_ID in a promotion time interval.Examine this SQL statement:What will be the result?

A. It gives an error because the ROUP BY clause is not valid.
B. It executes successfully and gives the required result.
C. It gives an error because the all keyword is not valid.
D. It executes successfully but does not give the required result.

Show Answer

Question # 11

Examine the structure of the two tables.Which two queries execute successfully? (Choose two.)

A. Option A
B. Option B
C. Option C
D. Option D
E. Option E

Show Answer

Question # 12

Examine this query:SELECT TRUNC(ROUND(156.00, -2), -1) FROM DUAL;What is the result?

A. 16
B. 200
C. 100
D. 160
E. 150

Show Answer

Question # 13

Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.)

A. The output is sorted by the UNION ALL operator
B. The names of columns selected in each SELECT statement must be identical
C. The number of columns selected in each SELECT statement must be identical
D. Duplicates are eliminated automatically by the UNION ALL operator
E. NULLS are not ignored during duplicate checking

Show Answer

Question # 14

Which two statements are true regarding a SAVEPOINT? (Choose two.)

A. A SAVEPOINT does not issue a COMMIT
B. Only one SAVEPOINT may be issued in a transaction
C. Rolling back to a SAVEPOINT can undo a TRUNCATE statement
D. Rolling back to a SAEPOINT can undo a CREATE INDEX statement
E. Rolling back to a SAVEPOINT can undo a DELETE statement

Show Answer

Question # 15

Examine the structure of the MEMBERS table:You want to display details of all members who reside in states starting with the letter A followed by exactly one character.Which SQL statement must you execute?

A. SELECT * FROM MEMBERS WHERE state LIKE '%A_';
B. SELECT * FROM MEMBERS WHERE state LIKE 'A_';
C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
D. SELECT * FROM MEMBERS WHERE state LIKE 'A%';

Show Answer

Question # 16

Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

A. A sequence can issue duplicate values
B. A sequence’s unallocated cached value are lost if the instance shuts down
C. Sequences can always have gaps
D. Two or more tables cannot have keys generated from the same sequence
E. A sequence can only be dropped by a DBA
F. A sequence number that was allocated can be rolled back if a transaction fails

Show Answer

Question # 17

The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson.Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second?

A. SELECT REPLACE (TRIM(TRAILING ‘son’ FROM cust_last_name), ‘An’, ‘o’) FROM customers;
B. SELECT INITCAP (REPLACE(TRIM(‘son’ FROM cust_last_name), ‘An’, ‘o’)) FROM customers;
C. SELECT REPLACE (SUBSTR(cust_last_name, -3), ‘An’, ‘o’) FROM customers;
D. SELECT REPLACE (REPLACE(cust_last_name, ‘son’, ‘’), ‘An’, ‘o’) FROM customers;

Show Answer

Question # 18

Which three actions can you perform by using the ALTER TABLE command?

A. Lock a set of rows in a table.
B. Drop pseudocolumns from a table.
C. Rename a table.
D. Drop all columns simultaneously from a table.
E. Enable or disable constraints on a table.
F. Restrict all DML statements on a table.

Show Answer

Question # 19

View the Exhibit and examine the structure of the PRODUCT_INFORMATION table.You want to see the product names and the date of expiration of warranty for all the products, if the product is purchased today. The products that have no warranty should be displayed at the top and the products with maximum warranty period should be displayed at the bottom.Which SQL statement would you execute to fulfill this requirement?

A. SELECT product_name, SYSDATE+warranty_period AS “Warranty expire date”FROM product_informationORDER BY SYSDATE-warranty_period
B. SELECT product_name, SYSDATE+warranty_period AS “Warranty expire date”FROM product_informationORDER BY SYSDATE+warranty_period
C. SELECT product_name, SYSDATE+warranty_period AS “Warranty expire date”FROM product_informationORDER BY SYSDATE
D. SELECT product_name, SYSDATE+warranty_period AS “Warranty expire date”FROM product_informationWHERE warranty_period > SYSDATE

Show Answer

Question # 20

Which two statements are true regarding savepoints? (Choose two.)

A. Savepoints may be used to ROLLBACK.
B. Savepoints can be used for only DML statements.
C. Savepoints are effective only for COMMIT.
D. Savepoints are effective for both COMMIT and ROLLBACK.
E. Savepoints can be used for both DML and DDL statements.

Show Answer

Question # 21

Which three statements are true about inner and outer joins?

A. Outer joins can be used when there are multiple join conditions on two tables.
B. Outer joins can only be used between two tables per query.
C. A full outer join returns matched and unmatched rows.
D. An inner join returns matched rows.
E. A full outer join must use Oracle syntax.
F. A left or right outer join returns only unmatched rows.

Show Answer

Question # 22

Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.)

A. Insert statements can always be done on a table through a view.
B. 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.
C. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
D. Delete statements can always be done on a table through a view.
E. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
F. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.

Show Answer

Question # 23

Which three statements are true about dropping and unused columns in an Oracle database?

A. Partition key columns cannot be dropped.
B. A column that is set to unused still counts towards the limit of 1000 columns per table.
C. An unused column's space is reclaimed automatically when the row containing that column is next queried.
D. A drop column command can be rolled back.
E. A primary key column referenced by another column as a foreign key can be dropped if using the cascade option.
F. An unused column's space is reclaimed automatically when the block containing that column is next queried.

Show Answer

Question # 24

Examine this SQL statement:Identify three order by clauses, any one of which can complete the query successfully.

A. ORDER BY CUST_NO
B. ORDER BY 2, 1
C. ORDER BY 2, cust_id
D. ORDER BY "Last Name"
E. ORDER BY "CUST NO"

Show Answer

Question # 25

Which three statements are true about GLOBAL TEMPORARY TABLES?

A. A GLOBAL TEMPORARY TABLE can have multiple indexes.
B. A GLOBAL TEMPORARY TABLE cannot have a PUBLIC SYNONYM.
C. A trigger can be created on a GLOBAL TEMPORARY TABLE.
D. A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view.
E. A GLOBAL TEMPORARY TABLE can have only one index..
F. Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO

Show Answer

Question # 26

Which two statements are true about the SET VERIFY ON command? (Choose two.)

A. It can be used in SQL Developer and SQL*Plus
B. It displays values for variables used only in the WHERE clause of a query
C. It can be used only in SQL*Plus
D. It displays values for variables prefixed with &&
E. It displays values for variables created by the DEFINE command

Show Answer

Question # 27

Which three actions can you perform on an existing table containing data? (Choose three.)

A. Add a new NOT NULL column with a DEFAULT value
B. Change the default value of a column
C. Change a DATE column containing data to a NUMBER data type
D. Add a new column as the table’s first column
E. Define a default value that is automatically inserted into a column containing nulls
F. Increase the width of a numeric column

Show Answer

Question # 28

You need to calculate the number of days from 1st January 2019 until today.Dates are stored in the default format of DD-MON-RR.Which two queries give the required output?

A. SELECT TO_CHAR(SYSDATE, ‘DD-MON-YYYY’) – ’01-JAN-2019’ FROM DUAL;
B. SELECT SYSDATE – TO_DATE(’01-JANUARY-2019’) FROM DUAL;
C. SELECT ROUND(SYSDATE – ’01-JAN-2019’) FROM DUAL;
D. SELECT ROUND(SYSDATE – TO_DATE(‘01/JANUARY/2019’)) FROM DUAL;
E. SELECT TO_DATE(SYSDATE, ‘DD/MONTH/YYYY’) – ‘01/JANUARY/2019’ FROM DUAL;

Show Answer

Question # 29

Which two statements are true about substitution variables? (Choose two.)

A. A substitution variable can be used with any clause in a SELECT statement
B. A substitution variable used to prompt for a column name must be enclosed in a single quotation marks
C. A substitution variable prefixed with & always prompts only once for a value in a session
D. A substitution variable can be used only in a SELECT statement
E. A substitution variable used to prompt for a column name must be enclosed in double quotation marks
F. A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session

Show Answer

Question # 30

Which three statements are true regarding single row subqueries?

A. They must return a row to prevent errors in the SQL statement.
B. They must be placed on the left side of the comparison operator or condition.
C. They can be used in the where clause.
D. A SQL statement may have multiple single row subquery blocks.
E. They must be placed on the right side of the comparison operator or condition.
F. They can be used in the having clause.

Show Answer

Question # 31

View the Exhibit and examine the structure of the PRODUCTS table. Which two tasks require subqueries?

A. Display the number of products whose PROD_LIST_PRlCE is more than the average prod_list_price.
B. Display the total number of products supplied by supplier 102 which have a product status of obsolete.
C. Display the minimum prod_list_price for each product status.
D. Display products whose prod_min_PRICE is more than the average PROD_LIST_PRlCE of all products, and whose status is orderable.
E. Display suppliers whose PR0D_LIST_PRICE is less than 1000.

Show Answer

Question # 32

Which two statements are true about the rules of precedence for operators? (Choose two.)

A. The concatenation operator | | is always evaluated before addition and subtraction in an expression
B. Multiple parentheses can be used to override the default precedence of operators in an expression
C. Arithmetic operators with equal precedence area evaluated from left to right within an expression
D. NULLS influence the precedence of operators in an expression
E. The + binary operator has the highest precedence in an expression in a SQL statement

Show Answer

Question # 33

Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)

A. 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
B. Only the primary key can be defined at the column and table level
C. The foreign key columns and parent table primary key columns must have the same names
D. A table can have only one primary key and one foreign key
E. A table can have only one primary key but multiple foreign keys
F. Primary key and foreign key constraints can be defined at both the column and table level
G. 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

Show Answer

Question # 34

Which three privileges can be restricted to a subset of columns in a table? (Choose three.)

A. ALTER
B. DELETE
C. UPDATE
D. SELECT
E. INDEX
F. REFERENCES
G. INSERT

Show Answer

Question # 35

Which three statements are true about the DESCRIBE command? (Choose three.)

A. It can be used to display the structure of an existing view
B. It can be used only from SQL*Plus
C. It displays the PRIMARY KEY constraint for any column or columns that have that constraint
D. It can be used from SQL Developer
E. It displays all constraints that are defined for each column
F. It displays the NOT NULL constraint for any columns that have that constraint

Show Answer

Question # 36

Which two statements are true about single-row functions? (Choose two.)

A. CEIL: can be used for positive and negative numbers
B. FLOOR: returns the smallest integer greater than or equal to a specified number
C. TRUNC: can be used with NUMBER and DATE values
D. CONCAT: can be used to combine any number of values
E. MOD: returns the quotient of a division operation

Show Answer

Question # 37

The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE.NLS_DATE_FORMAT is set to DD-MON-RR.Which two are true about data type conversions involving these columns in query expressions? (Choose two.)

A. CONCAT(qty_sold, invoice_date) : requires explicit conversion
B. invoice_date > ’01-02-2019’ : uses implicit conversion
C. invoice_date = ’15-march-2019’ : uses implicit conversion
D. qty_sold BETWEEN ‘101’ AND ‘110’ : uses implicit conversion
E. qty_sold = ‘0554982’ : requires explicit conversion

Show Answer

Question # 38

Which three are true about system and object privileges? (Choose three.)

A. WITH GRANT OPTION can be used when granting an object privilege to both users and roles
B. Adding a primary key constraint to an existing table in another schema requires a system privilege
C. Adding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege
D. Revoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect
E. Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect.
F. WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC

Show Answer

Question # 39

An Oracle database server session has an uncommitted transaction in progress which updated 5000 rows in a table.In which three situations does the transactions complete thereby committing the updates? (Choose three.)

A. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT
B. when a CREATE INDEX statement is executed successfully in the same session
C. when a COMMIT statement is issued by the same user from another session in the same database instance
D. when the session logs out successfully
E. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT
F. when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

Show Answer

Question # 40

Which three actions can you perform by using the ORACLE_DATAPUMP access driver? (Choose three.)

A. Read data from an external table and load it into a table in the database
B. Create a directory object for an external table
C. Execute DML statements on an external table
D. Query data from an external table
E. Read data from a table in the database and insert it into an external table
F. Create a directory object for a flat file

Show Answer

Question # 41

View the Exhibit and examine the structure of the PRODUCT table. Which two tasks would require subqueries? (Choose two.) 

A. display all products whose minimum list price is more than the average list price of products having the status ‘orderable’
B. display the total number of products supplied by supplier 102 and have product status as ‘OBSOLETE’
C. display the number of products whose list prices are more than the average list price
D. display all suppliers whose list price is more than 1000
E. display the minimum list price for each product status

Show Answer

Question # 42

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?

A. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c.department_id);
B. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c.department_id);
C. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d.department_id);
D. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d.department_id);

Show Answer

Question # 43

Examine the structure and data in the PRICE_LIST table: You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.Which SQL statement would give the required result?

A. SELECT TO_CHAR (prod_price* .25, ‘$99,999.99’)FROM PRICE_LIST
B. SELECT TO_CHAR (TO_NUMBER(prod_price) * .25, ‘$99,999.00’)FROM PRICE_LIST
C. SELECT TO_CHAR (TO_NUMBER(prod_price, ‘$99,999.99’) * . 25, ‘$99,999.00’)FROM PRICE_LIST
D. SELECT TO_NUMBER (TO_NUMBER(prod_price, ‘$99,999.99’) * . 25, ‘$99,999.00’)FROM PRICE_LIST

Show Answer

Question # 44

View the Exhibit and examine the data in the PROMOTIONS table. PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr. You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000. Which SQL statement would you use?

A. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category = 'post' AND promo_begin_date < '01-01-00';
B. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';
C. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';
D. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';

Show Answer

Question # 45

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:  Which two WHERE conditions give the required result?

A. Option A
B. Option B
C. Option C
D. Option D
E. Option E

Show Answer

Question # 46

View the Exhibit and examine the details of the PRODUCT_INFORMATION table.Exhibit You must display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088.You executed this SQL statement: Which statement is true regarding the execution?

A. It would not execute because the entire WHERE clause is not enclosed within parentheses.
B. It would execute but would return no rows.
C. It would not execute because the same column has been used twice with the AND logical operator.
D. It would execute and return the desired result.

Show Answer

Question # 47

Which three statements are true about performing Data Manipulation Language (DML) operations on a view in an Oracle Database?

A. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.
B. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
C. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
D. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
E. Insert statements can always be done on a table through a view.
F. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions.

Show Answer

Question # 48

Examine the structure of the MARKS table:  Which two statements would execute successfully? (Choose two.) 

A. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)FROM marksWHERE subject1 > subject2;
B. SELECT student_name subject1FROM marksWHERE subject1 > AVG(subject1);
C. SELECT SUM(subject1+subject2+subject3)FROM marksWHERE student_name IS NULL;
D. SELECT student_name,SUM(subject1)FROM marksWHERE student_name LIKE ‘R%’;

Show Answer

Question # 49

Which three statements are true about the Oracle join and ANSI join syntax? 

A. The Oracle join syntax supports natural joins.
B. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.
C. The Oracle join syntax supports creation of a Cartesian product of two tables.
D. The SQL:1999 compliant ANSI join syntax supports natural joins.
E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
F. The Oracle join syntax only supports right outer joins.
G. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.

Show Answer

Question # 50

Which two are true about granting privileges on objects? 

A. An object privilege can be granted to other users only by the owner of that object.
B. An object privilege can be granted to a role only by the owner of that object.
C. A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table.
D. The owner of an object acquires all object privileges on that object by default.
E. The WITH GRANT OPTION clause can be used only by DBA users.

Show Answer

Question # 51

Which three statements are true regarding the SQL WHERE and HAVING clauses? (Choose three.)

A. The HAVING clause conditions can have aggregating functions.
B. The HAVING clause conditions can use aliases for the columns.
C. The WHERE and HAVING clauses cannot be used together in a SQL statement.
D. The WHERE clause is used to exclude rows before grouping data.
E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

Show Answer

Question # 52

You want to display the date for the first Monday of the next month and issue the following command:  What is the outcome? 

A. In generates an error because rrrr should be replaced by rr in the format string.
B. It executes successfully but does not return the correct result.
C. It executes successfully and returns the correct result.
D. In generates an error because TO_CHAR should be replaced with TO_DATE.
E. In generates an error because fm and double quotation marks should not be used in the format string.

Show Answer

Question # 53

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?

A. SELECT LISTAGG(last_name)WITHIN GROUP ORDER BY (hire_date) “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;
B. SELECT LISTAGG(last_name, '; ')WITHIN GROUP ORDER BY (hire_date) “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;
C. SELECT LISTAGG(last_name, '; ') “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;WITHIN GROUP ORDER BY (hire_date);
D. SELECT LISTAGG(last_name, '; ') “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;ORDER BY (hire_date);

Show Answer

Question # 54

Which three statements are true reading subqueries? (Choose three.) 

A. A Main query can have many subqueries.
B. A subquery can have more than one main query.
C. The subquery and main query must retrieve date from the same table.
D. The subquery and main query can retrieve data from different tables.
E. Only one column or expression can be compared between the subquery and main query.
F. Multiple columns or expressions can be compared between the subquery and main query.

Show Answer

Question # 55

View the Exhibit and examine the structure of the PRODUCT table. Which two tasks would require subqueries? (Choose two.) 

A. display all products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable
B. display the total number of products supplied by supplier 102 and have product status as ‘OBSOLETE’
C. display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.
D. display suppliers whose PROD_LIST_PRICE is less than 1000
E. display the minimum PROD_LIST_PRICE for each product status

Show Answer

Question # 56

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 NayerYou need to display customers' second names where the second name starts with "Mc" or "MC". Which query gives the required output?

A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');

Show Answer

Question # 57

View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.  Evaluate the following MERGE statement: MERGE_INTO orders_master o USING monthly_orders m ON (o.order_id = m.order_id) WHEN MATCHED THEN UPDATE SET o.order_total = m.order_total DELETE WHERE (m.order_total IS NULL) WHEN NOT MATCHED THEN INSERT VALUES (m.order_id, m.order_total); What would be the outcome of the above statement?

A. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.
B. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.
C. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.
D. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.

Show Answer

Question # 58

Which statement adds a column called SALARY to the EMPLOYEES table having 100 rows, which cannot contain null?

A. ALTER TABLE EMPLOYEESADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL;
B. ALTER TABLE EMPLOYEESADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
C. ALTER TABLE EMPLOYEESADD SALARY NUMBER(8,2) DEFAULT NOT NULL;
D. ALTER TABLE EMPLOYEESADD SALARY NUMBER(8,2) NOT NULL;

Show Answer

Question # 59

Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

A. The outer query stops evaluating the result set of the inner query when the first value is found.
B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Show Answer

Question # 60

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables. ORDER_ID is the primary key in the ORDERS table and the foreign key of the ORDER_ITEMS table, whose constraint is defined with the ON DELETE CASCADE option. Which DELETE statement would execute successfully?

A. DELETE orders o, order_items iWHERE o.order_id = i.order_id;
B. DELETEFROM ordersWHERE (SELECT order_idFROM order_items);
C. DELETE ordersWHERE order_total < 1000;
D. DELETE order_idFROM ordersWHERE order_total < 1000;

Show Answer

Question # 61

Which statement is true regarding the SESSION_PRIVS dictionary view?

A. It contains the object privileges granted to other users by the current user session.
B. It contains the system privileges granted to other users by the current user session.
C. It contains the current object privileges available in the user session.
D. It contains the current system privileges available in the user session.

Show Answer

Question # 62

View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables. Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.SELECT employee_id FROM employeesWHERE job_id = 'SA_MAN'-------------------------------------SELECT employee_id FROM job_historyWHERE job_id='SA_MAN';Choose two correct SET operators which would cause the query to return the desired result. (Choose two.)

A. UNION
B. MINUS
C. INTERSECT
D. UNION ALL

Show Answer

Question # 63

View the Exhibit and examine the structure of the PRODUCT table. Which two tasks would require subqueries? (Choose two.) 

A. display all products whose minimum list price is more than the average list price of products having the status ‘orderable
B. display the total number of products supplied by supplier 102 and have product status as ‘OBSOLETE’
C. display the number of products whose list prices are more than the average list price
D. display all suppliers whose list price is more than 1000
E. display the minimum list price for each product status

Show Answer

Question # 64

Which statement is true regarding the USING clause in table joins? (Choose two.) 

A. It can be used to join a maximum of three tables.
B. It can be used to access data from tables through equijoins as well as nonequijoins.
C. It can be used to join tables that have columns with the same name and compatible data types.
D. It can be used to restrict the number of columns used in a NATURAL join.

Show Answer

Question # 65

Examine this query:  Which two methods should you use to prevent prompting for a hire date value when this query is executed? 

A. Replace ‘&1’ with ‘&&1’ in the query.
B. Use the DEFINE command before executing the query.
C. Use the UNDEFINE command before executing the query.
D. Execute the SET VERIFY ON command before executing the query.
E. Store the query in a script and pass the substitution value to the script when executing it.
F. Execute the SET VERIFY OFF command before executing the query.

Show Answer