A Guide to Basic SQL Commands

SQL (Structured Query Language) is the standard language for interacting with relational databases. Whether you are a data analyst, a backend developer, or just someone curious about data, learning the basics of SQL is an invaluable skill. It's the language you use to ask a database for the information you need.

At its core, most of what you do in SQL can be boiled down to four basic operations, often known by the acronym CRUD: Create, Read, Update, and Delete.

Let's walk through these fundamental commands.

For our examples, let's imagine we have a simple Employees table:

Employees Table

EmployeeID	FirstName	LastName	Department
1	Alice	Smith	Sales
2	Bob	Johnson	Marketing
3	Charlie	Williams	Sales

1. Reading Data: SELECT

The SELECT statement is used to retrieve data from a database. It's the command you will use most frequently.

To select all columns from the table:

SELECT * FROM Employees;

The * is a wildcard that means "all columns".

To select only specific columns:

SELECT FirstName, Department FROM Employees;

Filtering with WHERE

The WHERE clause is used to filter records and extract only those that fulfill a specified condition.

To select only the employees in the 'Sales' department:

SELECT * FROM Employees WHERE Department = 'Sales';

2. Creating Data: INSERT INTO

The INSERT INTO statement is used to add new rows of data to a table.

INSERT INTO Employees (FirstName, LastName, Department)
VALUES ('David', 'Jones', 'Engineering');

This will add a new employee to our table.

3. Updating Data: UPDATE

The UPDATE statement is used to modify existing records in a table.

Important: Always use a WHERE clause with UPDATE. If you forget it, you will update all the rows in the table!

To change Bob's department to 'Engineering':

UPDATE Employees
SET Department = 'Engineering'
WHERE EmployeeID = 2;

4. Deleting Data: DELETE

The DELETE statement is used to remove existing records from a table.

Important: Just like with UPDATE, always use a WHERE clause with DELETE to specify which record(s) to delete.

To delete the employee Charlie:

DELETE FROM Employees WHERE EmployeeID = 3;

Sorting Data with ORDER BY

You can sort your results using the ORDER BY clause. By default, it sorts in ascending order (ASC). You can specify DESC for descending order.

-- Select all employees, sorted by their last name
SELECT * FROM Employees ORDER BY LastName ASC;

Combining Data with JOIN

The real power of relational databases comes from the ability to combine data from multiple tables. Let's say we have another table called Salaries.

Salaries Table

EmployeeID	Salary
1	70000
2	60000

We can use an INNER JOIN to combine these tables to see the salary for each employee.

SELECT
    Employees.FirstName,
    Employees.LastName,
    Salaries.Salary
FROM Employees
INNER JOIN Salaries ON Employees.EmployeeID = Salaries.EmployeeID;

This query will return a result set that combines the name from the Employees table with the salary from the Salaries table where the EmployeeID matches.

Conclusion

These basic commands—SELECT, INSERT, UPDATE, and DELETE—are the absolute foundation of SQL. By mastering them, you can perform the vast majority of tasks required for data manipulation. While SQL has many more advanced features, a solid understanding of these CRUD operations is the essential first step to becoming proficient with databases.