SQL Tips And Tricks Examples

Example 1

Example 2

Example 3

Example 4

Example 5

Example 6

Example 7

Example 8

Example 9

Example 10

Example 11

Example 12

Example 13

Example 14

Example 15

Example 16

Example 17

Example 18

Example 19

Example 20

Example 21

Example 22

Example 23

Example 24

Example 25

Example 26

Example 27

Example 28

Example 29

Example 30

Example 31

Example 32

Example 33

Example 34

Example 35

Example 36

Example 37

Example 38

Example 39

Example 40

Example 41

Example 42

Example 43

Example 44

Example 45

Example 46

Example 47

Example 48

Example 49

Example 50


Example 51


Example 52


Example 53


Example 54


Example 55


Example 56


Example 57


Example 58


Example 59


Example 60


Example 61


Example 62


Example 63


Example 64


Example 65


Example 66


Example 67


Example 68


Example 69


Example 70


Example 71


Example 72


Example 73


Example 74


Example 75


Example 76


Example 77


Example 78


Example 79


Example 80


Example 81


Example 82







Solution By --- Deep Dey

Read more »

Youngest Employee - SQL TOP

********************Youngest & Oldest Employee********************

To get the youngest / olderst employee, two different methods are shown here.

1. Using Max and Min Functions
2. Using Top in Select Statement

'' Youngest Employee (Reference : AdventureWorks)

Select Min(BirthDate) from HumanResources.Employee
Select TOP 1 BirthDate from HumanResources.Employee Order By 1 Asc
Select TOP 1 * from HumanResources.Employee Order By BirthDate Asc

Select * from Person.Contact where ContactID = (Select TOP 1 EmployeeID from HumanResources.Employee Order By BirthDate Asc)

'' Oldest Employee (Reference : AdventureWorks)

Select Max(BirthDate) from HumanResources.Employee
Select TOP 1 BirthDate from HumanResources.Employee Order By 1 Desc

Read more »

Tie in Max or Min Function - WITH TIES

If you have two employees with the same birth date - the youngest/oldest employee has a tie. Then use of WITH TIES clause will solve it.

If WITH TIES is also specified, all rows that contain the last value returned by the ORDER BY clause are returned, even if doing this exceeds the number specified by expression.

Top 1 could return two or even more rows in case of a Tie

Select TOP 1 WITH TIES BirthDate from HumanResources.Employee Order By 1 Desc

WITH TIES can be used to get the matching records of the last/first even if it is more than the given number or percent

WITH TIES requires an ORDER BY clause.

Read more »

ANSI_NULLS - Equalling NULLs

********************Check For NULL********************

If you check for Null using <> or = operator, it might not return the results even if the table contains NULLs

Select * from Person.Address where AddressLine2 = Null

will not yield any results

on the otherhand it is a good practice to get the intended value using the following

Select * from Person.Address where AddressLine2 Is Null

If you still want to use = or <> with Nulls then the following will work

SET ANSI_NULLS OFF
Select * from Person.Address where AddressLine2 = Null

However, this feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Read more »

GetAge - Get Age Using SQL Query

How to calculate Age using SQL Query

DateDiff function can be used to get the Age from the birth date

Select BirthDate, DateDiff(year, BirthDate, getdate()) as Age from HumanResources.Employee

Read more »

SQL Adding or Subtracting Dates

********************Adding or Subtracting Dates********************

If the requirement is to calculate the age of a person six months from now, dateadd function can help

Select BirthDate, DateDiff(year, BirthDate, Dateadd(month, 6, getdate())) as BirthDateAfterSix from HumanResources.Employee Order By 1 Desc

subtracting dates is equally easy:

Select BirthDate, DateDiff(year, BirthDate, Dateadd(month, -6, getdate())) as BirthDateAfterSix from HumanResources.Employee Order By 1 Desc

Read more »

Universal Time or Greenwich Meridian Time (GMT)

Getting the Universal Time - GMT

Select GetDate() as LocalTime

Select GetUTCDate() as GMT_Time

GetUTCDate Returns the datetime value representing the current UTC time (Coordinated Universal Time or Greenwich Mean Time)

Select DateDiff(second,GetDate(),GetUTCDate() ) as DifferenceinSec
will give you the difference between GMT and your Local Time

Read more »