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
Employees TableNow, 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
Select Command2. 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
Insert Command3. 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
Update Command4. 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
Delete Command5. 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
id | name | age | department | salary | position |
---|
1 | John Doe | 30 | IT | 65000 | NULL |
3 | Sam Brown | 35 | Finance | 75000 | NULL |
4 | Lisa White | 28 | IT | 62000 | NULL |
5 | Adam Green | 40 | HR | 80000 | NULL |
6 | Emma Stone | 32 | Marketing | 68000 | NULL |
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
name | department |
---|
John Doe | IT |
Lisa White | IT |
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
name | salary |
---|
Adam Green | 80000 |
Sam Brown | 75000 |
John Doe | 65000 |
Emma Stone | 68000 |
Lisa White | 62000 |
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
department | num_employees |
---|
IT | 2 |
Finance | 1 |
HR | 1 |
Marketing | 1 |
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.
Similar Reads
DDL Commands & Syntax
In this article, we will discuss the overview of DDL commands and will understand DDL commands like create, alter, truncate, drop. We will cover each command syntax with the help of an example for better understanding. Let's discuss it one by one. Overview :Data Definition Language(DDL) is a subset
3 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands
SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
MongoDB Shell
MongoDB Shell is a powerful, interactive command-line interface that enables developers and database administrators to perform operations and manage their MongoDB databases using JavaScript syntax. Whether you're a beginner just starting with MongoDB or an experienced user, MongoDB Shell offers a co
5 min read
Basic Shell Commands in Linux: Complete List
Anyone using Linux should become an expert in the essential shell commands, as they form the backbone of working with the Linux terminal. These commands enable you to navigate the system, manage files, handle processes, and configure settings effectively.The Linux shell serves as an interface for us
5 min read
25 Basic Linux Commands For Beginners [2025]
While performing a task, we all need shortcuts. Shortcuts help us to complete a task quickly. Linux comes with such commands which are one to two words, using that commands, you can perform several operations in no time. As a beginner, you must be aware of those basic Linux commands to complete an o
13 min read
25 basic Ubuntu Commands
Ubuntu is one of the most popular distributions of Linux, known for its user-friendly interface and robust features. Whether you're a beginner or an experienced user, understanding basic Ubuntu commands is essential for navigating and managing your system efficiently. In this article, we'll explore
6 min read
Basic Linux Commands for day to day life
Linux is a popular desktop, embedded, and server operating system that is strong and adaptable. Knowing the basics of commands will greatly increase your productivity and simplicity of use if you use Linux. Essential Linux commands that are useful for daily operations will be covered in this article
3 min read
Essential Unix Commands
Unix commands are a set of commands that are used to interact with the Unix operating system. Unix is a powerful, multi-user, multi-tasking operating system that was developed in the 1960s by Bell Labs. Unix commands are entered at the command prompt in a terminal window, and they allow users to per
7 min read
Linux Commands Cheat Sheet
Linux, often associated with being a complex operating system primarily used by developers, may not necessarily fit that description entirely. While it can initially appear challenging for beginners, once you immerse yourself in the Linux world, you may find it difficult to return to your previous W
13 min read
Top Linux Command Line Tips and Tricks
Linux is a great OS that offers multiple options for simplifying tasks. It has everything you need for working more efficiently, whether a command-line or a GUI. However, many people don't know the Linux command line's short tricks and end up being stuck in complex tasks. An interface (text-based) t
5 min read