1Z0-051 | Accurate 1Z0-051 Exam Dumps 2019

We provide 1z0 051 pdf in two formats. Download PDF & Practice Tests. Pass Oracle 1Z0-051 Exam quickly & easily. The 1Z0-051 PDF type is available for reading and printing. You can print more and practice many times. With the help of our oracle 1z0 051 product and material, you can easily pass the 1Z0-051 exam.

Online Oracle 1Z0-051 free dumps demo Below:

NEW QUESTION 1
View the Exhibit and examine the structure of the SALES and PRODUCTS tables.
1Z0-051 dumps exhibit
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
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

  • A. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
  • B. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
  • C. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
  • D. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;
  • E. SELECT TO_CHAR(1890.55,'$9,999D99') FROM DUAL;

Answer: ACD

NEW QUESTION 3
Examine these statements:
CREATE ROLE registrar;
GRANT UPDATE ON student_grades TO registrar;
GRANT registrar to user1, user2, user3;
What does this set of SQL statements do?

  • A. The set of statements contains an error and does not wor
  • B. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three user
  • C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three user
  • D. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the rol
  • E. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES objec
  • F. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registra

Answer: C

Explanation: the statement will create a role call REGISTRAR, grant UPDATE on student_grades to registrar, grant the role to user1,user2 and user3.
Incorrect Answer: Athe statement does not contain error Bthere is no MODIFY privilege Dstatement does not create 3 users with the role Eprivilege is grant to role then grant to user Fprivilege is grant to role then grant to user

NEW QUESTION 4
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements insert a row into the table? (Choose three.)

  • A. INSERT INTO employees VALUES ( NULL, 'John', 'Smith');
  • B. INSERT INTO employees( first_name, last_name) VALUES( 'John', 'Smith');
  • C. INSERT INTO employees VALUES ( 1000, 'John', NULL);
  • D. INSERT INTO employees (first_name, last_name, employee_id) VALUES ( 1000, 'John', 'Smith');
  • E. INSERT INTO employees (employee_id) VALUES (1000);
  • F. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'John', ' ');

Answer: CEF

Explanation: EMPLOYEE_ID is a primary key. Incorrect Answer: AEMPLOYEE_ID cannot be null BEMPLOYEE_ID cannot be null Dmismatch of field_name with datatype
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-11

NEW QUESTION 5
The SQL statements executed in a user session as follows: Exhibit:
1Z0-051 dumps exhibit
Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)

  • A. Both the DELETE statements and the UPDATE statement are rolled back
  • B. The rollback generates an error
  • C. Only the DELETE statements are rolled back
  • D. Only the seconds DELETE statement is rolled back
  • E. No SQL statements are rolled back

Answer: BE

NEW QUESTION 6
You need to create a table with the following column specifications:
1.
Employee ID (numeric data type) for each employee
2.
Employee Name (character data type) that stores the employee name
3.
Hire date, which stores the date of joining the organization for each employee
4.
Status (character data type), that contains the value 'ACTIVE' if no data is entered
5.
Resume (character large object [CLOB] data type), which contains the resume submitted by the employee
Which is the correct syntax to create this table?

  • A. CREATE TABLE EMP_1 (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, e_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB(200));
  • B. CREATE TABLE 1_EMP (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB);
  • C. CREATE TABLE EMP_1 (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT "ACTIVE", resume CLOB);
  • D. CREATE TABLE EMP_1 (emp_id NUMBER, emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB);

Answer: D

Explanation:
CLOB Character data (up to 4 GB)
NUMBER [(p,s)] Number having precision p and scale s (Precision is the total number of
decimal digits and scale is the number of digits to the right of the decimal point; precision
can range from 1 to 38, and scale can range from –84 to 127.)

NEW QUESTION 7
Evaluate the following SQL statements: Exhibit:
1Z0-051 dumps exhibit
You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization.
Exhibit:
1Z0-051 dumps exhibit
Which two statements are true regarding the above view? (Choose two.)

  • A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table
  • B. It allows you to delete details of the existing sales staff from the EMPLOYEES table
  • C. It allows you to insert rows into the EMPLOYEES table
  • D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements

Answer: BD

NEW QUESTION 8
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city ='Singapore');
Which statement is true regarding the above query if one of the values generated by the subquery is NULL?

  • A. It produces an erro
  • B. It executes but returns no row
  • C. It generates output for NULL as well as the other values produced by the subquer
  • D. It ignores the NULL value and generates output for the other values produced by the subquer

Answer: C

NEW QUESTION 9
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 10
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 table
  • B. It can be used to restrict the number of columns used in a NATURAL joi
  • C. It can be used to access data from tables through equijoins as well as nonequijoin
  • D. It can be used to join tables that have columns with the same name and compatible data type

Answer: BD

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)

NEW QUESTION 11
View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.
1Z0-051 dumps exhibit
You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007.
You issue the following query:
Which statement is true regarding the above query?

  • A. It executes successfully and gives the required resul
  • B. It executes successfully but does not give the required resul
  • C. It produces an error because the join order of the tables is incorrec
  • D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statemen

Answer: B

NEW QUESTION 12
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 13
Examine the structure of the SHIPMENTS table:
1Z0-051 dumps exhibit
You want to generate a report that displays the PO_ID and the penalty amount to be paid if
the
SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:
1Z0-051 dumps exhibit
Which statement is true regarding the above commands?

  • A. Both execute successfully and give correct result
  • B. Only the first query executes successfully but gives a wrong resul
  • C. Only the first query executes successfully and gives the correct resul
  • D. Only the second query executes successfully but gives a wrong resul
  • E. Only the second query executes successfully and gives the correct resul

Answer: C

Explanation:
The MONTHS_BETWEEN(date 1, date 2) function returns the number of months between two dates: months_between('01-FEB-2008','01-JAN-2008') = 1 The DECODE Function Although its name sounds mysterious, this function is straightforward. The DECODE function implements if then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. DECODE Function Facilitates conditional inquiries by doing the work of a CASE expression or an IF-THENELSE statement: DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]) DECODE Function The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic that is used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned.
If the default value is omitted, a null value is returned where a search value does not match any of the result values.

NEW QUESTION 14
See the structure of the PROGRAMS table:
1Z0-051 dumps exhibit
Which two SQL statements would execute successfully? (Choose two.)

  • A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs;
  • B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM programs;
  • C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing') FROM programs;
  • D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing') FROM programs;

Answer: AD

Explanation:
NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number.
Data types must match:

NVL(commission_pct,0)

NVL(hire_date,'01-JAN-97')

NVL(job_id,'No Job Yet')
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. MONTHS_BETWEEN returns a numeric value. - answer C NVL has different datatypes -numeric and strings, which is not possible!
The data types of the original and if null parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert if null to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter.

NEW QUESTION 15
For which action can you use the TO_DATE function?

  • A. Convert any date literal to a date
  • B. Convert any numeric literal to a date
  • C. Convert any character literal to a date
  • D. Convert any date to a character literal
  • E. Format ’10-JAN-99’ to ‘January 10 1999’

Answer: C

NEW QUESTION 16
Evaluate the following SQL statements: Exhibit:
1Z0-051 dumps exhibit
Exhibit:
1Z0-051 dumps exhibit
The above command fails when executed. What could be the reason?

  • A. The BETWEEN clause cannot be used for the CHECK constraint
  • B. SYSDATE cannot be used with the CHECK constraint
  • C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY
  • D. The CHECK constraint cannot be placed on columns having the DATE data type

Answer: B

Explanation:
CHECK Constraint The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level. CREATE TABLE employees (... salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary > 0),

NEW QUESTION 17
You need to generate a list of all customer last names with their credit limits from the CUSTOMERS table. Those customers who do not have a credit limit should appear last in the list. Winch two queries would achieve the required result? (Choose two.)

  • A. SELECT cust_last_nam
  • B. cust_credit_limit FROM customers ORDER BY cust_credit_limit DESC:
  • C. SELECT cust_last_nam
  • D. cust_credit_limit FROM customers ORDER BY cust_credit_limit:
  • E. SELECT cust_last_nam
  • F. cust_credit_limit FROM customers ORDER BY cust_credit_limit NULLS LAST:
  • G. SELECT cust_last_nam
  • H. cust_credit_limit FROM customers ORDER BY cust_last_nam
  • I. cust_credit_limit NULLS LAST:

Answer: BC

Explanation:
If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order. Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence. ANSWER C Sorting The default sort order is ascending:
.
Numeric values are displayed with the lowest values first (for example, 1 to 999).
.
Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01-JAN-95).
.
Character values are displayed in the alphabetical order (for example, “A” first and “Z” last).
.
Null values are displayed last for ascending sequences and first for descending sequences.
-ANSWER B
. You can also sort by a column that is not in the SELECT list.

P.S. Certleader now are offering 100% pass ensure 1Z0-051 dumps! All 1Z0-051 exam questions have been updated with correct answers: https://www.certleader.com/1Z0-051-dumps.html (292 New Questions)