My Quiz 1

External table

The MERGE command

The multitable INSERT command

. WITH CHECK INSERT using OPTION

It would be created only if a unique index is manually created first

It would be created and would use an automatically created unique index

It would be created and would use an automatically created unique index

It would be created and remains in a disabled state because no index is specified in the command

ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'^A-Z'))NOVALIDATE

. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'^[0-9]'))NOVALIDATE ;

ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:alpha:]]'))NOVALIDATE

ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:digit:]]'))NOVALIDATE

SCOTT would be able to query the OE.ORDERS table

SCOTT would not be able to query the OE.ORDERS table

The REVOKE statement would remove the SELECT privilege from SCOTT as well as from the role R1

The REVOKE statement would give an error because the SELECT privilege has been granted to the role R1

A. Any synonym existing on the EMP table would have to be re-created

Any constraints defined on the MGR_ID column would be removed by the above command

Any views created on the EMP table that include the MGR_ID column would have to be dropped and re-created

Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed

UPDATE empdet SET ename = 'Amit' WHERE empno = 1234

DELETE FROM empdet WHERE ename LIKE 'J%';

CREATE VIEW empvu AS SELECT * FROM empdept;

CREATE INDEX empdet_idx ON empdet(empno);

To find the groups forming the subtotal in a row

To create group-wise grand totals for the groups specified within a GROUP BY clause

To create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals

To create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals

CREATE TABLE EMP_1 (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, e_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB(200));

CREATE TABLE 1_EMP (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB);

CREATE TABLE 1_EMP (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT "ACTIVE", resume CLOB);

CREATE TABLE EMP_1 (emp_id NUMBER, emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB);

A. Pivoting INSERT

B. Unconditional INSERT

C. Conditional ALL INSERT

Conditional FIRST INSERT Answer: C Question: 86 View the Exhibit and examine the description of the EMPLOYEES table. Evaluate the following SQL statement: SELECT first_name, employee_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM employees; The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. The review date is the first Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session. Which statement is true regarding this query? A. The query would execute to give the desired output. B. The query would not execute because date functions cannot be nested. C. The query would execute but the output would give review dates that are Sundays. D. The query would not execute because the NEXT_DAY function accepts a string as argument. Answer: C Question: 87 View the Exhibit and examine the structure of the EMPLOYEES table. Page 47 of 91 D. Conditional FIRST INSERT

When the New York user selects the row, booking_date is displayed as '007-05-10 3.00.00.000000'

When the New York user selects the row, booking_date is displayed as '2007-05-10 6.00.00.000000 -5:00'

When the San Francisco user selects the row, booking_date is displayed as '007-05-10 3.00.00.000000'

When the San Francisco user selects the row, booking_date is displayed as '007-05-10 3.00.00.000000 -8:00'

CREATE OR REPLACE VIEW ord_vu (order_id,order_date) AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date

CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date

CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date;

CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)||' NO OF ITEMS' FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date WITH CHECK OPTION;

A. 1-a, 2-c, 3-b, 4-d

1-d, 2-c, 3-b, 4-a

1-b, 2-c, 3-d, 4-a

1-d, 2-b, 3-c, 4-a

SELECT sales_rep_id, COUNT(customer_id) "Total" FROM orders HAVING COUNT(customer_id) > 10;

SELECT sales_rep_id, COUNT(customer_id) "Total" FROM orders WHERE COUNT(customer_id) > 10 GROUP BY sales_rep_id;

SELECT sales_rep_id, COUNT(customer_id) "Total" FROM orders GROUP BY sales_rep_id HAVING total > 10;

SELECT sales_rep_id, COUNT(customer_id) "Total" FROM orders GROUP BY sales_rep_id HAVING COUNT(customer_id) > 10;

The statement would execute and produce the desired output.

The statement would not execute because the WHERE clause is used before the GROUP BY clause.

The statement would not execute because prefixing table alias to column names is not allowed with the ON clause

The statement would not execute because the WHERE clause is not allowed with LEFT OUTER JOIN

The statement would execute and produce the desired output

The statement would not execute because the WHERE clause is used before the GROUP BY clause

The statement would not execute because prefixing table alias to column names is not allowed with the ON clause

The statement would not execute because the WHERE clause is not allowed with LEFT OUTER JOIN

SELECT first_name, salary, salary*12+salary*commission_pct "Total" FROM EMPLOYEES

SELECT first_name, salary, salary*12+NVL((salary*commission_pct), 0) "Total" FROM EMPLOYEES

SELECT first_name, salary, salary*12 + NVL(salary, 0)*commission_pct "Total" FROM EMPLOYEES

SELECT first_name, salary, salary*12+(salary*NVL2(commission_pct, salary,salary+commission_pct))"Total" FROM EMPLOYEES

Change the WHERE clause to WHERE list_price = 0

Change the WHERE clause to WHERE list_price = ' '

Change the WHERE clause to WHERE list_price IS NULL

In the WHERE clause, enclose NULL within single quotation marks

The inner query returns a single value to the main query

The inner query uses an aggregate function and returns one or more values

There is only one inner query in the main query and the inner query returns one or more values

The inner query returns one or more values and the main query returns a single value as output

SYSDATE cannot be used with the CHECK constraint

CHECK constraint cannot be used twice for the same table

The BETWEEN clause cannot be used for the CHECK constraint

. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY

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

CREATE VIEW V1 AS SELECT order_id, product_id FROM order_items;

CREATE VIEW V4(or_no, or_date, cust_id) AS SELECT order_id, order_date, customer_id FROM orders WHERE order_date < '30-mar-2007' WITH CHECK OPTION;

CREATE VIEW V3 AS SELECT o.order_id, o.customer_id, i.product_id FROM orders o, order_items i WHERE o.order_id=i.order_id;

CREATE VIEW V2 AS SELECT order_id, line_item_id, unit_price*quantity total FROM order_items

SELECT view_name, text FROM user_view

SELECT view_name, text FROM user_object

SELECT view_name, text FROM user_objects

SELECT view_name, text FROM user_views

Only the synonym would be dropped

The synonym would be dropped and the corresponding table would become invalid

The synonym would be dropped and the packages referring to the synonym would be dropped

The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid

All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101

The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified

The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified

All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20

ALTER TABLE products ADD price NUMBER(8,2) NOT NULL

ALTER TABLE products ADD price NUMBER(8,2) DEFAULT NOT NULL

ALTER TABLE products ADD price NUMBER(8,2) DEFAULT 0 NOT NULL

ALTER TABLE products ADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL

It would execute successfully and update the relevant data

. It would not execute successfully because there is no LOCATION_ID 2100 in the DEPARTMENTS table

It would not execute successfully because the condition specified with the concatenation operator is not valid

It would not execute successfully because multiple columns (SALARY,COMMISSION_PCT)cannot be used in an UPDATE statement

Enclose the character literal string in the SELECT clause within the double quotation marks

Do not enclose the character literal string in the SELECT clause within the single quotation marks

Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string

Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause

INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);

INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600),order_total) VALUES(1,'10-mar-2007', 'direct', &&customer_id, 1000

INSERT INTO orders (order_id,order_date,order_mode, Page 74 of 91 Exam Name: Oracle Database SQL Expert Exam Type Oracle Exam Code: 1Z0-047 Total Questions: 168 (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600),order_total)

INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' ANDc.credit_limit=600 ) VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000)

SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders GROUP BY sales_rep_id

SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders GROUP BY sales_rep_id, total_orders

SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders

SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders WHERE sales_rep_id IS NOT NULL

Replace = with the IN operator

Replace = with the >ANY operator

Replace = with the >ALL operator

Remove the GROUP BY clause from the subquery and place it in the main query

NATURAL JOIN

FULL OUTER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause

They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses

They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses

The INSERT statement would give an error because the ELSE clause is not present for support in case none of the WHEN clauses are true

SELECT order_id, product_id, unit_price*quantity "TOTAL" FROM order_items oi JOIN orders o ON (o.order_id=oi.order_id) WHERE o.order_date>=SYSDATE-7;

SELECT o.order_id,oi.product_id, oi.unit_price*oi.quantity "TOTAL" FROM order_items oi JOIN orders o USING (order_id WHERE o.order_date>=SYSDATE-7;

SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL" FROM order_items oi JOIN orders o WHERE o.order_date>=SYSDATE-7 ON (o.order_id=oi.order_id);

SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL" FROM order_items oi JOIN orders o ON (o.order_id=oi.order_id) WHERE o.order_date>=SYSDATE-7;

DBA_TABLES

DBA_COLUMNS

USER_COLUMNS

USER_TAB_COLUMNS