[Far out] 1z0-051 exam dumps

Top Quality of 1Z0-051 download materials and torrent for Oracle certification for candidates, Real Success Guaranteed with Updated 1Z0-051 pdf dumps vce Materials. 100% PASS Oracle Database: SQL Fundamentals I exam Today!

2021 May 1Z0-051 Study Guide Questions:

Q141. - (Topic 1) 

You issue the following command to drop the PRODUCTS table: 

SQL>DROP TABLE products; 

What is the implication of this command? (Choose all that apply.) 

A. All data in the table are deleted but the table structure will remain 

B. All data along with the table structure is deleted 

C. All views and synonyms will remain but they are invalidated 

D. The pending transaction in the session is committed 

E. All indexes on the table will remain but they are invalidated 

Answer: B,C,D 


Q142. - (Topic 2) 

In which four clauses can a sub query be used? (Choose four.) 

A. in the INTO clause of an INSERT statement 

B. in the FROM clause of a SELECT statement 

C. in the GROUP BY clause of a SELECT statement 

D. in the WHERE clause of a SELECT statement 

E. in the SET clause of an UPDATE statement 

F. in the VALUES clause of an INSERT statement 

Answer: A,B,D,E 

Explanation: 

A: a sub query is valid on the INTO clause of an ISERT Statement 

B: a sub query can be used in the FROM clause of a SELECT statement 

D: a sub query can be used in the WHERE clause of a SELECT statement, 

E: a sub query can be used in the SET clauses of an UPDATE statement, 

Incorrect Answer: 

Csub query cannot be used 

F: is incorrect. 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 6-5 


Q143. - (Topic 2) 

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 output. 

B. It executes successfully but does not give the correct output. 

C. It generates an error because the REPLACE function is not valid. 

D. It generates an error because the SUBSTR function cannot be nested in the CONCAT function. 

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 


1Z0-051  download

Replace 1z0-051 pdf free download:

Q144. - (Topic 2) 

Examine the data in the CUSTOMERS table: 


You want to list all cities that have more than one customer along with the customer details. Evaluate the following query: 

SQL>SELECT c1.custname, c1.city FROM Customers c1 __________________ Customers c2 ON (c1.city=c2.city AND c1.custname<>c2.custname); 

Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.) 

A. JOIN 

B. NATURAL JOIN 

C. LEFT OUTER JOIN 

D. FULL OUTER JOIN 

E. RIGHT OUTER JOIN 

Answer: A,E 


Q145. - (Topic 1) 

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: A,C,E 

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. 


Q146. - (Topic 2) 

Which three statements are true regarding subqueries? (Choose three.) 

A. Subqueries can contain GROUP BY and ORDER BY clauses. 

B. Main query and subquery can get data from different tables. 

C. Main query and subquery must get data from the same tables. 

D. Subqueries can contain ORDER BY but not the GROUP BY clause. 

E. Only one column or expression can be compared between the main query and subquery. 

F. Multiple columns or expressions can be compared between the main query and subquery. 

Answer: A,B,F 

Explanation: 

SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING 

clauses of a query. 

A subquery can have any of the usual clauses for selection and projection. The following 

are required clauses: 

A SELECT list 

A FROM clause 

The following are optional clauses: WHERE GROUP BY HAVING 

The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent. 


1Z0-051  download

Free 1z0-051 books:

Q147. - (Topic 2) 

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 output. 

B. It executes successfully but does not give the correct output. 

C. It generates an error because the subquery does not have a GROUP BY clause. 

D. It generates an error because = is not valid and should be replaced by the IN operator. 

Answer: C 


Q148. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements: 


Which statement is true regarding the above two SQL statements? 


A. statement 1 gives an error, statement 2 executes successfully 

B. statement 2 gives an error, statement 1 executes successfully 

C. statement 1 and statement 2 execute successfully and give the same output 

D. statement 1 and statement 2 execute successfully and give a different output 

Answer: D 


Q149. - (Topic 1) 

See the Exhibit and examine the structure of ORD table: Exhibit: 


Evaluate the following SQL statements that are executed in a user session in the specified order: 

CREATE SEQUENCE ord_seq; 

SELECT ord_seq.nextval 

FROM dual; 

INSERT INTO ord 

VALUES (ord_seq.CURRVAL, ’25-jan-2007,101); 

UPDATE ord 

SET ord_no= ord_seq.NEXTVAL 

WHERE cust_id =101; 

What would be the outcome of the above statements? 

A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101. 

B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified. 

C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified. 

D. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20. 

Answer: A 


Q150. - (Topic 1) 

Which is a valid CREATE TABLE statement? 

A. CREATE TABLE EMP9$# AS (empid number(2)); 

B. CREATE TABLE EMP*123 AS (empid number(2)); 

C. CREATE TABLE PACKAGE AS (packid number(2)); 

D. CREATE TABLE 1EMP_TEST AS (empid number(2)); 

Answer: A 

Explanation: Table names and column names must begin with a letter and be 1-30 

characters long. Characters A-Z,a-z, 0-9, _, $ and # (legal characters but their use is 

discouraged). 

Incorrect Answer: 

BNon alphanumeric character such as “*” is discourage in Oracle table name. 

DTable name must begin with a letter. 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-4 



see more 1Z0-051 dumps