Views, CTEs & Recursive Queries

7. Practice Problems

16 problems on views, simple CTEs, chained CTEs, and recursion.

1. Create a view top_students with students having GPA >= 3.8, then query it.
   GPA >= 3.8 student-দের একটি view বানিয়ে query করুন।
   ✨ Show Answer

   a1.sql

   CREATE VIEW top_students AS
   SELECT id,name,gpa FROM students WHERE gpa>=3.8;
   SELECT * FROM top_students ORDER BY gpa DESC;

   Run Copy Open in SQLite Online

2. Drop the view from problem 1.
   আগের view-টি drop করুন।
   ✨ Show Answer

   a2.sql

   DROP VIEW top_students;
   SELECT name FROM sqlite_master WHERE type='view';

   Run Copy Open in SQLite Online

3. Use a CTE to compute total spend per customer, then list those above 500 BDT.
   CTE দিয়ে customer-wise total ও ৫০০-এর বেশি ফিল্টার।
   ✨ Show Answer

   a3.sql

   WITH spend AS (
       SELECT cid,SUM(amount) AS tot FROM orders GROUP BY cid
   )
   SELECT c.name,s.tot
   FROM spend s
   JOIN customers c ON c.id=s.cid
   WHERE s.tot>500;

   Run Copy Open in SQLite Online

4. Generate the integers 1 to 20 with a recursive CTE.
   Recursive CTE-তে ১ থেকে ২০ পর্যন্ত সংখ্যা তৈরি করুন।
   ✨ Show Answer

   a4.sql

   WITH RECURSIVE n(i) AS (
     SELECT 1
     UNION ALL
     SELECT i+1 FROM n WHERE i<20
   )
   SELECT i FROM n;

   Run Copy Open in SQLite Online

5. Generate every date from 2025-01-01 to 2025-01-10 using recursion.
   2025-01-01 থেকে 2025-01-10 পর্যন্ত প্রতিটি তারিখ।
   ✨ Show Answer

   a5.sql

   WITH RECURSIVE d(day) AS (
     SELECT date('2025-01-01')
     UNION ALL
     SELECT date(day,'+1 day') FROM d WHERE day < date('2025-01-10')
   )
   SELECT day FROM d;

   Run Copy Open in SQLite Online

6. List every employee's chain of command up to the CEO using a recursive CTE.
   প্রত্যেক employee-এর CEO পর্যন্ত chain তৈরি করুন।
   ✨ Show Answer

   a6.sql

   WITH RECURSIVE chain(id,name,path) AS (
     SELECT id,name,name FROM employees
     UNION ALL
     SELECT c.id,c.name,c.path||' -> '||m.name
     FROM chain c
     JOIN employees e ON e.id=c.id
     JOIN employees m ON m.id=e.manager_id
   )
   SELECT name,path FROM chain
   WHERE path LIKE '%Karim'
   ORDER BY name;

   Run Copy Open in SQLite Online

7. Walk the category tree: list every leaf category along with its full path.
   Category tree-এ প্রতিটি leaf-এর সম্পূর্ণ path।
   ✨ Show Answer

   a7.sql

   WITH RECURSIVE t(id,name,label) AS (
     SELECT id,name,name FROM category WHERE parent IS NULL
     UNION ALL
     SELECT c.id,c.name,t.label||'/'||c.name
     FROM category c JOIN t ON c.parent=t.id
   )
   SELECT label FROM t
   WHERE NOT EXISTS (SELECT 1 FROM category WHERE parent=t.id)
   ORDER BY label;

   Run Copy Open in SQLite Online

8. Compute Fibonacci numbers F(1)..F(15) with a recursive CTE.
   Recursive CTE দিয়ে Fibonacci F(1) থেকে F(15)।
   ✨ Show Answer

   a8.sql

   WITH RECURSIVE fib(n,a,b) AS (
     SELECT 1,0,1
     UNION ALL
     SELECT n+1,b,a+b FROM fib WHERE n<15
   )
   SELECT n,b AS fib FROM fib;

   Run Copy Open in SQLite Online

9. Use chained CTEs: monthly totals → yearly totals from orders.
   Chained CTE: মাসিক → বার্ষিক total।
   ✨ Show Answer

   a9.sql

   WITH
   monthly AS (
     SELECT substr(ts,1,7) AS ym, SUM(amount) AS m FROM orders GROUP BY 1
   ),
   yearly AS (
     SELECT substr(ym,1,4) AS y, SUM(m) AS total FROM monthly GROUP BY 1
   )
   SELECT * FROM yearly ORDER BY y;

   Run Copy Open in SQLite Online

10. Create a view that hides salary but shows everything else of employees.
    salary বাদ দিয়ে employees-এর বাকি সব কিছু দেখায় — এমন view।
    ✨ Show Answer

    a10.sql

    CREATE VIEW employees_public AS
    SELECT id,name,dept FROM employees;
    SELECT * FROM employees_public;

    Run Copy Open in SQLite Online

11. Find every descendant (direct or indirect) of category id = 1 with a recursive CTE.
    id=1 category-র সব descendant।
    ✨ Show Answer

    a11.sql

    WITH RECURSIVE desc(id,name) AS (
      SELECT id,name FROM category WHERE parent=1
      UNION ALL
      SELECT c.id,c.name
      FROM category c JOIN desc d ON c.parent=d.id
    )
    SELECT * FROM desc ORDER BY id;

    Run Copy Open in SQLite Online

12. In one sentence, what is the difference between a view and a CTE?
    View ও CTE-এর মূল পার্থক্য কী?
    ✨ Show Answer

    Answer: A view is a saved query that persists in the database catalog and is reusable across statements; a CTE is a named subquery that lives only inside the single statement that defines it.

    View database-এ সংরক্ষিত — যেকোনো query থেকে ডাকা যায়। CTE শুধু সেই একটি statement-এর ভেতরেই থাকে।

13. Use a CTE to identify customers whose biggest order exceeds 2× their average.
    CTE দিয়ে — যেসব customer-এর সর্বোচ্চ order তাদের গড়ের ২ গুণ ছাড়িয়েছে।
    ✨ Show Answer

    a13.sql

    WITH stats AS (
      SELECT cid,AVG(amount) AS avg_a,MAX(amount) AS max_a
      FROM orders GROUP BY cid
    )
    SELECT * FROM stats WHERE max_a > 2*avg_a;

    Run Copy Open in SQLite Online

14. Generate the multiplication table for 7 (1×7 to 10×7) with recursion.
    ৭-এর নামতা ১ থেকে ১০ — recursive CTE-তে।
    ✨ Show Answer

    a14.sql

    WITH RECURSIVE t(i) AS (
      SELECT 1 UNION ALL SELECT i+1 FROM t WHERE i<10
    )
    SELECT i,i*7 AS result FROM t;

    Run Copy Open in SQLite Online

15. Why might a recursive CTE without a termination condition cause an infinite loop?
    termination না থাকলে recursive CTE-তে infinite loop কেন হয়?
    ✨ Show Answer

    Answer: The engine keeps applying the recursive step using the previous iteration's rows. If the step always produces at least one new row (or the data has a cycle and you don't track visited rows), the iteration never empties — so the engine keeps adding rows until memory or a built-in safety limit aborts it.

    প্রতিটি iteration-এ নতুন row আসতে থাকলে, বা data-তে cycle থাকলে, পুনরাবৃত্তি বন্ধ হয় না — memory শেষ না হওয়া পর্যন্ত চলতে থাকে।

16. Build an org chart view org_with_level using a recursive CTE wrapped in a view.
    Recursive CTE-কে view-তে মুড়ে org_with_level বানান।
    ✨ Show Answer

    a16.sql

    CREATE VIEW org_with_level AS
    WITH RECURSIVE o(id,name,level) AS (
      SELECT id,name,0 FROM employees WHERE manager_id IS NULL
      UNION ALL
      SELECT e.id,e.name,o.level+1
      FROM employees e JOIN o ON e.manager_id=o.id
    )
    SELECT * FROM o;
    SELECT * FROM org_with_level ORDER BY level,name;

    Run Copy Open in SQLite Online