Lesson 4: SQL – Data Definition and Data Manipulation

SQL DDL statements let us create and modify database structures. The main commands are CREATE, ALTER, and DROP. For example, to create a table you write:

CREATE TABLE Student (
  student_id INT PRIMARY KEY,
  name VARCHAR(50),
  program VARCHAR(20)
);

This creates a Student table with a primary key. Common data types include INT for integers, VARCHAR(n) for text up to n characters, DATE for dates, etc. You can also add constraints: NOT NULL (column must have a value), UNIQUE (no duplicates), and FOREIGN KEY (to link tables). For example, adding a foreign key to Enrollment might look like:

ALTER TABLE Enrollment
ADD FOREIGN KEY (student_id) REFERENCES Student(student_id);

DDL changes the schema but does not handle table rows (data).

DML statements let us manage the data inside tables. The main commands are INSERT, UPDATE, DELETE, and SELECT (SELECT is technically a query but used in DML context to retrieve data). Examples:

INSERT — Add new rows:

INSERT INTO Student VALUES (1, 'Ana Lopez', 'BSIT');

UPDATE — Change existing rows:

UPDATE Student SET program = 'BSCS' WHERE student_id = 1;

DELETE — Remove rows:

DELETE FROM Student WHERE student_id = 1;

Always use WHERE to specify which rows to update/delete; omitting it affects all rows!