Join with ON clause

In this lesson we are going to study about Join with ON clause in a database.

Join with ON clause:

• The join condition for the natural join is basically an equijoin of all columns with the same name.
• To specfify arbitary conditions or specfify columns to join,the ON clause is used.
• The join condition is separated from other search conditions.
  The ON clause makes code easy to understand.

Example:

we are having two table DEPARTMENTS and EMPLOYEES with comman column department_id .So, we can join this two table with ON  clause.

SQL:

Select e.employee_id,e.last_name,e.department_id,d.location_id
From employees e join departments d
On (e.department_id=d.department_id);




OUTPUT:

 Same result with equijoin :

Select e.employee_id,e.last_name,e.department_id,d.location_id
From employees e join departments d
where  e.department_id=d.department_id;


Related Article

• Join
• Cross Join
• Equi Join
• Non Equi Join 
• Self Join
• Natural Join
• Join with Using 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:

Join with Using clause

In this lesson we are going to study about Join with Using clause in a database.

Join with Using clause:

• Using clause is used to join the tables where the column in those tables shares a same name .
• Join operation carried out in these two table according to the values in these two columns.
• The columns listed the using clause cannot have any qualifier in the
  statement and it should not include the where clause .

     It means we can’t select the value of these two columns in output and we
can’t use these columns in where clause.

Example:
we are having two table DEPARTMENTS and EMPLOYEES with comman column department_id .So, we can join this two table with Using clause.

SQL:

select e.employee_id,e.last_name,d.location_id
from employees e join departments d
using (department_id)

OUTPUT:

 same result with equi join:

select  e.employee_id,
e.last_name,d.location_id
from employees e , departments d
where e.employee_id =d.department_id






NOTE:

• Do not use a table name or alias in referenced columns.
• The NATURAL JOIN and USING clause are mutually exclusive.

Related Article

• Join
• Cross Join
• Equi Join
• Non Equi Join 
• Self Join
• Natural Join
• 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:

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:

How To Attach mdf file in sql server

In the previous lesson we have already discuss about how to log in your database server and access your database  located in local server or remote server and all if you miss those lesson then just have a look here..

This article will assist with moving SQL Server Data File(s) (.mdf) and Log File(s) (.ldf) from one location to another using the attach and detach .but before going into step for attach and detach the mdf and ldf let talk in short about

what is mdf?

what is ldf?

what is ndf?

SQL Server databases have three types of files:

Primary data files:The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.

Secondary data files:Secondary data files comprise all of the data files other than the primary data file. Some databases may not have any secondary data files, while others have multiple secondary data files. The recommended file name extension for secondary data files is .ndf.

Log files:Log files hold all of the log information used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf.

step to attach database file:

Step1:Just folllow the below screen and brose the attach file window.Right click on database >>all task>>attach database.

Step2:click on browse option in newer version this option named as add new  then select .mdf file and click on OK.

Step3: Now the database .mdf file is in browse window for confirmation click on OK other choss another .mdf file. After click on OK it will give message whether the file is attach or not.

step to detach database file: first select your database then give right click and select the detach option .
 step to move database file:  To move the database file there are two way first way is Stop the sql server and then go to the physical path of .mdf file copy and past it another place or in your hard disk and attach in other sql server  and remeber dont forget to start the sql server once you stop it .Second way is detach the database then copy the database and move it another location then again attach the database .mdf file.
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:

Connect to server database using TNS

  We all know most of the database are having the client server architecture i.e client send a query to retrive some data from the database and the server process all those query and in retrun the client get the data which he request for.In this section we are not going in deep of client server architecture but yes this section is all about how you can connect to database server.

Connect to sql server database: 

  It is very easy to connect to SQL Server2008. Start your SQL Server Managment studio and you will get the screen as shown right side .Enter server type,servername ,authentication
(select sqlserver authentication if you are connecting to other then the local computer), login name,password 

and connct to your SQL Server database.

Connect to oracle database server:

     To connect the oracle database server we need to have to TNS Configure in the client system.There are many step to create a TNS but in this section we learn how to create TNS using TOAD for Oracle (I do belive that you have successfully installed the Toad for oracle which we have already discussed in the previous section if you miss the reading last article on installation of Oracle for Toad visit here)

     

     Step1: Start your Toad for Oralce you will get the screen like below if you are not getting the below screen then get confuse go to session menu (short key alt+s) and then click on new session and you will get the below screen.

        Step2: Now click on TNSnames Editor you will be getting the below screen to create the TNS for your oracle server Easy way to create the TNS is to go into the Text Editor tab copy and past the below informantion :

Your_oracle_ServerName =

  (DESCRIPTION=

     (ADDRESS_LIST=

        (ADDRESS=

           (COMMUNITY=tcp.world)

              (PROTOCOL=TCP)

                (Host=192.153.0.1)

                (Port=1521)

                )

              )

         (CONNECT_DATA=

            (SID=orcl)

          )

     )

Note: Replace the bold red colored text with your own oracle database server.

    Step3: Click on save then click on OK.

    Step4: Go to session menu 

(short key alt+s) and then click on new session

You will get the same screen as shown here .Now enter your User/Schema name, password , name of you database and then click on connect.

NOTE: if you are getting any TNS
error then get confirm that your computer is connect to server computer.
i.e chek your LAN setting (use ping command to chek the connection,type this in command prompt  ping 127.0.0.1 -t )

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:

Step to install oracle for Toad

      Toad® for Oracle is unrivaled for ensuring the greatest possible productivity in development and administration of Oracle databases. Only Toad combines the deepest functionality available with extensive automation and intuitive workflows. With Toad, database professionals of all skill and experience levels can collaborate and work efficiently and accurately.so in this section we are steping into the installation of oracle for toad.
and for this section download the toad from here   DOWNLOAD LINK .

Step to install the toad
Step1: first download the setup for toad then run the setup.exe and then click on next

Step2: after click on next you will get the licence agreement accept the agrement and click on next .

Step3: once you accept the licence agrement  then choss which type of installtion you want  i suggest you to choss full installtaion ,mode show that it will install all the things .then if you want add on programm then chek otherwise unchek the add on. and click on next.

Step4:Now select the installtion drive you want by default will install in   c:\program file\Quest Software\Toad for Oracle .again give a click on Next

Step5:Now its ready to install click on Next and install Toad.

Step6:Installation complete successfully.

Step7:open the toad it will ask for the
Authorization Key:
Site Message:
Then click on ok. all the step installtion are completed successfully



Step8:Enter the log in details like user/schema name and password for acces the database .select the database ,and mode of conncting by the oracle database server. like TNS,Direct...

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:

Steps to open SQL*plus

Steps to open SQL*plus for the First time
In the previous lession we have discuss different tools used  for entering the sql statement In this lession we are going to discuss how to open sql* plus for the first time after succesfully instalation of oracle 10g take the following step to open the sql* plus tool to connect to the database.
step for open the sql * plus:-
 
follow the below diagram to open the sql* plus.

 once sql plus is runnig it will show the log in screen that ask three things username,password ,and host string.
in the host string we need to enter the name we have assign for tns connectivity in the file TNS.ORA .this file contain the machine name,port no,sid of the database we need to specify this when we are going to connect to server database.here we are connecting the local database that is why hightlet only the username and password field. so leave the host string blank and clicj on ok. after  successfully log in you will get the sql * plus editor for writing the sql statement .

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:

Oracle10g Installation steps

     In the previous lesson we have already discuss about what is database? what is oralce?   and all if you miss those lesson then just have a look here
what is database?
what is oracle?
in this section we will learn how to install the oracle10g .There are only few simple and easy step to intall the oracle10g. 
 Step1: Insert Oracle CD , the autorun window opens automatically. If you are installing from network or hard disk, click setup.exe in the installation folder.
 Step2:The Oracle Universal Installer (OUI) will run and display the Select Installation Method Window.

 Step3:  In the above screen there are two combo box
             a.Choose Basic Installation:
             b.Advanced Installation:
a.Choose Basic Installation:   Select this option to quickly install Oracle Database 10g. This method requires minimal user input. It installs the software and optionally creates a general-purpose database based on the information you provide.
For basic installation, you specify the following:
Oracle Home Location — Enter the directory in which to install the Oracle Database 10g software. You must specify a new Oracle home directory for each new installation of Oracle Database 10g. Use the default value, which is :

c:\oracle\product\10.2.0\db_1

Installation Type — Select Enterprise Edition :
If you have limited space, select standard edition. Personal edition installs the same software as the Enterprise Edition, but supports only a single-user development and deployment environment.

Create Starter Database — Check this box to create a database during installation. Oracle recommends that you create a starter database for first Create Starter Database — time installations. Choose a Global Database Name, like cs157b, or just use the default value.
Type a password. Don’t lose this password, since you will need it to connect to the database server.
Click next 
Step4:The Product-Specific Prerequisite Checks window appears: Click next

 
Step5:A summary screen appears showing information such as your global settings, space requirements and the new products to be installed. Click Install to start the installation.

Step6:The Install window appears showing installation progress.

 

Step7:At the end of the  installation phase, the Configuration Assistants window appears. This window lists the configuration assistants that are started automatically. If you are creating a database, then the Database Configuration Assistant starts automatically in a separate window.wait for some time untill the progress bar reaches to 100% . In this step it first coping the database file ,then it is  creating  the instaces and start the instances  .

 

Step8:At the end of database creation, you are prompted to unlock user accounts to make the accounts accessible. The SYS and SYSTEM accounts are already unlocked. Click OK to bypass password management.

Note: Oracle 10g still keeps scott / tiger username and password (UID=scott, PWD=tiger) from the old version of oracle. In the old version of oracle, scott/tiger user ID is available by default, but not in oracle 10g. If you want to use scott /tiger account, you must unlock it by clicking “Password Management” at the last window.
Password Management window will appear like the one shown below. Find the user name “Scott” and uncheck the “Lock Account?” column for the user name.

Step9:Your installation and database creation is now complete. The End of Installation window displays several important URLs, one of which is for Enterprise Manager.
  Related article 

• Step to open sql * plus

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:

How to Import data from a excel file

In this section we are going to disscuss the step how we can import data in database from the excel.for this section we have used the tools oracle10g,toad,excel.

Step1: First create a excel file which consists of field name like

S_NO

FNAME

MNAME

SNAME

SHORTNAME

Step2: Add a extra field

S_NO

FNAME

MNAME

SNAME

SHORTNAME

FORMULA FIELD

Step3: Add the formula in FORMULA FIELD like the below image

Press F2 then add this line

="UNION ALL SELECT '"&A2&"' AS S_NO,'"&B2&"' AS FNAME,'"&C2&"' AS MNAME,'"&D2&"' AS SNAME,'"&E2&"' AS SHORTNAME FROM DUAL"

Step4: Add this formula in all rows

Easy way copy(ctrl+c) formula and then press ctrl+shift+down arrow  all the row will be selected then paste  (ctrl+v)

Step5: Create a database with field name S_NO,FNAME,MNAME,SNAME,SHORTNAME .To know more about table creation visit here

Table creation for this session code is

CREATE TABLE INFO

(

    S_NO   INTEGER,

    FNAME  VARCHAR2(100),

    MNAME  VARCHAR2(100),

    SNAME  VARCHAR2(100),

    SHORTNAME VARCHAR2(100)

)

Step6: Copy the new added fomula field  and paste it in sql editor and remove the first union all and run the sql the whole data is in your dual table

Step7: Now write down the insert query to insert all data in table from  the dual table the sql insert look like this. To know more about sql insert visit here

insert into INFO (S_NO,FNAME,MNAME,SNAME,SHORTNAME)

 SELECT '1' AS S_NO,'William' AS FNAME,'F.' AS MNAME,'Cordes' AS SNAME,'William F. Cordes' AS SHORTNAME FROM DUAL

Step8:  Now chek  the data is in your table is correct or not by writing simple select query like this.

SELECT * FROM INFO

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: