Natural Join

In this lesson we are going to study about Natural Join in a database.

Natural Join:

• This join is having a special features ,with the use of this join no need to specify the join condition explicitly.
• This type of join offer a further specification of equi join .
• We have to specify the keyword Natural join in the join statement.
• Natural Join automatically joins two table based on columns in the two table which have same datatype and names.

Example: EMPLOYEE table and DEPARTMENTtable have a same column DepeartmentID and same datatype.
so we can join this two table using
NATURAL JOIN.



SQL :

SELECT *

FROM employee

NATURAL JOIN department;

Note:

• If we write a where clause of a select statement with two or more table then the order parser will start the join operation from right to left.In this case the table name which is written last will be processed,
•  The  join can happen only on columns having the same names and same data types in both the table .If the columns have the same name ,but different data types,then the NATURAL JOIN syntex causes an error.

Related Article

• Join
• Cross Join
• Equi Join
• Non  Equi Join
• Self Join 
• Join with Using clause.
• Join with ON clause
• Outer Join. 

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Self Join

In this lesson we are going to study about Self joins in a database.

Self Join: 

• In order to join a table to itself we use the self join.
• This type of join is used to compare values between two columns in the same table .

Example:-To find the name of Abhishek and Aritra manager we need to

1. find in EMPLOYEE table by looking the Emp_name columns.
2. find the manager number from Manager_id.
3. find the name of manager with manager_id.

sql query :-

select

      emp.Emp_Name as Employee_name,

      manager.Emp_Name as manager_name

from  employees emp,

      employees manager

where emp.manager_id=manager.emp_id

OUTPUT: 

In simulate two table in the from clause there are two  aliases ,namely emp and manager for the same table ,EMPLOYEE.



Related Article

• Join
• Cross Join
• Equi Join
• Non  Equi Join
• Natural Join 
• Join with Using clause.
• Join with ON clause 
• Outer Join

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Non Equi joins

In this lesson we are going to study about Non Equi joins in a database.
Non Equi Join:

Non eque join is a type of join in which we can join two table with the join condition where the join condition uses other than equal operator “=”.

Example: In the below diagram we are having two table EMPLOYEES and JOB_GRADES.

 EMPLOYEES tabel contain the lastname and salary of the employee and JOB_GRADE contain the grading (Gra),lowest salary and highest salary.

A relation between the two table is that the salary columns in the EMPLOYEES table must be between the values in the Lowest_sal and Highest_sal columns of the JOB_GRADES table.The relationship is obtained using an operator other than equal(=).

Non Equi join Sample:

select e.last_name,e.salary,j.gra

from employees e,job_grades j

where e.salary between j.lowest_sal and j.highest_sal

NOTE: Other condition ,such as <= and >= can be used ,but BETWEEN is the simplest .Remember specify the low value first and hight value last when useing BETWEEN.
Table aliases used for reduce the code length and as well as the execution time .

Related Article

• Join
• Cross Join
• Equi Join
• Self Join
• Natural Join 
• Join with Using clause.
• Join with ON clause
• Outer Join. 

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Equi Join

In this lesson we are going to study about Equi joins in a database.

Equi Join or Theta Join: 

• An Equi Join is a type of joins the tables based on the columns in two table with same values.
• In Equi join ,the join condition must have an Equility operator.
• Using other comparison operators (such as <) disqualifies a join as an equi-join.

Example: In the below diagram we are having two Table,Table:A and Table:B. In Table:A we are having five records and Table:B is having four

 records which is also in Table:A  if  we do the Equi join on Table:A column X with Table:B column Y,

 this will retrun the record which are common in both the table A column X and Table:B column Y .

NOTE:   The columns X of Table A and columns Y of Table B Datatype must be same and the resulting table is having all the columns of Table:A ,and Table:B.

Syntex:

Explicit Equi join:

SELECT *

FROM Table1

JOIN Table2 ON  Table1.columnX=  Table2. columnY ;

Implicit Equi join:

SELECT * FROM  Table1 ,  Table2  WHERE  Table1.columnX=  Table2. columnY ;

just have a look on below video and try to Cross Join table by your self and you can download the sql scrpit used in this video from here DOWNLOAD LINK

Related Article

• Join
• Cross Join
• Non  Equi Join
• Self Join 
• Natural Join  
• Join with Using clause.
• Join with ON clause
• Outer Join. (comming soon.)

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Cross join

In this lesson we are going to study about Corss joins in a database.
Cross Join  or Cartesian Product:

• Each row from the first table is combined with the each rows from the second table.
• Rows in the result table is the product of rows in each table.
• For large no of rows ,it takes longer time.
• It does not include any join condition .
• A Cross Join B Result table as  A*B.

Example1:In the below diagram we are having two table's Table:A,Table:B .Table:A is having one column X

with three data and Table:B is having one column Y with two data.So the value of  m=3 and n=2.
So when we are doing cross join of Table:A with Table:B this will return a table which have
m*n =3*2
       =6(Record)
i.e each row of one table is mapped with each row of second table.




Example2:  In the below diagram we are having two table's Table:R,Table:S .Table:A is having Two column A,column B

with three data and Table:S is having two column B.column C with two data.So the value of  m=3 and n=2.
So when we are doing cross join of Table:A with Table:B this will return a table which have
m*n =3*2
       =6(Record)
NOTE: Result table is having all columns of Table:R,Table:S

Syntax:
Explicit cross join:

SELECT *

FROM Table1
CROSS JOIN Table2



Implicit cross join:

SELECT *
FROM  Table1 , Table2;

just have a look on below video and try to Cross Join table by your self and you can download the sql scrpit used in this video from here DOWNLOAD LINK

 Related Article

• Join
• Equi Join
• Non  Equi Join
• Self Join 
• Natural Join  
• Join with Using clause.
• Join with ON clause
• Outer Join.  

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Joins

In this lesson we are going to study about joins in a database . In order to join rows from multiple table or view we use join in our statement in this session we will briefly discussing the join and type of join in database .
JOINS:

• Joins query are written in the where clause of the select statement.
• Joins condition :comparing two columns of two tables.
• Database engine joins the table according to the join condition .
• If we want to join more then two table then database engine evaluates columns of the two tables and then it joins the result to the other table.

Have a look this image which shows the operation of the join.

Table A and Table B is having some common columns .by giving the condition in where clause we can have the result of joined table.

NOTE: The columns which are using in join condition must have the same datatype . otherwise it will give error and to prevent this cause if having different datatype but still we want to use that columns in join condition then use the cast function to convert the datatype and then use in join condition .

Type of Joins:

 Joins are classified according to the different type way the join operation performed on the tables.

we will discuss about all this Join in details in the next section .
Related Article

• Cross Join 
• Equi Join
• Non  Equi Join
• Self Join 
• Natural Join 
• Join with Using clause.
• Join with ON clause
• Outer Join.  

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

SIMPLE SQL SELECT STATEMENT

We are now steping into the important part of the database called the select statement.

    As we know data is store in the form of table in the relational database. Before getting deeply into the database here is the sample of how the data store in the form of table. Take a look of this table 

   In this table there are four columns s_no,first_name,last_name,city. The table data is store in a cell in a table cell is nothing but the intersection of row and columns. Generally the value of the table are store in a cell. We can store the different type of data in this cell. Take a look at this image Here we have given the query to select the rows from the table the database server processes this query and retrives the record from the database .

Use of SELECT statement:

• Basic statement to extract the data from the table.
• Retrives data from a table according to our needs.
   

Syntax: 

There are four main field to write a simple select query SELECT is the key word specifying we are going to select the data from the specific table list of column name or all columns name specify the column name or all columns name whose value we are going to select columns names are separated by commas (,) . if we want to select all column of the table then we specify asteric (*) instead of all column. From is the key word which specify where the value are store Table_Name is the name of the table where data is going to be retrieve and finaly we must put end of the select statement with semicolon (;) which is mendatory for all sql statement.

TABLE NAME:FRIENDSDTLS

Now we want to select the data of all field i.e all row and all columns sql query is :

Select * from friendsdtls;

If want to see the s_no,first_name, and city then the sql query is:

Select s_no,first_name, city from friendsdtls;

Here you go a sample video how to select data from table .you can view this video on my you tube channel as well @ http://youtu.be/r-A41Y50sXs

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Inserting data Into table

In this section we are going to discuss data insertion into tables .

• Insert statements is used to insert a data into a table.
  
• Basic statements for data insertion .
  

Take a look on below image:

 

 Here we have given the query to insert data into the table the database server process this statements and insert a single row into the table in the database .
Syntex:

In this syntex INSERT INTO is the keyword that specifies the data insertion .Table_Name specifies the name of the table in which we are going to insert the data,columns name specifies the name of column in which we are going to give the value here we can specfies the desired columns name and if we wish to insert value for all columns of table so there is no need of specifing the each columns name .VALUES is the keyword that specifies the value we are going to give to table say value-1,value-2,value-n is the values .we must enter the value of columns according to the columns in the table.

Strucutre of table :

Table Name:  friendsdtls
Columns Name or Field :
s_no                 int(10)
first_name       varchar (50)
last_name        varchar(50)
city                  varchar(50)
create the following table if you have any problem in creating the table then take a look on  table creation . The data which we are going to insert in the friendsdtls table

So the insert query for the above table is as follows :
INSERT INTO `test`.`friendsdtls` ( `s_no`,`first_name` ,`last_name` ,`city`)
                                        VALUES ( '1', 'abhishek', 'choudhary', 'kolkata');
we can also insert the data without mentioning the fields  but in that case must be in order as follows:
INSERT INTO `test`.`friendsdtls` VALUES ('2', 'md', 'imran', 'kolkata'  );
after  executing the above lines of query  your table will look like as below

Here you go a sample video how to insert data into table .you can view this video on my you tube channel as well @  http://youtu.be/yJl0z84Rh40

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Simple table creation

In this section we are going to discuss the
Data Definition Language (DDl):Data definition language is used to create alter and the drop the database objects.database obejects are nothing but a table ,views,indexes,etc.
Here we will see how to create a simple table. for creating a table we must have a database like oracle.sql server2008,DB2, and etc..install in owr  system and if you dont have any database  installed in your system then you can try

• My sql
• Sql server 2008
  

For this series I have used mysql database which is already installed in wamp server. If to see installation of wamp server then click here.

Take a look at this image here we have given the query to mysql server and my sql server process the request and creted the table in the database.


Syntex:

In this syntax create table is the keyword to create a table in the database Table_name is the name of table to be created in the database ,column_name1 to column_nameN is the name of the column creted in the table in this table creation we must specify the table datatype during table creation it specifiy the type of data and the length of data to be store in the table columns in this table definition we can also specify the default expression to be store in the column .
So before going to create a table decide the data type of the of different data which we will store in database so just have a look on below video and try to create a  table by your self.

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

SQL Data Types

In this section we will learn about datatype.
Data is stored in the form of tables in the database and the data is in differents formats such as name of person ,data of birth of a persion ,etc.So we need to specify the type of data before storing them .so we need to know about different types

Meaning of “data type”

Data type used to indicate the type of information in the database columns .Here is the list of database supported by oracle10g.

visit oracle10g,  sql server, mysql for more data type supported by oracle10g,  sql server, mysql.

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

INTRODUCTION TO SQL

Sql is the main language which is used in all the databases to create database and access data from the database.

Here we are going to discuss sql,before getting into sql let find out:

what sql is?

Sql stands for “structure query language ”.sql is non procedural language used to access and manipulate databases.

From sql we can say what do we want from a database. I hope this definition gives you basic idea of sql

next we will see,

Meaning of word “QUERY”?

A “query ” is a question we ask the database .if the asked data is in the database then SQL retrive it.

Here we will see sub language of sql SQL components are sub language in sql

to create ,modify,maintain,and provide security to database.sql  divided into six sub language.

Data Query Language (DQL)   statement used to  retrive a data from a database . Some Example:

• SELECT:-retrive data from the database.

Data Definition Language (DDL) statements are used to define the database structure or schema .Some Example:

• CREATE:-to create object in the database.
• ALTER:-alter the structure of the database.
• DROP :- delete object from the database.
• TRUNCATE:-romove all records from a table ,including all space allocated for the records are removed.
• COMMENT:- add comment to the data dictionary.
• RENAME:-rename an object.

Data Manipulation Language (DML) statements are used for managing data within schema objects.Some Example:

• SELECT:-retrive data from the database.
• INSERT:-insert data into table.
• UPDATE:-update existing data within a table.
• DELETE:-delete all records from a table ,the space for the record remain .
• MERGE: -UPSERT operation (insert or update)
• CALL:-call a PL/SQL or java subprogram.
• EXPALIN PLAN:- explain access path to data.
• LOCK TABLE:- control concurrency.

Data Control Language(DCL) statements are used to control the database like giving permission to access the database to user of database.Some Examples:

• GRANT:-gives user access privilage to database.
• REVOKE:-withdraw access privileges given with GRANT command

Transaction Control(TCL) statements are used to manage the changes made by DML statements.it allows user statements to be grouped together into logical transactions.

• COMMIT:-save work done.
• SAVE POINT:-identify a point in a transaction to which you can later roll back
• ROLLBACK :-restore database to original since the last COMMIT
• SET TRANSACTION: change transaction option like isolation level and what rollback segment
  to use

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: