Oracle 1Z0-051 Exam Dumps 2021

NEW QUESTION 1
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 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
Which statement is true regarding the INTERSECT operator?

• A. It ignores NULL values
• B. The number of columns and data types must be identical for all SELECT statements in the query
• C. The names of columns in all SELECT statements must be identical
• D. Reversing the order of the intersected tables the result

Answer: B

Explanation:
INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and
removing duplicates.
The columns in the queries that make up a compound query can have different names, but
the output result set will use the names of the columns in the first query.

NEW QUESTION 4
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 5
Evaluate the following SQL statements:
DELETE FROM sales;
There are no other uncommitted transactions on the SALES table.
Which statement is true about the DELETE statement?

• A. It removes all the rows as well as the structure of the table
• B. It removes all the rows in the table and deleted rows cannot be rolled back
• C. It removes all the rows in the table and deleted rows can be rolled back
• D. It would not remove the rows if the table has a primary key

Answer: C

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

• A. A subquery can retrieve zero or more row
• B. Only two subqueries can be placed at one leve
• C. A subquery can be used only in SQL query statement
• D. A subquery can appear on either side of a comparison operato
• E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statemen

Answer: AD

Explanation:
Using a Subquery to Solve a Problem Suppose you want to write a query to find out who earns a salary greater than Abel’s salary. To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount. You can solve this problem by combining the two queries, placing one query inside the other query. The inner query (or subquery) returns a value that is used by the outer query (or main query). Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query. Subquery Syntax A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself. You can place the subquery in a number of SQL clauses, including the following: WHERE clause HAVING clause FROM clause In the syntax: operator includes a comparison condition such as >, =, or IN Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) and multiple-row operators (IN, ANY, ALL, EXISTS). The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query. Guidelines for Using Subqueries Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition for readability. (However, the subquery can appear on either side of the comparison operator.) Use single-row operators with single-row subqueries and multiple-row operators with multiple-row subqueries.
Subqueries can be nested to an unlimited depth in a FROM clause but to “only” 255 levels in a WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.

NEW QUESTION 7
Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name Smith?

• A. SELECT COUNT(*) FROM employees WHERE last_name='Smith';
• B. SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith';
• C. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';
• D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';
• E. SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';

Answer: D

NEW QUESTION 8
You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the default format of dd-mon-rr. Which two SQL statements would give the required output? (Choose two.)

• A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL:
• B. SELECT SYSDATE - TOJDATE(X)1/JANUARY/2007") FROM DUAL:
• C. SELECT SYSDATE - TOJDATE('01-JANUARY-2007') FROM DUAL:
• D. SELECT TO_CHAR(SYSDAT
• E. 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL:
• F. SELECT TO_DATE(SYSDAT
• G. *DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL:

Answer: BC

NEW QUESTION 9
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 10
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. Sub queries can contain GROUP BY and ORDER BY clauses
• C. Only one column or expression can be compared between the main query and subqeury
• D. Main query and sub query can get data from different tables
• E. Main query and sub query must get data from the same tables
• F. Sub queries can contain ORDER BY but not the GROUP BY clause

Answer: ABD

NEW QUESTION 11
Which statement describes the ROWID data type?

• A. Binary data up to 4 gigabyte
• B. Character data up to 4 gigabyte
• C. Raw binary data of variable length up to 2 gigabyte
• D. Binary data stored in an external file, up to 4 gigabyte
• E. A hexadecimal string representing the unique address of a row in its tabl

Answer: E

Explanation:
The ROWID datatype stores information related to the disk location of table rows. They
also uniquely identify the rows in your table. The ROWID datatype is stored as a
hexadecimal string.
Incorrect Answers
A:It is not a binary data. The ROWID datatype is a hexadecimal string.
B:It is not a character data. The ROWID datatype is a hexadecimal string.
C:It is not a raw binary data. The ROWID datatype is a hexadecimal string.
D:It is not binary data stored in an external file. The ROWID datatype is a hexadecimal
string.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 216
Chapter 5: Creating Oracle Database Objects

NEW QUESTION 12
Examine the structure of the PROMOTIONS table:

The management wants to see a report of unique promotion costs in each promotion category.
Which query would achieve the required result?

• A. SELECT DISTINCT promo_cost, promo_category FROM promotions;
• B. SELECT promo_category, DISTINCT promo_cost FROM promotions;
• C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
• D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

Answer: D

NEW QUESTION 13
Which statement is true regarding the UNION operator?

• A. The number of columns selected in all SELECT statements need to be the same
• B. Names of all columns must be identical across all SELECT statements
• C. By default, the output is not sorted
• D. NULL values are not ignored during duplicate checking

Answer: A

Explanation:
The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.

NEW QUESTION 14
Evaluate these two SQL statements:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC;
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;
What is true about them?

• A. The two statements produce identical result
• B. The second statement returns a syntax erro
• C. There is no need to specify DESC because the results are sorted in descending order by defaul
• D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statemen

Answer: A

Explanation: Explanation: the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.
Incorrect Answer: Bthere is no syntax error Cresult are sorted in ascending order by default DORDER BY 2 will take the second column as sorting column. Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22

NEW QUESTION 15
Which view should a user query to display the columns associated with the constraints on a table owned by the user?

• A. USER_CONSTRAINTS
• B. USER_OBJECTS
• C. ALL_CONSTRAINTS
• D. USER_CONS_COLUMNS
• E. USER_COLUMNS

Answer: D

Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view. Incorrect Answer: Atable to view all constraints definition and names Bshow all object name belong to user Cdoes not display column associated Eno such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25

NEW QUESTION 16
View the Exhibit and examine the structure of the CUSTOMERS table. Exhibit:

you issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name 'king' and whose credit limit is less than the maximum credit limit in countries that have customers with the last name 'king'.

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

• A. It produces an error and the < operator should be replaced by < ANY to get the required output
• B. It produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to get the required output
• C. It executes and shows the required result
• D. It produces an error and the < operator should be replaced by < ALL to get the required output

Answer: C