Category Archives: SQL

Pivot table example in SQL SERVER

Posted by Abdul Rehman Khan on

Problem Statement:-

There are students, courses and courses are registered to students. I want to get a list of student and their courses by months.

Solution:-

Create table Course:-

–CREATE TABLE [dbo].[Course](
[CourseID] [int] IDENTITY(1,1) NOT NULL,
[CourseName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
[CourseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 

— Create table Student

CREATE TABLE [dbo].[Student](
[StudentID] [int] IDENTITY(1,1) NOT NULL,
[StudentName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 

— Create table StudentCourse

CREATE TABLE [dbo].[StudentCourse](
[StudentCourseID] [int] IDENTITY(1,1) NOT NULL,
[StudentID] [int] NOT NULL,
[CourseID] [int] NOT NULL,
[RegDate] [smalldatetime] NOT NULL,
CONSTRAINT [PK_StudentCourse] PRIMARY KEY CLUSTERED
(
[StudentCourseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 

—- Insert data into Course
–INSERT INTO Course ( CourseName ) VALUES (‘Maths’)
–INSERT INTO Course ( CourseName ) VALUES (‘Physics’)
–INSERT INTO Course ( CourseName ) VALUES (‘Computer’)

—- Insert data into Student
–INSERT INTO Student( StudentName )VALUES (‘Faisal’)
–INSERT INTO Student( StudentName )VALUES (‘Irfan’)
–INSERT INTO Student( StudentName )VALUES (‘Haris’)

—- Insert data into Student Course
–INSERT INTO dbo.StudentCourse( StudentID, CourseID, RegDate )VALUES ( 1, 1, ’07/05/2014′ )
–INSERT INTO dbo.StudentCourse( StudentID, CourseID, RegDate )VALUES ( 1, 2, ’07/05/2014′ )
–INSERT INTO dbo.StudentCourse( StudentID, CourseID, RegDate )VALUES ( 2, 1, ’08/05/2014′ )
–INSERT INTO dbo.StudentCourse( StudentID, CourseID, RegDate )VALUES ( 2, 3, ’09/05/2014′ )
–INSERT INTO dbo.StudentCourse( StudentID, CourseID, RegDate )VALUES ( 3, 2, ’09/05/2014′ )

 

— Following is the query to get the required results.
SELECT * FROM (
select
S.StudentName,
C.CourseName,
MONTH(SC.RegDate) RegMonth
from
dbo.Student S
INNER JOIN dbo.StudentCourse SC ON S.StudentID = SC.StudentID
INNER JOIN dbo.Course C ON SC.CourseID = C.CourseID
)a
pivot (count(a.CourseName) for a.StudentName in ([Faisal],[Irfan],[Haris])) as NoofCoursesPErStudent

 

In the above query i am taking count of CourseName field and showing each student name as a separate column as in ([Faisal],[Irfan],[Haris]);

 

Result :-

 

pivot

Arabic Characters are coming a Question Mark

Posted by Abdul Rehman Khan on

Problem Statement :-  I was fetching some data from a mysql DB and was inserting that into second mysql DB. I was performing this task using ASP.NET. I got an issue that the arabic characters were coming as question mark.

Solution :- I added ” CHARSET=utf8 ” to the connection string and the issue resolved.

Optimize SQL Queries

Posted by Abdul Rehman Khan on

I want to share few of my experiences and knowledge about optimizing the SQL Query performance, which are as follows:

1. Specify data types while passing the parameters into the SQL Stored Procedure from the Application.

2. Arrange the where clauses according to the data.

3. Apply No lock in select queries, because while fetching the data, the lock would not be applied on the table. While inserting / updating / deleting, it should be there.

SQL Query to find the Foreign Key Relations in MS SQL SERVER

Posted by Abdul Rehman Khan on

SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

Except statement in SQL Server

Posted by Abdul Rehman Khan on

— Create Table Student

CREATE TABLE student(

StudentID int IDENTITY(1,1) NOT NULL,

StudentName varchar(50) NULL

CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED

(

[StudentID] ASC

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

) ON [PRIMARY]


— Insert the values

INSERT INTO student (StudentName) VALUES (‘Faisal’)
INSERT INTO student (StudentName) VALUES (‘Farhan’)
INSERT INTO student (StudentName) VALUES (‘Zeeshan’)
INSERT INTO student (StudentName) VALUES (‘Imran’)


— Select all values

SELECT name FROM student

— Except statement

EXCEPT

— Select values with name ends with an

SELECT name FROM student WHERE name LIKE ‘%an’

Intersect statement in SQL Server

Posted by Abdul Rehman Khan on

— Create Table Student

CREATE TABLE student(

StudentID int IDENTITY(1,1) NOT NULL,

StudentName varchar(50) NULL

CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED

(

[StudentID] ASC

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

) ON [PRIMARY]


— Insert the values

INSERT INTO student (StudentName) VALUES (‘Faisal’)
INSERT INTO student (StudentName) VALUES (‘Farhan’)
INSERT INTO student (StudentName) VALUES (‘Zeeshan’)
INSERT INTO student (StudentName) VALUES (‘Imran’)


— Select all values

SELECT name FROM student

— Intersect statement

INTERSECT

— Select values with name ends with an

SELECT name FROM student WHERE name LIKE ‘%an’

Partition by clause in SQL-Server

Posted by Abdul Rehman Khan on

– Create Table Student

CREATE TABLE student(
StudentID int IDENTITY(1,1) NOT NULL,
StudentName varchar(50) NULL,
StudentContact varchar(50) NULL,
CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED 
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

– Create Table StudentAddress

CREATE TABLE StudentAddress(
S_Add_Id int IDENTITY(1,1) NOT NULL,
Address varchar(100) NOT NULL,
StudentID int NOT NULL,
CONSTRAINT [PK_StudentAddress] PRIMARY KEY CLUSTERED 
(
[S_Add_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]

– Add the Foreign Key reference

ALTER TABLE [dbo].[StudentAddress] WITH CHECK ADD CONSTRAINT [FK_StudentAddress_student] FOREIGN KEY([StudentID])
REFERENCES [dbo].[student] ([StudentID])

– Insert the values in Student and StudentAddress table

INSERT INTO student (StudentName,StudentContact)
VALUES (‘Imran’,’6545456454′)
INSERT INTO student (StudentName,StudentContact)
VALUES (‘Faraz’,’2223232′)

INSERT INTO StudentAddress(Address,StudentID)
VALUES(‘Street 1 Karachi’,1)
INSERT INTO StudentAddress(Address,StudentID)
VALUES(‘Street 2 LAhore’,1)

INSERT INTO StudentAddress(Address,StudentID)
VALUES(‘Street 100 Rawalpindi’,2)

 

— Query to select the Student names and their addresses group by StudentName but StudentName should not be repeated in the list.

SELECT
StudentID,

— Checking if it is first row then StudentName should come else it will select empty value.

CASE RowNum WHEN 1 THEN StudentName ELSE ” END AS StudentName,
Address
FROM
(
SELECT
S.StudentID,
S.StudentName,
SAddress.Address,
ROW_NUMBER() OVER (PARTITION BY S.StudentName ORDER BY S.StudentID) AS RowNum
FROM
student S
INNER JOIN StudentAddress SAddress ON S.StudentID = SAddress.StudentID
GROUP BY S.StudentName,SAddress.Address,S.StudentID
)A