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! 🚀

Author: Abhilash

Hi, I’m Abhilash! A seasoned web developer with 15 years of experience specializing in Ruby and Ruby on Rails. Since 2010, I’ve built scalable, robust web applications and worked with frameworks like Angular, Sinatra, Laravel, Node.js, Vue and React. Passionate about clean, maintainable code and continuous learning, I share insights, tutorials, and experiences here. Let’s explore the ever-evolving world of web development together! View all posts by Abhilash