Latest Oracle 1Z0-051 Actual Free Exam Questions & Community Discussion, Page 3

Question #21

You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

and examine the structure of CUSTOMRS AND SALES tables: Evaluate the following SQL statement:
Exhibit:

Which statement is true regarding the execution of the above UPDATE statement?

A. It would not execute because two tables cannot be used in a single UPDATE statement

B. It would execute and restrict modifications to only the column specified in the SELECT statement

C. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement

D. It would not execute because the SELECT statement cannot be used in place of the table name

Discussion 0

Correct Answer: B Vote an answer

Question #22

You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement:
1) The order ID will be unique and cannot have null values.
2) The order date cannot have null values and the default should be the current date.
3) The order amount should not be less than 50.
4) The order status will have values either shipped or not shipped.
5) The order payment mode should be cheque, credit card, or cash on delivery (COD).
Which is the valid DDL statement for creating the ORD_DETAIL table?

A. CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));

B. CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));

C. CREATE TABLE ord_details
(ord_id NUMBER(2),
ord_date DATE NOT NULL DEFAULT SYSDATE,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));

D. CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));

Discussion 0

Correct Answer: A Vote an answer

Question #23

See the Exhibit and examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you need to generate a report that shown the average credit limit for customers in WASHINGTON and NEW YORK.
Which SQL statement would produce the required result?

A. SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_credit_limit, cust_city;

B. SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city;

C. SELECT cust_city, AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK');

D. SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city,cust_credit_limit;

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for EduDump members. You can sign-up / login (it's free).

Question #24

Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)

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

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

C. Only One LONG column can be used per table

D. The minimum column width that can be specified for a VARCHAR2 data type column is one

E. A TIMESTAMP data type column stores only time values with fractional seconds

Discussion 0

Correct Answer: B,C,D Vote an answer

Explanation: Only visible for EduDump members. You can sign-up / login (it's free).

Question #25

Examine the structure of the TRANSACTIONS table:
Name Null Type
TRANS_ID NOT NULL NUMBER(3)
CUST_NAME VARCHAR2(30)
TRANS_DATE TIMESTAMP
TRANS_AMT NUMBER(10,2)
You want to display the date, time, and transaction amount of transactions that where done before 12 noon.
The value zero should be displayed for transactions where the transaction amount has not been entered.
Which query gives the required result?

A. SELECT TO_DATE (trans_date,'dd-mon-yyyy hh24:mi:ss'),
NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'), 0)
FROM transactions
WHERE TO_DATE(trans_date,'hh24') < 12;

B. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),
COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0)
FROM transactions
WHERE TO_DATE(trans_date,'hh24') < 12;

C. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),
NVL(TO_CHAR(trans_amt,'$99999999D99'),0)
FROM transactions
WHERE TO_CHAR(trans_date,'hh24') < 12;

D. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),
TO_CHAR(trans_amt,'$99999999D99')
FROM transactions
WHERE TO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 AND
COALESCE(trans_amt,NULL)<>NULL;

Discussion 0

Correct Answer: C Vote an answer

Question #26

Examine the structure of the EMP_DEPT_VU view:

Which SQL statement produces an error?

A. None of the statements produce an error; all are valid.

B. SELECT *
FROM emp_dept_vu;

C. SELECT department_id, job_id, AVG(salary)
FROM emp_dept_vu
GROUP BY department_id, job_id;

D. SELECT job_id, SUM(salary)
FROM emp_dept_vu
WHERE department_id IN (10,20)
GROUP BY job_id
HAVING SUM(salary) > 20000;

E. SELECT department_id, SUM(salary)
FROM emp_dept_vu
GROUP BY department_id;

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for EduDump members. You can sign-up / login (it's free).

Question #27

EMPLOYEES and DEPARTMENTS data: EMPLOYEES

DEPARTMENTS

On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID.
On the DEPARTMENTS table DEPARTMENT_ID is the primary key.
Evaluate this UPDATE statement.
UPDATE employees SET mgr_id = (SELECT mgr_id FROMemployees WHERE dept_id= (SELECT department_id FROM departments WHERE department_name = 'Administration')), Salary = (SELECT salary
FROM employees
WHERE emp_name = 'Smith')
WHERE job_id = 'IT_ADMIN';
What happens when the statement is executed?

A. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table.

B. The statement fails because there is no 'Administration' department in the DEPARTMENTS table.

C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105.

D. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105.

E. The statement fails because there is more than one row matching the employee name Smith.

F. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105.

Discussion 0

Correct Answer: E Vote an answer

Explanation: Only visible for EduDump members. You can sign-up / login (it's free).

Question #28

You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.
Which statement accomplishes this task?

A. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

B. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

C. ALTER TABLE students ADD PRIMARY KEY student_id;

D. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

E. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for EduDump members. You can sign-up / login (it's free).

Question #29

View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.

Evaluate the following SQL statements:

Which statement is true regarding the output of the above query?

A. It gives details of all promos irrespective of whether they have resulted in a sale or not

B. It gives details of product IDs that have been sold irrespective of whether they had a promo or not

C. It gives the details of promos for which there have been sales

D. It gives the details of promos for which there have been no sales

Discussion 0

Correct Answer: A Vote an answer

Question #30

View the Exhibit and examine the structure of the SALES table.
The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.

Which statement is true regarding this SQL statement?

A. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).

B. It executes successfully and generates the required result.

C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause.

D. It produces an error because COUNT(*) should be specified in the SELECT clause also.

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for EduDump members. You can sign-up / login (it's free).

Oracle 1Z0-051 Actual Free Exam Questions & Community Discussion

Download Free Oracle 1Z0-051 Demo