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

Exam Code: 1Z0-051 (1z0 051 latest dumps free download pdf), Exam Name: Oracle Database: SQL Fundamentals I, Certification Provider: Oracle Certifitcation, Free Today! Guaranteed Training- Pass 1Z0-051 Exam.

Online 1Z0-051 free questions and answers of New Version:

NEW QUESTION 1
You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement:
1) The order ID will be unique and cannot have null values.
2) The order date cannot have null values and the default should be the current date.
3) The order amount should not be less than 50.
4) The order status will have values either shipped or not shipped.
5) The order payment mode should be cheque, credit card, or cash on delivery (COD).
Which is the valid DDL statement for creating the ORD_DETAIL table?

  • A. CREATE TABLE ord_details (ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL, ord_date DATE DEFAULT SYSDATE NOT NULL, ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount > 50), ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN ('Shipped', 'Not Shipped')), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 'Cash On Delivery')));
  • B. CREATE TABLE ord_details (ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL, ord_date DATE DEFAULT SYSDATE NOT NULL, ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount > 50), ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN ('Shipped', 'Not Shipped')), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 'Cash On Delivery')));
  • C. CREATE TABLE ord_details (ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY, ord_date DATE DEFAULT SYSDATE NOT NULL, ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount >= 50), ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN ('Shipped', 'Not Shipped')), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 'Cash On Delivery')));
  • D. CREATE TABLE ord_details (ord_id NUMBER(2), ord_date DATE NOT NULL DEFAULT SYSDATE, ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount >= 50), ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN ('Shipped', 'Not Shipped')), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 'Cash On Delivery')));

Answer: C

NEW QUESTION 2
Which SQL statement returns a numeric value?

  • A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
  • B. SELECT ROUND(hire_date) FROM EMP;
  • C. SELECT sysdate-hire_date FROM EMP;
  • D. SELECT TO_NUMBER(hire_date + 7) FROM EMP;

Answer: C

Explanation:
DATE value subtract DATE value will return numeric value.
Incorrect Answer: Adoes not return numeric value Bdoes not return numeric value Ddoes not return numeric value
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-13

NEW QUESTION 3
Examine the statement:
Create synonym emp for hr.employees;
What happens when you issue the statement?

  • A. An error is generate
  • B. You will have two identical tables in the HR schema with different name
  • C. You create a table called employees in the HR schema based on you EMP tabl
  • D. You create an alternative name for the employees table in the HR schema in your own schem

Answer: D

NEW QUESTION 4
Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result?

  • A. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;
  • B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;
  • C. SELECT DISTINCT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;
  • D. SELECT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;

Answer: C

Explanation: Duplicate Rows Unless you indicate otherwise, SQL displays the results of a query without eliminating the duplicate rows. To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword. You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns.

NEW QUESTION 5
You work as a database administrator at ABC.com. You study the exhibit carefully and examine the structure of CUSTOMRS AND SALES tables.
1Z0-051 dumps exhibit
Evaluate the following SQL statement: Exhibit:
1Z0-051 dumps exhibit
Which statement is true regarding the execution of the above UPDATE statement?

  • A. It would execute and restrict modifications to only the column specified in the SELECT statement
  • B. It would not execute because two tables cannot be used in a single UPDATE statement
  • C. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement
  • D. It would not execute because the SELECT statement cannot be used in place of the table name

Answer: A

NEW QUESTION 6
Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?

  • A. SELECT last_name, department_name, location_id FROM employees , departments ;
  • B. SELECT employees.last_name, departments.department_name, departments.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
  • C. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id =manager_id;
  • D. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;

Answer: D

Explanation:
Equijoins are also called simple joins or inner joins. Equijoin involve primary key and foreign key.
Incorrect Answer: Athere is no join B invalid syntax Cdoes not involve the join in the primary and foreign key
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-8

NEW QUESTION 7
Which describes the default behavior when you create a table?

  • A. The table is accessible to all user
  • B. Tables are created in the public schem
  • C. Tables are created in your schem
  • D. Tables are created in the DBA schem
  • E. You must specify the schema when the table is create

Answer: C

Explanation:
sorted by highest to lowest is DESCENDING order
Incorrect Answer: Agrant the table privilege to PUBLIC Blogin as sysoper Dlogin as DBA or sysdba Eno such option is allow.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-9

NEW QUESTION 8
You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be used in the
SELECT statement to get the required output?

  • A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
  • B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\'
  • C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
  • D. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_'

Answer: B

Explanation:
A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character. The second example in Figure 3-12 shows the SQL statement that retrieves the JOBS table records with JOB_ID values equal to SA_MAN and SA_REP and which conforms to the original requirement: select job_id from jobs where job_id like 'SA\_%' escape '\';

NEW QUESTION 9
Examine the data in the ORD_ITEMS table:
ORD_NO ITEM_NO QTY
1 111 10
1 222 20
1 333 30
2 333 30
2 444 40
3 111 40
Evaluate the following query:
SQL>SELECT item_no, AVG(qty)
FROM ord_items
HAVING AVG(qty) > MIN(qty) * 2
GROUP BY item_no;
Which statement is true regarding the outcome of the above query?

  • A. It gives an error because the HAVING clause should be specified after the GROUP BY claus
  • B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT lis
  • C. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the tabl
  • D. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the tabl

Answer: C

NEW QUESTION 10
View the Exhibits and examine PRODUCTS and SALES tables.
1Z0-051 dumps exhibit
You issue the following query to display product name and the number of times the product has been sold:
SQL>SELECT p.prod_name, i.item_cnt FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i RIGHT OUTER JOIN products p
ON i.prod_id = p.prod_id;
What happens when the above statement is executed?

  • A. The statement executes successfully and produces the required outpu
  • B. The statement produces an error because ITEM_CNT cannot be displayed in the outer quer
  • C. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used togethe
  • D. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM claus

Answer: A

NEW QUESTION 11
You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns:
CUST_IDNUMBER(4)NOT NULL CUST_NAMEVARCHAR2(100)NOT NULL CUST_ADDRESSVARCHAR2(150)
CUST_PHONEVARCHAR2(20)
Which SELECT statement accomplishes this task?

  • A. SELECT * FROM customers
  • B. SELECT name, address FROM customers;
  • C. SELECT id, name, address, phone FROM customers;
  • D. SELECT cust_name, cust_address FROM customers;
  • E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;

Answer: D

Explanation:
This answer provides correct list of columns for the output.
Incorrect Answers
A:This answer does not provide correct list of columns for the output. It is not required to
show all columns of the table. Symbol “*” is used in the SELECT command to substitute a
list of all columns of the table.
B:This answer does not provide correct list of columns for the output. There are not NAME
and ADDRESS columns in the CUSTOMERS table.
C:This answer does not provide correct list of columns for the output. There are not ID,
NAME, ADDRESS or PHONE columns in the CUSTOMERS table.
E:This answer does not provide correct list of columns for the output. It is not required to
show all columns of the table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 20-24
Chapter 1: Overview of Oracle Databases

NEW QUESTION 12
You work as a database administrator at ABC.com. You study the exhibit carefully.
1Z0-051 dumps exhibit
Exhibit:
Which two SQL statements would execute successfully? (Choose two.)

  • A. UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000';
  • B. SELECT promo_begin_date FROM promotions WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';
  • C. UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8));
  • D. SELECT TO_CHAR(promo_begin_date,'dd/month') FROM promotions WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));

Answer: AB

NEW QUESTION 13
User Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She has the privilege to create a public synonym, and would like to create a synonym for this view that can be used by all users of the database.
Which SQL statement can Mary use to accomplish that task?

  • A. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu;
  • B. CREATE PUBLIC SYNONYM EDL:VU FOR mary (emp_dept_loc_vu);
  • C. CREATE PUBLIC SYNONYM EDL_VU FOR emp_dept_loc_vu;
  • D. CREATE SYNONYM EDL_VU ON emp_dept_loc_vu FOR EACH USER;
  • E. CREATE SYNONYM EDL_VU FOR EACH USER ON emp_dept_loc_vu;
  • F. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu FOR ALL USERS;

Answer: C

Explanation:
The general syntax to create a synonym is:
CREATE [PUBLIC] SYNONYM synonym FOR object;

NEW QUESTION 14
View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.
1Z0-051 dumps exhibit
1Z0-051 dumps exhibit
Evaluate the following SQL statements:
Which statement is true regarding the output of the above query?

  • A. It gives details of product IDs that have been sold irrespective of whether they had a promo or not
  • B. It gives the details of promos for which there have been no sales
  • C. It gives the details of promos for which there have been sales
  • D. It gives details of all promos irrespective of whether they have resulted in a sale or not

Answer: D

NEW QUESTION 15
What is true about the WITH GRANT OPTION clause?

  • A. It allows a grantee DBA privilege
  • B. It is required syntax for object privilege
  • C. It allows privileges on specified columns of table
  • D. It is used to grant an object privilege on a foreign key colum
  • E. It allows the grantee to grant object privileges to other users and role

Answer: AE

Explanation: The GRANT command with the WITH GRANT OPTION clause allows the grantee to grant
object privileges to other users and roles.
Incorrect Answers
A:The WITH GRANT OPTION does not allow a grantee DBA privileges.
B:It is not required syntax for object privileges. It is optional clause of GRANT command.
C:GRANT command does not allows privileges on columns of tables.
D:It is not used to grant an object privilege on a foreign key column.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access in Oracle

NEW QUESTION 16
View the exhibit and examine the description for the SALES and CHANNELS tables.
1Z0-051 dumps exhibit
You issued the following SQL statement to insert a row in the SALES table:
INSERT INTO sales VALUES (23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel_desc='Direct Sales'), 12, 1, 500);
Which statement is true regarding the execution of the above statement?

  • A. The statement will execute and the new row will be inserted in the SALES tabl
  • B. The statement will fail because subquery cannot be used in the VALUES claus
  • C. The statement will fail because the VALUES clause is not required with subquer
  • D. The statement will fail because subquery in the VALUES clause is not enclosed with in single quotation mark

Answer: A

NEW QUESTION 17
In the CUSTOMERS table, the CUST_CITY column contains the value 'Paris' for the
CUST_FIRST_NAME 'ABIGAIL'.
Evaluate the following query:
1Z0-051 dumps exhibit
What would be the outcome?

  • A. Abigail PA
  • B. Abigail Pa
  • C. Abigail IS
  • D. an error message

Answer: B

P.S. Easily pass 1Z0-051 Exam with 292 Q&As 2passeasy Dumps & pdf Version, Welcome to Download the Newest 2passeasy 1Z0-051 Dumps: https://www.2passeasy.com/dumps/1Z0-051/ (292 New Questions)