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.
