Introduction
SQL (Structured Query Language) is the standard language used to interact with relational databases. Instead of writing custom retrieval algorithms, SQL lets you describe the data you want in a declarative way — 'give me all customers who signed up last month' — and the RDBMS's query engine figures out how to execute that request efficiently.
Cricket analogy: Asking a scorer 'show me all centuries scored in the last IPL season' is declarative, like SQL: you describe what you want, and the scoring system figures out how to scan the records efficiently.
SQL is standardized by ANSI/ISO, but each RDBMS (PostgreSQL, MySQL, SQL Server, Oracle) adds its own extensions and small syntax differences. Code written using core standard SQL is highly portable across systems; dialect-specific features are not, so it's good practice to know which parts of your SQL are standard versus vendor-specific.
Cricket analogy: The ICC's Laws of Cricket are standardized globally, but leagues like the IPL add their own rules (like Impact Players), just as ANSI SQL is standard while PostgreSQL and MySQL add their own extensions.
Key Concepts
SQL statements fall into a few broad categories. DDL (Data Definition Language) defines structure: CREATE TABLE, ALTER TABLE, DROP TABLE. DML (Data Manipulation Language) manipulates data: SELECT, INSERT, UPDATE, DELETE. DCL (Data Control Language) manages permissions: GRANT, REVOKE. TCL (Transaction Control Language) manages transactions: COMMIT, ROLLBACK. Most day-to-day work as a developer revolves around DML, especially SELECT queries.
Cricket analogy: Setting up a new stadium's scoreboard structure is like DDL, entering live scores during a match is like DML, granting broadcasters access is like DCL, and confirming a completed innings is like TCL.
SQL keywords are case-insensitive (SELECT and select behave the same), but string data inside quotes IS case-sensitive, and identifier case sensitivity for unquoted table/column names varies by database (PostgreSQL lowercases them by default). Always test assumptions on your specific RDBMS.
Example
-- DDL: define the structure
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price NUMERIC(10, 2) NOT NULL,
in_stock BOOLEAN DEFAULT TRUE
);
-- DML: manipulate the data
INSERT INTO products (name, price) VALUES ('Wireless Mouse', 24.99);
UPDATE products SET price = 19.99 WHERE name = 'Wireless Mouse';
SELECT name, price
FROM products
WHERE in_stock = TRUE
ORDER BY price ASC;Output
This script first defines a products table (DDL), then adds and modifies a row (DML), and finally retrieves all in-stock products sorted by ascending price. The final SELECT would return a result set such as one row: 'Wireless Mouse | 19.99'. Notice that SELECT does not change any data — it only reads and returns rows matching the WHERE and ORDER BY clauses, which is why it is the statement you will write most often as a developer.
Cricket analogy: Just as checking the live scoreboard doesn't change the match result, a SELECT query only reads and returns rows like the cheapest in-stock item, such as a specific bat priced at a set amount, without altering data.
Key Takeaways
- SQL is a declarative language for defining, querying, and manipulating relational data.
- Statements fall into DDL (structure), DML (data), DCL (permissions), and TCL (transactions).
- SELECT, INSERT, UPDATE, and DELETE (DML) are the statements used most often in application development.
- Standard SQL is portable across RDBMSs, but each vendor has its own syntax extensions and quirks.
Practice what you learned
1. What does the acronym SQL stand for?
2. Which category of SQL statements includes CREATE TABLE and ALTER TABLE?
3. Which SQL statement is used to retrieve data without modifying it?
4. Why is it important to distinguish standard SQL from vendor-specific SQL dialect features?
Was this page helpful?
You May Also Like
SELECT Statement Basics
Learn how to retrieve data from a table using the SELECT statement, the foundation of every SQL query.
The Relational Model
Understand how the relational model organizes data into tables of rows and columns, based on set theory and predicate logic.
Filtering with WHERE
Use the WHERE clause to filter rows returned by a query based on specified conditions.