Blog

Introduction to SQL Keywords

SQL (Structured Query Language) is a standard language for managing and manipulating databases. It is used to perform various operations like querying, updating, inserting, and deleting data from a database. In this tutorial, we’ll cover some of the most essential SQL keywords and demonstrate their usage with sample codes and step-by-step instructions.

1. SELECT

The SELECT statement is used to query data from a database. The data returned is stored in a result table, sometimes called the result set.

Example:

SELECT first_name, last_name FROM employees;

Explanation:

• SELECT is the keyword used to fetch data from the database.
• first_name, last_name are the columns you want to retrieve.
• FROM employees specifies the table from which to retrieve the data.

2. WHERE

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

Example:

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

Explanation:

• WHERE department = 'Sales' filters the results to only include employees in the Sales department.

3. INSERT INTO

The INSERT INTO statement is used to add new rows of data to a table.

Example:

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

Explanation:

• INSERT INTO employees specifies the table where the data will be inserted.
• (first_name, last_name, department) specifies the columns to insert data into.
• VALUES ('John', 'Doe', 'Marketing') provides the data for the new row.

4. UPDATE

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

Example:

UPDATE employees SET department = 'Marketing' WHERE first_name = 'John' AND last_name = 'Doe';

Explanation:

• UPDATE employees specifies the table to update.
• SET department = 'Marketing' changes the department of the employee.
• WHERE first_name = 'John' AND last_name = 'Doe' identifies which record to update.

5. DELETE

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

Example:

DELETE FROM employees WHERE department = 'Marketing' AND first_name = 'John';

Explanation:

• DELETE FROM employees specifies the table from which to delete records.
• WHERE department = 'Marketing' AND first_name = 'John' specifies which records to delete.

6. CREATE TABLE

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

Example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50)
);

Explanation:

• CREATE TABLE employees creates a new table named employees.
• id INT PRIMARY KEY defines the id column as an integer and sets it as the primary key.
• first_name VARCHAR(50), last_name VARCHAR(50), and department VARCHAR(50) define other columns with data types.

7. ALTER TABLE

The ALTER TABLE statement is used to modify an existing table structure.

Example:

ALTER TABLE employees ADD COLUMN hire_date DATE;

Explanation:

• ALTER TABLE employees specifies the table to be modified.
• ADD COLUMN hire_date DATE adds a new column hire_date with data type DATE.

8. DROP TABLE

The DROP TABLE statement is used to delete a table and all its data from the database.

Example:

DROP TABLE employees;

Explanation:

• DROP TABLE employees deletes the entire employees table and its data.

9. JOIN

The JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Example:

SELECT employees.first_name, employees.last_name, departments.department_name 
FROM employees
JOIN departments ON employees.department_id = departments.id;

Explanation:

• JOIN departments ON employees.department_id = departments.id specifies the relationship between the employees and departments tables.
• The SELECT statement retrieves data from both tables based on the join condition.

10. GROUP BY

The GROUP BY statement is used to arrange identical data into groups.

Example:

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

Explanation:

• GROUP BY department groups the result set by department.
• COUNT(*) counts the number of employees in each department.
• AS number_of_employees assigns a name to the count column in the result set.

11. ORDER BY

The ORDER BY statement is used to sort the result set in ascending or descending order.

Example:

SELECT first_name, last_name FROM employees ORDER BY last_name ASC;

Explanation:

• ORDER BY last_name ASC sorts the results by the last_name column in ascending order.
• ASC is optional as ascending order is the default. Use DESC for descending order.

Conclusion

This tutorial covered basic SQL keywords essential for managing and manipulating databases. By practicing these commands, you’ll gain a solid foundation in SQL, enabling you to perform various database operations efficiently. Continue exploring more advanced SQL features and functions to further enhance your skills.