SQL Examples

Welcome to the SQL Examples page by The Coding College! This guide offers practical examples of SQL queries to help you learn how to interact with databases effectively. From basic operations to advanced techniques, this page is your go-to resource for hands-on SQL learning.

Table Setup

Before diving into examples, here’s a simple table we’ll use:

Table: Employees

Basic SQL Examples

1. Selecting All Columns

Retrieve all data from the Employees table.

SELECT * FROM Employees;  

2. Selecting Specific Columns

Retrieve only the names and departments of employees.

SELECT Name, Department FROM Employees;  

3. Using DISTINCT

Find unique departments in the Employees table.

SELECT DISTINCT Department FROM Employees;  

Filtering Data

4. Using WHERE Clause

Find employees in the IT department.

SELECT * FROM Employees WHERE Department = 'IT';  

5. Using AND Operator

Find employees in the IT department earning more than 50000.

SELECT * FROM Employees WHERE Department = 'IT' AND Salary > 50000;  

6. Using OR Operator

Find employees in HR or IT departments.

SELECT * FROM Employees WHERE Department = 'HR' OR Department = 'IT';  

7. Using NOT Operator

Exclude employees from the HR department.

SELECT * FROM Employees WHERE NOT Department = 'HR';  

Sorting and Limiting Data

8. Sorting with ORDER BY

List employees sorted by salary in descending order.

SELECT * FROM Employees ORDER BY Salary DESC;  

9. Limiting Results

Fetch the top 3 highest-earning employees (MySQL).

SELECT * FROM Employees ORDER BY Salary DESC LIMIT 3;  

Aggregating Data

10. Using COUNT()

Count the total number of employees.

SELECT COUNT(*) AS TotalEmployees FROM Employees;  

11. Using AVG()

Find the average salary of employees.

SELECT AVG(Salary) AS AverageSalary FROM Employees;  

12. Using SUM()

Calculate the total salary paid.

SELECT SUM(Salary) AS TotalSalary FROM Employees;  

Joining Tables

13. INNER JOIN

Combine Employees with a Departments table to fetch department names.

SELECT Employees.Name, Departments.DepartmentName  
FROM Employees  
INNER JOIN Departments ON Employees.Department = Departments.DepartmentID;  

Advanced Queries

14. Using Subqueries

Find employees earning more than the average salary.

SELECT * FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);  

15. Using CASE Statements

Categorize employees based on their salary.

SELECT Name,  
       Salary,  
       CASE  
           WHEN Salary > 50000 THEN 'High'  
           WHEN Salary BETWEEN 40000 AND 50000 THEN 'Medium'  
           ELSE 'Low'  
       END AS SalaryCategory  
FROM Employees;  

Managing Tables

16. Creating a Table

Create a new table for storing department details.

CREATE TABLE Departments (  
    DepartmentID INT PRIMARY KEY,  
    DepartmentName VARCHAR(50)  
);  

17. Inserting Data

Add a new employee to the Employees table.

INSERT INTO Employees (EmployeeID, Name, Age, Department, Salary, JoiningDate)  
VALUES (6, 'Mark Lee', 30, 'Finance', 52000, '2022-05-12');  

18. Updating Data

Increase the salary of all HR employees by 10%.

UPDATE Employees  
SET Salary = Salary * 1.1  
WHERE Department = 'HR';  

19. Deleting Data

Remove employees who left the company before 2020.

DELETE FROM Employees WHERE JoiningDate < '2020-01-01';  

Conclusion

SQL examples are an excellent way to solidify your understanding of database operations. By practicing these queries, you can build the skills needed for real-world applications.