Oracle 1Z0-051 Practice Test, High Success Rate Oracle 1Z0-051 Study Guide With The Knowledge And Skills

Welcome to download the newest Examwind JN0-692 dumps:

ATTENTION : Because Oracle 1Z0-051 exam has change recently,Flydumps has updated the Oracle 1Z0-051 exam dumps with all new EMC E20-545 exam questions and answers, visit flydumps.com to get free Oracle 1Z0-051 PDF and VCE dumps.

QUESTION 77
You need to display the date 11-Oct-2007 in words as `Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?
A. SELECT TO_CHAR(’11-oct-2007′, ‘fmDdspth “of” Month, Year’) FROM DUAL;
B. SELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdspth of month, year’) FROM DUAL;
C. SELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdthsp “of” Month, Year’) FROM DUAL;
D. SELECT TO_DATE(TO_CHAR(’11-oct-2007′,’fmDdspth ”of” Month, Year’)) FROM DUAL;

Correct Answer: C Explanation
Explanation/Reference:
Explanation: Using the TO_CHAR Function with Dates TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that describes the format of datetime stored in a character string. For example, the datetime format model for the string ’11- Nov-1999′ is ‘DD-Mon-YYYY’. You can use the TO_CHAR function to convert a date from its default format to the one that you specify. Guidelines · The format model must be enclosed with single quotation marks and is case-sensitive. · The format model can include any valid date format element. But be sure to separate the date value from the format model with a comma. · The names of days and months in the output are automatically padded with blanks. · To remove padded blanks or to suppress leading zeros, use the fill mode fm element.
Elements of the Date Format Model ——————————————————————— DY Three-letter abbreviation of the day of the week DAY Full name of the day of the week DD Numeric day of the month
MM Two-digit value for the month MON Three-letter abbreviation of the month MONTH Full name of the month YYYY Full year in numbers YEAR Year spelled out (in English)
QUESTION 78
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

Correct Answer: B Explanation
Explanation/Reference:
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.

QUESTION 79
You work as a database administrator at ABC.com. You study the exhibit carefully and examine the structure of CUSTOMRS AND SALES tables.

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

Correct Answer: A Explanation
Explanation/Reference:
QUESTION 80
The STUDENT_GRADES table has these columns:

STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)

The registrar has asked for a report on the average grade point average (GPA), sorted from the highest
grade point average to each semester, starting from the earliest date.
Which statement accomplish this?

A. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC;
B. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa ASC
C. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
D. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end DESC;
E. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
F. SELECT student_id,semester_end,gpa FROM student_grades ORDER BY semester_end,gpa DESC
Correct Answer: F Explanation

Explanation/Reference:
QUESTION 81
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

Evaluate the following query: Exhibit:

The above query produces an error on execution. What is the reason for the error?
A. An alias cannot be used in an expression
B. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression
C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values
D. The alias NAME should not be enclosed within double quotation marks

Correct Answer: A Explanation
Explanation/Reference:
QUESTION 82
Which statement is true regarding synonyms?
A. Synonyms can be created only for a table
B. Synonyms are used to reference only those tables that are owned by another user
C. The DROP SYNONYM statement removes the synonym and the table on which the synonym has been created becomes invalid
D. A public synonym and a private synonym can exist with the same name for the same table

Correct Answer: D Explanation
Explanation/Reference:
QUESTION 83
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

Examine the structure of PRODUCTS table.
Using the PRODUCTS table, you issue the following query to generate the names, current list price and discounted list price for all those products whose list price fails below $10 after a discount of 25% is applied on it.
Exhibit:

The query generates an error.
What is the reason of generating error?

A. The column alias should be put in uppercase and enclosed within double quotation marks in the WHERE clause
B. The parenthesis should be added to enclose the entire expression
C. The column alias should be replaced with the expression in the WHERE clause
D. The double quotation marks should be removed from the column alias

Correct Answer: C Explanation
Explanation/Reference:
Note: You cannot use column alias in the WHERE clause.
QUESTION 84
Which one is a system privilege?
A. SELECT
B. DELETE
C. EXECUTE
D. ALTER TABLE
E. CREATE TABLE

Correct Answer: E Explanation
Explanation/Reference:
QUESTION 85
What is true about sequences?
A. The start value of the sequence is always 1.
B. A sequence always increments by 1.
C. The minimum value of an ascending sequence defaults to 1.
D. The maximum value of descending sequence defaults to 1.

Correct Answer: C Explanation
Explanation/Reference:
QUESTION 86
Examine the structure of the INVOICE table: Exhibit:

Which two SQL statements would execute successfully? (Choose two.)
A. SELECT inv_no,NVL2(inv_date,’Pending’,’Incomplete’) FROM invoice;
B. SELECT inv_no,NVL2(inv_amt,inv_date,’Not Available’) FROM invoice;
C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice;
D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,’Not Available’) FROM invoice;

Correct Answer: AC Explanation
Explanation/Reference:
Explanation: The NVL2 Function The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not. 5-6 The NVL function\ If the first term is not null, the second parameter is returned, else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null. The NVL2 function takes three mandatory parameters. Its syntax is NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG. They must either be of the same type, or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter.
QUESTION 87
View the Exhibit and examine the description for the CUSTOMERS table.

You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566.
Which UPDATE statement will accomplish the task?
A. UPDATE customers SET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566) WHERE cust_id=2360;
B. UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 OR cust_id=2566) WHERE cust_id=2360;
C. UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id IN(2560, 2566) WHERE cust_id=2360;
D. UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 AND cust_id=2566) WHERE cust_id=2360;

Correct Answer: A Explanation
Explanation/Reference:
Explanation:
Updating Two Columns with a Subquery
You can update multiple columns in the SET clause of an UPDATE statement by writing multiple
subqueries. The syntax is as follows:
UPDATE table
SET column =
(SELECT column
FROM table
WHERE condition)
[ ,

column = (SELECT column FROM table WHERE condition)] [WHERE condition ] ;
QUESTION 88
A SELECT statement can be used to perform these three functions:
1.
Choose rows from a table.

2.
Choose columns from a table

3.
Bring together data that is stored in different tables by creating a link between them.
Which set of keywords describes these capabilities?
A. difference, projection, join
B. selection, projection, join
C. selection, intersection, join
D. intersection, projection, join
E. difference, projection, product

Correct Answer: B Explanation
Explanation/Reference:
Explanation: choose rows from a table is SELECTION,
Choose column from a table is PROJECTION
Bring together data in different table by creating a link between them is JOIN.

Incorrect answer:
A answer should have SELECTION, PROJECTION and JOIN.
C answer should have SELECTION, PROJECTION and JOIN.
D answer should have SELECTION, PROJECTION and JOIN.
E answer should have SELECTION, PROJECTION and JOIN.

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-6

QUESTION 89
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;

Correct Answer: AB Explanation
Explanation/Reference:
QUESTION 90
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 Chicago data and time
B. Number of days since the promo started based on the current Singapore data and time.
C. An error because the WHERE condition specified is invalid
D. An error because the ROUND function specified is invalid Correct Answer: A

Explanation Explanation/Reference:
QUESTION 91
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)); Correct Answer: A

Explanation Explanation/Reference:
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:
B Non alphanumeric character such as “*” is discourage in Oracle table name.
D Table name must begin with a letter.

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-4

The Oracle 1Z0-051 study materials are followed at some levels to test the skills which are required for the information technology companies. The flydumps Oracle 1Z0-051 test questions are must for you to get the Oracle 1Z0-051 exam and it helps to become an expert database administrator. Flydumps Oracle 1Z0-051 study materials are providing the ways for solving the troubleshooting problems in the operating systems. The Oracle 1Z0-051 certification exam preparation will be helpful for you to solve the complex problems in the information technology companies. Oracle 1Z0-051 study materials guide you to develop the projects or infrastructures. Flydumps Oracle 1Z0-051 test questions provide the ways for planning, configuring and operational technologies that are suitable for the information technology sectors.

Welcome to download the newest Examwind JN0-692 dumps: http://www.examwind.com/JN0-692.html

Huawei HC-224 Dumps PDF, High Success Rate Huawei HC-224 Free Dowload Latest Version PDF&VCE

Oracle1Z0-051 Exam Dumps Download, Help To Pass Oracle1Z0-051 Certification On Store

Welcome to download the newest Examwind 070-523 dumps:

Oracle1Z0-051 exam sample questions also pays off far better to that particular of various other certificates since HDI QQ0-100 can be an exam in connection with the joy of Oracle1Z0-051 can be a challenging test, measuring your Oracle1Z0-051 test skills, and compliments the other test in this certification. Our Oracle1Z0-051 test are developed by experiences IT Professionals working in today’s prospering companies and data centers. Guarantee your Oracle1Z0-051 success with our Oracle1Z0-051 exam sample questions. All of our practice test including the Oracle1Z0-051 test will prepare you for success. With our Oracle1Z0-051 exam sample questions, you can feel safe. Qur Q&A will help you in obtaining your successful completion of your Oracle1Z0-051 test.

QUESTION 30
View the Exhibit and examine the description for the CUSTOMERS table.

You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task?
A. UPDATE customers SET cust_credit_limit = NULL WHERE CUST_INCOME_LEVEL = NULL;
B. UPDATE customers SET cust_credit_limit = NULL WHERE cust_income_level IS NULL;
C. UPDATE customers SET cust_credit_limit = TO_NUMBER(NULL) WHERE cust_income_level = TO_NUMBER(NULL);
D. UPDATE customers SET cust_credit_limit = TO_NUMBER(‘ ‘,9999) WHERE cust_income_level IS NULL;

Correct Answer: B Explanation
Explanation/Reference:
QUESTION 31
Which two statements about sub queries are true? (Choose two.)
A. A sub query should retrieve only one row.
B. A sub query can retrieve zero or more rows.
C. A sub query can be used only in SQL query statements.
D. Sub queries CANNOT be nested by more than two levels.
E. A sub query CANNOT be used in an SQL query statement that uses group functions.
F. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column.

Correct Answer: BF Explanation
Explanation/Reference:
Explanation: sub query can retrieve zero or more rows, sub query is used with an inequality comparison operator in the outer SQL statement, and the column list in the SELECT clause of the sub query should contain only one column.
Incorrect answer: A sub query can retrieve zero or more rows C sub query is not SQL query statement D sub query can be nested E group function can be use with sub query
QUESTION 32
View the Exhibit and examine the structure of the PROMOTIONS table.

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the ‘INTERNET’ category.
Which query would give you the required output?
A. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions ) AND promo_category = ‘INTERNET’;
B. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category=’INTERNET’);
C. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = ‘INTERNET’);
D. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category = ‘INTERNET’);

Correct Answer: C Explanation
Explanation/Reference:
QUESTION 33
Which are /SQL*Plus commands? (Choose all that apply.)
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME

Correct Answer: D Explanation
Explanation/Reference:
Explanation:
Describe is a valid iSQL*Plus/ SQL*Plus command.
INSERT, UPDATE & DELETE are SQL DML Statements. A SELECT is an ANSI Standard SQL Statement

not an iSQL*Plus Statement. RENAME is a DDL Statement.
QUESTION 34
Which two statements are true regarding the COUNT function? (Choose two.)
A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns
B. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column
C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column
D. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause
E. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types

Correct Answer: AC Explanation
Explanation/Reference:
Explanation:
Using the COUNT Function
The COUNT function has three formats:
COUNT(*)
COUNT(expr)
COUNT(DISTINCT expr)
COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including
duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the
SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause.
In contrast,
COUNT(expr) returns the number of non-null values that are in the column identified by expr. COUNT
(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.

QUESTION 35
Examine the description of the EMP_DETAILS table given below: Exhibit:

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)
A. An EMP_IMAGE column can be included in the GROUP BY clause
B. You cannot add a new column to the table with LONG as the data type
C. An EMP_IMAGE column cannot be included in the ORDER BY clause
D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column

Correct Answer: BC Explanation
Explanation/Reference:
Explanation: LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB. There can only be one LONG column in a table. Guidelines A LONG column is not copied when a table is created using a subquery. A LONG column cannot be
included in a GROUP BY or an ORDER BY clause.
Only one LONG column can be used per table.
No constraints can be defined on a LONG column.
You might want to use a CLOB column rather than a LONG column.

QUESTION 36
Which CREATE TABLE statement is valid?
A. CREATE TABLE ord_details (ord_no NUMBER(2) PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY, ord_date DATE NOT NULL);
B. CREATE TABLE ord_details (ord_no NUMBER(2) UNIQUE, NOT NULL, item_no NUMBER(3), ord_date DATE DEFAULT SYSDATE NOT NULL);
C. CREATE TABLE ord_details (ord_no NUMBER(2) , item_no NUMBER(3), ord_date DATE DEFAULT NOT NULL, CONSTRAINT ord_uq UNIQUE (ord_no), CONSTRAINT ord_pk PRIMARY KEY (ord_no));
D. CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(3), ord_date DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

Correct Answer: D Explanation
Explanation/Reference:
Explanation: PRIMARY KEY Constraint A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value. Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns.
QUESTION 37
See the exhibit and examine the structure of the CUSTOMERS and GRADES tables: You need to display names and grades of customers who have the highest credit limit. Which two SQL statements would accomplish the task? (Choose two.)

A. SELECT custname, grade FROM customers, grades WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval;
B. SELECT custname, grade FROM customers, grades WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval AND cust_credit_limit BETWEEN startval AND endval;
C. SELECT custname, grade FROM customers, grades WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit) FROM customers) AND cust_credit_limit BETWEEN startval AND endval;
D. SELECT custname, grade FROM customers , grades WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit) FROM customers) AND MAX(cust_credit_limit) BETWEEN startval AND endval;

Correct Answer: BC Explanation
Explanation/Reference:
QUESTION 38
See the Exhibit and Examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message “Not Available” displayed.
Which SQL statement would produce the required result?
A. SELECT NVL(cust_credit_limit,’Not Available’)*.15 “NEW CREDIT” FROM customers;
B. SELECT NVL(cust_credit_limit*.15,’Not Available’) “NEW CREDIT” FROM customers;
C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,’Not Available’)) “NEW CREDIT” FROM customers;
D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),’Not Available’) “NEW CREDIT” FROM customers;

Correct Answer: D Explanation
Explanation/Reference:
Explanation:
NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number.
Data types must match:

NVL(commission_pct,0)
NVL(hire_date,’01-JAN-97′)
NVL(job_id,’No Job Yet’)

QUESTION 39
You need to calculate the number of days from 1st Jan 2007 till date:
Dates are stored in the default format of dd-mm-rr.
Which two SQL statements would give the required output? (Choose two.)

A. SELECT SYSDATE – TO_DATE(’01/JANUARY/2007′) FROM DUAL;
B. SELECT TO_DATE(SYSDATE,’DD/MONTH/YYYY’)-’01/JANUARY/2007′ FROM DUAL;
C. SELECT SYSDATE – TO_DATE(’01-JANUARY-2007′) FROM DUAL
D. SELECT SYSDATE – ’01-JAN-2007′ FROM DUAL
E. SELECT TO_CHAR(SYSDATE,’DD-MON-YYYY’)-’01-JAN-2007′ FROM DUAL;

Correct Answer: AC Explanation
Explanation/Reference:
QUESTION 40
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.
Correct Answer: AD Explanation

Explanation/Reference:
QUESTION 41
See the structure of the PROGRAMS table:

Which two SQL statements would execute successfully? (Choose two.)
A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs;
B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM programs;
C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),’Ongoing’) FROM programs;
D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),’Ongoing’) FROM programs;

Correct Answer: AD Explanation
Explanation/Reference:
Explanation:
NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number.
Data types must match:

NVL(commission_pct,0)

NVL(hire_date,’01-JAN-97′)

NVL(job_id,’No Job Yet’)

MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2 . The result
can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2,
the result is negative. The noninteger part of the result represents a portion of the month.
MONTHS_BETWEEN returns a numeric value. – answer C NVL has different datatypes – numeric and
strings, which is not possible!

The data types of the original and if null parameters must always be compatible. They must either be of the
same type, or it must be possible to implicitly convert if null to the type of the original parameter. The NVL
function returns a value with the same data type as the original parameter.

QUESTION 42
You issue the following command to drop the PRODUCTS table: SQL>DROP TABLE products;
What is the implication of this command? (Choose all that apply.)
A. All data in the table are deleted but the table structure will remain
B. All data along with the table structure is deleted
C. All views and synonyms will remain but they are invalidated
D. The pending transaction in the session is committed
E. All indexes on the table will remain but they are invalidated
Correct Answer: BCD Explanation

Explanation/Reference:
QUESTION 43
Exhibit contains the structure of PRODUCTS table: Evaluate the following query:
What would be the outcome of executing the above SQL statement?
A. It produces an error
B. It shows the names of products whose list price is the second highest in the table.
C. It shown the names of all products whose list price is less than the maximum list price
D. It shows the names of all products in the table Correct Answer: B

Explanation Explanation/Reference:
QUESTION 44
See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table: You issue the following query:
Which statement is true regarding the outcome of this query?
A. It produces an error because the NATURAL join can be used only with two tables
B. It produces an error because a column used in the NATURAL join cannot have a qualifier
C. It produces an error because all columns used in the NATURAL join should have a qualifier
D. It executes successfully

Correct Answer: B Explanation
Explanation/Reference:
Explanation: Creating Joins with the USING Clause Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin. The Natural JOIN USING Clause The format of the syntax for the natural JOIN USING clause is as follows: SELECT table1.column, table2.column FROM table1 JOIN table2 USING (join_column1, join_column2…); While the pure natural join contains the NATURAL keyword in its syntax, the JOIN…USING syntax does not. An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on certain columns, and this format caters to this requirement.
QUESTION 45
You work as a database administrator at ABC.com. You study the exhibit carefully.
Exhibit:

You want to create a SALE_PROD view by executing the following SQL statements:

Which statement is true regarding the execution of the above statement?
A. The view will be created and you can perform DLM operations on the view
B. The view will not be created because the join statements are not allowed for creating a view
C. The view will not be created because the GROUP BY clause is not allowed for creating a view
D. The view will be created but no DML operations will be allowed on the view
Correct Answer: D Explanation

Explanation/Reference:
Explanation: Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view
QUESTION 46
Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)
A. The BLOB data type column is used to store binary data in an operating system file
B. The minimum column width that can be specified for a VARCHAR2 data type column is one
C. A TIMESTAMP data type column stores only time values with fractional seconds
D. The value for a CHAR data type column is blank-padded to the maximum defined column width
E. Only One LONG column can be used per table

Correct Answer: BDE Explanation
Explanation/Reference:
Explanation:
LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is
provided by CLOB; LONGs should not be used in a modern database, and if your database has any
columns of this type they should be converted to CLOB. There can only be one LONG column in a table.
DVARCHAR2 Variable-length character data, from 1 byte to 4KB. The data is stored in the database
character set. The VARCHAR2 data type must be qualified with a number indicating the maximum length
of the column.
If a value is inserted into the column that is less than this, it is not a problem: the value will only take up as
much space as it needs. If the value is longer than this maximum, the INSERT will fail with an error.
VARCHAR2(size)
Variable-length character data (A maximum size must be specified: minimum size is 1; maximum size is
4,000.)
BLOB Like CLOB, but binary data that will not undergo character set conversion by Oracle Net. BFILE A
locator pointing to a file stored on the operating system of the database server. The size of the files is
limited to 4GB.
TIMESTAMP This is length zero if the column is empty, or up to 11 bytes, depending on the precision
specified.
Similar to DATE, but with precision of up to 9 decimal places for the seconds, 6 places by default.

QUESTION 47
Which three are true? (Choose three.)
A. A MERGE statement is used to merge the data of one table with data from another.
B. A MERGE statement replaces the data of one table with that of another.
C. A MERGE statement can be used to insert new rows into a table.
D. A MERGE statement can be used to update existing rows in a table.

Correct Answer: ACD Explanation
Explanation/Reference:
Explanation: The MERGE Statement allows you to conditionally insert or update data in a table. If the rows are present in the target table which match the join condition, they are updated if the rows are not present they are inserted into the target table
QUESTION 48
Which two statements are true regarding views? (Choose two.)
A. A sub query that defines a view cannot include the GROUP BY clause
B. A view is created with the sub query having the DISTINCT keyword can be updated
C. A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table
D. A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated

Correct Answer: CD Explanation
Explanation/Reference:
Explanation: Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view
QUESTION 49
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which DELETE statement is valid?
A. DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);
B. DELETE * FROM employees WHERE employee_id=(SELECT employee_id FROM new_employees);
C. DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = `Carrey’);
D. DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = `Carrey’);

Correct Answer: C Explanation Explanation/Reference:
QUESTION 50
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 Correct Answer: D

Explanation Explanation/Reference:
QUESTION 51
Examine the structure of the EMPLOYEES table:

Which UPDATE statement is valid?
A. UPDATE employees SET first_name = `John’
SET last_name = `Smith’
WHERE employee_id = 180;

B. UPDATE employees SET first_name = `John’, SET last_name = `Smoth’ WHERE employee_id = 180;
C. UPDATE employee SET first_name = `John’ AND last_name = `Smith’ WHERE employee_id = 180;
D. UPDATE employee SET first_name = `John’, last_name = `Smith’ WHERE employee_id = 180;

Correct Answer: D Explanation
Explanation/Reference:
QUESTION 52
View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.

Evaluate the following SQL statements: Which statement is true regarding the above compound query?

A. It shows products that have a cost recorded irrespective of sales
B. It shows products that were sold and have a cost recorded
C. It shows products that were sold but have no cost recorded
D. It reduces an error Correct Answer: C

Explanation Explanation/Reference:
QUESTION 53
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

Correct Answer: B Explanation
Explanation/Reference:
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 54
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

Correct Answer: ABD Explanation
Explanation/Reference:
QUESTION 55
You work as a database administrator at ABC.com. You study the exhibit carefully. 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’));

Correct Answer: AB Explanation
Explanation/Reference:
QUESTION 56
Examine these statements:

CREATE ROLE registrar;
GRANT UPDATE ON student_grades TO registrar;
GRANT registrar to user1, user2, user3;

What does this set of SQL statements do?

A. The set of statements contains an error and does not work.
B. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
D. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role.
E. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object.
F. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar.

Correct Answer: C Explanation
Explanation/Reference:
Explanation: the statement will create a role call REGISTRAR, grant UPDATE on student_grades to registrar, grant the role to user1,user2 and user3.
Incorrect answer: A the statement does not contain error B there is no MODIFY privilege D statement does not create 3 users with the role E privilege is grant to role then grant to user F privilege is grant to role then grant to user
QUESTION 57
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;

Correct Answer: CD Explanation
Explanation/Reference:
QUESTION 58
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 time.
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 Correct Answer: D

Explanation Explanation/Reference:
QUESTION 59
Evaluate the following two queries: Exhibit:

Exhibit:

Which statement is true regarding the above two queries?
A. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column
B. Performance would degrade in query 2
C. There would be no change in performance
D. Performance would improve in query 2

Correct Answer: C Explanation
Explanation/Reference:
Explanation:
Note: The IN operator is internally evaluated by the Oracle server as a set of OR conditions, such as
a=value1 or a=value2 or a=value3. Therefore, using the IN operator has no performance benefits and is
used only for logical simplicity.

QUESTION 60
View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address. Which SET operator would you use to get the required output?
A. INTERSECT
B. UNION ALL
C. MINUS

D. UNION Correct Answer: C
Explanation Explanation/Reference:
QUESTION 61
You created an ORDERS table with the following description: Exhibit:

You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column.
Which statement is true in this scenario?
A. You cannot add a primary key constraint if data exists in the column
B. You can add the primary key constraint even if data exists, provided that there are no duplicate values
C. The primary key constraint can be created only a the time of table creation
D. You cannot have two constraints on one column

Correct Answer: B Explanation
Explanation/Reference:
QUESTION 62
Which object privileges can be granted on a view?
A. none
B. DELETE, INSERT,SELECT
C. ALTER, DELETE, INSERT, SELECT
D. DELETE, INSERT, SELECT, UPDATE

Correct Answer: D Explanation
Explanation/Reference:
Explanation: Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE. Incorrect answer: A Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE B Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE C Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-12
QUESTION 63
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
Correct Answer: CDE Explanation

Explanation/Reference:
QUESTION 64
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

Correct Answer: C Explanation
Explanation/Reference:
QUESTION 65
You need to create a table for a banking application. One of the columns in the table has the following requirements:
You want a column in the table to store the duration of the credit period The data in the column should be stored in a format such that it can be easily added and subtracted with DATE data type without using conversion The maximum period of the credit provision in the application is 30 days the interest has to be calculated for the number of days an individual has taken a credit for
Which data type would you use for such a column in the table?
A. INTERVAL YEAR TO MONTH
B. NUMBER
C. TIMESTAMP
D. DATE
E. INTERVAL DAY TO SECOND
Correct Answer: E Explanation

Explanation/Reference:
QUESTION 66
The STUDENT_GRADES table has these columns: Which statement finds students who have a grade point average (GPA) greater than 3.0 for the calendar year 2001?

A. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2001′ AND ’31-DEC-2001′ OR gpa > 3.;
B. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2001′ AND ’31-DEC-2001′ AND gpa gt 3.0;
C. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2001′ AND ’31-DEC-2001′ AND gpa > 3.0;
D. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2001′ AND ’31-DEC-2001′ OR gpa > 3.0;
E. SELECT student_id, gpa FROM student_grades WHERE semester_end > ’01-JAN-2001′ OR semester_end < ’31-DEC-2001′ AND gpa >= 3.0;

Correct Answer: C Explanation
Explanation/Reference:
QUESTION 67
View the Exhibit and examine the data in the PROMOTIONS table.

You need to display all promo categories that do not have ‘discount’ in their subcategory. Which two SQL statements give the required result? (Choose two.)
A. SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions
WHERE promo_subcategory = ‘discount’;
B. SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory = ‘discount’;
C. SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory <> ‘discount’;
D. SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory <> ‘discount’;
Correct Answer: AD Explanation

Explanation/Reference:
QUESTION 68
You work as a database administrator at ABC.com. You study the exhibit carefully.
Exhibit:

and examine the structure of CUSTOMRS AND SALES tables:
Evaluate the following SQL statement:
Exhibit:
Which statement is true regarding the execution of the above UPDATE statement?
A. It would not execute because the SELECT statement cannot be used in place of the table name
B. It would execute and restrict modifications to only the column specified in the SELECT 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 two tables cannot be used in a single UPDATE statement Correct Answer: B

Explanation Explanation/Reference:
QUESTION 69
See the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:

Using the PROMOTIONS table, you need to find out the average cost for all promos in the range $0-2000
and $2000-5000 in category A.
You issue the following SQL statements:
Exhibit:
What would be the outcome?
A. It generates an error because multiple conditions cannot be specified for the WHEN clause
B. It executes successfully and gives the required result
C. It generates an error because CASE cannot be used with group functions
D. It generates an error because NULL cannot be specified as a return value Correct Answer: B

Explanation Explanation/Reference:
Explanation: CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END
QUESTION 70
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

Correct Answer: AD Explanation
Explanation/Reference:
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 71
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);

Correct Answer: D Explanation
Explanation/Reference:
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
QUESTION 72
The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?
A. GRANT select ON dept TO ALL_USERS;
B. GRANT select ON dept TO ALL;
C. GRANT QUERY ON dept TO ALL_USERS
D. GRANT select ON dept TO PUBLIC;

Correct Answer: D Explanation
Explanation/Reference:
Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view.
Incorrect answer: A table to view all constraints definition and names B show all object name belong to user C does not display column associated E no such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25
QUESTION 73
Which is an iSQL*Plus command?
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME

Correct Answer: D Explanation
Explanation/Reference:
Explanation:
The only SQL*Plus command in this list: DESCRIBE. It cannot be used as SQL command. This command
returns a description of tablename, including all columns in that table, the datatype for each column and an
indication of whether the column permits storage of NULL values.

Incorrect answer:
A INSERT is not a SQL*PLUS command
B UPDATE is not a SQL*PLUS command
C SELECT is not a SQL*PLUS command
E DELETE is not a SQL*PLUS command
F RENAME is not a SQL*PLUS command

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7
QUESTION 74
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit

Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001
starting with the latest promo.
Which query would give the required result? (Choose all that apply.)

A. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > ’01-JAN-01′ ORDER BY 1 DESC;
B. SELECT promo_name,promo_begin_date “START DATE” FROM promotions WHERE promo_begin_date > ’01-JAN-01′ ORDER BY “START DATE” DESC;
C. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > ’01-JAN-01′ ORDER BY 2 DESC;
D. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > ’01-JAN-01′ ORDER BY promo_name DESC;

Correct Answer: BC Explanation
Explanation/Reference:
QUESTION 75
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

Correct Answer: B Explanation
Explanation/Reference:
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. 1Z0-051

Choose FLYDUMPS Oracle1Z0-051 exam sample questions and download Oracle1Z0-051 test questions to get the Oracle1Z0-051 test official certifications. FLYDUMPS Oracle1Z0-051 exam sample questions issues are created using issues, put together with accurate, plausible and approved explanations. Each of our Oracle1Z0-051 exam sample questions affords you a tests practical knowledge unrivaled. Each of our Oracle1Z0-051 process test issues and Oracle1Z0-051 test happen to be listening to advice from up-to-date and effective Technology professionals who work with their own knowledge of setting up you competent in the future. Through becoming an Android Application Engineer Certifications Basic is aware of extensive range involving expertise essental to Android authorities while in the selected task purpose. Oracle1Z0-051 exam sample questions is in sought after demand in the current current market.

Welcome to download the newest Examwind 070-523 dumps: http://www.examwind.com/070-523.html

F5 F50-521 Practise Questions, Sale Latest F5 F50-521 Cert Exam Material Provider UP To 50% Off

Latest Oracle 1Z0-051 Exam Questions Ensure 100 Percent Exam Pass

New VCE and PDF– If you want to pass Oracle 1Z0-051 exam successfully,do not miss to test Cisco latest Oracle 1Z0-051 brain dumps.All Oracle 1Z0-051 the new questions and answers were timely added, visit Flydumps.com to free download VCE player and PDF files.

QUESTION 1
Evaluate the SQL statement: TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)
A. It releases the storage space used by the table.
B. It does not release the storage space used by the table.
C. You can roll back the deletion of rows after the statement executes.
D. You can NOT roll back the deletion of rows after the statement executes.
E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.
F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table
Correct Answer: ADF Explanation
Explanation/Reference:
Explanation:
A: The TRUNCATE TABLE Statement releases storage space used by the table,
D: Can not rollback the deletion of rows after the statement executes,
F: You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the DEPT table.
Incorrect answer: C is not true D is not true E is not true
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-18
QUESTION 2
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));
Correct Answer: D Explanation
Explanation/Reference:
Explanation: CONSTRAINT name FOREIGN KEY (column_name) REFERENCES table_name (column_name);
Incorrect answer: A invalid syntax B invalid syntax C invalid syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-14
QUESTION 3
Here is the structure and data of the CUST_TRANS table: Exhibit:

Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table. Which three SQL statements would execute successfully? (Choose three.)
A. SELECT transdate + ’10’ FROM cust_trans;
B. SELECT * FROM cust_trans WHERE transdate = ’01-01-07′;
C. SELECT transamt FROM cust_trans WHERE custno > ’11’;
D. SELECT * FROM cust_trans WHERE transdate=’01-JANUARY-07′;
E. SELECT custno + ‘A’ FROM cust_trans WHERE transamt > 2000; Correct Answer: ACD
Explanation Explanation/Reference:
QUESTION 4
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;
Correct Answer: AD Explanation
Explanation/Reference:
QUESTION 5
Which three statements are true regarding sub queries? (Choose three.)
A. Multiple columns or expressions can be compared between the main query and sub query
B. Main query and sub query can get data from different tables
C. Sub queries can contain GROUP BY and ORDER BY clauses
D. Main query and sub query must get data from the same tables
E. Sub queries can contain ORDER BY but not the GROUP BY clause
F. Only one column or expression can be compared between the main query and subqeury
Correct Answer: ABC Explanation
Explanation/Reference:
QUESTION 6
See the Exhibit and examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you need to generate a report that shown the average credit limit for
customers in WASHINGTON and NEW YORK.
Which SQL statement would produce the required result?

A. SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’) GROUP BY cust_credit_limit, cust_city;
B. SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’) GROUP BY cust_city,cust_credit_limit;
C. SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’) GROUP BY cust_city;
D. SELECT cust_city, AVG(NVL(cust_credit_limit,0)) FROM customers WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’);
Correct Answer: C Explanation
Explanation/Reference:
Explanation: Creating Groups of Data: GROUP BY Clause Syntax You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group. In the syntax: group_by_expression Specifies the columns whose values determine the basis for grouping rows Guidelines · If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause. · Using a WHERE clause, you can exclude rows before dividing them into groups. · You must include the columns in the GROUP BY clause. · You cannot use a column alias in the GROUP BY clause.
QUESTION 7
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.
Correct Answer: A Explanation
Explanation/Reference:
Explanation: the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.
Incorrect answer: B there is no syntax error C result are sorted in ascending order by default D ORDER BY 2 will take the second column as sorting column. Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22
QUESTION 8
Where can sub queries be used? (Choose all that apply)
A. field names in the SELECT statement
B. the FROM clause in the SELECT statement
C. the HAVING clause in the SELECT statement
D. the GROUP BY clause in the SELECT statement
E. the WHERE clause in only the SELECT statement
F. the WHERE clause in SELECT as well as all DML statements
Correct Answer: ABCF Explanation Explanation/Reference:
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.
QUESTION 9
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)
A. SELECT TO_CHAR(1890.55,’$99G999D00′) FROM DUAL;
B. SELECT TO_CHAR(1890.55,’$9,999V99′) FROM DUAL;
C. SELECT TO_CHAR(1890.55,’$0G000D00′) FROM DUAL;
D. SELECT TO_CHAR(1890.55,’$99G999D99′) FROM DUAL;
E. SELECT TO_CHAR(1890.55,’$9,999D99′) FROM DUAL;
Correct Answer: ACD Explanation
Explanation/Reference:
QUESTION 10
Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?
A. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement
B. It executes successfully and displays rows in the descending order of PROMO_CATEGORY
C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement
D. It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators

Correct Answer: A Explanation
Explanation/Reference:
Explanation: Using the ORDER BY Clause in Set Operations The ORDER BY clause can appear only once at the end of the compound query. Component queries cannot have individual ORDER BY clauses. The ORDER BY clause recognizes only the columns of the first SELECT query. By default, the first column of the first SELECT query is used to sort the output in an ascending order.
QUESTION 11
Which statement correctly describes SQL and /SQL*Plus?
A. Both SQL and /SQL*plus allow manipulation of values in the database.
B. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statements.
C. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the server.
D. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the database.
Correct Answer: A Explanation
Explanation/Reference:
QUESTION 12
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
Correct Answer: BEGH Explanation
Explanation/Reference:
Explanation: SQL have character, numeric, date, conversion function.
Incorrect answer:
A SQL have character, numeric, date, conversion function.
C SQL have character, numeric, date, conversion function.
D SQL have character, numeric, date, conversion function.
F SQL have character, numeric, date, conversion function.

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-3

QUESTION 13
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: Which MERGE statement is valid?

A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
B. name = e.first_name ||’,’|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||’, ‘||e.last_name);
C. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
D. name = e.first_name ||’,’|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||’, ‘||e.last_name);
E. MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
F. name = e.first_name ||’,’|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||’, ‘||e.last_name);
G. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
H. name = e.first_name ||’,’|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||’, ‘||e.last_name);
Correct Answer: A Explanation

Explanation/Reference:
Explanation: this is the correct MERGE statement syntax Incorrect answer: B it should MERGE INTO table_name C it should be WHEN MATCHED THEN D it should MERGE INTO table_name Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29
QUESTION 14
Which view should a user query to display the columns associated with the constraints on a table owned by the user?
A. USER_CONSTRAINTS
B. USER_OBJECTS
C. ALL_CONSTRAINTS
D. USER_CONS_COLUMNS
E. USER_COLUMNS

Correct Answer: D Explanation
Explanation/Reference:
Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view. Incorrect answer: A table to view all constraints definition and names B show all object name belong to user C does not display column associated E no such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25
QUESTION 15
The COMMISSION column shows the monthly commission earned by the employee. Exhibit

Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)
A. listing the employees who earn the same amount of commission as employee 3
B. finding the total commission earned by the employees in department 10
C. finding the number of employees who earn a commission that is higher than the average commission of the company
D. listing the departments whose average commission is more that 600
E. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
F. listing the employees whose annual commission is more than 6000

Correct Answer: AC Explanation
Explanation/Reference: QUESTION 16
Examine the structure of the STUDENTS table:

You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?
A. SELECT student_ id, marks, ROWNUM “Rank” FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN ’01-JAN-99’ AND ’31-DEC-99 AND course_id = ‘INT_SQL’ ORDER BY marks DESC;
B. SELECT student_id, marks, ROWID “Rank” FROM students WHERE ROWID <= 10 AND finish_date BETWEEN ’01-JAN-99′ AND ’31-DEC-99′ AND course_id = ‘INT_SQL’ ORDER BY marks;
C. SELECT student_id, marks, ROWNUM “Rank” FROM (SELECT student_id, marks FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN ’01-JAN-99′ AND ’31-DEC-99′ AND course_id = ‘INT_SQL’ ORDER BY marks DESC);
D. SELECT student_id, marks, ROWNUM “Rank” FROM (SELECT student_id, marks FROM students WHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC-99′ AND course_id = `INT_SQL’ ORDER BY marks DESC) WHERE ROWNUM <= 10 ;
E. SELECT student id, marks, ROWNUM “Rank” FROM (SELECT student_id, marks FROM students ORDER BY marks) WHERE ROWNUM <= 10 AND finish date BETWEEN ’01-JAN-99′ AND ’31-DEC-99′ AND course_id = `INT_SQL’;
Correct Answer: D Explanation

Explanation/Reference: QUESTION 17
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

Correct Answer: B Explanation
Explanation/Reference:
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),
QUESTION 18
Evaluate the following SQL statements: DELETE FROM sales;
There are no other uncommitted transactions on the SALES table. Which statement is true about the DELETE statement?
A. It removes all the rows as well as the structure of the table
B. It removes all the rows in the table and deleted rows cannot be rolled back
C. It removes all the rows in the table and deleted rows can be rolled back
D. It would not remove the rows if the table has a primary key Correct Answer: C

Explanation Explanation/Reference:
QUESTION 19
Examine the structure of the EMPLOYEES table:

You want to create a SQL script file that contains an INSERT statement. When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below:

Which INSERT statement meets the above requirements?
A. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, ‘&ename’, ‘&jobid’, 2000, NULL, &did);
B. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, ‘&ename’, ‘&jobid’, 2000, NULL, &did IN (20,50));
C. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50)) VALUES (emp_id_seq.NEXTVAL, ‘&ename’, ‘&jobid’, 2000, NULL, &did);
D. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECK OPTION) VALUES (emp_id_seq.NEXTVAL, ‘&ename’, ‘&jobid’, 2000, NULL, &did);
E. INSERT INTO (SELECT * FROM employees WHERE (department_id = 20 AND
department_id = 50)
WITH CHECK OPTION )
VALUES (emp_id_seq.NEXTVAL, ‘&ename’, ‘&jobid’, 2000, NULL, &did);
Correct Answer: D Explanation
Explanation/Reference:
QUESTION 20
Which two statements are true regarding constraints? (Choose two.)
A. A constraint can be disabled even if the constraint column contains data
B. A constraint is enforced only for the INSERT operation on a table
C. A foreign key cannot contain NULL values
D. All constraints can be defined at the column level as well as the table level
E. A columns with the UNIQUE constraint can contain NULL values

Correct Answer: AE Explanation
Explanation/Reference:

CCNA Exam Certification Guide is a best-of-breed Oracle 1Z0-051 exam study guide that has been completely updated to focus specifically on the objectives.Senior instructor and best-selling author Wendell Odom shares preparation hints and Oracle 1Z0-051 tips to help you identify areas of weakness and improve both your conceptual and hands-on knowledge.Oracle 1Z0-051 Material is presented in a concise manner, focusing on increasing your understanding and retention of exam topics.