SQL, or Structured Query Language, is the standard language for interacting with relational databases. It provides a way to communicate with the database and perform various operations. In this article, we'll delve into four essential SQL commands: SELECT, INSERT, UPDATE, and DELETE. Each command will be explained with practical examples to give you a solid understanding of their functionality.
1. SELECT Command
The SELECT command is used to retrieve data from a database. You can specify which columns of data you would like to see, as well as various filtering options to refine your search results.
Syntax:
SELECT column1, column2, ... FROM table_name WHERE condition;
Example:
Imagine we have a table named employees with the following structure:
| id | name | department | salary |
|---|---|---|---|
| 1 | Alice | HR | 70000 |
| 2 | Bob | Engineering | 80000 |
| 3 | Charlie | Marketing | 65000 |
| 4 | Dave | HR | 72000 |
To retrieve the names and salaries of employees from the HR department, you can execute:
SELECT name, salary FROM employees WHERE department = 'HR';
This will return:
| name | salary |
|---|---|
| Alice | 70000 |
| Dave | 72000 |
2. INSERT Command
The INSERT command is used to add new rows of data into a table. This command is essential for populating your database with initial data or any new records as they are created.
Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Example:
Continuing with the employees table, to add a new employee named "Eve" in the Engineering department with a salary of 85000:
INSERT INTO employees (name, department, salary) VALUES ('Eve', 'Engineering', 85000);
After executing the above command, the employees table will now have an additional row:
| id | name | department | salary |
|---|---|---|---|
| 5 | Eve | Engineering | 85000 |
3. UPDATE Command
The UPDATE command is used to modify existing data within a table. It allows you to change one or multiple columns for rows that meet certain criteria.
Syntax:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Example:
Let’s say we want to give the employees in the Marketing department a raise of 5000. We can execute the following command:
UPDATE employees SET salary = salary + 5000 WHERE department = 'Marketing';
After this command runs, Charlie's original salary of 65000 will be updated to 70000:
| id | name | department | salary |
|---|---|---|---|
| 3 | Charlie | Marketing | 70000 |
4. DELETE Command
The DELETE command removes records from a table. It can delete specific rows that meet specified conditions, or, if no conditions are provided, it can remove all rows from the table.
Syntax:
DELETE FROM table_name WHERE condition;
Example:
Suppose an employee, Bob, has left the company, and we need to remove his record from the employees table. We can do that as follows:
DELETE FROM employees WHERE name = 'Bob';
After executing this, the employees table will no longer contain Bob’s record, and it will look like this:
| id | name | department | salary |
|---|---|---|---|
| 1 | Alice | HR | 70000 |
| 3 | Charlie | Marketing | 70000 |
| 4 | Dave | HR | 72000 |
| 5 | Eve | Engineering | 85000 |
Each of these commands plays a crucial role in managing a database effectively. Understanding how to properly compose and execute these SQL commands will enable you to interact with your data in a powerful and meaningful way.