NEW QUESTION 1
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

Answer: BCD

NEW QUESTION 2
Examine the structure of the PROMOS table:

You want to display the list of promo names with the message 'Same Day' for promos that started and ended on the same day.
Which query gives the correct output?

• A. SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day') FROM promos;
• B. SELECT promo_name, NVL(TRUNC(promo_end_date - promo_start_date), 'Same Day') FROM promos;
• C. SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), NULL,'Same Day') FROM promos;
• D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), NULL,'Same day') FROM promos;

Answer: D

Explanation:
The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned ANSWER A - date and String incompatibl;a datatypes for NVL function The Date TRUNC Function The date TRUNC function performs a truncation operation on a date value based on a specified date precision format. The date TRUNC function takes one mandatory and one optional parameter. Its syntax is TRUNC(source date, [date precision format]). The source date parameter represents any value that can be implicitly converted into a date item. The date precision format parameter specifies the degree of truncation and is optional. If it is absent, the default degree of truncation is day. This means that any time component

NEW QUESTION 3
Evaluate the following query:

What would be the outcome of the above query?

• A. It produces an error because flower braces have been use
• B. It produces an error because the data types are not matchin
• C. It executes successfully and introduces an 's at the end of each promo_name in the outpu
• D. It executes successfully and displays the literal" {'s start date was} " for each row in the outpu

Answer: C

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.

NEW QUESTION 4
Examine this statement:
SELECT student_id, gpa FROM student_grades WHERE gpa > &&value;
You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time?

• A. An error is returne
• B. You are prompted to enter a new valu
• C. A report is produced that matches the first report produce
• D. You are asked whether you want a new value or if you want to run the report based on the previous valu

Answer: C

Explanation:
use the double-ampersand if you want to reuse the variable value without prompting the user each time.
Incorrect Answer: Ais not an error
B&& will not prompt user for second time D&& will not ask the user for new value
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7-13

NEW QUESTION 5
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);

Answer: D

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

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

Answer: BD

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

NEW QUESTION 7
View the Exhibits and examine the structures of the CUSTOMERS, SALES, and COUNTRIES tables.
You need to generate a report that shows all country names, with corresponding customers (if any) and sales details (if any), for all customers.
Which FROM clause gives the required result?

• A. FROM sales JOIN customers USING (cust_id) FULL OUTER JOIN countries USING (country_id);
• B. FROM sales JOIN customers USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
• C. FROM customers LEFT OUTER JOIN sales USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
• D. FROM customers LEFT OUTER JOIN sales USING (cust_id) LEFT OUTER JOIN countries USING (country_id);

Answer: C

NEW QUESTION 8
In the CUSTOMERS table, the CUST_CITY column contains the value 'Paris' for the
CUST_FIRST_NAME 'ABIGAIL'.
Evaluate the following query:

What would be the outcome?

• A. Abigail PA
• B. Abigail Pa
• C. Abigail IS
• D. an error message

Answer: B

NEW QUESTION 9
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: CD

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.

NEW QUESTION 10
View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.

You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007.
You issue the following query:
Which statement is true regarding the above query?

• A. It executes successfully and gives the required resul
• B. It executes successfully but does not give the required resul
• C. It produces an error because the join order of the tables is incorrec
• D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statemen

Answer: B

NEW QUESTION 11
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;

Answer: BC

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

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

Answer: C

NEW QUESTION 13
Which statement describes the ROWID data type?

• A. Binary data up to 4 gigabyte
• B. Character data up to 4 gigabyte
• C. Raw binary data of variable length up to 2 gigabyte
• D. Binary data stored in an external file, up to 4 gigabyte
• E. A hexadecimal string representing the unique address of a row in its tabl

Answer: E

Explanation:
The ROWID datatype stores information related to the disk location of table rows. They
also uniquely identify the rows in your table. The ROWID datatype is stored as a
hexadecimal string.
Incorrect Answers
A:It is not a binary data. The ROWID datatype is a hexadecimal string.
B:It is not a character data. The ROWID datatype is a hexadecimal string.
C:It is not a raw binary data. The ROWID datatype is a hexadecimal string.
D:It is not binary data stored in an external file. The ROWID datatype is a hexadecimal
string.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 216
Chapter 5: Creating Oracle Database Objects

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

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

Answer: A

NEW QUESTION 15
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;

Answer: D

Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view.
Incorrect Answer: Atable to view all constraints definition and names Bshow all object name belong to user Cdoes not display column associated Eno such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25

NEW QUESTION 16
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

Answer: C

NEW QUESTION 17
You need to create a table named ORDERS that contain four columns:
1.
an ORDER_ID column of number data type
2.
a CUSTOMER_ID column of number data type
3.
an ORDER_STATUS column that contains a character data type
4.
a DATE_ORDERED column to contain the date the order was placed.
When a row is inserted into the table, if no value is provided when the order was placed, today’s date should be used instead.
Which statement accomplishes this?

• A. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);
• B. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);
• C. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);
• D. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);
• E. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE = SYSDATE);
• F. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE DEFAULT SYSDATE);

Answer: B

Explanation: Requirement that Order_Status should be a character data type
Not E: Order_status must be a character data type. There is also a syntax error.