Oracle 1Z0-051 Braindumps 2021

NEW QUESTION 1
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 2
Examine the structure of the EMPLOYEES table:

Which INSERT statement is valid?

• A. INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ‘01/01/01’);
• B. INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ’01 January 01’);
• C. INSERT INTO employees(employee_id, first_name, last_name, Hire_date) VALUES ( 1000, ‘John’, ‘Smith’, To_date(‘01/01/01’));
• D. INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, 01-Jan-01);

Answer: D

Explanation: It is the only statement that has a valid date; all other will result in an error. Answer A is incorrect, syntax error, invalid date format

NEW QUESTION 3
You need to display the date ll-oct-2007 in words as "Eleventh of October, Two Thousand Seven'. Which SQL statement would give the required result?

• A. SELECT TO_CHAR('ll-oct-2007'. 'miDdspth "of Mont
• B. Year') FROM DUAL:
• C. SELECT TO_CHAR(TO_DATE('ll-oct-2007'X 'miDdspth of month, year') FROM DUAL;
• D. SELECT TO_CHAR(TO_DATE('ll-oct-2007'), 'miDdthsp "of* Mont
• E. Year') FROM DUAL;
• F. SELECT TO_DATE(TO_CHAR('ll-oct-20077fiiiDdspth "of" Mont
• G. Year')) FROM DUAL:

Answer: A

NEW QUESTION 4
Which two statements about creating constraints are true? (Choose two)

• A. Constraint names must start with SYS_
• B. All constraints must be defines at the column leve
• C. Constraints can be created after the table is create
• D. Constraints can be created at the same time the table is create
• E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary vie

Answer: CD

Explanation:
Constraints can be created after the table is created. Use ALTER TABLE command for
that.
Constraints can be created at the same time the table is created (CREATE TABLE
command).
Incorrect Answers
A:There is no requirements in Oracle that constraint names must start with SYS_C. Oracle
can use prefix “SYS” to build indexes for UNIQUE and NOT NULL constraints, but it is not
required for user to follow this naming rule.
B:Not all constraints must be defines at the column level. Only NOT NULL constraint must
be.
E:There is no VIEW_CONSTRAINTS dictionary view in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232
Chapter 5: Creating Oracle Database Objects

NEW QUESTION 5
View the Exhibit and examine the structure of the EMPLOYEES table.

Examine the data in the ENAME and HIREDATE columns of the EMPLOYEES table:
ENAME HIREDATE
SMITH 17-DEC-80 ALLEN 20-FEB-81 WARD 22-FEB-81
You want to generate a list of user IDs as follows: USERID
Smi17DEC80 All20FEB81 War22FEB81
You issue the following query:
SQL>SELECT CONCAT(SUBSTR(INITCAP(ename),1,3), REPLACE(hiredate,'-'))
"USERID"
FROM employees;
What is the outcome?

• A. It executes successfully and gives the correct outpu
• B. It executes successfully but does not give the correct outpu
• C. It generates an error because the REPLACE function is not vali
• D. It generates an error because the SUBSTR function cannot be nested in the CONCAT functio

Answer: A

Explanation:
REPLACE(text, search_string,replacement_string) Searches a text expression for a character string and, if found, replaces it with a specified replacement string The REPLACE Function The REPLACE function replaces all occurrences of a search item in a source string with a replacement term and returns the modified source string. If the length of the replacement term is different from that of the search item, then the lengths of the returned and source strings will be different. If the search string is not found, the source string is returned unchanged. Numeric and date literals and expressions are evaluated before being implicitly cast as characters when they occur as parameters to the REPLACE function. The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is REPLACE (source string, search item, [replacement term]). If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string. . The following queries illustrate the REPLACE function with numeric and date expressions: Query 1: select replace(10000-3,'9','85') from dual Query 2: select replace(sysdate, 'DEC','NOV') from dual

NEW QUESTION 6
When does a transaction complete? (Choose all that apply.)

• A. When a PL/SQL anonymous block is executed
• B. When a DELETE statement is executed
• C. When a data definition language statement is executed
• D. When a TRUNCATE statement is executed after the pending transaction
• E. When a ROLLBACK command is executed

Answer: CDE

NEW QUESTION 7
Which two statements complete a transaction? (Choose two)

• A. DELETE employees;
• B. DESCRIBE employees;
• C. ROLLBACK TO SAVEPOINT C;
• D. GRANT SELECT ON employees TO SCOTT;
• E. ALTER TABLE employeesSET UNUSED COLUMN sal;
• F. Select MAX(sal)FROM employeesWHERE department_id = 20;

Answer: DE

Explanation:
D: GRANT is a DML operation which will cause an implicit commit
E: It is important to understand that an implicit COMMIT occurs on the database when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a CREATE TABLE statement, used to create a database object, or an ALTER TABLE statement, used to alter a database object.
Incorrect Answers A:The DELETE command is data-manipulation language (DML) command and it does not complete a transaction. B:The DESCRIBE command is internal SQL*Plus command and it has nothing to do with completion a transaction.
C: ROLLBACK is not used to commit or complete a transaction, it is used to undo a transaction F:SELECT command is used to retrieve data. It does not complete a transaction.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 281-282 Chapter 3: Advanced Data Selection in Oracle

NEW QUESTION 8
View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.
Evaluate the following two queries:
SQL>SELECT DISTINCT promo_category to_char(promo_cost)"code"
FROM promotions
ORDER BY code;
SQL>SELECT DISTINCT promo_category promo_cost "code"
FROM promotions
ORDER BY 1;
Which statement is true regarding the execution of the above queries?

• A. Only the first query executes successfull
• B. Only the second query executes successfull
• C. Both queries execute successfully but give different result
• D. Both queries execute successfully and give the same resul

Answer: B

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

NEW QUESTION 9
Which statement is true regarding sub queries?

• A. The LIKE operator cannot be used with single- row subquerie
• B. The NOT IN operator is equivalent to IS NULL with single- row subquerie
• C. =ANY and =ALL operators have the same functionality in multiple- row subquerie
• D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subquerie

Answer: D

Explanation:
Using the ANY Operator in Multiple-Row Subqueries
The ANY operator (and its synonym, the SOME operator) compares a value to each value
returned by a subquery.
<ANY means less than the maximum.
>ANY means more than the minimum.
=ANY is equivalent to IN
Using the ALL Operator in Multiple-Row Subqueries
The ALL operator compares a value to every value returned by a subquery.
>ALL means more than the maximum and
<ALL means less than the minimum.
The NOT operator can be used with IN, ANY, and ALL operators.

NEW QUESTION 10
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 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 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 13
In which two cases would you use an outer join? (Choose two.)

• A. The tables being joined have NOT NULL column
• B. The tables being joined have only matched dat
• C. The columns being joined have NULL value
• D. The tables being joined have only unmatched dat
• E. The tables being joined have both matched and unmatched dat
• F. Only when the tables have a primary key/foreign key relationshi

Answer: CE

Explanation:
You use an outer join to also see rows that do not meet the join condition.
Incorrect Answer: Ameet a join condition Bmeet a join condition Dmeet non join condition only Fdoes not take into consideration of primary key and foreign key relationship
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-17

NEW QUESTION 14
View the Exhibit and examine the structure of the PROMOTIONS table.

Evaluate the following SQL statement:
SQL>SELECT promo_name,CASE
WHEN promo_cost >=(SELECT AVG(promo_cost)
FROM promotions
WHERE promo_category='TV')
then 'HIGH'
else 'LOW'
END COST_REMARK
FROM promotions;
Which statement is true regarding the outcome of the above query?

• A. It shows COST_REMARK for all the promos in the tabl
• B. It produces an error because the subquery gives an erro
• C. It shows COST_REMARK for all the promos in the promo category 'TV'.
• D. It produces an error because subqueries cannot be used with the CASE expressio

Answer: A

NEW QUESTION 15
Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?

• A. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;
• B. ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;
• C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;
• D. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;
• E. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;
• F. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;

Answer: C

NEW QUESTION 16
Examine the structure of the EMP_DEPT_VU view:

Which SQL statement produces an error?

• A. SELECT * FROM emp_dept_vu;
• B. SELECT department_id, SUM(salary) FROM emp_dept_vu GROUP BY department_id;
• C. SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu GROUP BY department_id, job_id;
• D. SELECT job_id, SUM(salary) FROM emp_dept_vu WHERE department_id IN (10,20) GROUP BY job_id HAVING SUM(salary) > 20000;
• E. None of the statements produce an error; all are vali

Answer: E

Explanation: Explanation: None of the statements produce an error. Incorrect Answer: AStatement will not cause error BStatement will not cause error CStatement will not cause error DStatement will not cause error