Learn SQL: Day 1 – Relational Database Fundamentals

We’re going to learn these topics at three levels simultaneously:

Database Level (PostgreSQL)
SQL Level
Rails / ActiveRecord Level

For every topic, ask yourself:

“How does PostgreSQL store this?”

“How do I query this with SQL?”

“How does Rails represent this?”

This will help you to prepare for interviews. We use PostgreSQL here.

Today’s Goal

By the end of Day 1, you should fully understand:

Database
Table
Row
Column
Primary Key
Foreign Key
Constraints
One-to-One
One-to-Many
Many-to-Many
Rails associations behind them

Part 1: What is a Database?

Imagine you are building an e-commerce application.

You need to store:

Users
Products
Orders
Payments

A database is simply a structured place to store and retrieve that information.

In PostgreSQL:

CREATE DATABASE shop_app;

Connect to it:

psql postgres

Inside psql:

CREATE DATABASE shop_app;

Connect:

\c shop_app

Verify:

SELECT current_database();

Output:

 shop_app

Part 2: What is a Table?

A table is similar to an Excel sheet.

Example:

Users table

id	name	email
1	John	john@test.com
2	Mary	mary@test.com

Create it:

			
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255)
);

		

Verify:

\d users

Interview Question:

Why not store everything in a single giant table?

Answer:

Because:

duplication increases
maintenance becomes difficult
relationships become unclear
updates become expensive

This concept is called normalization (we’ll study later).

Part 3: Rows

A row represents one record.

Insert data:

			
INSERT INTO users (name, email)
VALUES
('John', 'john@test.com'),
('Mary', 'mary@test.com');

View:

SELECT * FROM users;

Output:

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

Each row = one user.

Part 4: Columns

Columns describe attributes.

In users table:

			
id
name
email

View columns:

\d users

Senior Insight:

A database table models an entity.

Examples:

Entity	Table
User	users
Product	products
Order	orders

Columns represent attributes of that entity.

Part 5: Primary Keys

Every row needs a unique identifier.

Example:

id BIGSERIAL PRIMARY KEY

Meaning:

No duplicates.

No NULLs.

Try:

			
INSERT INTO users (id, name)
VALUES (1, 'Bob');

You should get:

duplicate key value violates unique constraint

Why Primary Keys Exist

Without a primary key:

			
John
John
John

Which John?

Nobody knows.

Primary key solves identity.

Rails Equivalent

Migration:

			
create_table :users do |t|
  t.string :name
  t.string :email
end

Rails automatically adds:

id

as the primary key.

Part 6: Constraints

Constraint = database rule.

Interviewers love this topic.

NOT NULL

Create:

			
CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

Try:

			
INSERT INTO products(name)
VALUES(NULL);

Fails.

UNIQUE

			
CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

Duplicate email:

			
INSERT INTO customers(email)
VALUES('test@test.com');
INSERT INTO customers(email)
VALUES('test@test.com');

Fails.

CHECK Constraint

Age must be positive.

			
CREATE TABLE employees (
  id BIGSERIAL PRIMARY KEY,
  age INTEGER CHECK(age > 0)
);

Fails:

			
INSERT INTO employees(age)
VALUES(-5);

Why Constraints Matter

Junior developer:

validates :email, uniqueness: true

Senior developer:

			
validates :email, uniqueness: true
+
UNIQUE(email)

Because application validations can be bypassed.

Database constraints cannot.

Part 7: Foreign Keys

Now let’s model:

User has many orders.

Create users:

			
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100)
);

Create orders:

			
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT,
  total NUMERIC(10,2)
);

		

Foreign key:

			
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id);

Insert user:

			
INSERT INTO users(name)
VALUES('John');

Insert order:

			
INSERT INTO orders(user_id,total)
VALUES(1,100);

Works.

Try:

			
INSERT INTO orders(user_id,total)
VALUES(999,100);

Fails.

Because user doesn’t exist.

Why Foreign Keys Exist

Without them:

Order belongs to user 999

But user 999 doesn’t exist.

Database becomes corrupted.

Rails Equivalent

			
class User < ApplicationRecord
  has_many :orders
end
class Order < ApplicationRecord
  belongs_to :user
end

		

Migration:

			
t.references :user,
             null: false,
             foreign_key: true

Rails creates:

			
user_id
FOREIGN KEY

behind the scenes.

Part 8: One-to-Many Relationship

Most common relationship.

Example:

User -> Orders

One user:

John

Many orders:

			
Order 1
Order 2
Order 3

Diagram:

			
users
-----
id
orders
------
id
user_id

		

Rails:

			
User has_many :orders
Order belongs_to :user

Practical Exercise

Insert:

			
INSERT INTO users(name)
VALUES('Mary');

Orders:

			
INSERT INTO orders(user_id,total)
VALUES
(2,50),
(2,75),
(2,120);

		

Query:

			
SELECT *
FROM orders
WHERE user_id = 2;

Part 9: One-to-One Relationship

Less common.

Example:

			
User
Profile

Each user has exactly one profile.

Create profile table:

			
CREATE TABLE profiles (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT UNIQUE,
  bio TEXT,
  FOREIGN KEY(user_id)
  REFERENCES users(id)
);

		

Notice:

UNIQUE(user_id)

This forces:

			
One user
One profile

Rails:

			
class User < ApplicationRecord
  has_one :profile
end
class Profile < ApplicationRecord
  belongs_to :user
end

		

Interview Question:

How does a database enforce one-to-one?

Answer:

			
FOREIGN KEY
+
UNIQUE

on the foreign key column.

Part 10: Many-to-Many Relationship

Classic interview topic.

Example:

			
Students
Courses

Student can enroll in many courses.

Course can have many students.

Create students:

			
CREATE TABLE students (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100)
);

Create courses:

			
CREATE TABLE courses (
  id BIGSERIAL PRIMARY KEY,
  title VARCHAR(100)
);

Need a join table:

			
CREATE TABLE enrollments (
  student_id BIGINT,
  course_id BIGINT,
  PRIMARY KEY(student_id, course_id),
  FOREIGN KEY(student_id)
    REFERENCES students(id),
  FOREIGN KEY(course_id)
    REFERENCES courses(id)
);

		

Diagram:

			
students
    |
    |
enrollments
    |
    |
courses

		

Rails

			
class Student < ApplicationRecord
  has_many :enrollments
  has_many :courses, through: :enrollments
end
class Course < ApplicationRecord
  has_many :enrollments
  has_many :students, through: :enrollments
end
class Enrollment < ApplicationRecord
  belongs_to :student
  belongs_to :course
end

		

Senior-Level Insight

Most Rails developers stop at:

			
has_many
belongs_to

Strong backend engineers understand:

			
Association
      ↓
Foreign Key
      ↓
Constraint
      ↓
Index
      ↓
Storage

		

That understanding helps you:

debug production issues
optimize queries
design schemas
answer interview questions confidently

Interview Questions

Try answering without looking.

Q1

Difference between:

PRIMARY KEY

and

UNIQUE

Q2

Can a table have multiple UNIQUE constraints?

Q3

Can a table have multiple PRIMARY KEYS?

Q4

How is a one-to-one relationship implemented in PostgreSQL?

Q5

Why should foreign keys exist even when Rails validations exist?

Q6

What problem does a join table solve?

Practical Lab (Run Everything)

Create a fresh database:

CREATE DATABASE interview_sql_day1;

Connect:

\c interview_sql_day1

Create:

			
users
profiles
orders
students
courses
enrollments

		

Insert sample data.

Then practice:

			
SELECT * FROM users;
SELECT * FROM orders;
SELECT * FROM profiles;
SELECT * FROM enrollments;

Try intentionally violating:

PRIMARY KEY
UNIQUE
NOT NULL
FOREIGN KEY

and observe PostgreSQL’s error messages.

A senior engineer learns a lot from database errors.

Homework

Exercise 1

Create:

			
authors
books

One author → many books

Add proper foreign keys.

Exercise 2

Create:

			
employees
employee_details

One-to-one relationship.

Exercise 3

Create:

			
movies
actors
movie_actors

Many-to-many relationship.

Insert:

3 movies
5 actors

Create relationships.

Exercise 4

For every relationship above, write the equivalent Rails models and associations.

Day 2 Preview

Next we’ll cover the foundation of everything in SQL:

SELECT Queries

Including:

SELECT
WHERE
ORDER BY
LIMIT
OFFSET
DISTINCT
IN
BETWEEN
LIKE
ILIKE
NULL handling

plus PostgreSQL execution behavior and ActiveRecord equivalents.

This is where real querying begins.

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

M	T	W	T	F	S	S
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29

Today’s Goal

Part 1: What is a Database?

Part 2: What is a Table?

Part 3: Rows

Part 4: Columns

Part 5: Primary Keys

Why Primary Keys Exist

Rails Equivalent

Part 6: Constraints

NOT NULL

UNIQUE

CHECK Constraint

Why Constraints Matter

Part 7: Foreign Keys

Why Foreign Keys Exist

Rails Equivalent

Part 8: One-to-Many Relationship

Practical Exercise

Part 9: One-to-One Relationship

Part 10: Many-to-Many Relationship

Senior-Level Insight

Interview Questions

Q1

Q2

Q3

Q4

Q5

Q6

Practical Lab (Run Everything)

Homework

Exercise 1

Exercise 2

Exercise 3

Exercise 4

Day 2 Preview

SELECT Queries

Share this:

Related

Author: Abhilash

Leave a comment Cancel reply