Derived Table — SQL Technical Interview Questions & Answers

Derived Table

Derived Table

  • A subquery is a SELECT inside another SQL. WHERE marks = (SELECT MAX(marks) FROM students); the topper. The inner query runs as a value or as a table.
  • Scalar subquery returns one value. IN (SELECT roll FROM …) returns a list. FROM (SELECT …) AS t is a derived table and needs an alias.
  • Correlated subquery uses the outer row (WHERE m.roll = s.roll) and can be slow. A JOIN is often clearer and faster.
  • Trap: subquery that returns two rows where one value was expected. Trap: correlated subquery in a huge table without an index.
  • On the example next to this theory: Derived Table: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Technical Interview Tip: WHERE marks = (SELECT MAX(marks) FROM students);

Code Examples for Derived Table

Derived Table — sample query

-- Derived Table
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;

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

How to Explain Derived Table to an Interviewer

Give a subquery example.

Topper: marks = (SELECT MAX(marks) FROM students)

JOIN vs subquery?

JOIN often clearer/faster Subquery fine for one value