How SQL Helps You Land a Full Stack Developer Job Faster

# models.py - a user has many orders
from django.db import models

class Order(models.Model):
    user = models.ForeignKey("auth.User", on_delete=models.CASCADE)
    total = models.DecimalField(max_digits=10, decimal_places=2)
    created_at = models.DateTimeField(auto_now_add=True)

# views.py - latest 10 orders with the username
orders = (Order.objects
          .select_related("user")
          .order_by("-created_at")[:10])

print(orders.query)   # shows the SQL Django will run

-- Roughly what select_related() produces: one query with a JOIN
SELECT o.id, o.total, o.created_at, u.username
FROM orders_order AS o
INNER JOIN auth_user AS u ON u.id = o.user_id
ORDER BY o.created_at DESC
LIMIT 10;

-- Without select_related, accessing order.user in a loop
-- triggers one extra query per order: the "N+1" problem.

-- PostgreSQL
CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  name  TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL CHECK (price >= 0)
);

CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INT NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id   INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INT NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

INSERT INTO users (email, name) VALUES
  ('asha@example.com', 'Asha'),
  ('ravi@example.com', 'Ravi');

INSERT INTO products (name, price) VALUES
  ('Notebook', 120.00),
  ('Pen pack', 60.00),
  ('Backpack', 900.00);

INSERT INTO orders (user_id) VALUES (1), (1), (2);

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 2, 120.00),
  (1, 2, 1, 60.00),
  (2, 3, 1, 900.00),
  (3, 2, 3, 60.00);

-- Order totals per user
SELECT u.name,
       COUNT(DISTINCT o.id)                AS orders,
       SUM(oi.quantity * oi.unit_price)    AS total_spent
FROM users u
JOIN orders o       ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY u.name
ORDER BY total_spent DESC;

-- Users who have never ordered
SELECT u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Speed up "my orders" lookups
CREATE INDEX idx_orders_user_id ON orders (user_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

// npm install pg
const { Pool } = require("pg");
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

// SAFE: value passed separately as $1, never concatenated
async function findUserByEmail(email) {
  const { rows } = await pool.query(
    "SELECT id, name FROM users WHERE email = $1",
    [email]
  );
  return rows[0] || null;
}

// UNSAFE - never do this:
// pool.query("SELECT * FROM users WHERE email = '" + email + "'");

module.exports = { findUserByEmail };

DAILY SQL DRILLS (use the shop schema above)

1. List every order with the customer's name and order date.
2. Show the total value of each order.
3. Find the top 3 products by quantity sold.
4. Show users who spent more than 500 in total (HAVING).
5. List products that have never been ordered (LEFT JOIN).
6. Show the second page of products, 2 per page, by name.
7. Count orders per day for the last 7 days.
8. Rewrite one query using a CTE (WITH ... AS).

Rule: write it, run it, then explain it out loud.

-- Place an order atomically: all steps succeed or none do
BEGIN;

INSERT INTO orders (user_id) VALUES (2)
RETURNING id;            -- suppose this returns 4

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT 4, id, 1, price FROM products WHERE id = 1;

COMMIT;
-- If anything fails before COMMIT, run ROLLBACK;
-- and the half-created order disappears.

FULL STACK PORTFOLIO - SQL CHECKLIST

[ ] schema.sql committed, with keys and constraints
[ ] README has an ER diagram or table list
[ ] At least one JOIN-based page (e.g. order history)
[ ] At least one aggregate report (GROUP BY)
[ ] Pagination on a list endpoint
[ ] Index added and explained in README
[ ] All user input passed as query parameters
[ ] Migrations used (Django, Alembic, Prisma or similar)
[ ] Deployed with a managed database, credentials in env vars