A Guide to SQL Operations: Selecting, Inserting, Updating, Deleting, Grouping, Ordering, Joining, and Using UNION

Structured Query Language (SQL) is a standard language for managing and manipulating databases. This guide will walk you through some common SQL operations using practical examples.

1. Selecting Data from a Table

To retrieve specific data from a database table, you use the `SELECT` statement.

Syntax:

SELECT <Column List> FROM <Table Name> WHERE <Search Condition>;

Example:

SELECT FirstName, LastName, OrderDate FROM Orders WHERE OrderDate > '2010-10-10';

This query selects the `FirstName`, `LastName`, and `OrderDate` columns from the `Orders` table where the `OrderDate` is later than October 10, 2010.

2. Inserting Data into a Table

To add new data to a table, you use the `INSERT INTO` statement.

Syntax:

INSERT INTO <Table Name> (<Column List>) VALUES (<Values>);

Example:

INSERT INTO Orders (FirstName, LastName, OrderDate) VALUES ('John', 'Smith', '2010-10-10');

This query inserts a new row into the `Orders` table with `FirstName` as ‘John’, `LastName` as ‘Smith’, and `OrderDate` as October 10, 2010.

3. Updating Data in a Table

To modify existing data in a table, you use the `UPDATE` statement.

Syntax:

UPDATE <Table Name> SET <Column1> = <Value1>, <Column2> = <Value2>, … WHERE <Search Condition>;

Example:

UPDATE Orders SET FirstName = 'John', LastName = 'Who' WHERE LastName = 'Wo';

This query updates the `FirstName` to ‘John’ and `LastName` to ‘Who’ in the `Orders` table where the `LastName` is ‘Wo’.

4. Deleting Data from a Table

To remove data from a table, you use the `DELETE` statement.

Syntax:

DELETE FROM <Table Name> WHERE <Search Condition>;

Example:

DELETE FROM Orders WHERE OrderDate < '2010-10-10';

This query deletes rows from the `Orders` table where the `OrderDate` is earlier than October 10, 2010.

5. Grouping Data and Using Aggregates

To group data based on one or more columns and perform aggregate functions, you use the `GROUP BY` statement.

Syntax:

SELECT <Column List>, <Aggregate Function>(<Column Name>) FROM <Table Name> WHERE <Search Condition> GROUP BY <Column List>;

Example:

SELECT LastName, SUM(OrderValue) FROM Orders WHERE OrderDate > '2010-10-10' GROUP BY LastName;

This query groups the `Orders` by `LastName` and calculates the sum of `OrderValue` for each `LastName` where the `OrderDate` is later than October 10, 2010.

6. Ordering Data

To sort the result set of a query, you use the `ORDER BY` statement.

Syntax:

SELECT <Column List> FROM <Table Name> WHERE <Search Condition> ORDER BY <Column List>;

Example:

SELECT FirstName, LastName, OrderDate FROM Orders WHERE OrderDate > '2010-10-10' ORDER BY OrderDate;

This query selects `FirstName`, `LastName`, and `OrderDate` from the `Orders` table where the `OrderDate` is later than October 10, 2010, and sorts the results by `OrderDate`.

7. Selecting Data from More Than One Table

To retrieve data from multiple tables based on a related column, you use the `JOIN` statement.

Syntax:

SELECT <Column List> FROM <Table1> JOIN <Table2> ON <Table1>.<Column1> = <Table2>.<Column1>;

Example:

SELECT Orders.LastName, Countries.CountryName FROM Orders JOIN Countries ON Orders.CountryID = Countries.ID;

This query selects `LastName` from the `Orders` table and `CountryName` from the `Countries` table where the `CountryID` in `Orders` matches the `ID` in `Countries`.

8. Using UNION

To combine the result sets of two or more `SELECT` statements, you use the `UNION` operator.

Syntax:

SELECT <Column List> FROM <Table1> UNION SELECT <Column List> FROM <Table2>;

Example:

SELECT FirstName, LastName FROM Orders2010 UNION SELECT FirstName, LastName FROM Orders2011;

This query combines the results of selecting `FirstName` and `LastName` from `Orders2010` with the results of selecting `FirstName` and `LastName` from `Orders2011`.

By mastering these SQL operations, you can effectively manage and manipulate data within your databases, making it easier to retrieve, update, and organize information as needed.

You may also like: