Progressively complete the result set

To select two employees (one male and the other female) from each department as a pair to play games WITH A AS (SELECT name,dept, row_number() OVER(PARTITION BY dept ORDER BY 1) sequence_no FROM employee WHERE gender=’male’), B AS (SELECT name,dept, row_number() OVER(PARTITION BY dept ORDER BY 1) sequence_no FROM employee WHERE gender=’female’) SELECT name,dept FROM [...]

Group the subtables

To list the employee and count the cities WHERE the employee has worked over one year. SELECT name,count(*) city_total FROM (SELECT name, city FROM employee,resume WHERE GROUP BY name,city HAVING sum(work_days)>=365) GROUP BY name Process the subtable in the way as joining the multiple tables. The grouped result set has the same number [...]

Query the subtable

To find out the first company that the employee worked for. Use multi-table join SELECT name,company first_company FROM (SELECT name, company, row_number() OVER(PARTITION BY ORDER BY resume.start_date) work_order FROM employee,resume WHERE WHERE work_order=1 Use the subquery SELECT name, (SELECT company FROM resume WHERE AND start_date=(SELECT min(start_date) FROM resume WHERE first_company [...]

Reference of the record associated by foreign key

To find out the male employees under the female managers. Use multi-table join SELECT A.* FROM employee A,dept B,employee C WHERE A.dept=B.dept AND AND A.gender=’male’ AND C.gender=’female’ Use the subquery SELECT * FROM employee WHERE gender=’male’ and dept in (SELECT dept FROM dept_table WHERE manager IN (SELECT name FROM employee WHERE gender=’female’))     Lacking of [...]

Compute cross rows in group subset

To find out the stock that reached the upper level of the daily trading limit (gains ?10%) for three consecutive days. WITH A AS (SELECT code,trade_date, close_price/lag(close_price) OVER(PARTITION BY stock ORDER BY trade_date)-1 rising_range FROM stock_price), B AS (SELECT code, CASE WHEN rising_range>=0.1 AND lag(rising_range) OVER(PARTITION BY code ORDER BY trade_date)>=0.1 AND lag(rising_range,2) OVER(PARTITION BY [...]

Sort and filter the group subset

To find out the student whose score ranks in top 10 for all subjects. SELECT name FROM (SELECT name FROM (SELECT name, rank() OVER(PARTITION BY subject ORDER BY score DESC) rank FROM score_table) WHERE rank<=10) GROUP BY name HAVING count(*)=(SELECT count(DISTINCT subject) FROM score_table) Using the group window function can conveniently ranks the students in [...]

Select rows from the group subset

To select two employees from each department SELECT * FROM (SELECT employee.*, row_number() OVER(PARTITION BY dept ORDER BY 1) seq_no FROM employee) WHERE seq_no<=2 To reselect rows from the group subsets, the subquery is necessary for SQL to query from the source set again. A 1 =demo.query(“select * from employee”).group(dept)).conj(~.m([1,2])) esProc grouping will not disrupt [...]

Select a group subset

To find out the employee born on the same day SELECT * FROM employee WHERE to_char(birthday,’MMDD’) IN (SELECT to_char(birthday,’MMDD’) FROM employee GROUP BY to_char(birthday,’MMDD’) HAVING count(*)>1) Sometimes the purpose of grouping rows is to obtain the group subsets, not the summary, as SQL cannot store the result, the user will need to change the way [...]

Sort the groupings

To find out the department with the most employees and the department with the fewest employees SELECT dept FROM (SELECT dept, row_number() OVER (ORDER BY total_emp DESC) rank_ASC, row_number() OVER (ORDER BY total_emp ASC) rank_DESC FROM (SELECT dept, count(*) total_emp FROM employee GROUP BY dept)) WHERE rank_ASC=1 OR rank_DESC=1 ORDER BY rank_ASC The having clause [...]

Filter the groups

To count the department whose average age is over 30, or that has more than three employees over 40 years old. SELECT count(*) FROM (SELECT dept FROM employee GROUP BY dept HAVING avg(age)>30 OR sum( CASE WHEN age>40 THEN 1 ELSE 0 END )>3) The intension of using GROUP BY clause is to divide the [...]