Oracle 1Z0-051 Dumps Questions 2021

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

You want to display the category with the maximum number of items. You issue the following query:
SQL>SELECT COUNT(*),prod_category_id FROM products GROUP BY prod_category_id HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);
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 subquery does not have a GROUP BY claus
• D. It generates an error because = is not valid and should be replaced by the IN operato

Answer: C

NEW QUESTION 2
View the Exhibit and examine the structure of the PROMOTIONS table.
Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on 'TV' and 'internet' that ended in the time interval 15th March '00 to 15th October '00.
Which two queries would give the required result? (Choose two.)

• A. SELECT promo_name, promo_cost FROM promotions WHERE promo_category IN ('TV', 'internet') AND promo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00';
• B. SELECT promo_name, promo_cost FROM promotions WHERE promo_category = 'TV' OR promo_category ='internet' AND promo_end_date >='15-MAR-00' OR promo_end_date <='15-OCT-00';
• C. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category BETWEEN 'TV' AND 'internet') AND (promo_end_date IN ('15-MAR-00','15-OCT-00'));
• D. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category = 'TV' OR promo_category ='internet') AND (promo_end_date >='15-MAR-00' AND promo_end_date <='15-OCT-00');

Answer: AD

NEW QUESTION 3
Examine the description of the EMP_DETAILS table given below: Exhibit:

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)

• A. An EMP_IMAGE column can be included in the GROUP BY clause
• B. You cannot add a new column to the table with LONG as the data type
• C. An EMP_IMAGE column cannot be included in the ORDER BY clause
• D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column

Answer: BC

Explanation:
LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB.
There can only be one LONG column in a table.
Guidelines
A LONG column is not copied when a table is created using a subquery.
A LONG column cannot be included in a GROUP BY or an ORDER BY clause.
Only one LONG column can be used per table.
No constraints can be defined on a LONG column.
You might want to use a CLOB column rather than a LONG column.

NEW QUESTION 4
The DBA issues this SQL command:
CREATE USER scott IDENTIFIED by tiger;
What privileges does the user Scott have at this point?

• A. no privileges
• B. only the SELECT privilege
• C. only the CONNECT privilege
• D. all the privileges of a default user

Answer: A

Explanation:
when a user is created, by default no privilege is granted
Incorrect Answer:
BSELECT is not grant
CCONNECT is not grant
Ddefault profile is grant by default not privilege.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-6

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

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_begin_date > '01-JAN-01' ORDER BY 1 DESC;
• B. SELECT promo_name,promo_begin_date "START DATE" FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;
• C. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 2 DESC;
• D. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC;

Answer: BC

NEW QUESTION 6
View the Exhibit and examine the structure of the PROMOTIONS table.
You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:
1.
The promo name should not begin with 'T' or 'N'.
2.
The promo should cost more than $20000.
3.
The promo should have ended after 1st January 2001.
Which WHERE clause would give the required result?

• A. WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
• B. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'
• C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
• D. WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')

Answer: C

NEW QUESTION 7
The user Sue issues this SQL statement:
GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION;
The user Alice issues this SQL statement:
GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION;
The user Reena issues this SQL statement:
GRANT SELECT ON sue.EMP TO timber;
The user Sue issues this SQL statement:
REVOKE select on sue.EMP FROM alice;
For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?

• A. Alice only
• B. Alice and Reena
• C. Alice, Reena, and Timber
• D. Sue, Alice, Reena, and Timber

Answer: C

Explanation: use the REVOKE statement to revoke privileges granted to other users. Privilege granted to others through the WITH GRANT OPTION clause are also revoked. Alice, Reena and Timber will be revoke.
Incorrect Answer: Athe correct answer should be Alice, Reena and Timber Bthe correct answer should be Alice, Reena and Timber Dthe correct answer should be Alice, Reena and Timber
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-17

NEW QUESTION 8
You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:
Exhibit:

PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.
What is the outcome?

• A. Number of days since the promo started based on the current Chicago data and time
• B. Number of days since the promo started based on the current Singapore data and tim
• C. An error because the WHERE condition specified is invalid
• D. An error because the ROUND function specified is invalid

Answer: A

NEW QUESTION 9
See the Exhibit and examine the structure of the PROMOSTIONS table: Exhibit:

Which SQL statements are valid? (Choose all that apply.)

• A. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost, promo_cost * 0.25, 100) "Discount" FROM promotions;
• B. SELECT promo_id, DECODE(promo_cost, 10000, DECODE(promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost" FROM promotions;
• C. SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), NULL, promo_cost*.25, 'N/A') "Catcost" FROM promotions;
• D. SELECT promo_id, DECODE(promo_cost, >10000, 'High', <10000, 'Low') "Range" FROM promotions;

Answer: AB

Explanation:
The DECODE Function Although its name sounds mysterious, this function is straightforward. The DECODE function implements ifthen-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. The DECODE function takes at least three mandatory parameters, but can take many more. The syntax of the function is DECODE(expr1,comp1, iftrue1, [comp2,iftrue2...[ compN,iftrueN]], [iffalse]).

NEW QUESTION 10
Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)

• A. The BLOB data type column is used to store binary data in an operating system file
• B. The minimum column width that can be specified for a VARCHAR2 data type column is one
• C. A TIMESTAMP data type column stores only time values with fractional seconds
• D. The value for a CHAR data type column is blank-padded to the maximum defined column width
• E. Only One LONG column can be used per table

Answer: BDE

Explanation:
LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB. There can only be one LONG column in a table. DVARCHAR2 Variable-length character data, from 1 byte to 4KB. The data is stored in the database character set. The VARCHAR2 data type must be qualified with a number indicating the maximum length of the column. If a value is inserted into the column that is less than this, it is not a problem: the value will only take up as much space as it needs. If the value is longer than this maximum, the INSERT will fail with an error. VARCHAR2(size) Variable-length character data (A maximum size must be specified: minimum size is 1; maximum size is 4,000.) BLOB Like CLOB, but binary data that will not undergo character set conversion by Oracle
Net.
BFILE A locator pointing to a file stored on the operating system of the database server.
The size of the files is limited to 4GB.
TIMESTAMP This is length zero if the column is empty, or up to 11 bytes, depending on
the precision specified.
Similar to DATE, but with precision of up to 9 decimal places for the seconds, 6 places by
default.

NEW QUESTION 11
You work as a database administrator at ABC.com. You study the exhibit carefully 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 execute and restrict modifications to only the column specified in the SELECT statement
• B. It would not execute because two tables cannot be used in a single UPDATE 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 the SELECT statement cannot be used in place of the table name

Answer: A

NEW QUESTION 12
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 13
Which two statements are true regarding constraints? (Choose two.)

• A. A foreign key cannot contain NULL value
• B. The column with a UNIQUE constraint can store NULLS .
• C. A constraint is enforced only for an INSERT operation on a tabl
• D. You can have more than one column in a table as part of a primary ke

Answer: BD

NEW QUESTION 14
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 15
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 16
You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.
Which statement accomplishes this task?

• A. ALTER TABLE students ADD PRIMARY KEY student_id;
• B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
• C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
• D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
• E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

Answer: D

Explanation:
ALTER TABLE table_name
ADD [CONSTRAINT constraint] type (coloumn);
Incorrect Answer:
Awrong syntax
Bwrong syntax
Cwrong syntax
Eno such MODIFY keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-17