How To Identify The Duplicate Records Present In a Table In SQL Server

How To Split Delimited Data In SQL Server

Group by Having clause in sql

GROUP BY – HAVING:

GROUP BY will be used in SELECT statement to gather the data specified in one column and perform aggregations on one or more columns.

HAVING will be used in combination with GROUP BY to perform filtering on grouped data.

Without using GROUP BY we can't use HAVING.

Example1:

If you count to see the number of employees from India, then we can achieve it by using GROUP BY clause:

SELECT

       Country

       ,COUNT(*)

FROM

       dbo.Employee

WHERE Country = 'India'

GROUP BY Country

One observation we need to do over here is, we should only have those columns in SELECT list which we have mentioned in GROUP BY apart from aggregations.

If you observe the above query, we have mentioned only Country and Aggregate function in our select list as Country is available in GROUP BY clause. If you mention any other columns apart from Country below error will be displayed.

SELECT

       Country

       ,EmployeeID

       ,COUNT(*)

FROM

       dbo.Employee

WHERE Country = 'India'

GROUP BY Country

Error: Msg 8120, Level 16, State 1, Line 3

Column 'dbo.Employee.EmployeeID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

EmployeeID is not available in GROUP BY but we have mentioned that in SELECT clause that is the reason it gave error message.

Example2:

If you want to see the number of employees from different countries then,

SELECT

       Country

       ,COUNT(*) AS NoOfEmp

FROM

       dbo.Employee

GROUP BY Country

I have added salary column in Employee table and the data looks like below:

Now, perform different aggregations using GROUP BY on top of this table:

SELECT

       Country

       ,MAX(Salary) AS MaxSalary

       ,MIN(Salary) AS MinSalary

       ,AVG(Salary) AS AvgSalary

       ,SUM(Salary) AS SumSalary

       ,COUNT(*) AS NoOfEmp

FROM

       dbo.Employee

GROUP BY Country

We can add HAVING clause in case of filtering the aggregated data:

Example: In the above result, you want to see the details such as MAX, MIN, AVG etc.. salaries only if the number of employees in that country exceeds 2.

In this case the query is as follows:

SELECT

       Country

       ,MAX(Salary) AS MaxSalary

       ,MIN(Salary) AS MinSalary

       ,AVG(Salary) AS AvgSalary

       ,SUM(Salary) AS SumSalary

       ,COUNT(*) AS NoOfEmp

FROM

       dbo.Employee

GROUP BY Country

HAVING COUNT(*) > 2

If we see the result set, USA country details got removed because number of employees from that country are 2 and HAVING clause is filtering that data.

Case Statement in SQL Server

Usage of CASE – WHEN:

This is used to derive new set of values from the given input.

It can be used in various ways depending on the business usage.

You may compare it with IF THEN ELSE and Switch statement as it will provide the same functionality.

Syntax:

CASE

            WHEN <Condition> THEN <Result1>

            WHEN <Condition> THEN <Result2>

            WHEN <Condition> THEN <Result3>

            ELSE <Result4> --Optional

END

In above syntax ELSE is optional, if we don't specify this then it will populate the value as NULL in case nothing is matched with conditions specified in WHEN clause.

Example:

SELECT

       EmployeeID

       ,CASE

              WHEN EmployeeID = 1 THEN 'Anil'

              WHEN EmployeeID = 2 THEN 'Suresh'

              WHEN EmployeeID = 3 THEN 'Vijaya'

              WHEN EmployeeID = 4 THEN 'Koushik'

              ELSE 'NA'

       END AS EmpName

       ,DepartmentID

FROM

       dbo.Employee

If we see the result as per the case statement EmpName column is populated.

As EmployeeID = 5 is not satisfying any condition, name is populated as NA

Now, remove ELSE part and check what is the result.

SELECT

       EmployeeID

       ,CASE

              WHEN EmployeeID = 1 THEN 'Anil'

              WHEN EmployeeID = 2 THEN 'Suresh'

              WHEN EmployeeID = 3 THEN 'Vijaya'

              WHEN EmployeeID = 4 THEN 'Koushik'

       END AS EmpName

       ,DepartmentID

FROM

       dbo.Employee

As we discussed, it placed NULL for EmployeeID = 5 as it is not satisfying any condition specified in WHEN clause.

NOT operator in SQL Server

Introduction to NOT operator:

Mostly we will use this keyword with LIKE and IN operators which will revese the action does by them.

If you want to select the data whose EmployeeID is not in 1, 3, 5 then just add NOT before IN clause:

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeID NOT IN (1, 3, 5)

In the same way if you don't want to see the employees whose name is starting with A then add NOT before LIKE operator:

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeName NOT LIKE 'A%'

LIKE operator in SQL Server

Usage of LIKE in SQL Server:

If we want to get the data which is partially matching then we need to use LIKE operator.

Example:

Get the list of employees whose name is starting with A

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeName LIKE 'A%'

Get the list of employees whose name is ending with K

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeName LIKE '%K'

Between and IN Operators in SQL Server

BETWEEN and IN comparison:

BETWEEN will be used whenever we want to select the data based on a range.

IN will be used, if we want to select the data that falls under some random values.

Example:

Fetch the employees whose ID is from 1 to 3:

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeID BETWEEN 1 AND 3

Fetch the employees whose ID is either 1 OR 3 OR 5

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeID IN (1, 3, 5)

If we don't have IN, BETWEEN clause we need to write the same query by using OR keyword which will increase length of your code.

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeID = 1

       OR EmployeeID = 3

       OR EmployeeID = 5

Introduction to SELECT statement in SQL Server

Introduction to SELECT statement:

It is a command in DQL and is used to fetch the data from database.

Below are the most commonly used keywords that we use while fetching the data:

  SEELCT

  FROM

  WHERE

  ORDER BY

We will have mainly two parts in SELECT statement:

  List of columns

  List of tables from which you want to retrieve these columns

Employee table:

Attributes/ Columns

EmployeeID

EmployeeName

DepartmentID

City

Country

SELECT …....... FROM Clause:

If you want to select:

all the columns from a table, use * in SELECT statement

Example:

            SELECT * FROM dbo.Employee – Here Employee is the table name

few columns from a table:

            SELECT

                        EmployeeID

                        ,EmployeeName

            FROM

                        dbo.Employee

           

WHERE.....

This is used for filtering out the data from your table and is useful when you want only part of the data.

Example:

If we need only the employees who are staying in India then we can get by using the below query

SELECT

       *

FROM

       dbo.Employee

WHERE

       Country='India'

If we don't want to see any employees who are from India then we can write:

SELECT

       *

FROM

       dbo.Employee

WHERE

       Country<>'India'

Order By:

            Used for sorting the data in ascending or descending format.

SQL is providing ASC and DESC for this purpose. ASC is the default sorting means id we don't mention anything it will order the data in ascending order.

SELECT

       *

FROM

       dbo.Employee

ORDER BY EmployeeName

If we see the result, data is sorted based on EmployeeName in ascending order as we didn't mention any ordering in Order BY clause.

SELECT

       *

FROM

       dbo.Employee

ORDER BY EmployeeName DESC

In the above result set, data sorted by using employee name in descending order.

Relationships in database management system

Relationships in Database:

A relationship is a bond between the different objects in a database. It works by matching data in key columns usually columns with the same name in both tables.

In most cases, the relationship matches the primary key from one table, which is a unique identifier for each row, with an entry in the foreign key in the other table.

There are three types of relationships between tables:

•       One-to-One Relationship

•       One-to-Many Relationship

•       Many-to-Many Relationship

One-to-One Relationships:

In this type of relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

Example: You can consider Country and Capital City relationship as One-to-One because one country will be associated with only one Capital City and One Capital City will be associated with only one country.

One-to-Many Relationships

In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table It is the most common type of relationship.

Example: You can consider Country and City relationship as One-to-Many because one country will be associated with many cities where as one city will be associated with only one country

Many-to-Many Relationships

In this type of relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B.

Example: You can consider Country and River as Many-to-Many relationship because one country may have different rivers passing through it, in the same way a river may pass through many countries

File System VS Database

File System:

A file system is what your operating system in your computer has for storing and organizing the data in files and folders.

Database:

A database is simply an organized collection of related data, typically stored on disk, and accessible by possibly many concurrent users.

Co-relation between File System and Database:

A file system is a more unstructured data store for storing arbitrary, probably unrelated data.

The file system is more general, and databases are built on top of the general data storage services provided by file systems.

When can we prefer DB (We can't achieve below with File System):

  Data Normalization (Remove Redundancy) – A well designed database schema can reduce storage requirements on the target storage media by reducing duplicate data.

  Transaction Support – Atomic transactions guarantee complete failure or success of an operation. This includes automatic recovery of the database to a transaction consistent point in the event of an abnormal termination of the application (system crash, power loss, etc.).

  Concurrent Access – Many users can access data concurrently with out any issues.

  Flexibility, Expandability, Scalability – A database system can scale easily to larger data sets.

  Quick Access – Databases allow indexing based on any attribute (i.e. SQL columns). This helps fast retrieval of data, based on the indexed attribute. This is an important advantage, as the data increases it provides a more predictable query response time.

  Interoperability – We can connect through different third-party tools to access and analyze data.

  Relationship between Objects: We can easily create the relationships between the objects using which we will maintain the database consistency

When can we avoid DB:

  Database is small with a simple structure

  Applications are simple, special purpose and relatively static.

  Concurrent, multi user access to data is not required.

  Need a quick prototype to demonstrate feasibility

  Need an easy way to see the data without having to write a program

  Customers don't want to install a DBMS and want to get online quickly