Basic SQL Commands

Structured Query Language (SQL) is the standard language used for managing and interacting with relational databases. Whether we are retrieving data, updating records, or defining the structure of our data, SQL commands provide a powerful and flexible way to handle these tasks.

This article will explain the basic SQL commands such as SELECT, INSERT, UPDATE, DELETE, and others, with examples and outputs to demonstrate their functionality. By mastering these commands, we can efficiently manage our database operations.

Basic SQL Commands

SQL commands are the building blocks of interacting with relational databases. These commands help in managing, retrieving, and manipulating data stored in structured formats. They are categorized into various types, such as Data Query Language (DQL), Data Manipulation Language (DML), and Data Definition Language (DDL). By understanding these commands, we can perform basic to advanced database operations efficiently.

We will use a sample table named employees to illustrate each command. This table represents an organization's employee data, including attributes such as ID, name, age, department, and salary. Below is the structure and data of the table

Now, let's perform basic SQL commands on this table, explain their usage, and provide corresponding outputs.

1. SELECT Command

The SELECT command is used to retrieve specific data from one or more tables in a database. It allows us to choose particular columns, apply filters, and sort results as needed. This command forms the foundation of querying in SQL.

Example: Retrieve the name and salary of all employees.

The SELECT command retrieves data from one or more tables.

SELECT name, salary FROM employees;

Output

2. INSERT Command

The INSERT command is used to add new rows to a table. It lets us specify values for each column, either partially or completely, depending on the table's structure. This command is important for populating and maintaining data in tables.

Example: Add a new employee to the table.

The INSERT command adds new rows to a table.

INSERT INTO employees (id, name, age, department, salary) 
VALUES (6, 'Emma Stone', 32, 'Marketing', 68000);

Output

3. UPDATE Command

The UPDATE command modifies existing data in a table. By specifying a condition, we can update one or multiple rows. It is commonly used for correcting or altering record

Example: Update the salary of "John Doe".

The UPDATE command modifies existing records in a table.

UPDATE employees SET salary = 65000 WHERE name = 'John Doe';

Output

4. DELETE Command

The DELETE command removes rows from a table based on a specified condition. Unlike the DROP command, it does not delete the table structure, only the data within it.

Example: Remove "Jane Smith" from the table.

The DELETE command removes rows from a table.

DELETE FROM employees WHERE name = 'Jane Smith';

Output

5. CREATE TABLE

The CREATE TABLE command is used to define a new table in the database. We specify the table name and its columns, along with their data types and constraints.

Example: Create a table for storing project information.

The CREATE TABLE command creates a new table named projects in database with columns for project_id, project_name, and budget.

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(50),
    budget DECIMAL(10, 2)
);

6. DROP TABLE Command

The DROP TABLE command permanently deletes a table and its data from the database. It should be used with caution, as the operation cannot be undone.

Example: Delete the projects table.

The DROP TABLE command deletes an entire table and its data

DROP TABLE projects;

7. ALTER TABLE Command

The ALTER TABLE command modifies the structure of an existing table. It allows us to add, modify, or drop columns, as well as change constraints.

Example: Add a new column position to the employees table.

The ALTER TABLE command modifies the structure of a table.

ALTER TABLE employees ADD position VARCHAR(50);

Output

8. WHERE Clause

The WHERE clause is used to filter records based on specific conditions. It works with most SQL commands, such as SELECT, UPDATE, and DELETE, to target only the relevant rows.

Example: Retrieve employees from the "IT" department.

The WHERE clause filters rows based on specific conditions.

SELECT name, department FROM employees WHERE department = 'IT';

Output

9. ORDER BY Clause

The ORDER BY clause sorts the result set of a query in ascending or descending order based on one or more columns. This helps in organizing the output data effectively.

Example: Order employees by salary in descending order.

The ORDER BY clause sorts the rows in ascending or descending order. So, the employees are ordered by their salary from highest to lowest.

SELECT name, salary FROM employees ORDER BY salary DESC;

Output

10. GROUP BY Clause

The GROUP BY clause groups rows that have the same values in specified columns into summary rows. It is often used with aggregate functions like COUNT, SUM, AVG, etc.

Example: Count the number of employees in each department.

The GROUP BY clause groups rows based on a column and performs aggregate functions.

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

Output

Conclusion

Mastering SQL commands is essential for anyone working with databases. From basic data retrieval using SELECT to modifying the structure of tables with ALTER TABLE, SQL provides the foundation for managing data efficiently. By practicing these fundamental commands, we will be well-equipped to interact with any relational database and perform various operations to meet our data needs.

What is the purpose of the SELECT statement in SQL?

The SELECT statement is used to retrieve data from one or more tables in a database. You can specify which columns to display, filter data with the WHERE clause, and order the results using ORDER BY.

How does the GROUP BY clause work in SQL?

The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used with aggregate functions like COUNT, SUM, AVG, etc., to perform calculations on each group.

What is the difference between DELETE and DROP commands in SQL?

The DELETE command is used to remove rows from a table, whereas the DROP command is used to delete entire tables or databases. DELETE keeps the table structure intact, while DROP removes the table structure along with its data.