About the Database and SQL tutorial
Building a database is like building a house - you have to get it right at the design stage
(also called the modeling stage
Once all the walls are up and the plumbing is installed, it's a bit late to realize that the bathroom should have been three feet wider.
It can be done but, it gonna cost you, baby!
So, in these lessons we're going to spend some time on database modeling
. By studying several examples of database applications
we'll define what the rules are for building a model
of a database that we can then expand to cover all kinds of situations.
Modeling is what we do when we are designing a database. It's sort of like building a model car except that it's not 3D and it's not plastic.
Our model is on paper, or in a modeling program's data file, but it does have a lot of parts that must be assembled in a
logical fashion and must all be glued together to work properly.
Even though this course was created with examples from Access 2003, you might want to take a look at the more recent version of MS Access,
Microsoft Access 2010 tutorials.
It also covers many aspects of database design with some very concrete examples.
Want to try the great Linux OS
There's a small distribution that will run from within Windows - you can explore as much as you want.
for the free tutorials, downloads and articles.
Linux is a natural fit with MySQL.
You really should give it a try!
About the SQL language
Once the modeling part is down pat, we will create the databases and learn how to work with them using the versatile SQL language
SQL is the lingua franca
of databases. It allows you to communicate between relational databases
from Microsoft, MySQL and Oracle,
to name just a few. We will look at all the important SQL commands that need to be mastered, again through the use of many examples and exercises.
Throughout this tutorial we will use Microsoft Access as the source of most of our examples.
Almost all the sample databases are built in Microsoft Access.
Gradually, we are including MySQL
into the tutorial. The MySQL database server
is a powerful,
very versatile server which is part of the whole open-source environment
along with other tools such as
the Apache Web server
, the PHP language
and the Open Office suite
All those tools are readily available free on the Internet. They are now a credible alternative to the proprietary software
sold by Microsoft and Oracle. And since the recent announcement of a joint venture between Sun and Google,
you can bet that we'll be seeing a lot of action in that area in the coming months and years!
In addition to the database server itself, MySQL has a whole series of tools that work with it.
Most of the tools are developed by third-party vendors who usually sell them but there are always free versions of
these clients that help to make the developer's life a lot easier. Since new tools are coming out all the time
you may see different screen shots of the examples in different lessons as we update things. We'll try to make it not too confusing!
- A short history lesson on databases
- Steps in the database design process
- Data modeling - terminology, graphical representation
- The Entity-Relationship (E-R) Diagram
- Describing the 'Project Management' database
- Normalization of database relationships
- The one-to-many relationship
- The many-to-many relationship
- Introducing the SQL language
- SQL syntax
- Table manipulation instructions: CREATE, ALTER, DROP
- Data manipulation: INSERT, UPDATE, DELETE
- Introducing the MySQL database server
- Installing the MySQL server
- Installing the MySQL Administrator
- The MySQL GUI clients:
DBManager Pro from DBTools Software
SQLyog from WEByog
- Creating the Project Management database
- Using the Query Editor
- Using the Table Editor
- Importing database from Microsoft Access into MySQL
- Case study - the Bids Management database
- Creating queries - the SELECT statement
- The WHERE clause
- The DISTINCT clause
- Working with dates
- Date and Time functions in MySQL
- New case study in MySQL - The 'Bids Management' database
- Aggregate functions: sum(), count(), avg(), min(), max()
- Subqueries with aggregates
- Other uses of subqueries
- Sorting data - the ORDER BY clause
- Grouping data - the GROUP BY clause
- Selecting groups with the HAVING ... clause
- Refining the 'Project Management' example
- Using multiple tables
- Joining tables
- The JOIN formula
- Using aliases with duplicate field names
- Another use of aliases
- Outer JOINs
- Advanced topics
- The UNION query
- Adding a 'Totals' row using UNION
- The power of the Crosstab query
- Sample databases: Microsoft Access
with MySQL databases
If you haven't found the database resource you're looking for,
use our Google Search box for more information.