use sample_recursive_db;
show tables;
select * from sales;
select product,
SUM(CASE WHEN region='North' THEN amount_sold END) as 'North',
SUM(CASE WHEN region='South' THEN amount_sold END) as 'South',
SUM(CASE WHEN region='East' THEN amount_sold END) as 'East',
SUM(CASE WHEN region='West' THEN amount_sold END) as 'West'
from sales group by product ;
use HR;
select * from employees;
with recursive mgr_hier as (
select employee_id, first_name, manager_id from employees where employee_id=109
UNION ALL
select e.employee_id, e.first_name, e.manager_id from mgr_hier mh join
employees e on mh.manager_id = e.employee_id
)
select employee_id, first_name, manager_id from mgr_hier;
use RTE;
with recursive power_of_2 as (
select 1 as power
UNION ALL
select power * 2 from power_of_2 where power < 8
)
select * From power_of_2;
select * From power_of_2;
WITH RECURSIVE factorial AS
(
SELECT 1 as n, 1 as next
UNION ALL
SELECT n+1, (n+1) * next FROM factorial WHERE n<5
)
SELECT * FROM factorial;
WITH RECURSIVE summation AS
(
SELECT 1 as n, 1 as next
UNION ALL
SELECT n+1, (n+1) + next FROM summation WHERE n<6
)
SELECT * FROM summation;
with recursive fab_of_n as (
select 0 as n, 1 as next
UNION ALL
select next , n+next from fab_of_n where n < 5
)
select * from fab_of_n;
