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:

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:

Top Tips for SQL Server2008

How to check that SQL Server 2008 has installed correctly
Here are a short number of post-installation checks which are useful to perform after re-booting your new SQL Server. You don’t have to run these, and there are other ways to check, but they are very useful for non-DBAs to be sure that the installation is basically sound and a connection can be made to the new SQL Server before handing it over to someone else.
Check 1: Has the SQL Server Service Started?
Check SQL Server 2008 has started.

Check 2: Does Management Studio Work?
Check Management Studio works by firing it up.


Click on NO when you see this dialog box:


Check 3: Can you run a basic query against the new SQL Server?
Check SQL Server works by running a simple query from Management Studio:

Enter the query shown below and hit F5 to run it:

Check 4: Is SQL Server Agent Running?
Check SQL Server Agent is running for scheduled jobs. There should be a green arrow next to the SQL Server Agent database symbol (it’s small, you might have to look hard):

Check 5: Can SQL Server be seen from the Network?
Check that the new SQL Server can be seen from another SQL Server on the same domain by running isql –L (or osql –L):
If you can’t see the new SQL Server in this list, check that the SQL Server Browser service is started on the machine where you have just installed SQL Server.
Check 6: Has the TCP/IP network protocol library been enabled on the server?
If the browser service is started but you still cannot connect to the server, click on Start ->Programs -> SQL Server 2008 -> SQL Server Configuration Manager(on the server where SQL Server’s just been installed)

The SQL Server Configuration Manager window opens.
Click on the SQL Server Network Configurationnode and expand it.
In the example below, we have MSSQLSERVER (a base instance of SQL Server), and SQLEXPRESS showing as installed.
If in doubt, click on Protocols for MSSQLSERVER.

In the above screenshot, the TCP/IP network protocol library is disabled. We need to enable it in order that remote servers can talk to the newly installed SQL Server.

• A word of explanation : In most installations, Named Pipes can be ignored, unless there is a requirement for it. In virtually all environments, VIA can also be ignored as this protocol requires a special network card. Shared memory is the “local” protocol that SQL Server uses when talking to a client application on the same server as itself, for example when SQL Server Management Studio connects to it. It is usually best to leave this enabled.

You will need the TCP/IP protocol enabled if you need to connect to your new SQL Server from a remote client or another server via TCP/IP, which is what most networks use.
If it shows as DISABLED (above), double click on the TCP/IP protocol line, and the following window will appear:

Ensure that Enabled is set to Yes, and click on OK.
The following warning will appear:

Click on OK, and you will be returned to the Configuration Manager window, where TCP/IP will now be shown as enabled:

Go back to the Services applet, and re-start the MSSQLSERVER service so that the TCP/IP protocol can be used to connect to your new SQL Server.
Then try to connect to it again from a remote machine.
If you have experienced problems with the previous connectivity tests, you should now be able to repeat at least some of them successfully.

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

Installing SQL Server 2008

A Step by Step guide to installing SQL Server 2008 simply and successfully with no prior knowledge
Developers and system administrators will find this installation guide useful, as will seasoned DBAs. It will teach you the basics required for a typical, problem-free installation of SQL Server 2008, allowing you to add other components later if you wish.

Remember to install the .Net Framework 3.5
Before you start the installation, you’ll need to install the .Net 3.5 Framework. This comes pre-installed on Windows 2008 Server, but for earlier versions of Windows, you’ll need to install it first. This is a straightforward pre-requisite and is usually included as part of the SQL Server 2008 installation. However, if you don’t know how to do this, or for some reason you need to download it, check out the guide
Installing .Net Framework 3.5 for SQL Server 2008.
official link to download Sql Server2008 
Torrent link: SQL Server 2008
Once this Framework in installed you can commence the installation of SQL Server 2008.
STEP 1 : Copy the installation files
First off I’d recommend you copy the entire directory structure from the SQL Server 2008 installation disc to the C: drive of the machine you are going to install it on.
Although this means you need to grab a cup of coffee whilst it’s copying, this has three advantages:

• It makes the installation process much faster than running it from CD/DVD once it gets started.
• It allows you to easily add or remove components later, without having to hunt around for the CD/DVD.
• If your media is damaged and a file won’t copy, you get to find out now, rather than halfway through the installation.

Here’s what my system looks like after the copy:
STEP 2 : Setup.exe Double click on the setup.exe file.
After a few seconds a dialog box appears:

This will disappear from the screen and then the main installation page appears:

STEP 3 : SQL Server Installation Center
Click on the Installation hyperlink on the left hand side of the screen:
STEP 4 : SQL Server Installation Center  Click on the “New Server stand-alone installation” link on the right side of the screen:
The following dialog appears on the screen whilst the install program prepares for installation:
After a minute or so (the timing will vary according to your system), the following screen appears:
 
STEP 5 (optional) :
If any checks have failed, click on the Show details button or “View detailed report link” to find out the cause, correct it, then click on the Re-run button to perform the checks again.
STEP 6 : Product key
If all checks have passed, click on the OK button. After a few moments, the option to select the edition and to enter the license key (or “product key”) will appear. Note that the product key box may already be populated, depending on which edition you have. Don’t enter the product key we’ve shown here, it won’t work on your system!:
STEP 7 : License TermsEnter the product key into the box, or choose the free edition if you’re evaluating SQL Server 2008, and click on the Next button:
Click in the “I accept the license terms” check box, then click on the Nextbutton again.
STEP 8 : Setup Support Files
The following screen appears; click on the Install button:
The following screen will appear whilst Windows Installer prepares itself for the installation. This will take a short while:
After 30 seconds or so the dialog appears again:

STEP 9 : Setup Support Rules
If all is well, the following screen appears:
Click on the Nextbutton again.
STEP 10 : Feature Selection
Select the features you want to install.
At a minimum, the following are useful (I’d argue essential), but what you need will depend on your needs:
Click on the Next button.
STEP 11 : Instance Configuration
After a short while the following screen appears:
For most installations, keep the default settings.
Click on the Nextbutton.
STEP 12 : Disk Space Requirements
This screen just tells you if you have sufficient disk space on the drive you’re installing to, and what’s going to be installed where.
Click on Next.
STEP 13 : Server Configuration
This step allows you to set up the service accounts that will be used to run SQL Server. If you have created Windows NT or Active Directory accounts for use with services, use these.
If not, then just to get the installation up and working, use the built-in Network Service account for all three services listed (this account does not require a password).
This allows SQL Server to start up after installation. However, it can be easily changed later to another account through the Services applet (Control Panel -> Administrator Tools -> Services):

In addition, remember to change the Startup Type to Automatic, for all three services. This automatically starts the SQL Server database engine, SQL Agent and SQL Browser services when the server is re-booted.
The first service runs the SQL Server database engines executable process. The other two services allow scheduled jobs to run after installation (and after a re-boot), and allow the SQL Server to be found by clients on the network.
Do not worry about changing the collation tab, unless there is a specific requirement for anything other than the default collation sequence. Finally, click on Next.
STEP 14 : Database Engine Configuration – Account Provision
This screen allows you to set up database engine security.

Change the Authentication Mode to Mixed Mode unless you are certainyou only need Windows-only authentication.

• Many third party applications rely on SQL Server logins to operate correctly, so if you are setting up a server for a third party application, rather than one developed in-house, enabling Mixed Mode authentication is a good idea.

If you pick Mixed Mode security, you must also enter a password for the sysadmin account (sa).
Enter and confirm a secure password for the sa account and keep it somewhere safe. Do not give it to any one you do not want to have access to the SQL Server.
Note that you MUST also provide a Windows NT account on the local machine as a SQL Server administrator. If you do not want Windows system administrators to be able walk up to the box and login to SQL Server, create a new, local, dummy Windows user and add this account instead. Otherwise, add in the local administrator account, or your own Windows account on the domain in which the SQL Server will reside.
STEP 15 : Database Engine Configuration – Data Directories
Click on the Data Directoriestab.

Change the directories to specify which drives in your system will be used for the various types of database files.
Generally it’s advisable to put the User database directory and User log directory on separate physical drives for performance, but it will depend on how Windows has been configured and how many disk drives you have available.
If you are installing on a single drive laptop or desktop, then simply specify:

Data root directory	C:\Program Files\Microsoft SQL Server
User database directory	C:\Data
User log directory	C:\Logs
Temp DB directory	C:\TempDB
Temp Log directory	C:\TempDB
Backup directory	C:\Backups

Do not click on the FILESTREAM tab unless you know you need to change these options, as it is not generally required for most installations, but can easily be changed by using sp_configure ‘filestream_access_level’, ”after SQL Server has been installed. Click on Next.
STEP 16 : Error Usage Reporting
This screen simply asks if you want to send error information to Microsoft and can safely be skipped if you do not want to share any information.

Click boxes if you want to help Microsoft help you.
Click on Nextagain…
STEP 16 : Installation Rules
This screen simply checks if there are any processes or other installations running which will stop the installation of SQL Server 2008.

Click on Next again – you’re almost ready to install:
STEP 17 : Ready to Install
This screen summarises what you are about to install and gives you a last chance to cancel or change anything that’s wrongly configured:

Check that what’s being installed is what you want and then click on Install when you’re sure you want to start the installation process:
Installation Progress
SQL Server 2008 will now install. How long it takes depends on the speed of your machine, what load it’s under, the installation media (CD is slower) and what you’ve chosen to install.

…More Installation Progress

… and Finally
Finally, the installation will complete:

…and the following dialog box will appear:

Click on OK, the machine will NOT reboot.
The following will appear:

…followed by:

Click on the Next button again…
STEP 18 : Installation Complete
The following screen appears:

It may be worth clicking on the installation log at the top of the screen to check everything’s gone as expected. Not that this is MUCH smaller than the usual SQL Server installation log files of old.
Finally, click on the Close button. The following dialog will appear:

Click on OK – your server will NOT re-boot at this point.
The dialog box will disappear and you will be returned to the Installation Center:

Click on the Close button (the “x”) in the top right of the screen.
Finally, manually reboot your machine to complete the SQL Server 2008 installation.

you may also like:

1. Top Tips for SQL Server2008 

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