RelationalDBDesign RelationalDBDesign





DB2 Questions   «Prev 

DB2 Interview Questions 8

  1. Give an example SQL statement of how to retrieve values from 2 related tables.
    Answer:
    Select NAME, D.DEPTNO, DEPTNAME 
    FROM EMP E, DEPT D
    WHERE E.DEPTNO = D.DEPTNO
    
  2. 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.
  3. 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. Outer join : This is a join whereby rows that do not have a match in both tables are also displayed.
  4. 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.

  5. 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 one-to-one relationship
  6. 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)
  7. 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.

  8. 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
    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
    
  9. 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
    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
  10. 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.