Below is a summary of the most important concepts in SQL (Structured Query Language), along with examples for each concept. SQL is a standard language for managing and manipulating relational databases.
---
### 1. **What is SQL?**
SQL is a language used to interact with relational databases. It allows users to create, read, update, and delete data, as well as manage database structures.
---
### 2. **Key SQL Concepts and Examples**
#### **A. Basic SQL Commands (CRUD Operations)**
SQL is primarily used for CRUD operations: Create, Read, Update, and Delete.
1. **CREATE: Creating Databases, Tables, or Other Objects**
- Used to define the structure of a database or table.
- Example: Creating a table named `employees`.
```sql
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10, 2),
hire_date DATE
);
```
2. **INSERT: Adding Data to a Table**
- Used to insert new records into a table.
- Example: Inserting a new employee record.
```sql
INSERT INTO employees (id, first_name, last_name, salary, hire_date)
VALUES (1, 'John', 'Doe', 50000.00, '2023-01-15');
```
3. **SELECT: Retrieving Data**
- Used to query and retrieve data from a database.
- Example: Retrieve all employees with a salary greater than 40,000.
```sql
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 40000;
```
4. **UPDATE: Modifying Data**
- Used to update existing records in a table.
- Example: Increase the salary of employee with ID 1 by 10%.
```sql
UPDATE employees
SET salary = salary * 1.10
WHERE id = 1;
```
5. **DELETE: Removing Data**
- Used to delete records from a table.
- Example: Delete an employee with ID 1.
```sql
DELETE FROM employees
WHERE id = 1;
```
---
#### **B. Filtering and Sorting Data**
1. **WHERE Clause: Filtering Records**
- Used to filter records based on a condition.
- Example: Retrieve employees hired after January 1, 2023.
```sql
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date > '2023-01-01';
```
2. **ORDER BY: Sorting Results**
- Used to sort query results in ascending (`ASC`) or descending (`DESC`) order.
- Example: Sort employees by salary in descending order.
```sql
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;
```
3. **LIKE: Pattern Matching**
- Used to search for patterns in data (e.g., using wildcards `%` and `_`).
- Example: Find employees whose first name starts with 'J'.
```sql
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'J%';
```
---
#### **C. Aggregate Functions**
Aggregate functions perform calculations on a set of values and return a single result.
1. **COUNT: Counting Records**
- Example: Count the number of employees.
```sql
SELECT COUNT(*) AS total_employees
FROM employees;
```
2. **SUM: Summing Values**
- Example: Calculate the total salary of all employees.
```sql
SELECT SUM(salary) AS total_salary
FROM employees;
```
3. **AVG: Calculating Average**
- Example: Find the average salary of employees.
```sql
SELECT AVG(salary) AS average_salary
FROM employees;
```
4. **MAX/MIN: Finding Maximum/Minimum Values**
- Example: Find the highest and lowest salaries.
```sql
SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary
FROM employees;
```
---
#### **D. Joins: Combining Data from Multiple Tables**
Joins are used to combine rows from two or more tables based on a related column.
1. **INNER JOIN: Matching Records in Both Tables**
- Example: Join `employees` and `departments` tables to get employee names and their department names.
```sql
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
```
2. **LEFT JOIN: All Records from Left Table**
- Example: Retrieve all employees, including those without a department.
```sql
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
```
3. **RIGHT JOIN: All Records from Right Table**
- Example: Retrieve all departments, including those without employees.
```sql
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
```
4. **FULL JOIN: All Records from Both Tables**
- Example: Retrieve all employees and departments, regardless of matches.
```sql
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.id;
```
---
#### **E. Grouping Data**
1. **GROUP BY: Aggregating Data by Groups**
- Used to group rows that have the same values in specified columns.
- Example: Count the number of employees in each department.
```sql
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
```
2. **HAVING: Filtering Grouped Data**
- Used to filter groups based on a condition (similar to `WHERE` but for groups).
- Example: Find departments with more than 5 employees.
```sql
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;
```
---
#### **F. Subqueries**
A subquery is a query nested inside another query.
- Example: Find employees with a salary greater than the average salary.
```sql
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
```
---
#### **G. Constraints: Enforcing Data Integrity**
Constraints ensure the accuracy and reliability of data in a table.
1. **PRIMARY KEY: Uniquely Identifies Records**
- Example: `id` is the primary key in the `employees` table.
```sql
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50)
);
```
2. **FOREIGN KEY: Enforces Referential Integrity**
- Example: `department_id` in `employees` references the `id` in `departments`.
```sql
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
```
3. **NOT NULL: Ensures a Column Cannot Have NULL Values**
- Example: Ensure `first_name` is always provided.
```sql
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL
);
```
4. **UNIQUE: Ensures All Values in a Column Are Unique**
- Example: Ensure email addresses are unique.
```sql
CREATE TABLE employees (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
```
---
#### **H. Indexes: Improving Query Performance**
Indexes speed up data retrieval but may slow down data modification.
- Example: Create an index on the `last_name` column.
```sql
CREATE INDEX idx_last_name ON employees(last_name);
```
---
#### **I. Transactions: Ensuring Data Consistency**
Transactions ensure that a series of SQL operations either all succeed or all fail.
- Example: Transfer $1,000 from one account to another.
```sql
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
COMMIT;
```
- If an error occurs, you can rollback:
```sql
ROLLBACK;
```
---
### 3. **Key Takeaways**
- SQL is essential for managing relational databases.
- Core operations include creating, reading, updating, and deleting data (CRUD).
- Joins, subqueries, and aggregate functions allow complex data manipulation.
- Constraints and transactions ensure data integrity.
- Indexes improve performance but should be used judiciously.
This summary covers the foundational concepts of SQL, providing a solid starting point for working with databases.