SQL: Introduction to SQL

1. Introduction to SQL

Imagine walking into a large library and wanting to borrow a specific book. You need to communicate with the librarian — tell her the book title, author, or classification number, and she can help you find it.

2. Core Concepts

(1) What is SQL?

SQL is a standard language specifically designed to communicate with relational databases. It was born in the 1970s, proposed by IBM researchers, and has since become the most widely used database query language in the world.

(2) Why Learn SQL?

• High market demand: Backend development, data analysis, data science, and many other positions almost all require SQL skills
• Highly versatile: After learning SQL, you can seamlessly switch between MySQL, PostgreSQL, SQLite, SQL Server, and many other databases
• Clear logic: SQL syntax is close to natural language, making it easy to get started, yet extremely powerful
• Timeless: SQL has been popular for over 50 years and remains a core skill in the data field

(3) Core Relational Database Concepts

Relational databases organize data using tables, just like Excel spreadsheets — intuitive and straightforward:

For example, an employees table:

• Each row represents an employee
• The id column is the primary key, ensuring each employee has a unique identifier
• name, department, salary are columns that describe employee information

(4) SQL vs NoSQL

(5) Major Database Comparison

3. Basic Syntax/Usage

(1) SQL Syntax Basics

SQL statements end with a semicolon ;. Keywords are case-insensitive (but uppercase is recommended for readability).

SELECT name, salary FROM employees WHERE salary > 10000;

This statement means: from the employees table, query the name and salary of employees whose salary exceeds 10000.

(2) SQL Command Categories

SQL commands are divided into four major categories by function:

▶ Example: Query All Data (Difficulty ⭐)

The most basic query — retrieve all data from a table:

-- Query all employee information
SELECT * FROM employees;

Example output:

id  name  department_id  salary    hire_date
1   John  1              15000.00  2023-01-15
2   Jane  2              12000.00  2023-03-20
3   Bob   1              18000.00  2022-06-01

* means "all columns." In production environments, it's recommended to explicitly list column names to avoid returning unnecessary data.

▶ Example: Create a Table and Insert Data (Difficulty ⭐)

Use DDL to create a simple table, then use DML to insert data:

-- Create students table
CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    grade TEXT
);

-- Insert two records
INSERT INTO students (name, age, grade) VALUES ('Alice', 18, 'Senior');
INSERT INTO students (name, age, grade) VALUES ('Bob', 17, 'Junior');

-- Query to verify
SELECT * FROM students;

Output:

id  name   age  grade
1   Alice  18   Senior
2   Bob    17   Junior

4. Common Application Scenarios

(1) Scenario 1: E-commerce Data Management

An e-commerce platform needs to manage products, users, orders, and other data. SQL can handle this easily:

-- Query products with low stock
SELECT name, stock FROM products WHERE stock < 10;

-- Calculate total order amount for this month
SELECT SUM(quantity * price) AS total_revenue
FROM orders
JOIN products ON orders.product_id = products.id
WHERE order_date >= '2026-06-01';

(2) Scenario 2: Employee Management System

An enterprise HR system needs to manage employee information, departments, salaries, etc.:

-- Query average salary by department
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- Query employees who joined in 2023
SELECT name, hire_date FROM employees
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

❓ FAQ

Q: Is SQL hard to learn? A: No. SQL syntax is close to English, and there are only a few core commands (SELECT, INSERT, UPDATE, DELETE). Mastering the basics only takes a few days.

Q: Do I need to install a database before learning SQL? A: It's recommended to start with SQLite — it doesn't require a server installation, just a single file to run. Lesson 02 of this tutorial will show you how to set up the environment.

Q: What's the difference between SQL and MySQL? A: SQL is a language standard, while MySQL is a specific database product that uses the SQL language. It's like the relationship between "English" and "Americans."

Q: What jobs can I get with SQL skills? A: Backend developer, data analyst, data engineer, BI engineer, DBA (database administrator), and many other positions rely on SQL.

📖 Summary

• SQL is the standard language for communicating with relational databases, with over 50 years of history
• Relational databases organize data using tables, rows, and columns; primary keys uniquely identify each row
• SQL commands are divided into DDL (define structure), DML (manipulate data), DCL (control permissions), and TCL (manage transactions)
• Major databases include MySQL, PostgreSQL, SQLite, and SQL Server; SQLite is recommended for beginners
• SQL suits most business scenarios; NoSQL is for special needs (massive scale, flexible structure)

📝 Exercises

1. Exercise 1 (⭐): Explain in your own words what a Primary Key is and why every table needs one.
2. Exercise 2 (⭐⭐): Suppose you need to design a "books" table containing title, author, price, and publication date. Write the CREATE TABLE statement.
3. Exercise 3 (⭐⭐⭐): Compare SQL and NoSQL, list 3 advantages and 3 disadvantages of each, and explain which scenarios call for which type of database.

5. Unified Example Database

The following data will be used in the remaining 27 lessons. Get familiar with it now; after setting up the environment in Lesson 02, you can copy and execute it:

-- Departments table
CREATE TABLE departments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    location TEXT
);

-- Employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    department_id INTEGER,
    salary DECIMAL(10,2),
    hire_date DATE,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT,
    price DECIMAL(10,2),
    stock INTEGER DEFAULT 0
);

-- Orders table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_name TEXT NOT NULL,
    product_id INTEGER,
    quantity INTEGER,
    order_date DATE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

6. Next Lesson

👉 02 - Environment Setup: Install database tools, configure your learning environment, and run your first SQL statement!

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