SQL Session Notes – Aggregate & Analytical Functions + Class Logistics

Class Logistics & Administrative Notes

• Attendance is Critical
  • Two consecutive absences ➜ automated rejection e-mail.
  • Always verify your name on the trainer’s shared attendance screen.
  • If you must miss class, keep Zoom logged-in on mobile or inform the trainer in advance.
• Joining the Class
  • Access via Tech Academy portal ➜ Calendar ➜ "Join"; direct Zoom links are NOT guaranteed.
  • If placed in the wrong batch, immediately contact the coordinator (e.g., Ramakrishna) and the trainer.
• Technical Issues Handling
  • Camera ON may freeze the portal; if asked about cameras, explain the freeze issue and follow trainer’s current instruction.
  • Recordings: locate the “View Recording” button next to the completed class inside Tech Academy (not in Otter).
• Communication Language
  • Trainer may switch briefly to Telugu for clarity with individual students.

Training Road-Map

• Phase 1: SQL & PL/SQL – 10 days (current module).
• Phase 2: Oracle Apps or another stream – 25 days (upcoming; assignment still pending).
• Future sessions (after SQL): multi-row functions, JOINs, sub-queries, analytical functions.

Assessment & Assignment Details

• Assignments (will appear under LXP → “My Learning” → “Advance in Learning”).
  • Expect upload of screenshots/videos as proof of completion.
  • Not yet released (expected by Day 3).
• Assessments
  1. MCQ Test – Monitored (camera & screen recording).
  2. Use-Case/Practical – Not monitored; group collaboration allowed via separate Teams/Zoom link.
• Milestone
  • Duration ≈ 2–3 hours.
  • Contains both MCQ + Use-Case parts (if implemented as a single milestone).

Practice Guidance

• Self-practice encouraged during afternoon slot (post-lunch, e.g., 02:00–02:30 PM onwards).
• Trainer willing to take extra sessions on request, but concepts already covered must be clear first.

SQL Content Covered Today

1. Aggregate (Single-Row) vs Multi-Row (Group) Functions

• Aggregate Functions: SUM, COUNT, AVG, MAX, MIN.
  • Operate on a set of rows and return one value.
  • Example: $\text{SELECT SUM(salary) FROM employees;}$ returns one total.
• Multi-Row (Group) Requirement: When aggregation must be done per category (e.g., per department).
  • Use $GROUP\ BY$.
  • Example:
    
    SELECT departmentid, SUM(salary) AS depttotal
    FROM employees
    GROUP BY department_id;
    
  • Output shows one row per department with aggregated salary.

2. GROUP BY Rules

• Every non-aggregated column in the SELECT list must appear in GROUP BY.
• Columns present in GROUP BY may be omitted from SELECT.
• Violating rule ➜ ORA-00979: not a GROUP BY expression.
• Example with NULL groups – departments not assigned (department_id IS NULL) still form a separate group; sums appear under the NULL key.

3. Filtering Rows vs Filtering Groups

• Row filter: WHERE clause.
  • Applies before grouping; examines individual row values.
  • Example: $\text{WHERE salary > 1000}$.
• Group filter: HAVING clause.
  • Applies after aggregation; examines aggregated values.
  • Example: get departments whose total salary > 50 k:
    
    SELECT departmentid, SUM(salary) AS depttotal
    FROM employees
    GROUP BY department_id
    HAVING SUM(salary) > 50000;
    

4. Logical Execution / Recommended Writing Order

1. SELECT (columns & aggregate functions)
2. FROM (tables or subqueries)
3. WHERE (row-level filter)
4. GROUP BY (create groups)
5. HAVING (group-level filter; optional)
6. ORDER BY (final sorting; optional)

5. Analytical Functions (Preview)

• Do not reduce number of rows; add extra computed columns.
• Examples introduced (full explanation next class):
  • RANK()
  • DENSE_RANK()
  • ROW_NUMBER()
• Basic syntax:
  
  SELECT employeeid, salary, RANK() OVER (ORDER BY salary DESC) AS salrank
  FROM employees
  WHERE department_id = 90;
  
• Purpose: provide ordering-based metrics (e.g., salary rank within dept).

Immediate Homework / Practice

• Re-implement every live query:
  1. Simple SUM, AVG, MAX, MIN on the entire table.
  2. Department-wise totals with GROUP BY.
  3. Mix multiple aggregates (e.g., count of employees per dept alongside total salary).
  4. Experiment with HAVING (e.g., departments with count > 3 or avg salary < 8 k).
• Try wrong combinations deliberately to observe error messages (reinforces GROUP BY rule).
• Prepare mentally for analytical functions: read docs on RANK, DENSE_RANK, ROW_NUMBER if possible.

Planned Topics for Next Session

• Multi-row (aggregate) analytical functions in detail.
• JOINs & Subqueries introduction.
• Hands-on examples of RANK, DENSE_RANK, ROW_NUMBER with partitions.

Miscellaneous Reminders

• Verify evening session camera requirement but prioritize portal stability.
• If assignment portal still empty by tomorrow, trainer will escalate to Nava(s)/support.
• Keep screenshots of every lab—you may need to upload them as evidence of completion.