Quiz
OCA 1Z0-071 12C - Parte 2
Este quiz tem tempo limite, por isso abre no player de sempre.
Perguntas do quiz (150)
1Which statement is true about Data Manipulation Language (DML)?
- ADML automatically disables foreign ley constraints when modifying primary key values in the parent table
- BEach DML statement forms a transaction by default.
- CA transaction can consist of one or more DML statements.
- DDML disables foreign key constraints when deleting primary key values in the parent table, only when the ON DELETE CASCADE option is set for the foreigr constraint.
2See 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 promotions
WHERE promo_begin_date> ALL
(SELECT MAX (promo_begin_date)
FROM promotions)
AND promo_category= 'INTERNET'; - BSELECT promo_name, promo_begin_date
FROM promotions
WHERE promo_begin_date IN (SELECT promo_begin_date
FROM promotions
WHERE promo_category= 'INTERNET'); - CSELECT promo_name, promo_begin_date
FROM promotions
WHERE promo_begin_date > ALL
(SELECT promo_begin_date
FROM promotions
WHERE promo_category = 'INTERNET'); - DSELECT promo_name, promo_begin_date
FROM promotions
WHERE promo_begin_date> ANY (SELECT promo_begin_date
FROM promotions
WHERE promo_category= 'INTERNET');
- ASELECT promo_name, promo_begin_date
3Which 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.
4Evaluate 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 is not permitted in the CREATE TABLE command.
5Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount
in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?- ASELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers; - BSELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers; - CSELECT DISTINCT cust_income_level ||‘ ‘|| cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers; - DSELECT cust_income_level ||‘ ‘ ||cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;
- ASELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
6Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement?
- 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.
7You need to display the date 11-Oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?
- ASELECT TO_CHAR(’11-oct-2007′, ‘fmDdspth "of" Month, Year’) FROM DUAL;
- BSELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdspth of month, year’) FROM DUAL;
- CSELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdthsp "of" Month, Year’) FROM DUAL;
- DSELECT TO_DATE(TO_CHAR(’11-oct-2007′,’fmDdspth ”of” Month, Year’)) FROM DUAL;
8Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS
SQL>CREATE TABLE DEPARTMENT_DETAILS
(DEPARTMENT_ID NUMBER PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(50),
HOD VARCHAR2(50));
SQL>CREATE TABLE COURSE_DETAILS
(COURSE_ID NUMBER PRIMARY KEY,
COURSE_NAME VARCHAR2(50),
DEPARTMENT_ID NUMBER REFERENCES DEPARTMENT_DETAILS(DEPARTMENT_ID));
You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses.
Which SQL statement must you use?- ASELECT course_id, department_id
FROM department_details d
RIGHT OUTER JOIN course_details c
USING(department_id) - BSELECT c.course_id, d.department_id
FROM course_details c
RIGHT OUTER JOIN department_details d
ON (c.department_id=d. department_id); - CSELECT c.course_id, d.department_id
FROM course_details c
FULL OUTER JOIN department_details d
ON (c.department_id=d. department_id); - DSELECT c.course_id, d.department_id
FROM course_details c
FULL OUTER JOIN department_details d
ON (c.department_id <> d. department_id);
- ASELECT course_id, department_id
9View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.
The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS table.
The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Examine this command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE) AS SELECT prod_id, cust_id, time_id FROM sales;
Which statement is true?
- AThe NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
- BThe NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
- CThe NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
- DThe NEW_SALES table would get created and all the NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
10View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) AS SELECT order_id.order_date,customer_id FROM orders;
Which statement is true regarding the above command?
- AThe NEW_ORDERS table would not get created because the DEFAULT value cannot be specified in the column definition.
- BThe NEW_ORDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
- CThe NEW_ORDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
- DThe NEW_ORDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
11Evaluate the following statement:
INSERT ALL
WHEN order_total < 10000 THEN
INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders
WHEN order_total > 2000000 THEN
INTO large_orders
SELECT order_id, order_total, customer_id
FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?- AThey are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
- BThey are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
- CThey are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
- DThe INSERT statement would give an error because the ELSE clause is not present for support in case none of the WHEN clauses are true.
12Which two statements are true regarding the SQL GROUP BY clause?
- 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.
13You issue this command which succeeds:
SQL> DROP TABLE products;
Which three statements are true?- 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.
14You execute the SQL statement:
SQL> CREATE TABLE citizens
(citizen_id CHAR (10) PRIMARY KEY,
last_name VARCHAR2 (50) NOT NULL,
first_name VARCHAR2 (50),
address VARCHAR2 (100),
city VARCHAR2 (30) NOT NULL DEFAULT 'SEATTLE',
CONSTRAINT cnames CHECK (first_name<>last_name) );
What is the outcome?- AIt succeeds and CITY can contain only 'SEATTLE' or null for all rows.
- BIt fails because the condition for the CANAMES constraint is not valid.
- CIt fails because the DEFAULT options cannot be after the sentence NOT NULL.
- DIt succeeds and an index is crated for CITIZEN_ID.
15Evaluate 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 BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT 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.
16Examine the structure of the PROGRAMS table:
Which two SQL statements would execute successfully?
- ASELECT NVL (ADD_MONTHS (END_DATE,1), SYSDATE) FROM programs;
- BSELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
- CSELECT NVL (MONTHS_BETWEEN (start_date, end_date), 'Ongoing') FROM programs;
- DSELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), 'Ongoing') FROM programs
17View the Exhibit and examine the structure of the CUSTOMERS table.
Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.
Customers with no credit limit should have 'Not Available' displayed.
Which SQL statement would produce the required result?
- ASELECT NVL (TO_CHAR(cust_credit_limit*.15), 'Not Available') 'NEW CREDIT' FROM customers
- BSELECT TO_CHAR(NVL(cust_credit_limit*.15), 'Not Available')) 'NEW CREDIT' FROM customers
- CSELECT NVL (cust_credit_limit*.15, 'Not Available') 'NEW CREDIT' FROM customers
- DSELECT NVL (cust_credit_limit, 'Not Available')*.15 'NEW CREDIT' FROM customers
18Examine these SQL statements that are executed in the given order:
CREATE TABLE emp
(emp_no NUMBER (2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
ename VARCHAR 2 (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 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.
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.
20View the Exhibit and examine, the description for the SALES and CHANNELS tables. (Choose the best answer.)
You issued this SQL statement:
INSERT INTO SALES VALUES (23, 2300, SYSDATE,
(SELECT CAHNNEL_ID FROM CHANNELS WHERE CHANNEL_DESC='DIRECT SALES'), 12, 500);
Which statement is true regarding the result?- AThe statement will fail because the sub-query in the VALUES clause is not enclosed within single quotation marks.
- BThe statement will fail because a subquery cannot be used in a VALUES clause.
- CThe statement will execute and a new row will be inserted in the SALES table.
- DThe statement will fail because the VALUES clause is not required with the subquery.
21View the Exhibit and examine the description of the ORDERS table.
Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)- AWHERE order_date > TO_DATE(‘JUL 10 2006′,’MON DD YYYY’)
- BWHERE TO_CHAR(order_date,’MON DD YYYY’) = ‘JAN 20 2003’
- CWHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),’MON DD YYYY’)
- DWHERE order_date IN ( TO_DATE(‘Oct 21 2003′,’Mon DD YYYY’), TO_CHAR(‘NOV 21 2003′,’Mon DD YYYY’) )
22Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)
- AFinding the lowest value
- BFinding the quotient
- CRaising to a power
- DSubtraction
- EAddition
23View 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 EMPLOYEES
WHERE JOB_ID = SA_MAN
————————————-
SELECT EMPLOYEE_ID
FROM JOB_HISTORY
WHERE JOB_ID = SA_MAN;
Choose correct SET operator which would cause the query to return the desired result.
- AUNION
- BMINUS
- CINTERSECT
- DUNION ALL
24You must create a SALES table with these column specifications and data types: (Choose the best answer.)
SALESID: Number
STOREID: Number
ITEMID: Number
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified
PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified
Which statement would create the table?- 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);
- ACREATE TABLE Sales
25View the Exhibit and examine the details of the PRODUCT_INFORMATION table. (Choose two.)
Evaluate this SQL statement:
SELECT TO_CHAR (list_price, $9,999)
From product_information;
Which two statements are true regarding the output?- AA row whose LIST_PRICE column contains value 11235.90 would be displayed as #######.
- BA row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,123.
- CA row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124.
- DA row whose LIST_PRICE column contains value 11235.90 would be displayed as $1,123.
26Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)
- ADuring parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.
- BDuring executing, the oracle server may read data from storage if the required data is not already in memory.
- CDuring row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
- DDuring optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.
27Examine the structure of the ORDERS table:
You want to find the total value of all the orders for each year and issue this command:
SQL> SELECT TO_CHAR(order_date,rr), SUM(order_total) FROM orders GROUP BY TO_CHAR(order_date, yyyy);
Which statement is true regarding the result?
- AIt executes successfully but does not give the correct output.
- BIt executes successfully but gives the correct output.
- CIt returns an error because the TO_CHAR function is not valid.
- DIt return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
28View the Exhibit and examine the structure of the ORDER_ITEMS table. (Choose the best answer.)
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_id FROM order_items GROUP BY order_id HAVING SUM(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)) FROM order_items GROUP BY order_id);
- BSELECT order_id FROM order_items WHERE(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity) FROM order_items) GROUP BY order_id);
- CSELECT order_id FROM order_items WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id);
- DSELECT order_id FROM order_items WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP BY order_id)
29View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table.
Evaluate this SQL statement:
ALTER TABLE emp DROP COLUMN first_name;
Which two statements are true?
- AThe FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.
- BThe FIRST_NAME column would be dropped provided at least one column remains in the table.
- CThe FIRST_NAME column would be dropped provided it does not contain any data
- DThe drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement
30View the exhibit and examine the structure and data in the invoice table
Which two SQL statements would execute successfully?
- 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
31Which two statements best describe the benefits of using the WITH clause?
- AIt can improve the performance of a large query by storing the result of a query block having the WITH clause in the sessions 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.
32Which three statements are true regarding subqueries?
- 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.
33Which two statements are true regarding single row functions?
- AMOD : returns the quotient of a division.
- BTRUNC : can be used with NUMBER and DATE values.
- CCONCAT : can be used to combine any number of values.
- DSYSDATE : returns the database server current date and time.
- EINSTR : can be used to find only the first occurrence of a character in a string.
- FTRIM : can be used to remove all the occurrences of a character from a string.
34View 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 orders
WHERE order_date > ANY
(SELECT order_date FROM orders WHERE customer_id = 101); - BSELECT order_id, order_date FROM orders
WHERE order_date > ALL
(SELECT MAX(order_date) FROM orders ) AND customer_id = 101; - CSELECT order_id, order_date FROM orders
WHERE order_date > ALL
(SELECT order_date FROM orders WHERE customer_id = 101) - DSELECT order_id, order_date FROM orders
WHERE order_date > IN
(SELECT order_date FROM orders WHERE customer_id = 101);
- ASELECT order_id, order_date FROM orders
35Which task can be performed by using a single Data Manipulation Language (DML) statement?
- ARemoving all data only from a single column on which a primary key constraint is defined.
- BRemoving all data from a single column on which a unique constraint is defined.
- CAdding a column with a default value while inserting a row into a table.
- DAdding a column constraint while inserting a row into a table.
36You must display details of all users whose username contains the string ch_. (Choose the best answer.)
Which query generates the required output?- ASELECT * FROM users
Where user_name LIKE '%ch_'; - BSELECT * FROM users
Where user_name LIKE '%ch_%'ESCAPE'%'; - CSELECT * FROM users
Where user_name LIKE 'ch\_%' ESCAPE '_'; - DSELECT * FROM users
Where user_name LIKE '%ch\_%' ESCAPE '\';
- ASELECT * FROM users
37View the Exhibit and examine the data in the PRODUCTS table.
You must display product names from the PRODUCTS table that belong to the Software/other category with minimum prices as either $2000 or $4000 and with no unit of measure.
You issue this query:
SQL > SELECT prod_name, prod_category, prod_min_price FROM products
Where prod_category LIKE '%Other%' AND (prod_min_price = '2000' OR prod_min_price = '4000') AND prod_unit_of_measure <> '';
Which statement is true?
- AIt executes successfully but returns no result
- BIt executes successfully and returns the required result.
- CIt generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
- DIt generates an error because the condition specified for the PROD_CATEGORY column is not valid.
38Examine the structure of the EMPLOYEES table.
You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output?
- ASELECT MIN(Salary) minsal, MAX(salary) maxsal FROM employees WHERE hire_date < SYSDATE-365 GROUP BY MIN(salary), MAX(salary);
- BSELECT minsal, maxsal FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employees WHERE hire_date < SYSDATE-365) GROUP BY maxsal, minsal;
- CSELECT minsal, maxsal FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employees WHERE hire_date < SYSDATE-365 GROUP BY MIN(salary), MAX(salary);
- DSELECT MIN(Salary), MAX(salary) FROM (SELECT salary FROM employees WHERE hire_date < SYSDATE-365);
39Which two statements are true regarding subqueries?
- AA subquery can appear on either side of a comparison operator.
- BOnly two subqueries can be placed at one level.
- CA subquery can retrieve zero or more rows.
- DThere is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
40Which two statements are true regarding the execution of the correlated subqueries?
- 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.
41Which two statement are true regarding table joins available in the Oracle Database server?
- 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.
42You issued this command:
SQL > DROP TABLE employees;
Which three statements are true?- ASequences used in the EMPLOYEES table become invalid.
- BIf there is an uncommitted transaction in the session, it is committed.
- CAll indexes and constraints defined on the table being dropped are also dropped.
- DThe space used by the EMPLOYEES table is always reclaimed immediately.
- EThe EMPLOYEES table can be recovered using the ROLLBACK command.
- FThe EMPLOYEES table may be moved to the recycle bin.
43View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best answer.)
The PROJ_TASK_DETAILS table stores information about project tasks and the relation between them.
The BASED_ON column indicates dependencies between tasks.
Some tasks do not depend on the completion of other tasks.
You must generate a report listing all task IDs, the task ID of any task upon which it depends and the name of the employee in charge of the task upon which it depends.
Which query would give the required result?
- ASELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p
JOIN proj_task_details d
ON (p.task_id = d.task_id); - BSELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p
FULL OUTER JOIN proj_task_details d
ON (p.based_on = d.task_id); - CSELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p
JOIN proj_task_details d
ON (p.based_on = d.task_id); - DSELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p
LEFT OUTER JOIN proj_task_details d
ON (p.based_on = d.task_id);
- ASELECT p.task_id, p.based_on, d.task_in_charge
44View the Exhibit and examine the structure of the SALES and PRODUCTS tables
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:
SQL > SELECT p.prod_id, count(s.prod_id)
FROM products p ______________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN operations can be used to obtain the required output?
- AFULL OUTER JOIN
- BJOIN
- CLEFT OUTER JOIN
- DRIGHT OUTER JOIN
45View the exhibit and examine the description of the EMPLOYEES table. You executed this SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the result?- 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 the 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.
46Examine the structure of the SALES table.
Examine this statement:
SQL > CREATE TABLE sales1 (prod_id, cust_id, quantity_sold, price) AS SELECT product_id, customer_id, quantity_sold, price FROM sales WHERE 1 = 2;
Which two statements are true about the SALES1 table?
- AIt will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
- BIt will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
- CIt will not be created because of the invalid WHERE clause.
- DIt is created with no rows.
- EIt has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
47Examine this SELECT statement and view the Exhibit to see its output:
SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule, status, FROM user_constraints WHERE table_name = 'ORDERS';
Which two statements are true about the output?
- AThe DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.
- BThe R_CONSTRAINT_NAME column contains an alternative name for the constraint.
- CIn the second column, c indicates a check constraint.
- DThe STATUS column indicates whether the table is currently in use.
48Which two statements are true regarding constraints?
- AA constraint is enforced only for an INSERT operation on a table.
- BA foreign key cannot contain NULL values.
- CThe column with a UNIQUE constraint can store NULLS
- DAll constraints can be defined at the column level and at the table level
- EA constraint can be disabled even if the constraint column contains data
49Which two statements are true regarding working with dates?
- 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.
50View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables
You executed this UPDATE statement:
UPDATE ( SELECT order_date, order_total, customer_id FROM orders) Set order_date = '22-mar-2007' WHERE customer_id IN (SELECT customer_id FROM customers WHERE cust_last_name = 'Roberts' AND credit_limit = 600);
Which statement is true regarding the execution?- AIt would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
- BIt would not execute because two tables cannot be referenced in a single UPDATE statement.
- CIt would execute and restrict modifications to the columns specified in the SELECT statement.
- DIt would not execute because a SELECT statement cannot be used in place of a table name.
51View the Exhibit and examine the structure of the PRODUCTS table
You must display the category with the maximum number of items.
You issue this query:
SQL > SELECT COUNT(*), prod_category_id FROM products GROUP BY prod_category_id HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);
What is the result?
- AIt generates an error because = is not valid and should be replaced by the IN operator.
- BIt executes successfully but does not give the correct output.
- CIt executes successfully and gives the correct output.
- DIt generate an error because the subquery does not have a GROUP BY clause.
52Examine the structure of the MEMBERS table: (Choose the best answer.)
Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC;
What would be the result execution?
- AIt displays all cities in descending order, within which the last names are further sorted in descending order.
- BIt fails because a column alias cannot be used in the ORDER BY clause.
- CIt fails because a column number and a column alias cannot be used together in the ORDER BY clause
- DIt displays all cities in ascending order, within which the last names are further sorted in descending order
53View and Exhibit and examine the structure and data in the INVOICE table.
Which two statements are true regarding data type conversion in query expressions?
- Ainv_date = '15-february-2008' :uses implicit conversion
- Binv_amt = '0255982' : requires explicit conversion
- Cinv_date > '01-02-2008' : uses implicit conversion
- DCONCAT(inv_amt, inv_date) : requires explicit conversion
- Einv_no BETWEEN '101' AND '110' : uses implicit conversion
54Examine 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.
- ASELECT last_name, hire_date, salary FROM employees WHERE salary > 10000
UNION ALL
SELECT last_name, hire_date, salary FROM employees WHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)
INTERSECT
SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE- 180; - BSELECT last_name, hire_date, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)
UNION ALL
(SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180
INTERSECT
SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000); - CSELECT last_name, hire_date, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)
UNION
SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180
INTERSECT
SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000; - D(SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000 UNION ALL SELECT last_name, hire_date, salary FROM employees WHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)) UNION SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180;
- ASELECT last_name, hire_date, salary FROM employees WHERE salary > 10000
55Examine the structure of the PROMOTIONS table:
Management requires a report of unique promotion costs in each promotion category.
Which query would satisfy this requirement?
- ASELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1
- BSELECT promo_category, DISTINCT promo_cost FROM promotions
- CSELECT DISTINCT promo_cost, promo_category FROM promotions
- DSELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
56You must create a table for a banking application. (Choose the best answer.)
One of the columns in the table has these requirements:
1: A column to store the duration of a short team loan
2: The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.
3: The maximum loan period is 30 days.
4: Interest must be calculated based on the number of days for which the loan remains unpaid.
Which data type would you use?- ADate
- BNumber
- CTimestamp
- DInterval day to second
- EInterval year to month
57Examine the structure of the CUSTOMERS table:
CUSTNO is the PRIMARY KEY.
You must determine if any customers details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?
- ASubquery
- BSelf-join
- CFull outer-join with self-join
- DLeft outer-join with self-join
- ERight outer-join with self-join
58Which two are the minimal requirements for a self-join?
- AOnly equijoin conditions may be used in the query.
- BOuter joins must not be used in the query.
- CThere must be a condition on which the self-join is performed.
- DNo other condition except the self-join may be specified.
- EThe table used for the self-join must have two different alias names in the query.
59Examine the SQL statement used to create the TRANSACTION table
SQL > CREATE TABLE transaction
(trn_id char(2) primary key,
Start_date date DEFAULT SYSDATE,
End_date date NOT NULL);
The value A1 does not exist for trn_id in this table.
Which SQL statement successfully inserts a row into the table with the default value for START_DATE?- AINSERT INTO transaction VALUES ('A1',DEFAULT, TO_DATE(DEFAULT+10))
- BINSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))
- CINSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')
- DINSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1',, '10-DEC-2014')
60Which three SQL statements would display the value 1890.55 as $1,890.55?
- ASELECT TO_CHAR (1890.55, $99G999D00) FROM DUAL
- BSELECT TO_CHAR (1890.55, $9,999V99) FROM DUAL
- CSELECT TO_CHAR (1890.55, $0G000D00) FROM DUAL
- DSELECT TO_CHAR (1890.55, $99,999D99) FROM DUAL
- ESELECT TO_CHAR (1890.55, $99G999D99) FROM DUAL
61A subquery is called a single-row subquery when _______.
- AThere is only one subquery in the outer query and the inner query returns one or more values
- BThe inner query returns a single value to the outer query.
- CThe inner query uses an aggregating function and returns one or more values.
- DThe inner query returns one or more values and the outer query returns a single value.
62You must write a query that prompts users for column names and conditions every time it is executed. The user must be prompted only once for the table name.
Which statement achieves those objectives?- ASELECT &col1, '&col2'
FROM &table
WHERE &&condition = '&cond'; - BSELECT &col1, &col2
FROM "&table"
WHERE &condition = &cond; - CSELECT &col1, &col2
FROM &&table
WHERE &condition = &cond; - DSELECT &col1, &col2
FROM &&table
WHERE &condition = &&cond
- ASELECT &col1, '&col2'
63Which three statements are true regarding single-row functions?
- AThe data type returned, can be different from the data type of the argument that is referenced.
- BThey can return multiple values of more than one data type.
- CThey can accept only one argument.
- DThey can be nested up to only two levels.
- EThey can be used in SELECT, WHERE, and ORDER BY clauses.
- FThey can accept column names, expressions, variable names, or a user-supplied constants as arguments.
64View the Exhibit and examine the structure in the DEPARTMENTS tables.
Examine this SQL statement:
SELECT department_id "DEPT_ID", department_name, 'b' FROM departments WHERE departments_id=90
UNION
SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10
Which two ORDER BY clauses can be used to sort output?- AORDER BY DEPT_NAME;
- BORDER BY DEPT_ID;
- CORDER BY b;
- DORDER BY 3;
65Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
- 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.
66You 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?- ACREATE 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)); - BCREATE TABLE employees
( employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY,
login_id NUMBER UNIQUE,
employee_name VARCHAR2(25),
hire_date DATE); - CCREATE 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)); - 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)); - ECREATE 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));
- ACREATE TABLE employees
67View the Exhibit and examine the structure of the PORDUCT_INFORMATION table.
PRODUCT_ID column is the primary key. You create an index using this command:
SQL > CREATE INDEX upper_name_idx ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table. Which query would use the UPPER_NAME_IDX index?- ASELECT product_id, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name) = 'LASERPRO'
OR list_price > 1000; - BSELECT UPPER(product_name)
FROM product_information; - CSELECT UPPER(product_name)
FROM product_information
WHERE product_id = 2254; - DSELECT product_id
FROM product_information
WHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');
- ASELECT product_id, UPPER(product_name)
68Examine the types and examples of relationship that follows:
1 One-to-one - a) teacher to Student
2 One-to-many - b) Employees to Manager
3 Many-to-one - c) Person to SSN
4 Many-to-many - d) Customers to Products
Which option indicates correctly matched relationships?- A1-d, 2-b, 3-a, and 4-c
- B1-c, 2-d, 3-a, and 4-b
- C1-a, 2-b, 3-c, and 4-d
- D1-c, 2-a, 3-b, and 4-d
69A non-correlated subquery can be defined as __________.
- 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.
70Which three statements are true reading subqueries?
- AA Main query can have many subqueries.
- BA subquery can have more than one main query.
- CThe subquery and main query must retrieve date from the same table.
- DThe subquery and main query can retrieve data from different tables.
- EOnly one column or expression can be compared between the subquery and main query.
- FMultiple columns or expressions can be compared between the subquery and main query.
71See the Exhibit and examine the structure of the PROMOTIONS table:
Using the PROMOTIONS table, you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
SELECT AVG(CASE WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category='A' THEN promo_cost ELSE null END) "CAT_2000A",
AVG(CASE WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category='A' THEN promo_cost ELSE null END) "CAT_5000A" FROM PROMOTIONS;
What would be the outcome?
- AIt generates an error because multiple conditions cannot be specified for the WHEN clause.
- BIt executes successfully and gives the required result.
- CIt generates an error because CASE cannot be used with group functions.
- DIt generates an error because NULL cannot be specified as a return value.
72Which two statements are true regarding multiple-row subqueries?
- 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.
73View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.
The CUSTOMERS table contains the current location of all currently active customers.
The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?- AINTERSECT
- BUNION ALL
- CMINUS
- DUNION
74View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;
What would happen when the above statement is executed?
- AThe statement would execute successfully to produce the required output.
- BThe statement would not execute because inline views and outer joins cannot be used together.
- CThe statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
- DThe statement would not execute because the GROUP BY clause cannot be used in the inline.
75Which statement is true regarding the UNION operator?
- ABy default, the output is not sorted.
- BNull values are not ignored during duplicate checking.
- CNames of all columns must be identical across all select statements.
- DThe number of columns selected in all select statements need not be the same.
76You issued the following command:
SQL> DROP TABLE employees;
Which three statements are true?- 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
77Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);
SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));
You executed the following statement:
SQL> DELETE from stores WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully?- ADisable the primary key in the STORES table.
- BUse CASCADE keyword with DELETE statement.
- CDELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
- DDisable the FOREIGN KEY in SALES table and then delete the rows.
- ECreate the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.
78In the customers table, the CUST_CITY column contains the value Paris for the CUST_FIRST_NAME Abigail. Evaluate the following query
What would be the outcome?
- AAbigail PA
- BAbigail Pa
- CAbigail IS
- DAn error message
79Which two statements are true regarding constraints?
- AA foreign key column cannot contain null values.
- BA column with the UNIQUE constraint can contain null values.
- CA constraint is enforced only for INSERT operation on the table.
- DA constraint can be disabled even if the constraint column contains data.
- EAll constraints can be defined at the column level and at the table level.
80On 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?- 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.
81You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference.
Which method or feature should you use?- ACompare Period ADDM report.
- BAWR Compare Period report.
- CActive Session History (ASH) report.
- DTaking a new snapshot and comparing it with a preserved snapshot.
82Which 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.
83Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c?
- AALTER TABLE DROP PARTITION
- BALTER TABLE ADD PARTITION
- CALTER TABLE MOVE PARTITION
- DALTER TABLE MERGE PARTITION
- EALTER TABLE SPLIT PARTITION
- FALTER TABLE TRUNCATE PARTITION
84View the Exhibits and examine PRODUCTS and SALES tables.
You issue the following query to display product name and the number of times the product has been sold:
SQL>SELECT p.prod_name, i.item_cnt
FROM (SELECT prod_id, COUNT(*) item_cnt
FROM sales
GROUP BY prod_id) i RIGHT OUTER JOIN products p
ON i.prod_id = p.prod_id;
What happens when the above statement is executed?
- AThe statement executes successfully and produces the required output.
- BThe statement produces an error because ITEM_CNT cannot be displayed in the outer query.
- CThe statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
- DThe statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.
85Examine the structure of the BOOKS_TRANSACTIONS table:
Examine the SQL statement:
SELECT * FROM books_transactions WHERE borrowed_date < SYSDATE AND transaction_type = 'RM' OR MEMBER_ID IN ('A101', 'A102'):
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
86View the Exhibit and examine the data in the EMPLOYEES table.
You want to generate a report showing the total compensation paid to each employee to date.
You issue the following query:
SELECT ename || 'joined on ' || hiredate || ', the total compesation paid is' || TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365* sal+comm ) "COMPENSATION UNTIL DATE" FROM employees;
What is the outcome?
- AIt generates an error because the alias is not valid.
- BIt executes successfully and gives the correct output
- CIt executes successfully but does not give the correct output
- DIt generates an error because the usage of the ROUND function in the expression is not valid.
- EIt generates an error because the concatenation operator can be used to combine only two items.
87Evaluate the following query:
SELECT INTERVAL ‘300’ MONTH,
INTERVAL ’54-2′ YEAR TO MONTH,
INTERVAL ’11:12:10.1234567′ HOUR TO SECOND
FROM dual;
What is the correct output of the above query?- A+25-00 , +54-02, +00 11:12:10.123457
- B+00-300, +54-02, +00 11:12:10.123457
- C+25-00 , +00-650, +00 11:12:10.123457
- D+00-300 , +00-650, +00 11:12:10.123457
88Which two statements are true regarding savepoints?
- ASavepoints are effective only for COMMIT
- BSavepoints may be used to ROLLBACK
- CSavepoints can be used for only DML statements.
- DSavepoints are effective for both COMMIT and ROLLBACK.
- ESavepoints can be used for both DML and DDL statements.
89Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:
You want to generate a list of all department IDs that do not exist in the COURSE_DETAILS table.
You execute the SQL statement:
SELECT d.department_id FROM course_details c INNER JOIN department_details d ON c.department_id <> d.department_id;
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
90View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
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:
SELECT product_name
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;
Which statement is true regarding the execution?
- AIt would not execute because the entire WHERE clause is not enclosed within parentheses
- BIt would execute but would return no rows
- CIt would not execute because the same column has been used twice with the AND logical operator
- DIt would execute and return the desired result.
91You 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 + 1000 AS "New Credit Limit" FROM customers;
- BSELECT cust_last_name AS Name, cust_credit_limit + 1000 AS New Credit Limit FROM 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 + 1000 INITCAP ("NEW CREDIT LIMIT") FROM customers;
92View the Exhibit and examine the structure of the ORDERS table.
Which UPDATE statement is valid?- AUPDATE orders SET order_date = '12-mar-2007', order_total IS NULL WHERE order_id = 2455;
- BUPDATE orders SET order_date = '12-mar-2007', AND order_total = TO_NUMBER(NULL) WHERE order_id = 2455;
- CUPDATE orders SET order_date = '12-mar-2007', order_total = NULL WHERE order_id = 2455;
- DUPDATE orders SET order_date = TO_DATE('12-mar-2007','dd-mon-yyyy'), SET order_total = TO_NUMBER (NULL) WHERE order_id = 2455;
93SCOTT 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.
94View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
Which two tasks would require subqueries?
- 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
95View 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;
96View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
SELECT promo_name, CASE WHEN promo_cost >= (SELECT AVG (promo_cost) FROM promotions WHERE promo_category = 'TV') THEN 'HIGH' ELSE 'LOW' END COST_REMARK FROM promotions
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.
97Which statement is true regarding the USING clause in table joins?
- AIt can be used to join a maximum of three tables
- BIt can be used to access data from tables through equijoins as well as nonequijoins
- CIt can be used to join tables that have columns with the same name and compatible data types
- DIt can be used to restrict the number of columns used in a NATURAL join
98Examine the structure proposed for the TRANSACTIONS table:
Which two statements are true regarding the storage of data in the above table structure?
- 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.
99Examine the structure of the MARKS table:
Which two statements would execute successfully?
- ASELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) FROM marks WHERE subject1 > subject2;
- BSELECT student_name subject1 FROM marks WHERE subject1 > AVG(subject1);
- CSELECT SUM(subject1+subject2+subject3) FROM marks WHERE student_name IS NULL;
- DSELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE "˜R%';
100Examine 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:
SELECT c1.custname, c1.city FROM Customers C1 ____________ Customers C2 ON (c1.city = c2.city AND c1.custname <> c2.custname);
Which two JOIN options can be used in the blank in the above query to give the correct output?
- ALEFT OUTER JOIN
- BJOIN
- CNATURAL JOIN
- DRIGHT OUTER JOIN
- EFULL OUTER JOIN
101Examine the structure proposed for the TRANSACTIONS table:
Which statements are true regarding the creation and storage of data in the above table structure?
- AThe CUST_STATUS column would store exactly one character.
- BThe TRANS_VALIDITY column would have a maximum size of one character.
- CThe CUST_CREDIT_LIMIT column would be able to store decimal values.
- DThe CUST_STATUS column would give an error.
- EThe TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
- FThe TRANS_VALIDITY column would give an error.
102View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.
You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task?- ASELECT i.product_id, i.quantity_on_hand, pi.supplier_id FROM product_information pi JOIN inventories i ON (pi.product_id=i.product_id) WHERE quantity_on_hand < 5;
- BSELECT product_id, quantity_on_hand, supplier_id FROM product_information NATURAL JOIN inventories AND quantity_on_hand < 5;
- CSELECT i.product_id, i.quantity_on_hand, pi.supplier_id FROM product_information pi JOIN inventories i ON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
- DSELECT i.product_id, i.quantity_on_hand, pi.supplier_id FROM product_information pi JOIN inventories i USING (product_id) AND quantity_on_hand < 5;
103In 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.
104Which statement adds a column called SALARY to the EMPLOYEES table having 100 rows, which cannot contain null?
- AALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL;
- BALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
- CALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT NOT NULL;
- DALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) NOT NULL;
105View 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?
- ASELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category = 'post' AND promo_begin_date < '01-01-00';
- BSELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';
- CSELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';
- DSELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';
106Which two statements are true regarding views?
- 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.
107View the Exhibit and examine the structure of CUSTOMERS table.
Evaluate the following query:
SELECT cust_id, cust_city
FROM customers
WHERE cust_first_name NOT LIKE 'A_%g_%'
AND cust_credit_limit BETWEEN 5000 AND 15000
AND cust_credit_limit NOT IN (7000,11000)
AND cust_city NOT BETWEEN 'A' AND 'B'
Which statement is true regarding the above query?- AIt produces an error because the condition on the CUST_CITY column is not valid.
- BIt produces an error because the condition on the CUST_FIRST_NAME column is not valid.
- CIt produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.
- DIt executes successfully.
108Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?- A100
- Ban error
- C10
- D1
109Which 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.
110Which three statements indicate the end of a transaction?
- Aafter a CREATE statement is issued
- Bafter a SAVEPOINT is issued
- Cafter a SELECT statement is issued
- Dafter a ROLLBACK is issued
- Eafter a COMMIT is issued
111Examine 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:
UPDATE books_transactions
SET book_id = 'INVALID'
WHERE ______________
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 = "";
112Evaluate the following SQL commands:
The command to create a table fails. Identify the reason for the SQL statement failure.
- AYou cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.
- BYou cannot use the BETWEEN clause in the condition of a CHECK constraint.
- CYou cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
- DYou cannot use SYSDATE in the condition of a CHECK constraint.
113Evaluate 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.
114View the exhibit and examine the description for the SALES and CHANNELS tables.
You issued this SQL statement:
INSERT INTO sales VALUES (23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel_desc = 'Direct Sales'), 12, 1, 500);
Which statement is true regarding the result?
- AThe statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.
- BThe statement will fail because a subquery cannot be used in a VALUES clause.
- CThe statement will fail because the VALUES clause is not required with a subquery.
- DThe statement will execute and a new row will be inserted in the SALES table.
115View the Exhibit and examine the structure of the CUSTOMERS table.
Evaluate the following SQL statement:
SELECT cust_city, COUNT(cust_last_name)
FROM customers
WHERE cust_credit_limt > 1000
GROUP BY cust_city
HAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;
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.
116You notice that the elapsed time for an important database scheduler Job is unacceptably long. The job belongs to a scheduler job class and window. Which two actions would reduce the job’s elapsed time?
- AIncreasing the job’s relative priority within the Job class to which it belongs
- BIncreasing the priority of the scheduler window to which the job belongs
- CIncreasing the value of the JOB_QUEUE_PROCESSES parameter
- DIncreasing the resource allocation for the consumer group mapped to the scheduler job’s
job class within the plan mapped to the scheduler window - EIncreasing the priority of the job class to which the job belongs
- FMoving the job to an existing higher priority scheduler window with the same schedule
and duration
117You run a script that completes successfully using SQL*Plus that performs these actions: 1.Creates a multitenant container database (CDB) 2. Plugs in three pluggable databases (PDBs) 3. Shuts down the CDB instance 4. Starts up the CDB instance using STARTUP OPEN READ WRITE Which two statements are true about the outcome after running the script?
- AThe PDBs will be opened read/write.
- BThe seed will be in mount state.
- CThe seed will be opened read-only
- DThe seed will be opened read/write.
- EThe other PDBs will be opened read-only.
- FThe other PDBs will be in mount state.
118Identify three valid methods of opening, pluggable databases (PDBs).
- AALTER PLUGGABLE DATABASE OPEN ALL ISSUED from the root
- BALTER PLUGGABLE DATABASE OPEN ALL ISSUED from a PDB
- CALTER PLUGGABLE DATABASE PDB OPEN issued from the seed
- DALTER DATABASE PDB OPEN issued from the root
- EALTER DATABASE OPEN issued from that PDB
- FALTER PLUGGABLE DATABASE PDB OPEN issued from another PDB
- GALTER PLUGGABLE DATABASE OPEN issued from that PDB
119Which two are false about the large pool for an Oracle database instance that supports shared server connections?
- AAllocates memory for RMAN backup and restore operations
- BAllocates memory for shared and private SQL areas
- CContains a cursor area for storing runtime information about cursors
- DContains stack space
- EContains a hash area performing hash joins of tables
120Which two action are impossible during an Online Datafile Move operation?
- ACreating and dropping tables in the datafile being moved
- BPerforming file shrink of the data file being moved
- CQuerying tables in the datafile being moved
- DPerforming Block Media Recovery for a data block in the datafile being moved
- EFlashing back the database
- FExecuting DML statements on objects stored in the datafile being moved
121Which two statements are true about the SET VERIFY ON command?
- AIt can be used in SQL Developer and SQL*Plus
- BIt displays values for variables used only in the WHERE clause of a query
- CIt can be used only in SQL*Plus
- DIt displays values for variables prefixed with &&
- EIt displays values for variables created by the DEFINE command
122Which two statements are true about the rules of precedence for operators?
- AThe concatenation operator | | is always evaluated before addition and subtraction in an expression
- BMultiple parentheses can be used to override the default precedence of operators in an expression
- CArithmetic operators with equal precedence area evaluated from left to right within an expression
- DNULLS influence the precedence of operators in an expression
- EThe + binary operator has the highest precedence in an expression in a SQL statement
123Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?
- AIt is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted
- BOnly the primary key can be defined at the column and table level
- CThe foreign key columns and parent table primary key columns must have the same names
- DA table can have only one primary key and one foreign key
- EA table can have only one primary key but multiple foreign keys
- FPrimary key and foreign key constraints can be defined at both the column and table level
- GIt 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
124Which three are true about system and object privileges?
- AWITH GRANT OPTION can be used when granting an object privilege to both users and roles
- BAdding a primary key constraint to an existing table in another schema requires a system privilege
- CAdding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege
- DRevoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect
- ERevoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect.
- FWITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC
125Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)?
- 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
126Which two statements are true about substitution variables?
- AA substitution variable can be used with any clause in a SELECT statement
- BA substitution variable used to prompt for a column name must be enclosed in a single quotation marks
- CA substitution variable prefixed with & always prompts only once for a value in a session
- DA substitution variable can be used only in a SELECT statement
- EA substitution variable used to prompt for a column name must be enclosed in double quotation marks
- FA substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session
127Which three statements are true about inner and outer joins?
- AA full outer join must use Oracle syntax
- BAn inner join returns matched rows
- CA left or right outer join returns only unmatched rows
- DA full outer join returns matched and unmatched rows
- EOuter joins can only be used between two per query
- FOuter joins can be used when there are multiple join conditions on two tables
128Which three statements are true about the DESCRIBE command?
- AIt can be used to display the structure of an existing view
- BIt can be used only from SQL*Plus
- CIt displays the PRIMARY KEY constraint for any column or columns that have that constraint
- DIt can be used from SQL Developer
- EIt displays all constraints that are defined for each column
- FIt displays the NOT NULL constraint for any columns that have that constraint
129The 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 name1), 'An', 'O') FROM names;
- BSELECT INITCAP (REPLACE(TRIM('son' FROM name1), 'An', 'O')) FROM names;
- CSELECT REPLACE (SUBSTR(name1, -3), 'An', 'O') FROM names;
- DSELECT REPLACE (REPLACE(name1, 'son', ''), 'An', 'O') FROM names;
130Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined?
- AInsert statements can always be done on a table through a view.
- BViews 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.
- CViews 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.
- DDelete statements can always be done on a table through a view.
- EThe WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
- FViews cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.
131Evaluate these commands which execute successfully:
Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?
- ASequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
- BAny user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.
- CColumn ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.
- DIf sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.
- ESequence ORD_SEQ is guaranteed not to generate duplicate numbers.
132Which two statements are true about INTERVAL data types
- AThe YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.
- BINTERVAL DAY TO SECOND columns support fractions of seconds.
- CINTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.
- DINTERVAL YEAR TO MONTH columns support yearly intervals.
- EINTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.
- FThe value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.
133An 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?- 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
134Which three statements are true regarding indexes?
- AA SELECT statement can access one or more indices without accessing any tables
- BAn update to a table can result in no updates to any of the table's indexes
- CA table belonging to one user can have an index that belongs to a different user
- DA UNIQUE index can be altered to be non-unique
- EAn update to a table can result in updates to any or all of the table's indexes
- FWhen a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped
135Which three are true about the CREATE TABLE command?
- 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
136You need to calculate the number of days from 1
January 2019 until today.
Dates are stored in the default format of DD-MON-RR.
Which two queries give the required output?- ASELECT to_char(sysdate, 'DD-MON-YYYY') - '01-JAN-2019' FROM DUAL;
- BSELECT sysdate-to_date('01-JANUARY-2019') FROM DUAL;
- CSELECT ROUND(sysdate-'01-JAN-2019') FROM DUAL;
- DSELECT ROUND(sysdate-TO_DATE('01/JANUARY/2019')) FROM DUAL;
- ESELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL;
137Which three actions can you perform by using the ORACLE_DATAPUMP access driver?
- ARead data from an external table and load it into a table in the database
- BCreate a directory object for an external table
- CExecute DML statements on an external table
- DQuery data from an external table
- ERead data from a table in the database and insert it into an external table
- FCreate a directory object for a flat file
138Which three privileges can be restricted to a subset of columns in a table?
- AALTER
- BDELETE
- CUPDATE
- DSELECT
- EINDEX
- FREFERENCES
- GINSERT
139You have been tasked to create a table for a banking application.
One of the columns must meet three requirements:
1) Be stored in a format supporting date arithmetic without using conversion functions
2) Store a load period of up to 10 years
3) Be used for calculating interest for the number of days the loan remains unpaid
Which data type should you use?- ATIMESTAMP WITH LOCAL TIMEZONE
- BTIMESTAMP WITH TIMEZONE
- CINTERVAL DAY TO SECOND
- DTIMESTAMP
- EINTERVAL YEAR TO MONTH
140The ORDERS table has a primary key constraint on the ORDER_ID column.
The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table.
The constraint is defined with ON DELETE CASCADE.
There are rows in the ORDERS table with an ORDER_TOTAL of less than 1000.
Which three DELETE statements execute successfully?- ADELETE order_id FROM orders WHERE order_total < 1000;
- BDELETE FROM orders WHERE order_total < 1000;
- CDELETE orders WHERE order_total < 1000;
- DDELETE * FROM orders WHERE order_total < 1000;
- EDELETE FROM orders;
141Which three statements are true about single-row functions?
- AThey can be nested to any level
- Bhe 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
142Which two statements are true regarding a SAVEPOINT?
- AA SAVEPOINT does not issue a COMMIT
- BOnly one SAVEPOINT may be issued in a transaction
- CRolling back to a SAVEPOINT can undo a TRUNCATE statement
- DRolling back to a SAVEPOINT can undo a CREATE INDEX statement
- ERolling back to a SAVEPOINT can undo a DELETE statement
143Which two are true about savepoints?
- AAfter issuing a savepoints, you can roll back to the savepoint name within the current transaction.
- BA ROLLBACK TO SAVEPOINT command issued before the start of a transaction results in an error.
- CThey make uncommitted updates visible to other sessions owned by the same user.
- DAfter issuing a savepoint, you cannot roll back the complete transaction.
- EYou can commit updates done between two savepoints without committing other updates in the current transaction
- FThey make uncommitted updates visible to sessions owned by other users
144ALTER TABLE employees SET UNUSED (department_id)
Which two are true?- ANo updates can be made to the data in the DEPARTMENT_ID column.
- BA new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.
- CA query can be display data from the DEPARTMENT_ID column.
- DThe DEPARTMENT_ID column is set to null for all rows in the table.
- EThe DEPARTMENT_ID column can be recovered from the recycle bin.
- FThe storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.
145The SYSDATE function displays the current Oracle Server date as:
21-MAY-19
You wish to display the date as:
MONDAY, 21 MAY, 2019 -
Which statement will do this?- ASELECT TO_DATE(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;
- BSELECT TO_CHAR(SYSDATE, 'FMDD, DAY MONTH, YYYY') FROM DUAL;
- CSELECT TO_CHAR(SYSDATE, 'FMDAY, DDTH MONTH, YYYY') FROM DUAL;
- DSELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;
146Which two are SQL features?
- Aprocessing sets of data
- Bproviding update capabilities for data in external files
- Cproviding graphical capabilities
- Dproviding variable definition capabilities
- Eproviding database transaction control
147Which two are true about scalar subquery expressions?
- AThey can return at most one row.
- BYou can use them as a default value for a column
- CYou cannot correlate them with a table in the parent statement.
- DYou must enclose them in parentheses.
- EThey can return two columns.
148You have the privileges to create any type of synonym.
Which statement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is accessible to all users?- ACREATE PUBLIC SYNONIM emp FOR hcm.employee_records;
- BCREATE GLOBAL SYNONIM emp FOR hcm.employee_records;
- CCREATE SYNONIM emp FOR hcm.employee_records;
- DCREATE SYNONIM PUBLIC.emp FOR hcm.employee_records;
- ECREATE SYNONIM SYS.emp FOR hcm.employee_records;
149A session's NLS_DATE_FORMAT is set to DD Mon YYYY.
Which two queries return the value 1 Jan 2019?- ASELECT TO_DATE('2019-01-01') FROM DUAL;
- BSELECT DATE '2019-01-01' FROM DUAL;
- CSELECT '2019-01-01' FROM DUAL;
- DSELECT TO_CHAR('2019-01-01') FROM DUAL;
- ESELECT TO_DATE('2019-01-01', 'YYYY-MM-DD') FROM DUAL;
150No-user-defined locks are used in your database.
Which three are true about Transaction Control Language (TCL)?- ACOMMIT erases all the transaction's savepoints and releases its locks.
- BROLLBACK TO SAVEPOINT undoes the transaction's changes made since the named savepoint and then ends the transaction.
- CCOMMIT ends the transaction and makes all its changes permanent.
- DROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints.
- EROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not release its locks.
- FROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes, releases its locks and erases its savepoints.
Informações
- Descrição
- examen corregido
- Autor
- Juan Taborda
- Idioma
- Español
- Visualizações
- 1.443
- Etiquetas
- Avaliação
- Ainda sem avaliações Avalie este quiz