Free 1Z0-051 Dumps 2021

NEW QUESTION 1
What are two reasons to create synonyms? (Choose two.)

• A. You have too many table
• B. Your tables names are too lon
• C. Your tables have difficult name
• D. You want to work on your own table
• E. You want to use another schema's table
• F. You have too many columns in your table

Answer: BC

Explanation:
Create a synonyms when the names of the tables are too long or the table names are difficult.

NEW QUESTION 2
View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.)

• A. SELECT promo_i
• B. DECODE(NVL(promo_cost.O).promo_cost * 0.25. 100) "Discount" FROM promotions;
• C. SELECT promo i
• D. DECODE(promo_cos
• E. 10000. DECODE(promo_categor
• F. 'Gl promo_cost * 25. NULL). NULL) "Catcost" FROM promotions;
• G. SELECT promo_i
• H. DECODE(NULLIF(promo_cos
• I. 10000). NUL
• J. promo_cost*.25, *N/A') "Catcost" FROM promotions;
• K. SELECT promo_i
• L. DECODE(promo_cos
• M. >10000. 'High'. <10000. 'Low') "Range"FROM promotions;

Answer: AB

Explanation:
Note: there are some syntax issues in this question.

NEW QUESTION 3
View the Exhibit; e xamine the structure of the PROMOTIONS table.

Each promotion has a duration of at least seven days.
Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to l date.
Which query would achieve the required result?

• A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;
• B. SELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;
• C. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;
• D. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions;

Answer: D

NEW QUESTION 4
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 5
Evaluate the following two queries: Exhibit:

Exhibit:

Which statement is true regarding the above two queries?

• A. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column
• B. Performance would degrade in query 2
• C. There would be no change in performance
• D. Performance would improve in query 2

Answer: C

Explanation:
Note: The IN operator is internally evaluated by the Oracle server as a set of OR conditions, such as a=value1 or a=value2 or a=value3. Therefore, using the IN operator
has no performance benefits and is used only for logical simplicity.

NEW QUESTION 6
In which four clauses can a sub query be used? (Choose four.)

• A. in the INTO clause of an INSERT statement
• B. in the FROM clause of a SELECT statement
• C. in the GROUP BY clause of a SELECT statement
• D. in the WHERE clause of a SELECT statement
• E. in the SET clause of an UPDATE statement
• F. in the VALUES clause of an INSERT statement

Answer: ABDE

Explanation:
A: a sub query is valid on the INTO clause of an ISERT Statement
B: a sub query can be used in the FROM clause of a SELECT statement
D: a sub query can be used in the WHERE clause of a SELECT statement,
E: a sub query can be used in the SET clauses of an UPDATE statement,
Incorrect Answer:
Csub query cannot be used
F: is incorrect.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 6-5

NEW QUESTION 7
The COMMISSION column shows the monthly commission earned by the employee. Exhibit

Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)

• A. listing the employees who earn the same amount of commission as employee 3
• B. finding the total commission earned by the employees in department 10
• C. finding the number of employees who earn a commission that is higher than the average commission of the company
• D. listing the departments whose average commission is more that 600
• E. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
• F. listing the employees whose annual commission is more than 6000

Answer: AC

NEW QUESTION 8
View the Exhibit and examine the structure and data in the INVOICE table.

Which statements are true regarding data type conversion in expressions used in queries? (Choose all that apply.)

• A. inv_amt ='0255982' : requires explicit conversion
• B. inv_date > '01-02-2008' : uses implicit conversion
• C. CONCAT(inv_amt,inv_date) : requires explicit conversion
• D. inv_date = '15-february-2008' : uses implicit conversion
• E. inv_no BETWEEN '101' AND '110' : uses implicit conversion

Answer: DE

Explanation:
In some cases, the Oracle server receives data of one data type where it expects data of a different data type. When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user. Explicit data type conversions are performed by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type. The first data type is the input data type and the second data type is the output. Note: Although implicit data type conversion is available, it is recommended that you do the explicit data type conversion to ensure the reliability of your SQL statements.

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

Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)

• A. listing of customers who do not have a credit limit and were born before 1980
• B. finding the number of customers, in each city, whose marital status is 'married'
• C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
• D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'
• E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

Answer: DE

Explanation:
Describe the Types of Problems That the Subqueries Can Solve There are many situations where you will need the result of one query as the input for another. Use of a Subquery Result Set for Comparison Purposes Which employees have a salary that is less than the average salary? This could be answered by two statements, or by a single statement with a subquery. The following example uses two statements: select avg(salary) from employees; select last_name from employees where salary < result_of_previous_query ;
Alternatively, this example uses one statement with a subquery:
select last_name from employees where salary < (select avg(salary)from employees);
In this example, the subquery is used to substitute a value into the WHERE clause of the
parent query: it is returning a single value, used for comparison with the rows retrieved by
the parent query.
The subquery could return a set of rows. For example, you could use the following to find
all departments that do actually have one or more employees assigned to them:
select department_name from departments where department_id in
(select distinct(department_id) from employees);

NEW QUESTION 10
Which statement describes the ROWID data type?

• A. Binary data up to 4 gigabyte
• B. Character data up to 4 gigabyte
• C. Raw binary data of variable length up to 2 gigabyte
• D. Binary data stored in an external file, up to 4 gigabyte
• E. A hexadecimal string representing the unique address of a row in its tabl

Answer: E

Explanation:
The ROWID datatype stores information related to the disk location of table rows. They
also uniquely identify the rows in your table. The ROWID datatype is stored as a
hexadecimal string.
Incorrect Answers
A:It is not a binary data. The ROWID datatype is a hexadecimal string.
B:It is not a character data. The ROWID datatype is a hexadecimal string.
C:It is not a raw binary data. The ROWID datatype is a hexadecimal string.
D:It is not binary data stored in an external file. The ROWID datatype is a hexadecimal
string.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 216
Chapter 5: Creating Oracle Database Objects

NEW QUESTION 11
A SELECT statement can be used to perform these three functions:
Choose rows from a table.
Choose columns from a table
Bring together data that is stored in different tables by creating a link between
them.
Which set of keywords describes these capabilities?

• A. difference, projection, join
• B. selection, projection, join
• C. selection, intersection, join
• D. intersection, projection, join
• E. difference, projection, product

Answer: B

Explanation: Explanation: choose rows from a table is SELECTION,
Choose column from a table is PROJECTION
Bring together data in different table by creating a link between them is JOIN.
Incorrect Answer:
Aanswer should have SELECTION, PROJECTION and JOIN.
Canswer should have SELECTION, PROJECTION and JOIN.
Danswer should have SELECTION, PROJECTION and JOIN.
Eanswer should have SELECTION, PROJECTION and JOIN.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-6

NEW QUESTION 12
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 13
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city ='Singapore');
Which statement is true regarding the above query if one of the values generated by the subquery is NULL?

• A. It produces an erro
• B. It executes but returns no row
• C. It generates output for NULL as well as the other values produced by the subquer
• D. It ignores the NULL value and generates output for the other values produced by the subquer

Answer: C

NEW QUESTION 14
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 15
You need to display the date 11-Oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?

• A. SELECT TO_CHAR('11-oct-2007', 'fmDdspth "of" Month, Year') FROM DUAL;
• B. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year') FROM DUAL;
• C. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year') FROM DUAL;
• D. SELECT TO_DATE(TO_CHAR('11-oct-2007','fmDdspth ''of'' Month, Year')) FROM DUAL;

Answer: C

Explanation:
Using the TO_CHAR Function with Dates TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that describes the format of datetime stored in a character string. For example, the datetime format model for the string '11-Nov-1999' is 'DD-Mon-YYYY'. You can use the TO_CHAR function to convert a date from its default format to the one that you specify. Guidelines
.
The format model must be enclosed with single quotation marks and is case-sensitive.
.
The format model can include any valid date format element. But be sure to separate the date value from the format model with a comma.
.
The names of days and months in the output are automatically padded with blanks.
.
To remove padded blanks or to suppress leading zeros, use the fill mode fm element.
Elements of the Date Format Model
DY Three-letter abbreviation of the day of the week
DAY Full name of the day of the week
DD Numeric day of the month
MM Two-digit value for the month
MON Three-letter abbreviation of the month
MONTH Full name of the month
YYYY Full year in numbers
YEAR Year spelled out (in English)

NEW QUESTION 16
Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)

• A. DELETE can be used to remove only rows from only one table at a tim
• B. DELETE can be used to remove only rows from multiple tables at a tim
• C. DELETE can be used only on a table that is a parent of a referential integrity constrain
• D. DELETE can be used to remove data from specific columns as well as complete row
• E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rul

Answer: AE

Explanation:
Transactions, consisting of INSERT, UPDATE, and DELETE (or even MERGE) commands can be made permanent (with a COMMIT) or reversed (with a ROLLBACK). A TRUNCATE command, like any other DDL command, is immediately permanent: it can never be reversed. The Transaction Control Statements A transaction begins implicitly with the first DML statement. There is no command to explicitly start a transaction. The transaction continues through all subsequent DML statements issued by the session. These statements can be against any number of tables: a transaction is not restricted to one table. It terminates (barring any of the events listed in the previous section) when the session issues a COMMIT or ROLLBACK command. The SAVEPOINT command can be used to set markers that will stage the action of a ROLLBACK, but the same transaction remains in progress irrespective of the use of SAVEPOINT Explicit Transaction Control Statements You can control the logic of transactions by using the COMMIT, SAVEPOINT, and ROLLBACK statements. Note: You cannot COMMIT to a SAVEPOINT. SAVEPOINT is not ANSI-standard SQL.

untitled

NEW QUESTION 17
View the Exhibit and examine the structure of the PRODUCTS table.
You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this
condition.
Evaluate the following SQL statement:
SQL>SELECT prod_name,prod_list_price FROM products WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output?
(Choose all that apply.)

• A. ORDER BY prod_list_price DESC, prod_name;
• B. ORDER BY (2*prod_min_price)DESC, prod_name;
• C. ORDER BY prod_name, (2*prod_min_price)DESC;
• D. ORDER BY prod_name DESC, prod_list_price DESC;
• E. ORDER BY prod_list_price DESC, prod_name DESC;

Answer: AE

Explanation:
Using the ORDER BY Clause The order of rows that are returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. However, if you use the ORDER BY clause, it must be the last clause of the SQL statement. Further, you can specify an expression, an alias, or a column position as the sort condition. Syntax SELECT expr FROM table [WHERE condition(s)] [ORDER BY {column, expr, numeric_position} [ASC|DESC]]; In the syntax: ORDER BY specifies the order in which the retrieved rows are displayed ASC orders the rows in ascending order (This is the default order.)
DESC orders the rows in descending order If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order. Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence.

NEW QUESTION 18
Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)

• A. The ON clause can be used to join tables on columns that have different names but compatible data types
• B. A maximum of one pair of columns can be joined between two tables using the ON clause
• C. Both USING and ON clause can be used for equijoins and nonequijoins
• D. The WHERE clause can be used to apply additional conditions in SELECT statement containing the ON or the USING clause

Answer: AD

Explanation:
Creating Joins with the USING Clause If several columns have the same names but the data types do not match, use the USING clause to specify the columns for the equijoin. Use the USING clause to match only one column when more than one column matches. The NATURAL JOIN and USING clauses are mutually exclusive Using Table Aliases with the USING clause When joining with the USING clause, you cannot qualify a column that is used in the USING clause itself. Furthermore, if that column is used anywhere in the SQL statement, you cannot alias it. For example, in the query mentioned in the slide, you should not alias the location_id column in the WHERE clause because the column is used in the USING clause. The columns that are referenced in the USING clause should not have a qualifier (table name oralias) anywhere in the SQL statement. Creating Joins with the ON Clause The join condition for the natural join is basically an equijoin of all columns with the same name. Use the ON clause to specify arbitrary conditions or specify columns to join. – ANSWER C The join condition is separated from other search conditions. ANSWER D
The ON clause makes code easy to understand.

NEW QUESTION 19
Evaluate this SQL statement:
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d
USING (department_id)
WHERE d.department_id NOT IN (10,40)
ORDER BY dept_name;
The statement fails when executed. Which change fixes the error?

• A. remove the ORDER BY clause
• B. remove the table alias prefix from the WHERE clause
• C. remove the table alias from the SELECT clause
• D. prefix the column in the USING clause with the table alias
• E. prefix the column in the ORDER BY clause with the table alias
• F. replace the condition ”d.department_id NOT IN (10,40)” in the WHERE clause with ”d.department_id <> 10 AND d.department_id <> 40”

Answer: B

NEW QUESTION 20
Examine these statements:
CREATE ROLE registrar;
GRANT UPDATE ON student_grades TO registrar;
GRANT registrar to user1, user2, user3;
What does this set of SQL statements do?

• A. The set of statements contains an error and does not wor
• B. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three user
• C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three user
• D. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the rol
• E. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES objec
• F. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registra

Answer: C

Explanation: the statement will create a role call REGISTRAR, grant UPDATE on student_grades to registrar, grant the role to user1,user2 and user3.
Incorrect Answer: Athe statement does not contain error Bthere is no MODIFY privilege Dstatement does not create 3 users with the role Eprivilege is grant to role then grant to user Fprivilege is grant to role then grant to user