Good thoughts on SQL:
- Often times there are multiple ways of getting the same answer in programming. This pertains to SQL also.
- Everyone needs to be stronger in SQL.
- Strong SQL skills greatly enhance one’s ability to do performance tuning of queries, programs, and applications.
We can eliminate duplicates in the following ways. I will give different ways how to eliminate duplicates in my next posts. There are some advantages and dis-advantages in the following methods. The method GROUP BY will takes less CPU time. The method DISTICT will take more CPU time (an internal sort is required before completing SQL).
Select * from samples.loan_account group by accno;
select distinct accno from samples.loan_account;
Complex SQL Query in DB2:
SELECT E1.EMPNO, E1.LASTNAME,E1.SALARY E1.DEPTNO, (SELECT AVG(E2.SALARY)FROM EMP E2WHERE E2.DEPTNO = E1.DEPTNO)AS DEPT_AVG_SALFROM EMP E1 ORDER BY E1.DEPTNO, E1.SALARY
Explanation: To add the average to the result, the subquery must be repeated as a scalar fullselect in the SELECT clause. Scalar fullselects were introduced in DB2 Version 8 and may be used as an expression anywhere in the statement provided they only return 1 column and 1 row.