SQL Tutorial for Beginners Part IV

SQL - Tips
• The scripting of the query should be clear

• Query should not be written in one line
select c.coname "Company",min(p.gsal) "Min",max(p.gsal) "MAX",avg(p.gsal) "AVG" from company c,placement p where c.cocode=p.cocode group by c.coname;

• No need for decode
select c.coname"Company",
min(decode(p.cocode,c.cocode,gsal))"Min",
max(decode(p.cocode,c.cocode,gsal))"max",
avg(decode(p.cocode,c.cocode,gsal))"Avg"
from placement p,company c
group by c.coname;

SQL – Optimization Tips
• Use only those table which are necessary to get result.
• Use ‘indexes’ to optimize retrieval.
• In your queries, don't return column data you don't need.
e.g. you should not use SELECT * to return all the columns from a table if you don't need all the data from each column
– Using SELECT * prevents the use of covered indexes, further potentially hurting query performance
• Avoid unnecessary use of group functions
• For single row selection criteria, use ‘where’ clause
• Use ‘having’ clause, for aggregate selection criteria
• Carefully evaluate whether your SELECT query needs the DISTINCT clause or not. Some developers automatically add this clause to every one of their SELECT statements, even when it is not necessary
• Avoid using LOWER or UPPER function, if you are sure about the case of data.
• Avoid use of functions wherever possible
e.g.
WHERE SUBSTRING(firstname,1,1) = 'm'
can be rewritten like this:
WHERE firstname like 'm%'
• Use BETWEEN clause (as it is more efficient) when you have a choice of using the IN or the BETWEEN clauses in Transact-SQL e.g.
SELECT customer_number, customer_name
FROM customer
WHERE customer_number BETWEEN 1000 and 1004
– Instead of
SELECT customer_number, customer_name
FROM customer
WHERE customer_number in (1000, 1001, 1002, 1003, 1004)

• Assuming there is a useful index on customer_number, the Query Optimizer can locate a range of numbers faster (using BETWEEN) than it can find a series of numbers using the IN clause
• If possible, avoid using the SUBSTRING function and instead use the LIKE condition for better performance
– Instead of
WHERE SUBSTRING(column_name,1,1) = 'b'
– Use
WHERE column_name LIKE 'b%'