Approved 1Z0-051 Exam Questions 2021

NEW QUESTION 1
Examine the SQL statement that creates ORDERS table:
CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN ('CREDIT', 'CASH')), PROD_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order_id, order_date));
For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.)

• A. SER_NO
• B. ORDER_ID
• C. STATUS
• D. PROD_ID
• E. ORD_TOTAL
• F. composite index on ORDER_ID and ORDER_DATE

Answer: AF

Explanation: Index exist for UNIQUE and PRIMARY KEY constraints
Incorrect Answer: BORDER_ID is neither UNIQUE nor PRIMARY KEY CSTATUS is neither UNIQUE nor PRIMARY KEY DPROD_ID is neither UNIQUE nor PRIMARY KEY EORD_TOTAL is neither UNIQUE nor PRIMARY KEY
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-15

NEW QUESTION 2
Evaluate the SQL statement:
SELECT LPAD (salary,10,’*’)
FROM EMP
WHERE EMP_ID = 1001;
If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?

• A. 17000.00
• B. 17000*****
• C. ****170.00
• D. **17000.00
• E. an error statement

Answer: D

NEW QUESTION 3
Examine the structure of the EMPLOYEES table:

You want to create a SQL script file that contains an INSERT statement. When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below:

Which INSERT statement meets the above requirements?

• A. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
• B. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did IN (20,50));
• C. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50)) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
• D. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECK OPTION) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
• E. INSERT INTO (SELECT * FROM employees WHERE (department_id = 20 AND department_id = 50) WITH CHECK OPTION ) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);

Answer: D

NEW QUESTION 4
View the Exhibits and examine the structures of the PRODUCTS SALES and CUSTOMERS tables.

You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in Tokyo'. Which two queries give the required result? (Choose two.)

• A. SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p USING(prod_id) JOIN customers c USING(cust_id) WHERE c.cust_city='Tokyo';
• B. SELECT c.cust_last_name, p.prod_name, s.quantity_sold FROM products p JOIN sales s JOIN customers c ON(p.prod_id=s.prod_id) ON(s.cust_id=c.cust_id) WHERE c.cust_city='Tokyo';
• C. SELECT c.cust_last_name, p.prod_name, s.quantity_sold FROM products p JOIN sales s ON(p.prod_id=s.prod_id) JOIN customers c ON(s.cust_id=c.cust_id) AND c.cust_city='Tokyo';
• D. SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM products p JOIN sales s USING(prod_id) JOIN customers c USING(cust_id) WHERE c.cust_city='Tokyo';

Answer: AC

NEW QUESTION 5
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 6
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 7
Evaluate the SQL statement
DROP TABLE DEPT:
Which four statements are true of the SQL statement? (Choose four)

• A. You cannot roll back this statemen
• B. All pending transactions are committe
• C. All views based on the DEPT table are delete
• D. All indexes based on the DEPT table are droppe
• E. All data in the table is deleted, and the table structure is also delete
• F. All data in the table is deleted, but the structure of the table is retaine
• G. All synonyms based on the DEPT table are delete

Answer: ABDE

Explanation:
You cannot roll back DROP TABLE statement. All pending transactions related on this
table are committed. If the table is dropped, Oracle automatically drops any index, trigger
and constraint associated with the table as well. All data in the table is deleted, and the
table structure is also deleted.
Incorrect Answers
C:All views based on the DEPT table become invalid, but they are not deleted.
F:All data in the table is deleted, and the table structure is also deleted. Command
TRUNCATE deletes all data in the table, but does not delete the structure of the table.
G:All synonyms based on the DEPT table are not deleted after dropping the table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 225 Chapter 5: Creating Oracle Database Objects

NEW QUESTION 8
View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.)

• A. CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id = 2034 WITH CHECK OPTION;
• B. CREATE VIEW v1 AS SELECT * FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION;
• C. CREATE VIEW v2 AS SELECT prod_id, cust_id, time_id FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION;
• D. CREATE VIEW v4 AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES WHERE time_id <= SYSDATE - 2*365 GROUP BY prod_id, cust_id WITH CHECK OPTION;

Answer: AB

Explanation:
Creating a View You can create a view by embedding a subquery in the CREATE VIEW statement. In the syntax: CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; OR REPLACE Re-creates the view if it already exists FORCE Creates the view regardless of whether or not the base tables exist NOFORCE Creates the view only if the base tables exist (This is the default.) View Is the name of the view alias Specifies names for the expressions selected by the view’s query (The number of aliases must match the number of expressions selected by the view.) subquery Is a complete SELECT statement (You can use aliases for the columns in the SELECT list.) WITH CHECK OPTION Specifies that only those rows that are accessible to the view can be inserted or updated ANSWER D constraint Is the name assigned to the CHECK OPTION constraint WITH READ ONLY Ensures that no DML operations can be performed on this view 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 – ANSWER C

NEW QUESTION 9
Which statement is true regarding transactions? (Choose all that apply.)

• A. A transaction can consist only of a set of DML and DDL statement
• B. A part or an entire transaction can be undone by using ROLLBACK comman
• C. A transaction consists of a set of DML or DCL statement
• D. A part or an entire transaction can be made permanent with a COMMI
• E. A transaction can consist of only a set of queries or DML or DDL statement

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 structure of the INVOICE table: Exhibit:

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

• A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete') FROM invoice;
• B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available') FROM invoice;
• C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice;
• D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available') FROM invoice;

Answer: AC

Explanation:
The NVL2 Function
The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not. 5-6 The NVL function If the first term is not null, the second parameter is returned, else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null. The NVL2 function takes three mandatory parameters. Its syntax is NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG. They must either be of the same type, or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter.

NEW QUESTION 12
You need to design a student registration database that contains several tables storing academic information.
The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.
You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?

• A. CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));
• B. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
• C. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));
• D. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

Answer: D

Explanation: CONSTRAINT name FOREIGN KEY (column_name) REFERENCES table_name (column_name);
Incorrect Answer: Ainvalid syntax Binvalid syntax Cinvalid syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-14

NEW QUESTION 13
Which two statements are true about WHERE and HAVING clauses? (Choose two)

• A. A WHERE clause can be used to restrict both rows and group
• B. A WHERE clause can be used to restrict rows onl
• C. A HAVING clause can be used to restrict both rows and group
• D. A HAVING clause can be used to restrict groups onl
• E. A WHERE clause CANNOT be used in a query of the query uses a HAVING claus
• F. A HAVING clause CANNOT be used in sub querie

Answer: ABDE

Explanation: B: WHERE clause cannot be use to restrict groups
WHERE clause cannot be use when there is group functions.
D: A HAVING clause can only e used to restrict GROUPS.
Note: HAVING clause to specify which groups are to be displayed and thus further restrict the groups on the basis of aggregate information. 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 group that match the criteria in the Having clause are displayed.
Incorrect Answers :
A. Where clause cannot be use to restrict groups
C. A HAVING clause can only e used to restrict GROUPS.
E. WHERE clause cannot be use when there is group function, instead HAVING is to be use.
F. There is no constraint to use HAVING clause in a sub queries.
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Aggregating Data using Group Functions, p. 5-20

NEW QUESTION 14
View the Exhibit and examine the structure of the SALES and PRODUCTS tables.

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You want to list each product ID and the number of times it has been sold.
Evaluate the following query:
SQL>SELECT p.prod_id, COUNT(s.prod_id)
FROM products p _____________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN options can be used in the blank in the above query to get the required output? (Choose two.)

• A. JOIN
• B. FULL OUTER JOIN
• C. LEFT OUTER JOIN
• D. RIGHT OUTER JOIN

Answer: BC

NEW QUESTION 15
Examine the structure of the PROMOS table:

You want to display the list of promo names with the message 'Same Day' for promos that started and ended on the same day.
Which query gives the correct output?

• A. SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day') FROM promos;
• B. SELECT promo_name, NVL(TRUNC(promo_end_date - promo_start_date), 'Same Day') FROM promos;
• C. SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), NULL,'Same Day') FROM promos;
• D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), NULL,'Same day') FROM promos;

Answer: D

Explanation:
The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned ANSWER A - date and String incompatibl;a datatypes for NVL function The Date TRUNC Function The date TRUNC function performs a truncation operation on a date value based on a specified date precision format. The date TRUNC function takes one mandatory and one optional parameter. Its syntax is TRUNC(source date, [date precision format]). The source date parameter represents any value that can be implicitly converted into a date item. The date precision format parameter specifies the degree of truncation and is optional. If it is absent, the default degree of truncation is day. This means that any time component

NEW QUESTION 16
What is true about the WITH GRANT OPTION clause?

• A. It allows a grantee DBA privilege
• B. It is required syntax for object privilege
• C. It allows privileges on specified columns of table
• D. It is used to grant an object privilege on a foreign key colum
• E. It allows the grantee to grant object privileges to other users and role

Answer: AE

Explanation: The GRANT command with the WITH GRANT OPTION clause allows the grantee to grant
object privileges to other users and roles.
Incorrect Answers
A:The WITH GRANT OPTION does not allow a grantee DBA privileges.
B:It is not required syntax for object privileges. It is optional clause of GRANT command.
C:GRANT command does not allows privileges on columns of tables.
D:It is not used to grant an object privilege on a foreign key column.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access in Oracle

NEW QUESTION 17
The EMPLOYEES table has these columns:
LAST NAMEVARCHAR2(35) SALARYNUMBER(8,2) HIRE_DATEDATE
Management wants to add a default value to the SALARY column. You plan to alter the table by using this SQL statement:
ALTER TABLE EMPLOYEES MODIFY (SALARY DEFAULT 5000);
What is true about your ALTER statement?

• A. Column definitions cannot be altered to add DEFAULT value
• B. A change to the DEFAULT value affects only subsequent insertions to the tabl
• C. Column definitions cannot be altered at add DEFAULT values for columns with a NUMBER data typ
• D. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.

Answer: B

Explanation:
A change to the DEFAULT value affects only subsequent insertions to the table. Existing
rows will not be affected.
Incorrect Answers
A:Column definitions can be altered to add DEFAULT values.
C:Column definitions can be altered to add DEFAULT values. It works for columns with a
NUMBER data type also.
D:A change to the DEFAULT value affects only subsequent insertions to the table. Existing rows will not be affected.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 219-224 Chapter 5: Creating Oracle Database Objects

NEW QUESTION 18
View the Exhibit and examine the data in the EMPLOYEES table:
You want to display all the employee names and their corresponding manager names.
Evaluate the following query:
SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME"
FROM employees e ______________ employees m
ON e.manager_id = m.employee_id;
Which JOIN option can be used in the blank in the above query to get the required output?
Exhibit:

• A. only inner JOIN
• B. only FULL OUTER JOIN
• C. only LEFT OUTER JOIN
• D. only RIGHT OUTER JOIN

Answer: C