Born to Thrive

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)
A.SELECT ename
FROM emp JOIN salgrade
USING (sal BETWEEN losal AND hisal) AND grade = 5;
B. SELECT ename
FROM emp e JOIN salgrade s
ON (e.sal BETWEEN s.losal AND s.hisal AND s.grade = 5);
C. SELECT ename
FROM emp e JOIN salgrade s
ON (e.sal BETWEEN s.losal AND s.hisal) AND s.grade = 5;
D. SELECT ename
FROM emp e JOIN salgrade s
ON (e.sal BETWEEN s.losal AND s.hisal) WHERE s.grade=5;
E. SELECT ename
FROM emp e JOIN salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal AND s.grade = 5;

Exhibit:
EMP table:

SALGRADE table:

Theory:

This question tests the ability to use joins in SELECT queries. A lot of very useful information on JOIN can be found on the following website - Creating and maintaining joins and here as far as select statement - SELECT. Here is the syntax of using JOIN in the SELECT queries:
All the queries in the question contain only the INNER JOIN. An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition. When you join more than two row sources, you can use parentheses to override default precedence. For example, the following syntax: SELECT ... FROM a JOIN (b JOIN c) ... results in a join of b and c, and then a join of that result set with a. You can specify INNER JOIN to explicitly specify an inner join.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. ON clause is required unless a join is using USING keyword or it is NATURAL JOIN. 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. Last but not least, JOIN can be equijoin or non-equijoin. Equijoins use the eqality operator (=) to join tables whereas non-equijoins use an operator other than the equality operator in the join. These operators are not-equal (<>), less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=), LIKE, IN and BETWEEN.

Answer:

A - Wrong - USING requires column name not condition
B - Correct
C - Correct
D - Correct
E - Wrong - JOIN requires ON keyword if joins without USING keyword