SQL
Example Table Creation and Data Update
1. Create the employees table:
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
age INT,
department VARCHAR(50)
);
2. Insert sample data into the employees table:
INSERT INTO employees (first_name, last_name, age, department)
VALUES
('John', 'Doe', 30, 'Engineering'),
('Jane', 'Smith', 28, 'Marketing'),
('Alice', 'Johnson', 35, 'HR'),
('Bob', 'Brown', 40, 'Finance');
3. Update data in the employees table:
Update John's age:
UPDATE employees
SET age = 31
WHERE first_name = 'John' AND last_name = 'Doe';
Update Jane's age and department:
UPDATE employees
SET age = 29, department = 'Sales'
WHERE first_name = 'Jane' AND last_name = 'Smith';
Increase the age of all employees by 1 year:
UPDATE employees
SET age = age + 1;
This covers the basics of the UPDATE statement in SQL. By following these examples, you can efficiently update data in your database tables.
General Tips!
- Use Proper Indexing: Index frequently queried columns to improve query performance, but avoid over-indexing as it can slow down write operations.
- Write Efficient Queries: Optimize your queries by selecting only the necessary columns, avoiding SELECT *, and using joins wisely.
- Normalize Your Data: Organize your database into normalized tables to reduce redundancy and improve data integrity.
- Use Transactions: Ensure data consistency and integrity by wrapping multiple related operations in transactions, especially in multi-step processes.
- Understand and Use SQL Functions: Leverage built-in SQL functions for data manipulation and retrieval, such as aggregate functions (SUM, COUNT) and string functions (CONCAT, SUBSTRING).