Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

I collected on the following pages various SQL queries and their results on a sample database. They use different SQL functions and will help to learn and understand SQL queries. In addition they help for look up most commonSQL syntax examples. A SQL beginner will get a good overview about the capabilities of SQL. Examples are given for select, count, union, where, having, order, limit, views, NULL, like, subselects and joins.

 
Every query has a short description about it's purpose. Next he SQL query is listed with the result returned by the query. To create the sample database and -data download this file.  All  SQL query examples can be downloaded here. The sample database is a model of asmall database whichuses some data relations also used by facebook: Persons, friends, siblings, cities, addresses and messages.
 
For installation instructions for mySQL check this link and
mysql --column-names --table <frampsSQLByExample.sql
will install the sample sample data and run the sample queries
Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

SQL is not very easy to learn, in particular joins of multiiple tables are difficult to understand at a first glance. On the next pages it's explained for sql beginners how SQL joins work. Venn diagrams are used to explain how the tables are combined. In addition sample tables with sample data and sample joins are used to explain in detail the query input data results. SQL files to create the sample data and execute the queries can be downloaded. The samples are for mySQL and DB2.

If you want to see more complex sql joins in action visit Learning SQL by example for sql join examples and other SQL funktions as views, count, max, subselect and other.

sql_innerjoin.jpg sql_leftouterjoin.jpg sql_fullouterjoin.jpg sql_leftouterjoinminusintersection.jpg sql_fullouterjoinminusintersection.jpg

 

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

Some SQL DBMs allow to create recursive queries. That's a feature of  DB2 (free download ), SQL Server and Oracle. Unfortunately mySQL misses this feature. But every recursive algorithm can be written in a non recursive way - so it's not clear whether this is a need or just a nice DBMS feature. Anyhow - it's an interesting topic.

 
References:
 
3) Trees and other hierachies (no recursion)


 

 
Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

If you want to use the SQL example files from this website and execute them on your system you have to setup a mySQL database on your system. It's quite easy and the following steps describe how to setup and configure mySQL on Linux Mint11. On other Linux distributions the way to install mySQL will be the only different step.