Premium

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



Question : Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
: Evaluate the following SQL statement:
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.

Correct Answer : 4
Explanation: The ORDER BY clause is an optional element of the following: A SELECT statement , A SelectExpression , A VALUES expression , A ScalarSubquery , A TableSubquery It can also be used in an INSERT statement or a CREATE VIEW statement.
An ORDER BY clause allows you to specify the order in which rows appear in the result set. In subqueries, the ORDER BY clause is meaningless unless it is accompanied by one or both of the result offset and fetch first clauses or in conjunction with the ROW_NUMBER function, since there is no guarantee that the order is retained in the outer result set. It is permissible to combine ORDER BY on the outer query with ORDER BY in subqueries.
Syntax : ORDER BY { column-Name | ColumnPosition | Expression } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [ , column-Name | ColumnPosition | Expression
[ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ] *
column-Name : Refers to the names visible from the SelectItems in the underlying query of the SELECT statement. The column-Name that you specify in the ORDER BY clause does not need to be the SELECT list.
ColumnPosition : An integer that identifies the number of the column in the SelectItems in the underlying query of the SELECT statement. ColumnPosition must be greater than 0 and not greater than the number of columns in the result table. In other words, if you want to order by a column, that column must be specified in the SELECT list.
Expression : A sort key expression, such as numeric, string, and datetime expressions. Expression can also be a row value expression such as a scalar subquery or case expression.
ASC : Specifies that the results should be returned in ascending order. If the order is not specified, ASC is the default.
DESC : Specifies that the results should be returned in descending order.
NULLS FIRST : Specifies that NULL values should be returned before non-NULL values.
NULLS LAST : Specifies that NULL values should be returned after non-NULL values.
Notes : If SELECT DISTINCT is specified or if the SELECT statement contains a GROUP BY clause, the ORDER BY columns must be in the SELECT list.
An ORDER BY clause prevents a SELECT statement from being an updatable cursor. For more information, see Requirements for updatable cursors and updatable ResultSets. If the null ordering is not specified then the handling of the null values is: NULLS LAST if the sort is ASC
NULLS FIRST if the sort is DESC If neither ascending nor descending order is specified, and the null ordering is also not specified, then both defaults are used and thus the order will be ascending with NULLS LAST. Example using a correlation name You can sort the result set by a correlation name, if the correlation name is specified in the select list. For example, to return from the CITIES database all of the entries in the CITY_NAME and COUNTRY columns, where the COUNTRY column has the correlation name NATION, you specify this SELECT statement:
SELECT CITY_NAME, COUNTRY AS NATION FROM CITIES ORDER BY NATION
Example using a numeric expression
You can sort the result set by a numeric expression, for example: SELECT name, salary, bonus FROM employee ORDER BY salary+bonus
In this example, the salary and bonus columns are DECIMAL data types. Example using a function You can sort the result set by invoking a function, for example:
SELECT i, len FROM measures ORDER BY sin(i) Example specifying null ordering
You can specify the position of NULL values using the null ordering specification: SELECT * FROM t1 ORDER BY c1 DESC NULLS LAST






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?
: View the Exhibit and examine the structure of the product, component, and PDT_COMP tables.
1. JOIN; JOIN

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


Correct Answer : 3 Outer Joins : An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.

To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.

To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.

You can use outer joins to fill gaps in sparse data. Such a join is called a partitioned outer join and is formed using the query_partition_clause of the join_clause syntax. Sparse data is data that does not have rows for all possible values of a dimension such as time or department. For example, tables of sales data typically do not have rows for products that had no sales on a given date. Filling data gaps is useful in situations where data sparsity complicates analytic computation or where some data might be missed if the sparse data is queried directly.






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?

: View the Exhibit for the structure of the student and faculty tables.
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.

Correct Answer : 4

Explanation: NATURAL JOIN operation
A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.

If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order):

All the common columns
Every column in the first (left) table that is not a common column
Every column in the second (right) table that is not a common column
An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to every column of that table that is not a common column.

If a common column is referenced without being qualified by a table name, the column reference points to the column in the first (left) table if the join is an INNER JOIN or a LEFT OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a common column point to the column in the second (right) table.

Syntax
TableExpression NATURAL [ { LEFT | RIGHT } [ OUTER ] | INNER ] JOIN { TableViewOrFunctionExpression | ( TableExpression ) }
Examples
If the tables COUNTRIES and CITIES have two common columns named COUNTRY and COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:

SELECT * FROM COUNTRIES NATURAL JOIN CITIES
SELECT * FROM COUNTRIES JOIN CITIES
USING (COUNTRY, COUNTRY_ISO_CODE)
The following example is similar to the one above, but it also preserves unmatched rows from the first (left) table:

SELECT * FROM COUNTRIES NATURAL LEFT JOIN CITIES


Related Questions

Question : View the Exhibit and
examine the structure of the customers table.
Using the customers table, you need to generate
a report that shows the average credit limit for
customers in Washington and NEW YORK.
Which SQL statement would produce the required result?
 : View the Exhibit and
1. A
2. B
3. Access Mostly Uused Products by 50000+ Subscribers
4. D




Question : View the Exhibit and examine the data in the employees table:
You want to display all the employee names and their corresponding manager names.
Evaluate the given query:
Which join option can be used in the blank in the above query to get the required output?

 : View the Exhibit and examine the data in the employees table:
1. INNER JOIN
2. FULL OUTER JOIN
 3. Access Mostly Uused Products by 50000+ Subscribers
4. RIGHT OUTER JOIN



Question : Evaluate the following query:
What would be the outcome of the above query?
 : Evaluate the following query:
1. It produces an error because flower braces have been used.
2. It produces an error because the data types are not matching.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. It executes successfully and displays the literal "{'s start date was \} * for each row in the output.



Question : Examine the structure of the transactions table:
You want to display the date, time, and transaction amount of transactions that where done before
12 noon. The value zero should be displayed for transactions where the transaction amount has
not been entered.
Which query gives the required result?

 : Examine the structure of the transactions table:
1. A
2. B
 3. Access Mostly Uused Products by 50000+ Subscribers
4. D




Question : What is the outcome?
 : What is the outcome?
1. It executes successfully and gives the correct output.
2. It executes successfully but does not give the correct output.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. It generates an error because the SUBSTR function cannot be nested in the CONCAT function.



Question : Evaluate the following SQL commands:
The command to create a table fails. Identify the two reasons for the SQL statement failure?
A. You cannot use SYSDATE in the condition of a check constraint.
B. You cannot use the BETWEEN clause in the condition of a check constraint.
C. You cannot use the NEXTVAL sequence value as a default value for a column.
D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the foreign key.
 : Evaluate the following SQL commands:
1. A,B
2. B,C
 3. Access Mostly Uused Products by 50000+ Subscribers
4. C,D
5. B,D