Basic FETCH Examples (1–20)
-- 1. Get first 5 employees
SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;
-- 2. Get first 10 employees sorted by salary
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY;
-- 3. Skip 10 and fetch next 5
SELECT * FROM employees OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
-- 4. Get top 1 highest salary
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 1 ROW ONLY;
-- 5. Get top 3 departments by budget
SELECT * FROM departments ORDER BY budget DESC FETCH FIRST 3 ROWS ONLY;
-- 6. Get last 5 employees (requires reverse order)
SELECT * FROM employees ORDER BY hire_date DESC FETCH FIRST 5 ROWS ONLY;
-- 7. Get next 5 employees after skipping 20
SELECT * FROM employees OFFSET 20 ROWS FETCH NEXT 5 ROWS ONLY;
-- 8. Fetch 10 most recent hires
SELECT * FROM employees ORDER BY hire_date DESC FETCH FIRST 10 ROWS ONLY;
-- 9. First 5 employees with salary > 5000
SELECT * FROM employees WHERE salary > 5000 FETCH FIRST 5 ROWS ONLY;
-- 10. Fetch 3 jobs ordered by title
SELECT * FROM jobs ORDER BY job_title FETCH FIRST 3 ROWS ONLY;
-- 11. Employees in department 10, top 5 earners
SELECT * FROM employees WHERE department_id = 10
ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY;
-- 12. Skip 50 rows, fetch next 25
SELECT * FROM employees OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
-- 13. Top 5 longest-serving employees
SELECT * FROM employees ORDER BY hire_date ASC FETCH FIRST 5 ROWS ONLY;
-- 14. Top 3 job titles alphabetically
SELECT DISTINCT job_title FROM employees ORDER BY job_title FETCH FIRST 3 ROWS ONLY;
-- 15. Fetch only 1 row from a large table
SELECT * FROM large_table FETCH FIRST 1 ROW ONLY;
-- 16. Top 5 orders by total value
SELECT * FROM orders ORDER BY total_amount DESC FETCH FIRST 5 ROWS ONLY;
-- 17. Fetch 10 random employees
SELECT * FROM (
SELECT * FROM employees ORDER BY DBMS_RANDOM.VALUE
) WHERE ROWNUM <= 10;
-- 18. First 5 distinct departments
SELECT DISTINCT department_id FROM employees FETCH FIRST 5 ROWS ONLY;
-- 19. Top 10 employees by salary in department 20
SELECT * FROM employees WHERE department_id = 20
ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY;
-- 20. Employees hired after 2020, fetch first 10
SELECT * FROM employees WHERE hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD')
ORDER BY hire_date FETCH FIRST 10 ROWS ONLY;
Intermediate FETCH Queries (21–60)
-- 21. Top 5 sales reps by revenue
SELECT employee_id, SUM(sales_amount) AS total_sales
FROM sales GROUP BY employee_id
ORDER BY total_sales DESC FETCH FIRST 5 ROWS ONLY;
-- 22. Fetch 10 employees with NULL commission
SELECT * FROM employees WHERE commission_pct IS NULL FETCH FIRST 10 ROWS ONLY;
-- 23. Top 3 managers with most employees
SELECT manager_id, COUNT(*) AS team_size
FROM employees GROUP BY manager_id
ORDER BY team_size DESC FETCH FIRST 3 ROWS ONLY;
-- 24. Fetch first 10 rows using subquery
SELECT * FROM (
SELECT * FROM employees ORDER BY salary DESC
) FETCH FIRST 10 ROWS ONLY;
-- 25. Pagination example: page 3 with 10 rows per page
SELECT * FROM employees OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- 26. Top 5 departments by avg salary
SELECT department_id, AVG(salary) avg_sal
FROM employees GROUP BY department_id
ORDER BY avg_sal DESC FETCH FIRST 5 ROWS ONLY;
-- 27. Top 10 cities by customer count
SELECT city, COUNT(*) AS customer_count
FROM customers GROUP BY city
ORDER BY customer_count DESC FETCH FIRST 10 ROWS ONLY;
-- 28. Top 5 products by rating
SELECT * FROM products ORDER BY rating DESC FETCH FIRST 5 ROWS ONLY;
-- 29. First 3 employees per job (using window function)
SELECT * FROM (
SELECT e.*, ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY salary DESC) rn
FROM employees e
) WHERE rn <= 3;
-- 30. Top 3 departments by total salary expense
SELECT department_id, SUM(salary) total_salary
FROM employees GROUP BY department_id
ORDER BY total_salary DESC FETCH FIRST 3 ROWS ONLY;
-- 31. Employees in top 10 salaries
SELECT * FROM (
SELECT * FROM employees ORDER BY salary DESC
) FETCH FIRST 10 ROWS ONLY;
-- 32. Fetch 10 youngest employees
SELECT * FROM employees ORDER BY hire_date DESC FETCH FIRST 10 ROWS ONLY;
-- 33. Top 5 jobs by number of employees
SELECT job_id, COUNT(*) cnt FROM employees GROUP BY job_id
ORDER BY cnt DESC FETCH FIRST 5 ROWS ONLY;
-- 34. Top 1 employee per department by salary
SELECT * FROM (
SELECT e.*, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) rn
FROM employees e
) WHERE rn = 1;
-- 35. Employees with 10th to 20th highest salary
SELECT * FROM (
SELECT * FROM employees ORDER BY salary DESC
) OFFSET 9 ROWS FETCH NEXT 11 ROWS ONLY;
-- 36. Top 10 selling products this month
SELECT product_id, SUM(quantity) AS total_sold
FROM sales WHERE sale_date >= TRUNC(SYSDATE, 'MM')
GROUP BY product_id
ORDER BY total_sold DESC FETCH FIRST 10 ROWS ONLY;
-- 37. Top 3 countries by revenue
SELECT country, SUM(order_total) total_rev
FROM orders GROUP BY country
ORDER BY total_rev DESC FETCH FIRST 3 ROWS ONLY;
-- 38. Top 5 active customers
SELECT customer_id, COUNT(*) AS orders
FROM orders GROUP BY customer_id
ORDER BY orders DESC FETCH FIRST 5 ROWS ONLY;
-- 39. Fetch first row in each job group by salary
SELECT * FROM (
SELECT e.*, RANK() OVER (PARTITION BY job_id ORDER BY salary DESC) rnk
FROM employees e
) WHERE rnk = 1;
-- 40. Latest 5 orders
SELECT * FROM orders ORDER BY order_date DESC FETCH FIRST 5 ROWS ONLY;
-- 41. Skip 5 rows in subquery
SELECT * FROM (
SELECT * FROM employees ORDER BY salary DESC
) OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
-- 42. Fetch top 3 states by number of customers
SELECT state, COUNT(*) cnt FROM customers GROUP BY state
ORDER BY cnt DESC FETCH FIRST 3 ROWS ONLY;
-- 43. Pagination: Page 2, 20 rows per page
SELECT * FROM employees OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;
-- 44. Top 10 employees by years of service
SELECT * FROM employees ORDER BY SYSDATE - hire_date DESC FETCH FIRST 10 ROWS ONLY;
-- 45. Top 5 product categories by revenue
SELECT category, SUM(price * quantity) AS revenue
FROM sales GROUP BY category
ORDER BY revenue DESC FETCH FIRST 5 ROWS ONLY;
-- 46. Skip first 100, fetch 50
SELECT * FROM employees OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY;
-- 47. Top 3 projects by duration
SELECT * FROM projects ORDER BY end_date - start_date DESC FETCH FIRST 3 ROWS ONLY;
-- 48. First 10 distinct regions from customers
SELECT DISTINCT region FROM customers FETCH FIRST 10 ROWS ONLY;
-- 49. Top 10 customers by lifetime spend
SELECT customer_id, SUM(order_total) AS lifetime_spend
FROM orders GROUP BY customer_id
ORDER BY lifetime_spend DESC FETCH FIRST 10 ROWS ONLY;
-- 50. Latest login per user
SELECT * FROM (
SELECT user_id, login_time,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time DESC) AS rn
FROM user_logins
) WHERE rn = 1;
No comments:
Post a Comment