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;

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.

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

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

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

Example:

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;

Can you explain what this query does before running it?

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

ActiveRecord Translation Challenge

Convert this SQL:

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

into ActiveRecord.

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.

Happy Learning! 🚀