Data Manipulation Language (DML) is the part of SQL used to work with the data itself—adding it, looking it up, changing it, and removing it. If you think of your database as a set of labeled spreadsheets, DML is how you fill rows in, read them back, edit them, or clear them out.
SQL Query is the foundation of working with any relational database. CRUD operations (Create, Read, Update, Delete) form the core actions you’ll perform when interacting with data. Whether you are managing user information, product details, or transaction records, mastering these SQL basics is essential for efficient database management.
1. SELECT – Retrieving Data
The SELECT statement is used to fetch data from a table. You can retrieve all columns using SELECT * or specific columns for more precise results.
This statement is used to retrieve complete data of all the employees in the ‘employees’ table.
Practice: Select all the rows from the departments table.
This statement is used to retrieve the first_name and last_name fields of all the employees in the ‘employees’ table.
Practice: Select 'location' field from departments table.
2. Aliases with AS
Aliases make query results more readable. You can rename columns or tables for convenience.
This will also retrieve the first_name and last_name fields of all the employees in the ‘employees’ table but you can use the aliases as done with employees table.
Practice: Retrieve the 'name' and 'location' fields of all the departments in the 'departments' table but you have to use the aliases.
3. INSERT INTO – Adding Records
The INSERT INTO statement is used to add new rows into a table.
This statement is used to insert a new record into the table with ‘name’ and ‘location’ fields having the values ‘Legal’ and ‘Boston’ respectively.
You can see the results by the following command:
SELECT * FROM departments;
Practice: Insert into first_name and last_name fields of the employees table values of your own choice and see the results.
4. UPDATE – Modifying Data
The UPDATE query modifies existing records in a table. Always use the WHERE clause to avoid changing all records unintentionally.
This statement will update the ‘phone’ field to ‘000-000' for all the records in the employees which have the value of ‘Alice’ in their ‘first_name’ column.
You can see the results by the following command:
SELECT * FROM employees;
Practice: Update the location field having value of 'New York' in the departments table and see the results.
5. DELETE – Removing Data
The DELETE query removes records from a table. Without a WHERE clause, all rows will be deleted, so use it with caution.
This statement will delete all the records in the clients table which have the value of ‘EcoWorld’ in their client_name field.
You can see the results by the following command:
SELECT * FROM clients;
Practice: Delete from the clients table with industry field having value of 'Technology' and see the results.
This statement will delete all the records from the employees table.
Practice: Delete departments table.
FAQs
1. What is CRUD in SQL with an example?
CRUD stands for Create, Read, Update, Delete. For example:
- Create: INSERT INTO employees (name) VALUES ('Ali');
- Read: SELECT * FROM employees;
- Update: UPDATE employees SET name='Ahmed' WHERE id=1;
- Delete: DELETE FROM employees WHERE id=1;
2. What is the safest way to use UPDATE and DELETE in SQL?
Always use a WHERE clause to target specific rows. Without it, all rows in the table will be updated or deleted, which can cause data loss.
3. Can we undo a DELETE or UPDATE query in SQL?
By default, no. But if you are using transactions, you can roll back changes before committing them.
4. What is the difference between DELETE and DROP in SQL?
DELETE removes rows from a table but keeps the table structure.
DROP removes the entire table (structure + data) from the database.
5. Can we insert multiple rows with one SQL query?
Yes. You can add multiple records in a single INSERT statement:
INSERT INTO employees (name, position) VALUES ('Ali', 'Manager'), ('Sara', 'Analyst'), ('John', 'Developer');