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

2016 Jun 1Z0-051 Study Guide Questions:

Q191. - (Topic 2) 

Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME 

Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer 

You need to display customers' second names where the second name starts with "Mc" or "MC." 

Which query gives the required output? 

A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) 

FROM customers 

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc' 

B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) 

FROM customers 

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%' 

C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) 

FROM customers 

WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%'); 

D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) 

FROM customers 

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%'); 

Answer: B 


Q192. - (Topic 1) 

Which is the valid CREATE [TABLE statement? 

A. CREATE TABLE emp9$# (emp_no NUMBER(4)); 

B. CREATE TABLE 9emp$# (emp_no NUMBER(4)); 

C. CREATE TABLE emp*123 (emp_no NUMBER(4)); 

D. CREATE TABLE emp9$# (emp_no NUMBER(4). date DATE); 

Answer: A 

Explanation: 

Schema Object Naming Rules 

Every database object has a name. In a SQL statement, you represent the name of an 

object with a quoted identifier or a nonquoted identifier. 

A quoted identifier begins and ends with double quotation marks ("). If you name a schema 

object using a quoted identifier, then you must use the double quotation marks whenever 

you refer to that object. 

A nonquoted identifier is not surrounded by any punctuation. 

The following list of rules applies to both quoted and nonquoted identifiers unless otherwise 

indicated: 

Names must be from 1 to 30 bytes long with these exceptions: 

Names of databases are limited to 8 bytes. 

Names of database links can be as long as 128 bytes. 

If an identifier includes multiple parts separated by periods, then each attribute can be up to 

30 bytes long. 

Each period separator, as well as any surrounding double quotation marks, counts as one 

byte. For example, suppose you identify a column like this: 

"schema"."table"."column" 

Nonquoted identifiers cannot be Oracle Database reserved words (ANSWER D). Quoted identifiers can be reserved words, although this is not recommended. Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words. The Oracle SQL language contains other words that have special meanings. These words include datatypes, schema names, function names, the dummy system table DUAL, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally in specific ways. Therefore, if you use these words as names for objects and object parts, then your SQL statements may be more difficult to read and may lead to unpredictable results. In particular, do not use words beginning with SYS_ as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions. You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems. Nonquoted identifiers must begin with an alphabetic character (ANSWER B - begins with 9) from your database character set. Quoted identifiers can begin with any character. Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers. Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0). Within a namespace, no two objects can have the same name. Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive. By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace: employees "employees" "Employees" "EMPLOYEES" 

Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace: employees EMPLOYEES "EMPLOYEES" Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name. Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function. 


Q193. - (Topic 2) 

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

A. A subquery can retrieve zero or more rows. 

B. Only two subqueries can be placed at one level. 

C. A subquery can be used only in SQL query statements. 

D. A subquery can appear on either side of a comparison operator. 

E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement. 

Answer: A,D 

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. 


1Z0-051  test

Most recent oracle 1z0-051 exam questions:

Q194. - (Topic 1) 

Exhibit contains the structure of PRODUCTS table: 


Evaluate the following query: 


What would be the outcome of executing the above SQL statement? 

A. It produces an error 

B. It shows the names of products whose list price is the second highest in the table. 

C. It shown the names of all products whose list price is less than the maximum list price 

D. It shows the names of all products in the table 

Answer: B 


Q195. - (Topic 1) 

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: A,B,D 


Q196. - (Topic 1) 

Examine the structure and data of the CUSTJTRANS table: 

CUSTJRANS 

Name Null? Type 

CUSTNO NOT NULL CHAR(2) TRANSDATE DATE TRANSAMT NUMBER(6.2) CUSTNO TRANSDATE TRANSAMT 

11 01-JAN-07 1000 

22 01-FEB-07 2000 

33 01-MAR-07 3000 

Dates are stored in the default date format dd-mon-rr in the CUSTJTRANS table. Which three SQL statements would execute successfully? (Choose three.) 

A. SELECT transdate + '10' FROM custjrans; 

B. SELECT * FROM custjrans WHERE transdate = '01-01-07': 

C. SELECT transamt FROM custjrans WHERE custno > '11': 

D. SELECT * FROM custjrans WHERE transdate='01-JANUARY-07': 

E. SELECT custno - 'A' FROM custjrans WHERE transamt > 2000: 

Answer: A,C,D 


2passeasy.com

Approved oracle 1z0-051 dumps free download:

Q197. - (Topic 1) 

Which two statements are true regarding the USING clause in table joins?(Choose two.) 

A. It can be used to join a maximum of three tables. 

B. It can be used to restrict the number of columns used in a NATURAL join. 

C. It can be used to access data from tables through equijoins as well as nonequijoins. 

D. It can be used to join tables that have columns with the same name and compatible data types. 

Answer: B,D 

Explanation: 

NATURAL JOIN operation A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order): 

All the common columns 

Every column in the first (left) table that is not a common column 

Every column in the second (right) table that is not a common column 

An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to 

every column of that table that is not a common column. 

If a common column is referenced without being qualified by a table name, the column 

reference points to the column in the first (left) table if the join is an INNER JOIN or a LEFT 

OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a common column 

point to the column in the second (right) table. 

Syntax 

TableExpression NATURAL [ { LEFT | RIGHT } [ OUTER ] | INNER ] JOIN { 

TableViewOrFunctionExpression | 

( TableExpression ) } 

Examples 

If the tables COUNTRIES and CITIES have two common columns named COUNTRY and 

COUNTRY_ISO_CODE, the following two SELECT statements are equivalent: 

SELECT * FROM COUNTRIES NATURAL JOIN CITIES 

SELECT * FROM COUNTRIES JOIN CITIES 

USING (COUNTRY, COUNTRY_ISO_CODE) 


Q198. - (Topic 2) 

View the Exhibit and examine the structure of the CUSTOMERS table. 

In the CUSTOMERS table, the CUST_LAST_NAME column contains the values 'Anderson' and 'Ausson'. 

You issue the following query: 

SQL> SELECT LOWER(REPLACE(TRIM('son' FROM cust_last_name),'An','O')) 

FROM CUSTOMERS 

WHERE LOWER(cust_last_name) LIKE 'a%n' 

What would be the outcome? 

A. 'Oder' and 'Aus' 

B. an error because the TRIM function specified is not valid 

C. an error because the LOWER function specified is not valid 

D. an error because the REPLACE function specified is not valid 

Answer: B 

Explanation: 

Function Purpose ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.) TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero The TRIM Function The TRIM function removes characters from the beginning or end of character literals, columns or expressions to yield one potentially shorter character item. Numeric and date literals are automatically cast as characters when they occur as parameters to the TRIM function. Numeric or date expressions are evaluated first before being converted to strings ready to be trimmed. The TRIM function takes a parameter made up of an optional and a mandatory component. Its syntax is TRIM ([trailing|leading|both] trimstring from s). The string to be trimmed (s) is mandatory. The following points list the rules governing the use of this function: 

TRIM(s) removes spaces from both sides of the input string. 

TRIM(trailing trimstring from s) removes all occurrences of trimstring from the end of the string s if it is present. TRIM(leading trimstring from s) removes all occurrences of trimstring from the beginning of the string s if it is present. 

TRIM(both trimstring from s) removes all occurrences of trimstring from the beginning and 

end of the string s if it is present. 

The following queries illustrate the usage of this function: 

Query 1: select trim(trailing 'e' from 1+2.14||' is pie') from dual 

Query 2: select trim(both '*' from '*******Hidden*******') from dual 

Query 3: select trim(1 from sysdate) from dual 

ORA-30001: trim set should have only one character 

30001. 00000 - "trim set should have only one character" 

*Cause: Trim set contains more or less than 1 character. This is not allowed in TRIM 

function. 

REPLACE(text, search_string, replacement_string) 

Searches a text expression for a character string and, if found, replaces it with a specified 

replacement string 


Q199. - (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 


Q200. - (Topic 1) 

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: A,D