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

Question : You need to generate a list of all customer last names with their credit limits from the customers
table. Those customers who do not have a credit limit should appear last in the list.
Which two queries would achieve the required result?


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





Correct Answer : 5
Explanation: If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not
fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the
rows in a specific order.
Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows
containing null values should appear first or last in the ordering sequence. ANSWER C
Sorting
The default sort order is ascending:
.Numeric values are displayed with the lowest values first (for example, 1 to 999).
.Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01-JAN-95).
.Character values are displayed in the alphabetical order (for example, "A" first and "Z" last).
.Null values are displayed last for ascending sequences and first for descending sequences.
ANSWER B
.You can also sort by a column that is not in the SELECT list.






Question : You issue the following command to alter the country column in the departments table:
SQL> ALTER TABLE departments MODIFY (country DEFAULT 'USA');
Which statement is true?

1. It produces an error because column definitions cannot be altered to add default values.
2. It executes successfully and all the rows that have a null value for the country column will be updated with the value 'USA'.

3. It executes successfully. The modification to add the default value takes effect only from subsequent insertions to the table.
4. It produces an error because the data type for the column is not specified.



Correct Answer : 2
Explanation: When you create a database table, you have the option to specify a DEFAULT value. Using default values on database columns helps to insulate database design issues from application code. You can change the default value of a column at some later date with a single ALTER TABLE command and application code will immediately start using the new values.
It's important to remember that DEFAULT values are only used when a column isn't specified in an INSERT or MERGE, or when the DEFAULT keyword is used. If you don't explicitly declare a DEFAULT value, Oracle implicitly defines the default value to be NULL, and the DEFAULT keyword will even work with such. Since Oracle 9i, you may use pseudocolumns like SYSDATE or CURRENT_TIMESTAMP in DEFAULT clauses. For example:
create table t1
( id$ integer not null, charcol char default 'Y', datecol date default sysdate, strcol varchar2(30) default user, intcol integer default 12 );
insert into t1 (id$) values (1);
select * from t1;
ID$ C DATECOL STRCOL INTCOL
---------- - --------- ------------------------------ ----------
1 Y 28-MAY-04 SCOTT 12
The DEFAULT keyword from INSERT, MERGE, or UPDATE syntax may seem unnecessary, but consider the case where you wish to insert a row using all the default values. Oracle will not accept INSERT INTO table or INSERT INTO table VALUES () as valid SQL. You must specify at least one column, but you may use the DEFAULT keyword to allow the default value rather than hard-coding a value, so the following is valid syntax that will create a row with all DEFAULT values.
create table t2(charcol char default 'Y',datecol date default sysdate); insert into t2 (charcol) values (default); select * from t2;
C DATECOL
- ---------
Y 28-MAY-04
One very common problem is to emulate the Autonumber functionality of other database vendors where a column is automatically populated with some kind of sequence number. In Oracle databases, you cannot specify a sequence number as a DEFAULT value for a column; however, you can emulate this functionality using a trigger. Even if a column is declared NOT NULL, you can still omit the column from INSERT statements to be populated in the trigger.





Question : Which two statements are true regarding constraints?
A. A foreign key cannot contain null values.
B. A column with the unique constraint can contain null values.
C. A constraint is enforced only for the insert operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All constraints can be defined at the column level as well as the table level.

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




Correct Answer : 4
Explanation: Unique Constraints : A unique constraint designates a column as a unique key. A composite unique key designates a combination of columns as the unique key. When you define a unique constraint inline, you need only the UNIQUE keyword. When you define a unique constraint out of line, you must also specify one or more columns. You must define a composite unique key out of line.

To satisfy a unique constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls. To satisfy a composite unique key, no two rows in the table or view can have the same combination of values in the key columns. Any row that contains nulls in all key columns automatically satisfies the constraint. However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint.

When you specify a unique constraint on one or more columns, Oracle implicitly creates an index on the unique key. If you are defining uniqueness for purposes of query performance, then Oracle recommends that you instead create the unique index explicitly using a CREATE UNIQUE INDEX statement. You can also use the CREATE UNIQUE INDEX statement to create a unique function-based index that defines a conditional unique constraint. See "Using a Function-based Index to Define Conditional Uniqueness: Example" for more information.

Restrictions on Unique Constraints Unique constraints are subject to the following restrictions:
None of the columns in the unique key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the unique key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE.
A composite unique key cannot have more than 32 columns.
You cannot designate the same column or combination of columns as both a primary key and a unique key.
You cannot specify a unique key when creating a subview in an inheritance hierarchy. The unique key can be specified only for the top-level (root) view.

Related Questions

---

Question : Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?

1. It executes successfully and displays rows in the descending order of PROMO_CATEGORY.

2. It produces an error because positional notation cannot be used in the order by clause with set operators.

3. It executes successfully but ignores the order by clause because it is not located at the end of the compound statement.

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


Question : View the Exhibit and examine the structure of the product, component, and PDT_COMP tables.
In product table, PDTNO is the primary key.
In component table, COMPNO is the primary key.
In PDT_COMP table, PDTNO, COMPNO) is the primary key, PDTNO is the foreign key
referencing PDTNO in product table and COMPNO is the foreign key referencing the COMPNO in
component table.
You want to generate a report listing the product names and their corresponding component
names, if the component names and product names exist.
Evaluate the following query:
SQL>SELECT pdtno, pdtname, compno, compname
FROM product _____________ pdt_comp
USING (pdtno) ____________ component USING (compno)
WHERE compname IS NOT NULL;
Which combination of joins used in the blanks in the above query gives the correct output?

1. JOIN; JOIN

2. FULL OUTER JOIN; FULL OUTER JOIN
3. RIGHT OUTER JOIN; LEFT OUTER JOIN
4. LEFT OUTER JOIN; RIGHT OUTER JOIN



Question : View the Exhibit for the structure of the student and faculty tables.
You need to display the faculty name followed by the number of students handled by the faculty at
the base location.
Examine the given two SQL statements:

Which statement is true regarding the outcome?


1. Only statement 1 executes successfully and gives the required result.
2. Only statement 2 executes successfully and gives the required result.
3. Both statements 1 and 2 execute successfully and give different results.
4. Both statements 1 and 2 execute successfully and give the same required result.


Question : View the Exhibits and
examine products and sales tables.
You issue the following query to display
product name and the number of times the product has
been sold: What happens when the
above statement is executed?

1. The statement executes successfully and produces the required output.
2. The statement produces an error because item_cnt cannot be displayed in the outer query.
3. The statement produces an error because a subquery in the from clause and outer-joins cannot be used together.
4. The statement produces an error because the group by clause cannot be used in a subquery in the from clause.


Question : You want to create a table employees in
which the values of columns EMPLOYEES_ID and
LOGIN_ID must be unique and not null.
Which two SQL statements would create the required table?

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


Question : View the Exhibit and examine the structure of the 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 falls below $10 after a discount of
25% is applied on it.
The query generates an error. What is the reason for the error?


1. The parenthesis should be added to enclose the entire expression.
2. The double quotation marks should be removed from the column alias.
3. The column alias should be replaced with the expression in the where clause.
4. The column alias should be put in uppercase and enclosed within double quotation marks in the where clause.