What Is SQL?
Understand what SQL is, how it works, popular SQL dialects, and the difference between declarative and procedural languages.
Introduction to SQL
Now that you understand what databases are, let's learn about SQL - the language used to communicate with relational databases.
SQL stands for Structured Query Language. It's a powerful language designed specifically for managing and manipulating data in relational database management systems (RDBMS).
SQL is a standardized programming language used to manage relational databases and perform various operations on the data in them. It allows you to create, read, update, and delete data, as well as manage database structures.
What Is SQL Used For?
SQL is used for a wide variety of database operations:
Operation | Description | Example Use Case |
|---|---|---|
| Querying Data | Retrieve specific information from databases | Find all customers who made purchases last month |
| Inserting Data | Add new records to tables | Register a new user account |
| Updating Data | Modify existing records | Update a customer's shipping address |
| Deleting Data | Remove records from tables | Delete old log entries |
| Creating Structures | Build databases and tables | Set up a new e-commerce database |
| Managing Permissions | Control who can access data | Grant read-only access to analysts |
Let's see a simple SQL query in action:
SELECT name, age
FROM employees
WHERE age > 30;This query retrieves the names and ages of all employees older than 30 from the employees table.
Declarative vs Procedural Languages
One of SQL's most powerful features is that it's a declarative language, not a procedural language.
A declarative language lets you specify what you want to accomplish, without specifying how to do it. The database engine figures out the best way to execute your request.
A procedural language requires you to specify exact step-by-step instructions for how to accomplish a task. Examples include Python, Java, and C++.
Example Comparison:
Procedural approach (like Python):
result = []
for employee in employees:
if employee.age > 30:
result.append({'name': employee.name, 'age': employee.age})
Declarative approach (SQL):
SELECT name, age FROM employees WHERE age > 30;
Notice how in SQL, you simply describe the result you want. The database engine optimizes and executes the query for you!
Benefit: SQL's declarative nature means you can write simpler queries while the database handles complex optimizations behind the scenes.
Popular SQL Dialects
While SQL is standardized (ANSI SQL), different database systems have their own "dialects" - variations with specific features and syntax differences.
Database | Developer | Common Use Cases | Key Features |
|---|---|---|---|
| MySQL | Oracle | Web applications, WordPress | Free, widely used, good for small to medium apps |
| PostgreSQL | Open Source | Complex applications, analytics | Advanced features, extensible, ACID compliant |
| SQL Server | Microsoft | Enterprise applications | Integration with Microsoft ecosystem |
| SQLite | Open Source | Mobile apps, embedded systems | Serverless, lightweight, file-based |
| Oracle | Oracle | Large enterprises | Robust, scalable, expensive |
Fun Fact: SQLite is the most deployed database engine in the world! It's embedded in billions of smartphones, browsers, and applications.
ANSI SQL vs Dialect Differences
ANSI SQL is the standardized version of SQL defined by the American National Standards Institute. It ensures a common baseline across different database systems.
However, each database vendor adds their own features and sometimes changes syntax slightly. Here are some common differences:
Feature | MySQL | PostgreSQL | SQL Server | SQLite | |||
|---|---|---|---|---|---|---|---|
| Auto-increment | AUTO_INCREMENT | SERIAL | IDENTITY | AUTOINCREMENT | |||
| String concatenation | CONCAT() | \ | \ | or CONCAT() | + | \ | \ |
| Limit results | LIMIT 10 | LIMIT 10 | TOP 10 | LIMIT 10 | |||
| Date function | NOW() | NOW() | GETDATE() | datetime('now') | |||
| Case sensitivity | Case-insensitive | Case-sensitive | Case-insensitive | Case-insensitive |
Example - Getting top 5 records:
MySQL/PostgreSQL/SQLite:
SELECT * FROM employees LIMIT 5;
SQL Server:
SELECT TOP 5 * FROM employees;
Both accomplish the same goal, but with different syntax!
Important: While this course teaches standard SQL that works across most databases, always check your specific database's documentation for exact syntax and features.
Basic SQL Syntax Rules
Before we start writing queries, let's cover some fundamental SQL syntax rules:
1. SQL is case-insensitive (in most databases)
SELECT name FROM employees;
select name from employees; -- Same as above
Convention: Write SQL keywords in UPPERCASE for better readability.
2. Statements end with a semicolon
SELECT * FROM employees;
3. Whitespace is ignored
-- These are equivalent:
SELECT name FROM employees;
SELECT name
FROM employees;
4. Comments
-- This is a single-line comment
/* This is a
multi-line comment */
5. String values use single quotes
SELECT * FROM employees WHERE name = 'Alice';
Style Tip: Use consistent formatting and indentation to make your queries readable, especially as they get more complex!
Your First SQL Query
Let's write our first complete SQL query! We'll retrieve all data from an employees table:
-- Select all columns from the employees table
SELECT *
FROM employees;Breaking it down:
SELECT *: The asterisk (*) means "select all columns"FROM employees: Specifies which table to query;: Ends the SQL statement
Congratulations! You've just learned to read your first SQL query. In the upcoming lessons, we'll learn to write much more sophisticated queries!