A Complete Guide to 1z0 051 dumps pdf
It is more faster and easier to pass the Oracle 1z0 051 pdf exam by using Certified Oracle Oracle Database: SQL Fundamentals I questuins and answers. Immediate access to the Far out 1z0 051 practice test Exam and find the same core area 1z0 051 dumps pdf questions with professionally verified answers, then PASS your exam with a high score now.
Q1. - (Topic 1)
Evaluate these two SQL statements:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC;
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;
What is true about them?
A. The two statements produce identical results.
B. The second statement returns a syntax error.
C. There is no need to specify DESC because the results are sorted in descending order by default.
D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
Answer: A
Explanation: Explanation: the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.
Incorrect Answer: Bthere is no syntax error Cresult are sorted in ascending order by default DORDER BY 2 will take the second column as sorting column. Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22
Q2. - (Topic 1)
Which two statements are true regarding single row functions? (Choose two.)
A. They can be nested only to two levels
B. They always return a single result row for every row of a queried table
C. Arguments can only be column values or constant
D. They can return a data type value different from the one that is referenced
E. They accept only a single argument
Answer: B,D
Explanation:
A function is a program written to optionally accept input parameters, perform an operation, or return a single value. A function returns only one value per execution. Three important components form the basis of defining a function. The first is the input parameter list. It specifies zero or more arguments that may be passed to a function as input for processing. These arguments or parameters may be of differing data types, and some are mandatory while others may be optional. The second component is the data type of its resultant value. Upon execution, only one value is returned by the function. The third encapsulates the details of the processing performed by the function and contains the program code that optionally manipulates the input parameters, performs calculations and operations, and generates a return value.
Q3. - (Topic 2)
Examine the structure of the PROMOTIONS table:
The management wants to see a report of unique promotion costs in each promotion category.
Which query would achieve the required result?
A. SELECT DISTINCT promo_cost, promo_category FROM promotions;
B. SELECT promo_category, DISTINCT promo_cost FROM promotions;
C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
Answer: D
Q4. - (Topic 1)
Examine the structure and data in the PRIC E_LIST table: Name Null? Type
PROD_D NOT NULL NUMBER(3)
PROD_PRICE VARCHAR2(10)
PROD_ID PROD PRICE
100 $234.55
101 $6,509.75
102 $1,234
in the same format as the PROD_PRICE. Which SQL statement would give the required result?
A. SELECT TO_CHAR(prod_price* .25.'$99.999.99') FROM PRICEJLIST:
B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25.'$99.999.00') FROM PRICE_LIST;
C. SELECT TO_CRAR(TO_NUMBER(prod_price.'S99.999.99')* .25.'$99.999.00') FROM PRICE_LIST:
D. SELECT TO_NUMBER(TO_NUMBER(prod_price.,$99.999.99')* .25/$99.999.00') FROM PRICE_LIST:
Answer: C
Q5. - (Topic 1)
Which four are types of functions available in SQL? (Choose 4)
A. string
B. character
C. integer
D. calendar
E. numeric
F. translation
G. date
H. conversion
Answer: B,E,G,H
Explanation: Explanation: SQL have character, numeric, date, conversion function.
Incorrect Answer:
ASQL have character, numeric, date, conversion function.
CSQL have character, numeric, date, conversion function.
DSQL have character, numeric, date, conversion function.
FSQL have character, numeric, date, conversion function.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-3
Q6. - (Topic 2)
Which substitution variable would you use if you want to reuse the variable without prompting the user each time?
A. &
B. ACCEPT
C. PROMPT
D. &&
Answer: D
Explanation:
To reuse the variable without prompting the user each time you can use && substitution
variable.
Incorrect Answers
A:This substitution variable will prompt the user each time.
B:ACCEPT is command, not substitution variable. It used to define more accurate or
specific prompt or when you want more output to display as the values are defined.
C:PROMPT is part of the ACCEPT command, it is not a variable.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 165-173
Chapter 4: Sub queries
Q7. - (Topic 1)
Which statements are correct regarding indexes? (Choose all that apply.)
A. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.
B. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
C. A FOREIGN KEY constraint on a column in a table automatically creates a non unique key
D. When a table is dropped, the corresponding indexes are automatically dropped
Answer: A,B,D
Q8. - (Topic 2)
The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4)
LAST_NAME VARCHAR2 (25)
JOB_ID VARCHAR2(10)
You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?
A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE ''
B. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_'
C. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE "";
D. SELECT employee_id, last_name, job_id FROM employees WHERE job_id = '%SA_'
Answer: A
Explanation: ESCAPE identifier to search for the _ symbol
Incorrect Answer: BESCAPE identifier must be use Cwrong syntax Dwrong syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-13
Q9. - (Topic 1)
View the Exhibit and examine the data in the COSTS table.
You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query:
Which statement is true regarding the above query?
A. It executes and produces the required result.
B. It produces an error because an expression cannot be used in the ORDER BY clause.
C. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.
D. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause.
Answer: A
Q10. - (Topic 1)
See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables:
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following the CREATE TABLE command:
Exhibit:
Which statement is true regarding the above command?
A. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match
B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table
C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition
D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table
Answer: B
Explanation:
Creating a Table Using a Subquery
Create a table and insert rows by combining the CREATE
TABLE statement and the AS subquery option.
CREATE TABLE table
[(column, column...)]
AS subquery;
Match the number of specified columns to the number of subquery columns.
Define columns with column names and default values.
Guidelines
The table is created with the specified column names, and the rows retrieved by the
SELECT statement are inserted into the table.
The column definition can contain only the column name and default value.
If column specifications are given, the number of columns must equal the number of
columns in the subquery SELECT list.
If no column specifications are given, the column names of the table are the same as the
column names in the subquery.
The column data type definitions and the NOT NULL constraint are passed to the new
table. Note that only the explicit NOT NULL constraint will be inherited. The PRIMARY KEY
column will not pass the NOT NULL feature to the new column. Any other constraint rules
are not passed to the new table. However, you can add constraints in the column definition.