SQL Basics

1.Types of joins?

Cartesian Products:

A Cartesian product is formed when:

A join condition is omitted

A join condition is invalid

All rows in the first table are joined to all rows in the second table

To avoid a Cartesian product, always include a valid join condition in a WHERE Clause.

Ex:

SELECT ename,dname dept_name FROM emp,dept;

Equi-join:

ATo determine an employee’s department name, we compare the value in the DEPTNO column in the EMP table with the DEPT NO values in the DEPT table. The relationship between the EMP and DEPT tables is an equijoin ie,values in the DEPT NO column on both tables must be equal. Frequently, this type of join involves primary and foreign key complements.Note: Equi-joins are also called simple joins or inner joins.

Ex:

SELECT e.empno,e.ename,e.deptno,d.deptno FROM emp e,dept d WHERE e.deptno = d.deptno;

Non-Equijoins:

A Non-equijoin is a join condition containing something other than a equality operator. The relationship between the EMP table and the SALGRADE table has an example of a non-equijoin. A relationship between the two tables is that the SALARY column in the EMP table must be between the values in the LOSAL and HISAL columns of the SALGRADE table. The relationship is obtained using an operator other than equals(=).

Ex:

SELECT e.ename,e.sal,s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

Outer Joins:

The missing rows can be returned if an outer join operator is used in the join condition. The operator is a plus sign enclosed in parentheses(+), and it is placed on the “side” of the join that is deficient in information. This operator has the effect of creating one or more null rows, to which one or more rows from the nondeficient can be joined.

Syntax:

Table1.column = is the condition that joins(or relates) the table together.Table2.column (+) is the outer join symbol which can be placed on either side of the WHERE clause condition, but not on both sides.

Ex:

SELECT e.ename,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno(+) = d.deptno;

Self Joins:

To find the name of each emp’s manager, we need to join the emp table to itself ,or perform a self join.

2.INNER Versus OUTER Joins?

In SQL:1999, the join of two tables returning only matched rows is an inner join.A Join between two tables that returns the results of the inner join as well as unmatched rows left (or right) tables is a left (or right) outer join.A Join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join.

3. Difference between having and where clause?

Where clause is used for restricting rows that are to be retrieved by a query and where as Having clause is used for restricting groups.

4. Difference between Left outer join and Right outer join?

For a RIGHT OUTER JOIN, the left operand cannot be a join expression, a view, or a (query).For a LEFT OUTER JOIN, the right operand cannot be a join expression. It can, however, be a view or a (query).

5. Normalization and de normalization?

Normalization of data can be looked on as a process during which unsatisfactory relation schemas are decomposed by breaking up their attributes into smaller relation schemas that possess desirable properties. One objective of the original normalization process is to ensure that the update anomalies do not occur.
Denormalization: As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

6. What is Referential Integrity?

A rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).The FOREIGN KEY OR referential integrity constraint, designates a column or combination of columns as a foreign key and establishes a relationship between a primary key or a unique key in the same table or a different table.

7. What’s a Primary Key?

Primary key constraints can be defined at the column level or table level. A composite PRIMARY KEY is created by using the table – level definition. A table can have only one primary key but can have several unique constraints.

8. What is a view? where will you use views?

A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables.

Usage of views:

a)To restrict data access

b)To make complex queries easy

c)To provide data independence

d)To present different views or the same data

9. What are Indexes, types of indexes, how data is stored?

An oracle server index is a schema object that can speed up the retrival or rows by using a pointer.Two types or indexes can be created. One type is a unique index: the oracle server automatically creates this index when you define a column in a table to have a PRIMARY KEY or a UNIQUE key constraint. The name of the index is the name given to the constraint.The other type of index is a nonunique index, which a user can create. For example we can create a foreign key column index for a join in a query to improve retrieval speed.

10. Why there is a performance difference between two similar queries that uses UNION and UNION ALL?

The UNION operator returns all rows selected by either query. Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows.

Use the UNION ALL operator to return all rows from multiple queries.

Unlike UNION, duplicate rows are not eliminated and the output is not sorted by default. The DISTINCT keyword cannot be used. 11. Explain Third normalization form with an example

A relation is in third normal form (3NF) if and only if it is in 2NF and every nonkey attribute is nontransitively dependent on the primary key.
Third normal form is violated when a non-key field is a fact about another non-key field, as in ------------------------------------ EMPLOYEE DEPARTMENT LOCATION ============------------------------The EMPLOYEE field is the key. If each department is located in one place, then the LOCATION field is a fact about the DEPARTMENT -- in addition to being a fact about the EMPLOYEE. The problems with this design are the same as those caused by violations of second normal form: The department's location is repeated in the record of every employee assigned to that department. If the location of the department changes, every such record must be updated. Because of the redundancy, the data might become inconsistent, with different records showing different locations for the same department. If a department has no employees, there may be no record in which to keep the department's location. To satisfy third normal form, the record shown above should be decomposed into the two records: ------------------------- ------------------------- EMPLOYEE DEPARTMENT DEPARTMENT LOCATION ============------------- ==============-----------

12. What are constraints?

The oracle server uses constraints to prevent invalid data entry into tables.We can use constraints to do the following:
Enforce rules on the data in a table whenever a row is inserted, updated, or deleted from that table. The constraint must be satisfied for the operation to succeed.
Prevent the deletion of a table if there are dependencies from other tables.
Provide rules for oracle tools, such as oracle developer.

13. What is a trigger and stored procedure?

Trigger: A named pl/sql block which fires automatically Procedure: A procedure is a subprogram that performs a specific action.