What is SQL Injection?

SQL Injection
SQL Injection

SQL Injection is a type of injection attack. An attacker can use it to make a web application process and execute injected SQL statements as part of an existing SQL query.

The user will use sql queries in the input area to hack the website.
Consider a website have login page with input user name and password. In the backend code we have created the query dynamically combining the inputs. In this situation, If user try to give sql queries in the input box, there is a chance the query will be executed. If it happened, then user have the control to manage our database.

SQL Constraints

SQL Constraints are set of rules applied to a SQL tables to validate data. The purpose of SQL Constraints are to ensure the accuracy and reliability of the data inserted/updated in table. If any Constraints is violated, then the operation will be aborted and exception will be thrown.

How to create a SQL constraint?

A SQL constraint can be created in a table in two way

  • While creating a table – with CREATE TABLE statement
  • after a table is created – with ALTER TABLE statement

Commonly used SQL Constraints are described below

NOT NULL Constraint

By default SQL columns accept null values. When we wants to restrict null values from a column then we can apply NOT NULL Constraint. So that it will block null value to that column. in this case an INSERT statement or UPDATE statement cannot be performed without giving a value to particular column.

Example
In the below examples it ensures that Id and Name must not be NULL.
NOT NULL on CREATE table statement

Create table Employee
(
   Id bigint NOT NULL,
   Name varchar(50) NOT NULL,
   Age int
)

NOT NULL on ALTER table statement

alter table Employee alter column Id bigint NOT NULL
alter table Employee alter column Name varchar(50) NOT NULL

UNIQUE Constraint

A UNIQUE Constraint means it ensures all the values in a column is different. Any value that matches the column data type including NULL can be inserted only once in this column. A table can have more than one UNIQUE Constraints.
Example
In the below examples it ensures that Id will not be repeated.
UNIQUE on CREATE table statement

Create table Employee
(
   Id bigint NOT NULL UNIQUE,
   Name varchar(50) NOT NULL,
   Age int
)

UNIQUE on ALTER table statement

alter table users add UNIQUE(Id)

PRIMARY KEY Constraint

PRIMARY KEY contains rules from both NOT NULL and UNIQUE. Which means a PRIMARY KEY column cannot contain NULL value and all values must be different. PRIMARY KEY is used to identify each record with a unique value. A table can have only one PRIMARY KEY. PRIMARY KEY can be applied to a single column or multiple columns together. PRIMARY KEY applied to multiple columns together is called COMPOSITE PRIMARY KEY
Example
Primary key on CREATE TABLE statement

CREATE TABLE users
(
	user_id int PRIMARY KEY,
	username varchar(45) NOT NULL,
	[password] varchar(45) NOT NULL,
	email varchar(45) NOT NULL
)

Primary key on ALTER TABLE statement

ALTER TABLE users ADD PRIMARY KEY(USER_ID)

COMPOSITE Primary key on CREATE TABLE statement

CREATE TABLE users
(
	user_id int NOT NULL,
	username varchar(45) NOT NULL,
	[password] varchar(45) NOT NULL,
	email varchar(45) NOT NULL,
        PRIMARY KEY (user_id ASC, email)
)

COMPOSITE Primary key on ALTER TABLE statement

ALTER TABLE users ADD CONSTRAINT PK_users PRIMARY KEY (user_id ASC, email)

FOREIGN KEY Constraint

FOREIGN KEY Constraint is used to link two tables. A FOREIGN KEY can be only created with the reference of PRIMARY KEY from another table. FOREIGN KEY helps to execute JOIN queries much faster. The FOREIGN KEY containing table is called as Child table and the referenced PRIMARY KEY containing table is called as Parent/referenced table.
Example
Foreign key on CREATE TABLE statement

CREATE TABLE USERADDRESS
(
	Id bigint not null PRIMARY KEY,
	UserId bigint NOT NULL,
	AddresLine1 varchar(200) NOT NULL,
	AddresLine2 varchar(200),
	PIN varchar(12) NOT NULL,
	FOREIGN KEY(UserId) references users(user_id)
)

Foreign key on ALTER TABLE statement

ALTER TABLE USERADDRESS ADD CONSTRAINT FK_USERID_USERADDRESS FOREIGN KEY (UserId) REFERENCES users(user_id)

Drop a Foreign key

ALTER TABLE USERADDRESS DROP FK_USERID_USERADDRESS

CHECK Constraint

CHECK Constraint is used to check the inserted or updated value to the column satisfy any specific condition. It will help to increase the data accuracy.
Example
CHECK on CREATE TABLE Statement

CREATE TABLE users
(
	user_id bigint IDENTITY(1,1) NOT NULL,
	username [varchar](45) NOT NULL,
	password [varchar](45) NOT NULL,
	email [varchar](45) NOT NULL,
	Age int check (Age >= 18),
	PRIMARY KEY (user_id ASC)
)

CHECK on ALTER TABLE statement

ALTER TABLE users ADD CONSTRAINT CK_USERAGE CHECK (Age >= 18)

Drop a CHECK Constraint

ALTER TABLE users DROP CK_USERAGE

DEFAULT Constraint

DEFAULT Constraint is used to set a default value to a column in a table. When an INSERT statement is not inserting value to DEFAULT Constraint column, then the default value will be inserted into that column.
Example
DEFAULT on CREATE TABLE statement

CREATE TABLE [dbo].[users]
(
	user_id bigint IDENTITY(1,1) NOT NULL,
	username [varchar](45) NOT NULL,
	password [varchar](45) NOT NULL DEFAULT 'Admin',
	email [varchar](45) NOT NULL,
	Age int,
	PRIMARY KEY (user_id ASC)
)

DEFAULT on ALTER TABLE statement

ALTER TABLE users ADD CONSTRAINT PWD_DEF DEFAULT 'Admin' for [password]

Drop a DEFAULT Constraint

ALTER TABLE users DROP PWD_DEF

What is a View in sql server and its advantages ?

 

A view is nothing more than a saved query.  A view can also be considered as a virtual table.

DeptId DeptName
1 IT
2 Payroll
3 HR
4 Admin

 

Id Name Salary Gender DepartmentId
1 Jhon 5000 Male 3
2 Mike 3400 Male 2
3 Pam 6000 Female 1
4 Todd 4800 Male 4
5 Sara 3200 Female 1
6 Ben 4800 Male 3

Select Query

Select Id, Name, Salary, Gender, DeptName

From tblEmployee

Join tblDepartment

On tblEmployee.DepartmentId=tblDepartment.DeptID

Result

Id Name Salary Gender DeptName
1 Jhon 5000 Male HR
2 Mike 3400 Male Payroll
3 Pam 6000 Female IT
4 Todd 4800 Male Admin
5 Sara 3200 Female IT
6 Ben 4800 Male HR

Creating View using select Query

Create View vWEmployeesByDepartment

As

Select Id, Name, Salary, Gender, DeptName

From tblEmployee

Join tblDepartment

On tblEmployee.DepartmentId=tblDepartment.DeptID

Select * from  vWEmployeesByDepartment

Result

Id Name Salary Gender DeptName
1 Jhon 5000 Male HR
2 Mike 3400 Male Payroll
3 Pam 6000 Female IT
4 Todd 4800 Male Admin
5 Sara 3200 Female IT
6 Ben 4800 Male HR

Advantages of views

  • Views can be used to reduce the complexity of the database schema
  • Views can be used as a mechanism to implement row and column level security

Example if IT department employees only to show

Create View vWITEmployeesByDepartment

As

Select Id, Name, Salary, Gender, DeptName

From tblEmployee

Join tblDepartment

On tblEmployee.DepartmentId=tblDepartment.DeptID

Where tblDepartment.DeptName=’IT’

Select * from  vWITEmployeesByDepartment

Result

Id Name Salary Gender DeptName
3 Pam 6000 Female IT
5 Sara 3200 Female IT

Example if salary to hide

Create View vWNonconfedentialdata

As

Select Id, Name,  Gender, DeptName

From tblEmployee

Join tblDepartment

On tblEmployee.DepartmentId=tblDepartment.DeptID

Select * from vWNonconfedentialdata

Result

Id Name Gender DeptName
1 Jhon Male HR
2 Mike Male Payroll
3 Pam Female IT
4 Todd Male Admin
5 Sara Female IT
6 Ben Male HR
  • Views can be used to present aggregated data and hide detailed data.

Example

Create view VwsummarizedData

As

select DeptName, Count(ID) as Total

From tblEmployee

Join tblDepartment

On tblEmployee.DepartmentId=tblDepartment.DeptID

Group by DeptName

 

Select * from VwsummarizedData

Result

DeptName Total
Admin 1
HR 2
IT 2
Payroll 1

 

To modify view – ALTER VIEW statement

To droop a view – DROP VIEW viewname

How to find Nth highest salary in Sql Server ?

How to find Nth highest salary in Sql Server ?

Finding the first or second or Nth highest salary of the person is a most important thing for the developers, as a developer we should know this. Mostly we hear this question in the interview.

Consider the below table with table name Employee

SELECT * FROM [dbo].[Employee]

How to find highest salary in Sql Server?

There are so many ways to find highest salary, you can see below some of the examples

Method 1

select max(salary) as highest_salary from [dbo].[Employee]

Method 2

select top 1 salary as highest_salary from [dbo].[Employee] order by salary desc

Method 3

;WITH CTE AS
(
SELECT salary ,ROW_NUMBER() OVER (ORDER BY Salary Desc) AS [Rank]
FROM [dbo].[Employee] group by salary
)
SELECT  salary as highest_salary
FROM CTE
WHERE [Rank]=1;

How to 2nd highest salary in Sql Server?

We are seen some of the methods to find highest salary above, we cannot use method 1 and method 2 to find the 2nd highest salary, but we can use the method 1 by little change in the code.

There are so many ways to 2nd highest salary, you can see below some of the example

Method 1

SELECT MAX(salary) FROM Employee WHERE Salary NOT IN ( SELECT Max(Salary) FROM Employee);

Method 2

SELECT TOP 1 Salary FROM ( SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) AS MyTable ORDER BY Salary ASC;

Method 3

;WITH CTE AS
(
SELECT salary ,ROW_NUMBER() OVER (ORDER BY Salary Desc) AS [Rank]
FROM [dbo].[Employee] group by salary
)
SELECT  salary as highest_salary FROM CTE WHERE [Rank]=2;

How to Nth highest salary in Sql Server?

Previously we have seen highest salary and 2nd highest salary solutions, also we know the query using CTE is used again used in 2nd highest salary with a little modification, the modification is we are changing the rank

If we want 1st highest salary we have to give rank as 1

If we want 2nd highest salary we have to give rank as 2

So we can use the same query to find the nth salary like below

;WITH CTE AS
(
SELECT salary ,ROW_NUMBER() OVER (ORDER BY Salary Desc) AS [Rank]
FROM [dbo].[Employee] group by salary
)

SELECT  salary as highest_salary FROM CTE WHERE [Rank]=”Nth”;

Example

;WITH CTE AS
(
SELECT salary ,ROW_NUMBER() OVER (ORDER BY Salary Desc) AS [Rank] 
FROM [dbo].[Employee] group by salary
)
SELECT  salary as highest_salary FROM CTE WHERE [Rank]=”Nth”;

Finding 5th highest salary in sql server

;WITH CTE AS
(
SELECT salary ,ROW_NUMBER() OVER (ORDER BY Salary Desc) AS [Rank]
FROM [dbo].[Employee] group by salary
)
SELECT  salary as highest_salary
FROM CTE WHERE [Rank]=5;

What are the main differences between DML vs DDL?

The main difference between DML vs DDL is – one for managing data in database and other for define database. DML stands for Data Manipulation Language and DDL stands for Data Definition Language.

    1. DML statements are used to manage the data in a database just like insert, update, delete etc.
      insert into table(col1, col2, col3) value(val1, val2, val3)
    2. DDL statements are used to create and define database structure just like create, alter, drop etc.
      create table tablename (col1 datatype, col2 datatype, col3 datatype)
    3. DML statements work with rows of data in table. For example, insert will create one or more rows in a table, where DDL works on database objects like table or views etc. For example, create will create a new object of table or view or procedure etc.
    4. Most of the DML support where clause and having clause for filtering data and order by and group by functions. But DDL does not support these functions.
    5. Transactions can be applied to DML statements. Which means, COMMIT and ROLLBACK are supported in DML statements. But DDL does not support Transactions. Which means once a table is created it cannot be roll backed. It need another DDL statement of DROP to remove the table from database.
    6. DDL statements cannot fire any triggers. But DML can fire triggers.

Joins in SQL

SQL JOINS

Joins in SQL are used to get related data from different tables. SQL joins are very useful feature in relational database while writing logical queries. There are different types of joins in SQL. They are

  • Inner Join (Join)
  • Outer Join
  • Cross Join
  • Self Join

Inner Join (Join)

Inner Join is used to retrieve only the matching records from both tables. The un-matching records will be omitted from result. The Join in SQL without any keywords like Inner, Outer, Left, Right or Cross is considered as Inner Join.

Outer Join

It retrieves either all rows from one table and only matching rows from other table or all rows from all tables. Doesn’t bother about matching. There are three kinds of outer join. They are.

Left Outer Join or Left Join

When we want all the rows from left table and only matching rows from right table, then we can use Left Outer or Left Join. If there is no matching records, it will return null values.

Right Outer Join or Right Join

When we need to fetch all the records from right table and only matching rows from left table, then we have to use Right outer or Right Join. If there is no matching rows in left table, then it will return null values.

Full Outer Join or Full Join

This combines both Left Outer join and Right Outer joins together. It returns records from both table when condition matches. If no match, then will show null. Concluding Outer Join, it can be said as it will return complete records from any of Left table or Right table and only matching from other table or from both(FULL) tables as matching together and not matching as null.

SQL JOINS
Types of JOINS in SQL

Cross Join

It is the Cartesian product of tables involved in join. Which means, each records from both tables will be combined to return result. This It is one of the least used join.

Self Join

Joining a table with itself is called Self Join It is mainly used with a table having hierarchy structure data in it like Employee Table with Manager_Id. Here Manager is also an employee and other employee will have a higher employee as manager. So in that situation, to get a manager of an employee, we need to join the same table with itself to get employee and manager. first table will act as employee table and second joining will act as Manager table.

Joins in SQL Based on Operators

Based on the operator used for joining tables, it can be classified into two types.

Equi Join

If we use only the = in join clause, then it is called Equi Join

Theta Join

When we use operators like >, <, >=, <= is known as Theta Join

what is order by vs group by in sql server ?

  • Both order by and group by are used to organize data according to user needs.
  • ORDER BY is used  to sort the query result by specific columns.
  • GROUP BY is used  to group unique combinations of columns values.

ORDER BY

Table name: tbl_name

ID Name
1 c
2 b
3 a
4 c

select * from  tbl_name order by Name

Result

ID Name
3 a
2 b
1 c
4 c

GROUP BY

SELECT COUNT(ID) as Count,Name

FROM tbl_name

GROUP BY Name

Result

Count Name
1 a
1 b
2 c

 

 

LOCAL AND GLOBAL TEMP TABLE

WHAT IS DIFFERENCE BETWEEN LOCAL AND GLOBAL TEMP TABLE?

LOCAL TEMP TABLE

Local temp table visible if there is a connection , and deleted when connection is closed.

 CREATE TABLE #<tablename>

GLOBAL TEMP TABLE

Global temp table is visible to all user,and are deleted when the connection that created it is closed.

 CREATE TABLE ##<tablename>