Python Developer interview prep

Short answer: SELECT retrieves rows from tables. The logical execution order does NOT match the written order: first FROM, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.

In depth:

Written (syntactic) order:

SELECT   DISTINCT col1, agg(col2)
FROM     t
WHERE    cond
GROUP BY col1
HAVING   agg_cond
ORDER BY col1
LIMIT    10 OFFSET 20;

Logical execution order:

1. FROM / JOIN      -- which tables, how to join
2. WHERE            -- row filter BEFORE grouping
3. GROUP BY         -- grouping
4. HAVING           -- group filter AFTER aggregation
5. SELECT           -- evaluate expressions, aliases
6. DISTINCT         -- remove duplicates
7. ORDER BY         -- sorting
8. LIMIT / OFFSET   -- slice

⚠️ Gotcha: an alias from SELECT cannot be used in WHERE (since WHERE runs before SELECT), but it can be used in ORDER BY and often in GROUP BY (depends on the DBMS). Example of the error:

SELECT salary * 12 AS annual FROM emp WHERE annual > 100000; -- ERROR
SELECT salary * 12 AS annual FROM emp ORDER BY annual;        -- OK

Short answer: HTTP (HyperText Transfer Protocol) is a text-based application-layer client-server protocol on top of TCP (in HTTP/3 — on top of QUIC/UDP). The client sends a request, the server returns a response; the connection holds no state between requests (stateless).

In depth:

A request consists of a start line (method + path + version), headers, and an optional body:

POST /api/v1/users HTTP/1.1
Host: api.example.com
Content-Type: application/json
Accept: application/json
Content-Length: 38

{"name": "Anna", "email": "a@ex.com"}

A response consists of a status line (version + code + reason phrase), headers, and a body:

HTTP/1.1 201 Created
Content-Type: application/json
Location: /api/v1/users/42

{"id": 42, "name": "Anna", "email": "a@ex.com"}

⚠️ Gotcha: "HTTP only works over TCP" is incorrect for HTTP/3, which uses QUIC over UDP. And the reason phrase ("OK", "Created") is purely informational — clients should rely on the numeric code, not the text.

Short answer: Big O describes how an algorithm's running time or memory usage grows as the input size n increases, dropping constants and lower-order terms. It's an upper bound on the growth rate.

In detail:

Big O answers the question "what happens when n becomes very large?". We're not interested in the exact number of operations, only the nature of the growth. That's why O(2n + 100) simplifies to O(n), and O(3n² + n) — to O(n²).

The main growth classes (from best to worst):

# O(1) — constant: doesn't depend on n
def first(arr):
    return arr[0] if arr else None

# O(log n) — logarithmic: each step halves the problem (binary search)
def binary_search(arr, target):
    lo, hi = 0, len(arr) - 1
    while lo <= hi:
        mid = (lo + hi) // 2
        if arr[mid] == target:
            return mid
        elif arr[mid] < target:
            lo = mid + 1
        else:
            hi = mid - 1
    return -1

# O(n) — linear: a single pass
def total(arr):
    s = 0
    for x in arr:        # n iterations
        s += x
    return s

# O(n log n) — efficient sorts (merge, quick, Timsort)
def sort_it(arr):
    return sorted(arr)

# O(n^2) — quadratic: a nested loop
def has_dup_naive(arr):
    for i in range(len(arr)):
        for j in range(i + 1, len(arr)):
            if arr[i] == arr[j]:
                return True
    return False

# O(2^n) — exponential: naive Fibonacci, enumerating subsets
def fib_naive(n):
    if n < 2:
        return n
    return fib_naive(n - 1) + fib_naive(n - 2)

A rough guide to growth at n = 1,000,000: O(1) — 1 operation, O(log n) — ~20, O(n) — a million, O(n log n) — ~20 million, O(n²) — a trillion (already too much), O(2^n) — infeasible even at n = 50.

⚠️ Gotcha: Big O is about asymptotics (behavior at large n), not about actual time. An O(n) algorithm can be slower than an O(n²) one on small data because of large constants. People also confuse: Big O (upper bound), Ω (lower bound), Θ (tight bound); in interviews "O" usually means Θ.

Short answer: Docker is a containerization platform that packages an application together with all its dependencies, libraries, and configuration into an isolated, portable image. It solves the "works on my machine, but not on the server" problem.

In depth:

The classic pain: a developer wrote code on their laptop (Python 3.11, a specific version of libpq, environment variables), but the server has a different OS version, doesn't have the other libraries, and the variables aren't set — the application crashes. Docker pins the whole environment into an image, and that image runs identically locally, in CI, and in production.

What Docker gives you:

• Reproducibility — the same image everywhere. The environment is described as code (a Dockerfile) and can be versioned in git.
• Isolation — containers don't interfere with each other (dependencies, ports, processes).
• Portability — the image runs on any machine with Docker, regardless of the host OS.
• Fast startup — a container comes up in seconds (unlike a VM).

# Build an image from the Dockerfile in the current directory
docker build -t myapp:1.0 .

# Run a container from the image, publish a port, in the background
docker run -d -p 8000:8000 --name myapp myapp:1.0

# List running containers
docker ps

# Get a shell inside a running container
docker exec -it myapp bash

# Container logs
docker logs -f myapp

⚠️ Gotcha: Docker doesn't "virtualize hardware" and doesn't run a full OS. Containers use the host's kernel. So a Linux image won't run natively on the Windows kernel — on Windows/macOS a lightweight Linux VM runs under the hood (via WSL2 or a hypervisor), and the containers run inside it.

Short answer: list is mutable, tuple is not. A tuple is hashable (if all its elements are hashable), uses less memory, and is created slightly faster. Use list for homogeneous, mutable collections and tuple for fixed, heterogeneous records.

In depth:

import sys
print(sys.getsizeof([1, 2, 3]))   # ~88 bytes
print(sys.getsizeof((1, 2, 3)))   # ~64 bytes

Why list is bigger: it keeps an over-allocation — it reserves space in advance for future appends to amortize the cost of growth to O(1). A tuple is fixed and needs no spare room.

When to choose which:

• tuple — when the count and meaning of the elements are fixed: coordinates (x, y), returning multiple values from a function, a dictionary key.
• list — when the collection grows/changes/gets sorted.

⚠️ Gotcha: "a tuple is faster" is true only for creating a literal and for access, not magically for everything. And remember nested mutable objects: a tuple guarantees the immutability of references, not of the objects they point to.