[Jan 2021] 1z0 051 dumps

Exam Code: 1z0 051 practice test (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database: SQL Fundamentals I
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass oracle 1z0 051 Exam.

♥♥ 2021 NEW RECOMMEND ♥♥

Free VCE & PDF File for Oracle 1Z0-051 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 1Z0-051 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/1Z0-051-exam-dumps.html

Q91. - (Topic 1)

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

Examine the structure of PRODUCTS table.

Using the PRODUCTS table, you issue the following query to generate the names, current list price and discounted list price for all those products whose list price fails below $10 after a discount of 25% is applied on it.

Exhibit:

The query generates an error.

What is the reason of generating error?

A. The column alias should be put in uppercase and enclosed within double quotation marks in the WHERE clause

B. The parenthesis should be added to enclose the entire expression

C. The column alias should be replaced with the expression in the WHERE clause

D. The double quotation marks should be removed from the column alias

Answer: C

Explanation: Note: You cannot use column alias in the WHERE clause.

Q92. - (Topic 1)

View the Exhibit and examine the description for the PRODUCTS and SALES table.

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years. Which is the valid DELETE statement?

DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE time_id - 3*365 = SYSDATE );

DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE SYSDATE >= time_id - 3*365 );

DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE SYSDATE - 3*365 >= time_id);

DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE time_id >= SYSDATE - 3*365 );

Answer: C

Q93. - (Topic 2)

You own a table called EMPLOYEES with this table structure:

EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE What happens when you execute this DELETE statement?

DELETE employees;

A. You get an error because of a primary key violation.

B. The data and structure of the EMPLOYEES table are deleted.

C. The data in the EMPLOYEES table is deleted but not the structure.

D. You get an error because the statement is not syntactically correct.

Answer: C

Explanation: Explanation: You can remove existing rows from a table by using the DELETE statement. DELETE [FROM] table [WHEREcondition]; Incorrect Answer: AStatement will not cause error BDelete statement will not delete the table structure DStatement will not cause error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-19

Q94. - (Topic 2)

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

B. It allows the MANAGER to pass the specified privileges on to other users.

C. It allows the MANAGER to create tables that refer to the STUDENT_GRADES table.

D. It allows the MANAGER to apply all DML statements on the STUDENT_GRADES table.

E. It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES table.

F. It allows the MANAGER the ability to select from, delete from, and update the STUDENT_GRADES table.

Answer: B,E

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

Q95. - (Topic 2)

Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;

SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;

SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL;

SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;

E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;

Answer: A,D,E

Q96. - (Topic 1)

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

You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:

The promo name should not begin with 'T' or 'N'.

The promo should cost more than $20000.

The promo should have ended after 1st January 2001.

Which WHERE clause would give the required result?

WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'

WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'

WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'

WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')

Answer: C

Q97. - (Topic 2)

Examine the structure of the ORDERS table:

You want to find the total value of all the orders for each year and issue the following command:

SQL>SELECT TO_CHAR(order_date,'rr'), SUM(order_total)

FROM orders

GROUP BY TO_CHAR(order_date,'yyyy');

Which statement is true regarding the outcome?

A. It executes successfully and gives the correct output.

B. It gives an error because the TO_CHAR function is not valid.

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

D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Answer: D

Q98. - (Topic 2)

View the Exhibit; e xamine the structure of the PROMOTIONS table.

Each promotion has a duration of at least seven days.

Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to l date.

Which query would achieve the required result?

A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;

B. SELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;

C. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;

D. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions;

Answer: D

Q99. - (Topic 2)

Which statements are true regarding single row functions? (Choose all that apply.)

A. MOD : returns the quotient of a division

B. TRUNC : can be used with NUMBER and DATE values

C. CONCAT : can be used to combine any number of values

D. SYSDATE : returns the database server current date and time

E. INSTR : can be used to find only the first occurrence of a character in a string

F. TRIM : can be used to remove all the occurrences of a character from a string

Answer: B,D

Explanation:

ROUND: Rounds value to a specified decimal TRUNC: Truncates value to a specified decimal MOD: Returns remainder of division SYSDATE is a date function that returns the current database server date and time.

Date-Manipulation Functions

Date functions operate on Oracle dates. All date functions return a value of the DATE data type except MONTHS_BETWEEN, which returns a numeric value. MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month. ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative. NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string. LAST_DAY(date): Finds the date of the last day of the month that contains date The above list is a subset of the available date functions. ROUND and TRUNC number functions can also be used to manipulate the date values as shown below: ROUND(date[,'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day. TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

The CONCAT Function

The CONCAT function joins two character literals, columns, or expressions to yield one larger character expression. Numeric and date literals are implicitly cast as characters when they occur as parameters to the CONCAT function. Numeric or date expressions are evaluated before being converted to strings ready to be concatenated. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or expressions resulting in character values. The INSTR(source string, search item, [start position],[nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins: instr('http://www.domain.com','.',1,2) = 18 The TRIM function literally trims off leading or trailing (or both) character strings from a given source string:

Q100. - (Topic 1)

Which CREATE TABLE statement is valid?

CREATE TABLE ord_details (ord_no NUMBER(2) PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY, ord_date DATE NOT NULL);

CREATE TABLE ord_details

(ord_no NUMBER(2) UNIQUE, NOT NULL,

item_no NUMBER(3),

ord_date DATE DEFAULT SYSDATE NOT NULL);

CREATE TABLE ord_details

(ord_no NUMBER(2) ,

item_no NUMBER(3),

ord_date DATE DEFAULT NOT NULL,

CONSTRAINT ord_uq UNIQUE (ord_no),

CONSTRAINT ord_pk PRIMARY KEY (ord_no));

CREATE TABLE ord_details

(ord_no NUMBER(2),

item_no NUMBER(3),

ord_date DATE DEFAULT SYSDATE NOT NULL,

CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

Answer: D

Explanation:

PRIMARY KEY Constraint

A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value. Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns.

To see all sample questions go to: 1Z0-051 test prep

Related 1Z0-051 posts: