Introduction to SQL: What It Is and How It Works, with Practical Examples

SQL (Structured Query Language) is a language used for managing and manipulating data in relational databases such as MySQL, PostgreSQL, Oracle, and SQL Server. SQL allows you to perform various operations, such as creating tables, inserting data, updating and deleting information, and querying data. In this article, we’ll explore the main functionalities of SQL through practical examples.

1. Creating a Table in SQL

To start working with data, the first step is to create a table. A table is a structure organized in rows and columns, where rows represent records and columns represent fields (attributes) of those records.

Example

Suppose we’re creating a table to store information about employees in a company. Here’s how to create a table called employees:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

In this example:

• employee_id is an integer that serves as the primary key, meaning each employee has a unique identifier.

• first_name and last_name are character fields to store employees’ names.

• department stores the department name as a string.

• salary stores the employee’s salary as a decimal value.

  2. Inserting Data into a Table

  Once the table is created, you can insert data into it. The INSERT INTO statement is used for this purpose.

Example

To add an employee to the employees table:

INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES (1, 'John', 'Doe', 'Engineering', 60000.00);

Here, we’re adding a record for an employee named John Doe who works in the Engineering department with a salary of $60,000.

3. Querying Data with SELECT

The SELECT statement is used to retrieve data from a table. You can specify which columns to retrieve and apply conditions to filter results.

Example

To retrieve all employees’ names and departments:

SELECT first_name, last_name, department
FROM employees;

To retrieve only those employees in the "Engineering" department:

SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering';

In this example, the WHERE clause filters records to include only those where department is "Engineering."

4. Updating Data

To modify existing records in a table, you use the UPDATE statement. This is helpful if you need to correct information or adjust values.

Example

Suppose we want to give all employees in the Engineering department a 5% raise. We can update their salary with the following command:

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Engineering';

This command increases the salary of each employee in the Engineering department by 5%.

5. Deleting Data

To remove records from a table, you use the DELETE statement. Be cautious with this operation, as deleted records cannot be recovered.

Example

To delete a specific employee by their employee_id:

DELETE FROM employees
WHERE employee_id = 1;

This command deletes the employee with employee_id 1 from the employees table.

6. Using SQL Functions

SQL includes various built-in functions that allow you to perform calculations, aggregate data, and manipulate strings or dates.

Example: COUNT and AVG

To find out how many employees are in each department:

 SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department;

This command groups employees by department and counts the number of employees in each department.

To calculate the average salary of all employees:

SELECT AVG(salary) AS average_salary
FROM employees;

The AVG function calculates the average of the salary column.

7. Joining Tables

Often, data is spread across multiple tables, and you need to combine information from these tables. This is done using SQL JOINs.

Example

Suppose we have another table, departments, that stores information about each department:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50),
    manager_id INT
);

To retrieve each employee’s name along with their department name, we can join the employees and departments tables:

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
JOIN departments
ON employees.department = departments.department_name;

This query joins the employees table with the departments table on the department_name field, showing each employee’s name and department name.

Conclusion

SQL is a powerful and versatile language for managing relational databases. By understanding the basics of creating tables, inserting data, querying, updating, deleting, and joining tables, you can effectively manage and manipulate data. SQL also provides advanced features, such as functions and subqueries, that enable complex data analysis and reporting.

With practice and experimentation, SQL can become an invaluable tool for anyone working with data.