Oracle DB interview questions

SQL join

Inner join
An inner join essentially combines the records from two tables (A and B) based on a

given join-predicate

An equi-join (also known as an equijoin), a specific type of comparator-based join,

or theta join, uses only equality comparisons in the join-predicate

A natural join offers a further specialization of equi-joins. The join predicate

arises implicitly by comparing all columns in both tables that have the same column-name in

the joined tables.

A cross join returns the cartesian product of the sets of records from the two

joined tables


Outer join
An outer join does not require each record in the two joined tables to have a

matching record in the other table
The result of a left outer join for tables A and B always contains all records of

the "left" table (A), even if the join-condition does not find any matching record in the

"right" table (B).
A right outer join closely resembles a left outer join, except with the tables

reversed
A full outer join combines the results of both left and right outer joins

============================================================================================
Join algorithms
============================================================================================
Nested loops : produces the simplest join-algorithm. For each tuple in the outer join

relation, the system scans the entire inner-join relation and appends any tuples that match

the join-condition to the result set. Naturally, this algorithm performs poorly with large

join-relations: inner or outer or both.

Merge join: Merge joins offer one reason why many optimizers keep track of the sort order

produced by query plan operators

A hash join algorithm can produce equi-joins. The database system pre-forms access to the

tables concerned by building hash tables on the join-attributes. The lookup in hash tables

operates much faster than through index trees.

SQL Queries

1. To remove duplicate elements
   

	delete from employee
 	 where (empid, empssn)
  	  not in
   	 ( select min(empid), empssn 
     	    from employee group by empssn);

Database normalization is the process of organizing data into distinct and unique sets.

The purposes of normalization are to:

• Reduce or eliminate storage of duplicate data
  
• Organize data into an efficient and logical structure
  

The process of normalization involves determining what data should be stored in each database table.

By tradition, the process of normalization involves working through well-defined steps, called normal forms.

In First Normal Form (1NF) you eliminate duplicate columns from the
same table, create separate tables for each group of related data, and
identify each row with a unique column or set of columns (the primary
keys).

In Second Normal Form (2NF) you remove subsets of data that
apply to multiple rows of a table, place them in separate tables, and
create relationships between these new tables and the original tables
through the use of foreign keys.

In Third Normal Form (3NF) you remove columns that are not dependent upon the primary key.

Additional normal forms have been defined, but are less commonly
utilized. These advanced normal forms include Fourth Normal Form (4NF),
Fifth Normal Form (5NF), Boyce Codd Normal Form (BCNF), and Domain-Key
Normal Form (DK/NF).