Oracle 1Z0-051 Braindumps 2021

NEW QUESTION 1
View the Exhibit and examine the structure of the CUSTOMERS table.

NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the CUSTOMERS table.
Evaluate the following INSERT statement:

The INSERT statement fails when executed. What could be the reason?

• A. The VALUES clause cannot be used in an INSERT with a subquery
• B. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table
• C. The WHERE clause cannot be used in a sub query embedded in an INSERT statement
• D. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match

Answer: A

Explanation:
Copying Rows from Another Table
Write your INSERT statement with a subquery:
Do not use the VALUES clause.
Match the number of columns in the INSERT clause to those in the subquery.
Inserts all the rows returned by the subquery in the table, sales_reps.

NEW QUESTION 2
Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

• A. addition
• B. subtraction
• C. raising to a power
• D. finding the quotient
• E. finding the lowest value

Answer: ACE

NEW QUESTION 3
View the Exhibit and examine the data in the PROJ_TASK_DETAILS table.

The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them.
The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks.
You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on.
Which query would give the required result?

• A. SELECT 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);
• B. SELECT 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);
• C. SELECT 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);
• D. SELECT 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);

Answer: B

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

Evaluate the following query: Exhibit:

The above query produces an error on execution. What is the reason for the error?

• A. An alias cannot be used in an expression
• B. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression
• C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values
• D. The alias NAME should not be enclosed within double quotation marks

Answer: A

NEW QUESTION 5
Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

• A. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
• B. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
• C. SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL;
• D. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
• E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;

Answer: ADE

NEW QUESTION 6
See the Exhibit and Examine the structure of SALES and PROMOTIONS tables: Exhibit:

You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values.
Which DELETE statements are valid? (Choose all that apply.)

• A. DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale') AND promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'everyday low price');
• B. DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale') OR promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'everyday low price');
• C. DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale' OR promo_name = 'everyday low price');
• D. D DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name IN ('blowout sale','everyday low price'));

Answer: BCD

NEW QUESTION 7
View the Exhibit for the structure of the STUDENT and FACULTY tables.

You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements:

Which statement is true regarding the outcome?

• A. Only statement 1 executes successfully and gives the required resul
• B. Only statement 2 executes successfully and gives the required resul
• C. Both statements 1 and 2 execute successfully and give different result
• D. Both statements 1 and 2 execute successfully and give the same required resul

Answer: D

NEW QUESTION 8
Top N analysis requires _____ and _____. (Choose two.)

• A. the use of rowid
• B. a GROUP BY clause
• C. an ORDER BY clause
• D. only an inline view
• E. an inline view and an outer query

Answer: CE

Explanation:
The correct statement for Top-N Analysis SELECT [coloumn_list], ROWNUM FROM (SELECT [coloumn_list]
FROM table
ORDER BY Top-N_coloumn)
WHERE ROWNUM <= N;
Incorrect Answer:
AROWID is not require
BGROUP BY clause is not require
DMust have inline view and outer query.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-23

NEW QUESTION 9
You need to display the date ll-oct-2007 in words as "Eleventh of October, Two Thousand Seven'. Which SQL statement would give the required result?

• A. SELECT TO_CHAR('ll-oct-2007'. 'miDdspth "of Mont
• B. Year') FROM DUAL:
• C. SELECT TO_CHAR(TO_DATE('ll-oct-2007'X 'miDdspth of month, year') FROM DUAL;
• D. SELECT TO_CHAR(TO_DATE('ll-oct-2007'), 'miDdthsp "of* Mont
• E. Year') FROM DUAL;
• F. SELECT TO_DATE(TO_CHAR('ll-oct-20077fiiiDdspth "of" Mont
• G. Year')) FROM DUAL:

Answer: A

NEW QUESTION 10
Which two statements are true regarding constraints? (Choose two.)

• A. A constraint can be disabled even if the constraint column contains data
• B. A constraint is enforced only for the INSERT operation on a table
• C. A foreign key cannot contain NULL values
• D. All constraints can be defined at the column level as well as the table level
• E. A columns with the UNIQUE constraint can contain NULL values

Answer: AE

NEW QUESTION 11
The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL
CUSTOMER_NAME VARCHAR2(100) NOT NULL
CUSTOMER_ADDRESS VARCHAR2(150)
CUSTOMER_PHONE VARCHAR2(20)
You need to produce output that states "Dear Customer customer_name, ".
The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table.
Which statement produces this output?

• A. SELECT dear customer, customer_name, FROM customers;
• B. SELECT "Dear Customer", customer_name || ',' FROM customers;
• C. SELECT 'Dear Customer ' || customer_name ',' FROM customers;
• D. SELECT 'Dear Customer ' || customer_name || ',' FROM customers;
• E. SELECT "Dear Customer " || customer_name || "," FROM customers;
• F. SELECT 'Dear Customer ' || customer_name || ',' || FROM customers;

Answer: D

Explanation: Concatenation operator to create a resultant column that is a character expression.
Incorrect Answer: Ano such dear customer column Binvalid syntax Cinvalid syntax Einvalid syntax Finvalid syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-18

NEW QUESTION 12
Evaluate 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

Answer: A

Explanation:
Datetime Data Types You can use several datetime data types: INTERVAL YEAR TO MONTH Stored as an interval of years and months INTERVAL DAY TO SECOND Stored as an interval of days, hours, minutes, and seconds

NEW QUESTION 13
View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.
Evaluate the following two queries:
SQL>SELECT DISTINCT promo_category to_char(promo_cost)"code"
FROM promotions
ORDER BY code;
SQL>SELECT DISTINCT promo_category promo_cost "code"
FROM promotions
ORDER BY 1;
Which statement is true regarding the execution of the above queries?

• A. Only the first query executes successfull
• B. Only the second query executes successfull
• C. Both queries execute successfully but give different result
• D. Both queries execute successfully and give the same resul

Answer: B

Explanation:
Note: You cannot use column alias in the WHERE clause.

NEW QUESTION 14
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_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')));
• 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) 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')));
• D. 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')));

Answer: C

NEW QUESTION 15
In 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?

• A. Abigail PA
• B. Abigail Pa
• C. Abigail IS
• D. an error message

Answer: B

NEW QUESTION 16
View the Exhibit and examine the data in the EMPLOYEES table:
You want to display all the employee names and their corresponding manager names.
Evaluate the following query:
SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME"
FROM employees e ______________ employees m
ON e.manager_id = m.employee_id;
Which JOIN option can be used in the blank in the above query to get the required output?
Exhibit:

• A. only inner JOIN
• B. only FULL OUTER JOIN
• C. only LEFT OUTER JOIN
• D. only RIGHT OUTER JOIN

Answer: C