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
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:
Inner Join
SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
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:
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;
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;
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.
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
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.
