Structured Query Language (SQL) tutorial
Comparison of SQL commands used in WHERE clauses
This tutorial will grow over time. It is being written as the command comparisons are required by my classes.
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 following - 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:
Referencing literals in WHERE clauses
| Datatype | MySQL | Oracle | SQL Server | Microsoft Access |
|---|---|---|---|---|
| Text strings | "example" or 'example', eg SELECT name FROM TABLE WHERE title="Mr"; | 'example', eg SELECT name FROM TABLE WHERE title='Mr'; |
'example', eg SELECT name FROM TABLE WHERE title='Mr'; |
"example", eg SELECT name FROM TABLE WHERE title="Mr"; |
| Numbers | No symbols required, eg SELECT name FROM TABLE WHERE age=10; | As MySQL | As MySQL | As MySQL |
| Dates | Date enclosed in single quotes, eg SELECT name FROM TABLE WHERE birthdate='2000-10-17'; |
As MySQL | As MySQL | Date enclosed between # symbol, eg SELECT name FROM TABLE WHERE birthdate=#2000-10-17#; |
Operators used in WHERE clauses
| MySQL | Oracle | SQL Server | Microsoft Access | |
|---|---|---|---|---|
| Inequality | != or <> | != or ^= or <> | As MySQL | <> |
| Greater than | > | As MySQL | As MySQL | As MySQL |
| Less than | < | As MySQL | As MySQL | As MySQL |
| Text partial match | LIKE + % as wildcard for 0 to several characters, eg SELECT age FROM table WHERE name LIKE "J%"; |
As MySQL | As MySQL | LIKE + * as wildcard for 0 to several characters, eg SELECT age FROM table WHERE name LIKE "J*"; |
Limiting the number of rows returned
| MySQL | Oracle | SQL Server | Microsoft Access | |
|---|---|---|---|---|
| Command | LIMIT n, eg SELECT age FROM table LIMIT 5; |
ROWNUM with n, eg SELECT age FROM table WHERE ROWNUM < = 5; |
TOP n, eg SELECT TOP 5 age FROM table; |
As SQL Server |
What's next?
Most databases provide mathematical functions and operators to manipulate numbers and dates in various ways. These are not consistent between databases. The next page will show you some of the more useful functions for MySQL, Oracle, SQL Server and Microsoft Access.