Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)
Question : Which join is performed by the following query? (Choose the best answer.)
SELECT E.USER_ID, J.CLICK_ID FROM TABLE1 E and TABLE2 J ON (E.CLICKS < J.MAX_CLICKS);
1. Equijoin
2. Nonequijoin
3. Access Mostly Uused Products by 50000+ Subscribers
4. Outer join
Correct Answer : Get Lastest Questions and Answer :
Explanation: The join condition is an expression based on the less than inequality operator. Therefore, this join is a nonequijoin.
Question : Which of the following statements are syntactically correct?
1. SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D USING (DEPARTMENT_ID);
2. SELECT * FROM EMPLOYEES JOIN DEPARTMENTS D USING (D.DEPARTMENT_ID);
3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above
Correct Answer : Get Lastest Questions and Answer :
Explanation: ou're presumably already aware of the distinction, but from the documentation: ON condition Use the ON clause to specify a join condition. Doing so lets you specify join conditions separate from any search or filter conditions in the WHERE clause. USING (column) When you are specifying an equijoin of columns that have the same name in both tables, the USING column clause indicates the columns to be used. You can use this clause only if the join columns in both tables have the same name. Within this clause, do not qualify the column name with a table name or table alias.
So these would be equivalent:
select e.ename, d.dname
from emp e join dept d using (deptno);
select e.ename, d.dname
from emp e join dept d on d.deptno = e.deptno;
To a large extent which you use is a matter of style, but there are (at least) two situations where you can't use using: (a) when the column names are not the same in the two tables, and (b) when you want to use the joining column:
select e.ename, d.dname, d.deptno
from emp e join dept d using(deptno);
select e.ename, d.dname, d.deptno
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier
You can of course just leave off the qualifier and select ..., deptno, as long as you don't have another table with the same column that isn't joined using it:
select e.ename, d.dname, deptno
from emp e join dept d using (deptno) join mytab m using (empno);
select e.ename, d.dname, deptno
*
ERROR at line 1:
ORA-00918: column ambiguously defined
Question : The EMPLOYEES and DEPARTMENTS tables have two identically named columns: DEPARTMENT_ID and MANAGER_ID.
Which of these statements joins these tables based only on common DEPARTMENT_ID values?
1. SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;
2. SELECT * FROM EMPLOYEES E NATURAL JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;
3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above
Correct Answer : Get Lastest Questions and Answer :
Explanation: . The queries in 2 and 3 incorrectly contain the NATURAL keyword. If this is removed, they will join the DEPARTMENTS and EMPLOYEES tables based on the DEPARTMENT_ID column.
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;