Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.1 SQL commands (SELECT statement)

Sample Question


There is an emp table that records employee information and a dept table that records department name information.

=> select * from emp;
 emp_id | emp_name | dept_id
      1 | Ichiro |       1
      2 | KIKUCHI Yusei |       1
      3 | MATSUI Hideki |       2
      4 | MATSUZAKA Daisuke |       3
(4 lines)

=> select * from dept;
 dept_id |   dept_name
       1 | Mariners
       2 | Yankees
       3 | Red Sox
(3 lines)

You want to combine these tables to create a list that displays employee names and department names. What is the keyword that goes in the next SELECT statement?

select emp_id, emp_name, dept_name from emp join dept ____ emp.dept_id = dept.dept_id

  1. AT

  2. IN

  3. ON

  4. USING

  5. WITH

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

There are several ways to write a SELECT statement that joins tables, but the standard and flexible approach is to specify the name of the table to be joined in the JOIN clause, and then write a conditional expression for the join in the ON clause. In the given example, the dept table is joined to the emp table, with emp.dept_id = dept.dept_id specified as the join condition. Therefore, the keyword that should fill the blank is "ON".

So the correct answer is C.

If two tables use the same column names, as in this question, you can use the USING keyword for convenience, followed by just the column names in list format. That is, you would specify it as JOIN dept USING (dept_id). Note that the column name must be enclosed in parentheses in this case.

If the only columns with the same name in the two tables are the ones you want to join, a simpler alternative is to specify NATURAL JOIN dept. In the example, the columns of employee name and department name are named differently (emp_name and dept_name, respectively), but it's common to see implementations where both are simply named "name". Be aware that if you use NATURAL JOIN in such a case, the "name" column will also be used for the join.