Brushing Up Basic SQL Syntax

Structured Query Language (SQL) is the foundation of database management and is essential for interacting with relational databases. SQL allows you to perform tasks such as querying data, updating records, and managing database structures. This blog will cover all major SQL keywords except joins and window functions, with practical examples to illustrate each concept.

1. SELECT: Retrieving Data

The SELECT keyword is used to query data from one or more tables in a database.

SELECT column1, column2 
FROM table_name;

Example:

SELECT first_name, last_name 
FROM employees;

This query will retrieve the first_name and last_name columns from the employees table.

2. WHERE: Filtering Data

The WHERE clause is used to filter records that meet specific conditions.

SELECT column1, column2
FROM table_name
WHERE condition;

Example:

SELECT first_name, last_name
FROM employees
WHERE department = 'Sales';

This will return employees who work in the Sales department.

3. DISTINCT: Removing Duplicates

The DISTINCT keyword is used to return only unique values.

SELECT DISTINCT column1
FROM table_name;

Example:

SELECT DISTINCT department
FROM employees;

This will return a list of unique departments from the employees table.

4. ORDER BY: Sorting Results

The ORDER BY keyword is used to sort the result set in either ascending (ASC) or descending (DESC) order.

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

Example:

SELECT first_name, last_name
FROM employees
ORDER BY last_name ASC;

This will return employee names sorted by their last names in ascending order.

5. GROUP BY: Grouping Data

GROUP BY is used to group rows that share a common field, often combined with aggregate functions (like COUNT, SUM, AVG).

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

Example:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

This query counts the number of employees in each department.

6. HAVING: Filtering Grouped Data

HAVING is similar to WHERE but is used to filter groups created by GROUP BY.

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > value;

Example:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

This will return departments that have more than 10 employees.

7. INSERT INTO: Inserting Data

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

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

Example:

INSERT INTO employees (first_name, last_name, department)
VALUES ('John', 'Doe', 'Sales');

This query adds a new employee to the employees table.

8. UPDATE: Updating Data

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

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Example:

UPDATE employees
SET department = 'Marketing'
WHERE employee_id = 101;

This updates the department of the employee with employee_id = 101.

9. DELETE: Deleting Data

The DELETE statement removes rows from a table.

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM employees
WHERE employee_id = 102;

This deletes the employee with employee_id = 102.

10. LIMIT: Limiting Result Set

LIMIT is used to restrict the number of rows returned by a query.

SELECT column1, column2
FROM table_name
LIMIT number;

Example:

SELECT first_name, last_name
FROM employees
LIMIT 5;

This returns the first 5 employees from the employees table.

11. BETWEEN: Filtering a Range of Values

The BETWEEN keyword allows filtering of records within a specific range.

SELECT column1, column2
FROM table_name
WHERE column1 BETWEEN value1 AND value2;

Example:

SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 60000;

This returns employees whose salary is between 40,000 and 60,000.

12. LIKE: Pattern Matching

The LIKE keyword is used to search for a specified pattern in a column.

SELECT column1, column2
FROM table_name
WHERE column1 LIKE pattern;

Example:

SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'J%';

This returns employees whose first name starts with ‘J’.

13. IN: Matching a List of Values

The IN keyword is used to specify multiple possible values for a column.

SELECT column1, column2
FROM table_name
WHERE column1 IN (value1, value2, value3);

Example:

SELECT first_name, department
FROM employees
WHERE department IN ('Sales', 'Marketing');

This returns employees working in the Sales or Marketing department.

14. IS NULL / IS NOT NULL: Checking for Null Values

IS NULL and IS NOT NULL are used to check for null (empty) values in a column.

SELECT column1, column2
FROM table_name
WHERE column1 IS NULL;

Example:

SELECT first_name, last_name
FROM employees
WHERE middle_name IS NULL;

This returns employees who don’t have a middle name.

15. CASE: Conditional Logic in Queries

The CASE statement is used to return conditional values in SQL.

SELECT column1, 
    CASE 
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result_default
    END AS alias_name
FROM table_name;

Example:

SELECT first_name, 
    CASE 
        WHEN department = 'Sales' THEN 'Sales Team'
        WHEN department = 'Marketing' THEN 'Marketing Team'
        ELSE 'Other'
    END AS team
FROM employees;

This categorizes employees into Sales Team, Marketing Team, or Other.

16. ALTER TABLE: Modifying Table Structure

The ALTER TABLE statement allows you to add, modify, or delete columns in a table.

Add a column:

ALTER TABLE table_name
ADD column_name datatype;

Example:

ALTER TABLE employees
ADD date_of_birth DATE;

Modify a column:

ALTER TABLE table_name
MODIFY column_name datatype;

Example:

ALTER TABLE employees
MODIFY salary DECIMAL(10, 2);

Drop a column:

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

ALTER TABLE employees
DROP COLUMN middle_name;

17. CREATE TABLE: Creating a New Table

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

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

18. DROP TABLE: Deleting a Table

The DROP TABLE statement is used to delete a table and its data permanently.

DROP TABLE table_name;

Example:

DROP TABLE employees;

19. TRUNCATE: Deleting All Records in a Table

TRUNCATE removes all records from a table but keeps the structure intact.

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE employees;

This removes all rows from the employees table.

Conclusion

This blog has covered the most commonly used SQL keywords, along with examples to help solidify your understanding. These keywords form the backbone of SQL operations and are crucial for working with any relational database. By mastering these, you’ll be well on your way to becoming proficient in SQL.

Boost My Skill