1.
ALTER Statement
The ALTER statement is used to modify an existing database table structure. You can add, delete, or
modify columns in an existing table.
Example Table: Employees
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
BirthDate DATE,
Position VARCHAR(50)
);
Adding a New Column
To add a new column Salary to the Employees table:
ALTER TABLE Employees
ADD Salary DECIMAL(10, 2);
Modifying an Existing Column
To change the data type of the Position column to VARCHAR(100):
ALTER TABLE Employees
MODIFY Position VARCHAR(100);
2. UPDATE Statement
The UPDATE statement is used to modify existing records in a table.
Example Table: Employees
INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, Position, Salary)
VALUES (1, 'John', 'Doe', '1980-01-01', 'Manager', 75000.00),
(2, 'Jane', 'Smith', '1985-05-15', 'Developer', 65000.00),
(3, 'Alice', 'Johnson', '1990-09-25', 'Designer', 60000.00);
Updating a Single Record
To update the salary of the employee with EmployeeID 2:
UPDATE Employees
SET Salary = 70000.00
WHERE EmployeeID = 2;
Updating Multiple Records
To give a 10% raise to all employees:
UPDATE Employees
SET Salary = Salary * 1.10;
3. DELETE Statement
The DELETE statement is used to remove existing records from a table.
Deleting a Single Record
To delete the employee with EmployeeID 3:
DELETE FROM Employees
WHERE EmployeeID = 3;
Dropping a Column
To remove the BirthDate column from the Employees table:
ALTER TABLE Employees
DROP COLUMN BirthDate;
Deleting All Records
To delete all records from the Employees table (but keep the table structure):
DELETE FROM Employees;
Or, you can use:
TRUNCATE TABLE Employees;
(Note: TRUNCATE is faster but less flexible compared to DELETE. TRUNCATE also resets any auto-
increment counters.)
Summary
ALTER: Modify the structure of a table (add, delete, modify columns).
UPDATE: Modify existing records in a table.
DELETE: Remove existing records from a table.
Advanced ALTER Statement
We'll enhance the Employees table by adding constraints, renaming columns, and modifying
multiple columns.
Adding a Unique Constraint and Renaming a Column
Suppose we want to add a unique constraint on the Email column and rename the Position column
to JobTitle.
ALTER TABLE Employees
ADD COLUMN Email VARCHAR(100) UNIQUE,
RENAME COLUMN Position TO JobTitle;
Adding a Foreign Key Constraint
Assume we have another table Departments:
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
We want to link the Employees table to the Departments table via a foreign key.
ALTER TABLE Employees
ADD COLUMN DepartmentID INT,
ADD CONSTRAINT FK_Department
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);
Advanced UPDATE Statement
Let's update records based on joins and subqueries.
Updating with a Join
Suppose we have a table Bonuses that records bonus percentages for different job titles.
CREATE TABLE Bonuses (
JobTitle VARCHAR(100),
BonusPercentage DECIMAL(5, 2)
);
Insert some example data:
INSERT INTO Bonuses (JobTitle, BonusPercentage)
VALUES ('Manager', 10.00),
('Developer', 7.50),
('Designer', 5.00);
Update the Salary in the Employees table based on the BonusPercentage from the Bonuses table.
UPDATE Employees
SET Salary = Salary + (Salary * BonusPercentage / 100)
FROM Employees e
JOIN Bonuses b ON e.JobTitle = b.JobTitle;
Updating with a Subquery
Update the DepartmentID of employees to match the department where the average salary is
highest.
UPDATE Employees
SET DepartmentID = (
SELECT TOP 1 DepartmentID
FROM Employees AS e
GROUP BY DepartmentID
ORDER BY AVG(Salary) DESC
);
Advanced DELETE Statement
Let's delete records based on joins and subqueries.
Deleting with a Join
Assume we have another table Projects that lists projects employees are working on.
CREATE TABLE Projects (
ProjectID INT PRIMARY KEY,
ProjectName VARCHAR(100),
EmployeeID INT
);
Insert some example data:
INSERT INTO Projects (ProjectID, ProjectName, EmployeeID)
VALUES (1, 'Project A', 1),
(2, 'Project B', 2),
(3, 'Project C', 3);
Delete employees who are not assigned to any projects.
DELETE e
FROM Employees e
LEFT JOIN Projects p ON e.EmployeeID = p.EmployeeID
WHERE p.EmployeeID IS NULL;
Deleting with a Subquery
Delete employees whose salary is below the average salary.
DELETE FROM Employees
WHERE Salary < (SELECT AVG(Salary) FROM Employees);
Summary of Advanced Examples
ALTER: Added constraints, renamed columns, and added foreign key relationships.
UPDATE: Updated records using joins and subqueries.
DELETE: Deleted records using joins and subqueries
