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 NULLconstraint:
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.comMary | 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 usersWHERE active = true;
Rails:
User.where(active: true)
Age Greater Than 30
SELECT * FROM usersWHERE age > 30;
Rails:
User.where("age > ?", 30)
Multiple Conditions
SELECT * FROM usersWHERE city = 'Chicago'AND active = true;
Rails:
User.where(city: "Chicago", active: true)
OR
SELECT * FROM usersWHERE 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 usersORDER BY age ASC;
Smallest age first.
Descending
SELECT * FROM usersORDER BY salary DESC;
Highest salary first.
Rails:
User.order(salary: :desc)
Multiple Columns
SELECT * FROM usersORDER BY city ASC, salary DESC;
Meaning:
Sort by city firstInside each citysort 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 usersLIMIT 3;
Rails:
User.limit(3)
Why LIMIT Matters
Imagine:
10 million rows
Fetching all:
slowmemory-heavyunnecessary
LIMIT reduces work.
5. OFFSET
Skip rows.
SELECT * FROM usersLIMIT 3OFFSET 3;
Meaning:
Skip first 3Return 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.,
idorcreated_at). - Next Pages: Instead of using
OFFSET, the query filters records using aWHEREclause 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:
ChicagoChicagoChicagoBostonBostonNew York
Distinct:
SELECT DISTINCT city FROM users;
Result:
ChicagoBostonNew 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 usersWHERE age BETWEEN 25 AND 35;
Equivalent:
age >= 25ANDage <= 35
Rails
User.where(age: 25..35)
Salary Range
SELECT * FROM usersWHERE 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 usersWHERE name LIKE 'M%';
Result:
MaryMike
Ends With
WHERE email LIKE '%test.com'
Contains
WHERE name LIKE '%ar%'
Matches:
MarySara
Wildcards
| Symbol | Meaning |
|---|---|
| % | Any number of chars |
| _ | Exactly one char |
Example
WHERE name LIKE '_o%'
Matches:
BobTom
10. ILIKE
PostgreSQL-specific.
Case-insensitive LIKE.
SELECT * FROM usersWHERE name ILIKE 'john';
Matches:
JohnJOHNjohnJoHn
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 = NULLsalary = NULL
Wrong
WHERE age = NULL
Returns:
Nothing
Correct
WHERE age IS NULL
Find users with no salary:
SELECT * FROM usersWHERE salary IS NULL;
Rails
User.where(age: nil)
Generates:
IS NULL
NOT NULL
SELECT * FROM usersWHERE salary IS NOT NULL;
Why NULL Is Special
SQL uses:
TRUEFALSEUNKNOWN
not just:
TRUEFALSE
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
select | pluck | |
|---|---|---|
| Returns | ActiveRecord objects | Ruby arrays |
| Example retrieval | user.name | row[0] |
| Instantiates models | Yes | No |
| Good for | Working with models | Just retrieving data |
| Memory | More | Less |
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:
- SELECT specific columns
- WHERE with multiple conditions
- ORDER BY price DESC
- LIMIT 5
- DISTINCT categories
- BETWEEN on price
- LIKE searches
- 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! 🚀
