Quiz
Simulado 4 - (46) 1Z0-071 - Oracle Database SQL
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 (46)
1You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL. If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL. All new customer details have to be inserted into the CUST table. Which technique should beused to load the data most efficiently?
- Aexternal table
- Bthe MERGE command
- Cthe multitable INSERT command
- DINSERT using WITH CHECK OPTION
2Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)
- AIt can be used to concatenate two strings.
- BIt can be used to find out the total length of the string.
- CIt can be used for string manipulation and searching operations.
- DIt can be used to format the output for a column or expression having string data.
- EIt can be used to find and replace operations for a column or expression having string data.
3Evaluate the following SQL statement:
ALTER TABLE emp
DROP COLUMN first_name;
Which two statements is true regarding the above command? (Choose two.)
- AThe FIRST_NAME column would be dropped provided it does not contain any data.
- BThe FIRST_NAME column would be dropped provided at least one or more columns remain in the table.
- CThe FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the above SQL statement.
- DThe FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY
provided the CASCADE option is used.
4Evaluate the CREATE TABLE statement:
CREATE TABLE products
(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY,
product_name VARCHAR2(15));
Which statement is true regarding the PROD_ID_PK constraint?- AIt would be created only if a unique index is manually created first.
- BIt would be created and would use an automatically created unique index.
- CIt would be created and would use an automatically created no unique index.
- DIt would be created and remains in a disabled state because no index is specified in the command.
5Which two statements are true?
- AThe USER_SYNONYMS view can provide information about private synonyms.
- BThe user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
- CAll the dynamic performance views prefixed with V$ are accessible to all the database users.
- DThe USER_OBJECTS view can provide information about the tables and views created by the user only.
- EDICTIONARY is a view that contains the namesof all the data dictionary views that the user can access.
6Which two WHERE clause conditions demonstrate the correct usage of conversion functions?
- 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') )
7View the Exhibit and examine the description of the EMPLOYEES table.
Your company decided to give a monthly bonus of $50 to all the employees who have completed
five years in the company. The following statement is written to display the LAST_NAME,
DEPARTMENT_ID, and the total annual salary:
SELECT last_name, department_id, salary+50*12 "Annual Compensation"
FROM employees
WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 >= 5;
When you execute the statement, the "Annual Compensation" is not computed correctly. What
changes would you make to the query to calculate the annual compensation correctly?
- AChange the SELECT clause to SELECT last_name, department_id, salary*12+50 "Annual Compensation".
- BChange the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation".
- CChange the SELECT clause to SELECT last_name, department_id, (salary+50)*12 "Annual Compensation".
- DChange the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation".
8Evaluate 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?- A1
- B10
- C100
- Dan error
9View the Exhibit and examine the description of the EMPLOYEES table.
You want to display the EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYEE_ID 104. To accomplish the task, you execute the following SQL statement:
SELECT employee_id, first_name, department_id
FROM employees
WHERE (manager_id, department_id) =(SELECT department_id, manager_id
FROM employees
WHERE employee_id = 104)
AND employee_id <> 104;
When you execute the statement itdoes not produce the desired output. What is the reason for this?
- AThe WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS.
- BThe WHERE clause condition in the main query is using the = comparison operator, instead of
the IN operator. - CThe WHERE clause condition in the main query is using the = comparison operator, instead of
the = ANY operator. - DThe columns in the WHERE clause condition ofthe main query and the columns selected in
the subquery should be in the same order.
10View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables.
You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by QUANTITY) for the order placed. You also want to display the subtotals for a CUSTOMER_ID as well as for a PRODUCT_ID for the last six months.
Which SQL statement would you execute to get the desired output?
- ASELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" FROM
order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id,oi.product_id)
WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6; - BSELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total"
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id,oi.product_id)
HAVING MONTHS_BETWEEN(order_date, SYSDATE) <= 6; - CSELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total"
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id, oi.product_id)
WHERE MONTHS_BETWEEN(order_date, SYSDATE) >= 6; - DSELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total"
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6
GROUP BY ROLLUP (o.customer_id, oi.product_id) ;
- ASELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" FROM
11Which two statements is 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.
12OE and SCOTT are the users in the database. The ORDERS table is owned by OE. Evaluate the statements issued by the DBA in the following sequence:
CREATE ROLE r1;
GRANT SELECT, INSERT ON oe.orders TO r1;
GRANT r1 TO scott;
GRANT SELECT ON oe.orders TO scott;
REVOKE SELECT ON oe.orders FROM scott;
What would be the outcome after executing the statements?- ASCOTT would be able to query the OE.ORDERS table.
- BSCOTT would not be able to query the OE.ORDERS table.
- CThe REVOKE statement would remove the SELECT privilege from SCOTT as well as from the
role R1. - DThe REVOKE statement would give an error because the SELECT privilege has been granted
to the role R1.
13Evaluate the following SQL statement:
ALTER TABLE hr.emp
SET UNUSED (mgr_id);
Which statement is true regarding the effect of the above SQL statement?- AAny synonym existing on the EMP table would have to be re-created.
- BAny constraints defined on the MGR_ID column would be removed by the above command.
- CAny views created on the EMP table that include the MGR_ID column would have to be dropped and re-created.
- DAny index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed.
14EMPDET is an external table containing the columns EMPNO and ENAME.
Which command would work in relation to the EMPDET table?- AUPDATE empdet
SET ename = 'Amit'
WHERE empno = 1234; - BDELETE FROM empdet
WHERE ename LIKE 'J%'; - CCREATE VIEW empvu
AS
SELECT * FROM empdept; - DCREATE INDEX empdet_idx
ON empdet(empno);
- AUPDATE empdet
15View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables.
Which is the best method to load data fromthe MARKS_DETAILStable to the MARKStable?
- APivoting INSERT
- BUnconditional INSERT
- CConditional ALL INSERT
- DConditional FIRST INSERT
16View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables.
You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the view successfully?
- ACREATE OR REPLACE VIEW ord_vu (order_id,order_date)
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date; - BCREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date; - CCREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date; - DCREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)||' NO OF ITEMS'
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date
WITH CHECK OPTION;
- ACREATE OR REPLACE VIEW ord_vu (order_id,order_date)
17View 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
view.
18Which statement correctly grants a system privilege?
- AGRANT EXECUTE
ON proc1
TO PUBLIC; - BGRANT CREATE VIEW
ON table1 TO
user1; - CGRANT CREATE TABLE
TO user1,user2; - DGRANT CREATE SESSION
TO ALL;
- AGRANT EXECUTE
19View the Exhibit and examine the structure of the CUST table.
Evaluate the following SQL statements executed in the given order:
ALTER TABLE cust
ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED;
INSERT INTO cust VALUES (1,'RAJ'); --row 1
INSERT INTO cust VALUES (1,'SAM'); --row 2
COMMIT;
SET CONSTRAINT cust_id_pk IMMEDIATE;
INSERT INTO cust VALUES (1,'LATA'); --row 3
INSERT INTO cust VALUES (2,'KING'); --row 4
COMMIT;
Which rows would be made permanent in the CUST table?
- Arow 4 only
- Brows 2 and 4
- Crows 3 and 4
- Drows 1 and 4
20View the Exhibit and examine the structure of the ORDERS table:
The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint.
Evaluate the following statement:
INSERT INTO (SELECT order_id,order_date,customer_id
FROM ORDERS
WHERE order_total = 1000
WITH CHECK OPTION)
VALUES (13, SYSDATE, 101);
What would be the outcome of the above INSERT statement?
- AIt would execute successfully and the new row would be inserted into a new temporary table created by the subquery.
- BIt would execute successfully and the ORDER_TOTAL column would have the value 1000 inserted automatically in the new row.
- CIt would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it.
- DIt would not execute successfully because all the columns from the ORDERS table should have been included in the SELECT list and values should have been provided for all the columns.
21View the Exhibit and examine the description of the EMPLOYEES table.
Your company wants to give 5% bonus to all the employees on their annual salary. The SALARY column stores the monthly salary for an employee. To check the total for annual salary and bonus amount for each employee, you issued the following SQL statement:
SELECT first_name, salary, salary*12+salary*12*.05 "ANNUAL SALARY + BONUS"
FROM employees;
Which statement is true regarding the above query?
- AIt would execute and give you the desired output.
- BIt would not execute because the AS keyword is missing between the column name and the alias.
- CIt would not execute because double quotation marks are used instead of single quotation marks for assigning alias for the third column.
- DIt would execute but the result for the third column would be inaccurate because the parentheses for overriding the precedence of the operator are missing
22Which statement is true regarding external tables?
- AThe default REJECT LIMIT for external tables is UNLIMITED.
- BThe data and metadata for an externaltable are stored outside the database.
- CORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
- DThe CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.
23View the Exhibit and examine the structure of the PRODUCT_INFORMATION table.
You want to see the product names and the date ofexpiration of warranty for all the products, if the product is purchased today. The products thathave 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, category_id, SYSDATE+warranty_period AS "Warranty expire date"
FROM product_information ORDER BY SYSDATE-warranty_period; - BSELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date"
FROM product_information ORDER BY SYSDATE+warranty_period; - CSELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date"
FROM product_information ORDER BY SYSDATE; - DSELECT product_name, category_id, SYSDATE+warranty_period "Warranty expire date"
FROM product_information WHERE warranty_period >SYSDATE;
- ASELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date"
24Which two statements are true regarding the EXISTS operator used in the correlated subqueries?
- AThe outer query stops evaluating the result set ofthe inner query when 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.
25A non-correlated subquery can be defined as ____.
- Aa set of sequential queries, all of which must always return a single value
- Ba set of sequential queries, all of which must return values from the same table
- Ca SELECT statement that can be embedded ina clause of another SELECT statement only
- Da 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
26You need to create a table for a banking application with the following considerations:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and subtracted with
3) date type data without using the conversion functions.
4) The maximum period of the credit provision in the application is 30 days.
5) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?- AINTERVAL YEAR TO MONTH
- BINTERVAL DAY TO SECOND
- CTIMESTAMP WITH TIME ZONE
- DTIMESTAMP WITH LOCAL TIME ZONE
27Which statements are true regarding the hierarchical query in Oracle Database 10g?
- AIt is possible to retrieve data only in top-down hierarchy.
- BIt is possible to retrieve data in top-down or bottom-up hierarchy.
- CIt is possible to remove an entire branch from the output of the hierarchical query.
- DYou cannot specify conditions when you retrieve data by using a hierarchical query.
28Which two statements are true regarding views? (Choose two.)
- AA simple view in which column aliases have been used cannot be updated.
- BA subquery used in a complex view definition cannot contain group functions or joins.
- 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.
- EThe OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.
- FThe WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
29View the Exhibit and examine the details of the ORDER_ITEMS table.
Evaluate the following SQL statements:
Statement 1:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items;
Statement 2:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items
GROUP BY order_id;
Which statements are true regarding the output of these SQL statements? (Choose all that apply.)
- AStatement 1 would return only one row of output.
- BBoth the statements would give the same output.
- CStatement 2 would return multiple rows of output.
- DStatement 1 would not return any row because the GROUP BY clause is missing.
- EBoth statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.
30View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT oi.order_id, product_id, order_date
FROM order_items oi JOIN orders o
USING(order_id);
Which statement is true regarding the execution of this SQL statement?
- AThe statement would not execute because table aliases are not allowed in the JOIN clause.
- BThe statement would not execute because the table alias prefix is not used in the USING clause.
- CThe statement would not execute because all the columns in the SELECT clause are not prefixed with table aliases.
- DThe statement would not execute because the column part of the USING clause cannot have a qualifier in the SELECT list.
31Evaluate the following SQL statements in the given order:
DROP TABLE dept;
CREATE TABLE dept
(deptno NUMBER(3) PRIMARY KEY,
deptname VARCHAR2(10));
DROP TABLE dept;
FLASHBACK TABLE dept TO BEFORE DROP;
Which statement is true regarding the above FLASHBACK operation?- AIt recovers only the first DEPT table.
- BIt recovers only the second DEPT table.
- CIt does not recover any of the tables because FLASHBACK is not possible in this case.
- DIt recovers both the tables but the names would be changed to the ones assigned in the RECYCLEBIN.
32Evaluate the following statements:
CREATE TABLE digits
(id NUMBER(2),
description VARCHAR2(15));
INSERT INTO digits VALUES (1,'ONE');
UPDATE digits SET description ='TWO' WHERE id=1;
INSERT INTO digits VALUES (2,'TWO');
COMMIT;
DELETE FROM digits;
SELECT description FROM digits
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;
What would be the outcome of the above query?- AIt would not display any values.
- BIt would display the value TWO once.
- CIt would display the value TWO twice.
- DIt would display the values ONE, TWO, and TWO.
33View the Exhibit and examine the description of the ORDERS table.
Evaluate the following SQL statement:
SELECT order_id, customer_id
FROM orders
WHERE order_date > 'June 30 2001';
Which statement is true regarding the execution of this SQL statement?
- AIt would not execute because 'June 30 2001' in the WHERE condition is not enclosed within double quotation marks.
- BIt would execute and would return ORDER_ID and CUSTOMER_ID for all records having ORDER_DATE greater than 'June 30 2001'.
- CIt would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_DATE conversion function for proper execution.
- DIt would not execute because 'June 30 2001' in the WHERE condition cannot be converted
implicitly and needs the use of the TO_CHAR conversion function for proper execution.
34Which statements are correct regarding indexes? (Choose all that apply.)
- AWhen a table is dropped, the corresponding indexes are automatically dropped.
- BFor each DML operation performed, the corresponding indexes are automatically updated.
- CIndexes should be created on columns that are frequently referenced as part of an expression.
- DA non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a
unique index.
35View the Exhibit and examine the description of the PRODUCT_INFORMATION table.
Which SQL statement would retrieve from the table the number of products having LIST_PRICE
as NULL?
- ASELECT COUNT(list_price)
FROM product_information
WHERE list_price IS NULL; - BSELECT COUNT(list_price)
FROM product_information
WHERE list_price = NULL; - CSELECT COUNT(NVL(list_price, 0))
FROM product_information
WHERE list_price IS NULL; - DSELECT COUNT(DISTINCT list_price)
FROM product_information
WHERE list_price IS NULL;
- ASELECT COUNT(list_price)
36User OE, the owner of the ORDERS table, issues the following command:
GRANT SELECT ,INSERT
ON orders
TO hr
WITH GRANT OPTION;
The user HR issues the following command:
GRANT SELECT
ON oe.orders
TO scott;
Then, OE issues the following command:
REVOKE ALL
ON orders
FROM hr;
Which statement is correct?- AThe user SCOTT loses the privilege to select rows from OE.ORDERS.
- BThe user SCOTT retains the privilege to select rows from OE.ORDERS.
- CThe REVOKE statement generates an error because OE has to first revoke the SELECT privilege from SCOTT.
- DThe REVOKE statement generates an error because the ALL keyword cannot be used for privileges that have been granted using WITH GRANT OPTION.
37View the Exhibit and examine the structure of the EMP table.
You executed the following command to add a primary key to the EMP table:
ALTER TABLE emp
ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
USING INDEX emp_id_idx;
Which statement is true regarding the effect of the command?
- AThe PRIMARY KEY is created along with a new index.
- BThe PRIMARY KEY is created and it would use an existing unique index.
- CThe PRIMARY KEY would be created in a disabled state because it is using an existing index.
- DThe statement produces an error because the USING clause is permitted only in the CREATE TABLE command.
38SCOTT is a user in the database.
Evaluate the commands issued by the DBA:
1 - CREATE ROLE mgr;
2 - GRANT CREATE TABLE, SELECT
ON oe.orders
TO mgr;
3 - GRANT mgr, create table TO SCOTT;
Which statement is true regarding the execution of the above commands?- AStatement 1 would not execute because the WITH GRANT option is missing.
- BStatement 1 would not execute because the IDENTIFIED BY <password> clause is missing.
- CStatement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.
- DStatement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.
39View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite
PRIMARY KEY is (ORD_NO, ITEM_NO).
Which two CREATE INDEX statements are valid? (Choose two.)
- ACREATE INDEX ord_idx
ON ord(ord_no); - BCREATE INDEX ord_idx
ON ord_items(ord_no); - CCREATE INDEX ord_idx
ON ord_items(item_no); - DCREATE INDEX ord_idx
ON ord,ord_items(ord_no, ord_date,qty);
- ACREATE INDEX ord_idx
40View 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 needs to be updated to reflect the latest information about
the customers.
What is the error in the following MERGE statement?
MERGE INTO customers c
USING customer_vu cv
ON (c.customer_id = cv.customer_id)
WHEN MATCHED THEN
UPDATE SET
c.customer_id = cv.customer_id,
c.cust_name = cv.cust_name,
c.cust_email = cv.cust_email,
c.income_level = cv.income_level
WHEN NOT MATCHED THEN
INSERT VALUES(cv.customer_id,cv.cust_name,cv.cust_email,cv,income_level)
WHERE cv.income_level >100000;
- 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.
41Which two statements are true regarding operators used with subqueries? (Choose two.)
- AThe NOT IN operator is equivalent to IS NULL.
- BThe <ANY operator means less than the maximum.
- C=ANY and =ALL operators have the same functionality.
- DThe IN operator cannot be used in single-row subqueries.
- EThe NOT operator can be used with IN, ANY and ALL operators.
42Given below are the SQL statements executed in a user session:
CREATE TABLE product
(pcode NUMBER(2),
pname VARCHAR2(10));
INSERT INTO product VALUES(1, 'pen');
INSERT INTO product VALUES (2,'pencil');
SAVEPOINT a;
UPDATE product SET pcode = 10 WHERE pcode = 1;
SAVEPOINT b;
DELETE FROM product WHERE pcode = 2;
COMMIT;
DELETE FROM product WHERE pcode=10;
ROLLBACK TO SAVEPOINT a;
Which statement describes the consequences?- ANo SQL statement would be rolled back.
- BBoth the DELETE statements would be rolled back.
- COnly the second DELETE statement would be rolled back.
- DBoth the DELETE statements and the UPDATE statement would be rolled back.
43Evaluate the following CREATE TABLE command:
CREATE TABLE order_item
(order_id NUMBER(3),
item_id NUMBER(2),
qty NUMBER(4),
CONSTRAINT ord_itm_id_pk
PRIMARY KEY (order_id,item_id)
USING INDEX
(CREATE INDEX ord_itm_idx
ON order_item(order_id,item_id)));
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 key.
- 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.
44View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS tables.
You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get
the maximum salary in their respective departments. The following SQL statement was written:
WITH SELECT employee_id, last_name, salary
FROM employees
WHERE (department_id, salary) = ANY (SELECT *
FROM dept_max)
dept_max as ( SELECT d.department_id, max(salary)
FROM departments d JOIN employees j
ON (d.department_id = j.department_id)
GROUP BY d.department_id);
Which statement is true regarding the execution and the output of this statement?
- AThe statement would execute and give the desired results.
- BThe statement would not execute because the = ANY comparison operator is used instead of =.
- CThe statement would not execute because the main query block uses the query name before it is even created.
- DThe statement would not execute because the comma is missing between the main query block and the query name.
45View the Exhibit and examine the data in the DEPARTMENTS tables.
SELECT department_id "DEPT_ID", department_name , 'b'
FROM departments
WHERE department_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 the output of the above statement? (Choose
two.)
- AORDER BY 3;
- BORDER BY 'b';
- CORDER BY DEPT_ID;
- DORDER BY DEPT_NAME;
46View the Exhibit and examine the description of the EMPLOYEES table.
You want to know the EMPLOYEE_ID and FIRST_NAME of all the records in the EMPLOYEES table wherein the JOB_ID column has ST_CLERK or ST_MAN values, the DEPARTMENT_ID column has value 30, and the SALARY column has a value greater than 3,000.
Which SQL statement would get you the desired result?
- ASELECT employee_id, first_name
FROM employees
WHERE job_id like 'MAN%' OR job_id like 'CLERK%'
AND department_id = 30 AND salary > 3000; - BSELECT employee_id, first_name
FROM employees
WHERE job_id like '%MAN' OR job_id like '%CLERK'
AND (department_id = 30 OR salary > 3000); - CSELECT employee_id, first_name
FROM employees
WHERE (job_id like '%MAN' AND job_id like '%CLERK')
AND department_id = 30 OR salary > 3000; - DSELECT employee_id, first_name
FROM employees
WHERE (job_id like '%MAN' OR job_id like '%CLERK' )
AND department_id = 30 AND salary > 3000;
- ASELECT employee_id, first_name
Information
- Description
- 46 Questoẽs aberta e sem tempo de execução
- Author
- Jorge Junior
- Language
- Português
- Views
- 4
- Tags
- Rating
- No ratings yet Rate this quiz