support

Completing the result set step by step

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 A [...]

Grouping the subtable

List the names of the employees and count the cities where each employee has worked for over one year. SELECT name,count(*) city_total FROM (SELECT employee.name name,resume.city city FROM employee,resume WHERE employee.name=resume.name GROUP BY name,city HAVING sum(work_days)>=365) GROUP BY name Process the subtable through the multi-table join. The grouped result set has the same number of [...]

Querying the subtable

Find out the first companies that the employees worked for. Multi-table join SELECT name,company first_company FROM (SELECT employee.name name,resume.company company, row_number() OVER(PARTITION BY resume.name ORDER BY resume.start_date) work_order FROM employee,resume WHERE employee.name=resume.name) WHERE work_order=1 The subquery SELECT name, (SELECT company FROM resume WHERE name=A.name AND start_date=(SELECT min(start_date) FROM resume WHERE name=A.name)) first_company FROM employee A [...]

Reference of the records associated by foreign key

Find out the male employees under the female managers. Multi-table join: SELECT A.* FROM employee A,dept B,employee C WHERE A.dept=B.dept AND B.manager=C.name AND A.gender=’male’ AND C.gender=’female’ 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 a record referencing mechanism, [...]

Inter-row calculations on grouping subsets

Find out the stocks that reached the daily trading limit (price 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 code ORDER BY trade_date)>=0.1 [...]

Sorting and filtering the grouping subsets

Find out the student whose score ranks 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 grouping window function can conveniently rank the students in each subject, [...]

Selecting rows from grouping subsets

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 the [...]

Selecting a grouping subset

Find out the employees born on the same days. 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 grouping subsets, not the summary value. As SQL cannot store the grouping result, the user will need to change the [...]

Sort the groupings

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 cannot [...]

Filter the groups

Count the departments in which the average age of the employees is over 30, or that have more than three employees who are 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 intention of using GROUP [...]