Quiz
1z0-01
Create a free account to finish this quiz
Save your progress and see your final score.
Already have an account? Sign in
Questions in this quiz (134)
1Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION?
- AEach SELECT statement in the compound query must have its own ORDER BY clause.
- BEach SELECT statement in the compound query can have its own ORDER BY clause.
- CColumn positions must be used in the ORDER BY clause.
- DThe first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause
- EOnly column names from the first SELECT statement in the compound query are recognized
2You 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?- ACREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id,o.order_date
- BCREATE 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 iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date
- CCREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id,o.order_date
- DCREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)||'NO OF ITEMS'FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id,o.order_dateWHITH CHECK OPTION
3Which two statements are true regarding multiple-row subqueries? (Choose two.)
- AThey can contain group functions.
- BThey always contain a subquery within a subquery.
- CThey use the < ALL operator to imply less than the maximum.
- DThey can be used to retrieve multiple rows from a single table only.
- EThey 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.
4Which two queries execute successfully?
- ASELKCT COALESCE (100, 'A') FROM DUAL;
- BSELECT NULLIF(NULL, 100) FROM DUAL;
- CSELECT COALESCE(100, NULL, 200) FROM DUAL;
- DSELECT NULLIF (100) FROM DUAL;
- ESELECT NULLIF (100, ‘A') FROM DUAL;
5You must create a SALES table with these column specifications and data types:
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? (Choose the best answer.)- ACREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT = 1,slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT = "CASH")
- BCREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT 1,slsdate DATE DEFAULT 'SYSDATE',payment VARCHAR2(30) DEFAULT CASH);
- CCREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT = 1,slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT = "CASH");
- DCREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT 1,slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT 'CASH');
6Which two statements are true regarding constraints? (Choose two.)
- AA constraint is enforced only for an INSERT operation on a table.
- BA foreign key cannot contain NULL values.
- CA column with the UNIQUE constraint can store NULLS.
- DYou can have more than one column in a table as part of a primary key.
7View 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?- ASELECT order_idFROM order_itemsGROUP BY order_idHAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity))FROM order_items GROUP BY order_id);
- BSELECT order_idFROM order_itemsWHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_items)GROUP BY order_id;
- CSELECT order_idFROM order_itemsWHERE(unit_price*quantity) = MAX(unit_price*quantity)GROUP BY order_id;
- DD. SELECT order_idFROM order_itemsWHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_itemsGROUP BY order_id)
8SCOTT is a user in the database.
Evaluate the commands issued by the DBA:
Which statement is true regarding the execution of the above commands?- AStatement 1 would not execute because the WITH GRANT option is missing.
- BStatement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.
- CStatement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement
- DStatement 1 would not execute because the IDENTIFIED BY <password> clause is missing.
9View 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?- AThe ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4
- BThe ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.
- CThe ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.
- DThe ORDERS_MASTER table would contain the ORDER_IDs 1 and 2
10View the Exhibit and examine the structure of the CUSTOMERS table.
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?- AIt returns an error because the BETWEEN operator cannot be used in the HAVING clause.
- BIt returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement
- CIt returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
- DIt executes successfully.
11View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.
Evaluate the following SQL statement:
What would be the outcome of the above SQL statement?- AIt displays prod IDs in the promo with the lowest cost.
- BIt displays prod IDs in the promos with the lowest cost in the same time interval
- CIt displays prod IDs in the promos with the highest cost in the same time interval.
- DIt displays prod IDs in the promos which cost less than the highest cost in the same time interval.
12Which three actions can you perform by using the ALTER TABLE command?
- ALock a set of rows in a table.
- BDrop pseudocolumns from a table.
- CRename a table.
- DDrop all columns simultaneously from a table.
- EEnable or disable constraints on a table
- FRestrict all DML statements on a table.
13Examine the description of the BOOKS table:
The table has 100 rows.
Examine this sequence of statements issued in a new session:
Which two statements are true?- AThe first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed
- BThe second ROLLBACK command replays the delete.
- CThe first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row
- DThe second ROLLBACK command undoes the insert.
- EThe second ROLLBACK command does nothing.
14Examine 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?- ASELECT * FROM MEMBERS WHERE state LIKE '%A_';
- BSELECT * FROM MEMBERS WHERE state LIKE 'A_';
- CSELECT * FROM MEMBERS WHERE state LIKE 'A_%';
- DSELECT * FROM MEMBERS WHERE state LIKE 'A%';
15View the Exhibit and examine the structure of the CUSTOMERS table.
CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table.
CUSTOMERS need to be updated to reflect the latest information about the customers. What is the error in the following MERGE statement?- AThe CUSTOMER_ID column cannot be updated.
- BThe INTO clause is misplaced in the command.
- CThe WHERE clause cannot be used with INSERT
- DCUSTOMER_VU cannot be used as a data source.
16Which two statements are true about Oracle synonyms?
- AA synonym can have a synonym
- BAll private synonym names must be unique in the database
- CAny user can create a PUBLIC synonym.
- DA synonym can be created on an object in a package.
- EA synonym has an object number.
17View the Exhibit and examine the data in the PRODUCTS table.
Which statement would add a column called PRICE, which cannot contain NULL?- AALTER TABLE productsADD price NUMBER(8,2) NOT NULL;
- BALTER TABLE productsADD price NUMBER(8,2) DEFAULT NOT NULL;
- CALTER TABLE productsADD price NUMBER(8,2) DEFAULT 0 NOT NULL;
- DALTER TABLE productsADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL.
18Examine the structure proposed for the TRANSACTIONS table:
Which two statements are true regarding the storage of data in the above table structure? (Choose two.)- AThe CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
- BThe TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
- CThe CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
- DThe TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.
19View the Exhibit and examine the structure in the EMPLOYEES tables.
Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees
WHERE department_id= 50 ORDER BY department_id UNION
SELECT employee_id, department_id FROM employees
WHERE department_id= 90 UNION
SELECT employee_id, department_id FROM employees
WHERE department_id= 10;
What would be the outcome of the above SQL statement?- AThe statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
- BThe statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID
- CThe statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
- DThe statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.
20You 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.)- AThe 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.
- BThe values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
- CThe values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column
- DThe values in all columns would be returned in descending order.
- EThe values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
21Which three statements are true about Structured Query Language (SQL)?
- AIt best supports relational databases.
- BIt is used to define encapsulation and polymorphism for a relational table.
- CIt is the only language that can be used for both relational and object-oriented databases.
- DIt guarantees atomicity, consistency, isolation, and durability (ACID) features.
- EIt provides independence for logical data structures being manipulated from the underlying physical data storage
- FIt requires that data be contained in hierarchical data storage
22Which two statements are true about a self join?
- AIt can be a left outer join.
- BIt must be a full outer jolin.
- CIt can be an inner join.
- DIt must be an equijoin.
- EThe join key column must have an index.
23Which statement is true regarding the SESSION_PRIVS dictionary view?
- AIt contains the object privileges granted to other users by the current user session.
- BIt contains the system privileges granted to other users by the current user session.
- CIt contains the current object privileges available in the user session.
- DIt contains the current system privileges available in the user session.
24Which three tasks can be performed by DDL statements?
- Aproviding an alternative name for a table
- Bmodifying a table to prevent data that violate certain conditions from being entered in a column
- Cpreventing any data modification to a table
- Dpreventing data retrieval from a table outside of office hours
- Ecreating multiple savepoints to enable partial rollback of a transaction
25Which two statements are true about Data Manipulation Language (DML) statements? (Choose two.)
- AAn INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
- BAn UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
- CA DELETE FROM. statement can remove rows based on only a single condition on a table.
- DAn INSERT INTO... VALUES. statement can add a single row based on multiple conditions on a
table. - EA DELETE FROM. statement can remove multiple rows based on multiple conditions on a table.
- FAn UPDATE....SET. statement can modify multiple rows based on only a single condition on a table.
26You execute this query:
What is the result?- AIt returns the date for the first Monday of the next month.
- BIt returns the date for the last Monday of the current month
- CIt executes successfully but does not return any result.
- DIt generates an error.
27Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)
- AYou can use column alias in the GROUP BY clause.
- BUsing the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
- CThe GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
- DUsing the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
- EIf the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY cause.
28Examine the structure of the EMPLOYEES table.
You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.
Which query would retrieve the required result?- ASELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;
- BSELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)UNION ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000);
- CSELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000;
- D(SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;
29Which two statements are true about current_timestamp?
- AIt returns the same date as CURRENT_date
- BThe time is In the time zone of DBTIMEZONE.
- CThe value varies depending on the setting of SESSIONTIMEZONE
- DIt returns a value of data type timestamp.
- EIt always returns the same value as SYSTIMESTAMP.
- FThe date Is In the time zone of DBTIMEZONE.
30Which two statements cause changes to the data dictionary?
- AGRANT UPDATE ON Bcott.emp TO fin_manager;
- BTRUNCATE TABLE emp;
- CDELETE FROM acott.emp;
- DALTER SESSION SET NLS DATE_FORMAT = * DD/MM/ YVYY * ;
- ESELECT * FROM user_tab_privs;
31Examine the structure of the BOOKS_TRANSACTIONS table.
You want to update this table such that BOOK_ID is set to 'INVALID' for all rows where no MEMBER_ID has been entered.
Examine this partial SQL statement:
Which condition must be used in the WHERE clause to perform the required update?- AMEMBER_ID = '';
- BMEMBER_ID = NULL;
- CMEMBER_ID IS NULL;
- DMEMBER_ID = "";
32Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database?
- AThe DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC).
- BA TIMESTAMP data type column contains information about year, month, and day.
- CThe CURRENT_TIMESTAMP function returns data without time zone information.
- DA TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row.
- EThe SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC).
33On 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.)- AIt succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.
- BIt fails because no SQL *Loader data file location is specified
- CIt fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
- DIt fails because no SQL *Loader control file location is specified.
34Which three are true about the CREATE TABLE command? (Choose three.)
- AIt can include the CREATE..INDEX statement for creating an index to enforce the primary key constraint
- BIt implicitly executes a commit
- CA user must have the CREATE ANY TABLE privilege to create tables
- DIt implicitly rolls back any pending transactions
- EThe owner of the table should have space quota available on the tablespace where the table is defined
- FThe owner of the table must have the UNLIMITED TABLESPACE system privilege
35Examine the description of the CUSTOMERS table:
You want to display details of all customers who reside in cities starting with the letter D followed by at least two characters.
Which query can be used?- ASELECT * FROM customers WHERE city = ‘D_%’;
- BSELECT * FROM customers WHERE city LIKE ‘D_’;
- CSELECT * FROM customers WHERE city LIKE ‘D_%’;
- DSELECT * FROM customers WHERE city = ‘%D_’;
36Which three statements are true about single-row functions? (Choose three.)
- AThey can be nested to any level
- BThe data type returned can be different from the data type of the argument
- CThey can accept only one argument
- DThe argument can be a column name, variable, literal or an expression
- EThey can be used only in the WHERE clause of a SELECT statement
- FThey return a single result row per table
37Which two statements are true regarding the SQL GROUP BY clause? (Choose two.)
- AYou can use a column alias in the GROUP BY clause.
- BUsing the WHERE clause after the GROUP BY clause excludes rows after creating groups.
- CThe GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.
- DUsing the WHERE clause before the GROUP BY clause excludes rows before creating groups
- EIf 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.
38Examine the description of the EMPLOYERS table:
Examine these requirements:
Disable the manager ID and salary of the lowest paid employees for that manager. Exclude anyone whose manager is not known.
Exclude any managers where the minimum salary is 6000 or less.
Sort the output by minimum salary with the highest salary shown first. Which statement will do this?- ASELECT manager_id, MTN(salary) FROM employees
WHERE manager_ id IS NOT NULL GROUP BY manager_id
HAVING MlN (salary) > 6000 ORDER BV MlN (salary); - BSELECT managar_id, MIN (salary) FROM employees
HAVING MIN(salary) > 6000
WHERE manager_id IS NOT NULL GROUP BY manager_id
ORDER BY MTN(salary) DESC; - CSELECT manager_id, HTN(salary) FROM employees
WHERE manager_id IS NOT NULL
HAVING MIN (salary) > 6000
GROUP BY manager_id ORDEU BY MIN (salary) DESC; - DSELECT manager_id, MIN(salary) FROM employees
WHERE manager_id IS NOT NULL AND MIN(salary) > 6000
GROUP BY manager_id ORDER BY MlN (salary) DESC;
- ASELECT manager_id, MTN(salary) FROM employees
39Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.)
- AThe output is sorted by the UNION ALL operator
- BThe names of columns selected in each SELECT statement must be identical
- CThe number of columns selected in each SELECT statement must be identical
- DDuplicates are eliminated automatically by the UNION ALL operator
- ENULLS are not ignored during duplicate checking
40The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues this GRANT command: GRANT ALL
ON orders, order_items TO PUBLIC;
What must be done to fix the statement?- APUBLIC should be replaced with specific usernames
- BALL should be replaced with a list of specific privileges
- CWITH GRANT OPTION should be added to the statement.
- DSeparate GRANT statements are required for the ORDERS and ORDER_ITEMS tables
41Examine the structure of the BOOKS_TRANSACTIONS table:
Examine the SQL statement:
Which statement is true about the outcome?- AIt displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
- BIt displays details for members who have borrowed before today’s date with either RM as TRANSACTION_TYPE or MEMBER_ID as A101 and A102.
- CIt displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE.
- DIt displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.
42Which three statements are true regarding subqueries? (Choose three.)
- AThe ORDER BY Clause can be used in a subquery.
- BA subquery can be used in the FROM clause of a SELECT statement.
- CIf a subquery returns NULL, the main query may still return rows.
- DA subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
- ELogical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery
43View the Exhibit and examine the description of the EMPLOYEES table.
You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.
Which SQL statement would you execute to get the desired output?- ASELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct, salary,salary+commission_pct))“Total”FROM EMPLOYEES;
- BSELECT first_name, salary, salary*12+salary*commission_pct “Total”FROM EMPLOYEES;
- CSELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 “Total”FROM EMPLOYEES;
- DSELECT first_name, salary, salary*12 + NVL(salary,0)*commission_pct, “Total”FROM EMPLOYEES;
44A non-correlated subquery can be defined as . (Choose the best answer.)
- AA set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query.
- BA set of sequential queries, all of which must return values from the same table
- CA set of sequential queries, all of which must always return a single value.
- DA SELECT statement that can be embedded in a clause of another SELECT statement only.
45Examine this SQL statement:
Identify three order by clauses, any one of which can complete the query successfully.- AORDER BY CUST_NO
- BORDER BY 2, 1
- CORDER BY 2, cust_id
- DORDER BY "Last Name"
- EORDER BY "CUST NO"
46Which three statements are true about Data Manipulation Language (DML)?
- AUPDATE statements can have different subqueries to specify the values for each updated column.
- BINSERT statements can insert NULLS explicitly into a column.
- CDELETE statements can remove multiple rows based on multiple conditions.
- DDML statements require a primary key be defined on a table.
- EINSERT INTO…SELECT…FROM statements automatically commit.
47Which two statements are true about the results of using the INTERSECT operator in compound queries?
- AINTERSECT ignores NULLs.
- BReversing the order of the intersected tables can sometimes affect the output.
- CThe number of columns in each SELECT in the compound query can be different.
- DINTERSECT returns rows common to both sides of the compound query.
- EColumn names in each SELECT in the compound query can be different.
48Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)? (Choose two.)
- AImplementing a relationship between two tables might require joining additional tables
- BRelating data from a table with data from the same table is implemented with a self join
- CRows from unrelated tables cannot be joined
- DEvery relationship between the two tables must be implemented in a join condition
- EAn inner join relates rows within the same table
49View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
Which two tasks would require subqueries? (Choose two.)- Adisplaying all the products whose minimum list prices are more than average list price of products having the status orderable
- Bdisplaying the total number of products supplied by supplier 102071 and having product status OBSOLETE
- Cdisplaying the number of products whose list prices are more than the average list price
- Ddisplaying all supplier IDs whose average list price is more than 500
- Edisplaying the minimum list price for each product status
50Examine this statement:
SELECT cust_id, cust^last_ndma "Last Name" FROM customers
WHERE country_id — 10 UNION
SELECT cust_ID_CUST_NO, cust_last_name PROM customers
WHERE country id = 30
Identify three order by clauses, any one of which will complete the query successfully.- AORDER BY 2. cust _id
- BORDER BY 2,
- CORDER BY "CUST^NO
- DORDER BY "Last Name
- EORDER BY CUST_NO
51In which three situations does a transaction complete? (Choose three.)
- Awhen a PL/SQL anonymous block is executed
- Bwhen a DELETE statement is executed
- Cwhen a ROLLBACK command is executed
- Dwhen a data definition language (DDL) statement is executed
- Ewhen a TRUNCATE statement is executed after the pending transaction
52The SQL statements executed in a user session are as follows:
Which two statements describe the consequences of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)- AThe rollback generates an error.
- BNo SQL statements are rolled back.
- COnly the DELETE statements are rolled back.
- DOnly the second DELETE statement is rolled back.
- EBoth the DELETE statements and the UPDATE statement are rolled back.
53Which two are true about the data dictionary?
- AThe SYS user owns all base tables and user-accessible views in the data dictionary.
- BAll users have permissions to access all information in the data dictionary by default.
- CThe data dictionary is constantly updated to reflect changes to database objects, permissions, and data.
- DAll user actions are recorded in the data dictionary.
- EBase tables in the data dictionary have the prefix DBA
54Which statement is true about aggregate functions?
- AThe AVG function implicitly converts NULLS to zero.
- BAggregate functions can be nested to any number of levels.
- CThe MAX and MIN functions can be used on columns with character data types.
- DAggregate functions can be used in any clause of a SELECT statement.
55Which three; statements are true about built-in data types?
- AA char column definition does not require the length to be specified.
- BA BFILE stores unstructured binary data In operating system files.
- CA varchar2 column definition does not require the length to be specified.
- DA varchar2 blank pads column values only if the data stored Is non-numeric and contains no Special characters.
- EThe default length for a char column Is always one character.
- FA blob stores unstructured binary data within the database.
56You want to display the date for the first Monday of the next month and issue the following command:
What is the outcome?- AIn generates an error because rrrr should be replaced by rr in the format string.
- BIt executes successfully but does not return the correct result.
- CIt executes successfully and returns the correct result.
- DIn generates an error because TO_CHAR should be replaced with TO_DATE.
- EIn generates an error because fm and double quotation marks should not be used in the format string
57Examine this SQL statement
Which two are true?- AThe subquery is not a correlated subquery.
- BThe subquery is executed before the DELETE statement is executed.
- CThe DELETE statement executes successfully even if the subquery selects multiple rows.
- DThe subquery is executed for every row in the EMPLOYEES table.
- EAll existing rows in the EMPLOYEES table are deleted.
58Examine this query and its output:
Examine this query with an incomplete was WHERE clause:
Which two are true about operators that can be used In the WHERE clause?- AUsing not in or <> any will give the same result.
- BUsing in will display all the product names,
- CUsing not in or <> all will give the same result.
- DUsing <> any will display all the product names except the product named Fork.
- EUsing <> any will display all the product names.
59Examine the description of the ORDERS table:
Which three statements execute successfully?- ASELECT ORDER_ID INVOICE_ID ORDER-DATE FROM ORDERS MINUS
SELECT INVOICE_ID INVOICE_DATA FROM INVOICE ORDER BY INVOICE_ID; - BSELECT * FROM ORDERS ORDER BY ORDER_ID UNION
SELECT * FROM INVOICES; - CSELECT ORDER_ID ORDER_DATE FROM ORDERS UION ALL
SELECT INVOICE_ID, INVOICE_DATE FROM INVOICE ORDER BY ORDER_ID; - DSELECT * FROM ORDERS ORDER BY ORDER_ID INTERSECT
SELECT * FROM INVOICE ORDER BY INVOICE_ID - E(SELECT * FROM ORDERS
- ASELECT ORDER_ID INVOICE_ID ORDER-DATE FROM ORDERS MINUS
60View the Exhibit and examine the structure of the ORDER_ITEMS table.
Examine the following SQL statement: SELECT order_id, product_id, unit_price FROM order_items
WHERE unit_price =
(SELECT MAX(unit_price) FROM order_items GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID. What correction should be made in the above SQL statement to achieve this?- ARemove the GROUP BY clause from the subquery and place it in the main query
- BReplace = with the >ANY operator
- CReplace = with the >ALL operator
- DReplace = with the IN operator
61Examine this query:
SELECT SUBSTR (SYSDATE, 1, 5) ''Result '' FROM DUAL;
Which statement Is true?- AIt falls unless the expression is modified to substr (tociiar (Trunc (svsdate) ) , 1, 5).
- BIt fails unless the expression is modified to to_char (Substr (sysdate, 1, 5) ) .
- CIt fails unless the expression is modified to substr (to_char (sysdate) , 1, 5S).
- DIt executes successfully with an implicit data type conversion.
62Which three statements are true about the Oracle join and ANSI join syntax?
- AThe Oracle join syntax supports natural joins.
- BThe Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.
- CThe Oracle join syntax supports creation of a Cartesian product of two tables.
- DThe SQL:1999 compliant ANSI join syntax supports natural joins.
- EThe Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
- FThe Oracle join syntax only supports right outer joins.
- GThe SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
63Which two statements are true regarding roles? (Choose two.)
- AA role can be granted to itself.
- BA role can be granted to PUBLIC.
- CA user can be granted only one role at any point of time.
- DThe REVOKE command can be used to remove privileges but not roles from other users.
- ERoles are named groups of related privileges that can be granted to users or other roles.
64Which three actions can you perform on an existing table containing data? (Choose three.)
- AAdd a new NOT NULL column with a DEFAULT value
- BChange the default value of a column
- CChange a DATE column containing data to a NUMBER data type
- DAdd a new column as the table’s first column
- EDefine a default value that is automatically inserted into a column containing nulls
- FIncrease the width of a numeric column
65Which three statements are true about sequences in a single instance Oracle database? (Choose three.)
- AA sequence can issue duplicate values
- BA sequence’s unallocated cached value are lost if the instance shuts down
- CSequences can always have gaps
- DTwo or more tables cannot have keys generated from the same sequence
- EA sequence can only be dropped by a DBA
- FA sequence number that was allocated can be rolled back if a transaction fails
66Which two are true about granting privileges on objects?
- AAn object privilege can be granted to other users only by the owner of that object.
- BAn object privilege can be granted to a role only by the owner of that object.
- CA table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table.
- DThe owner of an object acquires all object privileges on that object by default.
- EThe WITH GRANT OPTION clause can be used only by DBA users.
67Examine the data in the CUSTOMERS table:
You want to list all cities that have more than one customer along with the customer details. Evaluate the following query:
Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)- ALEFT OUTER JOIN
- BJOIN
- CNATURAL JOIN
- DRIGHT OUTER JOIN
- EFULL OUTER JOIN
68View 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?- ADELETE orders o, order_items iWHERE o.order_id = i.order_id;
- BDELETEFROM ordersWHERE (SELECT order_idFROM order_items);
- CDELETE ordersWHERE order_total < 1000;
- DDELETE order_idFROM ordersWHERE order_total < 1000;
69View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?- AIt produces an error because subqueries cannot be used with the CASE expression.
- BIt shows COST_REMARK for all the promos in the promo category ‘TV’.
- CIt shows COST_REMARK for all the promos in the table.
- DIt produces an error because the subquery gives an error.
70Which two statements best describe the benefits of using the WITH clause? (Choose two.)
- AIt 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.
- BIt enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
- CIt enables sessions to store a query block permanently in memory and use it to create complex queries.
- DIt enables sessions to store the results of a query permanently.
71View the Exhibit and examine the data in EMP and DEPT tables.
In the DEPT table, DEPTNO is the PRIMARY KEY.
In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the DEPTNO column in the DEPT table.
What would be the outcome of the following statements executed in the given sequence? DROP TABLE emp;
FLASHBACK TABLE emp TO BEFORE DROP; INSERT INTO emp VALUES (2, ‘SCOTT’, 10); INSERT INTO emp VALUES (3, ‘KING’, 55);- ABoth the INSERT statements would fail because the constraints are automatically retrieved when the table is flashed back.
- BBoth the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back
- COnly the first INSERT statement would succeed because all constraints except the primary key constraint are automatically retrieved after a table is flashed back.
- DOnly the SECOND INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.
72Which three privileges can be restricted to a subset of columns in a table? (Choose three.)
- AALTER
- BDELETE
- CUPDATE
- DSELECT
- EINDEX
- FREFERENCES
- GINSERT
73You issued the following command:
SQL> DROP TABLE employees;
Which three statements are true? (Choose three.)- AAll uncommitted transactions are committed.
- BAll indexes and constraints defined on the table being dropped are also dropped.
- CSequences used in the employees table become invalid.
- DThe space used by the employees table is reclaimed immediately.
- EThe employees table can be recovered using the rollback command
- FThe employees table is moved to the recycle bin
74Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.)
- AThe WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
- BThe aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
- CThe WHERE clause can be used to exclude rows after dividing them into groups.
- DThe HAVING clause can be used with aggregate functions in subqueries.
- EThe WHERE clause can be used to exclude rows before dividing them into groups.
75You issue this command which succeeds:
SQL> DROP TABLE products;
Which three statements are true? (Choose three.)- AAll existing views and synonyms that refer to the table are invalidated but retained.
- BAny uncommitted transaction in the session is committed.
- CTable data and the table structure are deleted.
- DAll the table’s indexes if any exist, are invalidated but retained.
- ETable data is deleted but the table structure is retained.
76Examine the description of the EMPLOYEES table:
Examine this query:
Which line produces an error?- ALine 5
- BLine 3
- CLine 7
- DLine 8
77View 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?- ASELECT product_name, SYSDATE+warranty_period AS “Warranty expire date”FROM product_informationORDER BY SYSDATE-warranty_period
- BSELECT product_name, SYSDATE+warranty_period AS “Warranty expire date”FROM product_informationORDER BY SYSDATE+warranty_period
- CSELECT product_name, SYSDATE+warranty_period AS “Warranty expire date”FROM product_informationORDER BY SYSDATE
- DSELECT product_name, SYSDATE+warranty_period AS “Warranty expire date”FROM product_informationWHERE warranty_period > SYSDATE
78You 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.)- ACREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(100),hire_date DATE,CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id));
- BCREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(25),hire_date DATE,CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));
- CCREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY,login_id NUMBER UNIQUE,employee_name VARCHAR2(25),hire_date DATE);
- DCREATE 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));
- ECREATE 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));
79An 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.)- Awhen a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT
- Bwhen a CREATE INDEX statement is executed successfully in the same session
- Cwhen a COMMIT statement is issued by the same user from another session in the same database instance
- Dwhen the session logs out successfully
- Ewhen a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT
- Fwhen a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session
80View the exhibit and examine the structure and data in the INVOICE table.
Which two SQL statements would execute successfully? (Choose two.)- ASELECT MAX(AVG(SYSDATE -inv_date))FROM invoice;
- BSELECT AVG(inv_date)FROM invoice;
- CSELECT MAX(inv_date),MIN(cust_id)FROM invoice;
- DSELECT AVG( inv_date - SYSDATE), AVG(inv_amt)FROM invoice;
81Which two statements are true about conditional INSERT ALL?
- AEach WHEN condition is tested for each row returned by the subquery.
- BThe total number of rows inserted is always equal to the number of rows returned by the subquery.
- CA single WHEN condition can be used for multiple INTO clauses.
- DIt cannot have an ELSE clause.
- EEach row returned by the subquery can be inserted into only a single target table.
82Which three statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose three.)
- AIt can be used only with the SELECT clause.
- BThe WITH clause can hold more than one query.
- CIf the query block name and the table name are the same, then the table name takes precedence.
- DThe query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block
83Which two statements are true about Oracle synonyms?
- ASynonyms can be created for packages.
- BSynonyms cannot be created for synonyms
- CUsers must have the required privileges on the underlying objects to use public synonyms
- DUsers must have the DBA role to create public synonyms.
- ESynonyms cannot be created for sequences.
- FSynonyms can be created for roles.
84View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables
You wrote this SQL statement to retrieve EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, for all employees
SELECT employee_id, first_name, department_name FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?- AThe table prefix is missing for the column names in the SELECT clause.
- BThe NATURAL JOIN clause is missing the USING clause.
- CThe DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
- DThe EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.
85Which two statements are true regarding working with dates? (Choose two.)
- AThe RR date format automatically calculates the century from the SYSDATE function but allows the session user to enter the century.
- BThe RR date format automatically calculates the century from the SYSDATE function and does not allow a session user to enter the century.
- CThe default internal storage of dates is in character format.
- DThe default internal storage of dates is in numeric format.
86Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:
You want to generate a list of all department IDs that do not exist in the COURSE_DETAILS table. You execute the SQL statement:
What is the outcome?- AIt fails because the join type used is incorrect.
- BIt executes successfully and displays the required list.
- CIt executes successfully but displays an incorrect list.
- DIt fails because the ON clause condition is not valid.
87View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.
You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?- AUPDATE dept dSET city = ALL (SELECT cityFROM locations lWHERE d.location_id = l.location_id);
- BUPDATE dept dSET city = (SELECT cityFROM locations l)WHERE d.location_id = l.location_id;
- CUPDATE dept dSET city = ANY (SELECT cityFROM locations l)
- DUPDATE dept dSET city = (SELECT cityFROM locations lWHERE d.location_id = l.location_id);
88Examine 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?- ASELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c.department_id);
- BSELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c.department_id);
- CSELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d.department_id);
- DSELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d.department_id);
89Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?
- ABy default, EM express is available for a database after database creation.
- BYou can use EM express to manage multiple databases running on the same server.
- CYou can perform basic administrative tasks for pluggable databases by using the EM express interface.
- DYou cannot start up or shut down a database Instance by using EM express.
- EYou can create and configure pluggable databases by using EM express.
90View 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.)- AINSERT INTO ordersVALUES (1,’09-mar-2007’, ‘online’,’’, 1000);
- BINSERT INTO orders(order_id,order_date,order_mode,(customer_id,order_total)VALUES (1,TO_DATE(NULL), ‘online’, 101, NULL);
- CINSERT INTO(SELECT order_id,order_date,customer_id FROM orders)VALUES (1,’09-mar-2007’, 101);
- DINSERT INTO ordersVALUES (1,’09-mar-2007’, DEFAULT, 101, DEFAULT);
- EINSERT INTO orders(order_id,order_date,order_mode,order_total)VALUES (1,’10-mar-2007’,‘online’,1000);
91View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT first_name, employee_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) “Review” FROM employees;
The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. The review date is the firsts Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session.
Which statement is true regarding this query?- AThe query would execute to give the desired output.
- BThe query would not execute because date functions cannot be nested.
- CThe query would execute but the output would give review dates that are Sundays.
- DThe query would not execute because the NEXT_DAY function accepts a string as argument
92Which two statement are true regarding table joins available in the Oracle Database server? (Choose two.)
- AYou can use the ON clause to specify multiple conditions while joining tables.
- BYou can explicitly provide the join condition with a NATURAL JOIN.
- CYou can use the JOIN clause to join only two tables.
- DYou can use the USING clause to join tables on more than one column.
93CREATE 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?- AIt would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.
- BIt would remain disabled and has to be enabled manually using the ALTER TABLE command
- CIt would be automatically enabled and immediate.
- DIt would be automatically enabled and deferred.
94Examine these SQL statements that are executed in the given order:
What will be the status of the foreign key EMP_MGR_FK?- AIt will be enabled and immediate.
- BIt will be enabled and deferred.
- CIt will remain disabled and can be re-enabled manually.
- DIt will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
95Evaluate the following query:
What would be the outcome of the above query?- AIt produces an error because the data types are not matching.
- BIt executes successfully and displays the literal " {'s start date was \> " for each row in the output.
- CIt executes successfully and introduces an 's at the end of each promo_name in the output.
- DIt produces an error because flower braces have been used.
96Which two statements are true about transactions in the Oracle Database server?
- AIf a session has an uncommitted transaction, then a DDL statement issues a COMMIT before starting a new transaction.
- BAn uncommitted transaction commits automatically if the user exists SQL*Plus.
- CData Manipulation Language (DML) statements always start a new transaction.
- DA user can always see uncommitted updates made by the same user in a different session.
- EA Data Definition Language (DDL) statement does a COMMIT automatically only for the data dictionary updates caused by the DDL.
- FA session can always see uncommitted updates made by itself.
97The BOOKS_TRANSACTIONS table exists in your database.
SQL>SELECT * FROM books_transactions ORDER BY 3;
What is the outcome on execution?- AThe execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
- BRows 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.
- CRows are displayed in the order that they are stored in the table only for the first three rows.
- DRows are displayed sorted in ascending order of the values in the third column in the table
98Which two statements are true about sequences created in a single instance Oracle database? (Choose two.)
- AWhen the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.
- BDELETE <sequencename> would remove a sequence from the database.
- CThe numbers generated by an explicitly defined sequence can only be used to insert data in one table.
- DCURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.
- EWhen a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.
99View 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.)- ADELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promo_name = ‘blowout sale’)AND promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = ‘everyday low price’)FROM promotionsWHERE promo_name = ‘everyday low price’);
- BDELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = ‘blowout sale’)OR promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = ‘everyday low price’)
- CDELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = ‘blowout sale’)OR promo_name = ‘everyday low price’);
- DDELETEFROM salesWHERE promo_id IN (SELECT promo_idFROM promotionsWHERE promo_name IN = ‘blowout sale’,‘everyday low price’));
100View 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?- ASELECT order_id, order_date FROM ordersWHERE order_date >ANY(SELECT order_date FROM orders WHERE customer_id = 101);
- BSELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
- CSELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT order_date FROM orders WHERE customer_id = 101);
- DSELECT order_id, order_date FROM ordersWHERE order_date > IN(SELECT order_date FROM orders WHERE customer_id = 101);
101Examine this data in the EMPLOYEES table:
Which statement will execute successfully?- ASELECT dept_id, LENGTH (last: name) , SUM(salary) FROM employees GROUP BV dept_id;
- BSELECT dept_id, STDDEV(last_name), SUM( salary) FROM employees GROUP BV dept_id;
- CSELECT dept_id, MAX (last_name) . SUM(salary) FROM employees GROUP BY dept_id;
- DSELECT dept_id( INSTR(last_name, 'A'), SUM (salary) FROM employees GROUP BY dept_id;
102Examine 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?- ABoth execute successfully and give correct results.
- BOnly the first query executes successfully but gives a wrong result.
- COnly the first query executes successfully and gives the correct result.
- DOnly the second query executes successfully but gives a wrong result.
- EOnly the second query executes successfully and gives the correct result
103Which two statements are true regarding views? (Choose two.)
- AThe WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
- BThe OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.
- CRows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
- DRows added through a view are deleted from the table automatically when the view is dropped.
- EA simple view in which column aliases have been used cannot be updated.
- FA subquery used in a complex view definition cannot contain group functions or joins.
104Which three actions can you perform only with system privileges?
- AQuery any table in a database.
- BLog in to a database.
- CAccess flat files via a database, which are stored in an operating system directory.
- DTruncate a table in another schema.
- EExecute a procedure in another schema.
- FUse the WITH GRANT OPTION clause.
105View the exhibit and examine the structure of the EMPLOYEES table.
You want to select all employees having 100 as their MANAGER_ID manages and their manager.
You want the output in two columns: the first column should have the employee’s manager’s LAST_NAME and the second column should have the employee’s LAST_NAME.
Which SQL statement would you execute?- ASELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE m.manager_id=100;
- BSELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE e.manager_id=100;
- CSELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON e.employee_id = m.manager_idWHERE m.manager_id=100;
- DSELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eWHERE m.employee_id = e.manager_id AND e.manager_id=100;
106View 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?- ASELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions) ANDpromo_category= ‘INTERNET’;
- BSELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);
- CSELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = ‘INTERNET’);
- DSELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);
107Examine the description of the TRANSACTIONS table
Which two SQL statements execute successfully?- ASELECT customer_id AS “CUSTOMER-ID”, transaction_date AS DATE, amount + 100 “DUES” FROM transactions;
- BSELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 “DUES AMOUNT” FROM transactions;
- CSELECT customer_id CUSTID, transaction_date TRANS_DATE, amount +100 DUES FROM transactions;
- DSELECT customer_id AS “CUSTOMER-ID”, transaction_date AS “DATE”, amount + 100 DUES FROM transactions;
- ESELECT customer_id AS ‘CUSTOMER-ID’, transaction_date AS DATE, amount + 100 ‘DUES’ FROM transactions;
108View the Exhibit and examine the structure of the ORDERS table.
Which UPDATE statement is valid?- AUPDATE ordersSET order_date = ’12-mar-2007’,order_total IS NULLWHERE order_id = 2455;
- BUPDATE ordersSET order_date = ’12-mar-2007’,AND order_total = TO_NUMBER(NULL)WHERE order_id = 2455;
- CUPDATE ordersSET order_date = ’12-mar-2007’,order_total = NULLWHERE order_id = 2455
- DUPDATE ordersSET order_date = TO_DATE(’12-mar-2007’,’dd-mon-yyyy’),SET order_total = TO_NUMBER (NULL)WHERE order_id = 2455;
109Which three statements are true about views in an Oracle Database?
- AViews can join tables only if they belong to the same schema.
- BA view can be created that refers to a non-existent table in its defining query.
- CViews have no object number.
- DViews have no segment.
- ERows inserted into a table using a view are retained in the table if the view is dropped.
- FA SELECT statement cannot contain a WHERE clause when querying a view containing a WHERE clause in its defining query.
110Evaluate the following query:
SQL> SELECT TRUNC (ROUND (156.00, -1),-1) FROM DUAL;
What would be the outcome?- A150
- B200
- C160
- D16
- E100
111Examine 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?- ASELECT TO_CHAR (prod_price* .25, ‘$99,999.99’)FROM PRICE_LIST
- BSELECT TO_CHAR (TO_NUMBER(prod_price) * .25, ‘$99,999.00’)FROM PRICE_LIST
- CSELECT TO_CHAR (TO_NUMBER(prod_price, ‘$99,999.99’) * . 25, ‘$99,999.00’)FROM PRICE_LIST
- DSELECT TO_NUMBER (TO_NUMBER(prod_price, ‘$99,999.99’) * . 25, ‘$99,999.00’)FROM PRICE_LIST
112View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)
In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator:
Which two JOIN operations can be used to obtain the required output? (Choose two.)- AFULL OUTER JOIN
- BJOIN
- CLEFT OUTER JOIN
- DRIGHT OUTER JOIN
113Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)
- AThe outer query stops evaluating the result set of the inner query wh¿en the first value is found.
- BIt is used to test whether the values retrieved by the inner query exist in the result of the outer query.
- CIt is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
- DThe outer query continues evaluating the result set of the inner query until all the values in the result set are processed.
114The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER.
Which two queries execute successfully?- ASELECT NVL (cust_credit_limit * .15, ‘Not Available’) FROM customers;
- BSELECT TO_CHAR(NVL(cust_credit_limit * .15, ‘Not Available’)) FROM customers;
- CSELECT NVL(TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
- DSELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
- ESELECT NVL2 (cust_credit_limit * .15, ‘Not Available’) FROM customers;
115Which three statements are true about the ALTER TABLE....DROP COLUMN. command? (Choose three.)
- AA column can be dropped only if it does not contain any data.
- BA column can be dropped only if another column exists in the table.
- CA dropped column can be rolled back.
- DThe column in a composite PRIMARY KEY with the CASCADE option can be dropped.
- EA parent key column in the table cannot be dropped.
116You 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?- ASELECT LISTAGG(last_name)WITHIN GROUP ORDER BY (hire_date) “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;
- BSELECT LISTAGG(last_name, '; ')WITHIN GROUP ORDER BY (hire_date) “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;
- CSELECT LISTAGG(last_name, '; ') “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;WITHIN GROUP ORDER BY (hire_date);
- DSELECT LISTAGG(last_name, '; ') “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;ORDER BY (hire_date);
117Examine this SQL statement:
Which two are true?- AAll existing rows in the ORDERS table are updated
- BThe subquery is executed for every updated row in the ORDERS table
- CThe subquery is executed before the UPDATE statement is executed
- DThe subquery is not a correlated subquery
- EThe UPDATE statement executes successfully even if the subquery selects multiple rows
118Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?- AIt executes successfully and displays rows in the descending order of PROMO_CATEGORY.
- BIt produces an error because positional notation cannot be used in the ORDER BY clause with SET operators.
- CIt executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.
- DIt produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.
119Which three statements are true about multiple row subqueries?
- AThey can contain GROUP BY clauses.
- BThey can return multiple columns.
- CTwo or more values are always returned from the subquery.
- DThey can contain HAVING clauses.
- EThey cannot contain a subquery.
120Examine the structure of the MEMBERS table:
NameNull?Type
MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
You execute the SQL statement:
SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members;
What is the outcome?- AIt fails because the alias name specified after the column names is invalid.
- BIt fails because the space specified in single quotation marks after the first two column names is invalid.
- CIt executes successfully and displays the column details in a single column with only the alias column heading.
- DIt executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.
121The 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?- ASELECT REPLACE (TRIM(TRAILING ‘son’ FROM cust_last_name), ‘An’, ‘o’) FROM customers;
- BSELECT INITCAP (REPLACE(TRIM(‘son’ FROM cust_last_name), ‘An’, ‘o’)) FROM customers;
- CSELECT REPLACE (SUBSTR(cust_last_name, -3), ‘An’, ‘o’) FROM customers;
- DSELECT REPLACE (REPLACE(cust_last_name, ‘son’, ‘’), ‘An’, ‘o’) FROM customers;
122Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)
- AThe nested query executes after the outer query returns the row.
- BThe nested query executes first and then the outer query executes.
- CThe outer query executes only once for the result returned by the inner query.
- DEach row returned by the outer query is evaluated for the results returned by the inner query.
123Examine 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?- ASELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
- BSELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
- CSELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
- DSELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
124You 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?- ASELECT cust_last_name AS “Name”, cust_credit_limit + 1000AS “New Credit Limit”FROM customers;
- BSELECT cust_last_name AS Name, cust_credit_limit + 1000AS New Credit LimitFROM customers;
- CSELECT cust_last_name AS Name, cust_credit_limit + 1000“New Credit Limit”FROM customers;
- DSELECT INITCAP (cust_last_name) “Name”, cust_credit_limit + 1000INITCAP (“NEW CREDIT LIMIT”)FROM customers;
125Evaluate the following CREATE TABLE command
Which statement is true regarding the above SQL statement?- AIt would execute successfully and only ORD_ITM_IDX index would be created.
- BIt would give an error because the USING INDEX clause cannot be used on a composite primary.
- CIt would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.
- DIt would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command.
126Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c? (Choose two.)
- AALTER TABLE SPLIT PARTITION
- BALTER TABLE MERGE PARTITION
- CALTER TABLE TRUNCATE PARTITION
- DALTER TABLE ADD PARTITION
- EALTER TABLE DROP PARTITION
- FALTER TABLE MOVE PARTITION
127Which three statements are true about GLOBAL TEMPORARY TABLES?
- AA GLOBAL TEMPORARY TABLE can have multiple indexes.
- BA GLOBAL TEMPORARY TABLE cannot have a PUBLIC SYNONYM.
- CA trigger can be created on a GLOBAL TEMPORARY TABLE.
- DA GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view.
- EA GLOBAL TEMPORARY TABLE can have only one index.
- FData Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO.
128Which three statements are true about GLOBAL TEMPORARY TABLES?
- AGLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose
user has been granted select on the table. - BGLOBAL TEMPORARY TABLE space allocation occurs at session start.
- CA DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.
- DA GLOBAL TEMPORARY TABLE’s definition is available to multiple sessions.
- EAny GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.
- FA TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for
the issuing session to be deleted.
- AGLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose
129Which statement is true about TRUNCATE and DELETE?
- AYou can never TRUNCATE a table if foreign key constraints will be violated.
- BFor large tables TRUNCATE is faster than DELETE.
- CFor tables with multiple indexes and triggers DELETE is faster than TRUNCATE.
- DYou can never DELETE rows from a table if foreign key constraints will be violated.
130Which statement is true about transactions?
- AA set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
- BEach Data Definition Language (DDL) statement executed forms a single transaction.
- CA set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
- DA combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.
131Examine this query:
Which two methods should you use to prevent prompting for a hire date value when this query is executed?- AReplace ‘&1’ with ‘&&1’ in the query.
- BUse the DEFINE command before executing the query.
- CUse the UNDEFINE command before executing the query.
- DExecute the SET VERIFY ON command before executing the query.
- EStore the query in a script and pass the substitution value to the script when executing it.
- FExecute the SET VERIFY OFF command before executing the query.
132Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)
- AWHERE and HAVING clauses cannot be used together in a SQL statement.
- BThe HAVING clause conditions can have aggregate functions.
- CThe HAVING clause conditions can use aliases for the columns.
- DThe WHERE clause is used to exclude rows before the grouping of data.
- EThe HAVING clause is used to exclude one or more aggregated results after grouping data.
133In 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?- AIt executes successfully but no rows updated.
- BIt executes successfully and updates the records of those employees who have been working in the
company for more than 600 days. - CIt gives an error because multiple NVL functions are used in an expression.
- DIt gives an error because NVL function cannot be used with UPDATE.
134Evaluate 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?- ASYSDATE cannot be used with the CHECK constraint.
- BThe BETWEEN clause cannot be used for the CHECK constraint.
- CThe CHECK constraint cannot be placed on columns having the DATE data type.
- DORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the
FOREIGN KEY.
Information
- Description
- Quiz on 1z0-01, created by efraim vallejo on 08/12/2020.
- Author
- efraim vallejo
- Language
- English
- Views
- 6
- Rating
- No ratings yet Rate this quiz