SQL Basics Practice: DDL & DML Commands with Constraints

The Story — Build the House, Then Furnish It

The SQL Command Families

Every SQL statement belongs to a family. This tutorial focuses on the two you use most — DDL and DML — but it helps to see the whole map.

CREATE TABLE — Building the Structure

CREATE TABLE defines a new table: its name, its columns, each column's data type, and the constraints that guard it. This is always the first command when setting up a schema.

-- Build two related tables for our company database
CREATE TABLE DEPARTMENT (
    Dept_ID   INT          PRIMARY KEY,
    Dname     VARCHAR(40)  NOT NULL
);

CREATE TABLE EMPLOYEE (
    Emp_ID    INT          PRIMARY KEY,
    Name      VARCHAR(50)  NOT NULL,
    Age       INT          CHECK (Age >= 18),
    Salary    INT          DEFAULT 30000,
    Email     VARCHAR(80)  UNIQUE,
    Dept_ID   INT,
    FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID)
);

Constraints — The Guardrails of Your Data

Constraints are rules attached to columns that the database enforces automatically. Any insert or update that breaks a rule is rejected, keeping the data clean and consistent.

ALTER TABLE — Changing the Structure

ALTER TABLE modifies an existing table without losing the data already in it. You can add or drop columns, change a column's type, or add and remove constraints.

-- Add a column
ALTER TABLE EMPLOYEE ADD Joining_Date DATE;

-- Change a column's data type
ALTER TABLE EMPLOYEE MODIFY Name VARCHAR(80);

-- Add a constraint to an existing table
ALTER TABLE EMPLOYEE ADD CONSTRAINT chk_sal CHECK (Salary > 0);

-- Remove a column
ALTER TABLE EMPLOYEE DROP COLUMN Joining_Date;

DROP vs TRUNCATE vs DELETE — A Crucial Comparison

These three commands all "remove" something, but they are very different — and confusing them is the single most common SQL exam mistake.

DELETE FROM EMPLOYEE WHERE Age < 21;   -- removes matching rows only
TRUNCATE TABLE EMPLOYEE;              -- empties the table, keeps structure
DROP TABLE EMPLOYEE;                  -- deletes the table completely

INSERT — Adding Rows

INSERT adds new rows. You can list all values in column order, name specific columns, or insert several rows at once. Columns you skip take their DEFAULT (or NULL).

-- Departments first (parent table, for the foreign key)
INSERT INTO DEPARTMENT VALUES (10, 'Sales'), (20, 'Tech');

-- Full row, values in column order
INSERT INTO EMPLOYEE VALUES (1, 'Raj', 30, 50000, 'raj@co.com', 10);

-- Named columns; Salary defaults to 30000, Email becomes NULL
INSERT INTO EMPLOYEE (Emp_ID, Name, Age, Dept_ID) VALUES (3, 'Amit', 19, 10);

-- Multiple rows at once
INSERT INTO EMPLOYEE VALUES
    (2, 'Sara', 25, 40000, 'sara@co.com', 20);

UPDATE — Changing Existing Rows

UPDATE modifies values in rows that match a WHERE condition. The WHERE clause is critical: omit it and every row is changed.

-- Give everyone in department 10 a 10% raise
UPDATE EMPLOYEE SET Salary = Salary * 1.10 WHERE Dept_ID = 10;

DELETE — Removing Rows

DELETE removes rows that match a condition. Unlike TRUNCATE, it is a DML command: it works row by row, supports a WHERE clause, and can be rolled back inside a transaction.

-- Remove employees younger than 21
DELETE FROM EMPLOYEE WHERE Age < 21;

-- Remove everyone (rows only) -- structure stays, but prefer TRUNCATE for speed
DELETE FROM EMPLOYEE;

Practice Problems — Solve Them Yourself First

Here is a full mini-scenario. Try each problem before revealing the logic in the solution. We build a small college database from scratch.

Problem 1 — Create the tables

-- Solution 1
CREATE TABLE COURSE (
    Course_ID  INT          PRIMARY KEY,
    Title      VARCHAR(50)  NOT NULL
);

CREATE TABLE STUDENT (
    Roll       INT          PRIMARY KEY,
    Name       VARCHAR(50)  NOT NULL,
    Marks      INT          CHECK (Marks >= 0 AND Marks <= 100),
    Grade      CHAR(2)      DEFAULT 'NA',
    Course_ID  INT,
    FOREIGN KEY (Course_ID) REFERENCES COURSE(Course_ID)
);

Problem 2 — Insert data

-- Solution 2
INSERT INTO COURSE VALUES (101, 'DBMS');

INSERT INTO STUDENT VALUES (1, 'Raj', 82, 'A', 101);
INSERT INTO STUDENT VALUES (2, 'Sara', 55, 'C', 101);
INSERT INTO STUDENT (Roll, Name, Marks, Course_ID) VALUES (3, 'Amit', 91, 101);

Problem 3 — Alter the table

-- Solution 3
ALTER TABLE STUDENT ADD Age INT;
ALTER TABLE STUDENT ADD CONSTRAINT chk_age CHECK (Age >= 17);

Problem 4 — Update with a rule

-- Solution 4  (this promotes Amit, 91 marks, to grade A)
UPDATE STUDENT SET Grade = 'A' WHERE Marks > 90;

Problem 5 — Delete, then clean up

-- Solution 5
DELETE FROM STUDENT WHERE Marks < 60;   -- removes Sara (55)

TRUNCATE TABLE STUDENT;   -- (a) empties rows, keeps the table
DROP TABLE STUDENT;       -- (b) removes the table completely

Command Cheat Sheet

Common Mistakes to Avoid

Golden Rules of DDL & DML