SQL Basics

Table of Contents

1. Introduction to SQL
2. Data Retrieval: SELECT Statement
3. Data Manipulation: INSERT, UPDATE, DELETE
4. Filtering Data: WHERE Clause
5. Sorting Data: ORDER BY Clause
6. Joining Tables
7. Aggregating Data
8. Subqueries
9. Indexes
10. Conclusion

Introduction to SQL

SQL is a domain-specific language used for managing and manipulating relational databases. It allows you to perform various operations like creating tables, inserting records, and querying data. SQL is essential for anyone who works with databases, as it provides a standardized way to interact with them.

Key Concepts

• Database: A collection of tables that store data.
• Table: A set of rows and columns that hold data.
• Row: A single record in a table.
• Column: A field in a table.

Data Retrieval: SELECT Statement

The SELECT statement is used to query data from one or more tables. The basic syntax is as follows:

SELECT column1, column2, ...
FROM table_name;

Examples

-- Select all columns from the 'users' table
SELECT * FROM users;
 
-- Select specific columns from the 'users' table
SELECT username, email FROM users;

Data Manipulation: INSERT, UPDATE, DELETE

These statements allow you to add, modify, or remove data in a table.

INSERT

To insert data into a table:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

UPDATE

To update existing records:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE some_column = some_value;

DELETE

To delete records:

DELETE FROM table_name WHERE some_column = some_value;

Filtering Data: WHERE Clause

The WHERE clause is used to filter records based on specific conditions.

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

Examples

-- Select users older than 21
SELECT * FROM users WHERE age > 21;

Sorting Data: ORDER BY Clause

The ORDER BY clause is used to sort the result set based on one or more columns.

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Joining Tables

SQL joins are used to combine rows from two or more tables based on a related column.

Types of Joins

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables. This is the most common type of join.

Syntax

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example

Let's say we have two tables: Orders and Customers, and we want to find all orders made by a specific customer.

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN keyword returns all records from the left table, and the matching records from the right table. If there's no match, NULL values are returned for right table's columns.

Syntax

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Example

To find all customers and their orders, but also include customers who have not placed any orders:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

RIGHT JOIN (or RIGHT OUTER JOIN)

The RIGHT JOIN keyword returns all records from the right table, and the matching records from the left table. If there's no match, NULL values are returned for left table's columns.

Syntax

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example

To find all orders and the customers who placed them, but also include orders that have not been placed by any customer:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all records when there is a match in either the left or right table records.

Syntax

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

Example

To find all customers and their orders, including customers who have not placed orders and orders that have not been placed by any customer:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Aggregating Data

SQL provides various aggregate functions like COUNT, SUM, MIN, MAX, and AVG to perform calculations on a set of values.

COUNT

The COUNT function returns the number of rows that match a specified condition.

Syntax

SELECT COUNT(column)
FROM table
WHERE condition;

Example

To find the total number of orders in the Orders table:

SELECT COUNT(OrderID)
FROM Orders;

SUM

The SUM function returns the total sum of a numeric column.

Syntax

SELECT SUM(column)
FROM table
WHERE condition;

Example

To find the total amount of money spent by all customers in the Orders table:

SELECT SUM(TotalAmount)
FROM Orders;

MIN

The MIN function returns the smallest value of the selected column.

Syntax

SELECT MIN(column)
FROM table
WHERE condition;

Example

To find the lowest price among all products in the Products table:

SELECT MIN(Price)
FROM Products;

MAX

The MAX function returns the largest value of the selected column.

Syntax

SELECT MAX(column)
FROM table
WHERE condition;

Example

To find the highest price among all products in the Products table:

SELECT MAX(Price)
FROM Products;

AVG

The AVG function returns the average value of a numeric column.

Syntax

SELECT AVG(column)
FROM table
WHERE condition;

Example

To find the average price of all products in the Products table:

SELECT AVG(Price)
FROM Products;

Understanding how to use aggregate functions effectively can greatly assist in data analysis and reporting tasks. These functions allow you to summarize and analyze your data in various ways, providing valuable insights into the information stored in your database.

Subqueries

A subquery is a SQL query within another query. Subqueries are used to retrieve data that will be used in the main query as a condition to further restrict the data retrieved.

Types of Subqueries

Scalar Subquery

A scalar subquery returns a single value. It is often used in the SELECT, WHERE, or HAVING clause.

Syntax

SELECT column1, column2, (SELECT column FROM table2 WHERE condition) AS alias
FROM table1
WHERE condition;

Example

To find the name of each customer along with their most recent order date:

SELECT CustomerName, (SELECT MAX(OrderDate) FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) AS MostRecentOrder
FROM Customers;

Row Subquery

A row subquery returns zero or more rows but only one column. It is often used with operators like IN, NOT IN, EXISTS, and NOT EXISTS.

Syntax

SELECT column
FROM table1
WHERE column operator (SELECT column FROM table2 WHERE condition);

Example

To find all customers who have placed an order:

SELECT CustomerName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

Column Subquery

A column subquery returns one or more columns and is often used in the FROM clause.

Syntax

SELECT column1, column2
FROM (SELECT column FROM table2 WHERE condition) AS alias
WHERE condition;

Example

To find the average price of all products that have been ordered:

SELECT AVG(Price)
FROM (SELECT DISTINCT ProductID FROM Orders) AS OrderedProducts;

Table Subquery

A table subquery returns zero or more rows and columns. It is often used in the FROM clause to replace a table.

Syntax

SELECT column1, column2
FROM (SELECT column1, column2 FROM table2 WHERE condition) AS alias
WHERE condition;

Example

To find the total number of unique products ordered by each customer:

SELECT CustomerID, COUNT(DISTINCT ProductID)
FROM (SELECT CustomerID, ProductID FROM Orders) AS UniqueOrders
GROUP BY CustomerID;

Understanding how to use subqueries effectively can significantly enhance your SQL querying capabilities. Subqueries allow you to perform more complex operations and retrieve more specific data, providing a powerful tool for data analysis and manipulation.

Indexes

An index is a database object that improves the speed of data retrieval operations at the cost of additional storage and decreased performance on data modification operations.

Types of Indexes

Single-Column Index

A single-column index is created on a single table column. It's the simplest form of an index and is useful for queries that retrieve data based on a single column.

Syntax

CREATE INDEX index_name
ON table_name (column_name);

Example

To create an index on the CustomerID column of the Customers table:

CREATE INDEX idx_customer_id
ON Customers (CustomerID);

Composite Index

A composite index is created on two or more columns of a table. It's useful for queries that filter data based on multiple columns.

Syntax

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Example

To create a composite index on the FirstName and LastName columns of the Customers table:

CREATE INDEX idx_name
ON Customers (FirstName, LastName);

Unique Index

A unique index ensures that all values in the indexed columns are unique. It is often used to enforce the uniqueness constraint on columns.

Syntax

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

Example

To create a unique index on the Email column of the Customers table:

CREATE UNIQUE INDEX idx_unique_email
ON Customers (Email);

Full-Text Index

A full-text index is used to perform full-text searches on text-based columns. It's useful for queries that involve text matching or searching.

Syntax

CREATE FULLTEXT INDEX index_name
ON table_name (column_name);

Example

To create a full-text index on the Description column of the Products table:

CREATE FULLTEXT INDEX idx_fulltext_description
ON Products (Description);

Considerations

• Storage: Indexes consume disk space. The more indexes you have, the more storage you'll need.
• Data Modification: While indexes speed up data retrieval, they can slow down data modification operations like INSERT, UPDATE, and DELETE.
• Maintenance: Indexes require regular maintenance to remain efficient.

Understanding how to use indexes effectively can significantly improve the performance of your database queries. However, it's crucial to strike a balance between read and write operations to ensure overall database performance.

Conclusion

Understanding SQL basics is crucial for anyone who interacts with databases. This section has provided you with the foundational knowledge needed to perform essential database operations using SQL.