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

User defined function in SQL Server

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

User defined function in SQL Server

Functions are basically a separate area where we write our code and according to the need we can call it.

SQL Server allows you to create functions, as we create functions in programming language. We can pass parameter to function and function can also return the output. These are fast as these are precompiled.

Consider the following table prod_rep as an example:-

Figure 1

Type of User defined function in SQL Server

According to its return type we categorized the user defined function into following two parts

Scalar function

In scalar function, we can return a single value from the function.

For example:-

Suppose I have to create function which will return the dept according to id.

Use of percent(%) in Top command in SQL Server

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

Use of percent(%) in Top command in SQL Server

In my last article I discussed about the top command. In this article I am showing how to use percent with top command. I am taking the table same as I used in my last article.

Use of percent in Top command

 If I Want to select 2 rows from the top then I will write the following query:-

select top 2 * from prod_rep

But we can also select Data in percentage (%) using top command. Using percentage it will show the row Accordingly. For example if table contains 4 rows and I select 50% then it will Show 2 rows.

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

SQL Server Queries for Practice

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

45 SQL Server Queries for Practice (1-20) - Part 1

Following is the Employee Table which contains the detail for the Employee with department id and head id. Head of the employee is also an another employee

Table:-Employee

EmpId	EmpName	Department	ContactNo	EmailId	EmpHeadId
101	Isha	E-101	1234567890	abc@yahoo.com	105
102	Priya	E-104	1234567890	abc@gmail.com	103
103	Neha	E-101	1234567890	abc@gmail.com	101
104	Rahul	E-102	1234567890	abc@ymail.com	105
105	abhishek	E-101	1234567890	abc@gmail.com	102

Table:-EmpDept

DeptId	DeptName	DeptHead
E-101	HR	105
E-102	Development	101
E-103	House Keeping
E-104	sales	104
E-105	purchase	104

Use of Top command in SQL Server

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

Use of Top command in SQL Server

The top command is used to select the top value from the table. We have to give the number of row with the top command.

For Example:-

Consider the following table as example in which it shows the prod_year, dept and no_of_prod.

Figure 1

Suppose in this table if you want to select top 2 rows then you will use the top command as follows:-

select top 2 * from prod_rep;

Click Here to View Complete Artical

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