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

Question : Select the clauses of the SELECT statement which help for selection and projection?

1. SELECT, FROM
2. ORDER BY, WHERE
3. Access Mostly Uused Products by 50000+ Subscribers
4. SELECT, ORDER BY

Correct Answer : Get Lastest Questions and Answer :

Explanation:






Question : Which of the following WHERE clauses contains an error? The SELECT and FROM clauses are SELECT * FROM HADOOPEXAM:

1. WHERE JOIN_DATE IN ('02-JUN-2004');
2. WHERE USER_ID IN ('1000','4000','2000');
3. Access Mostly Uused Products by 50000+ Subscribers
4. WHERE RANK BETWEEN 0.1 AND 0.5;


Correct Answer : Get Lastest Questions and Answer :

Explanation: Character literal should be in single quote e.g. 'MISS'





Question : Choose the WHERE clause that extracts the USER_NAME values containing the character literal "ab" from the DEPARTMENTS table.
The SELECT and FROM clauses are SELECT USER_NAME FROM DEPARTMENTS:

1. WHERE USER_NAME IN ('%a%b');
2. WHERE USER_NAME LIKE '%ab%';
3. Access Mostly Uused Products by 50000+ Subscribers
4. WHERE USER_NAME CONTAINS 'a%b';



Correct Answer : Get Lastest Questions and Answer :

Explanation:

Related 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



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



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



Question : What value is returned after executing the following statement?

SELECT SUM((AVG(LENGTH(NVL(CLICKS,0))))) FROM USERS GROUP BY CLICKS;

Assume there are ten user records and each contains a CLICKS value of 100, except for one, which has a null value in the CLICKS field.




1. An error is returned
2. 3
3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above



Question : How would I write an Oracle DELETE statement to delete all records in TableA whose data in field and field
DO NOT match the data in fieldx and fieldz of TableB?




1. DELETE FROM TableA
WHERE NOT EXIST
( SELECT *
FROM TableB
WHERE TableA.field1 = TableB.fieldx
AND TableA.field2 = TableB.fieldz );
2. DELETE FROM TableA
WHERE IN
( SELECT *
FROM TableB
WHERE TableA.field1 = TableB.fieldx
AND TableA.field2 = TableB.fieldz );
3. Access Mostly Uused Products by 50000+ Subscribers
WHERE NOT IN
( SELECT *
FROM TableB
WHERE TableA.field1 = TableB.fieldx
AND TableA.field2 = TableB.fieldz );
4. DELETE FROM TableA
WHERE NOT EXISTS
( SELECT *
FROM TableB
WHERE TableA.field1 = TableB.fieldx
AND TableA.field2 = TableB.fieldz );



Question : Select the statement which is not correct ?

1. SELECT department, SUM(sales) AS "Total sales"
FROM order_details GROUP BY department HAVING SUM(sales) > 25000;
2. SELECT department, COUNT(*) AS "Number of employees"
FROM employees WHERE salary & lt; 49500 GROUP BY department HAVING COUNT(*) > 10;
3. Access Mostly Uused Products by 50000+ Subscribers
FROM employees GROUP BY department HAVING MIN(salary) & lt; 42000;
4. 1 and 3

5. None of the above