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

Question : How many rows of data are returned after executing the following statement?
SELECT USER_ID, SUM(NVL(CLICKS,100) FROM USERS GROUP BY USER_ID HAVING SUM(CLICKS) >400
Assume the USERS table has ten rows and each contains a CLICKS value of 100, except for one, which has a null value in the CLICKS field.
The first and second five rows have USER_ID values of 10 and 20, respectively.

1. 2
2. 1
3. Access Mostly Uused Products by 50000+ Subscribers
4. None


Correct Answer : Get Lastest Questions and Answer :

Explanation: Two groups are created based on their common USER_ID values. The group with no null CLICKS values consists of five rows with CLICKS values of 100 in each of them. Therefore, the SUM(CLICKS) function returns 500 for this group, and it satisfies the HAVING SUM(CLICKS) > 400 clause. The group with the null CLICKS record has four rows with CLICKS values of 100 and one row with a NULL CLICK. SUM(CLICKS) only returns 400 and this group does not satisfy the HAVING clause.





Question : Select the correct update statement syntex

1. UPDATE table
SET column1 = expression1,
column2 = expression2,
...
WHERE conditions;
2. UPDATE table1
SET column1 = (SELECT expression1
FROM table2
WHERE conditions)
WHERE conditions;
3. Access Mostly Uused Products by 50000+ Subscribers
SET state = 'California',
customer_rep = 32
WHERE customer_id > 100;
4. Only 1 and 3
5. All 1,2 and 3


Correct Answer : Get Lastest Questions and Answer :

Explanation: The Oracle UPDATE statement is used to update existing records in a table in an Oracle database. There are 2 syntaxes for an update query in Oracle depending on whether you are performing a traditional update or updating one table with data from another table.
SYNTAX : The syntax for the Oracle UPDATE statement when updating one table is:
UPDATE table SET column1 = expression1, column2 = expression2,
... WHERE conditions;
OR

The syntax for the Oracle UPDATE statement when updating one table with data from another table is:

UPDATE table1 SET column1 = (SELECT expression1 FROM table2 WHERE conditions)
WHERE conditions;
Parameters or Arguments : column1, column2 are the columns that you wish to update. expression1, expression2 are the new values to assign to the column1, column2. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on. conditions are the conditions that must be met for the update to execute.




Question : What values are returned after executing the following statement?

SELECT USER_ID,MAX_CLICKS FROM USERS GROUP BY MAX_CLICKS;

Assume that the USERS table has ten records with the same USER_ID value of DBA and the same MAX_CLICKS value of 100.


1. One row of output with the values DBA, 100
2. Ten rows of output with the values DBA, 100
3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above


Correct Answer : Get Lastest Questions and Answer :

Explanation: For a GROUP BY clause to be used, a group function must appear in the SELECT list. 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 : What value is returned after executing the following statement?
REPLACE('123123tech', '123');

1. 123
2. tech
3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above




Question : What value is returned after executing the following statement?
REPLACE('0000123', '0', ' ');

1. 123
2. 4 space and 123
3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above




Question : What value is returned after executing the following statement?
MOD(15, 4)

1. 3
2. 4
3. Access Mostly Uused Products by 50000+ Subscribers
4. Null




Question : What value is returned after executing the following statement?
MOD(-100, 0)

1. 100
2. 0
3. Access Mostly Uused Products by 50000+ Subscribers
4. Null




Question : What value is returned after executing the following statement?
ADD_MONTHS('01-Aug-03', 3)

1. 01-Nov-03
2. 01-Nov-06
3. Access Mostly Uused Products by 50000+ Subscribers
4. 01-Sep-03




Question : What value is returned after executing the following statement?
ADD_MONTHS('21-Aug-03', -3)

1. 21-Jan-03
2. 21-Mar-03
3. Access Mostly Uused Products by 50000+ Subscribers
4. 21-Nov-03