Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)

Question : Which two statements are true regarding constraints?
A. A table can have only one primary key and one foreign key.
B. A table can have only one primary key but multiple foreign keys.
C. Only the primary key can be defined at the column and table levels.
D. The foreign key and parent table primary key must have the same name.
E. Both primary key and foreign key constraints can be defined at both column and table levels.

1. A,C
2. B,D
3. A,E
4. C,D
5. B,E


Correct Answer 5 :

Explanation: SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Most tables should have a primary key, and each table can have only ONE primary key.

A foreign key is a way to enforce referential integrity within your Oracle database. A foreign key means that values in one table must also appear in another table.
The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.

A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.





Question : In which three situations does a transaction complete?
A. When a DELETE statement is executed
B. When a ROLLBACK command is executed
C. When a PL/SQL anonymous block is executed
D. When a data definition language (DDL) statement is executed
E. When a TRUNCATE statement is executed after the pending transaction

1. A,B,C
2. A,C,E
3. A,D,E
4. B,C,E
5. B,D,E

Correct Answer 5 :
Explanation: Use the ROLLBACK statement to undo work done in the current transaction or to manually undo the work done by an in-doubt distributed transaction. To roll back your current transaction, no privileges are necessary.
To manually roll back an in-doubt distributed transaction that you originally committed, you must have the FORCE TRANSACTION system privilege. To manually roll back an in-doubt distributed transaction originally committed by another user, you must have the FORCE ANY TRANSACTION system privilege.
In Oracle DDL statements implicitly commit any transaction.
In Oracle, TRUNCATE TABLE is a DDL statement that cannot be used in a transaction (or, more accurately, cannot be rolled back). AFAIK, if there is a transaction in progress when the statement is executed, the transaction is committed and then the TRUNCATE is executed and cannot be undone.
Firstly, BEGIN..END are merely syntactic elements, and have nothing to do with transactions.
Secondly, in Oracle all individual DML statements are atomic (i.e. they either succeed in full, or rollback any intermediate changes on the first failure) (unless you use the EXCEPTIONS INTO option, which I won't go into here).
If you wish a group of statements to be treated as a single atomic transaction, you'd do something like this:
BEGIN
SAVEPOINT start_tran;
INSERT INTO .... ; -- first DML
UPDATE .... ; -- second DML
BEGIN ... END; -- some other work
UPDATE .... ; -- final DML
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO start_tran;
RAISE;
END;
That way, any exception will cause the statements in this block to be rolled back, but any statements that were run prior to this block will not be rolled back.
Note that I don't include a COMMIT - usually I prefer the calling process to issue the commit.







Question : Examine the structure of the orders table:
Which statement is true regarding the outcome?

1. It executes successfully and gives the correct output.
2. It gives an error because the TO_CHAR function is not valid.
3. It executes successfully but does not give the correct output.
4. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Correct Answer 4 :
Explanation: The Oracle GROUP BY Clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns. SYNTAX
The syntax for the Oracle GROUP BY Clause is:

SELECT expression1, expression2, ... expression_n,
aggregate_function (expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;
Parameters or Arguments

expression1, expression2, ... expression_n are expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause.

aggregate_function can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

tables are the tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

conditions are conditions that must be met for the records to be selected.

EXAMPLE - USING SUM FUNCTION

Let's look at an Oracle GROUP BY query example that uses the SUM function.

This Oracle GROUP BY example uses the SUM function to return the name of the product and the total sales (for the product).

SELECT product, SUM(sale) AS "Total sales"
FROM order_details
GROUP BY product;
Because you have listed one column (the product field) in your SELECT statement that is not encapsulated in the SUM function, you must use the GROUP BY Clause. The product field must, therefore, be listed in the GROUP BY clause.

Related Questions

---

Question : View the Exhibit and examine the structure of the products table.
What would be the outcome if all the parentheses are removed from the above statement?

1. It produces a syntax error.
2. The result remains unchanged.
3. The total price value would be lower than the correct value.
4. The total price value would be higher than the correct value.


Question : Examine the data in the PROMO_BEGIN_DATE column of the promotions table:

1. A
2. B
3. C
4. D


Question : You want to display the date for the first Monday of the next month and issue the following
command:

SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'),
'dd "is the first Monday for" fmmonth rrrr')
FROM DUAL;

What is the outcome?



1. It executes successfully and returns the correct result.
2. It executes successfully but does not return the correct result.
3. It generates an error because TO_CHAR should be replaced with TO_DATE.
4. It generates an error because rrrr should be replaced by rr in the format string.
5. It generates an error because fm and double quotation marks should not be used in the format string.


Question : View the Exhibit and examine the structure of the promotions table.
You need to generate a report of all promos from the promotions table based on the following
conditions:
1. The promo name should not begin with 'T' or 'N'.
2. The promo should cost more than $20000.
3. The promo should have ended after 1st January 2001.
Which where clause would give the required result?

1. WHERE promo_name NOT LIKE '%T' AND promo_name NOT LIKE 'N' AND
promo_cost > 20000 AND promo_end_date > '1-JAN-01'
2. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND
promo_cost > 20000 AND promo_end_date > '1-JAN-01'
3. WHERE promo_name NOT LIKE '%T' AND promo_name NOT LIKE '%N' AND
promo_cost > 20000 AND promo_end_date > '1-JAN-01'
4. WHERE promo_name NOT LIKE '%T%' AND promo_name NOT LIKE '%N%' AND
promo_cost > 20000 AND promo_end_date > '1-JAN-01'



Question : Examine the structure of the employees table.


1. A,C
2. B,E
3. D,E
4. B,F
5. B,D




Question : Using the customers table, you need to generate a report that shows % of each credit amount in
each income level. The report should NOT show any repeated credit amounts in each income
level. Which query would give the required result?

1. A
2. B
3. C
4. D