SQL Server DDL & DML Examples
-------------------------------------
-- 1. CREATE TABLE
-------------------------------------
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
name NVARCHAR(20),
age INT
);
-------------------------------------
-- 2. RENAME EXISTING COLUMN
-- SQL Server uses sp_rename (not RENAME COLUMN)
-------------------------------------
EXEC sp_rename '[Link]', 'NewAge', 'COLUMN';
-------------------------------------
-- 3. ADD NEW COLUMN
-------------------------------------
ALTER TABLE Employee
ADD City NVARCHAR(10) NOT NULL DEFAULT 'Unknown';
-------------------------------------
-- 4. CHANGE DATA TYPE OF EXISTING COLUMN
-------------------------------------
ALTER TABLE Employee
ALTER COLUMN NewAge NVARCHAR(50);
-------------------------------------
-- 5. DROP EXISTING COLUMN
-------------------------------------
ALTER TABLE Employee
DROP COLUMN City;
-------------------------------------
-- 6. INSERT DATA
-- Must match table structure (emp_id, name, NewAge)
-------------------------------------
INSERT INTO Employee (emp_id, name, NewAge)
VALUES
(5, 'Ahmed', 25),
(6, 'Zoz', 30);
-------------------------------------
-- 7. UPDATE DATA
-------------------------------------
UPDATE Employee
SET name = 'Islam', City = '5anka'
WHERE emp_id = 6;
-------------------------------------
-- 8. DELETE SPECIFIC ROW
-------------------------------------
DELETE FROM Employee
WHERE emp_id = 6;
-------------------------------------
-- 9. DELETE TOP N ROWS (any 50 rows)
-------------------------------------
DELETE TOP (50)
FROM Employee;
-------------------------------------
-- 10. DELETE SPECIFIC 50 ROWS (oldest by HireDate)
-- Example assumes Employee table has HireDate column
-------------------------------------
DELETE FROM Employee
WHERE emp_id IN (
SELECT TOP (50) emp_id
FROM Employee
ORDER BY HireDate ASC
);