Oracle 1z0-051 Free Practice Exam & Test Training

Question 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

Question 2

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

Question 3

See the Exhibit and examine the structure and data in the INVOICE table:
Exhibit:

Which two SQL statements would executes successfully? (Choose two.)

A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
B. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
C. SELECT (AVG(inv_date) FROM invoice;
D. SELECT AVG(inv_date - SYSDATE),AVG(inv_amt) FROM invoice;

Answer : A,D

Question 4

Which two are true about aggregate functions? (Choose two.)

A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.
C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
D. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.
E. You can use aggregate functions on a table, only by grouping the whole table as one single group.
F. You cannot group the rows of a table by more than one column while using aggregate functions.

Answer : A,D

Question 5

Which two statements are true regarding the USING and ON clauses in table joins?
(Choose two.)

A. The ON clause can be used to join tables on columns that have different names but compatible data types
B. A maximum of one pair of columns can be joined between two tables using the ON clause
C. Both USING and ON clause can be used for equijoins and nonequijoins
D. The WHERE clause can be used to apply additional conditions in SELECT statement containing the ON or the USING clause

Answer : A,D

Explanation:
Creating Joins with the USING Clause
If several columns have the same names but the data types do not match, use the USING clause to specify the columns for the equijoin.
Use the USING clause to match only one column when more than one column matches.
The NATURAL JOIN and USING clauses are mutually exclusive
Using Table Aliases with the USING clause
When joining with the USING clause, you cannot qualify a column that is used in the
USING clause itself. Furthermore, if that column is used anywhere in the SQL statement, you cannot alias it. For example, in the query mentioned in the slide, you should not alias the location_id column in the WHERE clause because the column is used in the USING clause.
The columns that are referenced in the USING clause should not have a qualifier (table name oralias) anywhere in the SQL statement.
Creating Joins with the ON Clause
The join condition for the natural join is basically an equijoin of all columns with the same name.
Use the ON clause to specify arbitrary conditions or specify columns to join. ANSWER C
The join condition is separated from other search conditions. ANSWER D
The ON clause makes code easy to understand.

Question 6

See the Exhibit and examine the structure and data in the INVOICE table:
Exhibit:

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

A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
B. SELECT AVG(inv_date-SYSDATE),AVG(inv_amt) FROM invoice;
C. SELECT MAX(AVG(SYSDATE-inv_date)) FROM invoice;
D. SELECT AVG(inv_date) FROM invoice;

Answer : A,B

Question 7

Which is a valid CREATE TABLE statement?

A. CREATE TABLE EMP9$# AS (empid number(2));
B. CREATE TABLE EMP*123 AS (empid number(2));
C. CREATE TABLE PACKAGE AS (packid number(2));
D. CREATE TABLE 1EMP_TEST AS (empid number(2));

Answer : A

Explanation: Table names and column names must begin with a letter and be 1-30 characters long. Characters A-Z,a-z, 0-9, _, $ and # (legal characters but their use is discouraged).
Incorrect answer:
BNon alphanumeric character such as * is discourage in Oracle table name.
DTable name must begin with a letter.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-4

Question 8

The PRODUCTS table has the following structure:

Evaluate the following two SQL statements:

Which statement is true regarding the outcome?

A. Both the statements execute and give the same result
B. Both the statements execute and give different results
C. Only the second SQL statement executes successfully
D. Only the first SQL statement executes successfully

Answer : B

Explanation:

Using the NVL2 Function -
The NVL2 function examines the first expression. If the first expression is not null, the
NVL2 function returns the second expression. If the first expression is null, the third expression is returned.

Syntax -
NVL2(expr1, expr2, expr3)
In the syntax:
expr1 is the source value or expression that may contain a null expr2 is the value that is returned if expr1 is not null expr3 is the value that is returned if expr1 is null

Question 9

View the Exhibit and examine the structure of the CUSTOMERS table.
Exhibit:

you issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name 'king' and whose credit limit is less than the maximum credit limit in countries that have customers with the last name
'king'.

Which statement is true regarding the outcome of the above query?

A. It produces an error and the < operator should be replaced by < ANY to get the required output
B. It produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to get the required output
C. It executes and shows the required result
D. It produces an error and the < operator should be replaced by < ALL to get the required output

Answer : C

Question 10

Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

A. addition
B. subtraction
C. raising to a power
D. finding the quotient
E. finding the lowest value

Answer : A,C,E

Question 11

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.)

Question 12

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

Question 13

View the Exhibit and examine the structure of the PRODUCTS table. You need to generate a report in the following format:

CATEGORIES -
5MP Digital Photo Camera's category is Photo

Y Box's category is Electronics -
Envoy Ambassador's category is Hardware
Which two queries would give the required output? (Choose two.)

A. SELECT prod_name || q'''s category is ' || prod_category CATEGORIES FROM products;
B. SELECT prod_name || q'['s ]'category is ' || prod_category CATEGORIES FROM products;
C. SELECT prod_name || q'\'s\' || ' category is ' || prod_category CATEGORIES FROM products;
D. SELECT prod_name || q'<'s >' || 'category is ' || prod_category CATEGORIES FROM products;

Answer : C,D

Explanation:
So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string
Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any other character literal.
These character-enclosing symbols could have been anything other than single quotation marks.
Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces},
[squarebrackets], or <angle brackets>. Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character
The syntax of the alternative quote operator is as follows:
q'delimiter'character literal which may include the single quotes delimiter' where delimiter can be any character or bracket.

Alternative Quote (q) Operator -
Specify your own quotation mark delimiter.
Select any delimiter.
Increase readability and usability.
SELECT department_name || q'[ Department's Manager Id: ]'
|| manager_id
AS "Department and Manager"
FROM departments;

Alternative Quote (q) Operator -
Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter.
You can choose any convenient delimiter, single-byte or multibyte, or any of the following character pairs: [ ], { }, ( ), or < >.
In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets
[] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.

Question 14

See the Exhibit and Examine the structure of SALES and PROMOTIONS tables:
Exhibit:

You want to delete rows from the SALES table, where the PROMO_NAME column in the
PROMOTIONS table has either blowout sale or everyday low price as values.
Which DELETE statements are valid? (Choose all that apply.)

A. DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale') AND promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'everyday low price');
B. DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale') OR promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'everyday low price');
C. DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale' OR promo_name = 'everyday low price');
D. D DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name IN ('blowout sale','everyday low price'));

Answer : B,C,D

Question 15

View the Exhibit and examine the structure of the PRODUCTS table.
All products have a list price.
You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of S100 have to be applied to all the products.