Latest Oracle 1Z1-047 Actual Free Exam Questions & Community Discussion, Page 1

Question #1

Which two statements are true regarding multiple-row subqueries? (Choose two.)

A. They can contain group functions.

B. They always contain a subquery within a subquery.

C. They 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.

D. They can be used to retrieve multiple rows from a single table only.

E. They use the < ALL operator to imply less than the maximum.

Discussion 0

Correct Answer: A,C Vote an answer

Question #2

View the Exhibit and examine the structure of the ORDERS table. Which task would require subqueries?

A. displaying the number of orders that have order mode online and order date in 1999

B. displaying the total order value for sales representatives 161 and 163

C. displaying the number of orders whose order total is more than the average order total for all online orders

D. displaying the order total for sales representative 161 in the year 1999

Discussion 0

Correct Answer: C Vote an answer

Question #3

View 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?

A. The statement would not execute because the comma is missing between the main query block and the query name.

B. The statement would not execute because the main query block uses the query name before it is even created.

C. The statement would not execute because the = ANY comparison operator is used instead of=.

D. The statement would execute and give the desired results.

Discussion 0

Correct Answer: B Vote an answer

Question #4

View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
There are some products listed in the PRODUCT_INFORMATION table that have no value in the
LIST_PRICE column. You issued the following SQL statement to find out the PRODUCT_NAME
for these products:
SELECT product_name, list_price
FROM product_information WHERE list_price = NULL;
The query returns no rows. What changes would you make in the statement to get the desired result?

A. In the WHERE clause, enclose NULL within double quotation marks.

B. In the WHERE clause, enclose NULL within single quotation marks.

C. Change the WHERE clause to WHERE list_price = ''.

D. Change the WHERE clause to WHERE list_price = 0

E. Change the WHERE clause to WHERE list_price IS NULL.

Discussion 0

Correct Answer: E Vote an answer

Question #5

View the Exhibit and examine the description of the ORDERS table.
Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

A. WHERE TO_CHAR(order_date,'MON DD YYYY} = 'JAN 20 2003'

B. WHERE order_date > T0_CHAR(ADD_M0NTHS(SYSDATE,6),'M0N DD YYYY ')

C. WHERE order_date IN (T0_DATE('0ct 21 2003','Mon DD YYYY'), T0_CHAR('N0V 21 2003','Mon DD YYYY'))

D. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY")

Discussion 0

Correct Answer: A,D Vote an answer

Question #6

Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seql 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 seql.nextval FROM dual;
What is displayed by the SELECT statement?

A. 100

B. 10

C. an error

D. 1

Discussion 0

Correct Answer: D Vote an answer

Question #7

Evaluate the following SQL statement:
ALTER TABLE hr.emp SET UNUSED (mgr_id);
Which statement is true regarding the effect of the above SQL statement?

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

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

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

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

Discussion 0

Correct Answer: B Vote an answer

Question #8

Which CREATE TABLE statement is valid?

A. CREATE TABLE ord_details
(ord_no NUMBER(2) ,
item_no NUMBER(3),
ord_date date DEFAULT NOT NULL,
CONSTRAINT ord_uq UNIQUE (ord_no),
CONSTRAINT ord_pk PRIMARY KEY (ord_no));

B. CREATE TABLE ord_details
(ord_no NUMBER(2) UNIQUE, NOT NULL,
item_no NUMBER(3),
ord_date date DEFAULT SYSDATE NOT NULL);

C. CREATE TABLE ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date date DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

D. CREATE TABLE ord_details
(ord_no NUMBER(2) PRIMARY KEY,
item_no NUMBER(3) PRIMARY KEY,
ord_date date NOT NULL);

Discussion 0

Correct Answer: C Vote an answer

Question #9

View the Exhibit and examine the table structure of DEPARTMENTS and LOCATIONS tables.
You want to display all the cities that have no departments and the departments that have not been allocated cities.
Which type of join between DEPARTMENTS and LOCATIONS tables would produce this information as part of its output?

A. NATURAL JOIN

B. FULL OUTER JOIN

C. RIGHT OUTER JOIN

D. LEFT OUTER JOIN

Discussion 0

Correct Answer: B Vote an answer

Question #10

Which three statements are true? (Choose three.)

A. The BLOB data type column is used to store binary data in an operating system file.

B. The minimum column width that can be specified for a varchar2 data type column is one.

C. The value for a CHAR data type column is blank-padded to the maximum defined column width.

D. ATIMESTAMP data type column stores only time values with fractional seconds.

E. Only one LONG column can be used per table.

Discussion 0

Correct Answer: B,C,E Vote an answer

Question #11

View 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?

A. The PRIMARY KEY would be created in a disabled state because it is using an existing index.

B. The statement produces an error because the USING clause is permitted only in the CREATE TABLE command.

C. The PRIMARY KEY is created along with a new index.

D. The PRIMARY KEY is created and it would use an existing unique index.

Discussion 0

Correct Answer: D Vote an answer

Oracle 1Z1-047 Actual Free Exam Questions & Community Discussion

Download Free Oracle 1Z1-047 Demo