Today we start writing queries.

Today’s Goals

By the end of Day 2 you should understand:

• SELECT
• WHERE
• ORDER BY
• LIMIT
• OFFSET
• DISTINCT
• IN
• BETWEEN
• LIKE
• ILIKE
• NULL handling
• ActiveRecord equivalents
• Common interview questions
• Common mistakes

Step 1: Create Our Practice Database

Connect:

psql sql_day1

Let’s create a new table.

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255),
  age INTEGER,
  city VARCHAR(100),
  salary NUMERIC(10,2),
  active BOOLEAN,
  created_at TIMESTAMP DEFAULT NOW()
);

Insert Sample Data

INSERT INTO users
(name, email, age, city, salary, active)
VALUES
('John',  'john@test.com', 30, 'New York', 70000, true),
('Mary',  'mary@test.com', 25, 'Chicago', 60000, true),
('Bob',   'bob@test.com', 35, 'Chicago', 90000, false),
('Alice', 'alice@test.com', 28, 'Boston', 75000, true),
('Tom',   'tom@test.com', 40, 'New York', 120000, false),
('Sara',  'sara@test.com', 32, 'Boston', 85000, true),
('Mike',  'mike@test.com', NULL, 'Chicago', NULL, true);

View data:

SELECT * FROM users;

ADD a column to an existing database table

To add a column to an existing database table, you use the ALTER TABLE statement combined with the ADD clause.

Common Examples

• Add a basic text column:

ALTER TABLE employees 
ADD email VARCHAR(255);

• Add a column with a default value and a NOT NULL constraint:

ALTER TABLE employees 
ADD status VARCHAR(50) NOT NULL DEFAULT 'Active';

• Add multiple columns at once:

ALTER TABLE employees 
ADD phone_number VARCHAR(20),
    hire_date DATE;

1. SELECT

The most basic query.

SELECT * FROM users;

Meaning:

Give me all columns from users

Output:

id | name | email | age | city | salary

Select Specific Columns

Instead of everything:

SELECT name, email FROM users;

Output:

name   | email
--------+---------------
John   | john@test.com
Mary   | mary@test.com

Rails Equivalent

User.select(:name, :email)

Senior Insight

Avoid:

SELECT *

in production systems unless needed.

Why?

Because fetching unnecessary columns:

• uses more memory
• transfers more data
• slows queries

Good:

SELECT id, name FROM users;

2. WHERE Clause

Filters rows.

Example

Only active users.

SELECT * FROM users
WHERE active = true;

Rails:

User.where(active: true)

Age Greater Than 30

SELECT * FROM users
WHERE age > 30;

Rails:

User.where("age > ?", 30)

Multiple Conditions

SELECT * FROM users
WHERE city = 'Chicago'
AND active = true;

Rails:

User.where(city: "Chicago", active: true)

OR

SELECT * FROM users
WHERE city = 'Chicago'
OR city = 'Boston';

Rails:

User.where(city: ["Chicago", "Boston"])

Interview Question

Which runs first?

WHERE A OR B AND C

Answer:

AND

before

OR

Use parentheses.

WHERE (A OR B)
AND C

3. ORDER BY

Sort results.

Ascending

SELECT * FROM users
ORDER BY age ASC;

Smallest age first.

Descending

SELECT * FROM users
ORDER BY salary DESC;

Highest salary first.

Rails:

User.order(salary: :desc)

Multiple Columns

SELECT * FROM users
ORDER BY city ASC, salary DESC;

Meaning:

Sort by city first
Inside each city
sort by salary

Common Interview Question

What happens if you omit ASC/DESC?

ORDER BY age

Default:

ASC

4. LIMIT

Return only N rows.

SELECT * FROM users
LIMIT 3;

Rails:

User.limit(3)

Why LIMIT Matters

Imagine:

10 million rows

Fetching all:

slow
memory-heavy
unnecessary

LIMIT reduces work.

5. OFFSET

Skip rows.

SELECT * FROM users
LIMIT 3
OFFSET 3;

Meaning:

Skip first 3
Return next 3

Rails

User.limit(3).offset(3)

Pagination Example

Page 1

LIMIT 10 OFFSET 0

Page 2

LIMIT 10 OFFSET 10

Page 3

LIMIT 10 OFFSET 20

Senior Insight

Large OFFSET values become expensive.

Example:

OFFSET 500000

PostgreSQL still scans through those rows.

Later we’ll learn:

Keyset Pagination

which is much faster.

Keyset pagination is a database query method that fetches next sets of data by using the last seen record’s unique key or value (like an ID or timestamp) instead of skipping rows with an offset. It is also known as seek-based pagination or the engine behind cursor-based pagination.

How It Works

• First Page: The app queries the first N rows ordered by an indexed column (e.g., id or created_at).
• Next Pages: Instead of using OFFSET, the query filters records using a WHERE clause matching values after the last key from the previous page (e.g., WHERE id > last_seen_id).
• Continuation: The application repeats this using the final item’s key from the current batch as the anchor for the next request.

6. DISTINCT

Remove duplicates.

Example:

SELECT city FROM users;

Result:

Chicago
Chicago
Chicago
Boston
Boston
New York

Distinct:

SELECT DISTINCT city FROM users;

Result:

Chicago
Boston
New York

Rails

User.select(:city).distinct

Multiple Columns

SELECT DISTINCT city, active FROM users;

Distinct applies to the combination.

7. IN

Cleaner alternative to multiple OR conditions.

Instead of:

WHERE city='Boston'
OR city='Chicago'
OR city='New York'

Use:

WHERE city IN
('Boston','Chicago','New York');

Rails:

User.where(city: ["Boston", "Chicago", "New York"])

8. BETWEEN

Range filtering.

Age between 25 and 35.

SELECT * FROM users
WHERE age BETWEEN 25 AND 35;

Equivalent:

age >= 25
AND
age <= 35

Rails

User.where(age: 25..35)

Salary Range

SELECT * FROM users
WHERE salary BETWEEN 60000 AND 90000;

Interview Question

Is BETWEEN inclusive?

Answer:

YES

Both boundaries included.

9. LIKE

Pattern matching.

Find names beginning with M.

SELECT * FROM users
WHERE name LIKE 'M%';

Result:

Mary
Mike

Ends With

WHERE email LIKE '%test.com'

Contains

WHERE name LIKE '%ar%'

Matches:

Mary
Sara

Wildcards

Example

WHERE name LIKE '_o%'

Matches:

Bob
Tom

10. ILIKE

PostgreSQL-specific.

Case-insensitive LIKE.

SELECT * FROM users
WHERE name ILIKE 'john';

Matches:

John
JOHN
john
JoHn

Rails AR:

User.where("name ILIKE ?", "john")

User.where("city ILIKE ?", "%boston%")
# matches:
# Boston
# boston
# BOSTON
# BoStOn

# Starts with Boston
User.where("city LIKE ?", "Bos%")

# Ends with ton
User.where("city LIKE ?", "%ton")

# Contains ost
User.where("city LIKE ?", "%ost%")

# with another condition
User.where("city ILIKE ?", "%boston%")
    .where("salary > ?", 80_000)

Interview point: Always use ? parameter binding rather than string interpolation:

# Avoid
User.where("city LIKE '%#{city}%'")

# Good
User.where("city LIKE ?", "%#{city}%")

Senior Interview Insight

In PostgreSQL:

LIKE

is case-sensitive.

ILIKE

is case-insensitive.

Many developers don’t know this.

11. NULL Handling

This is a favorite interview topic.

Let’s inspect:

SELECT * FROM users;

Mike has:

age = NULL
salary = NULL

Wrong

WHERE age = NULL

Returns:

Nothing

Correct

WHERE age IS NULL

Find users with no salary:

SELECT * FROM users
WHERE salary IS NULL;

Rails

User.where(age: nil)

Generates:

IS NULL

NOT NULL

SELECT * FROM users
WHERE salary IS NOT NULL;

Why NULL Is Special

SQL uses:

TRUE
FALSE
UNKNOWN

not just:

TRUE
FALSE

This is called:

Three-Valued Logic

Interviewers love asking this.

Practical Exercises

Exercise 1

Find all active users.

Exercise 2

Find users older than 30.

Exercise 3

Find users from Boston.

Exercise 4

Find top 3 highest-paid users.

Exercise 5

Find unique cities.

Exercise 6

Find users aged between 25 and 35.

Exercise 7

Find names starting with S.

Exercise 8

Find users whose salary is NULL.

Combining Everything

Qn) Display name, city, salary of 3 highest salaried users who are active and living in cities chicago and boston.

Example:

Qn) Can you explain what this query does before running it?

SELECT name, city, salary
FROM users
WHERE active = true
  AND city IN ('Chicago', 'Boston')
  AND salary IS NOT NULL
ORDER BY salary DESC
LIMIT 3;

That’s exactly the kind of reasoning expected in senior interviews.

Convert the above into ActiveRecord query:

User.where(active: true, city: ["Chicago", "Boston"])
    .where.not(salary: nil)
    .order(salary: :desc)
    .limit(3)
    .pluck(:name, :city, :salary)

We can also write this AR Query using select

User.select(:name, :city, :salary)
    .where(active: true, city: ["Boston", "Chicago"])
    .where.not(salary: nil)
    .order(salary: :desc)
    .limit(3)

Both queries apply the same filtering, ordering, and limiting. The main difference is what they return.

1. query using select

User.select(:name, :city, :salary)
    .where(active: true, city: ["Boston", "Chicago"])
    .where.not(salary: nil)
    .order(salary: :desc)
    .limit(3)

It returns User ActiveRecord objects with only those selected columns:

[
  #<User name: "John", city: "Boston", salary: 100000>,
  ...
]

Because id is not selected, some operations expecting user.id may not work as expected.

2. Second query

User.where(active: true, city: ["Chicago", "Boston"])
    .where.not(salary: nil)
    .order(salary: :desc)
    .limit(3)
    .pluck(:name, :city, :salary)

This returns plain Ruby arrays, not User objects:

[
  ["John", "Boston", 100000],
  ["Alice", "Chicago", 90000]
]

pluck directly retrieves the requested columns from the database.

Key difference

Interview takeaway: select narrows the columns of returned ActiveRecord objects; pluck skips model instantiation and returns the requested values directly.

ActiveRecord Translation Challenge

Convert this SQL:

SELECT * FROM users
  WHERE city = 'Chicago'
  AND active = true
  ORDER BY salary DESC
  LIMIT 2;

into ActiveRecord.

Solution:

User.where(city: "Chicago", active: true)
    .order(salary: :desc)
    .limit(2)

#If you only need the name, city, and salary:
User.where(city: "Chicago", active: true)
    .order(salary: :desc)
    .limit(2)
    .pluck(:name, :city, :salary)

Common Mistakes

Mistake 1

WHERE age = NULL

Wrong.

Use:

WHERE age IS NULL

Mistake 2

Using:

SELECT *

everywhere.

Mistake 3

Forgetting ORDER BY when using LIMIT.

LIMIT 5

without ordering can return arbitrary rows.

Mistake 4

Using huge OFFSET values.

Senior-Level Knowledge

Understand that SQL logically executes in this order:

FROM
WHERE
SELECT
DISTINCT
ORDER BY
LIMIT

Even though we write:

SELECT ...
FROM ...
WHERE ...

PostgreSQL conceptually processes the clauses in the above order.

This understanding becomes extremely important when we move to:

• JOINs
• GROUP BY
• HAVING
• Query Optimization
• EXPLAIN ANALYZE

Homework

Create a new table:

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100),
  category VARCHAR(100),
  price NUMERIC(10,2),
  stock_quantity INTEGER
);

Insert at least 10 records.

Practice:

1. SELECT specific columns
2. WHERE with multiple conditions
3. ORDER BY price DESC
4. LIMIT 5
5. DISTINCT categories
6. BETWEEN on price
7. LIKE searches
8. Products with stock_quantity IS NULL

Day 3 Preview

Next we’ll cover one of the most important interview topics:

JOINs

Including:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• CROSS JOIN
• Self Join
• ActiveRecord joins
• includes vs joins vs preload vs eager_load
• Real Rails interview questions

Day 3 is where SQL starts becoming truly powerful.

Day 3: https://railsdrop.com/2024/02/12/learn-sql-day-3-joins/

Happy Learning! 🚀