Computer ScienceCBSE Class 12 · Structured Query Language (SQL)

DDL and DML, Data Types, Constraints and Table Commands: CREATE, DESCRIBE, ALTER, DROP (Class 12 Computer Science)

Updated 17 October 2026 · Fact-checked

DDL (Data Definition Language) commands such as CREATE, ALTER and DROP define and change the structure of databases and tables. DML (Data Manipulation Language) commands such as INSERT, UPDATE, DELETE and SELECT work on the data. Columns have data types (CHAR, VARCHAR, INT, FLOAT, DATE) and constraints (NOT NULL, UNIQUE, PRIMARY KEY).

DDL (blue) changes the table's structure, shown by the header row; DML (orange) works on the data rows inside it.

Illustration drawn for QuestionBuddy.

Understand DDL, DML, Data Types, Constraints and Table Commands (CREATE, ALTER, DROP)

Two groups of SQL commands.

DDL (Data Definition Language): defines the structure: CREATE, ALTER, DROP.
DML (Data Manipulation Language): works on the data: INSERT, UPDATE, DELETE, SELECT.

Database commands (MySQL).

CREATE DATABASE school;
SHOW DATABASES;
USE school;
DROP DATABASE school;

USE makes a database the current one. DROP DATABASE deletes it with all its tables.

Data types in the syllabus.

CHAR(n): fixed-length text of n characters (shorter values are padded).
VARCHAR(n): variable-length text up to n characters; stores only what is given.
INT: whole numbers. FLOAT: decimal numbers.
DATE: a date written as 'YYYY-MM-DD'.

Constraints restrict the values of a column.

NOT NULL: the column cannot be empty.
UNIQUE: no two rows may have the same value (NULL is allowed).
PRIMARY KEY: unique and not NULL; one per table.

CREATE TABLE and DESCRIBE.

CREATE TABLE FEES (
    AdmNo INT PRIMARY KEY,
    Name VARCHAR(25) NOT NULL,
    Class CHAR(3),
    Amount FLOAT,
    DueDate DATE
);
DESCRIBE FEES;

DESCRIBE (or DESC) lists the columns of a table (MySQL 8 shows):

Field	Type	Null	Key	Default
AdmNo	int	NO	PRI	NULL
Name	varchar(25)	NO		NULL
Class	char(3)	YES		NULL
Amount	float	YES		NULL
DueDate	date	YES		NULL

SHOW TABLES; lists the tables of the current database.

ALTER TABLE changes an existing table:

ALTER TABLE FEES ADD Remarks VARCHAR(20);
ALTER TABLE FEES DROP DueDate;
ALTER TABLE T1 ADD PRIMARY KEY (Code);
ALTER TABLE T1 DROP PRIMARY KEY;

DROP TABLE DROP TABLE FEES; removes the table, with its data and structure.

Inserting, updating and deleting rows and the SELECT forms are separate topics.

DDL, DML, Data Types, Constraints and Table Commands (CREATE, ALTER, DROP): key facts

DDL and DML: DDL: CREATE, ALTER, DROP | DML: INSERT, UPDATE, DELETE, SELECT; DDL changes structure, DML works on rows.
Data types: CHAR(n) fixed length
VARCHAR(n) variable length up to n
INT whole numbers
FLOAT decimals
DATE YYYY-MM-DD; Always give a size to CHAR and VARCHAR.
Constraints: NOT NULL no empty value
UNIQUE no duplicates, NULL allowed
PRIMARY KEY unique and not NULL, one per table; PRIMARY KEY = UNIQUE + NOT NULL.
Table commands: CREATE TABLE
DESCRIBE or DESC
SHOW TABLES
ALTER TABLE t ADD column
ALTER TABLE t DROP column
ALTER TABLE t ADD PRIMARY KEY (col)
ALTER TABLE t DROP PRIMARY KEY
DROP TABLE; Database level: CREATE DATABASE, USE, SHOW DATABASES, DROP DATABASE.

CHAR(n)	fixed length
VARCHAR(n)	variable length up to n
INT	whole numbers
FLOAT	decimals
DATE	YYYY-MM-DD

NOT NULL	no empty value
UNIQUE	no duplicates, NULL allowed
PRIMARY KEY	unique and not NULL, one per table

How to answer DDL, DML, Data Types, Constraints and Table Commands (CREATE, ALTER, DROP) questions

Write SQL statements in a fixed order, and keep every keyword and punctuation mark exact.

1Decide the command group: structure (DDL) or data (DML).
2For CREATE TABLE, list each column with its data type; give a size to CHAR and VARCHAR.
3Attach constraints after the data type: INT PRIMARY KEY, VARCHAR(25) NOT NULL.
4Separate the column definitions by commas; end the statement with a semicolon.
5For ALTER TABLE write the table name, then ADD or DROP, then the column (or PRIMARY KEY).
6For a DESC output, list Field, Type, Null and Key, with PRI for a primary key.

DDL, DML, Data Types, Constraints and Table Commands (CREATE, ALTER, DROP) in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Designing a fee table

Suppose a school wants to record admission number, student name, class, fee amount and due date.

Which data type suits each column, and which constraint would you put on the admission number?

Try it first, then show the solutionSolution

AdmNo: INT; Name: VARCHAR(25); Class: CHAR(3) (fixed short code); Amount: FLOAT; DueDate: DATE.
AdmNo must be unique and never empty, so PRIMARY KEY.

Answer: INT PRIMARY KEY, VARCHAR(25), CHAR(3), FLOAT, DATE

Memory hook: Fixed-size code in CHAR, free-length text in VARCHAR.

Adding a column later

Suppose the school later decides to add a remarks column but not to lose any fee record.

Which command and which type of statement is it?

Try it first, then show the solutionSolution

A change to the structure of an existing table is DDL: ALTER TABLE.
ALTER TABLE FEES ADD Remarks VARCHAR(20); keeps existing rows.

Answer: ALTER TABLE FEES ADD Remarks VARCHAR(20);

Memory hook: ALTER renovates the room; DROP demolishes it.

Tricks to remember DDL, DML, Data Types, Constraints and Table Commands (CREATE, ALTER, DROP)

Short memory aids. Say each one aloud once, then use it on a question the same day.

DDL defines, DML manipulates
D-D-L: define the shape; D-M-L: move the data.
Use it when: Classifying commands.
CHAR is fixed, VARCHAR varies
CHAR keeps the full width; VARCHAR keeps only what is typed.
Use it when: Data type differences.
PRIMARY KEY = UNIQUE + NOT NULL
Two guards in one constraint.
Use it when: Constraint questions.
Create, Alter, Drop
Build the table, change it, demolish it.
Use it when: DDL list.

DDL, DML, Data Types, Constraints and Table Commands (CREATE, ALTER, DROP): solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

Example 1: DDL command · 1 mark · MCQBoard level

Which of the following is a DDL command?
(a) INSERT (b) SELECT (c) CREATE (d) UPDATE

Try it first, then show the solutionSolution

CREATE defines a structure; the rest work on data.

Answer: (c) CREATE

Example 2: CHAR and VARCHAR · 2 marks · Short answerBoard level

Differentiate between CHAR(10) and VARCHAR(10).

Try it first, then show the solutionSolution

CHAR(10) is fixed length: a value shorter than 10 characters is padded to fill the length. (1 mark)
VARCHAR(10) is variable length: it stores only the characters given, up to a maximum of 10. (1 mark)

Answer: CHAR is fixed length, VARCHAR is variable length.

Example 3: Create the table and show DESC · 3 marks · Short answerHarder board level

Write a command to create the table FEES(AdmNo integer primary key, Name up to 25 characters not null, Class 3 characters, Amount decimal, DueDate date) and write the output of DESC FEES.

Try it first, then show the solutionSolution

CREATE TABLE FEES (
    AdmNo INT PRIMARY KEY,
    Name VARCHAR(25) NOT NULL,
    Class CHAR(3),
    Amount FLOAT,
    DueDate DATE
);

(2 marks)

DESC FEES: AdmNo int NO PRI; Name varchar(25) NO; Class char(3) YES; Amount float YES; DueDate date YES (1 mark).

Answer: Statement as above; five rows in DESC with AdmNo as PRI and Name as NOT NULL.

Example 4: ALTER and degree · 3 marks · Short answerHarder board level

Starting from the FEES table (5 columns), write the commands to (i) add a column Remarks VARCHAR(20), (ii) delete the column DueDate. Find the degree of the table after both.

Try it first, then show the solutionSolution

(i) ALTER TABLE FEES ADD Remarks VARCHAR(20); the degree becomes 6. (1 mark)
(ii) ALTER TABLE FEES DROP DueDate; the degree becomes 5. (1 mark)
Columns now: AdmNo, Name, Class, Amount, Remarks. (1 mark)

Answer: Degree 5

Example 5: Case: building an item table · 4 marks · Case-basedHOTS / competency

Suppose a shop creates the table ITEM(Code INT, Name VARCHAR(20), Price FLOAT) but forgets a key.
(i) Write a command to make Code the primary key. (1 mark)
(ii) The command fails if two rows have the same Code or a NULL Code. Why? (1 mark)
(iii) Write a command to remove the key again. (1 mark)
(iv) Write the command that deletes the table and its data. (1 mark)

Try it first, then show the solutionSolution

(i) ALTER TABLE ITEM ADD PRIMARY KEY (Code);
(ii) A primary key must be unique and not NULL, so duplicate or NULL values in the existing data are not allowed.
(iii) ALTER TABLE ITEM DROP PRIMARY KEY;
(iv) DROP TABLE ITEM;

Answer: (i) ALTER TABLE ITEM ADD PRIMARY KEY (Code); (iii) ALTER TABLE ITEM DROP PRIMARY KEY; (iv) DROP TABLE ITEM;

Where students lose marks in DDL, DML, Data Types, Constraints and Table Commands (CREATE, ALTER, DROP)

The slips examiners see most often, and what to write instead.

Mistake: Leaving out the size of VARCHAR
CHAR and VARCHAR need a length.
Wrong working
Name VARCHAR NOT NULL
Right working
Name VARCHAR(25) NOT NULL
Do this: Always write the size in brackets.
Usually costs: 0.5 mark
Mistake: Using DELETE to remove a table
DELETE removes rows; DROP TABLE removes the table.
Wrong working
DELETE TABLE FEES;
Right working
DROP TABLE FEES;
Do this: Link structure removal with DROP.
Usually costs: 1 mark
Mistake: Missing commas or the semicolon
Columns in CREATE TABLE are separated by commas; the last column has none.
Wrong working
Class CHAR(3)
Amount FLOAT
Right working
Class CHAR(3),
Amount FLOAT
Do this: Check each line end before submitting.
Usually costs: 0.5 mark

Exam tips

Learn the five data types by the one-line use case: CHAR for codes, VARCHAR for names, DATE as quoted YYYY-MM-DD.
Mention the key in your DESC output as PRI.
ALTER TABLE ... ADD PRIMARY KEY (col) uses brackets; DROP PRIMARY KEY has none.
Do not confuse DROP (structure) with DELETE (rows).
End every statement with a semicolon and keep keywords in capitals for clarity.

Practice questions on DDL, DML, Data Types, Constraints and Table Commands (CREATE, ALTER, DROP)

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

Q1 · 1 mark · MCQBoard level
Which command shows all the tables of the current database?
(a) DESCRIBE (b) SHOW DATABASES (c) SHOW TABLES (d) USE
Hint
Tables, not databases.
Answer
(c) SHOW TABLES
Q2 · 1 mark · Very short answerBoard level
Which constraint ensures that a column cannot contain a NULL value?
Hint
Not empty.
Answer
NOT NULL
Q3 · 2 marks · Short answerHarder board level
Write SQL commands to (i) create a database named library and make it current, (ii) display the structure of a table BOOK.
Hint
Three keywords.
Answer
(i) CREATE DATABASE library; USE library; (ii) DESCRIBE BOOK; (or DESC BOOK;)
Q4 · 1 mark · Assertion-reasonHarder board level
Assertion (A): A PRIMARY KEY column cannot contain NULL.
Reason (R): The PRIMARY KEY constraint combines the UNIQUE and NOT NULL constraints.
(a) Both A and R are true and R is the correct explanation of A. (b) Both A and R are true but R is not the correct explanation of A. (c) A is true but R is false. (d) A is false but R is true.
Hint
What does PRIMARY KEY include?
Answer
(a) Both are true and R explains A.
Q5 · 3 marks · Short answerHOTS / competency
Find the errors in the statement and rewrite it correctly:
CREATE TABLE ITEM (Code INT PRIMARY KEY, Name VARCHAR NOT NULL, Price FLOAT, Code INT);
Hint
Look at the VARCHAR and the column names.
Answer
Errors: VARCHAR has no size; the column name Code is repeated. Corrected: CREATE TABLE ITEM (Code INT PRIMARY KEY, Name VARCHAR(20) NOT NULL, Price FLOAT, Stock INT);

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1.What does DDL stand for?Show answer
Data Definition Language.
2.What does DML stand for?Show answer
Data Manipulation Language.
3.Which command opens a database for use?Show answer
USE.
4.Which command shows the structure of a table?Show answer
DESCRIBE (or DESC).
5.Which command adds a column to an existing table?Show answer
ALTER TABLE ... ADD.
6.Which command removes a table completely?Show answer
DROP TABLE.

DDL, DML, Data Types, Constraints and Table Commands (CREATE, ALTER, DROP): frequently asked questions

What is the difference between DDL and DML?

DDL commands (CREATE, ALTER, DROP) define or change the structure of database objects. DML commands (INSERT, UPDATE, DELETE, SELECT) add, change, remove or read the data in tables.

What is the difference between CHAR and VARCHAR?

CHAR(n) is a fixed-length string padded to n characters; VARCHAR(n) is a variable-length string that stores only the characters given, up to n.

What is the difference between UNIQUE and PRIMARY KEY?

Both stop duplicates. UNIQUE allows NULL values and a table can have several; PRIMARY KEY does not allow NULL and a table has only one.

What does ALTER TABLE do in the Class 12 syllabus?

It adds or removes a column and adds or removes a primary key in an existing table.