Structured Query Language (SQL) is a standardized programming language used for managing and manipulating relational databases. Whether you are a database administrator, developer, or data analyst, SQL statements are fundamental to accomplishing your tasks. Here are 25 important SQL statements that will help you carry out the majority of your tasks with relevant coding examples for each.

1. SELECT

The SELECT statement is the most basic yet essential SQL command. It retrieves data from a database.

SELECT * FROM Employees;

This statement will select all fields from the Employees table.

2. FROM

The FROM clause specifies the table from which to retrieve the data.

SELECT EmployeeName, EmployeeID FROM Employees;

This will select the EmployeeName and EmployeeID fields from the Employees table.

3. WHERE

The WHERE clause filters the records and fetches only those that fulfill a specified condition.

SELECT * FROM Employees WHERE Salary > 50000;

This statement selects all fields from the Employees table where the Salary is greater than 50000.

4. INSERT INTO

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Employees (EmployeeName, EmployeeID, Salary) VALUES ('John Doe', '1234', 55000);

This statement will insert a new record into the Employees table.

5. UPDATE

The UPDATE statement is used to modify existing records in a table.

UPDATE Employees SET Salary = 60000 WHERE EmployeeID = '1234';

This statement will update the Salary of the employee with EmployeeID 1234.

6. DELETE

The DELETE statement is used to remove existing records from a table.

DELETE FROM Employees WHERE EmployeeID = '1234';

This statement will delete the employee with EmployeeID 1234 from the Employees table.

7. COUNT

The COUNT function returns the number of rows that matches a specified criterion.

SELECT COUNT(EmployeeID) FROM Employees;

This statement will return the total number of employees.

8. AVG

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

SELECT AVG(Salary) FROM Employees;

This statement will return the average salary of all employees.

9. MIN/MAX

The MIN and MAX functions return the lowest and highest values of the selected column, respectively.

SELECT MIN(Salary), MAX(Salary) FROM Employees;

These statements will return the lowest and highest salary from the Employees table.

10. GROUP BY

The GROUP BY statement groups rows with the same value in specified columns into aggregated data.

SELECT Department, COUNT(EmployeeID) FROM Employees GROUP BY Department;

This statement will return the number of employees in each department.

11. HAVING

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. HAVING is typically used with GROUP BY.

SELECT Department, COUNT(EmployeeID) FROM Employees GROUP BY Department HAVING COUNT(EmployeeID) > 10;

This statement will return departments with more than 10 employees.

12. ORDER BY

The ORDER BY keyword sorts the result-set in ascending or descending order.

SELECT * FROM Employees ORDER BY Salary DESC;

This statement will return employees sorted by Salary in descending order.

13. INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

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

This statement will return the employee names along with their respective department names.

14. LEFT JOIN (or RIGHT JOIN)

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL on the right side when there is no match.

SELECT Employees.EmployeeName, Departments.DepartmentName 
FROM Employees 
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This statement will return all the employees along with the names of their departments. If an employee is not assigned to a department, the department name will be returned as NULL.

15. UNION

The UNION operator is used to combine the result-set of two or more SELECT statements (without returning any duplicate values).

SELECT EmployeeName FROM Employees
UNION
SELECT EmployeeName FROM Managers;

This statement will return all unique employee names from both the Employees and Managers tables.

16. CREATE DATABASE

The CREATE DATABASE statement is used to create a new SQL database.

CREATE DATABASE TestDB;

This statement will create a new database named TestDB.

17. CREATE TABLE

The CREATE TABLE statement is used to create a new table in a database.

CREATE TABLE Customers (
    CustomerID int,
    CustomerName varchar(255),
    ContactName varchar(255),
    Country varchar(255),
    City varchar(255)
);

This statement will create a new table named Customers with the specified columns.

18. ALTER TABLE

The ALTER TABLE statement is used to add, delete/drop or modify columns in an existing table.

ALTER TABLE Customers
ADD Email varchar(255);

This statement will add a new column named Email to the Customers table.

19. DROP TABLE

The DROP TABLE statement is used to drop an existing table in a database.

DROP TABLE Customers;

This statement will drop the table named Customers.

20. TRUNCATE TABLE

The TRUNCATE TABLE statement is a Data Definition Language (DDL) operation used to mark the extents of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table.

TRUNCATE TABLE Customers;

This statement will delete all data in the Customers table, but the table structure and its columns, constraints, indexes, and so on, will remain.

21. DISTINCT

The DISTINCT keyword is used to return only distinct (unique) values within the specified column.

SELECT DISTINCT Country FROM Customers;

This statement will return all unique country values from the Customers table.

22. BETWEEN

The BETWEEN operator is used to filter the result set within a certain range. The values can be numbers, text, or dates.

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

This statement will return all rows from the Products table where the price is between 10 and 20.

23. LIKE

The LIKE operator is used in a WHERE clause to search for a specific pattern in a column.

SELECT * FROM Customers WHERE Country LIKE 'A%';

This statement will select all fields from “Customers” where country starts with “A”.

24. IN

The IN operator allows you to specify multiple values in a WHERE clause. It is a shorthand for multiple OR conditions.

SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');

This statement will select all fields from Customers where the country is either ‘Germany’, ‘France’, or ‘UK’.

25. CASE

The CASE statement goes through conditions and returns a value when the first condition is met.

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN "Over 30"
    ELSE "Under 30"
END 
FROM OrderDetails;

This statement will return a new column that specifies whether the quantity is “Over 30” or “Under 30”.

SQL is a powerful language for managing and manipulating databases. The commands listed above, along with their examples, represent some of the most common tasks you’ll perform as a database user. By mastering these SQL statements, you can effectively interact with a database, whether it’s for creating, retrieving, updating, or deleting data.

ABOUT LONDON DATA CONSULTING (LDC)

We, at London Data Consulting (LDC), provide all sorts of Data Solutions. This includes Data Science (AI/ML/NLP), Data Engineer, Data Architecture, Data Analysis, CRM & Leads Generation, Business Intelligence and Cloud solutions (AWS/GCP/Azure).

For more information about our range of services, please visit: https://london-data-consulting.com/services

Interested in working for London Data Consulting, please visit our careers page on https://london-data-consulting.com/careers

More info on: https://london-data-consulting.com