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.
This statement is used to retrieve the first_name and last_name fields of all the employees in the ‘employees’ 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.
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.
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.
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.
This statement will delete all the records from the employees 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');