Structured Query Language (SQL) tutorial
Comparison of Useful functions
There is a growing requirement to learn and use SQL because of the recent proliferation of user-friendly but powerful databases. Some developers are fortunate in that they can remain specialists working with their chosen database. Others will find that they need to learn to manage more than one relational database management system (RDBMS). This - and related - pages attempt to make the learning process as easy as possible.
At present, I do not intend to delve into the similarities and differences between proprietary SQL-based scripting languages such as Transact-SQL used with SQL Server, or Oracle's PL/SQL. These pages will contain the basic, (almost) generic SQL commands used with many large databases.
Topics covered:
- Displaying the current date and time
- Displaying text strings
- Evaluating expressions
- Mathematical functions
Displaying the current date and time
| Date part | MySQL | Oracle | SQL Server | Microsoft Access |
|---|---|---|---|---|
| Current date and time | now() function, eg SELECT now(); |
Use SYSDATE with DUAL table, eg SELECT SYSDATE FROM DUAL; |
getdate() function, eg SELECT getdate(); |
Now() function, eg SELECT Now(); |
| Current day | DAYOFMONTH() function, eg SELECT DAYOFMONTH(CURRENT_DATE); |
TO_DATE() function, eg SELECT TO_DATE(SYSDATE, 'DD'); |
DATEPART() function, eg SELECT DATEPART(dd, GETDATE()) |
Day() or Format() function, eg SELECT Day(Now); OR SELECT Format(now, "dd"); |
| Current month (number) | Month() function, eg SELECT MONTH(CURRENT_DATE); |
TO_DATE() function, eg SELECT TO_DATE(SYSDATE, 'MM'); |
DATEPART() function, eg SELECT DATEPART(mm, GETDATE()); |
Month() or Format() function, eg, eg SELECT Month(NOW); OR SELECT Format(Now, "mm"); |
You can find further date and time formatting codes at the following locations:
These codes are used in the same way that the day and month are selected in the above examples.
| MySQL | Oracle | SQL Server | Microsoft Access | |
|---|---|---|---|---|
| Text strings | SELECT the text string, eg SELECT 'I use MySQL'; |
SELECT the text string from DUAL, eg< /td> SELECT 'I use Oracle from DUAL'; |
As MySQL, eg SELECT 'I use SQL Server'; |
As MySQL, eg SELECT 'I use Access'; |
| Concatenating text strings | CONCAT() function, eg SELECT CONCAT(f_name, " ", l_name) from people; |
As MySQL | Format output using operators, eg SELECT (f_name & " " & l_name) FROM people; |
As SQL Server |
In the above example, f_name and l_name are fields of the people table. These fields store the first name and last name for each record respectively. The concatenated output displays the full name of each person.
| MySQL | Oracle | SQL Server | Microsoft Access | |
|---|---|---|---|---|
| Division using integer datatypes | Automatically casts results to decimal if necessary | Uses only NUMBER datatype: no casting required | Performs integer division on INTEGER datatypes.
Use DECIMAL, FLOAT or REAL datatypes for fields involved in calculations | Automatically casts results to decimal if necessary |
| Function | MySQL | Oracle | SQL Server | Microsoft Access |
|---|---|---|---|---|
| Modulus | Uses modulus operator (%) or MOD() function, eg SELECT (87 % 9); OR SELECT MOD(87, 9); Result is 6 |
Uses MOD() function, eg SELECT MOD(87, 9); Result is 6 |
Uses modulus operator (%), eg
SELECT (87 % 9); Result is 6 |
Uses mod operator, eg SELECT (87 mod 9); Result is 6 |
| Power | Uses POWER() function, eg SELECT POWER(4,3); Result is 64 |
As MySQL | POWER() function as for MySQL | Uses power operator (^), eg SELECT 4 ^ 3; Result is 64 |
| Square Root | Uses SQRT() function, eg SELECT SQRT(4); Result is 2 |
As MySQL | As MySQL | Uses SQR() function, eg SELECT SQR(4); Result is 2 |
The following mathematical functions are consistent between MySQL, Oracle, SQL Server and Microsoft Access. Remember that you must select from a table in Oracle. The default table used for these operations is DUAL.
| Expression | Function | Example |
|---|---|---|
| Absolute value | ABS(x) | SELECT ABS(-4.05); Result is 4.05 |
| Sign | SIGN(x) | SELECT SIGN(-32.5); Result is -1 in this case, 1 for positive numbers and 0 for the number 0 |
| Round | ROUND(x,y). This function rounds the value of x to y decimal places. | SELECT ROUND(50.113, 1); Result is 50.1 |
| Floor | FLOOR(x) | SELECT FLOOR(26.87); Result is 26 |
| Ceiling | CEILING(x) | SELECT CEILING(26.87); Result is 27 |
What's next?
MySQL, Oracle, SQL Server and Access have variations in the datatypes the RDBMS can use. The next tutorial will compare these datatypes.