CHEGG BACKED DEV

Backend development reference

Data persistence uses PostgreSQL. Raw SQL builds fluency for interviews and debugging; Knex adds migrations, seeds, and a query builder your Express services call. Learn both: SQL shows what the database actually runs; Knex shows how the app stays maintainable.

Raw SQL

Writing raw SQL strengthens mental models for what Knex generates. Practice CREATE, INSERT, SELECT, UPDATE, DELETE, and JOIN here—then implement the same operations with Knex in services.

5 topic(s) on this page.

How to create tables with SQL — Basic

SQL / Knex Raw SQL
Define schemas with CREATE TABLE for columns, types, and constraints.
Learn: Tables define your domain model in PostgreSQL. Choose types deliberately (TEXT vs VARCHAR, TIMESTAMPTZ for times). Primary keys and foreign keys document relationships and enable referential integrity.

Supplementary examples

Users and pastes tables

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username TEXT NOT NULL UNIQUE
);

CREATE TABLE pastes (
  paste_id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(user_id),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Course example

-- First, remove the table if it exists
DROP TABLE IF EXISTS customers;
-- Create the table anew
CREATE TABLE customers (
customer_id INTEGER primary key generated by default as identity,
customer_name varchar NOT NULL,
phone VARCHAR(30)
);
-- Insert some test data
-- using a multi-row insert statement here
INSERT INTO customers (customer_name, phone)
VALUES
('Sam', '555-1234'),
('Ham', '555-4321'),
('Ram', '555-5678');

Additional references & examples

How to insert data with SQL — Basic

SQL / Knex Raw SQL
Add rows with INSERT INTO for seeds, fixtures, or manual data setup.
Learn: INSERT adds rows; RETURNING is PostgreSQL’s way to get generated IDs and columns back in one round trip—handy for APIs that respond with the created record.

Supplementary examples

Insert with RETURNING

INSERT INTO pastes (user_id, name, tags)
VALUES (1, 'My paste', ARRAY['node', 'sql'])
RETURNING *;

Course example

TRUNCATE
employees,
departments,
projects,
employees_projects,
customers,
orders
RESTART IDENTITY;
-- Insert some data into the employees table
INSERT INTO employees
(first_name, last_name, phone, title, salary, hire_date)
VALUES
('Jane', 'Doe','5551234', 'CEO', 80000, '12/07/1999'),
('Wright', 'Palmer', '5554321', 'Manager', 60000, '01/06/2001'),
('Jim', 'Doe', '5555678', 'Accountant', 50000, '11/06/2015'),
('Toby', 'Bestley', '5558765', 'Associate', 35000, '09/07/2008') ,
('Meredith', 'Hartford', '5559876', 'Associate', 30000, '02/17/2014'),
('Tom', 'Flenderson', '5558769', 'Associate', 32000, '11/23/2013'),
('Bently', 'Singh', '5554326', 'Manager', 60000, '06/06/2005'),
('Winnie', 'Lim', '5554527', 'Manager', 62000, '10/24/2003'),
('Ruda', 'Bross', '5554428', 'Manager', 66000, '11/06/2004');
-- Insert four projects into the projects table
INSERT INTO projects
(project_name, budget, start_date)
VALUES
('Build Database', 20000, '3/4/2020'),
('Plan christmas party', 500, '11/20/2020'),
('Remove old stock', 1000, '4/6/2020'),
('Watch paint dry', 3000, '2/11/2020');
-- Insert some customers into the customers table
INSERT INTO customers
(first_name, last_name, phone, email, street, city, zip_code)
VALUES
('Kacie', 'Mckee','5555234', 'kacie.mckee@gmail.com', '61 Shadow Brook Court', 'Melrose, MA', '02176'),
('Moses', 'Mcghee', '5554651', 'moses.mcghee@gmail.com', '570 Old York St.', 'Upland, CA', '91784'),
('Jerome', 'Aguilar', '5555699', 'jerome.aguilar@yahoo.com', '68 Victoria Road', 'Hoboken, NJ', '07030'),
('Ainsley', 'Bonner', '5558564', 'ainsley.bonner@hotmail.com', '60 Winchester Road', 'Dundalk, MD', '21222') ,
('Delilah', 'Bateman', '5523124', 'delilah.bateman@gmail.com', '482 E. Oxford St.', 'Thibodaux, LA', '70301');

Additional references & examples

How to query data with SQL — Basic

SQL / Knex Raw SQL
Retrieve rows using SELECT with WHERE, ordering, and limits as needed.
Learn: SELECT is the foundation of read APIs. Filter with WHERE, sort with ORDER BY, and paginate with LIMIT/OFFSET. Select only columns you need to keep responses small.

Supplementary examples

Filtered, ordered query

SELECT paste_id, name, created_at
FROM pastes
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 10;

Course example

{
"data": {
"product_id": 1,
"product_sku": "XLH4P7t3er",
"product_title": "Vanilla Scented Candle",
"product_description": "Vanilla-scented candle, perfect for your living room.",
"product_price": "48.25",
"product_quantity_in_stock": 0,
"product_weight_in_lbs": "2.30",
"supplier_id": 1,
"created_at": "2020-12-01T20:37:09.550Z",
"updated_at": "2020-12-01T20:37:09.550Z",
"category_id": 2,
"category_name": "candles",
"category_description": "A category for gift candles, including both scented and non-scented candles, for the home."
}
}

Additional references & examples

How to update and delete with SQL — Basic

SQL / Knex Raw SQL
Modify or remove rows using UPDATE and DELETE with appropriate WHERE clauses.
Learn: Always include WHERE on UPDATE and DELETE unless you intend to touch every row. Check `rowCount` or RETURNING to confirm something actually changed—return 404 when no row matched.

Supplementary examples

Update and delete by id

UPDATE pastes SET name = 'Renamed' WHERE paste_id = 3;
DELETE FROM pastes WHERE paste_id = 3;

Course example

UPDATE employees
SET job_title = 'Chemical Engineer II', city = 'Lawrenceville'
WHERE job_title = 'Chemical Engineer' AND city = 'Trenton';

Additional references & examples

  • UPDATE

    Modify existing rows with WHERE clauses.

  • DELETE

    Remove rows safely with filters.

How to join tables with SQL — Intermediate

SQL / Knex Raw SQL
Combine related tables with JOIN to return denormalized result sets.
Learn: JOINs combine tables in one query. INNER JOIN keeps rows that match both sides; LEFT JOIN keeps all left rows even when the right side is missing—common for optional relationships.

Supplementary examples

Pastes with usernames

SELECT pastes.paste_id, pastes.name, users.username
FROM pastes
INNER JOIN users ON pastes.user_id = users.user_id;

Course example

SELECT *
FROM departments
JOIN employees
ON departments.manager = employees.employee_id;

Additional references & examples