Interview Question base on SQL joining

Question and Answer(SQL JOINING)

1. View the Exhibit and examine the structure of the EMP and SALGRADE tables. You want to display the names of all employees whose salaries belong to GRADE 5. Which SQL statements give the required output? (Choose all that apply)

1. SELECT ename
   FROM emp JOIN salgrade
   USING (sal BETWEEN losal AND hisal) AND grade = 5;
2. SELECT ename
   FROM emp e JOIN salgrade s
   ON (e.sal BETWEEN s.losal AND s.hisal AND s.grade = 5);
3. SELECT ename
   FROM emp e JOIN salgrade s
   ON (e.sal BETWEEN s.losal AND s.hisal) AND s.grade = 5;
4. SELECT ename
   FROM emp e JOIN salgrade s
   ON (e.sal BETWEEN s.losal AND s.hisal) WHERE s.grade=5;
5. SELECT ename
   FROM emp e JOIN salgrade s
   WHERE e.sal BETWEEN s.losal AND s.hisal AND s.grade = 5;
   
   table "Exhibit" 

	EMP
	Name		Null?		Type
	EMPNO ENAME JOB HIREDATE SAL DEPTNO		NOT NULL		NUMBER(4) VARCHAR2(10) VARCHAR2(9) DATE NUMBER(7,2) NUMBER(2)
	SALGRADE
	Name		Null?		Type
	GRADE LOSAL HISAL				NUMBER NUMBER NUMBER

2. View the Exhibit and examine the structure of the DEPARTMENTS and LOCATIONS tables. You want to display all the cities and the corresponding departments in them, if any. Which query would give you the required output? 

1. SELECT location_id LOC, city, department_id DEPT
   FROM locations LEFT OUTER JOIN departments
   USING (location_id);
2. SELECT location_id LOC, city, department_id DEPT
   FROM locations RIGHT OUTER JOIN departments
   USING (location_id);
3. SELECT l.location_id LOC, l.city, d.department_id DEPT
   FROM locations l LEFT OUTER JOIN departments d
   USING (location_id);
4. SELECT l.location_id LOC, l.city, d.department_id DEPT
   FROM locations l FULL OUTER JOIN departments d
   USING (location_id);
   
   table "Exhibit" 

	DEPARTMENTS
	Name		Null?		Type
	DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID		NOT NULL NOT NULL		NUMBER(4) VARCHAR2(30) NUMBER(6) NUMBER(4)
	LOCATIONS
	Name		Null?		Type
	LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID		NOT NULL NOT NULL		NUMBER(4) VARCHAR2(40) VARCHAR2(12) VARCHAR2(30) VARCHAR2(25) CHAR(2)

3. You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use?

A) SELECT last_name, department_name
FROM employees e LEFT OUTER
JOIN departments d ON (e.department_id = d.department_id);

B) SELECT last_name, department_name
FROM employees e RIGHT OUTER
JOIN departments d ON (e.department_id = d.department_id);

C) SELECT last_name, department_name
FROM employees e FULL OUTER
JOIN departments d ON (e.department_id = d.department_id);

4. A SELECT statement can be used to perform these three functions:

- Choose rows from a table.
- Choose columns from a table.
- Bring together data that is stored in different tables by creating a link between them.
Which set of keywords describes these capabilities?

A) difference, projection, join
B) selection, projection, join
C) selection, intersection, join
D) intersection, projection, join
E) difference, projection, product

5)
In which two cases would you use an outer join? (Choose two.)

A) The tables being joined have NOT NULL columns.
B) The tables being joined have only matched data.
C) The columns being joined have NULL values.
D) The tables being joined have only unmatched data.
E) The tables being joined have both matched and unmatched data.
F) Only when the tables have a primary key/foreign key relationship.

6)
Examine the structure of the EMPLOYEES and DEPARTMENTS tables:

EMPLOYEES

EMPLOYEE_ID NUMBER
DEPARTMENT_ID NUMBER
MANAGER_ID NUMBER
LAST_NAME VARCHAR2(25)

DEPARTMENTS

DEPARTMENT_ID NUMBER
MANAGER_ID NUMBER
DEPARTMENT_NAME VARCHAR2(35)
LOCATION_ID NUMBER



A) SELECT last_name, department_name, location_id FROM employees , departments ;
B) SELECT employees.last_name, departments.department_name, departments.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
C) SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id =manager_id;
D) SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;

7. )
In which three cases would you use the USING clause? (Choose three.)

A) You want to create a nonequijoin.
B) The tables to be joined have multiple NULL columns.
C) The tables to be joined have columns of the same name and different data types.
D) The tables to be joined have columns with the same name and compatible data types.
E) You want to use a NATURAL join, but you want to restrict the number of columns in the join condition.

8)
Which three are true regarding the use of outer joins? (Choose three.)

A) You cannot use IN operator in a condition that involves an outerjoin.
B) You use (+) on both sides of the WHERE condition to perform an outerjoin.
C) You use (*) on both sides of the WHERE condition to perform an outerjoin.
D) You use an outerjoin to see only the rows that do not meet the join condition.
E) In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin.
F) You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator.

9)
What is true about joining tables through an equijoin?

A) You can join a maximum of two tables through an equijoin.
B) You can join a maximum of two columns through an equijoin.
C) You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
D) To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.
E) You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.

10.
Evaluate this SQL statement:

SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMP e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;

In the statement, which capabilities of a SELECT statement are performed?

A) Selection, projection, join
B) Difference, projection, join
C) Selection, intersection, join
D) Intersection, projection, join
E) Difference, projection, product

11.
In which case would you use a FULL OUTER JOIN?

A) Both tables have NULL values.
B) You want all unmatched data from one table.
C) You want all matched data from both tables.
D) You want all unmatched data from both tables.
E) One of the tables has more data than the other.
F) You want all matched and unmatched data from only one table.

12.
Evaluate this SQL statement:

SELECT employee_id, e.department_id, department_name, salary
FROM employees e, departments d
WHERE e.department_id = d.department_id;

Which SQL statement is equivalent to the above SQL statement?

A) SELECT employee_id, department_id, department_name, salary
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments);

B) SELECT employee_id, department_id, department_name, salary
FROM employees
NATURAL JOIN departments;

C) SELECT employee_id, d.department_id, department_name, salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

D) SELECT employee_id, department_id, department_name, salary
FROM employees
JOIN departments
USING (e.department_id, d.department_id);

13)
Which two statements about views are true? (Choose two.)

A) A view can be created as read only.
B) A view can be created as a join on two or more tables.
C) A view cannot have an ORDER BY clause in the SELECT statement.
D) A view cannot be created with a GROUP BY clause in the SELECT statement.
E) A view must have aliases defined for the column names in the SELECT statement

14.

You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use?

A) SELECT last_name, department_name
FROM employees e LEFT OUTER
JOIN departments d ON (e.department_id = d.department_id);

B) SELECT last_name, department_name
FROM employees e RIGHT OUTER
JOIN departments d ON (e.department_id = d.department_id);

C) SELECT last_name, department_name
FROM employees e FULL OUTER
JOIN departments d ON (e.department_id = d.department_id);   

15.

Which are the join types in join condition:

a.       Cross join

b.      Natural join

c.       Join with USING clause

d.      Outer join

e.       Join with ON clause

f.        All of these

16.

-- Join the EMPLOYEE and DEPARTMENT tables,

-- select the employee number (EMPNO),

-- employee surname (LASTNAME),

-- department number (WORKDEPT in the EMPLOYEE table

-- and DEPTNO in the DEPARTMENT table)

-- and department name (DEPTNAME)

-- of all employees who were born (BIRTHDATE) earlier than 1930

Which query should you use to fetch data from tables?

a. SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME 

   FROM SAMP.EMPLOYEE LEFT OUTER JOIN SAMP.DEPARTMENT 

   ON WORKDEPT = DEPTNO 

   AND YEAR(BIRTHDATE) < 1930

b. SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME 

   FROM SAMP.EMPLOYEE LEFT OUTER JOIN SAMP.DEPARTMENT 

   ON WORKDEPT = DEPTNO 

   AND YEAR(BIRTHDATE) <= 1929

c. SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME 

   FROM SAMP.EMPLOYEE LEFT OUTER JOIN SAMP.DEPARTMENT 

   ON WORKDEPT = DEPTNO 

   AND YEAR(BIRTHDATE) =< 1929

d. SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME 

   FROM SAMP.EMPLOYEE LEFT OUTER JOIN SAMP.DEPARTMENT 

   ON WORKDEPT = DEPTNO 

   AND YEAR(BIRTHDATE) < 1930

--match cities to countries in Asia

17. Which query returns the mortgage information for all payments received from customers during the year 2007.

a. 

SELECT customer.account_no, mortgage.mortgage_id, payment.payment_id, payment.amount

 FROM customer

     JOIN mortgage ON mortgage.customer_id = customer.customer_id

     JOIN payment ON payment.mortgage_id = mortgage.mortgage_id

 WHERE payment.YEAR = 2012;

b.

SELECT customer.account_no, mortgage.mortgage_id, payment.payment_id, payment.amount

 FROM customer, 

      mortgage,

      payment

 WHERE mortgage.customer_id = customer.customer_id

 AND   payment.mortgage_id = mortgage.mortgage_id

 AND   payment.YEAR = 2007;

c.

SELECT customer.account_no, mortgage.mortgage_id, payment.payment_id, payment.amount

 FROM customer, 

      mortgage,

      payment

 WHERE customer.customer_id = mortgage.customer_id

 AND   mortgage.mortgage_id = payment.mortgage_id

 AND   payment.YEAR = 2007;

d. 

SELECT customer.account_no, mortgage.mortgage_id, payment.payment_id, payment.amount

 FROM customer, 

      mortgage,

      payment

 WHERE customer.customer_id = mortgage.customer_id

 AND   payment.YEAR = 2007;

18. This joins the customer and order table, connecting the customers to their orders. The result contains a combined list of customers and their orders, if a customer does not have an order, they are omitted from the result.

a.

SELECT c.customer_id, o.order_id

 FROM   customer c, ORDER o

 WHERE  c.customer_id = o.customer_id;

b.

SELECT customer_id, order_id

 FROM customer c

 INNER JOIN ORDER o ON c.customer_id = o.customer_id;

c.   A  and B

d. None of them

19.

Which query correctly uses an outer JOIN that involves the PRODUCT table and another table?

Please select the best answer.

A.            SELECT P.PRODUCT_NAME

FROM PRODUCT P, PET_CARE_LOG PCL

WHERE P.PRODUCT_ID(+) = PCL.PRODUCT_ID(+)

B.            SELECT CS.CUST_ID, P.PRODUCT_NAMEFROM PRODUCT P(+), CUSTOMER_SALE CS, SALE_ITEM SALEWHERE CS.SALES_ID = SALE.SALES_ID AND P.PRODUCT_ID = SALE.PRODUCT_ID

C.            SELECT COUNT(*)FROM PET_CARE_LOGWHERE PRODUCT_ID IN (SELECT SI.PRODUCT_ID FROM PRODUCT P, SALE_ITEM SI WHERE P.PRODUCT_ID(+) = SI.PRODUCT_ID)

D.            SELECT S.SALES_ID, S.SALE_AMOUNT, P.SALE_PRICEFROM SALE_ITEM S, PRODUCT PWHERE P.PRODUCT_ID = S.PRODUCT_ID(+)

20.

What kind of query is best used to create a report listing customers by zip code, and including their associated purchase order numbers if they have made any orders?
Please select the best answer.

A.            A query with an IN clause that uses one column

B.            A query with an outer JOIN between two tables

C.            A query with an IN clause that uses two columns

D.            A query with an inner JOIN between two tables

E.            Left outer join

F.            Outer join

G.           Sub-query

H.            Inner join

Answer

1.  BCD
2.  A
3. A
4. B
5. C,E
6. D
7. C,D,E
8. D,E,F
9. E
10. A
11. D
12. C
13. A,B
14. A
15. F
16. A,b
17. A,b,c
18. C
19. C,D (Choice C is correct because it has correct syntax and uses the PRODUCT table and the SALE_ITEM in an outer join. The outer JOIN is found inside a sub-query in this case, which is acceptable. Choice D is correct because it is syntactically accurate and it uses the PRODUCT table and the SALE_ITEM table in an outer join. Notice that the (+) symbol does not have to be placed on the PRODUCT table's column to involve the PRODUCT table in the outer join. The fact that the PRODUCT table's column is in the same comparison as a column with a (+) symbol indicates that the PRODUCT table is involved in an outer join. Choice A is incorrect because its syntax is wrong. There cannot be a (+) symbol on both sides of a comparison. The last line should be either: WHERE P.PRODUCT_ID(+) = PCL.PRODUCT_ID or WHERE P.PRODUCT_ID = PCL.PRODUCT_ID(+) B is incorrect because the (+) symbol is next to the table in the FROM clause. There should be no symbol in the FROM clause. The WHERE clause should contain the (+) symbol. In order to involve the PRODUCT table, the last line in the query should be: AND P.PRODUCT_ID = SALE.PRODUCT_ID(+))
20.  B ( An outer JOIN lets you include all customers, even those who have not made any orders. At the same time, it also lets you include data FROM the related table containing the customer's orders. Choices A and C are not the best answers. The IN clause usually helps narrow down your selection, rather than adding information to your selection. For example, if you wanted to list only customers who had made purchases, the IN clause might have worked. Choice D is incorrect. If you use an inner join, customers who do not have any orders are not included in your report.)