Please note that this is one question divided into 4 parts. Exercise 25.4 Briefl

Question

Please note that this is one question divided into 4 parts.

Exercise 25.4 Briefly answer the following questions:

1. What is the difIerences between the WINDOW clause and the GROUP BY clause'?

2. Give an example query that cannot be expressed in SQL without the WINDOW clause but that can be expressed with the WINDOW clause.

3. What is the frame of a window in SQL:1999

4. Consider the following simple GROUP BY query.

SELECT T.year, SUM (S.sales)

FROM Sales 5, Times T

WHERE S.tilneid='T.timeid

GROUP BY T.y e a r

Can you write this query in SQL:1999 without using a GROUP BY clause? (Hint: Use the SQL:1999 WINDOW clause.)

Explanation / Answer

1.

Window clauses are more faster than the group by clause.

The group by clause fuctions work on the data fetched directly from the table.

Where as window clause works on the data result fetched by the query and carries the operations which ever we specify it to do.

But in group by clause you could see the datawith proper relation over the data,

But in window cluase since the function acts on the results of the data there may be

high chances that user may get confused sometimes by seeing the data, not that the datais wrong,

but it creates some confusion in understanding with the results.

In group by clause you can not use the columns in select which does not included in the group by cluase but in windows clause you can do this. And also there are many a functions are available in windows cluase which are called OLAP functions.

2.

As I told "In group by clause you can not use the columns in select which does not included in the group by cluase but in windows clause you can do this." , such situactions where

you feel that you have to use the select columns which are not in the group by then you must have to choose the window clause.

Other than that you have differnt options or functions available in OLAP functions.

For example: using group by you can not run the ranks grouped by department wise,

where are by using windows you can.

select empno,deptno,rownum as rank

from emp;

select empno,deptno,dense_rank()over(partition by deptno order by empno,deptno)

from emp;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.