Oct32015 by Shashangka ShekharNo Comments

Aggregate Functions in SQL Server

Let’s create a SampleDB database fist with SQL Management Studio. Now create a table in SampleDB.

CREATE TABLE [dbo].[tblEmployee](
	[Emp_ID] [int] NOT NULL,
	[Emp_Name] [varchar](max) NOT NULL,
	[Emp_Age] [int] NOT NULL,
	[Emp_Salary] [int] NOT NULL,
	[Emp_City] [varchar](max) NOT NULL,
	[Emp_Gender] [char](1) NOT NULL,
 CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED 
(
	[Emp_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[tblEmployee](

[Emp_ID] [int] NOT NULL,

[Emp_Name] [varchar](max) NOT NULL,

[Emp_Age] [int] NOT NULL,

[Emp_Salary] [int] NOT NULL,

[Emp_City] [varchar](max) NOT NULL,

[Emp_Gender] [char](1) NOT NULL,

CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED

(

[Emp_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Insert Sample Data:

INSERT INTO [dbo].[tblEmployee]

SELECT 1,'Shashangka',20,35000,'Dhaka',1 UNION ALL
SELECT 2,'Shekhar',	19,	40000,	'Bagerhat',	1 UNION ALL
SELECT 3,'Shamim',	21,	35000,	'Dhaka',	0 UNION ALL
SELECT 4,'Shariar',	20,	40000,	'Rajshahi',	1 UNION ALL
SELECT 5,'Rajib',	22,	35000,	'Rajshahi',	0 UNION ALL
SELECT 6,'Avishek',	21,	35000,	'Barishal',	1 UNION ALL
SELECT 7,'Asif',	23,	40000,	'Dhaka',	0

INSERT INTO [dbo].[tblEmployee]

SELECT 1,'Shashangka',20,35000,'Dhaka',1 UNION ALL

SELECT 2,'Shekhar', 19, 40000, 'Bagerhat', 1 UNION ALL

SELECT 3,'Shamim', 21, 35000, 'Dhaka', 0 UNION ALL

SELECT 4,'Shariar', 20, 40000, 'Rajshahi', 1 UNION ALL

SELECT 5,'Rajib', 22, 35000, 'Rajshahi', 0 UNION ALL

SELECT 6,'Avishek', 21, 35000, 'Barishal', 1 UNION ALL

SELECT 7,'Asif', 23, 40000, 'Dhaka', 0

Aggregate Functions in SQL Server

COUNT (Transact-SQL)

Returns the number of items in a group. COUNT always returns an int data type value. Continue reading “Aggregate Functions in SQL Server” →

Oct32015 by Shashangka ShekharNo Comments

The History of SQL Server

MS SQL Server

SQL stands for Structured Query Language and is pronounced either ess-que-el orsequel. It is the language used by relational database management systems (RDBMS) to access and manipulate data and to create, structure and destroy databases and database objects. Continue reading “The History of SQL Server” →

Oct32015 by Shashangka ShekharNo Comments

Keyboard Shortcuts in SQL

MS SQL Server

This list displays the keyboard shortcuts available in SQL Query Analyzer.

CTRL+F2 :: Insert or remove a bookmark (toggle).
F2 :: Move to next bookmark.
SHIFT+F2 :: Move to previous bookmark.
ALT+BREAK :: Cancel a query. Continue reading “Keyboard Shortcuts in SQL” →

Oct32015 by Shashangka ShekharNo Comments

Table Variables

T-SQL

Temp variable can be created like this:

DECLARE @temp_tblEmployee TABLE 
(
	[Emp_ID] [int] NOT NULL,
	[Emp_Name] [varchar](max) NOT NULL
)
INSERT INTO @temp_tblEmployee
SELECT 1,'Shashangka Shekhar'

SELECT * FROM @temp_tblEmployee

GO

DECLARE @temp_tblEmployee TABLE

(

[Emp_ID] [int] NOT NULL,

[Emp_Name] [varchar](max) NOT NULL

)

INSERT INTO @temp_tblEmployee

SELECT 1,'Shashangka Shekhar'

SELECT * FROM @temp_tblEmployee

Table variable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.

Oct32015 by Shashangka ShekharNo Comments

Temporary Tables

T-SQL

Types of Temporary Tables
SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:

Local Temp Table
Global Temp Table

Continue reading “Temporary Tables” →

Oct32015 by Shashangka ShekharNo Comments

Views In SQL Server

T-SQL

Let’s create a SampleDB database fist with SQL Management Studio. Now we have to create a table in SampleDB.

Let’s Name the table as – Employee

CREATE TABLE [dbo].[tblEmployee](
	[Emp_ID] [int] NOT NULL,
	[Emp_Name] [varchar](max) NOT NULL,
	[Emp_Age] [int] NOT NULL,
	[Emp_Salary] [int] NOT NULL,
	[Emp_City] [varchar](max) NOT NULL,
	[Emp_Gender] [char](1) NOT NULL,
 CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED 
(
	[Emp_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[tblEmployee](

[Emp_ID] [int] NOT NULL,

[Emp_Name] [varchar](max) NOT NULL,

[Emp_Age] [int] NOT NULL,

[Emp_Salary] [int] NOT NULL,

[Emp_City] [varchar](max) NOT NULL,

[Emp_Gender] [char](1) NOT NULL,

CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED

(

[Emp_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Continue reading “Views In SQL Server” →

SHASHANGKA SHEKHAR'S BLOG

Category: MS SQL Server

Aggregate Functions in SQL Server

Aggregate Functions in SQL Server

COUNT (Transact-SQL)

The History of SQL Server

Keyboard Shortcuts in SQL

Table Variables

Temporary Tables

Views In SQL Server