A guide with detailed explanations and engaging examples to help you learn SQL
SQL (Structured Query Language) is used to interact with relational databases. To start practicing SQL, install a database system such as MySQL, PostgreSQL, or SQLite. You can also try free online platforms like SQLZoo or Mode Analytics.
Once you have a database installed, you can execute queries using a command-line tool or a graphical interface. For example, in a MySQL terminal you might type:
mysql -u username -pAfter connecting, you'll be ready to write and run SQL commands.
SQL is the standard language for managing and manipulating relational databases. It allows you to create databases, query data, update records, and control access to your data. Whether you’re analyzing data or building database-driven applications, SQL is an essential skill.
A basic SQL query typically starts with the SELECT statement, followed by the columns you want to retrieve, and the FROM clause to specify the table.
SELECT column1, column2
FROM table_name;
For example, to get the names and emails from a users table:
SELECT name, email
FROM users;
This query fetches the name and email columns from the users table.
When designing tables, you must choose the right data types for your columns. Common SQL data types include:
Choosing the correct data type ensures efficient storage and accurate data processing.
Use the CREATE TABLE statement to define a new table and its columns, along with data types and constraints.
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
created_at DATE
);
This command creates a table named users with columns for user ID, name, email, and creation date.
The INSERT INTO statement adds new rows to a table.
INSERT INTO users (name, email, created_at)
VALUES ('Alice', 'alice@example.com', '2025-04-16');
This statement inserts a new user into the users table with a name, email, and creation date.
The SELECT statement is the foundation for querying data in SQL. Below are multiple examples that demonstrate a variety of techniques, each explained in detail.
SELECT * FROM users;
This query retrieves all columns and every row from the users table. The asterisk (*) serves as a wildcard, which means "all columns."
SELECT name, email FROM users;
This query returns only the name and email columns from the users table, helping to reduce unnecessary data and improve query efficiency.
SELECT * FROM users
WHERE age >= 18;
The WHERE clause filters rows according to the specified condition. In this example, it retrieves only those users aged 18 or older.
SELECT name, email FROM users
WHERE email LIKE '%@example.com';
The LIKE operator is used for pattern matching. Here, the percent sign (%) acts as a wildcard. This query returns rows where the email ends with "@example.com."
SELECT name, email FROM users
WHERE age >= 18 AND country = 'USA';
This query uses the AND operator to return users who are at least 18 years old and are located in the USA. You can also use OR to broaden the conditions.
SELECT name, email FROM users
ORDER BY name ASC, age DESC;
The ORDER BY clause sorts the results by one or more columns. Here, the rows are ordered by name in ascending order; if names are identical, the rows are sorted by age in descending order.
SELECT DISTINCT country FROM users;
The DISTINCT keyword removes duplicate values from the result set, ensuring that each country appears only once.
SELECT * FROM users
LIMIT 5;
The LIMIT clause restricts the number of rows returned. This query returns only the first 5 rows from the users table.
SELECT * FROM users
LIMIT 5 OFFSET 10;
Here, LIMIT 5 OFFSET 10 skips the first 10 rows and returns the next 5 rows. This is useful for implementing pagination.
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country;
The GROUP BY clause groups rows by the specified column. This query calculates the number of users in each country using the COUNT() function.
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 10;
The HAVING clause filters the results after grouping. This query returns only those groups where the number of users is greater than 10.
SELECT name, email
FROM users
WHERE id IN (
SELECT user_id FROM orders WHERE total > 100
);
This query employs a subquery to select users who have placed orders exceeding 100. The inner query retrieves user_id values from the orders table, and the outer query fetches the corresponding name and email.
SELECT UPPER(name) AS upper_name, LENGTH(name) AS name_length
FROM users;
This query uses UPPER() to convert names to uppercase and LENGTH() to calculate the character count of each name. Aliases (AS upper_name, AS name_length) are used to label the results.
SELECT name, email FROM users
WHERE country = 'USA'
UNION
SELECT name, email FROM users
WHERE country = 'Canada';
The UNION operator combines the results of two SELECT statements into a single result set. In this example, it returns users from both the USA and Canada. Note that UNION removes duplicates by default.
SELECT name,
CASE
WHEN age >= 18 THEN 'Adult'
ELSE 'Minor'
END AS age_group
FROM users;
This query uses a CASE expression to create a new column called age_group that categorizes users as 'Adult' or 'Minor' based on their age. CASE expressions are useful for conditional logic directly within your SELECT statements.
These examples showcase a wide range of techniques to retrieve and manipulate data using SQL. Experiment with these queries on your own database to build confidence in data extraction and transformation.
Modify existing data using UPDATE and remove data using DELETE.
-- Update a user's email
UPDATE users
SET email = 'alice_new@example.com'
WHERE name = 'Alice';
-- Delete a user record
DELETE FROM users
WHERE id = 10;
These commands let you change or remove data from the table as needed.
Joins combine rows from two or more tables based on related columns. Common joins include INNER JOIN, LEFT JOIN, and RIGHT JOIN.
SELECT orders.id, users.name, orders.total
FROM orders
INNER JOIN users ON orders.user_id = users.id;
This example retrieves order information along with the corresponding user's name using an inner join.
Aggregate functions allow you to perform calculations on a set of values. Common functions include COUNT, SUM, AVG, MIN, and MAX.
SELECT COUNT(*) AS total_users, AVG(age) AS average_age
FROM users;
This query calculates the total number of users and the average age.
Subqueries are queries nested within another SQL query. They can be used in SELECT, INSERT, UPDATE, or DELETE statements.
SELECT name, email
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);
This query selects users who have orders over 100 in value.
Views are virtual tables based on a SELECT query, and stored procedures are saved SQL code that can be executed on demand.
-- Create a view
CREATE VIEW active_users AS
SELECT * FROM users
WHERE active = 1;
-- Create a stored procedure (MySQL syntax)
DELIMITER $$
CREATE PROCEDURE GetUserCount()
BEGIN
SELECT COUNT(*) FROM users;
END$$
DELIMITER ;
These features help encapsulate complex queries and operations.
Database security is crucial. Use user roles and permissions to control access to your data. Ensure you use parameterized queries or prepared statements to protect against SQL injection.
-- Granting SELECT privilege on a table
GRANT SELECT ON database_name.users TO 'username'@'localhost';
Proper security practices help maintain the integrity and confidentiality of your data.
Congratulations on completing this comprehensive SQL guide! You now have a solid foundation in querying and managing databases. The next steps are to build projects, explore advanced optimization techniques, and dive deeper into database design and management.
For further exploration, check out these resources: