SQL with MySQL: Create Tables, Change Rows and Ask Questions

What is SQL? DDL, DML and DQL

SQL (Structured Query Language) is the standard language to create and use relational databases. MySQL is a free, open-source DBMS that understands SQL. SQL keywords are not case-sensitive; each statement ends with a semicolon ;.

• DDL (Data Definition Language): defines structure. CREATE, ALTER, DROP.
• DML (Data Manipulation Language): changes data in rows. INSERT, UPDATE, DELETE.
• DQL (Data Query Language): reads data. SELECT.

Data types in MySQL

• INT / INTEGER: whole numbers (roll no, marks).
• FLOAT, DECIMAL(p, s): numbers with decimals; DECIMAL(7,2) → up to 99999.99.
• CHAR(n): fixed length text; always uses n characters (good for PIN codes, phone numbers).
• VARCHAR(n): variable length text up to n; uses only as much space as needed (good for names).
• DATE: a date in 'YYYY-MM-DD' form.

Text and dates go in single quotes: 'Asha', '2026-04-01'.

CREATE DATABASE, CREATE TABLE, DROP and ALTER

CREATE DATABASE school;
USE school;
SHOW TABLES;
CREATE TABLE student (
  Roll  INT PRIMARY KEY,
  Name  VARCHAR(20) NOT NULL,
  Marks INT,
  City  VARCHAR(15)
);
DESCRIBE student;               -- see the structure
ALTER TABLE student ADD Phone CHAR(10);
ALTER TABLE student MODIFY City VARCHAR(25);
ALTER TABLE student DROP Phone;
DROP TABLE student;             -- removes table + data
DROP DATABASE school;

PRIMARY KEY and NOT NULL are constraints (rules the DBMS checks). ALTER can also add a primary key: ALTER TABLE student ADD PRIMARY KEY (Roll);

INSERT: adding rows

INSERT INTO student VALUES (1, 'Asha', 92, 'Delhi');
INSERT INTO student (Roll, Name, Marks) VALUES (3, 'Mohan', 58);   -- City becomes NULL

Values must be in column order and match the data types. A duplicate primary key is rejected.

SELECT with WHERE

SELECT * FROM student;                       -- all rows, all columns
SELECT Name, Marks FROM student;             -- chosen columns
SELECT * FROM student WHERE Marks > 80;

Relational operators: = <> (or !=) < > <= >=.

BETWEEN

WHERE Marks BETWEEN 60 AND 80 includes both ends (60 and 80). Same as Marks >= 60 AND Marks <= 80.

Logical operators

AND (both true), OR (at least one true), NOT (reverse). WHERE City = 'Delhi' AND Marks > 90.

IS NULL

NULL means "unknown", so = NULL never works. Use WHERE City IS NULL or IS NOT NULL.

UPDATE and DELETE

UPDATE student SET Marks = Marks + 5 WHERE Marks < 70;
UPDATE student SET City = 'Agra' WHERE Roll = 3;
DELETE FROM student WHERE City IS NULL;
DELETE FROM student;          -- removes ALL rows, table stays

Always check the WHERE clause first. Without it, UPDATE changes every row and DELETE empties the table. DELETE removes rows (DML); DROP removes the table itself (DDL).

Try it: your class in MySQL

Install MySQL (or use any online SQL playground). Create a table of 5 friends with Roll, Name, Marks, City. Insert rows, leaving one City empty. Before running each query, write down which names you expect: marks above 80, marks between 60 and 80, city IS NULL. Then run them. Compare with step 6 of the 3D.

Key formulas and definitions

• DDL: CREATE, ALTER, DROP · DML: INSERT, UPDATE, DELETE · DQL: SELECT
• SELECT columns FROM table WHERE condition;
• x BETWEEN a AND b ⇔ x >= a AND x <= b (both ends included)
• Use IS NULL / IS NOT NULL, never = NULL

Worked examples

Common mistakes

• Writing WHERE City = NULL; use IS NULL.
• Forgetting WHERE in UPDATE or DELETE, changing every row.
• Thinking BETWEEN 60 AND 80 leaves out 60 and 80; both are included.
• Putting text values without quotes: Name = Asha instead of Name = 'Asha'.