SQL · Theory

SELECT Record

Theory

35/439

SELECT Record

SELECT name, marks FROM students WHERE marks < 40 ORDER BY marks; is the whole language in one line. SELECT = which columns. FROM = which table. WHERE = which rows. ORDER BY = sort.

SELECT * is fine at home. In interviews, list columns. DISTINCT removes duplicate rows in the result. LIMIT 10 is ‘first 10’ after sorting.

Functions — SELECT COUNT(*), AVG(marks) FROM students; aggregates. They collapse rows unless you GROUP BY.

Trap: WHERE after GROUP BY (illegal — use HAVING). Trap: SELECT extra columns not in GROUP BY (ONLY_FULL_GROUP_BY error).

On the example next to this theory — SELECT Record — sELECT projects columns; WHERE filters; ORDER BY sorts.

Diagram

FROM students
       │ WHERE marks < 40
       ▼
  matching rows
       │ SELECT name, marks
       ▼
  result table

Exam tip

SELECT name, marks FROM students WHERE marks < 40;

SELECT Record — sample query

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Eng', 70000),
  (2, 'Ravi', 'Eng', 65000),
  (3, 'Neha', 'HR', 50000);
SELECT name, salary FROM employees WHERE dept = 'Eng' ORDER BY salary DESC;

SELECT Record — sELECT projects columns; WHERE filters; ORDER BY sorts.

Short notes

DefSELECT columns FROM table WHERE rows.
RuleFROM → WHERE → SELECT in your head. ORDER BY / LIMIT last.
TrapSELECT * as the only habit. WHERE vs HAVING.

SQL · Theory

SELECT Record

← All stacks

Theory

35/439

SELECT Record

SELECT name, marks FROM students WHERE marks < 40 ORDER BY marks; is the whole language in one line. SELECT = which columns. FROM = which table. WHERE = which rows. ORDER BY = sort.

SELECT * is fine at home. In interviews, list columns. DISTINCT removes duplicate rows in the result. LIMIT 10 is ‘first 10’ after sorting.

Functions — SELECT COUNT(*), AVG(marks) FROM students; aggregates. They collapse rows unless you GROUP BY.

Trap: WHERE after GROUP BY (illegal — use HAVING). Trap: SELECT extra columns not in GROUP BY (ONLY_FULL_GROUP_BY error).

On the example next to this theory — SELECT Record — sELECT projects columns; WHERE filters; ORDER BY sorts.

Diagram

FROM students
       │ WHERE marks < 40
       ▼
  matching rows
       │ SELECT name, marks
       ▼
  result table

Exam tip

SELECT name, marks FROM students WHERE marks < 40;

SELECT Record — sample query

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Eng', 70000),
  (2, 'Ravi', 'Eng', 65000),
  (3, 'Neha', 'HR', 50000);
SELECT name, salary FROM employees WHERE dept = 'Eng' ORDER BY salary DESC;

SELECT Record — sELECT projects columns; WHERE filters; ORDER BY sorts.

Short notes

DefSELECT columns FROM table WHERE rows.
RuleFROM → WHERE → SELECT in your head. ORDER BY / LIMIT last.
TrapSELECT * as the only habit. WHERE vs HAVING.

SELECT Record

SELECT Record — sample query

Short notes

Questions

SELECT Record

SELECT Record — sample query

Short notes

Questions