Q
Problem solve Get help with specific problems with your technologies, process and projects.

Key factors for successful SQL

What are the key factors of the SQL language which an individual MUST grasp in order to become a successful SQL...

developer?

Excellent question, because it's difficult to come up with a wrong answer. ? ;o)

1. Think in sets of rows, not individual rows

This is a subtle distinction. "Who are the employees that have the lowest salary in their department?" is slightly different than "For each department, find the employees that have the lowest salary." The former makes us look at the problem in terms of which rows satisfy the requirement, while the latter diverts our attention to the procedure for finding them.

  select dept, employee, salary
    from payroll XX
   where salary = ( select min(salary)
                      from payroll
                     where dept = XX.dept )

You don't have to write any code to "loop through" the rows. Just describe which set of rows you want.

2. There is no "how"

Waste no time worrying about how efficient your SQL is. Concentrate on the sets you want your SQL to operate on. Which is better, a join or a subselect? Use whichever you are comfortable with, as long as it is semantically correct, i.e. operates on the right set of rows. For example, what is the average salary of employees on staff less than one year? You could do it like this --

     select avg(salary)
       from payroll
      where employeeid in
            ( select employeeid
                from employees
               where hiredate > current_date() - 1 year )

or like this --

     select avg(salary)
       from payroll 
 inner join employees 
         on employeeid
      where hiredate > current_date() - 1 year

Both queries give the same results.

3. Design to third normal form

This has actually nothing to do with the SQL language at all. Make sure your rowboat is watertight and you won't be constantly bailing. Design your tables to third normal form and your SQL becomes very, very simple.

Tip: Despite my advice in 2. above not to worry about how efficient your SQL is, it is still a very good idea to make sure your primary keys have indexes. Possibly your foreign keys as well.

For More Information

  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was last published in November 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close