We have spent considerable time laying the foundation for your use of SQL. These are important aspects and will be truly helpful as you use the databases associated with your company or endeavor. This module dicusses the business of actually working with the table, issuing queries against it and returning information.
Perhaps some of the most important things to keep in mind as you work with SQL are the items that make up the foundation. Ask yourself
- why a table is designed as it is,
- why is the information that is in that table placed there?
- Is there a better, more normalized way to store the information?
- Should exceptions be made to the normalization approach for this table?
It is not that everything has been created incorrectly, but rather that you can more readily use SQL if you understand exactly how information is stored and why it was stored that way.
Both the (ISO) International Organization for Standardization and the American National Standards Institute (ANSI) have standards for the SQL language and most database products follow those standards pretty faithfully.
However, different database products also add extra features to make certain chores easier. Those features can make your life easier but only if you are aware of which features are standard and which are not.
For example, in the Transact-SQL language used by SQL Server, the special values
- @@TOTAL_READS, and
return the total number of disk write errors, disk reads, and disk writes respectively since SQL Server was last started. Other relational databases do not provide those,
although they may have their own special values that return similar statistics.
If you use them irresponsibly, it may be very hard to rebuild your database or the applications that use it if you are forced to move to a new kind of database.
For that matter, extra features can make it hard for you to reuse tools and techniques that you develop in your next database project.
Fortunately most flavors of SQL are 93% identical.
You can guard against troublesome changes in the future by keeping the non-standard features in a single place as much as possible.
Usually the places where SQL implementations differ the most is in system-level activities such as database management and searching meta-data.
For example, different databases might provide different tools for
- searching through lists of tables,
- creating new databases,
- learning about the number of reads and writes
- that the database performed,
- examining errors, and
- optimizing queries.
In the next lesson, you will create the table that is used for the rest of this module.