SQL Queries for Data Updation and Common Use Cases

SQL Queries: Updating Data and Common Use Cases

SQL is a strong tool. It is used to interact with and change relational databases. It has many capabilities. Data updating and table changes are common tasks. This post will delve into key SQL queries. These are used for data updating. They cover practical scenarios and come with examples.

1. Updating Data in Tables

Update statement is used to change existing records in table. This can update one row or more. It does this based on certain conditions.

Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Examples:

• Making Changes in Single Row:
  UPDATE employees
  SET salary = 75000
  WHERE employee_id = 101;

This query changes the salary of employee with ID 101. The salary is updated to $75,000.

• Changes to Multiple Rows:
  UPDATE employees
  SET department = 'HR'
  WHERE department = 'Human Resources';

This query alters all rows. It alters rows where the department is 'Human Resources' and switches it to 'HR'.

• Alterations Based on Another Table:
  UPDATE employees
  SET salary = salary * 1.1
  WHERE department IN (
  SELECT department_name
  FROM departments
  WHERE location = 'New York'
  );

This query boosts the salary by 10% for employees. Employees working in departments located in New York are targeted.

2. Adding, Removing, Modifying Table Columns

ALTER TABLE statement modifies table's structure.

• Adding A Column:
  ALTER TABLE employees
  ADD date_of_joining DATE;

This query adds new column date_of_joining. The column is of type DATE. It is added to employees table.

• Modifying a Column:
  ALTER TABLE employees
  MODIFY salary DECIMAL(10, 2);

This query changes the salary column. It has a precision of 10 digits with 2 decimal places.

• Removing a Column:
  ALTER TABLE employees
  DROP COLUMN date_of_joining;

This query removes date_of_joining column from employees table.

3. Inserting New Data

INSERT INTO statement is used to add records to table.

Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Examples:

• Inserting a Single Row:
  INSERT INTO employees (employee_id, name, department, salary)
  VALUES (102, 'John Doe', 'Finance', 60000);
• Inserting Multiple Rows:
  INSERT INTO employees (employee_id, name, department, salary)
  VALUES
  (103, 'Jane Smith', 'IT', 70000),
  (104, 'Mike Brown', 'Marketing', 65000);
• Inserting Data from Another Table:
  INSERT INTO retired_employees (employee_id, name, retirement_date)
  SELECT employee_id, name, CURDATE()
  FROM employees
  WHERE status = 'Retired';

This query inserts records into retired_employees table. Records are selected from employees table. The status condition is 'Retired'.

4. Deleting Data

DELETE statement is used to rid records from table.

Syntax:
DELETE FROM table_name
WHERE condition;

Examples:

• Deleting a Single Row:
  DELETE FROM employees
  WHERE employee_id = 105;
• Deleting Multiple Rows:
  DELETE FROM employees
  WHERE department = 'Temporary';
• Deleting All Rows (Caution):
  DELETE FROM employees;

Query erases all rows from the employees table. This is executed without erasing table structure. Use with caution.

5. Renaming and Dropping Tables

• Renaming a Table:
  ALTER TABLE employees
  RENAME TO staff;

This query reassigns the employees table name. It is renamed to staff.

• Dropping a Table:
  DROP TABLE archived_employees;

This query wipes off archived_employees table. It deletes both table and data completely.

6. Filtering Data with SELECT Queries

SELECT statement retrieves data from a database. Filters can be included. Filters fetch specific records.

Examples:

• Basic SELECT Query:
  SELECT name, salary;

This query extracts names and salaries from the employees.

• Using WHERE Clause:
  SELECT name, department FROM employees
  WHERE salary > 50000;

This query obtains names and department details for employees earning more than 50000.

• Using Aggregate Functions:
  SELECT department, AVG(salary) AS average_salary
  FROM employees
  GROUP BY department;

The query calculates the average salary for each department.

• Joining Tables:
  SELECT e.name, d.department_name
  FROM employees e
  JOIN departments d
  ON e.department_id = d.department_id;

This query retrieves employee names and their department names by joining the employees and departments tables on the department ID.

7. Handling Null Values

Updating Null Values:
UPDATE employees
SET salary = 50000
WHERE salary IS NULL;

This query adjusts salary to $50000 for all rows where the salary is NULL.

Filtering Null Values:
SELECT * FROM employees
WHERE date_of_joining IS NOT NULL;

This selects all employees with non-null date_of_joining values.

8. Advanced Use Case: Conditional Updates with CASE

Example:
UPDATE employees
SET salary =
CASE
WHEN department = 'IT' THEN salary * 1.2
WHEN department = 'HR' THEN salary * 1.1
ELSE salary * 1.05
END;

This query increases salaries by 20% for IT employees, 10% for HR employees, and 5% for others.

Conclusion

SQL presents a broad spectrum of capabilities for manipulating and managing data. From updating and deleting records to modifying table structures, proficiency in these queries is crucial. It empowers you to efficiently manage complex database operations.

The examples need practicing. And then adapting them to fit your specific cases lets you master SQL. By this way, you will be proficient.