Whether you are a newbie or a seasoned expert looking to tweak query performance, this learning guide to the structured query language (SQL) has something for you. These seven chapters can help you figure out how to get the data you need by looking at solutions to real-world problems. The advice listed below comes primarily from SQL guru Rudy Limeback.
Chances are, you are here because you want to know more about how to get the data from a relational database. It helps to know more about how the different tables are connected. That's where primary and foreign keys come in.
What is a primary key? Composite PK? Foreign key? What is a candidate key?
Although you don't have to define a primary key for a table, if you plan on referencing that table, there are many reasons why the primary key is important, even if you think irrelevant data entry is handled at the front end.
You can form your primary key from:
You cannot have two primary keys on a table, but you can use several columns to form a composite primary key. Take note, however, that there is a maximum number of columns it can have. Using composite keys can be a straightforward solution when dealing with M:N relationships. Here is an example of a query that will return the "next" composite primary key from a result set in SQL server.
The most important thing to remember about primary keys is that you must
ensure that they are unique. It seems obvious, but defining primary keys can get messy when data rows get moved to other tables. Your database design should take into consideration subtypes and supertypes.
The foreign key must reference a unique key, the primary key. But many foreign keys can reference the same primary key, and optional foreign keys can be NULL. Here's how you can get the surrogate key of the row just inserted.
If you are referencing a composite primary key, your foreign key will be composite as well. You can sometimes run into problems when columns involved in composite keys become orphaned. You can make business rules to prevent childless parents, or you could create assertions to ensure certain conditions always exist.
Here's what the recursive relationship looks like in SQL. If you need recursion, but your DBMS doesn't support it, here is a work-around.
The key point in comparing data in tables is to realize that the data are not stored in any specific order. To get the first 10, the last 10, or some other arbitrary number of records, you have to define the order in terms of a particular column, using the ORDER BY clause. Here are examples of how to get :
Here's what happens if there are ties. Here is how to get the top 10 using the TOP keyword, even with multiple columns. There is a difference between the ORDER BY and GROUP BY clauses. Here's an explanation of the difference between WHERE and HAVING and an in-depth discussion on the GROUP BY clause.
Here are some examples of how you can use SQL to:
Once you get the right results, you can:
When comparing two or more tables, you can:
You can use SQL to perform calculations on data. Here are a few examples of how to:
Each DBMS has its own way of working with dates. But here are a few examples of how to work time and dates in with your queries to:
SQL queries can be written in many ways. In this webcast on Oracle SQL tuning secrets, guru Don Burleson discusses a few techniques, including using materialized views, for improving the speed of SQL queries. In excerpts from her book Oracle SQL and Index Internals, author Kimberly Floss explores Oracle's internal mechanisms like the cost-based optimizer and indexing.
Here are some examples that can help you decide what the best solution is:
- Crash Course: Oracle basics This guide helps you to research, compare or work with the Oracle DBMS. It provides Oracle basics for versions 8.x, 9.x and 10g.
- Learning guide to Oracle error messages Solve your Oracle errors quickly and easily with help from this fast guide. It covers every expert response pertaining to Oracle errors on SearchOracle.com.
- Learning Guide -- Performance tuning Newbies and experts will benefit from this guide; you'll establish a smart approach to tuning, use and interpret Oracle's tuning utilities and identify specific problem areas.
- Learning guide to PL/SQL This fast guide to PL/SQL can help both newbies and veterans become better acquainted with the language and hone their developer skills.
- Learning guide: Oracle security This guide covers a wide variety of topics on many aspects of Oracle security to help you lock down your data.
- Fast Guide: Becoming an Oracle Certified Professional Get expert technical advice on where to begin your certification studies, passing the final exam and putting your certification into effect in the workplace.
- Guide to Oracle freeware and shareware A library of freeware, shareware or inexpensive tools (less than $100) to help with Oracle administration and development.
- Learning Guide: Backup and recovery This guide covers everything from the basics of creating a backup and recovery plan to the specifics of using RMAN and other methods of hot and cold backups.
- Learning Guide: SQL Whether you are a newbie or a seasoned expert looking to tweak query performance, this learning guide can help you figure out how to get the data you need by looking at solutions to real-world problems.
