Archive for April 2012

SELF JOIN in SQL SERVER

JOIN is the keyword or Command which is used to link 2 or more tables to retrieve data.

As the name Suggests SELF JOIN is joining a table with it self.

Often a table needs to be self joined to fetch the required results. In such cases SELF JOIN will be used.

SELF JOIN is not a keyword or Command, Its just the name of the JOIN.

There is no specific syntax for SELF JOIN. SELF JOIN can be done using either of the following joins:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN

Example:

Consider a table which stored Employee details of an organisation.

Data in the EMPLOYEE table is given below:




















Employee table contains Employee Id, Employee Name, Salary and Employee's Manager ID.

Manager is also an Employee. So, Manager details will also be stored in the same table.

In order to find out the Name of the manager for an EMPLOYEE ;   EMPLOYEE  table has to be joined with it self.

Query for fetching the Employee's Manager Name is:

SELECT A.EMP_ID, A.EMP_NAME,A.EMP_SALARY,B.EMP_NAME
FROM EMPLOYEE A
LEFT JOIN EMPLOYEE B
ON A.MANAGER_ID=B.EMP_ID



Result Set for the above Query is:













In the above Query Left side table and Right side table both are one and the same. That is here a table is self joined to the same table.


To know more about other types of JOINs in SQL SERVER refer the below link:

JOIN in SQL SERVER

Posted in , | Leave a comment

SQL Server Interview Questions: Part5

Posted in | Leave a comment

CROSS JOIN in SQL SERVER

JOIN is the keyword or Command which is used to link 2 or more tables to retrieve data.


CROSS JOIN or CARTESIAN PRODUCT is a less frequently used or rarely used JOIN in SQL Server.


CARTESIAN PRODUCT will match each record from left side table to each record is right side table . That is CROSS JOIN will be a many to many relationship.


 Syntax for CROSS JOIN is:

SELECT <A.Column Names >, <B.Column Names>
FROM <Left side Table Name> <A> , <Right Side Table> <B>



JOIN keyword need not be used in CROSS JOIN.
In the above syntax A indicates the alias name for left side table Name
B indicates the alias name for right side table Name.


Example:


Consider 2 tables STUDENT and LANGUAGES .

STUDENT table holds STUDENT details like student id,name, class and student second language. LANGUAGES  table holds all the languages that are offered in that school.

SECOND_LANG_ID in STUDENT will hold the LANG_ID value of LANGUAGES table.

Below screen shot represents the data in STUDENT and LANGUAGES table.















Query to get the CARTESIAN PRODUCT of STUDENT and LANGUAGES table is:

Query:

SELECT S.STUDENT_ID,S.NAME,S.CLASS,L.NAME
FROM STUDENT S   ,OUTER JOIN LANGUAGES L
Result Set:














CROSS JOIN or CARTESIAN PRODUCT can be queried using CROSS JOIN Keyword also. Here is the syntax for CARTESIAN PRODUCT using CROSS JOIN Keyword:
Syntax for CROSS JOIN is:

SELECT <A.Column Names >, <B.Column Names>
FROM <Left side Table Name> <A> 
CROSS JOIN  <Right Side Table> <B>

In the above syntax A indicates the alias name for left side table Name
B indicates the alias name for right side table Name

ON keyword will not be used in conjunction with CROSS JOIN.

Query to retrieve CARTESIAN PRODUCT using the above Syntax is


SELECT S.STUDENT_ID,S.NAME,S.CLASS,L.NAME
FROM STUDENT S
CROSS OUTER JOIN LANGUAGES L

Result Set:











Note: 

If Table A have 'x'rows and Table B have 'y' rows then , result set of CROSS JOIN of tables A and B will have X*Y rows.
In the above example STUDENT table have 10 rows and LANGUAGES table have 18 rows.
Result set of CROSS JOIN or CARTESIAN PRODUCT between these 2 tables have 10*8=180 Rows.

To know more about other types of JOINs in SQL SERVER refer the below link:

JOIN in SQL SERVER

Posted in , | Leave a comment

FULL OUTER JOIN in SQL SERVER

JOIN is the keyword or Command which is used to link 2 or more tables to retrieve data.

FULL OUTER JOIN or FULL JOIN is used to retrieve complete data from left side table as well as complete data from right side table.

That is whether matching record is found or not in the tables all the records from Left and Right side tables will be retrieved.

 Syntax for FULL OUTER JOIN is:

SELECT <A.Column Names >, <B.Column Names>
FROM <Left side Table Name> <A>
FULL OUTER JOIN  <Right side Table Name > <B>
ON <A.matching column Name> = <B.Matching Column Name>

In the above syntax A indicates the alias name for left side table Name
B indicates the alias name for right side table Name.

Example:

Consider 2 tables STUDENT and LANGUAGES .

STUDENT table holds STUDENT details like student id,name, class and student second language. LANGUAGES  table holds all the languages that are offered in that school.

SECOND_LANG_ID in STUDENT will hold the LANG_ID value of LANGUAGES table.

Below screen shot represents the data in STUDENT and LANGUAGES table.
















If all the information needs to be fetched from both LANGUAGES and STUDENT table, then following query can be used:
Query:
SELECT S.STUDENT_ID,S.NAME,S.CLASS,L.NAME
FROM STUDENT S
FULL OUTER JOIN LANGUAGES L
ON S.SECOND_LANG_ID =L.LANG_ID

Result Set:















If matching record exits in the tables then matching values will be returned else Nulls will be returned for the Non matching records.
FULL OUTER JOIN result set will contain complete data from both the tables which are listed in join clause.


To know more about other types of JOINs in SQL SERVER refer the below link:

JOIN in SQL SERVER

Posted in , | Leave a comment

RIGHT OUTER JOIN in SQL SERVER

JOIN is the keyword or Command which is used to link 2 or more tables to retrieve data.

RIGHT OUTER JOIN or RIGHT JOIN is used to retrieve matching data from left side table and complete data from right side table.

 
Syntax for RIGHT OUTER JOIN is:

SELECT <A.Column Names >, <B.Column Names>
FROM <Left side Table Name> <A>
RIGHT OUTER JOIN  <Right side Table Name > <B>
ON <A.matching column Name> = <B.Matching Column Name>

In the above syntax A indicates the alias name for left side table Name
B indicates the alias name for right side table Name.

Example:

Consider 2 tables STUDENT and LANGUAGES .

STUDENT table holds STUDENT details like student id,name, class and student second language. LANGUAGES  table holds all the languages that are offered in that school.

SECOND_LANG_ID in STUDENT will hold the LANG_ID value of LANGUAGES table.

Below screen shot represents the data in STUDENT and LANGUAGES table.

















If all the languages information needs to be retrieved along with the students information following query can be used.

Query:
SELECT S.STUDENT_ID,S.NAME,S.CLASS,L.NAME
FROM STUDENT S
RIGHT OUTER JOIN LANGUAGES L
ON S.SECOND_LANG_ID =L.LANG_ID

Result Set:

















Result Set contains complete information from LANGUAGES (right side table) table and matching information from STUDENT (Left side table) table .

Even though no matching record found for the languages like Tamil, Punjab,Telugu etc., still they are displayed because these records are part of Right Side table.


That is RIGHT JOIN will return all the records from the right side table irrespective of matching record in left side table.

If matching record exits in left side table, then left side matching values will be returned in the result set, else NULLs will be returned.



To know more about other types of JOINs in SQL SERVER refer the below link:

JOIN in SQL SERVER

Posted in , | Leave a comment

LEFT OUTER JOIN in SQL Server

JOIN is the keyword or Command which is used to link 2 or more tables to retrieve data.

LEFT OUTER JOIN or LEFT JOIN is used to retrieve complete data from left side table and matching data from right side table.


Syntax for LEFT OUTER JOIN is:

SELECT <A.Column Names >, <B.Column Names>
FROM <Left side Table Name> <A>
LEFT OUTER JOIN  <Right side Table Name > <B>
ON <A.matching column Name> = <B.Matching Column Name>

In the above syntax A indicates the alias name for left side table Name
B indicates the alias name for right side table Name.

Example:

Consider 2 tables STUDENT and LANGUAGES .

STUDENT table holds STUDENT details like student id,name, class and student second language. LANGUAGES  table holds all the languages that are offered in that school.

SECOND_LANG_ID in STUDENT will hold the LANG_ID value of LANGUAGES table.

Below screen shot represents the data in STUDENT and LANGUAGES table.

















Student information along with second language information can be fetched irrespective of whether valid language information exits or not.
This information can be fetched by left joining STUDENT table with LANGUAGES table:


Query:

SELECT S.STUDENT_ID,S.NAME,S.CLASS,L.NAME
FROM STUDENT S
LEFT OUTER JOIN LANGUAGES L
ON S.SECOND_LANG_ID =L.LANG_ID


Result Set:



Result Set contains the Student information and their second language also.

In the above result set STUDENT_ID=8 information is also fetched though this student Language Id is 20. Language ID =20 doesn't found in LANGUAGES table.
Even though matching record does not exist in STUDENT and LANGUAGES table for STUDENT_ID=8 it appears in the result set.

That is left join will return all the records from the left side table irrespective of matching record in right side table.

If matching record exits in right side table, then right side matching values will be returned else NULLs will be returned.






To know more about other types of JOINs in SQL SERVER refer the below link:
JOIN in SQL SERVER


Posted in , | Leave a comment

INNER JOIN in SQL Server

JOIN is the keyword or Command which is used to link 2 or more tables to retrieve data.


INNER JOIN is the the most commonly used JOIN in SQL SERVER. INNER JOIN will retrieve (fetch) the data only if the criteria is satisfied in both the tables.

Syntax for INNER JOIN is:

SELECT <A.Column Names >, <B.Columm Names>
FROM <Left side Table Name> <A>
INNER JOIN  <Right side Table Name > <B>
ON <A.matching column Name> = <B.Matching Column Name>

In the above syntax A indicates the alias name for left side table Name
B indicates the alias name for right side table Name.

Example:

Consider 2 tables STUDENT and LANGUAGES .

STUDENT table holds STUDENT details like student id,name, class and student second language. LANGUAGES  table holds all the languages that are offered in that school.

SECOND_LANG_ID in STUDENT will hold the LANG_ID value of LANGUAGES table.

Below screen shot represents the data in STUDENT and LANGUAGES table.


















Second language information of a student can be fetched by joining STUDENT table with LANGUAGES table.

To get the student details along with their language information following query can be used:


Query:

SELECT S.STUDENT_ID,S.NAME,S.CLASS,L.NAME
FROM STUDENT S
INNER JOIN LANGUAGES L
ON S.SECOND_LANG_ID =L.LANG_ID

Result Set:



























Result Set contains the Student information and their second language also.

In the above result set STUDENT_ID=8 information is not fetched since this student Language Id is 20. Language ID =20 is not found in LANGUAGES table.
Since NO matching record found for Student_id=8 in both the tables. , this record is not retrieved.

To know more about other types of JOINs in SQL SERVER refer the below link:

Posted in , | Leave a comment

Joins in SQL SERVER

Joins play a vital role in retrieving data from 2 or more tables in SQL SERVER.
JOIN is the keyword which is used to link 2 or more table and get the required data based on the matching criteria in the joined tables.

There are 6 types of joins in SQL Server:

  1. INNER JOIN
  2. LEFT OUTER JOIN  (or) LEFT JOIN
  3. RIGHT OUTER JOIN (or) RIGHT JOIN
  4. FULL OUTER JOIN  (or) FULL JOIN
  5. CARTESIAN PRODUCT  (or) CROSS JOIN
  6. SELF JOIN
Types of Joins in SQL Server will be discussed more in coming articles.

Posted in , | Leave a comment

SELECT ,DISTINCT, FROM, WHERE, GROUP BY,HAVING and ORDER BY Commands together in SQL SERVER

In the earlier articles we have seen examples for SELECT, DISTINCT FROM, WHERE, GROUP BY, HAVING and ORDER BY statements.


In this article we will discuss how all the above commands can be combined in a single query.


Syntax for the query containing SELECT, DISTINCT, FROM, WHERE, GROUP BY , HAVING and ORDER BY Commands is:


Syntax :


SELECT  DISTINCT <Column Names>
FROM <TABLE NAME>
WHERE <Condition>
GROUP BY <Column Names>
HAVING <Condition>
ORDER BY <Column Names>


Example:




We have a table with the following data in the table STUDENTS.


NAME SUBJECTMARKSSTUDENT_ID
SAMMATHS401
JOSEPHECONOMICS622
DARRELLARTS783
CHRISTOPHERMATHS784
WALTERARTS595




In order find out the average marks ( avg marks less than 75) among the students who are having marks more than 60 the following query can be using.


SELECT  DISTINCT SUBJECT,AVG(MARKS)
FROM  STUDENTS   
WHERE MARKS > 60
GROUP BY SUBJECT
HAVING AVG(MARKS) <75
ORDER BY SUBJECT

Result Set:
























Result indicates the subjects in which students have to concentrate more

Posted in | Leave a comment

What is the difference between HAVING and WHERE in SQL SERVER?

HAVING and WHERE  are the clauses which are used to filter the data in a select quey.

Some differences between HAVING and WHERE clauses are:

  • HAVING is generally used along with the group by clause. WHERE clause is used independently.
  • Purpose of the HAVING clause is to work on the aggregate functions. WHERE clause can be used without aggregate functions also.
  • HAVING behaves similar to WHERE clause if there is no GROUP BY in the Query.

Posted in | Leave a comment

AVG in SQL SERVER

AVG is an aggregate function which is used to obtain the average of the values in a particular column.

Syntax:

SELECT AVG(<Column Name>) from <Table Name>

Example:

We have a table with the following data in the table SALES.

ITEMPRICE
ABC99.05
ABC1110.99
ABC270.89
ABC3100.99
ABC455.89


In order find out the average price of items in the sales we should use AVG function.

QUERY:

SELECT AVG(PRICE) FROM SALES

Result Set:
















Result set contains average of all the values in PRICE column.

Posted in , | Leave a comment

MIN in SQL SERVER

MIN is an aggregate function which is used to obtain the minimum value out of all the values in a particular column.
Syntax:

SELECT MIN (<Column Name>) from <Table Name>

Example:

We have a table with the following data in the table SALES.

ITEMPRICE
ABC99.05
ABC1110.99
ABC270.89
ABC3100.99
ABC455.89

In order find out the minimum price out of all the sales we should use MIN function.

QUERY:

SELECT MIN(PRICE) FROM SALES

Result Set:














Result set contains minimum value of all the values in PRICE column.

Posted in , | Leave a comment

MAX Function in SQL Server

MAX is an aggregate function which is used to obtain the maximum value out of all the values in a particular column.

Syntax:

SELECT MAX (<Column Name>) from <Table Name>

Example:

We have a table with the following data in the table SALES.

ITEMPRICE
ABC99.05
ABC1110.99
ABC270.89
ABC3100.99
ABC455.89

In order find out the maximum price out of all the sales we should use MAX function.

QUERY:

SELECT MAX(PRICE) FROM SALES

Result Set:













Result set contains maximum value of all the values in PRICE column.

Posted in , | Leave a comment

SUM in SQL Server

SUM is an aggregate function which is used to calculate the sum or total of values in a particular column.

Syntax:

SELECT SUM (<Column Name>) from <Table Name>

Example:

We have a table with the following data in the table SALES.

ITEMPRICE
ABC99.05
ABC1110.99
ABC270.89
ABC3100.99
ABC455.89


In order to count the total amount ( sum of the total prices) can be obtained by using SUM function.

QUERY:

SELECT SUM(PRICE) FROM SALES

Result Set:

Result set contains sum (total amount) of all the values in column PRICE.














Note:
  1. Sum function can be applied only on int , float , decimal , money data types.
  2. SUM Function ignores the NULL values in the columns.

Posted in , | Leave a comment

COUNT in SQL SERVER

COUNT is one of the most frequently used aggregate function in SQL SERVER.
As the indicated it counts the number of rows or records in the table.

Generally COUNT function is used to find out the number of rows in a table.
Syntax for the COUNT functions is:

SELECT COUNT(*) FROM <Table Name>
SELECT COUNT(1) FROM <Table Name>

Example:

Lets find out the number of rows in STUDENTS table using COUNT.

Query:

SELECT COUNT(*) FROM STUDENTS
SELECT COUNT(1) FROM STUDENTS 

Both of the above queries will return the same result.

Result Set:


Posted in , | Leave a comment

Aggregate Functions in SQL Server

Aggregate functions perform the calculation on the data and will return a single value as the out put.

In real time aggregate functions are used to calculate the minimum, maxmimum and average statistics.

HAVING Clause is mostly used to operate along with the aggregate functions.

Most frequently used Aggregate functions are:
Less frequently used aggregate functions are:
  • CHECKSUM
  • COUNT_BIG
  • CHECKSUM_AGG
  • BINARY_CHECKSUM
  • GROUPING
  • VAR
  • VARP
  • STDEV
  • STDEVP

Posted in , | Leave a comment

HAVING Clause in SQL Server

HAVING Clause is used to restrict or limit the number of rows returned by a query.

HAVING Clause is used only with the SELECT Command. It is generally used along with the GROUP BY Clause.

Having Clause filter the resultt set that is obtained by GROUP BY Command.

It generally works on aggregate function. One important point to be noted about HAVING Clause is it will not work with text data type. HAVING Clause is mostly used to operate on int,float,money and similar data types.

Syntax for the HAVING clause is:

HAVING < search criteria>

SELECT <Column list> FROM <Table Name> GROUP BY < Column List> HAVING <search condition>

Example: Lets assume that we have a table with student marks details along with the subjects they have chosen.

we can use HAVING clause to find out the average aggregate marks in a particular subject and the subject which is having average aggregate marks less than 60( any number).

Data in the STUDENTS table is:





















Query:
  • SELECT SUBJECT,AVG(MARKS) FROM STUDENTS GROUP BY SUBJECT
Above query will find out the average marks in each subject.

Result set for the above query is:












It contains all the subjects and corresponding average aggregate marks.

To find out the subjects which are having average aggregate marks less than 60 can be find out by using the below query:

Query:

SELECT SUBJECT,AVG(MARKS) FROM STUDENTS GROUP BY SUBJECT HAVING AVG(MARKS)  <60

Result Set of the above query is:
This indicates the list of subjects which are having aggregate less than 60.


Note: HAVING clause without GROUP BY clause acts as WHERE clause.




Posted in | Leave a comment

Group By Errors in SQL Server

Did you ever face the following error while running a query which is containing GROUP BY Clause in the query :

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

I got the above error while I was trying to run the following query  in SQL Server.

SELECT  SUBJECT, ID,NAME FROM STUDENTS GROUP BY SUBJECT

GROUP BY statement in SQL Server comes with set of predefined rules. Above statement is violating GROUP BY clause rules.


That is all the columns which are present in SELECT list should present in GROUP BY List of the query.

In the above query SELECT list contains SUBJECT,ID,NAME columns. Where as GROUP BY list contains only SUBJECT. In GROUP BY Clause ID, NAME are missing.

So, to correct this error we should include all the columns which should maintain the same column list in SELECT and GROUP BY.

The correct query for the above scenario would be:

SELECT  SUBJECT, ID,NAME FROM STUDENTS GROUP BY SUBJECT, ID,NAME

Posted in | Leave a comment

GROUP BY in SQL Server


As the name suggests, GROUP BY command is used to GROUP the data based on certain criteria or based on the required columns.


GROUP BY is considered as one of the complex commands in SQL Server because of its syntax.

A general GROUP BY clause includes GROUPING SETS, CUBE, ROLLUP, WITH CUBE, or WITH ROLLUP. All these complex clauses which are used along with GROUP BY will be discussed later.

As of now, we will concentrate more on only GROUP BY clause and will make the things simple.

As mentioned earlier GROUP BY is the clause used to group data based on the given criteria.

Syntax for the simple GROUP BY is:

SELECT <Column Names> FROM <table Name> GROUP BY <Column Names>

Note: All the columns which needs to be retrieved in the select command , they should present in the GROUP BY clause.

Example:

Let consider the scenario in which a table holds students names and there subject details.

To group the students based on the subjects that they have selected , we can use the below query:

Query:

SELECT ID,NAME, SUBJECT FROM STUDENTS GROUP BY SUBJECT, ID,NAME

Below picture shows the difference between normal SELECT query and the SELECT query along with GROUP BY Command.

Result Set:






Posted in | Leave a comment

ORDER BY in SQL Server

In the earlier articles we have seen how to use SELECT, WHERE, IN, DISTINCT etc commands.  These commands are used to retrieve the data from table(s).

By default when we run the select query , data will be retrieved in the same order in which it is inserted into table.

Example:

Here I have a students table with 5 records.

Below picture contains the result set when SELECT query is executed:





















In the above pictures Student Names are not in alphabetical order.

In order to fetch the data in alphabetical or sequential order we should be using ORDER BY clause in SQL Server.

ORDER BY clause in SQL Server is used to rearrange the data in the required order.

Syntax for the ORDER BY Statement is:

ORDER BY <Name of the columns> {ASC/DESC}

By default in ORDER BY clause data will be ordered or sorted by the the ascending order of the data.

That is explicitly ASC command will be considered in ORDER BY Clause if sort type is not mentioned.

Example: Here is the data from STUDENTS table sorted(ordered) based on their names.

Query: SELECT * FROM STUDENTS ORDER BY NAME

Result Set:

















in the above example student name are arranged alphabetically.

To arrange the data in the descending order of their names , use the following query:

Query: SELECT * FROM STUDENTS ORDER BY NAME DESC

Result Set:


















Using DESC keyword along with ORDER BY Clause data can be sorted in the descending order.

Posted in | Leave a comment