Oracle 1Z0-051 Study Guides 2021

NEW QUESTION 1
Evaluate the following SQL statements: Exhibit:

Which is the correct output of the above query?

• A. +00-300, +54-02,+00 11:12:10.123457
• B. +00-300,+00-650,+00 11:12:10.123457
• C. +25-00, +54-02, +00 11:12:10.123457
• D. +25-00,+00-650,+00 11:12:10.123457

Answer: C

NEW QUESTION 2
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 3
View the Exhibit and examine the structure of the CUSTOMERS table.
You want to generate a report showing the last names and credit limits of all customers
whose last names start with A, B, or C, and credit limit is below 10, 000.
Evaluate the following two queries:

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

• A. Only the first query gives the correct resul
• B. Only the second query gives the correct resul
• C. Both execute successfully and give the same resul
• D. Both execute successfully but do not give the required resul

Answer: A

NEW QUESTION 4
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 5
Which constraint can be defined only at the column level?

• A. UNIQUE
• B. NOT NULL
• C. CHECK
• D. PRIMARY KEY
• E. FOREIGN KEY

Answer: B

Explanation:
the NOT NULL constraint can be specified only at the column level, not at the table level.
Incorrect Answer: AUNIQUE can be define at table level CCHECK can be define at table level DPRIMARY KEY can be define at table level EFOREIGN KEY can be define at table level
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-8
New Questions

NEW QUESTION 6
Which two statements are true regarding views? (Choose two.)

• A. A simple view in which column aliases have been used cannot be update
• B. Rows cannot be deleted through a view if the view definition contains the DISTINCT keywor
• C. Rows added through a view are deleted from the table automatically when the view is droppe
• D. The OR REPLACE option is used to change the definition of an existing view without dropping and recreating i
• E. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the vie

Answer: BD

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

• A. A table name can be of any lengt
• B. A table can have any number of column
• C. A column that has a DEFAULT value cannot store null value
• D. A table and a view can have the same name in the same schem
• E. A table and a synonym can have the same name in the same schem
• F. The same table name can be used in different schemas in the same databas

Answer: EF

Explanation:
Synonyms Synonyms are database objects that enable you to call a table by another name. You can create synonyms to give an alternative name to a table.

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

You want to create a SALE_PROD view by executing the following SQL statements:

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

• A. The view will be created and you can perform DLM operations on the view
• B. The view will not be created because the join statements are not allowed for creating a view
• C. The view will not be created because the GROUP BY clause is not allowed for creating a view
• D. The view will be created but no DML operations will be allowed on the view

Answer: D

Explanation:
Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view

NEW QUESTION 9
Which three statements are true regarding views? (Choose three.)

• A. Views can be created only from table
• B. Views can be created from tables or other view
• C. Only simple views can use indexes existing on the underlying table
• D. Both simple and complex views can use indexes existing on the underlying table
• E. Complex views can be created only on multiple tables that exist in the same schem
• F. Complex views can be created on multiple tables that exist in the same or different schema

Answer: BDF

Explanation:
Creating a Sequence (continued)
CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after
reaching its maximum or minimum value
(NOCYCLE is the default option.)
CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and
keeps in memory (By default, the Oracle server caches 20 values.)

NEW QUESTION 10
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 11
Examine the structure of the STUDENTS table:

You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?

• A. SELECT student_ id, marks, ROWNUM "Rank" FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99 AND course_id = 'INT_SQL' ORDER BY marks DESC;
• B. SELECT student_id, marks, ROWID "Rank" FROM students WHERE ROWID <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks;
• C. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC);
• D. SELECT student_id, marks, ROWNUM "Rank” FROM (SELECT student_id, marks FROM students WHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC-99’ AND course_id = ‘INT_SQL’ ORDER BY marks DESC) WHERE ROWNUM <= 10 ;
• E. SELECTstudent id, marks, ROWNUM “Rank” FROM(SELECT student_id, marks FROM students ORDER BY marks) WHEREROWNUM <= 10 ANDfinish date BETWEEN ’01-JAN-99’ AND ’31-DEC-99’ ANDcourse_id = ‘INT_SQL’;

Answer: D

NEW QUESTION 12
Which two statements are true regarding views? (Choose two.)

• A. A sub query that defines a view cannot include the GROUP BY clause
• B. A view is created with the sub query having the DISTINCT keyword can be updated
• C. A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table
• D. A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated

Answer: CD

Explanation:
Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view

NEW QUESTION 13
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 14
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 15
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 16
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which DELETE statement is valid?

• A. DELETE FROM employeesWHERE employee_id = (SELECT employee_id FROM employees);
• B. DELETE * FROM employeesWHERE employee_id=(SELECT employee_id FROM new_employees);
• C. DELETE FROM employeesWHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ‘Carrey’);
• D. DELETE * FROM employeesWHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ‘Carrey’);

Answer: C

NEW QUESTION 17
Examine the statement:
GRANT select, insert, update
ON student_grades
TO manager
WITH GRANT OPTION;
Which two are true? (Choose two.)

• A. MANAGER must be a rol
• B. It allows the MANAGER to pass the specified privileges on to other user
• C. It allows the MANAGER to create tables that refer to the STUDENT_GRADES tabl
• D. It allows the MANAGER to apply all DML statements on the STUDENT_GRADES tabl
• E. It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES tabl
• F. It allows the MANAGER the ability to select from, delete from, and update the STUDENT_GRADES tabl

Answer: BE

Explanation:
GRANT ROLE to ROLE/USER
Incorrect Answer: ARole can be grant to user CCreate table privilege is not granted DExecute privilege is not granted FDelete privilege is not granted
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-15

NEW QUESTION 18
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