SQL Examples


SQL Examples

Here are practical SQL examples to demonstrate how various commands and concepts work:

1. SELECT Statement

Retrieve all rows from a table:

sql
SELECT * FROM Employees;

Retrieve specific columns:

sql
SELECT FirstName, LastName FROM Employees;

2. SELECT DISTINCT

Retrieve unique job titles from the table:

sql
SELECT DISTINCT JobTitle FROM Employees;

3. WHERE Clause

Filter employees by department:

sql
SELECT * FROM Employees WHERE Department = 'Sales';

Find employees hired after 2020:

sql
SELECT * FROM Employees WHERE HireDate > '2020-01-01';

4. ORDER BY

Sort employees by last name in ascending order:

sql
SELECT * FROM Employees ORDER BY LastName ASC;

Sort by salary in descending order:

sql
SELECT * FROM Employees ORDER BY Salary DESC;

5. INSERT INTO

Add a new employee to the table:

sql
INSERT INTO Employees (FirstName, LastName, Department, Salary) 
VALUES ('John', 'Doe', 'IT', 60000);

6. UPDATE

Update the salary of an employee:

sql
UPDATE Employees 
SET Salary = 70000 
WHERE EmployeeID = 1;

7. DELETE

Remove an employee from the table:

sql
DELETE FROM Employees 
WHERE EmployeeID = 5;

8. JOIN

Inner join to find employees with matching department details:

sql
SELECT Employees.FirstName, Departments.DepartmentName 
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

9. GROUP BY and HAVING

Count employees in each department:

sql
SELECT Department, COUNT(*) AS EmployeeCount 
FROM Employees 
GROUP BY Department;

Filter groups with more than 5 employees:

sql
SELECT Department, COUNT(*) AS EmployeeCount 
FROM Employees 
GROUP BY Department
HAVING COUNT(*) > 5;

10. AGGREGATE FUNCTIONS

Find the average salary in the IT department:

sql
SELECT AVG(Salary) AS AvgSalary 
FROM Employees 
WHERE Department = 'IT';

Get the total salary expense for all employees:

sql
SELECT SUM(Salary) AS TotalSalary 
FROM Employees;

11. LIKE and Wildcards

Find employees with first names starting with "J":

sql
SELECT * FROM Employees WHERE FirstName LIKE 'J%';

Find employees whose names contain "ohn":

sql
SELECT * FROM Employees WHERE FirstName LIKE '%ohn%';

12. IN Clause

Filter employees working in specific departments:

sql
SELECT * FROM Employees WHERE Department IN ('HR', 'IT', 'Sales');

13. BETWEEN

Find employees with salaries between $50,000 and $70,000:

sql
SELECT * FROM Employees WHERE Salary BETWEEN 50000 AND 70000;

14. CASE

Display salary grades based on salary amounts:

sql
SELECT FirstName, LastName, 
CASE 
    WHEN Salary > 80000 THEN 'High'
    WHEN Salary BETWEEN 50000 AND 80000 THEN 'Medium'
    ELSE 'Low'
END AS SalaryGrade
FROM Employees;

15. CREATE TABLE

Create a new table for departments:

sql
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100),
    ManagerID INT
);

16. ALTER TABLE

Add a new column to store phone numbers:

sql
ALTER TABLE Employees ADD PhoneNumber VARCHAR(15);

Drop a column:

sql
ALTER TABLE Employees DROP COLUMN PhoneNumber;

17. JOIN Multiple Tables

Combine employee and department data along with projects:

sql
SELECT Employees.FirstName, Departments.DepartmentName, Projects.ProjectName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
INNER JOIN Projects ON Employees.EmployeeID = Projects.EmployeeID;

18. STORED PROCEDURE

Create a procedure to retrieve employee details by department:

sql
CREATE PROCEDURE GetEmployeesByDepartment(@DepartmentName VARCHAR(50))
AS
BEGIN
    SELECT * FROM Employees WHERE Department = @DepartmentName;
END;

Execute the procedure:

sql
EXEC GetEmployeesByDepartment 'Sales';

19. TRANSACTIONS

Perform operations with rollback capability:

sql
START TRANSACTION;

UPDATE Employees SET Salary = 70000 WHERE EmployeeID = 1;

ROLLBACK; -- Reverts the above update
COMMIT;   -- Saves changes

These examples cover a variety of scenarios to help you master SQL queries and their practical applications.