The Substring function in SQL is used to grab a portion of the stored data. The general syntax is: SUBSTRING(String, Position,Length)

The most frequent uses are as follows:

SUBSTR(String,Position,Length): Starting with the th character in string and select the next characters.

Assume we have the following table:

Store Regions

Example 1:

SELECT SUBSTRING(store_name,2,4)
FROM Geography
WHERE storename = ‘San Diego’;
Result:
‘an D’

The Length function in SQL is used to get the length of a string. The syntax is:

LEN(string)

Let’s take a look at some examples. Assume we have the following table:

Store Regions

Example 1:
SELECT Length(storename)
FROM Geography
WHERE storename = ‘Los Angeles’;
Result:
11

Example 2:
SELECT region_name, Length(region)
FROM Geography;
Result:
region
Length(region)
East
4
East
4
West
4
West
4

The SUM function is used to calculate the total for a column. The syntax is:

SELECT SUM(“column_name”)
FROM
“table_name”

For example, if we want to get the sum of all sales from the following table,

Store Sales Information

we would type in
SELECT SUM(Sales) FROM Store_Information

Result:
SUM(Sales)
$2750

$2750 represents the sum of all Sales entries: $1500 + $250 + $300 + $700.

SQL uses the MIN function to find the maximum value in a column.

The syntax for using the MIN function is:

SELECT MIN(“column_name”)
FROM
“table_name”

For example, if we want to get the lowest sales from the following table

Store Sales Information

type in

SELECT MIN(Sales) FROM Store_Information

Result:
MIN(Sales)
$250

$250 represents the minimum value of all Sales entries: $1500, $250, $300, and $700.

SQL uses the MAX function to find the maximum value in a column. The syntax for using the MAX function is:

SELECT MAX(“column_name”)
FROM
“table_name”

For example, if we want to get the highest sales from the following table,

Store Sales Information

we would type in
SELECT MAX(Sales) FROM Store_Information

Result:
MAX(Sales)
$1500
$1500 represents the maximum value of all Sales entries: $1500, $250, $300, and $700.

Another arithmetic function is COUNT. This allows us to COUNT up the number of row in a certain table. The syntax is:

SELECT COUNT(“column_name”)
FROM “table_name”

For example, if we want to find the number of store entries in our table, type:

Store Sales Information

SELECT COUNT(store_name)
FROM
Store_Information

Result:
Count(store_name)
4

SQL uses the AVG() function to calculate the average of a column.

The syntax for using this function is;

SELECT
AVG(“column_name”)
FROM
“table_name”

For example, if we want to get the average of all sales from the following table:

Store Sales Information

you would type in”

SELECT
AVG(Sales)
FROM
Store_Information

Result:
AVG(Sales) = $687.5

$687.5 represents the average of all Sales entries: ($1500 + $250 + $300 + $700) / 4.

The DATETIME data type is often misunderstood and used inefficiently. This article focuses on the date component of DATETIME, how it is handled internally and how it can be used effectively for querying. The DATETIME type is internally stored as two separate 4-byte integers: one of those integers stores the date portion, and the other the time. When the date portion has a value of 0, the date is 1900-01-01. Because the date is internally stored as an INT, casting and converting directly between the types is natural:

SELECT CONVERT(DATETIME, 0)–Result: 1900-01-01–Store the date to a DATETIME variable firstDECLARE @DATE DATETIMESET @DATE = ’1900-01-01′SELECT CAST(@DATE AS INT)–Result: 0
You can use cast and convert for either direction, there is no restriction. Note in the second example, I stored the date in a variable first, because there is no way to natively pass a DATETIME type into the query window; SQL Server must always convert it from a string. By taking the intermediary step of storing it into a variable, we ensure that the Database Engine truly understands the value as a DATETIME and not a VARCHAR. If you had attempted to convert the VARCHAR value directly, you would receive the following error:

SELECT CAST(’1900-01-01′ AS INT)–Msg 245, Level 16, State 1, Line 1–Conversion failed when converting the varchar value ’1900-01-01′ to data type int.
The trick works for any value valid in the DATETIME range (January 1, 1753, through December 31, 9999):

SELECT CONVERT(DATETIME, 2958463)–Result: 9999-12-31
Try to go too far, however, and you’ll get an error:

SELECT CONVERT(DATETIME, 2958464)–Msg 8115, Level 16, State 2, Line 1–Arithmetic overflow error converting expression to data type datetime.

Manipulating Dates
Fortunately Microsoft has provided some very useful functions for manipulating the DATETIME data type, negating our need to perform complicated math.

DATEADD
According to MSDN, DATEADD returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date. The function prototype looks something like this:

DATEADD (DATEPART, number, DATE)
Because any date can be referenced as a simple integer, adding dates becomes trivial with the DATEADD function. Let’s compare adding days with both as a DATETIME and as an INT:

SELECT DATEADD(DAY, 1, ’1900-1-1′)–Result: 1900-01-02
And compare that with adding 1 date to 0

SELECT DATEADD(DAY, 1, 0)–Result: 1900-01-02
Notice the result is the same. We can also add any other datepart. Want the start of 2009?

SELECT DATEADD(YEAR, 109, 0) — Result: 2009-01-01

DATEDIFF
DATEDIFF acts in an opposite capacity of DATEADD: it calculates the date or time difference between DATETIME values. The prototype looks like this:

DATEDIFF (DATEPART, startdate , enddate)
And an actual example:

SELECT DATEDIFF(YEAR, ’1900-01-01′, ’2009-01-01′)–Result: 109
As before, we can certainly specify any of the dates as a INT. Since we know that 1900 has a corresponding integer value of 0, let’s try that:

SELECT DATEDIFF(YEAR, 0, ’2009-01-01′)–Result: 109
We can take it a step further and compare two INT values (again, as long as they fall within the valid DATETIME range). Let’s find how many days are between 1900-01-01 and 9999-12-31:

SELECT DATEDIFF(YEAR, 0, 2958463)–Result: 8099
The important aspect to note is that DATEDIFF “returns the number of date and time boundaries crossed between two specified dates.” (MSDN) In plain language, that means SQL Server never rounds the time period, but rather calculates the time periods completed. For example, let’s check the number of years between Leap Day in 2000 and Christmas 2008:

SELECT DATEDIFF(YEAR, ’2000-02-29′, ’2008-12-25′)–Result: 8
Although the total time is nearly 8.9 years, only 8 actual year boundaries have been crossed. This principle is the basis for calculating beginnings of time periods. When we combine DATEDIFF with DATEADD, the combination will predictably give us the start of any time period. Let’s say we wanted to calculate the number of years since 1900-01-01:

–Assume GETDATE() returns 2009-02-12 22:23:00.000 SELECT DATEDIFF(YEAR, 0, GETDATE())–Result: 109
We could easily take this result and place it in a DATEADD and see that we get 1900 + 109 = 2009

SELECT DATEADD(YEAR, 109, ’1900-01-01′)–Result: 2009-01-01
Or, as before, let’s use the integer value for 1900-01-01:

SELECT DATEADD(YEAR, 109, 0)–Result: 2009-01-01
Since DATEDIFF returns an INT and DATEADD adds an INT to a DATETIME, we can combine the two and return a natural DATETIME in a single query:

–Assume GETDATE() returns 2009-02-12 22:23:00.000SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)–Result: 2009-01-01
To analyze what is actually occurring, let’s start with the DATEDIFF in the middle . This calculates the number of year boundaries crossed between 1900 and 2009, which is 109. The outer portion adds that result of 109 to 1900 and gives us the start of the year 2009. This technique allows us to find the start of any date or time period based on a relative starting point. That starting point is commonly GETDATE(), but can be any valid DATETIME. If you want the beginning of the month, swap “year” for “month”. The same can be used for day, week, or any other datepart. This can help us greatly when querying a DATETIME field to fall in a range of values (i.e. “Select all rows created this year.”) What if we wanted the start of next year (2010)?

–Assume GETDATE() returns 2009-02-12 22:23:00.000SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)–Result: 2010-01-01
Notice the only change was a “+ 1″ next to the DATEDIFF calculation. Because we’re calculating the number of years since 1900 (109), we can adjust for years forward by adding, or years back by subtracting. By adding only 1, we jump forward a single year.

There are so many variants of SQL that it is hard sometimes to figure out what to use. Here is a short article that briefly explains the difference between SQL and PL/SQL. Enjoy!

What is SQL?

SQL (pronounced “sequal”) stands for Structured Query Language. Withe SQL, you can view data – called Data Definiton Language or DDL and manipulate data – called Data Manipulation Languate or DML. All of the above are just a fancy way to say that with SQL, the user can both view and alter records in the database. To help , here are a couple of queries:

DDL or View

SELECT * FROM employees

View all of the records in the employee table

DML or Manipulate

UPDATE employees SET employeefirstname = ‘John’ WHERE employeeid = 101

Find the record for employee ID 101 and change the first name to John

What is PL/SQL?

The official answer is from the PL/SQL User Guide:
PL/SQL, Oracle’s procedural extension of SQL, is an advanced fourth-generation programming language (4GL). It offers software-engineering features such as data encapsulation, overloading, collection types, exceptions, and information hiding. PL/SQL also supports rapid prototyping and development through tight integration with SQL and the Oracle database.
Huh? That is what I thought at the beginning. But at a high level, all this means is that it can do all of the things that regular SQL can do, but also, it is procedural and can be used like a programming language (C++, Java, etc.) For instance, you can use loops and If . . . Then statements in your PL/SQL statements (Programs).

Here is a definition of PL/SQL from Lewis Cunningham (an Oracle database expert):

“If I wanted to create my own, very short, definition of PL/SQL it would be this: PL/SQL is the Oracle native programming language that provides database-centric application development. It can natively call static SQL and provides multiple methods of calling dynamic SQL.

Mr. Cunningham also does a very good job of spelling out the differences between SQL and PL/SQL.

SQL is a data oriented language for selecting and manipulating sets of data. PL/SQL is a procedural language to create applications. You don’t normally have a “SQL application”. You normally have an application that uses SQL and a relational database on the back-end. PL/SQL can be the application language just like Java or PHP can. SQL may be the source of data for your screens, web pages and reports. PL/SQL might be the language you use to build, format and display those screens, web pages and reports.

Think of it like this: The code that makes your program function is PL/SQL. The code that manipulates the data is SQL DML. The code that creates stored database objects is SQL DDL. DDL compiles the code that is written in PL/SQL. PL/SQL may call SQL to perform data manipulation. The commands that format the output of a tool are not related to the SQL standard or to PL/SQL.
To muddy the waters a little more, while you cannot write an application in SQL, you can use a procedural language to automate parts. These are called Stored Procedures and here is a link to all you want to know about stored procedures on this site.