Joins in SQL Server

Join in SQL Sever

When we need to select data from more than one table then we can use joins in SQL Server. We can join two or more tables on the basis of common column between these tables.

Consider the following table which I am using in this example:-

Table 1:- Emp_Record

create table Emp_Record(eid int primary key, ename varchar(100), email varchar(100))

Figure 1

Table 2:-emp_sal

create table emp_sal(eid int foreign key references emp_record(eid), salary int, ispermanent varchar(100))

Figure 2

Types of Joins

Inner Join

When we need to select the common data from more than one table then we will use inner join. It will select only those data which will be common in these two tables.

Read More

SQL Server Queries for Practice

Written By:- Isha Malhotra

Our Website:-Tech Altum

45 SQL Server  Queries for practice(21-45)- Part 2 

Create the Following Tables

1.       EmpReg

Column Name	Data type	Is Null	Constraints
EmpId	Int	No	Primary key
EmpName	Varchar(100)	No	it can not be blank
Gender	Varchar(10)	No	It can not be blank
DeptId	Int	No	Depends on the table dept with column Id
Phone	Varchar(100)	Yes
Mobile	Varchar(100)	No	Should be unique
Email	Varchar(100)	no	Should be unique
Country	Int	No	Depends on table country with column cid
state	Int	no	Depends on table country with column sid
Salary	Varchar(100)	No	Should be between 5000 to 15000
DateOfJoining	datetime	no

Use of XML in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Use of XML in SQL Server

When we need to store data in SQL Server in XML format .in this situation we can use XML Data Type.

For Example:-

Create the following table to store data:-

create table XmlExample(CustID int, Prod_Detail xml);

Insetion of DATA

We can insert as follows:-

insert into XmlExample values(1,'<TechAltum><Product><ID>P-101</ID><Name>Mobile</Name><Price>10,000</Price></Product></TechAltum>');

or

insert into XmlExample values(2,cast('<TechAltum><Product><ID>P-102</ID><Name>AC</Name><Price>18,000</Price></Product></TechAltum>' as XML));

Selection of DATA

select * from XmlExample

if you run this query then you will get following output:-

Figure 1

Range operator in SQL Server

Written By:- Isha Malhotra

Our Website:-Tech Altum

Range operator in SQL Server

Range operators are those operators which selects data according to particular range. There are two type of range operator and these are following:-

Between operator:-

Between operator is used to select the data between any range.

For example:-

select * from prod_rep where No_of_Prod between 1000 and 1200;

in this example it will select data between 1000 and 1200.

Note:-between operator include the range also. It means it will also select those data which contain 1000 and 1200.

Click Here to View Complete Tutorial

Compute and Compute By in Sql Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Compute and Compute By

Sometimes in your records you need to show all data of table with some aggregate.

For example:-

See the data of following table:-

Figure 1

Suppose I want to show the data of all columns and after that I want to show the count of gender.

In this situation we will use the compute in following way:-

Sorting of Data in SQL Server

Written By:- Isha Malhotra (Software Developer)
Email:-malhotra.isha3388@gmail.com.
Note: -if you find this article helpful then kindly leave your comment

Sorting of Data in SQL Server

If we want to sort the data on the particular column in that case we use order by command. We can sort data either in ascending order or in descending order. By default order by command sorts data in ascending order.

For example:-

Check the data of following table:-

Figure 1

Suppose I want to sort data according to the gender then I will use the following query:-

select * from Enqdata order by gender

Note:-by default it sorts data in ascending order.

Alter command in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Use of Alter in SQL Server

When we need to modify the definition of Table then we use alter command. Alter comes under DDL Commands.

To start with alter check the structure of following table:-

Figure 1

To Add New Column in Table

Suppose I want to add new column for dept. in this table then we use alter query in following manner:-

alter table emp

add dept varchar(100)

now run select * from emp

you will get following result:-

Figure 2

Use of Pivot in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Use of Pivot in SQL Server

When we need to format the result according to user‘s need then we use pivot. Pivot provides us facilities to use the column data as column.

For example:-

Check the following data in table:-

Figure 1

Use of Case in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Use of Case in SQL Server

Sometimes we need to show the data of table in different way at runtime. In this situation we can use case statement with select query.

For Example:-

Suppose we have the following table:-

Figure 1

Use of If –else statement in SQL Server

Use of If –else statement in SQL Server

We can also use if-else statement in SQL Server also.

Syntax for using if else is as follow:-

If(condition)

Begin

------------------------- working----------------------

End

Else

Begin

----------------------- working …………………..

End

For example:-

declare @id int;

set @id=10;

if(@id>5)

begin

print 'you have entered value more than 5';

end

else

begin

print 'you have entered value below 5'

end

When we execute the complete statement it will return the output as follows:-

‘You have entered value more than 5’

Similarly you can use nested if-else in it.

Hope you enjoyed the article.

hope you enjoyed this article. For any query you can mail me at malhotra.isha3388@gmail.com.

Join our Facebook page to get updates of Jobs, Interview Question, Course Details and Study Material

https://www.facebook.com/thetechaltum

Date function in SQL Server

Written By:- Isha Malhotra (Software Developer)

Email:-malhotra.isha3388@gmail.com.

Note: -if you find this article helpful then kindly leave your comment

Use of DATE Function in SQL Server

When we need to play with date in sql server, in that case we can use Date Function. Following are some function which I used in my project:-

     1.       Getdate()

This function returns the current date and time in format of ‘ yyyy-mm-dd hh:mm:ss’ where

Yyyy-year

mm-months

dd-date

hh-hour

mm-minutes

ss-Second

For example:-

select GETDATE();

     2.       Day(date)

This functin will return the day part from date in numeric format.

For example:-

select DAY('2013-03-20');

this query will return 20.

  3.  Year(date)

This functin will return the year part from date in numeric format.

For example:-

select year('2013-03-20');

this query will return 2013.