Sample Exam Questions
From the objective of OSS-DB Exam Silver
S3.1 SQL commands (SELECT statement)
Sample Question
3.29
There are two tables: the 'dept' table, which stores the department ID and department name, and the 'emp' table, which stores the employee ID, employee name, date of birth, and department ID. Each has the following structure:
=> \d dept
Table "public.dept"
Columns | Type | Collation | Null allowed | Default
-------------+-------------------+----------+---------------+---------
dept_id | integer | | not null |
dept_name | character varying | | |
Index:
"dept_pkey" PRIMARY KEY, btree (dept_id)
=> \d emp
Table "public.emp"
Columns | Type | Collation | Null allowed | Default
-------------+--------------------+----------+--------------+---------
emp_id | integer | | not null |
emp_name | character varying | | |
dob | date | | |
dept_id | integer | | |
Index:
"emp_pkey" PRIMARY KEY, btree (emp_id)
Choose three appropriate SELECT statements to join these tables and display a list of employees with their department names.
-
select * from emp, dept join dept_id;
-
select * from emp e, dept d where e.dept_id = d.dept_id;
-
select * from emp natural join dept;
-
select * from emp join dept using (dept_id);
-
select * from emp e join dept d where e.dept_id = d.dept_id;
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2024/05/02
Answer and Explanation
In relational databases, related data is often divided into multiple tables through normalization, making it very common to join and use them. This process of joining tables is called a JOIN, and there are several ways to write SQL for it. If you're writing your own SQL, you may only need to understand a few of these methods. However, you might also encounter JOINs written in different styles by others, so it's important to have a general understanding.
One method for joining involves listing all the tables to be joined in the FROM clause, separated by commas, and writing the conditions for the join in the WHERE clause. In the example, the dept_id column of the emp table is joined with the dept_id column of the dept table, resulting in a statement like option B.
Another method involves writing the table to be joined in a JOIN clause after FROM. There are three main ways to write the join condition in this case:
One method is to write the join condition in the ON clause, which can be achieved by replacing 'where' with 'on' in option E.
If the column names used for joining are the same in both tables, as in this example, you can use the USING clause. By specifying the common column names used for joining in parentheses, you can replace the logical expressions written in the ON clause. This is represented as option D.
By specifying NATURAL before JOIN, you can also omit the USING clause. This is represented as option C. However, in this case, if there are multiple columns with the same name in both tables, all of them will be used as join conditions. Therefore, if this is not the desired behavior, you must specify the columns to be used for joining using the USING clause.
Options A and E will result in syntax errors and cannot be executed.
Therefore, the correct answers are B, C, and D.
Although most joins can be written using only FROM and WHERE, you need to use JOIN when performing an outer join. Also, using JOIN allows you to write clear SQL, with data filtering conditions organized in the WHERE clause and table join conditions in the ON (or USING) clause. Therefore, it is recommended to use JOIN when performing joins.
© EDUCO All Rights Reserved.