Premium

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



Question : Which statement is true regarding the default behavior of the order by clause?
: Which statement is true regarding the default behavior of the order by clause?
1. In a character sort, the values are case-sensitive.
2. NULL values are not considered at all by the sort operation.
3. Only those columns that are specified in the select list can be used in the order by clause.
4. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.


Correct Answer : 1

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 LASTCharacter Strings and Dates Character strings and date values are enclosed with single quotation marks. Character values are case-sensitive and date values are format-sensitive. The default date display format is DD-MON-RR.








Question : View the Exhibits and
examine the structures of
the products and sales tables.
Which two SQL statements
would give the same output?

: View the Exhibits and
1. A,C
2. B,D
3. C,D
4. A,D
5. B,C


Correct Answer : 1
Explanation: The UNION [ALL], INTERSECT, MINUS Operators You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order. The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character). If component queries select character data, then the datatype of the return values are determined as follows: If both queries select values of datatype CHAR of equal length, then the returned values have datatype CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value.
If either or both of the queries select values of datatype VARCHAR2, then the returned values have datatype VARCHAR2.
If component queries select numeric data, then the datatype of the return values is determined by numeric precedence:
If any query selects values of type BINARY_DOUBLE, then the returned values have datatype BINARY_DOUBLE.
If no query selects values of type BINARY_DOUBLE but any query selects values of type BINARY_FLOAT, then the returned values have datatype BINARY_FLOAT.

If all queries select values of type NUMBER, then the returned values have datatype NUMBER.Examples
The following query is valid:
SELECT 3 FROM DUAL INTERSECT SELECT 3f FROM DUAL;
This is implicitly converted to the following compound query:
SELECT TO_BINARY_FLOAT(3) FROM DUAL INTERSECT SELECT 3f FROM DUAL;
The following query returns an error:
SELECT '3' FROM DUAL INTERSECT SELECT 3f FROM DUAL;
Restrictions on the Set Operators
The set operators are subject to the following restrictions:
The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table.
The UNION, INTERSECT, and MINUS operators are not valid on LONG columns.
If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the order_by_clause. You cannot also specify the for_update_clause with the set operators. You cannot specify the order_by_clause in the subquery of these operators.
You cannot use these operators in SELECT statements containing TABLE collection expressions. INTERSECT Example
The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:
SELECT product_id FROM inventories INTERSECT SELECT product_id FROM order_items;
MINUS Example The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second:
SELECT product_id FROM inventories MINUS SELECT product_id FROM order_items;




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



Correct Answer : 2
Explanation:


Related Questions

Question : Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);
SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4),
CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));

You executed the following statement:
SQL> DELETE from stores
WHERE store_id=900;

The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated

Which three options ensure that the statement will execute successfully?

A. Disable the primary key in the STORES table.
B. Use CASCADE keyword with DELETE statement.
C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
D. Disable the FOREIGN KEY in SALES table and then delete the rows.
E. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.
 : Examine the create table statements for the stores and sales tables.
1. A,C,D

2. C,D,E
3. B,C,E

4. C,D,E
5. A,C,E


Question : You want to create a sales
table with the following column
specifications and data types:

SALESID: Number
STOREID: Number
ITEMID: Number
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified
PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified

Which statement would create the table?

 : You want to create a sales
1. A
2. B
3. C
4. D


Question : Examine the data in the CUST_NAME column of the customers table.
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?
 : Examine the data in the CUST_NAME column of the customers table.
1. A
2. B
3. C
4. D


Question : : Evaluate the following query:
SQL> SELECT TRUNC(ROUND(156.00, -1), -1) FROM DUAL;
What would be the outcome?
 : : Evaluate the following query:
1. 16
2. 100
3. 160
4. 200
5. 150


Question : You want to display percent of the rows from
the sales table for products with the lowest
AMOUNT_SOLD and also want to include the rows that
have the same AMOUNT_SOLD even if
this causes the output to exceed 5 percent of the rows.
Which query will provide the required result?
 : You want to display percent of the rows from
1. A
2. B
3. C
4. D


Question : You need to list the employees
in DEPARTMENT_ID 30 in a single row, ordered by HIRE_DATE.
Examine the sample output:
Which query will provide the required output?
 : You need to list the employees
1. A
2. B
3. C
4. D