Joins

The SQL Joins clause is used to combine rows from two or more tables in a database. Joining tables must have at least one common column with same data type and same value.

The purpose of a join is to combine the data across tables. A join is actually performed by the where clause which combines the specified rows of tables.

If a join involves in more than two tables then oracle joins first two tables based on the joins condition and then compares the result with the next table and so on.

TYPES

Equi Joins	Equality operator =  in the join
Non Equi Joins	Other than = like <,>,Between, so on
Inner Join	Returns a row only when the columns in the join contain values that satisfy the join condition. This means that if a row has null value in one of the columns in the join condition. That row isnt returned .
Outer join	Can return a row even when one of the column s in the join condition contains a null value
Self join	Returns rows joined on the same tables
Natural join	All columns in the two tables that have same name. It selects rows from the two tables that have equal values in all matched column

Assume that we have the following tables.

SQL> select * from dept;

DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

SQL> select * from emp;

EMPNO ENAME                    JOB         MGR HIREDATE    SAL  COMM DEPTNO
----- ------------------------ --------- ----- --------- ----- ----- ------
 7369 SMITH                    CLERK      7902 17-DEC-80   800           20
 7499 ALLEN                    SALESMAN   7698 20-FEB-81  1600   300     30
 7521 WARD                     SALESMAN   7698 22-FEB-81  1250   500     30
 7566 JONES                    MANAGER    7839 02-APR-81  2975           20
 7654 MARTIN                   SALESMAN   7698 28-SEP-81  1250  1400     30
 7698 BLAKE                    MANAGER    7839 01-MAY-81  2850           30
 7782 CLARK                    MANAGER    7839 09-JUN-81  3450           10
 7788 SCOTT                    ANALYST    7566 09-DEC-82  3000           20
 7839 KING                     PRESIDENT       17-NOV-81  6000           10
 7844 TURNER                   SALESMAN   7698 08-SEP-81  1500           30
 7876 ADAMS                    CLERK      7788 12-JAN-83  1100           20
 7900 JAMES                    CLERK      7698 03-DEC-81   950           30
 7902 FORD                     ANALYST    7566 03-DEC-81  3000           20
 7934 MILLER                   CLERK      7782 23-JAN-82  2300           10

14 rows selected.


EQUI JOIN

A join which contains an ‘=’ operator in the joins condition.
SQL> SELECT empno,ename,job,dname,loc
  2  FROM emp e,dept d
  3  WHERE e.deptno=d.deptno;

EMPNO ENAME           JOB       DNAME          LOC
----- -------------- --------- -------------- ----------
 7782 CLARK           MANAGER   ACCOUNTING     NEW YORK
 7839 KING            PRESIDENT ACCOUNTING     NEW YORK
 7934 MILLER          CLERK     ACCOUNTING     NEW YORK
 7566 JONES           MANAGER   RESEARCH       DALLAS
 7902 FORD            ANALYST   RESEARCH       DALLAS
 7876 ADAMS           CLERK     RESEARCH       DALLAS
 7369 SMITH           CLERK     RESEARCH       DALLAS
 7788 SCOTT           ANALYST   RESEARCH       DALLAS
 7521 WARD            SALESMAN  SALES          CHICAGO
 7844 TURNER          SALESMAN  SALES          CHICAGO
 7499 ALLEN           SALESMAN  SALES          CHICAGO
 7900 JAMES           CLERK     SALES          CHICAGO
 7698 BLAKE           MANAGER   SALES          CHICAGO
 7654 MARTIN          SALESMAN  SALES          CHICAGO

14 rows selected.


USING CLAUSE


SQL> select empno,ename,job ,dname,loc from emp e join dept d using(deptno);

          EMPNO     ENAME      JOB    DNAME      LOC
          ----------      ----------     ----------   ---------- ----------
            111           saketh    analyst     mkt          hyd
            333           jagan      manager  mkt         hyd
            222           sudha     clerk        fin          bang

ON CLAUSE


SQL>select empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno);
          EMPNO     ENAME      JOB    DNAME      LOC
          ---------- ---------- ---------- ---------- ----------
            111           saketh    analyst    mkt        hyd
            333           jagan      manager  mkt        hyd
            222           sudha      clerk        fin        bang

NON-EQUI JOIN

A join which contains an operator other than ‘=’ in the joins condition.
Ex: SQL>select empno, ename, job, dname, loc from emp e,dept d where   e.deptno > d.deptno;

          EMPNO     ENAME    JOB      DNAME      LOC
          ---------- ---------- ---------- ---------- ----------
       222    sudha      clerk          mkt        hyd
       444    madhu     engineer   mkt        hyd
       444    madhu     engineer   fin          bang
       444    madhu     engineer   hr           bombay

SELF JOIN

Joining the table itself is called self join.
Ex:
     SQL> select e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where
             e1.empno=e2.mgr;

     EMPNO     ENAME    JOB      DEPTNO
     ---------- ---------- ---------- ----------
       111          jagan      analyst         10
       222          madhu      clerk           40
       333          sudha      manager      20
       444          saketh     engineer      10

NATURAL JOIN

Natural join compares all the common columns.
Ex:
     SQL> select empno,ename,job,dname,loc from emp natural join dept;

     EMPNO   ENAME      JOB      DNAME    LOC
    ---------- ---------- ---------- ---------- ----------
       111          saketh     analyst     mkt        hyd
       333          jagan      manager   mkt        hyd
       222          sudha      clerk         fin          bang


CROSS JOIN

This will gives the cross product.
Ex:
     SQL> select empno,ename,job,dname,loc from emp cross join dept;

 EMPNO  ENAME    JOB        DNAME      LOC
---------- ---------- ---------- ---------- ----------
       111     saketh   analyst      mkt        hyd
       222     sudha    clerk          mkt        hyd
       333     jagan     manager   mkt        hyd
       444     madhu   engineer   mkt        hyd
       111     saketh   analyst      fin          bang
       222     sudha    clerk          fin          bang
       333     jagan     manager   fin          bang
       444     madhu   engineer   fin          bang
       111     saketh   analyst      hr           bombay
       222     sudha    clerk          hr           bombay
       333     jagan     manager   hr           bombay
       444     madhu   engineer   hr           bombay

OUTER JOIN
Outer join gives the non-matching records along with matching records.

LEFT OUTER JOIN
This will display the all matching records and the records which are in left hand side table those that are not in right hand side table.
Ex:
     SQL> select empno,ename,job,dname,loc from emp e left outer join dept d
             on(e.deptno=d.deptno);
Or
     SQL> select empno,ename,job,dname,loc from emp e,dept d where
             e.deptno=d.deptno(+);

                             EMPNO     ENAME   JOB       DNAME      LOC
     ---------- ---------- ---------- ---------- ----------
       111          saketh    analyst       mkt        hyd
       333          jagan      manager    mkt        hyd
       222          sudha     clerk           fin          bang
       444          madhu    engineer

RIGHT OUTER JOIN
This will display the all matching records and the records which are in right hand side table those that are not in left hand side table.
Ex:
     SQL> select empno,ename,job,dname,loc from emp e right outer join dept d
              on(e.deptno=d.deptno);
Or
      SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) =
             d.deptno;


     EMPNO    ENAME     JOB      DNAME      LOC
     ---------- ---------- ---------- ---------- ----------
       111          saketh     analyst      mkt        hyd
       333          jagan       manager   mkt        hyd
       222          sudha      clerk          fin          bang
                                                       hr           bombay

FULL OUTER JOIN
This will display the all matching records and the non-matching records from both tables.
Ex:
     SQL> select empno,ename,job,dname,loc from emp e full outer join dept d
              on(e.deptno=d.deptno);

 EMPNO   ENAME    JOB        DNAME      LOC
---------- ---------- ---------- ---------- ----------
       333     jagan     manager    mkt        hyd
       111     saketh   analyst       mkt        hyd
       222     sudha    clerk           fin        bang
       444     madhu   engineer     hr         bombay

INNER JOIN
This will display all the records that have matched.
Ex:
     SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);

     EMPNO     ENAME   JOB        DNAME    LOC
     ---------- ---------- ---------- ---------- ----------
       111          saketh     analyst      mkt       hyd
       333          jagan       manager   mkt       hyd
       222          sudha      clerk          fin         bang

Functions

Functions can be categorized as follows.

1. Single row functions      2. Group functions

SINGLE ROW FUNCTIONS:  

Single row functions can be categorized into five. These will be applied for each row and produces individual output for each row.

1. Numeric functions
2. String functions
3. Date functions
4. Miscellaneous functions
5. Conversion functions

GROUP FUNCTIONS 
1. Sum
2. Avg
3. Max
4. Min
5. Count

Numeric functions

1     abs
2     sign
3     sqrt
4     mod
5     nvl
6     power
7     exp
8     ln
9     log
10  ceil
11  floor
12  round
13  trunk
14  bitand
15  greatest
16  least
17  coalesce


String functions

18  initcap
19  upper
20  lower
21  length
22  rpad
23  lpad
24  ltrim
25  rtrim
26  trim
27  translate
28  replace
29  soundex
30  concat  ( ‘ || ‘ concatenation operator)
31  ascii
32  chr
33  substr
34  instr
35  decode
36  greatest
37  least
38  coalesce


Date functions

39  sysdate
40  current_date
41  current_timestamp
42  systimestamp
43  localtimestamp
44  dbtimezone
45  sessiontimezone
46  to_char
47  to_date
48  add_months
49  months_between
50  next_day
51  last_day
52  extract
53  greatest
54  least
55  round
56  trunc
57  new_time
58  coalesce

H) to_char

  
    Date formats
 
            D                      --         no of days in week
            Dd                    --         no of days in month
            Ddd                 --         no of days in year
            Mm                   --         no of month
            Mon                 --         three letter abbreviation of month
            Month             --         fully spelled out month
            Rm                   --         roman numeral month
            Dy                    --         three letter abbreviated day
            Day                  --         fully spelled out day
            Y                      --         last one digit of the year
            Yy                    --         last two digits of the year
            Yyy                  --         last three digits of the year
            Yyyy                --         full four digit year
            Syyyy             --          signed year
            I                       --         one digit year from iso standard
            Iy                     --         two digit year from iso standard
            Iyy                   --         three digit year from iso standard
            Iyyy                 --         four digit year from iso standard
            Y, yyy              --         year with comma
            Year                --         fully spelled out year
            Cc                    --         century
            Q                      --         no of quarters
            W                     --         no of weeks in month
            Ww                  --         no of weeks in year
            Iw                    --         no of weeks in year from iso standard
            Hh                    --         hours
            Mi                    --         minutes
            Ss                    --         seconds
            Ff                     --         fractional seconds
            Am or pm         --         displays am or pm depending upon time of day
            A.m or p.m       --         displays a.m or p.m depending upon time of day
            Ad or bc          --         displays ad or bc depending upon the date
            A.d or b.c        --         displays ad or bc depending upon the date
            Fm                    --         prefix to month or day, suppresses padding of month or day
            Th                    --         suffix to a number
            Sp                    --         suffix to a number to be spelled out
            Spth                --         suffix combination of th and sp to be both spelled out
            Thsp                --         same as spth




S) new_time


    Timezones

                        Ast/adt         --         atlantic standard/day light time
                        Bst/bdt          --         bering standard/day light time
                        Cst/cdt          --         central standard/day light time
                        Est/edt          --         eastern standard/day light time
                        Gmt                 --         greenwich mean time
                        Hst/hdt         --         alaska-hawaii standard/day light time
                        Mst/mdt         --         mountain standard/day light time
                        Nst                 --          newfoundland standard time
                        Pst/pdt          --         pacific standard/day light time
                        Yst/ydt          --         yukon standard/day light time

Miscellaneous functions

59  uid
60  user
61  vsize
62  rank
63  dense_rank

Conversion functions

64  bin_to_num
65  chartorowid
66  rowidtochar
67  to_number
68  to_char
69  to_date


Group functions

70  sum
71  avg
72  max
73  min

74  count

Select

Syntax:

Select * | {Distinct} column | expression [alias] ,... }  from table;

Select all Columns : Select * from table_name;

Selecting particular column:   Select column1,column2 from table_name;

Examples:
Select last_name,salary+300 from table_name
Select last_name, 12*salary+300 from table_name
Select last_name,12*(salary+100) from table_name


Arthritic Expression

Alias
Select last_name as name from table_name (alias)
Select last_name “Name” from table_name

Concatitation: 
Select first_name||last_name from table_name
Select last_name||'is a'|| salary from table_name (literal)
Select last_name || q'[, it's a manager_id]' from table_name

Distinct: Unique
Select distinct(last_name) from table_name

SQL comparison Operators

=	Equal  (a = b )
!=  or  <>	Not Equal ( a != b)
<	Less than
>	Greater than
<=	Less than equal to
>=	Greater than equal to
any	Compares one value with any value in list
all	Compares one value with all values in list
like	Matches patterns in strings. _ single character , % multiple
in	Matches lists of values
between	Matches range of values
Is null	Matches null values
Is nan	Matches nan special value, not number
Is infinite	Matches binary float and binary infinite
and	Returns true when x and y are true
or	Returns true when either x or y is true
Not x	Return true if x is false , returns false if x is true

Constraints

SQl Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.

Constraints can be divided into following two types,

Column level constraints : limits only column data
Table level constraints : limits whole table data
Constraints are used to make sure that the integrity of data is maintained in the database. Following are the most used constraints that can be applied to a table.

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
NOT NULL Constraint

NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column. It enforces a column to contain a proper value. One important point to note about NOT NULL constraint is that it cannot be defined at table level.

Example using NOT NULL constraint

CREATE table Student(s_id int NOT NULL, Name varchar(60), Age int);
The above query will declare that the s_id field of Student table will not take NULL value.

UNIQUE Constraint

UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not have duplicate data. UNIQUE constraint can be applied at column level or table level.

Example using UNIQUE constraint when creating a Table (Table Level)

CREATE table Student(s_id int NOT NULL UNIQUE, Name varchar(60), Age int);
The above query will declare that the s_id field of Student table will only have unique values and wont take NULL value.

Example using UNIQUE constraint after Table is created (Column Level)

ALTER table Student add UNIQUE(s_id);
The above query specifies that s_id field of Student table will only have unique value.

Primary Key Constraint

Primary key constraint uniquely identifies each record in a database. A Primary Key must contain unique value and it must not contain null value. Usually Primary Key is used to index the data inside the table.

Example using PRIMARY KEY constraint at Table Level

CREATE table Student (s_id int PRIMARY KEY, Name varchar(60) NOT NULL, Age int);
The above command will creates a PRIMARY KEY on the s_id.

Example using PRIMARY KEY constraint at Column Level

ALTER table Student add PRIMARY KEY (s_id);
The above command will creates a PRIMARY KEY on the s_id.

Foreign Key Constraint

FOREIGN KEY is used to relate two tables. FOREIGN KEY constraint is also used to restrict actions that would destroy links between tables. To understand FOREIGN KEY, let's see it using two table.

Customer_Detail Table :

c_id Customer_Name address
101 Adam Noida
102 Alex Delhi
103 Stuart Rohtak
Order_Detail Table :

Order_id Order_Name c_id
10 Order1 101
11 Order2 103
12 Order3 102
In Customer_Detail table, c_id is the primary key which is set as foreign key in Order_Detail table. The value that is entered in c_id which is set as foreign key in Order_Detail table must be present in Customer_Detail table where it is set as primary key. This prevents invalid data to be inserted into c_id column of Order_Detail table.

Example using FOREIGN KEY constraint at Table Level

CREATE table Order_Detail(order_id int PRIMARY KEY,
order_name varchar(60) NOT NULL,
c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id));
In this query, c_id in table Order_Detail is made as foriegn key, which is a reference of c_id column of Customer_Detail.

Example using FOREIGN KEY constraint at Column Level

ALTER table Order_Detail add FOREIGN KEY (c_id) REFERENCES Customer_Detail(c_id);
Behaviour of Foriegn Key Column on Delete

There are two ways to maintin the integrity of data in Child table, when a particular record is deleted in main table. When two tables are connected with Foriegn key, and certain data in the main table is deleted, for which record exit in child table too, then we must have some mechanism to save the integrity of data in child table.

foriegn key behaviour on delete - cascade and Null

On Delete Cascade : This will remove the record from child table, if that value of foriegn key is deleted from the main table.
On Delete Null : This will set all the values in that record of child table as NULL, for which the value of foriegn key is eleted from the main table.
If we don't use any of the above, then we cannot delete data from the main table for which data in child table exists. We will get an error if we try to do so.
ERROR : Record in child table exist

CHECK Constraint

CHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before storing them into the database. Its like condition checking before saving data into a column.

Example using CHECK constraint at Table Level

create table Student(s_id int NOT NULL CHECK(s_id > 0),
Name varchar(60) NOT NULL,
Age int);
The above query will restrict the s_id value to be greater than zero.

Example using CHECK constraint at Column Level

ALTER table Student add CHECK(s_id > 0);

Normalization

Normalization of Database

Database Normalisation is a technique of organizing the data in the database. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anamolies. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.


Normalization is used for mainly two purpose,
Eliminating reduntant(useless) data.
Ensuring data dependencies make sense i.e data is logically stored.

Problem Without Normalization
Without Normalization, it becomes difficult to handle and update the database, without facing data loss. Insertion, Updation and Deletion Anamolies are very frequent if Database is not Normalized. To understand these anomalies let us take an example of Student table.

S_id	S_Name	S_Address	Subject_opted
401	Adam	Noida	Bio
402	Alex	Panipat	Maths
403	Stuart	Jammu	Maths
404	Adam	Noida	Physics

Updation Anamoly : To update address of a student who occurs twice or more than twice in a table, we will have to update S_Address column in all the rows, else data will become inconsistent.

Insertion Anamoly : Suppose for a new admission, we have a Student id(S_id), name and address of a student but if student has not opted for any subjects yet then we have to insert NULL there, leading to Insertion Anamoly.

Deletion Anamoly : If (S_id) 401 has only one subject and temporarily he drops it, when we delete that row, entire student record will be deleted along with it.

Normalization Rule

Normalization rule are divided into following normal form.



First Normal Form

Second Normal Form

Third Normal Form

BCNF

First Normal Form (1NF)



As per First Normal Form, no two Rows of data must contain repeating group of information i.e each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row. Each table should be organized into rows, and each row should have a primary key that distinguishes it as unique.



The Primary key is usually a single column, but sometimes more than one column can be combined to create a single primary key. For example consider a table which is not in First normal form

Student	Age	Subject
Adam	15	Biology, Maths
Alex	14	Maths
Stuart	17	Maths

In First Normal Form, any row must not have a column in which more than one value is saved, like separated with commas. Rather than that, we must separate such data into multiple rows.



Student Table following 1NF will be :

Student	Age	Subject
Adam	15	Biology
Adam	15	Maths
Alex	14	Maths
Stuart	17	Maths

Using the First Normal Form, data redundancy increases, as there will be many columns with same data in multiple rows but each row as a whole will be unique.

Second Normal Form (2NF)

As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. If any column depends only on one part of the concatenated key, then the table fails Second normal form.

In example of First Normal Form there are two rows for Adam, to include multiple subjects that he has opted for. While this is searchable, and follows First normal form, it is an inefficient use of space. Also in the above Table in First Normal Form, while the candidate key is {Student, Subject}, Age of Student only depends on Student column, which is incorrect as per Second Normal Form. To achieve second normal form, it would be helpful to split out the subjects into an independent table, and match them up using the student names as foreign keys.

New Student Table following 2NF will be :

Student	Age
Adam	15
Alex	14
Stuart	17

In Student Table the candidate key will be Student column, because all other column i.e Age is dependent on it.



New Subject Table introduced for 2NF will be :

Student	Subject
Adam	Biology
Adam	Maths
Alex	Maths
Stuart	Maths

In Subject Table the candidate key will be {Student, Subject} column. Now, both the above tables qualifies for Second Normal Form and will never suffer from Update Anomalies. Although there are a few complex cases in which table in Second Normal Form suffers Update Anomalies, and to handle those scenarios Third Normal Form is there.

Third Normal Form (3NF)

Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form. For example, consider a table with following fields.

Student_Detail Table :

Student_id	Student_name	DOB	Street	city	State	Zip

In this table Student_id is Primary key, but street, city and state depends upon Zip. The dependency between zip and other fields is called transitive dependency. Hence to apply 3NF, we need to move the street, city and state to new table, with Zip as primary key.



New Student_Detail Table :

Student_id	Student_name	DOB	Zip

Address Table :

Zip	Street	city	state

The advantage of removing transtive dependency is,



Amount of data duplication is reduced.

Data integrity achieved.

Boyce and Codd Normal Form (BCNF)



Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals with certain type of anamoly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied:



R must be in 3rd Normal Form

and, for each functional dependency ( X -> Y ), X should be a super Key.

BCNF Normal Form

SQL Intro

 SQL Architecture:

Primary Statements:

DDL: Data Definition Language

DML: Data Manipulation Language

DRL: Data Retrieval Language

TCL: Transaction Control Language

DCL: Data Control Language

Introduction

RDBMS

Its originally developed back in 1970 by Dr,E.F.Codd.
RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects called tables.
A table is a collection of related data entries and it consists of columns and rows.

Table Basics

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns. Here is a sample table called "weather".
city, state, high, and low are the columns. The rows contain the data for this table:

Weather
city	state	high	low
Phoenix	Arizona	105	90
Tucson	Arizona	101	92
Flagstaff	Arizona	88	69
San Diego	California	77	60
Albuquerque	New Mexico	80	72

Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.

What is SQL?

SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.