What are the key factors of the SQL language which an individual MUST grasp in order to become a successful SQL...
Excellent question, because it's difficult to come up with a wrong answer. ?
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 formThis 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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.