Functions — SQL Technical Interview Questions & Answers

Functions

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.

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

Code Examples for Functions

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.

How to Explain Functions to an Interviewer

COUNT(*) vs COUNT(marks)?

* all rows marks skips NULL