Relational Databases & SQL

Relational databases store data in tables with rows and columns. Every table has a primary key that uniquely identifies each row, and foreign keys that express relationships between tables. SQL is the language you use to create, read, update, and delete that data.

The power of relational databases comes from JOINs — combining rows from multiple tables based on a shared key. A well-designed schema with normalized tables and proper foreign keys prevents duplicated data and maintains consistency. PostgreSQL, MySQL, and SQL Server all speak standard SQL with minor dialects.

Security boundary: never concatenate user input into SQL. Use parameterized queries or a trusted query builder, and grant the app database role least privilege (no DROP/ALTER in production app roles).

Before

Multiple round trips

1-- Two separate queries2SELECT * FROM orders WHERE user_id = 42;3SELECT * FROM users WHERE id = 42;

After

Single JOIN query

1-- One query with a JOIN2SELECT o.id, o.total, u.name, u.email3FROM orders o4JOIN users u ON u.id = o.user_id5WHERE o.user_id = 42;

Exercise

Write one JOIN that returns order id, total, and customer email for a given user_id. Then rewrite the same need as two queries and note when the JOIN is preferable. Use parameterized placeholders ($1 / ?) — never string-concatenate user input.