A Beginner‘s Guide to SQL Commands and Sub-Languages

Introduction

SQL, which stands for Structured Query Language, is a fundamental tool for managing and manipulating relational databases. Whether you‘re an aspiring data analyst, web developer, or business intelligence specialist, learning SQL is essential for working with structured data stored in databases.

In this guide, we‘ll introduce you to the basics of SQL and its various sub-languages. You‘ll learn about the different types of SQL commands, see examples of how they‘re used, and gain an understanding of the role SQL plays in interacting with databases. By the end of this post, you‘ll have a solid foundation to start writing your own SQL queries and working with relational databases.

SQL Basics

Before diving into SQL commands, let‘s first review some key concepts related to relational databases. A relational database organizes data into one or more tables. Each table is like a spreadsheet with rows and columns.

A row, also called a record or tuple, represents a single data item in the table. Each row is composed of one or more columns, also referred to as fields or attributes. Columns define the structure of the table and the type of data that can be stored, such as text, numbers, dates, etc.

SQL is used to interact with relational database management systems (RDBMS). Some popular RDBMS that use SQL include:

• MySQL
• PostgreSQL
• Microsoft SQL Server
• Oracle Database

While syntax can vary slightly between database systems, the core SQL commands and concepts are the same.

Now let‘s take a look at the different sub-languages that make up SQL:

• DDL – Data Definition Language
• DML – Data Manipulation Language
• DQL – Data Query Language
• DCL – Data Control Language
• TCL – Transaction Control Language

Each sub-language contains a set of commands for carrying out specific database operations. In the following sections, we‘ll explore each of these sub-languages and their commonly used commands.

Data Definition Language (DDL)

DDL commands are used to define the structure and schema of a database. This includes creating, altering, and deleting database objects like tables.

Some key DDL commands include:

• CREATE – create a new table or database object
• ALTER – modify the structure of an existing table or database object
• DROP – delete a table or database object
• TRUNCATE – remove all data from a table

Here‘s an example of using the CREATE TABLE command to create a new table named "employees":

CREATE TABLE employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100),
  hire_date DATE
);

This SQL statement creates a table with 5 columns: id, first_name, last_name, email, and hire_date. The id column is defined as the primary key, which uniquely identifies each row in the table.

To modify an existing table, you can use the ALTER TABLE command. For example, to add a new salary column to the employees table:

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

And to completely remove a table, use the DROP TABLE command:

DROP TABLE employees;

Be careful when using DROP TABLE, as it will permanently delete the table and all its data from the database.

Data Manipulation Language (DML)

DML commands are used for adding, modifying, and deleting data within database tables. The main DML commands are:

• INSERT – add new rows of data into a table
• UPDATE – modify existing data in a table
• DELETE – remove rows of data from a table

The INSERT command is used to add new records to a table. For example, to add a new employee to the employees table:

INSERT INTO employees (id, first_name, last_name, email, hire_date, salary)
VALUES (1, ‘John‘, ‘Doe‘, ‘[email protected]‘, ‘2022-01-15‘, 75000);

To update an existing employee‘s salary, you would use the UPDATE command:

UPDATE employees
SET salary = 80000
WHERE id = 1;

The WHERE clause specifies which record(s) to update based on a condition, in this case where the id is equal to 1.

Finally, to remove an employee from the table, use the DELETE command:

DELETE FROM employees
WHERE id = 1;

Again, be careful when using DELETE statements, as the deleted data can‘t be recovered unless you have a backup.

Data Query Language (DQL)

DQL, sometimes considered a subset of DML, is used for querying and retrieving data from a database. The most commonly used DQL command is SELECT.

The basic syntax for a SELECT query is:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

For example, to retrieve the first and last names of all employees with a salary greater than 50,000:

SELECT first_name, last_name  
FROM employees
WHERE salary > 50000;

You can also query data from multiple tables using joins. For instance, if you had a separate departments table, you could retrieve the department name along with employee names like:

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Here, the tables are joined based on a foreign key relationship between the employee‘s department_id and the id of the related department.

Data Control Language (DCL)

DCL commands are used for managing database security and user permissions. The two main DCL commands are:

• GRANT – give specific users access privileges to database objects
• REVOKE – remove previously granted access privileges from users

For example, to grant SELECT privileges on the employees table to a user named jane:

GRANT SELECT ON employees TO jane;  

And to later revoke those privileges:

REVOKE SELECT ON employees FROM jane;

DCL is typically used by database administrators to control what actions different users or user groups are allowed to perform on the database.

Transaction Control Language (TCL)

TCL commands are used for managing database transactions. A transaction is a unit of work performed against a database that is treated in a coherent and reliable way.

The main TCL commands are:

• COMMIT – save all database changes made within the current transaction
• ROLLBACK – roll back the changes made by the current transaction
• SAVEPOINT – temporarily save a transaction so you can roll back to that point if needed

Transactions are used to ensure data consistency, even in the event of errors or system failures. For example:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; 
COMMIT;

In this transaction, funds are being transferred from one account to another. By wrapping the two UPDATE statements within a transaction, it ensures that either all or none of the changes are committed to the database.

If an error occurs before the COMMIT statement, a ROLLBACK can be issued to undo the changes made by the UPDATE statements, leaving the database in a consistent state.

Putting It All Together

Now that we‘ve covered the different types of SQL commands, let‘s look at a more complex query that uses several of them together.

Suppose we wanted to retrieve a list of all employees hired in the last year, along with their department name and current salary, ordered by hire date:

SELECT e.first_name, e.last_name, d.department_name, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.hire_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) 
ORDER BY e.hire_date DESC;

Let‘s break this down:

• The SELECT clause specifies the columns to retrieve from the employees and departments tables.
• The FROM clause indicates the employees table will be used, with "e" as its alias.
• The JOIN clause brings in the related department name for each employee.
• The WHERE clause filters the results to only employees hired within the last 1 year.
• Finally, the ORDER BY clause sorts the results by hire_date in descending order.

This example shows how the different SQL sub-languages can work together to query and manipulate data in powerful ways.

When writing your own queries, here are a few best practices to keep in mind:

• Use clear and descriptive names for tables, columns, and aliases.
• Include comments to explain complex queries or business logic.
• Avoid using SELECT * and instead only select the specific columns needed.
• Use LIMIT to restrict the number of rows returned, especially for testing.
• Double-check your WHERE conditions to ensure you‘re filtering correctly.
• Regularly monitor query performance and optimize when needed.

Conclusion

SQL is a powerful language for working with relational databases, and mastering it is key for anyone looking to pursue a career in data. In this guide, we covered the basics of SQL sub-languages and commands, including:

• DDL for defining database structures
• DML for manipulating data within tables
• DQL for querying and retrieving data
• DCL for managing database security
• TCL for controlling database transactions

We looked at examples of commonly used commands and walked through a complex query to demonstrate SQL in action.

To continue learning and practicing SQL, here are some great resources:

• W3Schools SQL Tutorial
• Khan Academy Intro to SQL Course
• SQLZoo Interactive Tutorials
• LeetCode SQL Problems
• DataCamp SQL Courses

As you work on projects, try to incorporate SQL whenever you need to interact with structured data. With time and practice, writing queries will become second nature, and you‘ll be well on your way to becoming an SQL expert.