Concatenation in SQL

An operator in a string expression that concatenates two or more character or binary strings, columns, or a combination of strings and column names into one expression (a string operator).

It is a simple way to display different columns value in single one.

Note: ‘+’ sign is used for concatenation.

Supposew we have a table with name tbl_StudentDetails with following fields:

FirstName

MiddleName

LastName

DOB                 etc.

Now try for simple query for display data of tbl_studentDetails, we write something like this;

Select FirstName, , MiddleName, LastName, DOB from tbl_StudentDetails

The Result will look like this;

FirstName	MiddleName	LastName	DOB
Anil	Mohan	Sharma	2-2-1980
Manoj	Kumar	Singh	5-12-1983
Rahul	Tripathi		25-9-1990

Now I want to display whole result in two columns like StudentName and DOB. StudentName Column contains whole information ie, firstname, middleName, LastName.

We write this query in such manner;

Select (FirstName +’ ‘+ MiddleName+’ ‘+ LastName) As StudentName , DOB from tbl_StudentDetails

The Result will look like this;

StudentName	DOB
Anil Mohan Sharma	2-2-1980
Manoj Kumar Singh	5-12-1983
Rahul Tripathi	25-9-1990

IF Statement in SQL

Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL statement that follows an IF keyword and its condition is executed if the condition is satisfied: the Boolean expression returns TRUE. The optional ELSE keyword introduces another Transact-SQL statement that is executed when the IF condition is not satisfied: the Boolean expression returns FALSE.

Example: 

declare @TotalAgra int

declare @TotalShimla int

set @TotalAgra=(select COUNT(*) from tblStudentMaster where collegeCampusId =10 )

if @TotalAgra>1

begin

print 'Agra Total Students='+ convert(varchar(10),@TotalAgra)

end

else

set @TotalShimla=(select COUNT(*) from tblStudentMaster where collegeCampusId =11 )

begin

print 'Shimla Total Students='+ convert(varchar(100),@TotalShimla)

end

SQL Alias

If you have very long or complex tables name or columns name than it can be denoted with their alias.
There are two types of aliases that are used most frequently:

·         Column alias and

·         Table alias.

Syntax for Tables Alias:

SELECT column_name(s) FROM table_name AS alias_name

Example:

SELECT s.first_name,s.Last_Name FROM student_details s;

Note: In the above query, alias 's' is defined for the table student_details and the column first_name and Last_Name is selected from the table.

Syntax for Columns Alias:

SELECT column_name AS alias_name FROM table_name

Example:

SELECT first_name AS Name FROM student_details; or SELECT first_name Name FROM student_details;

Result will look like this:

Name

Priya Chandra

Anjali Bhagwat

Rahul Sharma

An Example of both Column alias and Table alias


Suppose we have a table called "tbl_Employee" and another table called "tbl_Department". We will give the table aliases of "e" and "d" respectively.
Now we want to list all the Employees with there Departments.


We use the following SELECT statement:

SELECT e.EmpID, e.EmpName, d.DeparmentName FROM tbl_Employee AS e, tbl_Department AS d WHERE e.EmpID=d.EmpID_fk

The same SELECT statement without aliases:

SELECT tbl_Employee.EmpID, tbl_Employee.EmpName, tbl_Department.DeptName FROM tbl_Employee, tbl_Department WHERE tbl_Employee.EmpID=tbl_Department.EmpID_fk

As you'll see from the two SELECT statements above; aliases can make queries easier to both write and to read.

Advantages of Alias:

1.     There are more than one tables involved in a query,

2.     Functions are used in the query,

3.     The column names are big or not readable,

4.     More than one columns are combined together.

Difference Between Select into and Create and insert Statement in SQL

SELECT INTO STATEMENT

ü  SELECT INTO is very fast because it is minimally logged(Full details given below).

ü  SELECT INTO is also a quick way of creating a table from SELECT queries. SELECT INTO is a powerful tool for a DBA or database developer.

ü  The SELECT INTO statement selects data from one table and inserts it into a different table.

ü  The SELECT INTO statement is most often used to create backup copies of tables.

ü  Another benefit of SELECT INTO is a simple single statement table create and populate.

ü  SELECT INTO is creating meta data when you don't know the output structure.

ü  SELECT INTO creates the table specified after INTO. If the table exists, you get an error. Obviously Insert Into requires that the table already exist.

CREATE TABLE AND INSERT INTO STATEMENT

ü  This is the step by step way to create a table and insert data into table.

ü  You have more control over the table's schema. Ie. you can create indexes and other keys on a table when you Create it, you can't do that with Select Into.

Note: Create Table, the locks are acquired and released immediately once the table is created. but, Select Into, the locks are acquired and held until the transaction completes. So something like this :

select  ( Huge amount of records ) into otherTable

This query will hold locks on system tables until all rows have been inserted. So it is very expensive.

SELECT...INTO creates a new table, while INSERT...SELECT requires an existing table. So there are security implications and possible performance issues to consider.

Also, with SELECT...INTO, the new table has the same columns but no keys or constraints, so it is best for simply creating a temp backup.

When you have a very long-running query in a temporary table using the SELECT INTO format, those same system table locks are held until the query completes and data loads into the temp table. You can avoid system table locking by manually creating the table with the CREATE TABLE command – before loading the data into the table.

For example, this code …

CREATE TABLE #TempTable
(spid int)

INSERT INTO #TempTable
SELECT spid
FROM sys.objects

… will require much less locking than this code:

SELECT spid
INTO #TempTable
FROM sys.objects

While the total number of locks taken is the same, the length of time the locks are held for the first query will be much shorter. This allows other processes to create temp tables.

Typically, when developing SQL code the development server has only a single user or few users. When working on SQL code, it's important to know when the code will impact sessions other than the current session. And unexpected interaction can cause major performance issues.

SQL Operators

An operator is a symbol specifying an action that is performed on one or more expressions. 

Types of Operators:

·         Arithmetic Operators

·         Logical Operators

·         Comparison Operators

 

Arithmetic Operators: Arithmetic operators perform mathematical operations on two expressions of one or more of the data types of the numeric data type category.

There are following operators;

Operator	Meaning
+ (ADD)	Addition ie, select (2+3) gives 5
- (Subtract)	Subtraction ie, select (5 - 3) gives 2
* (Multiply)	Multiplication ie, select (2*3) gives 6
/ (Divide)	Division ie, select (8/3) gives 2
% (Modulo)	Returns the integer remainder of a division. For example, 12 % 5 = 2 because the remainder of 12 divided by 5 is 2. select (9%3)gives 0

---

Logical Operators: Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.

There are following operators;

Operator	Meaning
ALL	TRUE if all of a set of comparisons are TRUE.
AND	TRUE if both Boolean expressions are TRUE.
ANY	TRUE if any one of a set of comparisons are TRUE.
BETWEEN	TRUE if the operand is within a range.
EXISTS	TRUE if a subquery contains any rows.
IN	TRUE if the operand is equal to one of a list of expressions.
LIKE	TRUE if the operand matches a pattern.
NOT	Reverses the value of any other Boolean operator.
OR	TRUE if either Boolean expression is TRUE.
SOME	TRUE if some of a set of comparisons are TRUE.

---

Comparison Operators:

Comparison operators are used to compare the column data with specific values in a condition.

Comparison Operators are also used along with the SELECT statement to filter data based on specific conditions.

The below table describes each comparison operator.

Operator	Meaning
= (Equals)	Equal to
> (Greater Than)	Greater than
< (Less Than)	Less than
>= (Greater Than or Equal To)	Greater than or equal to
<= (Less Than or Equal To)	Less than or equal to
<> (Not Equal To)	Not equal to
!= (Not Equal To)	Not equal to
!< (Not Less Than)	Not less than
!> (Not Greater Than)	Not greater than