FIIT PDT session 5 https://github.com/fiit-pdt/hands-on

pdt-session-5-exercise-1.md

Exercise 1

Write a recursive query which returns a number and its factorial for all numbers up to 10. ☕

   ------------
   | 0  | 1   |
   | 1  | 1   |
   | 2  | 2   |
   | 3  | 6   |
   | 4  | 24  |
   | 5  | 120 |
   ...

Solution

with recursive factorial(n, fact) as (
    select 0, 1
    union
    select n+1, (n+1)*fact from factorial where n < 10
)
select * from factorial;

pdt-session-5-exercise-2.md

Exercise 2

Write a recursive query which returns a number and the number in Fibonacci sequence at that position for the first 20 Fibonacci numbers. ☕☕

   ------------
   | 1  | 1   |
   | 2  | 2   |
   | 3  | 3   |
   | 4  | 5   |
   | 5  | 8   |
   | 6  | 13  |
   | 7  | 21  |
   ...

Solution

with recursive fibonacci(n, fibo1, fibo2) as (
    select 1, 1, 1
    union
    select n+1, fibo2, fibo1+fibo2 from fibonacci where n < 20
)
select n, fibo2 from fibonacci;

pdt-session-5-exercise-3.md

Exercise 3

Table product_parts contains products and product parts which are needed to build them. A product part may be used to assemble another product part or product, this is stored in the part_of_id column. When this column contains NULL it means that it is the final product. List all parts and their components that are needed to build a 'chair'. ☕☕

    ------------
    "armrest"
    "metal leg"
    "metal rod"
    "cushions"
    "red dye"
    "cotton"
    ------------

Solution

with recursive parts(p_id, p_name) as (
    select id, name from product_parts where part_of_id in
    (select id from product_parts where name = 'chair')
    
    union

    select pp.id, pp.name from parts
    join product_parts pp on pp.part_of_id = parts.p_id
)
select p_name from parts;

pdt-session-5-exercise-4.md

Exercise 4

Which one of all the parts that are used to build a 'chair' has longest shipping time? ☕☕

    ------------------------------
     name         | shipping_time
    ------------------------------
     metal rod    | 10
    ------------------------------    

Solution

with recursive parts(p_id, p_name, p_shipping_time) as (
    select id, name, shipping_time from product_parts where part_of_id in
    (select id from product_parts where name = 'chair')
    
    union

    select pp.id, pp.name, pp.shipping_time from parts
    join product_parts pp on pp.part_of_id = parts.p_id
)
select p_name, p_shipping_time from parts
order by p_shipping_time desc limit 1;

pdt-session5.sql

-- Vypis cisla od nula do 100
-- kostra pre kazdy priklad
with recursive sum() as (
	select
	union
	select
)
select * from sum

-- riesenie priklady, n je VYSTUP funkcie, nie vstup
with recursive sum(n) as (
	-- inital term
	select 1

	union	-- toto je len syntax, ktora oddeluje inital term od recursive

	-- recursive term
	select n+1 from sum where n < 100
)
select * from sum


-- faktorial cisel po 10
with recursive faktorial(n, fakt) as (
	select 0, 1

	union

	select n+1, (n+1)*fakt from faktorial where n < 10
)
select * from faktorial

-- autobus zo zastavky "Nad luckami"
with recursive path(hop, line, start_stop_id, end_stop_id, start_name, end_name, route, transfers) as (
	select 1, c.line, c.start_stop_id, c.end_stop_id, ss.name, es.name, array[ss.name, es.name], array[c.line]
	from connections c
	join stops ss on ss.id = c.start_stop_id
	join stops es on es.id = c.end_stop_id
	where ss.name = 'Nad lúčkami'

	union

	select p.hop + 1, c.line, c.start_stop_id, c.end_stop_id, ss.name, es.name, p.route || es.name,
			case when c.line = any(p.transfers)
				then p.transfers
				else p.transfers || c.line
			end
	from path p
	join connections c on p.end_stop_id = c.start_stop_id
	join stops ss on ss.id = c.start_stop_id
	join stops es on es.id = c.end_stop_id
	
	where not es.name = any(p.route)
	and p.line = c.line or not c.line = any(p.transfers)
	and ss.name != 'Zochova'
	and array_length(p.transfers, 1) < 4
	and c.line not like 'N%'
)
select * from path p where p.end_name = 'Zochova'
order by array_length(p.transfers, 1)
limit 100



-------------------------------------------
with recursive nabytok(p_id, part) as (
	select id, name from product_parts where part_of_id in 
	(select id from product_parts where name = 'chair')

	union

	select p.id, p.name from nabytok n 
	join product_parts p on p.part_of_id = n.p_id 
)
select part from nabytok




with recursive nabytok(p_id, part, st) as (
	select id, name, shipping_time as st from product_parts where part_of_id in 
	(select id from product_parts where name = 'chair')

	union

	select p.id, p.name, p.shipping_time as st from nabytok n 
	join product_parts p on p.part_of_id = n.p_id 
)
select part, st from nabytok
order by st desc
limit 1





select (with recursive nabytok(p_id, part, st) as (
	select id, name, shipping_time as st from product_parts where part_of_id in 
	(select id from product_parts where name = 'chair')

	union

	select p.id, p.name, p.shipping_time as st from nabytok n 
	join product_parts p on p.part_of_id = n.p_id 
	
)
select sum(st) from nabytok
where p_id not in (select a.part_of_id from product_parts a where a.part_of_id is not NULL)
)