Wednesday 14 August 2013

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.



Sunday 4 August 2013

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.

Friday 19 April 2013

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.

Monday 15 April 2013

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






Saturday 13 April 2013

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

Sunday 7 April 2013

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;


Tuesday 26 March 2013

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.