Understanding SQL Fundamentals

Structured Query Language (SQL) is the backbone of relational database management systems (RDBMS). Whether you are a novice in the world of databases or looking to brush up on your SQL skills, understanding the basics of SQL is crucial. This article will walk you through the fundamental concepts of SQL, enriched with examples to solidify your understanding.

Basic SQL Commands

SQL can be divided into several categories of commands:

• DDL (Data Definition Language): Deals with database structure creation and modification.
• DML (Data Manipulation Language): Deals with data manipulation.
• DCL (Data Control Language): Deals with rights, permissions, and other controls of the database system.

Let’s explore each of these with examples.

Data Definition Language (DDL)

CREATE TABLE

The CREATE TABLE statement is used to create a new table in the database.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(100)
);

ALTER TABLE

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

ALTER TABLE employees
ADD salary DECIMAL(10, 2);

DROP TABLE

The DROP TABLE statement is used to delete a table and its data permanently.

DROP TABLE employees;

Data Manipulation Language (DML)

INSERT INTO

The INSERT INTO statement is used to insert new records into a table.

INSERT INTO employees (id, name, age, department, salary)
VALUES (1, 'John Doe', 30, 'Engineering', 70000.00);

SELECT

The SELECT statement is used to query the database and retrieve data from one or more tables.

SELECT name, department FROM employees WHERE age > 25;

UPDATE

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

UPDATE employees
SET salary = 75000.00
WHERE id = 1;

DELETE

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

DELETE FROM employees WHERE id = 1;

Data Control Language (DCL)

GRANT

The GRANT statement is used to provide access or privileges to the database objects.

GRANT SELECT, INSERT ON employees TO user_name;

REVOKE

The REVOKE statement is used to withdraw access or privileges.

REVOKE SELECT, INSERT ON employees FROM user_name;

SQL Functions and Operators

Aggregate Functions

SQL includes aggregate functions that allow you to perform calculations on multiple values to produce a single result.

• AVG(): Returns the average value.

SELECT AVG(salary) FROM employees;

• COUNT(): Returns the number of rows that match a specified criterion.

SELECT COUNT(*) FROM employees WHERE department = 'Engineering';

• MAX() and MIN(): Return the highest and lowest values.

SELECT MAX(salary), MIN(salary) FROM employees;

• SUM(): Returns the total sum of a numeric column.

SELECT SUM(salary) FROM employees WHERE department = 'Engineering';

String Functions

• CONCAT(): Concatenates two or more strings.

SELECT CONCAT(name, ' works in ', department) AS employee_details FROM employees;

• SUBSTRING(): Extracts a substring from a string.

SELECT SUBSTRING(name, 1, 4) AS short_name FROM employees;

Joining Tables

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

• INNER JOIN: Returns records that have matching values in both tables.

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department = departments.department_id;

• LEFT JOIN: Returns all records from the left table and the matched records from the right table. The result is NULL from the right side if there is no match.

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department = departments.department_id;

• RIGHT JOIN: Returns all records from the right table and the matched records from the left table. The result is NULL from the left side when there is no match.

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department = departments.department_id;

Subqueries

A subquery is a query nested inside another query. They can be used in SELECT, INSERT, UPDATE, or DELETE statements.

SELECT name
FROM employees
WHERE department = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Engineering'
);

Understanding SQL fundamentals is essential for anyone working with databases. By mastering the basics—such as creating, modifying, and querying tables—you will be well-equipped to handle more complex database operations. This guide provides a solid foundation, and the included examples should help clarify how these SQL commands work in practice. As you continue to practice and explore more advanced features, your proficiency with SQL will grow, making you a more effective and efficient data handler.