SQL guide for Oracle users
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.
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.
| Chapter 1: Answers to very basic questions |  Return to top |
- What is SQL?
- How do I pronounce SQL?
- What do I need to know to be successful with SQL?
- What does NULL mean?
| Chapter 2: Primary and foreign keys | Return to top |
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:
- generated sequences (doesn't matter if there are gaps in the sequence.)
- alphanumeric characters
- existing columns (for example, the ISBN number of a book)
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.
| Chapter 3: Selecting data | Return to top |
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 :
- the top N subcategories within each major category
- the top N distinct records
- the top N for each X
- the last N rows in each group
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:
- select all rows for a particular column
- select 1
- select exactly one row or at least one row
- select consecutive rows
- select all combinations from one table that don't exist in another
- select rows based on a certain condition
- select rows from several tables based on a certain condition
- select rows based on several specific conditions within a certain timeframe
- select only the latest matching row
- select rows by mixing ORs with NOTs
- select rows based on lookup table
- select rows from four different tables
- select only the second duplicate
- select all users who are not in a group
- select only one distinct value in a group
- select the highest number in a group
- select every 100th row
- select the last row inserted
- select all rows, even if they are not related
- select the maximum value for the row
- select the highest value in each row
- select all rows that match in a many-to-many relationship
- select strings that are embedded in other columns
- select rows based on values returned in the main query
- select only the first n characters of a field
- select only the first name in a name field
- select only the surname in a name field
- select "ANY" search results with a dynamic query
- select LIKEs
- select based on multiple keywords
- select items based on many traits
- select groups based on one value from a column
- select sequential numbers
- select gaps in a sequence
- select a specific number of rows
- select random rows
- select by looping through rows
- select rows from unrelated one-to-many relationships
Once you get the right results, you can:
- translate a value to a string
- pad the returned rows with zeroes
- add leading zeros
- remove leading numbers from a field
- format output as header and details
- concatenate values into a comma-delimited string
- get a count on aggregated fields
- get a count of rows along with the selected results
- get counts and total count in one query
- get the count from two tables for a given date in one query
- sum two tables
- get counts of only some rows
- get counts of one and compare to total
- count rows in a subtree
- limit the number of rows returned and page through the results
- page through result set without using TOP or LIMIT (but here's the best way to page through results)
- format the results as a text file
- convert columns to rows
- convert rows to columns
- group by a column that isn't there
- consolidate multiple rows into one
- join several table summaries
- combine two result sets and specify order
| Chapter 4: Updating, inserting, and deleting data | Return to top |
When comparing two or more tables, you can:
- insert into and search tables with many-to-many relationships
- update with the newest data, delete matching rows, and insert rows that are missing
- update one table based on the conditions of the other
- delete one table based on the conditions of the other
- insert only some columns into a table
- check for duplicated before inserting rows
- delete duplicates
- copy an entire table
- compare one set of rows with another
- update a local table with new entries to a global table
- validate address information
- insert values based on another table
- insert data into an empty table
- insert multiple child rows
- update databases at two different physical locations
- delete values based on another table
- but watch out for NULLs (here's how to concatenate NULLs in a string)
| Chapter 5: Calculations | Return to top |
You can use SQL to perform calculations on data. Here are a few examples of how to:
- sum across columns
- get a cumulative sum across rows
- conditionally sum fields
- sum either of two columns
- sum rows and get a "totals" row
- sum values in discrete ranges
- get cumulative sums within groups
- sum elapsed hours by task and date
- select the max of sums
- calculate the difference between two timestamps
- calculate a percentage for a group
- calculate a total on INSERT
- calculate fractions
- calculate an average over years
- perform calculations on two rows in the same table
- perform calculations using a derived table
| Chapter 6: Working with times and dates | Return to top |
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:
- perform data calculations on dates stored as CHAR fields
- get a DB2 date in Julian format
- group by only the date part of a datetime column
- group output by week
- get dates from the past month
- get the first and last date of the current month
- get the max value from any month
- count number of rows by month
- figure out what day of the week a certain date is
- sort by day name
- get all dates between two dates
- count days between two dates and group by month
- count days based on anniverary year
- calculate the weeks between two dates
- calculate the duration of an event based on start and end times
- determine when a number increased over a given percentage
- get the date closest to another date
- get all rows with consecutive dates
- get rows with info for three consecutive days
- get the latest row from each group
- get the latest two rows from each group
- get all months, even if they are missing
- calculate an end date based on a start date
- total by yearly quarters
- get crosstab totals by month
| Chapter 7: Performance issues | Return to top |
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:
- how normalization affects query simplicity
- predict query performance on larger data sets
- when to use a cursor
- cursor, application code or SQL?
- table JOIN sequence
- an efficient selection of "all" rows
- why SELECT * is bad
- optimizing COUNT
- an IN list or OR predicates?
- subquery or join?
- One table or two tables with a join?
- OUTER JOIN or NOT EXISTS subquery?
- NOT EXISTS correlated subquery or OUTER JOIN?
- INNER JOIN or WHERE EXISTS or WHERE ... IN?
- OR or UNION?
| More Learning Guides | Return to top |
- 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.
