Oracle 1Z0-051 Exam Dumps 2021

NEW QUESTION 1
See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:



You issue the following query:

Which statement is true regarding the outcome of this query?

• A. It produces an error because the NATURAL join can be used only with two tables
• B. It produces an error because a column used in the NATURAL join cannot have a qualifier
• C. It produces an error because all columns used in the NATURAL join should have a qualifier
• D. It executes successfully

Answer: B

Explanation:
Creating Joins with the USING Clause
Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin.
The Natural JOIN USING Clause
The format of the syntax for the natural JOIN USING clause is as follows: SELECT table1.column, table2.column FROM table1 JOIN table2 USING (join_column1, join_column2…); While the pure natural join contains the NATURAL keyword in its syntax, the JOIN…USING syntax does not. An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on certain columns, and this format caters to this requirement.

NEW QUESTION 2
Which is the valid CREATE [TABLE statement?

• A. CREATE TABLE emp9$# (emp_no NUMBER(4));
• B. CREATE TABLE 9emp$# (emp_no NUMBER(4));
• C. CREATE TABLE emp*123 (emp_no NUMBER(4));
• D. CREATE TABLE emp9$# (emp_no NUMBER(4). date DATE);

Answer: A

Explanation:
Schema Object Naming Rules
Every database object has a name. In a SQL statement, you represent the name of an
object with a quoted identifier or a nonquoted identifier.
A quoted identifier begins and ends with double quotation marks ("). If you name a schema
object using a quoted identifier, then you must use the double quotation marks whenever
you refer to that object.
A nonquoted identifier is not surrounded by any punctuation.
The following list of rules applies to both quoted and nonquoted identifiers unless otherwise
indicated:
Names must be from 1 to 30 bytes long with these exceptions:
Names of databases are limited to 8 bytes.
Names of database links can be as long as 128 bytes.
If an identifier includes multiple parts separated by periods, then each attribute can be up to
30 bytes long.
Each period separator, as well as any surrounding double quotation marks, counts as one
byte. For example, suppose you identify a column like this:
"schema"."table"."column"
Nonquoted identifiers cannot be Oracle Database reserved words (ANSWER D). Quoted identifiers can be reserved words, although this is not recommended. Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words. The Oracle SQL language contains other words that have special meanings. These words include datatypes, schema names, function names, the dummy system table DUAL, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally in specific ways. Therefore, if you use these words as names for objects and object parts, then your SQL statements may be more difficult to read and may lead to unpredictable results. In particular, do not use words beginning with SYS_ as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions. You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems. Nonquoted identifiers must begin with an alphabetic character (ANSWER B - begins with 9) from your database character set. Quoted identifiers can begin with any character. Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers. Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (). Within a namespace, no two objects can have the same name. Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive. By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace: employees "employees" "Employees" "EMPLOYEES"
Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace: employees EMPLOYEES "EMPLOYEES" Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name. Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.

NEW QUESTION 3
See the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:

Using the PROMOTIONS table, you need to find out the average cost for all promos in the
range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
Exhibit:

What would be the outcome?

• A. It generates an error because multiple conditions cannot be specified for the WHEN clause
• B. It executes successfully and gives the required result
• C. It generates an error because CASE cannot be used with group functions
• D. It generates an error because NULL cannot be specified as a return value

Answer: B

Explanation:
CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END

NEW QUESTION 4
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 5
Which object privileges can be granted on a view?

• A. none
• B. DELETE, INSERT,SELECT
• C. ALTER, DELETE, INSERT, SELECT
• D. DELETE, INSERT, SELECT, UPDATE

Answer: D

Explanation: Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE.
Incorrect Answer: AObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE BObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE CObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-12

NEW QUESTION 6
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?

• A. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement
• B. It executes successfully and displays rows in the descending order of PROMO_CATEGORY
• C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement
• D. It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators

Answer: A

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 7
Which is a valid CREATE TABLE statement?

• A. CREATE TABLE EMP9$# AS (empid number(2));
• B. CREATE TABLE EMP*123 AS (empid number(2));
• C. CREATE TABLE PACKAGE AS (packid number(2));
• D. CREATE TABLE 1EMP_TEST AS (empid number(2));

Answer: A

Explanation: Table names and column names must begin with a letter and be 1-30
characters long. Characters A-Z,a-z, 0-9, _, $ and # (legal characters but their use is
discouraged).
Incorrect Answer:
BNon alphanumeric character such as “*” is discourage in Oracle table name.
DTable name must begin with a letter.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-4

NEW QUESTION 8
Which three statements are true about multiple-row sub queries? (Choose three.)

• A. They can contain a subquery within a sub quer
• B. They can return multiple columns as well as row
• C. They cannot contain a sub query within a sub quer
• D. They can return only one column but multiple row
• E. They can contain group functions and GROUP BY and HAVING clause
• F. They can contain group functions and the GROUP BY clause, but not the HAVING claus

Answer: ABE

NEW QUESTION 9
See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:

You need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)

• A. SELECT custname, grade FROM customers, grades WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval;
• B. SELECT custname, grade FROM customers, grades WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval AND cust_credit_limit BETWEEN startval AND endval;
• C. SELECT custname, grade FROM customers, grades WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit) FROM customers) AND cust_credit_limit BETWEEN startval AND endval;
• D. SELECT custname, grade FROM customers , grades WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit) FROM customers) AND MAX(cust_credit_limit) BETWEEN startval AND endval;

Answer: BC

NEW QUESTION 10
Examine the structure of the MARKS table:
Exhibit:

Which two statements would execute successfully? (Choose two.)

• A. SELECT student_name,subject1 FROM marks WHERE subject1 > AVG(subject1);
• B. SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE 'R%';
• C. SELECT SUM(subject1+subject2+subject3) FROM marks WHERE student_name IS NULL;
• D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) FROM marks WHERE subject1 > subject2;

Answer: CD

NEW QUESTION 11
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 12
View the Exhibit and examine the data in the PROMOTIONS table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000.
Which SQL statement would you use?

• A. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category = 'post' AND promo_begin_date < '01-01-00';
• B. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';
• C. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';
• D. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';

Answer: D

NEW QUESTION 13
View the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements:

Which statement is true regarding the above two SQL statements?

• A. statement 1 gives an error, statement 2 executes successfully
• B. statement 2 gives an error, statement 1 executes successfully
• C. statement 1 and statement 2 execute successfully and give the same output
• D. statement 1 and statement 2 execute successfully and give a different output

Answer: D

NEW QUESTION 14
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 15
Examine the structure of the CUSTOMERS table:

CUSTNO is the PRIMARY KEY in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names.
Which two methods can you use to get the required result? (Choose two.)

• A. self-join
• B. subquery
• C. full outer-join with self-join
• D. left outer-join with self-join
• E. right outer-join with self-join

Answer: AB

NEW QUESTION 16
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.