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 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

Group by in Sql Server

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

Group by

Sometimes you need to show data in some groups with some aggregate function. In this situation you can use group by statement.

For example:-

Check the data of following table. Suppose you want to show the sum of all no of prod according to year. In that case you use group by statement.

Click Here to View Complete Artical

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

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.