Dec 08

WCF Beginners – Step by step WCF approach

WCF stands for Windows Communication Foundation. It is a framework for building service-oriented applications. It enables developers to build secure, reliable, transacted solutions that integrate across platforms and interoperate with existing investments. WCF used to send messages between client and service, these messages can be sent over intranet or the internet using common transport protocols such as TCP or HTTP.

This post is intended to give you quick exposure to Windows Communication Foundation programming experience. This post helps you to understand the steps involved to create WCF service and client application.

A service exposes one or more endpoints which contains one or more service operations. The endpoint of service specifies and address where service can be found, binding contains the information that describes how a client must communicate with the service and contract defines the functionality provided by the service to its clients.

 To create any WCF service we need to do following things,

  1. Create visual studio project structure
  2. Add WCF Service
  3. Define Data Contract 
  4. Create Business Layer
  5. Define Service Contract
  6. Implement Service Contract
  7. Creating and consuming Service from Console based Client application
  8. Test it

So let’s start with step by step approach to create WCF service. I hope you must have Visual Studio installed on your machine, Here I have used Visual Studio 2017 Community Edition to build WCF service and its console based client.

Step 1: Open Visual Studio and create project structure 

Create visual studio solution WcfBasics.sol

Create Solution

Create Wcf Service Application i.e. CsharpDocs.WcfApp and remove default services created i.e. Service1.svc

create wcf service application

Add .Net Framework based Console application within above solution i.e. CsharpDocs.Wcf.Client

create client

 

Create sub folders Contracts, Interface and Utils for better understanding (this is not mandatory but good practice)

Step 2: Add WCF Service(.svc) file

Create service file (WeatherForcastService.svc) by right clicking on CsharpDocs.WcfApp project -> select Add New Item -> Web -> WCF Service

add service

Move IWeatherForcastService.cs file to Interface folder and rename it to IWeatherForcast to keep proper naming conventions

That’s it !! your folder structure will look like below

Step 3: Define Data Contract

Create WeatherInfo.cs file inside Contracts folder and add below code. Here the class name is decorated with DataContract attribute and properties are decorated with DataMember attribute. These DataContract (message) is used to pass between client and service.

using System.Runtime.Serialization;

namespace CsharpDocs.WcfApp.Contracts
{
    [DataContract]
    public class WeatherInfo
    {
        [DataMember]
        public string City { get; set; }

        [DataMember]
        public string Tempreture { get; set; }
    }
}
Step 4: Create Business Layer

Create WeatherHelper.cs file inside Utils folder and add below code. I have created two functions to explain concepts. In practical scenario this would be business layer calling data access layer. Do the appropriate changes(separate .dll for Business Layer and Data Access Layer (using EF or ADO.NET)) while accessing SQL/Oracle data access.

using CsharpDocs.WcfApp.Contracts;
using System.Collections.Generic;
using System.Linq;

namespace CsharpDocs.WcfApp.Utils
{
    public class WeatherHelper
    {
        public IEnumerable<WeatherInfo> GetAll()
        {
            return new List
            {
                    new WeatherInfo { City = "Delhi", Tempreture="09" },
                    new WeatherInfo { City = "Mumbai", Tempreture="26"},
                    new WeatherInfo { City = "Kolkata", Tempreture="16"},
                    new WeatherInfo { City = "Pune", Tempreture="17"},
                    new WeatherInfo { City = "Nagpur", Tempreture="13"},
                    new WeatherInfo { City = "Indore", Tempreture="15"},
                    new WeatherInfo { City = "Ahmadabad", Tempreture="20"},
                    new WeatherInfo { City = "Vadodara", Tempreture="20"},
                    new WeatherInfo { City = "Chandigrah", Tempreture="8"},
                    new WeatherInfo { City = "Leh", Tempreture="-13"},
                    new WeatherInfo { City = "Manali", Tempreture="-06"},
                    new WeatherInfo { City = "Jaipur", Tempreture="16"},
                    new WeatherInfo { City = "Hyderabad", Tempreture="18"},
                    new WeatherInfo { City = "Bangalore", Tempreture="18"},
                    new WeatherInfo { City = "Chennai", Tempreture="26"}
                };
        }

        public WeatherInfo GetWeatherInfo(string city)
        {
            var weatherInfos = GetAll();

            return weatherInfos.Where(x => x.City.ToLower() == city.ToLower()).Single();
        }
    }
}
Step 5:  Define Service Contract

Here IWeatherForecast service defines two functionalities i.e. weather information of all major cities and for perticular city exposed by service. Here interface decorated with ServiceContract attribute and the functions exposed by service are decorated with OperationContract attribute.

using CsharpDocs.WcfApp.Contracts;
using System.Collections.Generic;
using System.ServiceModel;

namespace CsharpDocs.WcfApp.Interface
{
    [ServiceContract]
    public interface IWeatherForcast
    {
        [OperationContract]
        IEnumerable<WeatherInfo> GetAll();

        [OperationContract]
        WeatherInfo GetWeatherInfo(string city);
    }
}
Step 6: Implement Service Contract

Here WatherForecastService describes the implementation of service contract. Here I have created instance of business layer (WeatherHelper) and called appropriate function. 

using CsharpDocs.WcfApp.Contracts;
using CsharpDocs.WcfApp.Interface;
using CsharpDocs.WcfApp.Utils;
using System.Collections.Generic;

namespace CsharpDocs.WcfApp
{
    public class WeatherForcastService : IWeatherForcast
    {
        public IEnumerable<WeatherInfo> GetAll()
        {
            var weatherHelper = new WeatherHelper();
            return weatherHelper.GetAll();
        }

        public WeatherInfo GetWeatherInfo(string city)
        {
            var weatherHelper = new WeatherHelper();
            return weatherHelper.GetWeatherInfo(city);
        }
    }
}

Here is the web.config file code of my WCF service




  
    
  
  
    
    
  
  
    
      
        
          
          
          
          
        
      
    
    
        
        
    
  
  
    
    
    
  

Step 7: Creating and consuming Service from Console based Client application

We have already created CsharpDocs.Wcf.Client project. So, first Add Service Reference of WCF service. This web reference will acts as a proxy of service on which we invoke method call.

Right click on Connected Services in CsharpDocs.Wcf.Client project and select Add Service Reference… option

Now click on Discover button which will show you services available. Give the namespace and click Ok button to add service reference

add service reference in client

Replace below code in Program.cs file Here i have added CsharpDocs.Wcf.Client.WeatherForcastService as using statement where WeatherForcastClient resides. This is our proxy class. Create the instance of proxy class and call the appropriate function on it.

using CsharpDocs.Wcf.Client.WeatherForcastService;
using System;

namespace CsharpDocs.Wcf.Client
{
    public class Program
    {
        static void Main(string[] args)
        {
            GetAll();
            Console.Write("\n\n\n----------------------------------------------------\n\n\n");
            GetWeatherInfo();
            Console.ReadKey();
        }

        private static void GetAll()
        {
            var weatherForcastClient = new WeatherForcastClient();
            var cityWathers = weatherForcastClient.GetAll();

            Console.Write("Weather of major cities in india on 3rd Dec 2017 \n\n");
            Console.Write(string.Format("City                 Tempreture(in degree celcius)\n"));
            foreach (var weather in cityWathers)
            {
                Console.Write(string.Format("{0}                 {1}\n", weather.City, weather.Tempreture));
            }
        }

        private static void GetWeatherInfo()
        {
            var weatherForcastClient = new WeatherForcastClient();
            var cityWather = weatherForcastClient.GetWeatherInfo("Pune");

            Console.Write("Weather of Pune on 3rd Dec 2017 \n");
            Console.Write(string.Format("{0} degree celcius", cityWather.Tempreture));
        }
    }
}

Once you add the service reference in client application it aromatically creates endpoint and address in app.config file as below


Step 8: Lets test it

I hope you enjoyed this article. Please don’t forget to like, comment, share and subscribe to this post. 

Nov 26

Student Course Instructor based SQL Queries

In my last post we discussed about Student, Course and Instructor based database design and relationships. In this post I will show you how interviewer will ask questions. I hope you already went through previous post.

Question: Find students who enrolled more than two courses
SELECT s.StudentId, (s.FirstName + ' ' + s.LastName) as Student, COUNT(CourseId) as CoursesTaken
FROM Student AS s INNER JOIN StudentCourse AS sc ON s.StudentId = sc.StudentId
GROUP BY s.StudentId, s.FirstName, s.LastName
HAVING COUNT(CourseId) > 2
ORDER BY CoursesTaken DESC
Students who enrolled more than two courses

Students who enrolled more than two courses

Question: Find department with maximum number of students
SELECT d.DepartmentName, COUNT(SC.StudentId) AS StudentCount
FROM Department d 
INNER JOIN Course c ON d.DepartmentId = c.DepartmentId
INNER JOIN StudentCourse sc ON sc.CourseId = c.CourseId
GROUP BY d.DepartmentName
Department with maximum number of students

Department with maximum number of students

Question: Top three popular courses (this means maximum number of students enrolled for course)
SELECT TOP 3 c.CourseName, COUNT(SC.StudentId) AS StudentCount
FROM Course c
INNER JOIN StudentCourse sc ON sc.CourseId = c.CourseId
GROUP BY c.CourseName
ORDER BY StudentCount DESC
Top three popular courses

Top three popular courses

Question: Top five instructors with maximum students in a course
SELECT TOP 5 (i.FirstName + ' ' + i.LastName) AS InstructorName, COUNT(SC.StudentId) AS StudentCount
FROM Instructor i
INNER JOIN CourseInstructor ci ON i.InstructorId = ci.InstructorId
INNER JOIN StudentCourse sc ON sc.CourseId = ci.CourseId
GROUP BY i.FirstName, i.LastName
ORDER BY StudentCount DESC
Top three instructors with maximum students in a course

Top three instructors with maximum students in a course

Question: Department with maximum number of courses
SELECT DepartmentName, COUNT(c.CourseId) AS CourseCount
FROM Department d 
LEFT JOIN Course c ON d.DepartmentId = c.DepartmentId
GROUP BY d.DepartmentName
ORDER BY CourseCount DESC
Department with maximum number of courses

Department with maximum number of courses

Similarly, the questions based on dates of birth, instructor hire date are

Question: Youngest/oldest student for each department or youngest student for course
Question: Senior most instructor (on hire date) for each department

There could be more number of questions but the general idea is questions are based on group by and having clauses. I hope you all enjoyed this post. Don’t forget to like, comment, subscribe and share this post.

Nov 25

Student Course Instructor based SQL Interview Questions

School based system design is one of most used case to start with any learning application. The typical school based system has four main entities i.e. Student, Instructor, Department and Course

There are number of possibilities that interviewer may ask questions. So here I will show you the detail understanding of School based system.

Database or SQL Interviewer will check your approach to design tables, their relationships and query skills etc.

Full stack developer Interviewer will check above plus Class Design, Entity Framework/ADO.NET,  Repositories, Services Required, Controller Actions and UI presentation using Angular or MVC etc etc..

The typical requirement contains following use cases or requirements

  1. College contains many departments
  2. Each department offers number of courses
  3. Many instructors can work in a department
  4. An instructor can be associated to only one department
  5. One head for each department
  6. Instructor can be head only one department
  7. Instructor take only one course
  8. Student can enroll for any number of courses
  9. Each course can have any number of students (here max 30 student for one course)
  10. Removal of department should also removes the courses and student associated 
  11. Removal of course should also removes the students associated

We will see how the system design should be to answer this question. So let’s start…

Now from above it is clear that there would be four basic entities like student, instructor, department and course. So, first create basic tables like Student, Instructor, Department and Course. I am not going details of all attributes that each entity holds.

CREATE TABLE [dbo].[Course](
       [CourseId] [int] IDENTITY(1,1) NOT NULL,
       [CourseName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
       [CourseId] ASC
)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Department](
       [DepartmentId] [int] IDENTITY(1,1) NOT NULL,
       [DepartmentName] [nvarchar](50) NOT NULL,
       [DepartmentLocation] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
       [DepartmentId] ASC
)
) ON [PRIMARY]
 
GO

CREATE TABLE [dbo].[Instructor](
       [InstructorId] [int] IDENTITY(1,1) NOT NULL,
       [FirstName] [nvarchar](50) NOT NULL,
       [LastName] [nvarchar](50) NOT NULL,
       [Phone] [nvarchar](50) NULL,
CONSTRAINT [PK_Instructor] PRIMARY KEY CLUSTERED
(
       [InstructorId] ASC
)
) ON [PRIMARY]
 
GO

CREATE TABLE [dbo].[Student](
       [StudentId] [int] IDENTITY(1,1) NOT NULL,
       [FirstName] [nvarchar](50) NOT NULL,
       [LastName] [nvarchar](50) NOT NULL,
       [Phone] [nvarchar](50) NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
       [StudentId] ASC
)
) ON [PRIMARY]
 
GO

Now, each department offers number of courses hence add additional (required/mandatory) column  DepartmentId to course table. This is one to many (1:M) relationship.

ALTER TABLE [dbo].[Course]
ADD DepartmentId INT NOT NULL

If the department is removed then the courses associated with department needs to be remove. So, we need ON DELETE CASCADE relationship between Department and Course table. Let’s add foreign key to DepartmentId in Course table. As the DepartmentId is mandatory in Course table hence add additional CHECK constraint on Course table.

ALTER TABLE [dbo].[Course]  WITH CHECK ADD CONSTRAINT [FK_Course_Department_DepartmentID] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Department] ([DepartmentID])
ON DELETE CASCADE
GO
 
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department_DepartmentID]
GO

Instructor can be associated to only one department and instructor is can also be head of the department. So we need InstructorId in Department table. This is one-to-one (1:1) relationship between department and instructor (as a head of department)

ALTER table [Department]
ADD InstructorId INT NULL
GO

Let’s add foreign key to InstructorId in Department table. As the InstructorId is mandatory in Department table hence add additional CHECK constraint on Department table.

ALTER TABLE [dbo].[Department]  WITH CHECK ADD  CONSTRAINT [FK_Department_Instructor_InstructorId] FOREIGN KEY([InstructorId])
REFERENCES [dbo].[Instructor] ([InstructorId])
GO
 
ALTER TABLE [dbo].[Department] CHECK CONSTRAINT [FK_Department_Instructor_InstructorId]
GO

Now each student can enroll to any number of courses. So we need additional table to keep track of mapping between course and students. So, create StudentCourse mapping table. This is many-to-many (M:M) relationship as course can have multiple students enrollment and vice versa.

CREATE TABLE [dbo].[StudentCourse](
       [StudentCourseId] [int] IDENTITY(1,1) NOT NULL,
       [CourseId] [int] NOT NULL,
       [StudentId] [int] NOT NULL,
CONSTRAINT [PK_StudentCourse] PRIMARY KEY CLUSTERED
(
       [StudentCourseId] ASC
)
) ON [PRIMARY]
 
GO

If either course or student is deleted then corresponding record will be deleted from StudentCourse mapping table. Hence we need to add foreign key and ON DELETE CASCADE relationship for both student and course tables. 

ALTER TABLE [dbo].[StudentCourse]  WITH CHECK ADD  CONSTRAINT [FK_StudentCourse_Course_CourseId] FOREIGN KEY([CourseId])
REFERENCES [dbo].[Course] ([CourseId])
ON DELETE CASCADE
GO
 
ALTER TABLE [dbo].[StudentCourse] CHECK CONSTRAINT [FK_StudentCourse_Course_CourseId]
GO
 
ALTER TABLE [dbo].[StudentCourse]  WITH CHECK ADD  CONSTRAINT [FK_StudentCourse_Student_StudentId] FOREIGN KEY([StudentId])
REFERENCES [dbo].[Student] ([StudentId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[StudentCourse] CHECK CONSTRAINT [FK_StudentCourse_Student_StudentId]
GO

Instructor can take only one course. We need separate table for Course and Instructor mapping. This is one-to-one (1:1) relationship between course and instructor.

CREATE TABLE [dbo].[CourseInstructor](
       [CourseId] [int] NOT NULL,
       [InstructorId] [int] NOT NULL,
CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED
(
       [CourseId] ASC,
       [InstructorId] ASC
)
) ON [PRIMARY]
 
GO

If either Course or Instructor is deleted then corresponding record will be deleted from CourseInstructor mapping table. Hence we need to add foreign key and ON DELETE CASCADE relationship for both Instructor and Course tables. 

ALTER TABLE [dbo].[CourseInstructor]  WITH CHECK ADD  CONSTRAINT [FK_CourseInstructor_Course_CourseId] FOREIGN KEY([CourseId])
REFERENCES [dbo].[Course] ([CourseId])
ON DELETE CASCADE
GO
 
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Course_CourseId]
GO
 
ALTER TABLE [dbo].[CourseInstructor]  WITH CHECK ADD  CONSTRAINT [FK_CourseInstructor_Instructor_InstructorId] FOREIGN KEY([InstructorId])
REFERENCES [dbo].[Instructor] ([InstructorId])
ON DELETE CASCADE
GO
 
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Instructor_InstructorId]
GO

Thats it !! Database table design is over. Let’s generate the database diagram to visualize how tables are related to each other.  

Instructor Course and Student Database

Instructor Course and Student Database

Not lets fill some sample data. Here we will start with Department, Course, Student and Instructor first then insert data into mappings table.

Create Master Data

SET IDENTITY_INSERT [dbo].[Instructor] ON 
GO

INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (1, N'Pat', N'Reynolds')
GO
INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (2, N'Mathew', N'Alexander')
GO
INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (3, N'Patty', N'Lambert')
GO
INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (4, N'Peter', N'Adams')
GO
INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (5, N'Wilfredo', N'Layne')
GO
INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (6, N'Carletta', N'Windham')
GO
INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (7, N'Christiane', N'Sears')
GO
INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (8, N'Loan', N'Canfield')
GO
INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (9, N'Dede', N'Bach')
GO
INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (10, N'Elinor', N'Lemons')
GO
INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (11, N'Wendolyn', N'Catalano')
GO
INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (12, N'Efren', N'Coughlin')
GO
INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (13, N'Victor', N'Kee')
GO
INSERT [dbo].[Instructor] ([InstructorId], [FirstName], [LastName]) 
VALUES (14, N'Frances', N'Crisp')
GO
SET IDENTITY_INSERT [dbo].[Instructor] OFF
GO


SET IDENTITY_INSERT [dbo].[Department] ON 
GO

INSERT [dbo].[Department] ([DepartmentId], [DepartmentName], [DepartmentLocation], [InstructorId]) 
VALUES (1, N'Engineering', N'Building-B1', 1)
GO
INSERT [dbo].[Department] ([DepartmentId], [DepartmentName], [DepartmentLocation], [InstructorId]) 
VALUES (2, N'English', N'Building-B2', 2)
GO
INSERT [dbo].[Department] ([DepartmentId], [DepartmentName], [DepartmentLocation], [InstructorId]) 
VALUES (3, N'Economics', N'Building-B3', 3)
GO
INSERT [dbo].[Department] ([DepartmentId], [DepartmentName], [DepartmentLocation], [InstructorId]) 
VALUES (4, N'Mathematics', N'Building-B4', 4)
GO
INSERT [dbo].[Department] ([DepartmentId], [DepartmentName], [DepartmentLocation], [InstructorId]) 
VALUES (5, N'Geography', N'Building-B5', NULL)
GO
SET IDENTITY_INSERT [dbo].[Department] OFF
GO


SET IDENTITY_INSERT [dbo].[Course] ON 

GO
INSERT [dbo].[Course] ([CourseId], [CourseName], [DepartmentId]) 
VALUES (1, N'Computer Architecture', 1)
GO
INSERT [dbo].[Course] ([CourseId], [CourseName], [DepartmentId]) 
VALUES (2, N'Chemistery', 1)
GO
INSERT [dbo].[Course] ([CourseId], [CourseName], [DepartmentId]) 
VALUES (3, N'Modern Physics', 1)
GO
INSERT [dbo].[Course] ([CourseId], [CourseName], [DepartmentId]) 
VALUES (4, N'Biology', 1)
GO
INSERT [dbo].[Course] ([CourseId], [CourseName], [DepartmentId]) 
VALUES (5, N'Eassy', 2)
GO
INSERT [dbo].[Course] ([CourseId], [CourseName], [DepartmentId]) 
VALUES (6, N'Composition', 2)
GO
INSERT [dbo].[Course] ([CourseId], [CourseName], [DepartmentId]) 
VALUES (7, N'Speaking', 2)
GO
INSERT [dbo].[Course] ([CourseId], [CourseName], [DepartmentId]) 
VALUES (8, N'Microeconomics', 3)
GO
INSERT [dbo].[Course] ([CourseId], [CourseName], [DepartmentId]) 
VALUES (9, N'Macroeconomics', 3)
GO
INSERT [dbo].[Course] ([CourseId], [CourseName], [DepartmentId]) 
VALUES (10, N'Trigonometry', 4)
GO
INSERT [dbo].[Course] ([CourseId], [CourseName], [DepartmentId]) 
VALUES (11, N'Statistics', 4)
GO
INSERT [dbo].[Course] ([CourseId], [CourseName], [DepartmentId]) 
VALUES (12, N'Geometry', 4)
GO
INSERT [dbo].[Course] ([CourseId], [CourseName], [DepartmentId]) 
VALUES (13, N'Algebra', 4)
GO
SET IDENTITY_INSERT [dbo].[Course] OFF
GO


SET IDENTITY_INSERT [dbo].[Student] ON 
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (1, N'Corey', N'Sanchez')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (2, N'Eugene', N'Mcdonald')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (3, N'Sheldon', N'Freeman')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (4, N'Melba', N'Bell')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (5, N'Tyrone', N'Chavez')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (6, N'Faith', N'Perkins')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (7, N'Javier', N'Bishop')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (8, N'Tracy', N'Erickson')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (9, N'Alice', N'Jordan')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (10, N'Janice', N'Mason')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (11, N'Evan', N'Mccoy')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (12, N'Wallace', N'Pena')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (13, N'Ian', N'Taylor')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (14, N'Jeffrey', N'Hanson')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName])
VALUES (15, N'Jo', N'Glover')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (16, N'Angelina', N'Wade')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (17, N'Franklin', N'Kelly')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (18, N'Jordan', N'Baldwin')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (19, N'Ken', N'French')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (20, N'Luther', N'Miller')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (21, N'Francis', N'Powers')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (22, N'Meredith', N'Hubbard')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (23, N'Judy', N'Cobb')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (24, N'Lee', N'Santiago')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (25, N'Nathan', N'Johnson')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName])
VALUES (26, N'Jeremy', N'Jimenez')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (27, N'Joey', N'Fernandez')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (28, N'Jessica', N'Hughes')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (29, N'Courtney', N'Nunez')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (30, N'Woodrow', N'Brady')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (31, N'Lowell', N'Abbott')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (32, N'Justin', N'Howell')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (33, N'Sarah', N'Cox')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (34, N'Noel', N'Watts')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (35, N'Grady', N'Farmer')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (36, N'Erica', N'Becker')
GO
INSERT [dbo].[Student] ([StudentId], [FirstName], [LastName]) 
VALUES (37, N'Martin', N'Martin')
GO
SET IDENTITY_INSERT [dbo].[Student] OFF
GO 

Create Transaction Data

INSERT [dbo].[CourseInstructor] ([CourseId], [InstructorId]) VALUES (1, 1)
GO
INSERT [dbo].[CourseInstructor] ([CourseId], [InstructorId]) VALUES (2, 2)
GO
INSERT [dbo].[CourseInstructor] ([CourseId], [InstructorId]) VALUES (3, 3)
GO
INSERT [dbo].[CourseInstructor] ([CourseId], [InstructorId]) VALUES (4, 4)
GO
INSERT [dbo].[CourseInstructor] ([CourseId], [InstructorId]) VALUES (5, 5)
GO
INSERT [dbo].[CourseInstructor] ([CourseId], [InstructorId]) VALUES (6, 6)
GO
INSERT [dbo].[CourseInstructor] ([CourseId], [InstructorId]) VALUES (7, 7)
GO
INSERT [dbo].[CourseInstructor] ([CourseId], [InstructorId]) VALUES (8, 8)
GO
INSERT [dbo].[CourseInstructor] ([CourseId], [InstructorId]) VALUES (9, 9)
GO
INSERT [dbo].[CourseInstructor] ([CourseId], [InstructorId]) VALUES (10, 10)
GO
INSERT [dbo].[CourseInstructor] ([CourseId], [InstructorId]) VALUES (11, 11)
GO
INSERT [dbo].[CourseInstructor] ([CourseId], [InstructorId]) VALUES (12, 12)
GO
INSERT [dbo].[CourseInstructor] ([CourseId], [InstructorId]) VALUES (13, 13)
GO

SET IDENTITY_INSERT [dbo].[StudentCourse] ON 

GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (1, 2, 2)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (2, 3, 2)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (3, 2, 3)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (4, 3, 3)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (5, 2, 6)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (6, 4, 6)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (7, 2, 7)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (8, 4, 7)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (9, 2, 8)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (10, 4, 8)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (11, 4, 9)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (12, 4, 10)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (13, 4, 11)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (14, 4, 12)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (15, 6, 12)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (16, 2, 14)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (17, 2, 13)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (18, 6, 13)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (19, 4, 14)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (20, 2, 15)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (21, 2, 16)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (22, 2, 17)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (23, 2, 19)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (24, 6, 20)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (25, 6, 21)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (26, 2, 22)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (27, 4, 22)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (28, 6, 22)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (29, 2, 23)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (30, 4, 23)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (31, 6, 24)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (32, 6, 25)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (33, 5, 26)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (34, 6, 26)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (36, 4, 28)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (37, 5, 28)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (38, 6, 29)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (39, 5, 30)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (40, 6, 30)
GO
INSERT [dbo].[StudentCourse] ([StudentCourseId], [CourseId], [StudentId]) VALUES (41, 6, 27)
GO
SET IDENTITY_INSERT [dbo].[StudentCourse] OFF
GO

In next post we will see the interview queries and their answers.

I hope you enjoyed this post. If you like this post don’t forget to like, share and comment if any.

Nov 21

Frequently asked SQL Queries in Interviews

Today we discuss about the most frequently asked SQL queries in the interviews.

In my previous article we discussed we created two tables i.e. Employee and Department with dummy data. I use same table and data from there except department.

CREATE TABLE [dbo].[Employee](
	[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeName] [varchar](50) NOT NULL,
        [Salary] [int] NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
	[EmployeeId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Insert employee data
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Rohit', 200000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Pranav', 100000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Akshay', 125000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Prakash', 50000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Anjali', 50000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Sudha', 25000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Dinesh', 20000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Meetanshu', 22000)
GO
INSERT INTO [Employee] ([EmployeeName], [Salary]) VALUES ('Robert', 150000)
GO

Now, execute SELECT query on newly created table and sort data by descending order it will show you  following result

Employee Id Employee Name Salary
1 Rohit 200000
9 Robert 150000
3 Akshay 125000
2 Pranav 100000
4 Prakash 50000
5 Anjali 50000
6 Sudha 25000
8 Meetanshu 22000
7 Dinesh 20000

Employee and Salary based Questions

Top 3 Employees with maximum salary

SELECT TOP 3 EmployeeId, EmployeeName, Salary 
FROM Employee 
ORDER BY Salary DESC

Top 3 Employees with minimum salary 

SELECT TOP 3 EmployeeId, EmployeeName, Salary 
FROM Employee 
ORDER BY Salary ASC

Third highest salary of employee

SELECT MIN(Salary)
FROM(
		SELECT DISTINCT TOP 3 Salary
		FROM Employee
		ORDER BY Salary DESC
) AS Result

Third lowest salary of employee (to make you confuse)

SELECT MAX(Salary)
FROM(
		SELECT DISTINCT TOP 3 Salary
		FROM Employee
		ORDER BY Salary ASC
) AS Result

Fifth highest salary of employee

SELECT MIN(Salary)
FROM(
		SELECT DISTINCT TOP 5 Salary
		FROM Employee
		ORDER BY Salary DESC
) AS Result

Fifth lowest salary of employee

SELECT MAX(Salary)
FROM(
		SELECT DISTINCT TOP 5 Salary
		FROM Employee
		ORDER BY Salary ASC
) AS Result

Nth highest salary of employee (Suppose 7th)

SELECT TOP 1 Salary
FROM (
      SELECT DISTINCT TOP 7 Salary
      FROM Employee
      ORDER BY Salary DESC
      ) AS Emp
ORDER BY Salary ASC

Nth lowest salary of employee (Suppose 7th)

SELECT TOP 1 Salary
FROM (
      SELECT DISTINCT TOP 7 Salary
      FROM Employee
      ORDER BY Salary ASC
      ) AS Emp
ORDER BY Salary DESC

Customer and Account based Questions

We already discussed these questions on my previous post hence posting only queries.

Customer having maximum number of accounts

SELECT c.CustomerName, COUNT(ca.AccountId) AS AccountCount
FROM @Customer c
INNER JOIN @CustomerAccount ca ON c.CustomerId = ca.CustomerId
INNER JOIN @Account a ON a.AccountId = ca.AccountId
GROUP BY ca.CustomerId, c.CustomerName
ORDER BY AccountCount DESC

Accounts having maximum number of customer

SELECT a.AccountName, COUNT(ca.CustomerId) AS CustomerCount
FROM @Customer c
INNER JOIN @CustomerAccount ca ON c.CustomerId = ca.CustomerId
INNER JOIN @Account a ON a.AccountId = ca.AccountId
GROUP BY ca.AccountId, a.AccountName
ORDER BY CustomerCount DESC

Nov 16

Sorting, Filtering, Searching and Pagination using SQL Stored Procedure

Application performance is one of the important aspect in any kind of software application. This non functional requirement (NFR)  developers forget while implementing (this in generally I observed while doing code reviews) table or grid design . Developers generally try to fetch all the records from database and displays using third party grids or open source grids.

This is the area where end users or clients complains about the application performance is not up to the mark, page is taking long time to load, grid not loading quickly etc. whenever the table contains large number of records let’s say more than half million ?? This problem increases if you  want to display the hierarchical data.

Is there any solution to fix this problem? if yes, then how ?

Yes, Definitely!!

Generally, in any table we perform Searching or Filtering, Pagination, Page Size, Sorting or Ordering operations means we are displaying fixed size of records on page let’s 10, 20 etc. but we are fetching all the unnecessary records from database. This increases query execution time and converting fetched records to class objects.

The solution to solve this problem, to write SQL query in such a way that it will do all the required operations  (search, pagination, sorting) to fetch data. In following sql stored procedure, I will pass search term, column to sort the record, sorting order (ascending or descending), page number clicked, and number of records parameters to fetch data.

I will use temporary table concept (common table expression) to fetch the records.

PROCEDURE [dbo].[usp_Employee_GetAll] 
	@SearchTerm VARCHAR(50),
	@SortColumn VARCHAR(50),
	@SortOrder VARCHAR(50),
	@PageNumber INT,
	@PageSize INT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @StartRow INT
	DECLARE @EndRow INT
	
	-- calculate the starting and ending of records
	SET @SortColumn = LOWER(ISNULL(@SortColumn, ''))
	SET @SortOrder = LOWER(ISNULL(@SortOrder, ''))
	SET @StartRow = (@PageNumber - 1) * @PageSize		 
    SET @EndRow = (@PageNumber * @PageSize)+1 			
	
	;WITH CTEResult AS (
		SELECT ROW_NUMBER() OVER (ORDER BY
			CASE WHEN (@SortColumn = 'firstname' AND @SortOrder='asc') THEN FirstName END ASC,
			CASE WHEN (@SortColumn = 'firstname' AND @SortOrder='desc') THEN FirstName END DESC,

			CASE WHEN (@SortColumn = 'lastname' AND @SortOrder='asc') THEN LastName END ASC,
			CASE WHEN (@SortColumn = 'lastname' AND @SortOrder='desc') THEN LastName END DESC,

			CASE WHEN (@SortColumn = 'title' AND @SortOrder='asc') THEN Title END ASC,
			CASE WHEN (@SortColumn = 'title' AND @SortOrder='desc') THEN Title END DESC,

			CASE WHEN (@SortColumn = 'department' AND @SortOrder='asc') THEN Department END ASC,
			CASE WHEN (@SortColumn = 'department' AND @SortOrder='desc') THEN Department END DESC,

			CASE WHEN (@SortColumn = 'employeeid' AND @SortOrder='asc') THEN Id END ASC,
			CASE WHEN (@SortColumn = 'employeeid' AND @SortOrder='desc') THEN Id END DESC
		 ) AS RowNumber
		, COUNT(*) OVER () AS TotalCount
		,[Id]
		,[FirstName]
		,[LastName]
		,[Gender]
		,[Dob]
		,[Title]
		,[Department]
		,[Salary]
		,[Description]
		,[IsActive]
		,[ModifiedDate]
		FROM [dbo].[Employee] 
		WHERE IsActive = 1
		 AND (
				 (ISNULL(@SearchTerm, '') = '' OR Title LIKE '%' + @SearchTerm + '%')
				OR (ISNULL(@SearchTerm, '') = '' OR FirstName LIKE '%' + @SearchTerm + '%')
				OR (ISNULL(@SearchTerm, '') = '' OR LastName LIKE '%' + @SearchTerm + '%')
				OR (ISNULL(@SearchTerm, '') = '' OR Department LIKE '%' + @SearchTerm + '%')
			)
	)

	SELECT RowNumber, TotalCount, [Id], [FirstName], [LastName], [Gender], [Dob], [Title], [Department], [Salary], [Description], [IsActive], [ModifiedDate]
	FROM CTEResult
	WHERE RowNumber > @StartRow AND RowNumber < @EndRow
	ORDER BY RowNumber

END

Now let's test it

CASE 1: Fetch All Records
This will fetches only 10 records and displays first page

Query

EXEC [usp_Employee_GetAll]  '','','',1,10

Output

Sorting pagination and ordering in sql

Sorting pagination and ordering in Sql Stored Procedure

CASE 2: Fetch Records for third page

Query

EXEC [usp_Employee_GetAll]  '','','',3,10

Output

Sorting pagination and ordering in sql

Sorting pagination and ordering in sql - third page

CASE 3: Ascending ordered First Name

Query

EXEC [usp_Employee_GetAll]  '','FirstName','asc',1,10

Output

Sorting pagination and ordering in sql - Ascending Order Firstname

Sorting pagination and ordering in sql - Ascending Order First Name

CASE 4: Ascending ordered First Name and Search term by department

Query

EXEC [usp_Employee_GetAll]  '','FirstName','asc',1,10

Output

Sorting pagination and ordering in sql - Filter term

Sorting pagination and ordering in sql - Filter term

Likewise you can play around this query.

I hope you enjoyed this post. Don't forget to like, subscribe, comment and share this article with your friends.