SQL Fundamentals
Introduction to SQL
The Language of Data
Imagine a massive library filled with countless books, all organized on shelves. To find a specific piece of information, you wouldn't just wander around aimlessly. You'd go to the librarian and ask a clear, structured question. In the world of data, a database is that library, and SQL is the language you use to talk to the librarian.
SQL, or Structured Query Language, is a language to talk to databases.
SQL (often pronounced "sequel" or as S-Q-L) is the standard way to communicate with relational databases. Whether you want to add new information, retrieve something specific, or update an existing record, you'll use SQL to make the request. It was developed at IBM in the early 1970s, originally called SEQUEL, and quickly became the go-to tool for database management.
Breaking Down the Language
SQL isn't a single monolithic language; it's more like a toolkit with different sets of commands for different jobs. These command sets are often grouped into sublanguages. While different database systems might have slight variations, or "dialects," they all share this fundamental structure.
Although SQL can be used in all relational databases, many databases have some syntax after the standard, which we can call "dialects."
Let's look at the five main components of SQL.
| Sublanguage | Name | Purpose |
|---|---|---|
| DQL | Data Query Language | Asks questions and retrieves data. |
| DML | Data Manipulation Language | Adds, deletes, and modifies data. |
| DDL | Data Definition Language | Creates and alters the database structure itself. |
| DCL | Data Control Language | Manages permissions and user access. |
| TCL | Transaction Control Language | Manages transactions and saves changes. |
The Five Sublanguages
1. Data Query Language (DQL)
This is the part of SQL you'll likely use most often. Its primary command is SELECT. DQL is all about retrieving data from the database. It lets you specify exactly what information you want, from which tables, and under what conditions. Think of it as asking a precise question.
-- Find the names and ages of all users from the 'customers' table
SELECT name, age FROM customers;
2. Data Manipulation Language (DML)
While DQL just looks at data, DML changes it. This sublanguage is for creating, updating, and deleting the actual records within your tables. The main commands are INSERT, UPDATE, and DELETE.
-- Add a new user to the 'customers' table
INSERT INTO customers (name, age) VALUES ('Alice', 30);
-- Change Alice's age to 31
UPDATE customers SET age = 31 WHERE name = 'Alice';
3. Data Definition Language (DDL)
DDL commands don't touch the data itself; they build and modify the structure that holds the data. This includes creating tables, defining columns and their data types, and setting up relationships between tables. The key commands are CREATE, ALTER, and DROP.
-- Create a new table to store customer information
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
age INT
);
4. Data Control Language (DCL)
DCL is the security guard of the database. It handles user permissions and controls who can see or change what data. The two main commands are GRANT (which gives permissions) and REVOKE (which takes them away).
-- Allow a user named 'analyst' to view the customers table
GRANT SELECT ON customers TO analyst;
5. Transaction Control Language (TCL)
TCL commands manage transactions, which are sequences of operations performed as a single logical unit of work. These commands ensure that groups of changes are either all successful or none are. This prevents data from becoming inconsistent. Important commands include COMMIT (to save the changes) and ROLLBACK (to undo them).
-- Save all changes made during the current transaction
COMMIT;
Understanding these five parts is the first step to mastering SQL. Each one has a distinct role, but they all work together to give you complete control over your database.
What is the primary purpose of SQL?
A database administrator needs to create a new table to store customer information. Which SQL sublanguage would they use for this task?