Lesson 1 - Setting up and Getting started with SQL

Result: 2 seconds. Ten routes. Failure rates. Done.

Your coworker needs the same data? They run the same query against the same database. No file sharing. No version conflicts. No duplicated storage.

This is why databases exist. Not because they’re theoretically better – because at scale, they’re the only thing that works.

This lesson builds the technical foundation for everything that follows. You’ll install PostgreSQL (the database engine) and pgAdmin (your interface to that engine). More importantly, you’ll understand why these tools are structured the way they are, and what business problems they solve.

 

2. The Problem: Why Files Don’t Scale?

Let’s start with first principles.

When you save an Excel file, you’re creating a file-based data system:

• Data lives in a single file on your computer
• To access data, you open the entire file
• One person can edit at a time (unless you use cloud tools with limitations)
• Calculations happen in your computer’s memory
• Sharing means copying files

 

This works perfectly fine for:

• Personal budgets
• Small project tracking (< 10,000 rows)
• Ad-hoc calculations
• Quick exploratory analysis
• One-off reports

 

It breaks down when:

1. Data Volume Exceeds Memory

Your laptop has 8-16GB of RAM. A dataset with 5 million rows and 30 columns consumes roughly 4-6GB when loaded into Excel. Add pivot tables, formulas, and background processes—your system is at capacity. Excel becomes unusable.

 

2. Multiple Users Need Concurrent Access

Your sales team needs live dashboard data. Your finance team needs the same data for forecasting. Your operations team needs it for capacity planning. If everyone opens the same Excel file, you get:

• File locking (only one editor at a time)
• Version chaos (“Wait, are you working on v3_final or v3_final_ACTUAL?”)
• Manual refresh cycles (someone has to update and re-share the file)

 

3. Query Patterns Become Complex

“Show me customers who bought Product A but not Product B, grouped by region, excluding returns, for customers acquired in Q4 2024.”

In Excel: 20 minutes of filtering, helper columns, VLOOKUPs, and pivot tables. In SQL: One query, 5 seconds.

 

4. Data Integrity Requirements Increase

You need to ensure:

• Customer IDs are always unique
• Order totals always match line item sums
• Dates are always in valid format
• Foreign key relationships remain consistent

 

Excel has no built-in enforcement. You rely on manual validation. At scale, manual validation fails.

 

3. The Solution: Server-Based Data Systems

A database is fundamentally different from a file. Instead of storing data in a file that must be opened in full, a database server:

1. Runs continuously as a background service, always ready to respond to requests
2. Stores data in optimized structures (tables, indexes) designed for fast retrieval
3. Accepts queries from multiple users simultaneously, returning only requested data
4. Enforces rules (constraints) that maintain data integrity automatically
5. Manages concurrent access so multiple users can read and write without conflicts

 

The key insight: You don’t load the database. You query it. You ask specific questions and get specific answers.

 

4. PostgreSQL: Your Database Engine

PostgreSQL is a relational database management system (RDBMS)—software that stores, manages, and serves data in table format with defined relationships between tables.

 

4.1. Why PostgreSQL?

1. Industry Legitimacy PostgreSQL powers production systems at:

• Apple (internal data infrastructure)
• Instagram (main database for years during explosive growth)
• Spotify (user data and music metadata)
• Reddit (comment and post storage)
• Uber (geospatial data and route optimization)

 

When you list PostgreSQL on your resume, employers recognize it as enterprise-grade experience.

 

2. Open Source with No Strings

• Free to use commercially
• No licensing restrictions
• No vendor lock-in
• Extensive community documentation
• Active development (new features every year)

 

3. SQL Standards Compliance PostgreSQL adheres closely to ANSI SQL standards. Skills you develop here transfer directly to Oracle, MySQL, Microsoft SQL Server, and other systems.

 

4. Advanced Feature Set While you’re learning fundamentals, PostgreSQL supports:

• Complex queries with subqueries and CTEs
• Window functions for advanced analytics
• JSON data types (bridging relational and document storage)
• Full-text search
• Custom functions and extensions

 

You won’t outgrow PostgreSQL as your skills advance.

 

4.2. How PostgreSQL Works?

When you install PostgreSQL, you’re installing a database server – a program that runs continuously in the background, listening for incoming requests.

Think of it like this:

• PostgreSQL = A library with millions of books
• You = Someone who wants specific information
• SQL = The language you use to ask the librarian for exactly what you need
• The librarian = PostgreSQL’s query engine, which finds and returns your results

 

You never enter the library and read every book. You ask precise questions and get precise answers.

 

5. pgAdmin: Your Window into PostgreSQL

PostgreSQL runs as a server process. It has no graphical interface. You communicate with it by sending SQL commands through a connection.

pgAdmin is a graphical user interface (GUI) that:

• Connects to PostgreSQL servers
• Provides a visual browser for databases, tables, and data
• Offers a query editor for writing and executing SQL
• Displays query results in table format
• Includes tools for database management and design

 

Important distinction:

• PostgreSQL = The engine (stores data, executes queries)
• pgAdmin = The dashboard (your interface to the engine)

 

You’ll write SQL in pgAdmin’s query editor. pgAdmin sends that SQL to PostgreSQL. PostgreSQL executes the query and returns results. pgAdmin displays those results.

 

6. Installation: Building Your Environment

You’re about to install the. data infrastructure. Follow the setup video carefully.