Environment Setup and First Query

Lesson 2: Environment Setup and First Query

🎯 Life Analogy

Imagine you want to learn cooking. Before you start cooking, you need to:

1. Prepare the kitchen — Installing a database is like installing a stove and oven
2. Prepare the tools — Command line and GUI tools are like spatulas and measuring cups
3. Prepare the ingredients — Creating a database is like laying out ingredients on the counter
4. Start cooking — Your first SQL query is like making the first cut

You can't cook without a kitchen, and similarly, you can't learn SQL without setting up a database environment. This lesson will guide you from zero to building your own "SQL kitchen."

📚 Core Concepts

1. Installing a Database

There are three mainstream relational databases. Beginners should install one first and expand later.

MySQL

MySQL is the world's most popular open-source database, widely used in web development.

Windows Installation:

1. Go to the MySQL official website to download the installer
2. Run the installer and select "Developer Default"
3. Set the root password (remember it!)
4. After installation, the MySQL service starts automatically

macOS Installation:

# Install using Homebrew
brew install mysql

# Start MySQL service
brew services start mysql

# Secure installation
mysql_secure_installation

Linux (Ubuntu) Installation:

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo mysql_secure_installation

PostgreSQL

PostgreSQL is the most powerful open-source database, suitable for complex queries and enterprise applications.

Windows Installation:

1. Go to the PostgreSQL official website to download the installer
2. Run the installer, set password and port (default 5432)
3. Check the "pgAdmin" component (GUI management tool)

macOS Installation:

brew install postgresql@16
brew services start postgresql@16

Linux (Ubuntu) Installation:

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

SQLite

SQLite is a lightweight embedded database that requires no server installation, making it perfect for learning and small projects.

Windows Installation:

1. Go to the SQLite download page and download sqlite-tools-win64
2. Extract to any directory and add that directory to the system PATH

macOS / Linux Installation:

# macOS (usually pre-installed)
brew install sqlite

# Linux
sudo apt install sqlite3

Verify Installation:

sqlite3 --version

2. Connecting to a Database Using the Command Line

The command line is the most direct way to interact with a database.

Connect to MySQL:

mysql -u root -p
# Enter password to access the MySQL interactive interface

After entering, you'll see the prompt change to mysql>, indicating you can now enter SQL statements.

Connect to PostgreSQL:

# Using the psql client
psql -U postgres

# Specify a database
psql -U postgres -d mydb

After entering, the prompt changes to mydb=#.

Connect to SQLite:

# Create or open a database file
sqlite3 mydb.db

# If the file doesn't exist, it will be created automatically

After entering, the prompt changes to sqlite>.

Exit the Database Command Line:

3. GUI Tools

If you prefer a visual interface, the following tools are recommended:

DBeaver Installation Example (Recommended):

1. Go to the DBeaver official website and download the Community Edition
2. After installation, open it and click "New Database Connection"
3. Select the database type (e.g., MySQL)
4. Fill in host, port, username, and password
5. Click "Test Connection" — once successful, you're ready to use it

4. Online SQL Practice Platforms

If you don't want to install any software for now, you can practice directly on online platforms:

📝 Basic Syntax and Usage

The First SQL Statement: SELECT

SELECT is the most commonly used statement in SQL, used to query data from a database. The simplest form is:

SELECT expression;

Let's start with the simplest query:

SELECT 1;

This statement means: calculate and return the value 1. It doesn't involve any table — it's purely to verify that the database is working properly.

Syntax in Different Databases:

-- Works in MySQL / PostgreSQL / SQLite
SELECT 1;

-- Return a string
SELECT 'Hello, SQL!';

-- Return a calculation result
SELECT 2 + 3;

Viewing Database Information

After connecting to a database, you may want to know about the current environment:

MySQL:

-- View all databases
SHOW DATABASES;

-- View current database
SELECT DATABASE();

-- View version
SELECT VERSION();

PostgreSQL:

-- View all databases
\l

-- View current database
SELECT current_database();

-- View version
SELECT version();

SQLite:

-- View all tables
.tables

-- View database info
.database

-- View version
SELECT sqlite_version();

Creating Your First Database and Table

Before formally learning SELECT, we need data to query. Here's the complete process for creating a database and table:

MySQL Example:

-- Create database
CREATE DATABASE learn_sql;

-- Switch to the database
USE learn_sql;

-- Create table
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    city VARCHAR(50)
);

-- Insert sample data
INSERT INTO students (name, age, city) VALUES
('John', 20, 'New York'),
('Jane', 22, 'Los Angeles'),
('Bob', 21, 'Chicago'),
('Alice', 23, 'Houston'),
('Charlie', 20, 'New York');

PostgreSQL Example:

CREATE DATABASE learn_sql;

-- Disconnect and reconnect to learn_sql
\c learn_sql

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    city VARCHAR(50)
);

INSERT INTO students (name, age, city) VALUES
('John', 20, 'New York'),
('Jane', 22, 'Los Angeles'),
('Bob', 21, 'Chicago'),
('Alice', 23, 'Houston'),
('Charlie', 20, 'New York');

SQLite Example:

-- SQLite doesn't require creating a database; create table directly
CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    city TEXT
);

INSERT INTO students (name, age, city) VALUES
('John', 20, 'New York'),
('Jane', 22, 'Los Angeles'),
('Bob', 21, 'Chicago'),
('Alice', 23, 'Houston'),
('Charlie', 20, 'New York');

Executing Your First SELECT Query

Now that the data is ready, let's query it:

-- Query all information for all students
SELECT * FROM students;

Output:

+----+---------+------+-----------+
| id | name    | age  | city      |
+----+---------+------+-----------+
|  1 | John    |   20 | New York  |
|  2 | Jane    |   22 | Los Angeles|
|  3 | Bob     |   21 | Chicago   |
|  4 | Alice   |   23 | Houston   |
|  5 | Charlie |   20 | New York  |
+----+---------+------+-----------+

Query Only Specific Columns:

-- Query only name and city
SELECT name, city FROM students;

Output:

+---------+-----------+
| name    | city      |
+---------+-----------+
| John    | New York  |
| Jane    | Los Angeles|
| Bob     | Chicago   |
| Alice   | Houston   |
| Charlie | New York  |
+---------+-----------+

🧪 Examples

Example: Verify Database Connection Using SELECT

The first step after installing a database is to confirm the connection is working.

-- Verify database responds normally
SELECT 'Database connected!' AS message;

-- View current time and user
SELECT NOW() AS current_time, CURRENT_USER AS db_user;

SQLite syntax is slightly different:

SELECT 'Database connected!' AS message;

SELECT datetime('now') AS current_time;

Example output:

+---------------------+
| message             |
+---------------------+
| Database connected! |
+---------------------+

This simple test can help you quickly troubleshoot connection issues — if this statement executes, your environment is set up correctly.

Example: Create a Table, Insert Data, and Query

Suppose we want to manage a small book library.

-- Create books table
CREATE TABLE books (
    id INT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(50),
    price DECIMAL(8, 2),
    publish_year INT
);

-- Insert sample data
INSERT INTO books (id, title, author, price, publish_year) VALUES
(1, 'Database Systems', 'Connolly', 45.00, 2014),
(2, 'SQL in 10 Minutes', 'Ben Forta', 39.00, 2020),
(3, 'High Performance MySQL', 'Baron Schwartz', 89.00, 2013),
(4, 'PostgreSQL in Action', 'Tan Feng', 69.00, 2018);

-- Query all books
SELECT * FROM books;

Output:

+----+-------------------------+-----------------+-------+---------------+
| id | title                   | author          | price | publish_year  |
+----+-------------------------+-----------------+-------+---------------+
|  1 | Database Systems        | Connolly        | 45.00 |          2014 |
|  2 | SQL in 10 Minutes       | Ben Forta       | 39.00 |          2020 |
|  3 | High Performance MySQL  | Baron Schwartz  | 89.00 |          2013 |
|  4 | PostgreSQL in Action    | Tan Feng        | 69.00 |          2018 |
+----+-------------------------+-----------------+-------+---------------+

Query Only Title and Price:

SELECT title, price FROM books;

By specifying column names (title, price) instead of using *, you can make the output clearer and the query more efficient.

Example: Query with Calculations and Aliases

SQL's SELECT can not only query data but also perform calculations.

-- Calculate years since publication for each book (based on 2026)
SELECT
    title AS book_title,
    author AS book_author,
    price AS original_price,
    price * 0.8 AS discounted_price,
    2026 - publish_year AS years_since_publish
FROM books;

Output:

+-------------------------+-------------+----------------+------------------+--------------------+
| book_title              | book_author | original_price | discounted_price | years_since_publish|
+-------------------------+-------------+----------------+------------------+--------------------+
| Database Systems        | Connolly    |          45.00 |            36.00 |                 12 |
| SQL in 10 Minutes       | Ben Forta   |          39.00 |            31.20 |                  6 |
| High Performance MySQL  | Baron S.    |          89.00 |            71.20 |                 13 |
| PostgreSQL in Action    | Tan Feng    |          69.00 |            55.20 |                  8 |
+-------------------------+-------------+----------------+------------------+--------------------+

Key Points:

• The AS keyword is used to create aliases for columns, making the output more readable
• You can perform math operations on columns (e.g., price * 0.8)
• You can mix constants and column names (e.g., 2026 - publish_year)

🏢 Scenario Applications

Scenario 1: Development Environment Setup

You are a backend developer, and a new project requires a MySQL database. Complete the following tasks:

1. Install MySQL and start the service
2. Create the project database project_db
3. Create a users table for storing user information
4. Insert a test record and verify

-- Step 1: Create database
CREATE DATABASE project_db CHARACTER SET utf8mb4;

-- Step 2: Switch database
USE project_db;

-- Step 3: Create users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 4: Insert test data
INSERT INTO users (username, email) VALUES
('testuser', 'test@example.com');

-- Step 5: Verify
SELECT * FROM users;

Scenario 2: Data Analysis Preparation

You are a data analyst who needs to quickly examine CSV data. Import the data into SQLite for querying.

# Operate in the command line
sqlite3 sales.db

# Create table
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product TEXT,
    quantity INTEGER,
    unit_price REAL,
    sale_date TEXT
);

# Import CSV data (using .import command)
.mode csv
.import sales_data.csv sales

# View import results
SELECT * FROM sales LIMIT 5;

# View total record count
SELECT COUNT(*) AS total_records FROM sales;

❓ FAQ

Q: Should I choose MySQL, PostgreSQL, or SQLite? A: Beginners should start with SQLite — zero configuration, one file is a database. Choose MySQL for web development, and PostgreSQL for enterprise applications or complex queries. Learning all three is ideal, but master one first.

Q: What if I forgot the root password during installation? A: For MySQL, start with mysqld_safe --skip-grant-tables and reset the password. For PostgreSQL, modify the pg_hba.conf file to change authentication to trust and restart the service. SQLite has no password mechanism, so no worries.

Q: Nothing happens after entering an SQL statement in the command line. What do I do? A: Check if you forgot the semicolon ;. SQL statements must end with a semicolon to execute. If you've already typed content without a semicolon, just add ; and press Enter.

Q: What's the difference between online platforms and local installation? A: Online platforms require no installation and are ready to use, but typically have timeout and data size limits, and data is lost when you close the browser. Local installation has no limits, data is persistently saved, and it's suitable for long-term learning and real project development.

📖 Summary

In this lesson, we completed the environment preparation for SQL learning:

1. Installing databases — Learned how to install MySQL, PostgreSQL, and SQLite
2. Command line connection — Mastered connecting to and operating databases using command line tools
3. GUI tools — Explored DBeaver, Navicat, DB Browser, and other graphical tools
4. Online platforms — Discovered SQL Fiddle, DB Fiddle, and other online practice environments
5. First query — Learned the basics of the SELECT statement, including querying, calculations, and aliases

Setting up the environment may be tedious, but it's the foundation for all subsequent learning. Just like learning to cook requires familiarizing yourself with the kitchen first, knowing your tools allows you to focus on "cooking" itself.

📝 Exercises

Exercise 1: Environment Verification

Connect to your installed database, execute the following query, and confirm the output is correct:

SELECT 'Hello SQL!' AS greeting, 1 + 1 AS result;

Exercise 2: Create and Query a Products Table

Create a products table with the following fields, insert at least 3 records, then query all records:

Exercise 3: Query with Calculations

Using the products table from Exercise 2, write a query that calculates the total inventory value (price × stock) for each product, and use Chinese aliases for the output columns.

Expected output example:

+----------+------+--------+------------+
| Product Name | Price | Stock | Total Stock Value |
+----------+------+--------+------------+
| ...      | ...  | ...    | ...        |
+----------+------+--------+------------+

Next Lesson: Lesson 3: Basic Queries — SELECT and FROM

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us