SQL · Theory

Functions

Theory

307/439

Functions

A function returns a value in a SELECT: COUNT, AVG, CONCAT, NOW(). Aggregate functions squash rows (need GROUP BY for per-group). Scalar functions run per row (UPPER(name)).

SELECT CONCAT(name, ' ', roll), UPPER(city), NOW() FROM students; You can nest: ROUND(AVG(marks), 1).

Trap — putting UPPER(name) in WHERE on a huge table (index on name may not apply). Trap: COUNT(col) vs COUNT(*).

On the example next to this theory: Functions: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

Per-row: UPPER(name). Aggregate: AVG(marks) with GROUP BY.

Functions — sample query

-- Functions
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Functions: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefFunction returns a value. Aggregate vs per-row.
RuleCOUNT(*) counts rows. COUNT(marks) skips NULL marks.
TrapFunction on column in WHERE killing the index.

-- Functions CREATE TABLE demo ( id INTEGER PRIMARY KEY, label TEXT NOT NULL, qty INTEGER DEFAULT 0 ); INSERT INTO demo (id, label, qty) VALUES (1, 'alpha', 2), (2, 'beta', 5); SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Functions

Functions — sample query

Short notes

Questions

Functions

Functions — sample query

Short notes

Questions