SQL Commands

SQL commands allow you to retrieve, modify, and delete data from one or more tables in a database.

In this post, we’ll outline SQL commands, grouped from easiest to hardest, and provide examples for each.

Basic SQL Commands

The basic SQL commands are the most fundamental commands for managing data in a database.

These commands are easy to use and are a good starting point for beginners.

1. SELECT

The SELECT command retrieves data from one or more tables in a database.

The example code below retrieves all columns and rows from the employees table.

SELECT * 
FROM employees;

2. INSERT

The INSERT command is used to add new rows of data to a table in a database.

Here’s an example query that inserts a new employee into a table called “employees”:

INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'johndoe@example.com');

3. UPDATE

The UPDATE command is used to modify existing rows of data in a table in a database.

This command updates the salary column for the employee with an employee_id of 1 to 60,000 in the employees table.

UPDATE employees
SET salary = 60000
WHERE employee_id = 1;

4. DELETE:

The DELETE command is used to remove one or more rows of data from a table in a database.

This command removes the row with an employee_id of 2 from the employees table.

DELETE FROM employees
WHERE employee_id = 2;

5. WHERE

The WHERE command is used to filter data based on a specified condition.

This command retrieves the first_name and last_name columns from the employees table where the department column equals ‘Sales’.

DELETE FROM employees
WHERE employee_id = 2;

Intermediate SQL Commands:

Joins

Joins allow you to retrieve data from two or more tables in a database based on a specified condition.

Joins are a bit more complex than basic SQL commands, but they are an essential part of querying data in a relational database.

1. INNER JOIN:

The INNER JOIN command is used to retrieve data from two or more tables in a database based on a specified condition.

Here’s an example query that selects all orders and their corresponding customers from two tables called “orders” and “customers”:

SELECT *
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.id;

2. LEFT JOIN:

The LEFT JOIN command is used to retrieve all data from the left table and matching data from the right table in a database based on a specified condition.

Here’s an example query that selects all customers and their corresponding orders, even if the customer has no orders, from two tables called “customers” and “orders”:

SELECT *
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;

3. RIGHT JOIN:

The RIGHT JOIN command is used to retrieve all data from the right table and matching data from the left table in a database based on a specified condition.

Here’s an example query that selects all orders and their corresponding customers, even if the order has no customer, from two tables called “orders” and “customers”:

SELECT *
FROM orders
RIGHT JOIN customers
ON orders.customer_id = customers.id;

4. FULL OUTER JOIN:

The FULL OUTER JOIN command is used to retrieve all data from both tables in a database based on a specified condition.

Here’s an example query that selects all customers and orders from two tables called “customers” and “orders”:

SELECT *
FROM customers
FULL OUTER JOIN orders
ON customers.id = orders.customer_id;

Advanced SQL Commands:

Subqueries:

Subqueries allow you to use the results of one SELECT statement as the input for another SELECT, INSERT, UPDATE, or DELETE statement.

Subqueries are more complex than basic SQL commands, but they are a powerful tool for querying data in a relational database.

1. SELECT SUB QUERY:

A subquery using SELECT is used to perform a nested query that retrieves data from one or more tables based on a condition.

Here’s an example query that uses a subquery to retrieve all orders for customers with a particular region:

SELECT *
FROM orders
WHERE customer_id IN (
    SELECT id
    FROM customers
    WHERE region = 'West'
);

2. INSERT SUB QUERY

A subquery using INSERT is used to insert data into a table based on a SELECT statement that retrieves data from another table. Here’s an example query that uses a subquery to insert all orders for customers with a particular region into a new table:

INSERT INTO west_orders (order_id, customer_id, order_date, total)
SELECT order_id, customer_id, order_date, total
FROM orders
WHERE customer_id IN (
    SELECT id
    FROM customers
    WHERE region = 'West'
);

3. UPDATE SUB QUERY

A subquery using UPDATE is used to modify data in a table based on a SELECT statement that retrieves data from another table. Here’s an example query that uses a subquery to update all orders for customers with a particular region to a new order status:

UPDATE orders
SET status = 'Pending'
WHERE customer_id IN (
    SELECT id
    FROM customers
    WHERE region = 'West'
);

4. DELETE SUB QUERY

A subquery using DELETE is used to delete data from a table based on a SELECT statement that retrieves data from another table. Here’s an example query that uses a subquery to delete all orders for customers with a particular region:

DELETE FROM orders
WHERE customer_id IN (
    SELECT id
    FROM customers
    WHERE region = 'West'
);

Conclusion:

SQL commands are a fundamental tool for managing and querying data in a relational database.

Understanding the different types of SQL commands, their purpose, and their syntax can help you write more efficient and effective queries.

By selecting the appropriate command and using best practices for using SQL commands, you can manipulate and analyze your data more effectively.

Whether you’re a beginner or an advanced user, SQL commands are an essential part of working with data in a relational database.