Structured Query Language (SQL) is the backbone of relational databases. Whether you're a beginner or an experienced backend engineer, mastering SQL is essential for efficiently managing and querying data. In this article, we'll walk through the basics of SQL, starting from creating a database to performing advanced operations like altering tables, updating records, and deleting data.

1. Creating a Database

Before you can work with tables and data, you need to create a database. A database is a collection of tables that store related data.

CREATE DATABASE CompanyDB;

This command creates a new database named CompanyDB. You can switch to this database using the following command:

USE CompanyDB;

2. Creating a Table

Once the database is created, the next step is to create a table. A table is a structured set of data made up of rows and columns.

Let's create a table named Employees with the following columns:

• EmployeeID (Primary Key, Integer)

• FirstName (Varchar)

• LastName (Varchar)

• Department (Varchar)

• Salary (Decimal)

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

This command creates a table named Employees with the specified columns.

3. Inserting Data into the Table

Now that the table is created, let's insert some data into it.

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (1, 'John', 'Doe', 'HR', 50000.00);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (2, 'Jane', 'Smith', 'IT', 60000.00);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (3, 'Alice', 'Johnson', 'Finance', 55000.00);

These commands insert three rows into the Employees table.

4. Selecting Data from the Table

The SELECT statement is used to retrieve data from a table.

Basic SELECT

To retrieve all columns from the Employees table:

SELECT * FROM Employees;

This will return all rows and columns from the Employees table.

Selecting Specific Columns

To retrieve only the FirstName and LastName columns:

SELECT FirstName, LastName FROM Employees;

Filtering Data with WHERE Clause

To retrieve employees who work in the IT department:

SELECT * FROM Employees WHERE Department = 'IT';

Sorting Data with ORDER BY

To retrieve employees sorted by their salary in descending order:

SELECT * FROM Employees ORDER BY Salary DESC;

Limiting Results with LIMIT

To retrieve only the top 2 highest-paid employees:

SELECT * FROM Employees ORDER BY Salary DESC LIMIT 2;

5. Altering the Table Structure

Sometimes, you may need to modify the structure of an existing table. The ALTER TABLE statement is used for this purpose.

Adding a New Column

To add a new column named Email to the Employees table:

ALTER TABLE Employees ADD Email VARCHAR(100);

Modifying an Existing Column

To change the data type of the Salary column to INT:

ALTER TABLE Employees MODIFY Salary INT;

Dropping a Column

To remove the Email column from the Employees table:

ALTER TABLE Employees DROP COLUMN Email;

6. Updating Data in the Table

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

Updating a Single Record

To update the salary of the employee with EmployeeID 1:

UPDATE Employees SET Salary = 52000.00 WHERE EmployeeID = 1;

Updating Multiple Records

To give a 10% raise to all employees in the IT department:

UPDATE Employees SET Salary = Salary * 1.10 WHERE Department = 'IT';

7. Deleting Data from the Table

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

Deleting a Single Record

To delete the employee with EmployeeID 3:

DELETE FROM Employees WHERE EmployeeID = 3;

Deleting Multiple Records

To delete all employees in the HR department:

DELETE FROM Employees WHERE Department = 'HR';

Deleting All Records

To delete all records from the Employees table:

DELETE FROM Employees;

8. Advanced SQL Queries

Joins

Joins are used to combine rows from two or more tables based on a related column.

INNER JOIN

To retrieve employees along with their department details (assuming a Departments table exists):

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

LEFT JOIN

To retrieve all employees and their department details, including those without a department:

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Aggregations

Aggregate functions perform a calculation on a set of values and return a single value.

COUNT

To count the number of employees in each department:

SELECT Department, COUNT(*) AS NumberOfEmployees
FROM Employees
GROUP BY Department;

AVG

To calculate the average salary in the company:

SELECT AVG(Salary) AS AverageSalary FROM Employees;

SUM

To calculate the total salary expenditure:

SELECT SUM(Salary) AS TotalSalary FROM Employees;

Subqueries

A subquery is a query nested inside another query.

To find employees whose salary is above the average salary:

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Indexes

Indexes are used to speed up the retrieval of rows from a table.

To create an index on the LastName column:

CREATE INDEX idx_lastname ON Employees (LastName);

Conclusion

Mastering SQL is a fundamental skill for any backend engineer. From creating databases and tables to performing complex queries, SQL provides the tools you need to manage and manipulate data effectively. By understanding and practicing these concepts, you'll be well-equipped to handle a wide range of data-related tasks in your backend development projects.

Remember, the key to mastering SQL is practice. Try creating your own databases, experimenting with different queries, and exploring advanced features like joins, aggregations, and subqueries. Happy querying!