Ultimate SQL Tutorial




SQL Data Types
Data types and ranges for Microsoft Access, MySQL and SQL Server.


Microsoft Access Data Types
Data type
Description
Storage
Text
Use for text or combinations of text and numbers. 255 characters maximum

Memo
Memo is used for larger amounts of text. Stores up to 65,536 characters. Note: You cannot sort a memo field. However, they are searchable

Byte
Allows whole numbers from 0 to 255
1 byte
Integer
Allows whole numbers between -32,768 and 32,767
2 bytes
Long
Allows whole numbers between -2,147,483,648 and 2,147,483,647
4 bytes
Single
Single precision floating-point. Will handle most decimals
4 bytes
Double
Double precision floating-point. Will handle most decimals
8 bytes
Currency
Use for currency. Holds up to 15 digits of whole dollars, plus 4 decimal places. Tip: You can choose which country's currency to use
8 bytes
AutoNumber
AutoNumber fields automatically give each record its own number, usually starting at 1
4 bytes
Date/Time
Use for dates and times
8 bytes
Yes/No
A logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False (equivalent to -1 and 0).Note: Null values are not allowed in Yes/No fields
1 bit
Ole Object
Can store pictures, audio, video, or other BLOBs (Binary Large OBjects)
up to 1GB
Hyperlink
Contain links to other files, including web pages

Lookup Wizard
Let you type a list of options, which can then be chosen from a drop-down list
4 bytes



MySQL Data Types
In MySQL there are three main types : text, number, and Date/Time types.
Text types:
Data type
Description
CHAR(size)
Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size)
Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
TINYTEXT
Holds a string with a maximum length of 255 characters
TEXT
Holds a string with a maximum length of 65,535 characters
BLOB
For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT
Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB
For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT
Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB
For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.)
Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.
Note: The values are sorted in the order you enter them.
You enter the possible values in this format: ENUM('X','Y','Z')
SET
Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice
Number types:
Data type
Description
TINYINT(size)
-128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis
SMALLINT(size)
-32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis
MEDIUMINT(size)
-8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis
INT(size)
-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
BIGINT(size)
-9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis
FLOAT(size,d)
A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DOUBLE(size,d)
A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DECIMAL(size,d)
A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.
Date types:
Data type
Description
DATE()
A date. Format: YYYY-MM-DD
Note: The supported range is from '1000-01-01' to '9999-12-31'
DATETIME()
*A date and time combination. Format: YYYY-MM-DD HH:MM:SS
Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP()
*A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MM:SS
Note: The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC
TIME()
A time. Format: HH:MM:SS
Note: The supported range is from '-838:59:59' to '838:59:59'
YEAR()
A year in two-digit or four-digit format.
Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069
*Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD.


SQL Server Data Types
Character strings:
Data type
Description
Storage
char(n)
Fixed-length character string. Maximum 8,000 characters
n
varchar(n)
Variable-length character string. Maximum 8,000 characters

varchar(max)
Variable-length character string. Maximum 1,073,741,824 characters

text
Variable-length character string. Maximum 2GB of text data

Unicode strings:
Data type
Description
Storage
nchar(n)
Fixed-length Unicode data. Maximum 4,000 characters

nvarchar(n)
Variable-length Unicode data. Maximum 4,000 characters

nvarchar(max)
Variable-length Unicode data. Maximum 536,870,912 characters

ntext
Variable-length Unicode data. Maximum 2GB of text data

Binary types:
Data type
Description
Storage
bit
Allows 0, 1, or NULL

binary(n)
Fixed-length binary data. Maximum 8,000 bytes

varbinary(n)
Variable-length binary data. Maximum 8,000 bytes

varbinary(max)
Variable-length binary data. Maximum 2GB

image
Variable-length binary data. Maximum 2GB

Number types:
Data type
Description
Storage
tinyint
Allows whole numbers from 0 to 255
1 byte
smallint
Allows whole numbers between -32,768 and 32,767
2 bytes
int
Allows whole numbers between -2,147,483,648 and 2,147,483,647
4 bytes
bigint
Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807
8 bytes
decimal(p,s)
Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1.
The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.
The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
5-17 bytes
numeric(p,s)
Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1.
The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.
The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
5-17 bytes
smallmoney
Monetary data from -214,748.3648 to 214,748.3647
4 bytes
money
Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807
8 bytes
float(n)
Floating precision number data from -1.79E + 308 to 1.79E + 308.
The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.
4 or 8 bytes
real
Floating precision number data from -3.40E + 38 to 3.40E + 38
4 bytes
Date types:
Data type
Description
Storage
datetime
From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds
8 bytes
datetime2
From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds
6-8 bytes
smalldatetime
From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute
4 bytes
date
Store a date only. From January 1, 0001 to December 31, 9999
3 bytes
time
Store a time only to an accuracy of 100 nanoseconds
3-5 bytes
datetimeoffset
The same as datetime2 with the addition of a time zone offset
8-10 bytes
timestamp
Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable

Other data types:
Data type
Description
sql_variant
Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier
Stores a globally unique identifier (GUID)
xml
Stores XML formatted data. Maximum 2GB
cursor
Stores a reference to a cursor used for database operations
table
Stores a result-set for later processing

SQL Functions
SQL has many built-in functions for performing calculations on data.


SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
  • AVG() - Returns the average value
  • COUNT() - Returns the number of rows
  • FIRST() - Returns the first value
  • LAST() - Returns the last value
  • MAX() - Returns the largest value
  • MIN() - Returns the smallest value
  • SUM() - Returns the sum


SQL Scalar functions
SQL scalar functions return a single value, based on the input value.
Useful scalar functions:
  • UCASE() - Converts a field to upper case
  • LCASE() - Converts a field to lower case
  • MID() - Extract characters from a text field
  • LEN() - Returns the length of a text field
  • ROUND() - Rounds a numeric field to the number of decimals specified
  • NOW() - Returns the current system date and time
  • FORMAT() - Formats how a field is to be displayed
Tip: The aggregate functions and the scalar functions will be explained in details in the next chapters.

The AVG() Function
The AVG() function returns the average value of a numeric column.
SQL AVG() Syntax
SELECT AVG(column_name) FROM table_name



SQL AVG() Example
We have the following "Orders" table:
O_Id
OrderDate
OrderPrice
Customer
1
2008/11/12
1000
Hansen
2
2008/10/23
1600
Nilsen
3
2008/09/02
700
Hansen
4
2008/09/03
300
Hansen
5
2008/08/30
2000
Jensen
6
2008/10/04
100
Nilsen
Now we want to find the average value of the "OrderPrice" fields.
We use the following SQL statement:
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
The result-set will look like this:
OrderAverage
950
Now we want to find the customers that have an OrderPrice value higher than the average OrderPrice value.
We use the following SQL statement:
SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
The result-set will look like this:
Customer
Hansen
Nilsen
Jensen

SQL COUNT() Function

The COUNT() function returns the number of rows that matches a specified criteria.


SQL COUNT(column_name) Syntax
The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:
SELECT COUNT(column_name) FROM table_name
SQL COUNT(*) Syntax
The COUNT(*) function returns the number of records in a table:
SELECT COUNT(*) FROM table_name
SQL COUNT(DISTINCT column_name) Syntax
The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:
SELECT COUNT(DISTINCT column_name) FROM table_name
Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.


SQL COUNT(column_name) Example
We have the following "Orders" table:
O_Id
OrderDate
OrderPrice
Customer
1
2008/11/12
1000
Hansen
2
2008/10/23
1600
Nilsen
3
2008/09/02
700
Hansen
4
2008/09/03
300
Hansen
5
2008/08/30
2000
Jensen
6
2008/10/04
100
Nilsen
Now we want to count the number of orders from "Customer Nilsen".
We use the following SQL statement:
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen'
The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders in total:
CustomerNilsen
2



SQL COUNT(*) Example
If we omit the WHERE clause, like this:
SELECT COUNT(*) AS NumberOfOrders FROM Orders
The result-set will look like this:
NumberOfOrders
6
which is the total number of rows in the table.


SQL COUNT(DISTINCT column_name) Example
Now we want to count the number of unique customers in the "Orders" table.
We use the following SQL statement:
SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders
The result-set will look like this:
NumberOfCustomers
3
which is the number of unique customers (Hansen, Nilsen, and Jensen) in the "Orders" table.
The FIRST() Function
The FIRST() function returns the first value of the selected column.
SQL FIRST() Syntax
SELECT FIRST(column_name) FROM table_name



SQL FIRST() Example
We have the following "Orders" table:
O_Id
OrderDate
OrderPrice
Customer
1
2008/11/12
1000
Hansen
2
2008/10/23
1600
Nilsen
3
2008/09/02
700
Hansen
4
2008/09/03
300
Hansen
5
2008/08/30
2000
Jensen
6
2008/10/04
100
Nilsen
Now we want to find the first value of the "OrderPrice" column.
We use the following SQL statement:
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders
 Tip: Workaround if FIRST() function is not supported:
SELECT OrderPrice FROM Orders ORDER BY O_Id LIMIT 1
The result-set will look like this:
FirstOrderPrice
1000

The LAST() Function
The LAST() function returns the last value of the selected column.
SQL LAST() Syntax
SELECT LAST(column_name) FROM table_name



SQL LAST() Example
We have the following "Orders" table:
O_Id
OrderDate
OrderPrice
Customer
1
2008/11/12
1000
Hansen
2
2008/10/23
1600
Nilsen
3
2008/09/02
700
Hansen
4
2008/09/03
300
Hansen
5
2008/08/30
2000
Jensen
6
2008/10/04
100
Nilsen
Now we want to find the last value of the "OrderPrice" column.
We use the following SQL statement:
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders
 Tip: Workaround if LAST() function is not supported:
SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1
The result-set will look like this:
LastOrderPrice
100

The MAX() Function
The MAX() function returns the largest value of the selected column.
SQL MAX() Syntax
SELECT MAX(column_name) FROM table_name



SQL MAX() Example
We have the following "Orders" table:
O_Id
OrderDate
OrderPrice
Customer
1
2008/11/12
1000
Hansen
2
2008/10/23
1600
Nilsen
3
2008/09/02
700
Hansen
4
2008/09/03
300
Hansen
5
2008/08/30
2000
Jensen
6
2008/10/04
100
Nilsen
Now we want to find the largest value of the "OrderPrice" column.
We use the following SQL statement:
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
The result-set will look like this:
LargestOrderPrice
2000

The MIN() Function
The MIN() function returns the smallest value of the selected column.
SQL MIN() Syntax
SELECT MIN(column_name) FROM table_name



SQL MIN() Example
We have the following "Orders" table:
O_Id
OrderDate
OrderPrice
Customer
1
2008/11/12
1000
Hansen
2
2008/10/23
1600
Nilsen
3
2008/09/02
700
Hansen
4
2008/09/03
300
Hansen
5
2008/08/30
2000
Jensen
6
2008/10/04
100
Nilsen
Now we want to find the smallest value of the "OrderPrice" column.
We use the following SQL statement:
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders
The result-set will look like this:
SmallestOrderPrice
100

The SUM() Function
The SUM() function returns the total sum of a numeric column.
SQL SUM() Syntax
SELECT SUM(column_name) FROM table_name



SQL SUM() Example
We have the following "Orders" table:
O_Id
OrderDate
OrderPrice
Customer
1
2008/11/12
1000
Hansen
2
2008/10/23
1600
Nilsen
3
2008/09/02
700
Hansen
4
2008/09/03
300
Hansen
5
2008/08/30
2000
Jensen
6
2008/10/04
100
Nilsen
Now we want to find the sum of all "OrderPrice" fields".
We use the following SQL statement:
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders
The result-set will look like this:
OrderTotal
5700

The GROUP BY Statement
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
SQL GROUP BY Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name



SQL GROUP BY Example
We have the following "Orders" table:
O_Id
OrderDate
OrderPrice
Customer
1
2008/11/12
1000
Hansen
2
2008/10/23
1600
Nilsen
3
2008/09/02
700
Hansen
4
2008/09/03
300
Hansen
5
2008/08/30
2000
Jensen
6
2008/10/04
100
Nilsen
Now we want to find the total sum (total order) of each customer.
We will have to use the GROUP BY statement to group the customers.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
The result-set will look like this:
Customer
SUM(OrderPrice)
Hansen
2000
Nilsen
1700
Jensen
2000
Nice! Isn't it? :)
Let's see what happens if we omit the GROUP BY statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
The result-set will look like this:
Customer
SUM(OrderPrice)
Hansen
5700
Nilsen
5700
Hansen
5700
Hansen
5700
Jensen
5700
Nilsen
5700
The result-set above is not what we wanted.
Explanation of why the above SELECT statement cannot be used: The SELECT statement above has two columns specified (Customer and SUM(OrderPrice). The "SUM(OrderPrice)" returns a single value (that is the total sum of the "OrderPrice" column), while "Customer" returns 6 values (one value for each row in the "Orders" table). This will therefore not give us the correct result. However, you have seen that the GROUP BY statement solves this problem.


GROUP BY More Than One Column
We can also use the GROUP BY statement on more than one column, like this:
SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

The HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SQL HAVING Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value



SQL HAVING Example
We have the following "Orders" table:
O_Id
OrderDate
OrderPrice
Customer
1
2008/11/12
1000
Hansen
2
2008/10/23
1600
Nilsen
3
2008/09/02
700
Hansen
4
2008/09/03
300
Hansen
5
2008/08/30
2000
Jensen
6
2008/10/04
100
Nilsen
Now we want to find if any of the customers have a total order of less than 2000.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
The result-set will look like this:
Customer
SUM(OrderPrice)
Nilsen
1700
Now we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500.
We add an ordinary WHERE clause to the SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
The result-set will look like this:
Customer
SUM(OrderPrice)
Hansen
2000
Jensen
2000

The UCASE() Function
The UCASE() function converts the value of a field to uppercase.
SQL UCASE() Syntax
SELECT UCASE(column_name) FROM table_name
Syntax for SQL Server
SELECT UPPER(column_name) FROM table_name



SQL UCASE() Example
We have the following "Persons" table:
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
Now we want to select the content of the "LastName" and "FirstName" columns above, and convert the "LastName" column to uppercase.
We use the following SELECT statement:
SELECT UCASE(LastName) as LastName,FirstName FROM Persons
The result-set will look like this:
LastName
FirstName
HANSEN
Ola
SVENDSON
Tove
PETTERSEN
Kari

The LCASE() Function
The LCASE() function converts the value of a field to lowercase.
SQL LCASE() Syntax
SELECT LCASE(column_name) FROM table_name
Syntax for SQL Server
SELECT LOWER(column_name) FROM table_name



SQL LCASE() Example
We have the following "Persons" table:
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
Now we want to select the content of the "LastName" and "FirstName" columns above, and convert the "LastName" column to lowercase.
We use the following SELECT statement:
SELECT LCASE(LastName) as LastName,FirstName FROM Persons
The result-set will look like this:
LastName
FirstName
hansen
Ola
svendson
Tove
pettersen
Kari

The MID() Function
The MID() function is used to extract characters from a text field.
SQL MID() Syntax
SELECT MID(column_name,start[,length]) FROM table_name

Parameter
Description
column_name
Required. The field to extract characters from
start
Required. Specifies the starting position (starts at 1)
length
Optional. The number of characters to return. If omitted, the MID() function returns the rest of the text



SQL MID() Example
We have the following "Persons" table:
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
Now we want to extract the first four characters of the "City" column above.
We use the following SELECT statement:
SELECT MID(City,1,4) as SmallCity FROM Persons
The result-set will look like this:
SmallCity
Sand
Sand
Stav

The LEN() Function
The LEN() function returns the length of the value in a text field.
SQL LEN() Syntax
SELECT LEN(column_name) FROM table_name



SQL LEN() Example
We have the following "Persons" table:
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
Now we want to select the length of the values in the "Address" column above.
We use the following SELECT statement:
SELECT LEN(Address) as LengthOfAddress FROM Persons
The result-set will look like this:
LengthOfAddress
12
9
9

The ROUND() Function
The ROUND() function is used to round a numeric field to the number of decimals specified.
SQL ROUND() Syntax
SELECT ROUND(column_name,decimals) FROM table_name

Parameter
Description
column_name
Required. The field to round.
decimals
Required. Specifies the number of decimals to be returned.



SQL ROUND() Example
We have the following "Products" table:
Prod_Id
ProductName
Unit
UnitPrice
1
Jarlsberg
1000 g
10.45
2
Mascarpone
1000 g
32.56
3
Gorgonzola
1000 g
15.67
Now we want to display the product name and the price rounded to the nearest integer.
We use the following SELECT statement:
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products
The result-set will look like this:
ProductName
UnitPrice
Jarlsberg
10
Mascarpone
33
Gorgonzola
16

The NOW() Function
The NOW() function returns the current system date and time.
SQL NOW() Syntax
SELECT NOW() FROM table_name



SQL NOW() Example
We have the following "Products" table:
Prod_Id
ProductName
Unit
UnitPrice
1
Jarlsberg
1000 g
10.45
2
Mascarpone
1000 g
32.56
3
Gorgonzola
1000 g
15.67
Now we want to display the products and prices per today's date.
We use the following SELECT statement:
SELECT ProductName, UnitPrice, Now() as PerDate FROM Products
The result-set will look like this:
ProductName
UnitPrice
PerDate
Jarlsberg
10.45
10/7/2008 11:25:02 AM
Mascarpone
32.56
10/7/2008 11:25:02 AM
Gorgonzola
15.67
10/7/2008 11:25:02 AM

The FORMAT() Function
The FORMAT() function is used to format how a field is to be displayed.
SQL FORMAT() Syntax
SELECT FORMAT(column_name,format) FROM table_name

Parameter
Description
column_name
Required. The field to be formatted.
format
Required. Specifies the format.



SQL FORMAT() Example
We have the following "Products" table:
Prod_Id
ProductName
Unit
UnitPrice
1
Jarlsberg
1000 g
10.45
2
Mascarpone
1000 g
32.56
3
Gorgonzola
1000 g
15.67
Now we want to display the products and prices per today's date (with today's date displayed in the following format "YYYY-MM-DD").
We use the following SELECT statement:
SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products
The result-set will look like this:
ProductName
UnitPrice
PerDate
Jarlsberg
10.45
2008-10-07
Mascarpone
32.56
2008-10-07
Gorgonzola
15.67
2008-10-07

THANK YOU

Computer science, Number Systems, Boolean Algebra,QBASIC, C tutorial, SQL Tutorial, HTML 5 tutorial

Follow by Email