Latest 1Z0-051 Testing Engine 2021

NEW QUESTION 1

Which is an iSQL*Plus command?

• A. INSERT
• B. UPDATE
• C. SELECT
• D. DESCRIBE
• E. DELETE
• F. RENAME

Answer: D

Explanation:
Explanation:
The only SQL*Plus command in this list: DESCRIBE. It cannot be used as SQL command.
This command returns a description of tablename, including all columns in that table, the
datatype for each column and an indication of whether the column permits storage of NULL
values.
Incorrect Answer:
A INSERT is not a SQL*PLUS command
B UPDATE is not a SQL*PLUS command
C SELECT is not a SQL*PLUS command
E DELETE is not a SQL*PLUS command
F RENAME is not a SQL*PLUS command
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7

NEW QUESTION 2

See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables:

The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following the CREATE TABLE command:
Exhibit:
Which statement is true regarding the above command?

• A. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match
• B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table
• C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition
• D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table

Answer: B

Explanation:

Creating a Table Using a Subquery
Create a table and insert rows by combining the CREATE
TABLE statement and the AS subquery option.
CREATE TABLE table
[(column, column...)]
AS subquery;
Match the number of specified columns to the number of subquery columns.
Define columns with column names and default values.
Guidelines
The table is created with the specified column names, and the rows retrieved by the
SELECT statement are inserted into the table.
The column definition can contain only the column name and default value.
If column specifications are given, the number of columns must equal the number of
columns in the subquery SELECT list.
If no column specifications are given, the column names of the table are the same as the
column names in the subquery.
The column data type definitions and the NOT NULL constraint are passed to the new
table. Note that only the explicit NOT NULL constraint will be inherited. The PRIMARY KEY
column will not pass the NOT NULL feature to the new column. Any other constraint rules
are not passed to the new table. However, you can add constraints in the column definition.

NEW QUESTION 3

Which two statements about sub queries are true? (Choose two.)

• A. A sub query should retrieve only one ro
• B. A sub query can retrieve zero or more row
• C. A sub query can be used only in SQL query statement
• D. Sub queries CANNOT be nested by more than two level
• E. A sub query CANNOT be used in an SQL query statement that uses group function
• F. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one colum

Answer: BF

Explanation:
Explanation: sub query can retrieve zero or more rows, sub query is used with an inequality comparison operator in the outer SQL statement, and the column list in the SELECT clause of the sub query should contain only one column.
Incorrect Answer: Asub query can retrieve zero or more rows Csub query is not SQL query statement Dsub query can be nested Egroup function can be use with sub query

NEW QUESTION 4

Which three are true? (Choose three.)

• A. A MERGE statement is used to merge the data of one table with data from anothe
• B. A MERGE statement replaces the data of one table with that of anothe
• C. A MERGE statement can be used to insert new rows into a tabl
• D. A MERGE statement can be used to update existing rows in a tabl

Answer: ACD

Explanation:
The MERGE Statement allows you to conditionally insert or update data in a table. If the rows are present in the target table which match the join condition, they are updated if the rows are not present they are inserted into the target table

NEW QUESTION 5

Examine the structure of the PRODUCTS table:

You want to display the names of the products that have the highest total value for UNIT_PRICE *QTY_IN_HAND.
Which SQL statement gives the required output?

• A. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products);
• B. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);
• C. SELECT prod_name FROM products GROUP BY prod_name HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);
• D. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand)) FROM products) GROUP BY prod_name;

Answer: A

NEW QUESTION 6

The PRODUCTS table has these columns:
PRODUCT_ID NUMBER(4)
PRODUCT_NAME VARCHAR2(45)
PRICE NUMBER(8,2)
Evaluate this SQL statement:
SELECT *
FROM PRODUCTS
ORDER BY price, product_name;
What is true about the SQL statement?

• A. The results are not sorte
• B. The results are sorted numericall
• C. The results are sorted alphabeticall
• D. The results are sorted numerically and then alphabeticall

Answer: D

Explanation:

the result is sort by price which is numeric and follow by product_name which is alphabetically.
Incorrect Answer: Athe results are sorted Bthe results are sorted with alphabetically as well Cthe results are sorted with numerically as well
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-2

NEW QUESTION 7

View the Exhibit and examine the structure of CUSTOMERS table. Evaluate the following query:

Which statement is true regarding the above query?

• A. It executes successfull
• B. It produces an error because the condition on the CUST_CITY column is not vali
• C. It produces an error because the condition on the CUST_FIRST_NAME column is not vali
• D. It produces an error because conditions on the CUST_CREDIT_LIMIT column are not vali

Answer: A

NEW QUESTION 8

Which two statements are true regarding single row functions? (Choose two.)

• A. They can be nested only to two levels
• B. They always return a single result row for every row of a queried table
• C. Arguments can only be column values or constant
• D. They can return a data type value different from the one that is referenced
• E. They accept only a single argument

Answer: BD

Explanation:

A function is a program written to optionally accept input parameters, perform an operation, or return a single value. A function returns only one value per execution. Three important components form the basis of defining a function. The first is the input parameter list. It specifies zero or more arguments that may be passed to a function as input for processing. These arguments or parameters may be of differing data types, and some are mandatory while others may be optional. The second component is the data type of its resultant value. Upon execution, only one value is returned by the function. The third encapsulates the details of the processing performed by the function and contains the program code that optionally manipulates the input parameters, performs calculations and operations, and generates a return value.

NEW QUESTION 9

Examine the structure proposed for the TRANSACTIONS table:

Which statements are true regarding the creation and storage of data in the above table structure? (Choose all that apply.)

• A. The CUST_STATUS column would give an erro
• B. The TRANS_VALIDITY column would give an erro
• C. The CUST_STATUS column would store exactly one characte
• D. The CUST_CREDIT_LIMIT column would not be able to store decimal value
• E. The TRANS_VALIDITY column would have a maximum size of one characte
• F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of second

Answer: BC

Explanation:

VARCHAR2(size)Variable-length character data (A maximum size must be specified:
minimum size is 1; maximum size is 4,000.)
CHAR [(size)] Fixed-length character data of length size bytes (Default and minimum size
is 1; maximum size is 2,000.)
NUMBER [(p,s)] Number having precision p and scale s (Precision is the total number of
decimal digits and scale is the number of digits to the right of the decimal point; precision
can range from 1 to 38, and scale can range from –84 to 127.)
DATE Date and time values to the nearest second between January 1, 4712 B.C., and
December 31, 9999 A.D.

NEW QUESTION 10

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 executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105.
• B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105.
• C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105.
• D. The statement fails because there is more than one row matching the employee name Smit
• E. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES tabl
• F. The statement fails because there is no 'Administration' department in the DEPARTMENTS tabl

Answer: D

Explanation:

'=' is use in the statement and sub query will return more than one row.
Employees table has 2 row matching the employee name Smith.
The update statement will fail.
Incorrect Answers :
A. The Update statement will fail no update was done.
B. The update statement will fail no update was done.
C. The update statement will fail no update was done.
E. The update statement will fail but not due to job_it='IT_ADMIN'
F. The update statement will fail but not due to department_id='Administration'
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Sub queries, p. 6-12

NEW QUESTION 11

Which two statements are true regarding the ORDER BY clause? (Choose two.)

• A. It is executed first in the query executio
• B. It must be the last clause in the SELECT statemen
• C. It cannot be used in a SELECT statement containing a HAVING claus
• D. You cannot specify a column name followed by an expression in this claus
• E. You can specify a combination of numeric positions and column names in this claus

Answer: BE

NEW QUESTION 12

You created an ORDERS table with the following description: Exhibit:

You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column.
Which statement is true in this scenario?

• A. You cannot add a primary key constraint if data exists in the column
• B. You can add the primary key constraint even if data exists, provided that there are no duplicate values
• C. The primary key constraint can be created only a the time of table creation
• D. You cannot have two constraints on one column

Answer: B

NEW QUESTION 13

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 14

View the Exhibit and examine the structure of the EMPLOYEES table.

Examine the data in the ENAME and HIREDATE columns of the EMPLOYEES table:
ENAME HIREDATE
SMITH 17-DEC-80 ALLEN 20-FEB-81 WARD 22-FEB-81
You want to generate a list of user IDs as follows: USERID
Smi17DEC80 All20FEB81 War22FEB81
You issue the following query:
SQL>SELECT CONCAT(SUBSTR(INITCAP(ename),1,3), REPLACE(hiredate,'-'))
"USERID"
FROM employees;
What is the outcome?

• A. It executes successfully and gives the correct outpu
• B. It executes successfully but does not give the correct outpu
• C. It generates an error because the REPLACE function is not vali
• D. It generates an error because the SUBSTR function cannot be nested in the CONCAT functio

Answer: A

Explanation:

REPLACE(text, search_string,replacement_string) Searches a text expression for a character string and, if found, replaces it with a specified replacement string The REPLACE Function The REPLACE function replaces all occurrences of a search item in a source string with a replacement term and returns the modified source string. If the length of the replacement term is different from that of the search item, then the lengths of the returned and source strings will be different. If the search string is not found, the source string is returned unchanged. Numeric and date literals and expressions are evaluated before being implicitly cast as characters when they occur as parameters to the REPLACE function. The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is REPLACE (source string, search item, [replacement term]). If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string. . The following queries illustrate the REPLACE function with numeric and date expressions: Query 1: select replace(10000-3,'9','85') from dual Query 2: select replace(sysdate, 'DEC','NOV') from dual

NEW QUESTION 15

The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
Which statement finds the highest grade point average (GPA) per semester?

• A. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;
• B. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
• C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;
• D. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;
• E. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

Answer: C

Explanation:
Explanation: For highest gpa value MAX function is needed, for result with per semester GROUP BY clause is needed
Incorrect Answer: Aper semester condition is not included Bresult would not display the highest gpa value Dinvalid syntax error Einvalid syntax error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7

NEW QUESTION 16

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

Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?

• A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers;
• B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers;
• C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers;
• D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers;

Answer: D

Explanation:

NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number.
Data types must match:
–
NVL(commission_pct,0)
–
NVL(hire_date,'01-JAN-97')
–
NVL(job_id,'No Job Yet')

NEW QUESTION 17

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 18

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 19

View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001, starting with the latest promo. Which query would give the required result? (Choose all that apply.)

• A. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begiii_date > '01-JAN-01' ORDER BY 2 DESC;
• B. SELECT promo_nam
• C. promo_begiii_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC:
• D. SELECT promo_nam
• E. promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 1DESC:
• F. SELECT promo_name, promo_begin_date "START DATE" FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;

Answer: AD

NEW QUESTION 20

View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement:

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

• A. It executes successfull
• B. It returns an error because the BETWEEN operator cannot be used in the HAVING claus
• C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statemen
• D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same colum

Answer: A

NEW QUESTION 21

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 22

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 23

Which three statements are true regarding sub queries? (Choose three.)

• A. Multiple columns or expressions can be compared between the main query and sub query
• B. Main query and sub query can get data from different tables
• C. Sub queries can contain GROUP BY and ORDER BY clauses
• D. Main query and sub query must get data from the same tables
• E. Sub queries can contain ORDER BY but not the GROUP BY clause
• F. Only one column or expression can be compared between the main query and subqeury

Answer: ABC

NEW QUESTION 24

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 25

The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query successfully?
SELECT * FROM ord;

• A. CREATE SYNONYM ord FOR orders; This command is issued by O
• B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by O
• C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrato
• D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrato

Answer: D

Explanation:

Creating a Synonym for an Object To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views. In the syntax: PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created Guidelines The object cannot be contained in a package. A private synonym name must be distinct from all other objects that are owned by the same user. If you try to execute the following command (alternative B, issued by OE):

NEW QUESTION 26

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 the SELECT statement cannot be used in place of the table name
• 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 two tables cannot be used in a single UPDATE statement

Answer: B

NEW QUESTION 27

View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.

Evaluate the following SQL statements:

Which statement is true regarding the above compound query?

• A. It shows products that have a cost recorded irrespective of sales
• B. It shows products that were sold and have a cost recorded
• C. It shows products that were sold but have no cost recorded
• D. It reduces an error

Answer: C

NEW QUESTION 28

Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements insert a row into the table? (Choose three.)

• A. INSERT INTO employees VALUES ( NULL, 'John', 'Smith');
• B. INSERT INTO employees( first_name, last_name) VALUES( 'John', 'Smith');
• C. INSERT INTO employees VALUES ( 1000, 'John', NULL);
• D. INSERT INTO employees (first_name, last_name, employee_id) VALUES ( 1000, 'John', 'Smith');
• E. INSERT INTO employees (employee_id) VALUES (1000);
• F. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'John', ' ');

Answer: CEF

Explanation:
EMPLOYEE_ID is a primary key. Incorrect Answer: AEMPLOYEE_ID cannot be null BEMPLOYEE_ID cannot be null Dmismatch of field_name with datatype
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-11

NEW QUESTION 29
......