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

Question :

SELECT * FROM TABLE1 WHERE CLICKS > (SELECT AVG(CLICKS) FROM USERS);

When will the subquery be executed?



1. It will be executed before the outer query.
2. It will be executed after the outer query.
3. Access Mostly Uused Products by 50000+ Subscribers
4. It will be executed once for every row in the EMPLOYEES table.


Correct Answer : Get Lastest Questions and Answer :

Explanation: The result set of the inner query is needed before the outer query can run.










Question : SELECT A.USER_ID, A.LAST_NAME FROM TABLE A WHERE A.CLICKS> (SELECT avg(B.CLICKS) FROM TABLE B WHERE B.click_id=A.click_id);
When will the subquery be executed?




1. It will be executed before the outer query.
2. It will be executed after the outer query.
3. Access Mostly Uused Products by 50000+ Subscribers
4. It will be executed once for every row in the EMPLOYEES table.




Correct Answer : Get Lastest Questions and Answer :

Explanation: This is a correlated subquery which must be run for every row in the table.









Question : What are the distinguishing characteristics of a scalar subquery?


1. A scalar subquery returns one row.
2. A scalar subquery cannot be used in the SELECT LIST of the parent query.
3. Access Mostly Uused Products by 50000+ Subscribers
4. 1 and 2
5. All 1,2 and 3



Correct Answer : Get Lastest Questions and Answer :

Explanation: A scalar subquery can be defined as a query that returns a single value.

Related Questions

---

Question : What value is returned after executing the following statement?
SELECT suppliers.name, subquery1.total_amt
FROM suppliers,
(SELECT supplier_id, SUM(orders.amount) AS total_amt
FROM orders
GROUP BY supplier_id) subquery1
WHERE subquery1.supplier_id = suppliers.supplier_id;

1. It would be cross product between suppliers and subquery1
2. All rows from suppliers and subquery1 where condition match
3. Access Mostly Uused Products by 50000+ Subscribers
4. Error




Question : What value is returned after executing the following statement?
SELECT last_name
FROM contacts
WHERE REGEXP_LIKE (last_name, '^A(*)');

1. This REGEXP_LIKE example will return all contacts whose last_name starts with 'A'.
2. This REGEXP_LIKE example will return all contacts whose last_name ends with 'A'.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Error




Question : What value is returned after executing the following statement?
SELECT last_name FROM contacts WHERE REGEXP_LIKE (last_name, '(*)A$');

1. This REGEXP_LIKE example will return all contacts whose last_name starts with 'A'.
2. This REGEXP_LIKE example will return all contacts whose last_name ends with 'A'.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Error


Question : Consider following statements..
CREATE TABLE employees
( employee_number number(10) not null,
employee_name varchar2(50) not null,
salary number(6),
CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'John Smith', 62000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1002, 'Jane Anderson', 57500);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, 'Brad Everest', 71000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1004, 'Jack Horvath', 42000);

What would be the output of following statement
SELECT * FROM employees WHERE employee_name LIKE '%h';


1. Rows containing employee name 'Jack Horvath'
2. Rows containing employee name 'Brad Everest'
3. Access Mostly Uused Products by 50000+ Subscribers
4. Rows containing employee name 'John Smith' and 'Jack Horvath'


Question : Which of the query depict the given shared area ( Blue is a shared area)


1. SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
2. SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
3. Access Mostly Uused Products by 50000+ Subscribers
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
4. SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;


Question : Which of the query depict the given shared area ( Blue is a shared area)


1. SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
2. SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
3. Access Mostly Uused Products by 50000+ Subscribers
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
4. SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;