Approved 1Z0-051 Study Guides 2021

NEW QUESTION 1
Evaluate the SQL statement:
TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)

• A. It releases the storage space used by the tabl
• B. It does not release the storage space used by the tabl
• C. You can roll back the deletion of rows after the statement execute
• D. You can NOT roll back the deletion of rows after the statement execute
• E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an erro
• F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table

Answer: ADF

Explanation:
A: The TRUNCATE TABLE Statement releases storage space used by the table,
D: Can not rollback the deletion of rows after the statement executes,
F: You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the DEPT table.
Incorrect Answer: Cis not true Dis not true Eis not true
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-18

NEW QUESTION 2
Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table:
PROMO_BEGIN _DATE
04-jan-00
10-jan-00
15-dec-99
18-oct-98
22-aug-99
You want to display the number of promotions started in 1999 and 2000.
Which query gives the correct output?

• A. SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000", SUM(DECODE(SUBSTR (promo_begin_date,8),'99',1,0)) "1999" FROM promotions;
• B. SELECT SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 END) "1999",SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 0 END) "2000" FROM promotions;
• C. SELECT COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 END) "1999", COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 0 END) "2000" FROM promotions;
• D. SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1, 0)) "1999", COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8),'2000', 1, 0)) "2000" FROM promotions;

Answer: A

NEW QUESTION 3
Examine the structure of the PROMOS table:

You want to generate a report showing promo names and their duration (number of days).
If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed. Which queries give the correct output? (Choose all that apply.)

• A. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date,'ONGOING')) FROM promos;
• B. SELECT promo_name,COALESCE(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos;
• C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos;
• D. SELECT promo_name, DECODE(promo_end_date -promo_start_date,NULL,'ONGOING',promo_end_date - promo_start_date) FROM promos;
• E. SELECT promo_name,ecode(coalesce(promo_end_date,promo_start_date),null,'ONGOING', promo_end_date - promo_start_date) FROM promos;

Answer: BCD

NEW QUESTION 4
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 and generates the required resul
• B. It produces an error because COUNT(*) should be specified in the SELECT clause als
• C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE claus
• D. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).

Answer: C

Explanation:
Restricting Group Results with the HAVING Clause
You use the HAVING clause to specify the groups that are to be displayed, thus further
restricting the groups on the basis of aggregate information.
In the syntax, group_condition restricts the groups of rows returned to those groups for
which the specified condition is true.
The Oracle server performs the following steps when you use the HAVING clause:
1.
Rows are grouped.
2.
The group function is applied to the group.
3.
The groups that match the criteria in the HAVING clause are displayed.
The HAVING clause can precede the GROUP BY clause, but it is recommended that you
place the GROUP BY clause first because it is more logical. Groups are formed and group
functions are calculated before the HAVING clause is applied to the groups in the SELECT
list.
Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.

NEW QUESTION 5
You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be used in the
SELECT statement to get the required output?

• A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
• B. WHERE prod_id LIKE '%_D123%' ESCAPE ''
• C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
• D. WHERE prod_id LIKE '%_D123%' ESCAPE '_'

Answer: B

Explanation:
A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character. The second example in Figure 3-12 shows the SQL statement that retrieves the JOBS table records with JOB_ID values equal to SA_MAN and SA_REP and which conforms to the original requirement: select job_id from jobs where job_id like 'SA_%' escape '';

NEW QUESTION 6
Which two statements are true about constraints? (Choose two.)

• A. The UNIQUE constraint does not permit a null value for the colum
• B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraint
• C. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE inde
• D. The NOT NULL constraint ensures that null values are not permitted for the colum

Answer: BD

Explanation:
B: A unique constraint can contain null values because null values cannot be compared to anything.
D: The NOT NULL constraint ensure that null value are not permitted for the column
Incorrect Answer: Astatement is not true Cstatement is not true
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-9

NEW QUESTION 7
Which two statements are true regarding indexes? (Choose two.)

• A. They can be created on tables and cluster
• B. They can be created on tables and simple view
• C. You can create only one index by using the same column
• D. You can create more than one index by using the same columns if you specify distinctly different combinations of the column

Answer: AD

NEW QUESTION 8
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 9
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which MERGE statement is valid?

• A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
• B. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
• C. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
• D. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name);
• E. MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
• F. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
• G. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
• H. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||', '||e.last_name);

Answer: A

Explanation: Explanation: this is the correct MERGE statement syntax
Incorrect Answer: Bit should MERGE INTO table_name Cit should be WHEN MATCHED THEN Dit should MERGE INTO table_name Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29

NEW QUESTION 10
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,cust_credit_limit;
• C. SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN ('WASHINGTON','NEW YORK') GROUP BY cust_city;
• D. SELECT cust_city, AVG(NVL(cust_credit_limit,0)) FROM customers WHERE cust_city IN ('WASHINGTON','NEW YORK');

Answer: C

Explanation:
Creating Groups of Data: GROUP BY Clause Syntax You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group. In the syntax: group_by_expression Specifies the columns whose values determine the basis for grouping rows Guidelines
.
If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause.
.
Using a WHERE clause, you can exclude rows before dividing them into groups.
.
You must include the columns in the GROUP BY clause.
.
You cannot use a column alias in the GROUP BY clause.

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

you issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name 'king' and whose credit limit is less than the maximum credit limit in countries that have customers with the last name 'king'.

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

• A. It produces an error and the < operator should be replaced by < ANY to get the required output
• B. It produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to get the required output
• C. It executes and shows the required result
• D. It produces an error and the < operator should be replaced by < ALL to get the required output

Answer: C

NEW QUESTION 12
You need to calculate the number of days from 1st Jan 2007 till date:
Dates are stored in the default format of dd-mm-rr.
Which two SQL statements would give the required output? (Choose two.)

• A. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
• B. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;
• C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL
• D. SELECT SYSDATE - '01-JAN-2007' FROM DUAL
• E. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;

Answer: AC

NEW QUESTION 13
Examine the data in the ORD_ITEMS table:
ORD_NO ITEM_NO QTY
1 111 10
1 222 20
1 333 30
2 333 30
2 444 40
3 111 40
Evaluate the following query:
SQL>SELECT item_no, AVG(qty)
FROM ord_items
HAVING AVG(qty) > MIN(qty) * 2
GROUP BY item_no;
Which statement is true regarding the outcome of the above query?

• A. It gives an error because the HAVING clause should be specified after the GROUP BY claus
• B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT lis
• C. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the tabl
• D. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the tabl

Answer: C

NEW QUESTION 14
Which SQL statement returns a numeric value?

• A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
• B. SELECT ROUND(hire_date) FROM EMP;
• C. SELECT sysdate-hire_date FROM EMP;
• D. SELECT TO_NUMBER(hire_date + 7) FROM EMP;

Answer: C

Explanation:
DATE value subtract DATE value will return numeric value.
Incorrect Answer: Adoes not return numeric value Bdoes not return numeric value Ddoes not return numeric value
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-13

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

You issue the following SQL statement:

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

• A. It produces an error because the AMT_SPENT column contains a null valu
• B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMI
• C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is nul
• D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 functio

Answer: C

Explanation:
The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned.

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

Examine the structure of PRODUCTS table.
Using the PRODUCTS table, you issue the following query to generate the names, current list price and discounted list price for all those products whose list price fails below $10 after a discount of 25% is applied on it.
Exhibit:

The query generates an error.
What is the reason of generating error?

• A. The column alias should be put in uppercase and enclosed within double quotation marks in the WHERE clause
• B. The parenthesis should be added to enclose the entire expression
• C. The column alias should be replaced with the expression in the WHERE clause
• D. The double quotation marks should be removed from the column alias

Answer: C

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

NEW QUESTION 17
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name "Last Name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30;
Which ORDER BY clause are valid for the above query? (Choose all that apply.)

• A. ORDER BY 2,1
• B. ORDER BY CUST_NO
• C. ORDER BY 2,cust_id
• D. ORDER BY "CUST_NO"
• E. ORDER BY "Last Name"

Answer: ACE

Explanation:
Using the ORDER BY Clause in Set Operations
-The ORDER BY clause can appear only once at the end of the compound query.
-Component queries cannot have individual ORDER BY clauses.
-The ORDER BY clause recognizes only the columns of the first SELECT query.
-By default, the first column of the first SELECT query is used to sort the output in an ascending order.

NEW QUESTION 18
Here is the structure and data of the CUST_TRANS table: Exhibit:

Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.
Which three SQL statements would execute successfully? (Choose three.)

• A. SELECT transdate + '10' FROM cust_trans;
• B. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
• C. SELECT transamt FROM cust_trans WHERE custno > '11';
• D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
• E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;

Answer: ACD