Pinpoint 1Z0-051 Exam Dumps 2021

NEW QUESTION 1
You are granted the CREATE VIEW privilege. What does this allow you to do?

• A. Create a table vie
• B. Create a view in any schem
• C. Create a view in your schem
• D. Create a sequence view in any schem
• E. Create a view that is accessible by everyon
• F. Create a view only of it is based on tables that you create

Answer: C

Explanation:
You can create a view in your own schema only if you are granted the CREATE VIEW
privilege.
Incorrect Answers
A:You can create a view in your own schema only.
B:You can create a view in your own schema only, not in any schema.
D:There is no sequence view in Oracle.
E:You cannot create a view that is accessible by everyone. You will need specially grant
SELECT privileges on this view for everyone.
F:You can create a view in your own schema, but not only for tables in your schema. You
can use object from other users schemas if you have privileges to retrieve data from them.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 292-301
Chapter 7: Creating Other Database Objects in Oracle

NEW QUESTION 2
You are currently located in Singapore and have connected to a remote database in
Chicago.
You issue the following command:
Exhibit:

PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.
What is the outcome?

• A. Number of days since the promo started based on the current Singapore data and tim
• B. An error because the ROUND function specified is invalid
• C. An error because the WHERE condition specified is invalid
• D. Number of days since the promo started based on the current Chicago data and time

Answer: D

NEW QUESTION 3
Which two statements are true regarding constraints? (Choose two.)

• A. A foreign key cannot contain NULL value
• B. A column with the UNIQUE constraint can contain NULL value
• C. A constraint is enforced only for the INSERT operation on a tabl
• D. A constraint can be disabled even if the constraint column contains dat
• E. All constraints can be defined at the column level as well as the table leve

Answer: BD

Explanation:
Including Constraints
.
Constraints enforce rules at the table level.
.
Constraints prevent the deletion of a table if there are dependencies.
.
The following constraint types are valid:
–
NOT NULL
–
UNIQUE
–
PRIMARY KEY
–
FOREIGN KEY
–
CHECK

NEW QUESTION 4
View the Exhibit and examine the data in the PROMOTIONS table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000.
Which SQL statement would you use?

• A. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category = 'post' AND promo_begin_date < '01-01-00';
• B. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';
• C. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';
• D. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';

Answer: D

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

• A. Subqueries can contain GROUP BY and ORDER BY clause
• B. Main query and subquery can get data from different table
• C. Main query and subquery must get data from the same table
• D. Subqueries can contain ORDER BY but not the GROUP BY claus
• E. Only one column or expression can be compared between the main query and subquer
• F. Multiple columns or expressions can be compared between the main query and subquer

Answer: ABF

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.

NEW QUESTION 6
View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.


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 7
Evaluate the following SQL statements: Exhibit:

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 8
View the Exhibit and examine the structure of the PRODUCTS table.

Which two tasks would require subqueries? (Choose two.)

• A. Display the minimum list price for each product statu
• B. Display all suppliers whose list price is less than 1000.
• C. Display the number of products whose list price is more than the average list pric
• D. Display the total number of products supplied by supplier 102 and have product status as 'obsolete'.
• E. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'.

Answer: CE

NEW QUESTION 9
View the Exhibit and examine the structure of the PRODUCT, COMPONENT, and PDT_COMP tables.

In PRODUCT table, PDTNO is the primary key.
In COMPONENT table, COMPNO is the primary key.
In PDT_COMP table, (PDTNO,COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in PRODUCT table and COMPNO is the foreign key referencing the COMPNO in COMPONENT table.
You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist.
Evaluate the following query:
SQL>SELECT pdtno,pdtname, compno,compname FROM product _____________ pdt_comp USING (pdtno) ____________ component USING(compno)
WHERE compname IS NOT NULL;
Which combination of joins used in the blanks in the above query gives the correct output?

• A. JOIN; JOIN
• B. FULL OUTER JOIN; FULL OUTER JOIN
• C. RIGHT OUTER JOIN; LEFT OUTER JOIN
• D. LEFT OUTER JOIN; RIGHT OUTER JOIN

Answer: C

NEW QUESTION 10
Examine the structure of the MARKS table:
Exhibit:

Which two statements would execute successfully? (Choose two.)

• A. SELECT student_name,subject1 FROM marks WHERE subject1 > AVG(subject1);
• B. SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE 'R%';
• C. SELECT SUM(subject1+subject2+subject3) FROM marks WHERE student_name IS NULL;
• D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) FROM marks WHERE subject1 > subject2;

Answer: CD

NEW QUESTION 11
See the Exhibit and examine the structure of the PROMOSTIONS table: Exhibit:

Which SQL statements are valid? (Choose all that apply.)

• A. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost, promo_cost * 0.25, 100) "Discount" FROM promotions;
• B. SELECT promo_id, DECODE(promo_cost, 10000, DECODE(promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost" FROM promotions;
• C. SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), NULL, promo_cost*.25, 'N/A') "Catcost" FROM promotions;
• D. SELECT promo_id, DECODE(promo_cost, >10000, 'High', <10000, 'Low') "Range" FROM promotions;

Answer: AB

Explanation:
The DECODE Function Although its name sounds mysterious, this function is straightforward. The DECODE function implements ifthen-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. The DECODE function takes at least three mandatory parameters, but can take many more. The syntax of the function is DECODE(expr1,comp1, iftrue1, [comp2,iftrue2...[ compN,iftrueN]], [iffalse]).

NEW QUESTION 12
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?

• A. DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE time_id - 3*365 = SYSDATE );
• B. DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE SYSDATE >= time_id - 3*365 );
• C. DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE SYSDATE - 3*365 >= time_id);
• D. DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE time_id >= SYSDATE - 3*365 );

Answer: C

NEW QUESTION 13
You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Which statement accomplishes this task?

• A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;
• B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;
• C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);
• D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;

Answer: B

Explanation: function MAX(column_name)
Incorrect Answer:
Ainvalid statement
Cinner query return more than one line
Dcolumn maxsal does not exists.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7

NEW QUESTION 14
Evaluate the SQL statement
DROP TABLE DEPT:
Which four statements are true of the SQL statement? (Choose four)

• A. You cannot roll back this statemen
• B. All pending transactions are committe
• C. All views based on the DEPT table are delete
• D. All indexes based on the DEPT table are droppe
• E. All data in the table is deleted, and the table structure is also delete
• F. All data in the table is deleted, but the structure of the table is retaine
• G. All synonyms based on the DEPT table are delete

Answer: ABDE

Explanation:
You cannot roll back DROP TABLE statement. All pending transactions related on this
table are committed. If the table is dropped, Oracle automatically drops any index, trigger
and constraint associated with the table as well. All data in the table is deleted, and the
table structure is also deleted.
Incorrect Answers
C:All views based on the DEPT table become invalid, but they are not deleted.
F:All data in the table is deleted, and the table structure is also deleted. Command
TRUNCATE deletes all data in the table, but does not delete the structure of the table.
G:All synonyms based on the DEPT table are not deleted after dropping the table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 225 Chapter 5: Creating Oracle Database Objects

NEW QUESTION 15
You need to design a student registration database that contains several tables storing academic information.
The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.
You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?

• A. CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));
• B. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
• C. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));
• D. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

Answer: D

Explanation: CONSTRAINT name FOREIGN KEY (column_name) REFERENCES table_name (column_name);
Incorrect Answer: Ainvalid syntax Binvalid syntax Cinvalid syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-14

NEW QUESTION 16
Which statement is true regarding the INTERSECT operator?

• A. It ignores NULL values
• B. The number of columns and data types must be identical for all SELECT statements in the query
• C. The names of columns in all SELECT statements must be identical
• D. Reversing the order of the intersected tables the result

Answer: B

Explanation:
INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and
removing duplicates.
The columns in the queries that make up a compound query can have different names, but
the output result set will use the names of the columns in the first query.

NEW QUESTION 17
What does the FORCE option for creating a view do?

• A. creates a view with constraints
• B. creates a view even if the underlying parent table has constraints
• C. creates a view in another schema even if you don't have privileges
• D. creates a view regardless of whether or not the base tables exist

Answer: D

Explanation:
create a view regardless of whether or not the base tables exist.
Incorrect Answer: Athe option is not valid Bthe option is not valid Cthe option is not valid
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-3

NEW QUESTION 18
Examine the structure of the TRANSACTIONS table:
Name Null Type
TRANS_ID NOT NULL NUMBER(3)
CUST_NAME VARCHAR2(30) TRANS_DATE DATE TRANS_AMT NUMBER(10,2)
You want to display the transaction date and specify whether it is a weekday or weekend. Evaluate the following two queries:

Which statement is true regarding the above queries?

• A. Both give wrong result
• B. Both give the correct resul
• C. Only the first query gives the correct resul
• D. Only the second query gives the correct resul

Answer: C

Explanation:
Range Conditions Using the BETWEEN Operator Use the BETWEEN operator to display rows based on a range of values: SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500; Range Conditions Using the BETWEEN Operator You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower limit and an upper limit. The SELECT statement in the slide returns rows from the EMPLOYEES table for any employee whose salary is between $2,500 and $3,500. Values that are specified with the BETWEEN operator are inclusive. However, you must specify the lower limit first. You can also use the BETWEEN operator on character values: SELECT last_name FROM employees WHERE last_name BETWEEN 'King' AND 'Smith';

NEW QUESTION 19
Evaluate the following SQL statements: Exhibit:

Which is the correct output of the above query?

• A. +00-300, +54-02,+00 11:12:10.123457
• B. +00-300,+00-650,+00 11:12:10.123457
• C. +25-00, +54-02, +00 11:12:10.123457
• D. +25-00,+00-650,+00 11:12:10.123457

Answer: C

NEW QUESTION 20
View the Exhibit and examine the data in the PROJ_TASK_DETAILS table.

The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them.
The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks.
You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on.
Which query would give the required result?

• A. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.based_on = d.task_id);
• B. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);
• C. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);
• D. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.task_id = d.task_id);

Answer: B