DB2 Join Interview Questions

Give an SQL example on how to retrieve values from 2 related tables.

Answer:
The SQL syntax below is correct for DB2 (and most other SQL databases) to retrieve values from two related tables using a join.
Here is an analysis.
```
SELECT NAME, D.DEPTNO, DEPTNAME 
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
```
Explanation:
1. Tables Involved:
  - EMP (aliased as E) - presumably an Employee table containing employee details like NAME and DEPTNO (department number).
  - DEPT (aliased as D) - a Department table containing department details like DEPTNO and DEPTNAME.
2. Join Type:
  - This is an implicit inner join written in the older SQL style (pre-ANSI-92), where the join condition is specified in the WHERE clause rather than using the JOIN keyword. In DB2, this syntax is still valid and widely supported.
3. Columns Selected:
  - NAME (from the EMP table, no alias needed since it’s unambiguous).
  - D.DEPTNO (department number from the DEPT table, qualified with the alias D to avoid ambiguity since DEPTNO exists in both tables).
  - DEPTNAME (from the DEPT table).
4. Join Condition:
  - WHERE E.DEPTNO = D.DEPTNO ensures that rows from EMP and DEPT are matched based on the common DEPTNO column, linking employees to their respective departments.
Equivalent Explicit Join (Modern Syntax):
In modern SQL (ANSI-92 style), which is also fully supported in DB2, this could be rewritten as:
```
SELECT E.NAME, D.DEPTNO, D.DEPTNAME 
FROM EMP E
INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
```
Both versions will work in DB2 and produce the same result: a list of employee names along with their department numbers and department names, where the rows are matched based on the DEPTNO column.
Notes on DB2 Compatibility:
- The syntax you provided is valid in DB2 (e.g., IBM DB2 for z/OS, LUW, etc.).
- DB2 supports both the older comma-separated table style with WHERE conditions and the newer explicit JOIN syntax.
- Ensure the tables (EMP and DEPT) and columns (NAME, DEPTNO, DEPTNAME) exist in your specific DB2 database schema.

How do you retrieve data from 2 tables using a foreign key?

Answer:
The foreign key of the child table maps to the primary key of the parent table.
See the following page for a diagram outlining parent-child relationships.

Give examples of the various types of joins that you know?

Answer:
1. Inner Join
```
SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield 
```
2. To rewrite the given INNER JOIN as an OUTER JOIN, you can use a LEFT OUTER JOIN, RIGHT OUTER JOIN, or a FULL OUTER JOIN, depending on the desired behavior:
  1. LEFT OUTER JOIN:
    - Includes all rows from first_table and the matching rows from second_table.
    - Rows in first_table without a match in second_table will still appear, but fields from second_table will be NULL for those rows.
```
SELECT field1, field2, field3
FROM first_table
LEFT OUTER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield;
        
```
  2. RIGHT OUTER JOIN:
    - Includes all rows from second_table and the matching rows from first_table.
    - Rows in second_table without a match in first_table will still appear, but fields from first_table will be NULL for those rows.
```
SELECT field1, field2, field3
FROM first_table
RIGHT OUTER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield;
        
```
  3. FULL OUTER JOIN:
    - Includes all rows from both first_table and second_table.
    - Rows without a match in either table will have NULL in the corresponding columns.
```
SELECT field1, field2, field3
FROM first_table
FULL OUTER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield;
        
```
  Notes:
  - INNER JOIN returns only the matching rows.
  - LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN will include additional rows with `NULL` where there is no match in the corresponding table. Choose the type of outer join based on your specific requirements.
  Outer join: This is a join whereby rows that do not have a match in both tables are also displayed.

How are cursors used in Oracle 8i, 9i, 10G?

Answer:
(Cursor) = current set of records
The cursor is an extremely important PL/SQL construct. It is at the heart of PL/SQL and SQL cooperation and stands for current set of records. A cursor is a special PL/SQL element that has an associated SQL SELECT statement. Using a cursor, each row of the SQL statement associated with the cursor can be processed one row at a time.

DB2 Certification

What is a one-to-one relationship in a 3rd normal form data model?

Answer:
Each row in table A has exactly one corresponding row in table B. In relational database design, a one-to-one (1:1) relationship exists when zero or one instance of entity A can be associated with zero or one instance of entity B, and zero or one instance of entity B can be associated with zero or one instance of entity A.
See also
1. First, Second, Third Normal-Forms
2. one-to-one relationship

Give a detailed example of an inner join.

Answer:
Depict the following scenario Table A in our database represents Plumbers and Table B represents Electricians. The intersection between the 2 sets Table A and Table B represents the plumbers that are also electricians.
In such a scenario, the SQL query which retrieves all those plumbers who are electricians as well can be written as:
```
SELECT * FROM A 
INNER JOIN B ON A.SID=B.SID 
```
SID is the social identity number (assuming every individual has a unique number)

What is the difference between an Inner and Outer joins?

Answer:
If you don't specify whether a join is an INNER JOIN, a LEFT OUTER JOIN, a RIGHT OUTER JOIN, or a FULL OUTER JOIN, then INNER JOIN is the default.
That is why INNER JOIN is the same as just JOIN.

Give an example of a left outer join:

Answer:
Depict the following scenario.
Table A in our database represents Plumbers and Table B represents Electricians. The intersection between the 2 sets Table A and Table B represents the plumbers that are also electricians.
```
A = Plumbers, B = Electricians
```
Now if you wish to write a query which enlists all the plumbers and also those electricians which are plumbers, you will need a Left Outer Join.
Left Outer Join

The query can then be written as
```
SELECT A.NAME, B.NAME FROM A LEFT JOIN B ON A.SID=B.SID
```

Give an example of a right outer join.

Depict the following scenario. Table A in our database represents Plumbers and Table B represents Electricians.
A = Plumbers, B = Electricians

Right Outer Join

Answer:
See Diagram above: In the case of the right outer join, all those records which are from the second table and those records from the first table which meet the matching criteria are selected as results. We are required to list down all the Electricians and only those Plumbers who are also Electricians, we shall make use of Right Outer Join. Therefore in such a scenario the SQL Query will look like this
SELECT A.NAME, B.NAME FROM A RIGHTJOIN B ON A.SID=B.SID

What is the difference between clustered and nonclustered indexes?

Answer:
There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore a table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.