“Create a student table and insert data. Implement ALTER, UPDATE, ORDER BY, DELETE and GROUP BY on it” is a standard Class 12 computer science SQL practical. In this post you will create a student table with six students and then run every command on it: ALTER TABLE to add, modify and drop a column, UPDATE to change data, ORDER BY in ascending and descending order, DELETE to remove rows, and GROUP BY with MIN, MAX, SUM, COUNT and AVG. Every command comes with its output, a row-by-row explanation, common mistakes, practice questions and an FAQ. Use the interactive tool below to run the commands on the table and watch every row.
CREATE, INSERT, ALTER, UPDATE, ORDER BY, DELETE and GROUP BY, shown row by row.
Graphical view
SQL commands
Output
What You Will Learn
- The problem statement
- Create the student table and insert data
- ALTER TABLE: add, modify and drop a column
- UPDATE: change data
- ORDER BY: ascending and descending
- DELETE: remove rows
- GROUP BY: min, max, sum, count and average
- Common mistakes, practice questions and FAQ
Problem Statement
Create a student table and insert data. Implement the following SQL commands on the student table: ALTER table to add new attributes / modify data type / drop attribute; UPDATE table to modify data; ORDER BY to display data in ascending / descending order; DELETE to remove tuple(s); GROUP BY and find the min, max, sum, count and average.
In this post every example (except the first one) starts from the original table with 6 rows, so each output is easy to follow on its own. In your practical file you can run the commands one after another on the same table. The commands are written for MySQL, the database used in most school practicals.
Create the Student Table and Insert Data
A table is made of columns (attributes) and rows (tuples). CREATE TABLE sets the column names and data types. roll_no is the primary key, so no two students can have the same roll number. INSERT INTO adds one row at a time, with text in single quotes.
CREATE TABLE student (
roll_no INT PRIMARY KEY,
name VARCHAR(20),
section CHAR(1),
marks INT,
city VARCHAR(20)
);
INSERT INTO student VALUES (1, 'Amit', 'A', 78, 'Delhi');
INSERT INTO student VALUES (2, 'Riya', 'B', 85, 'Mumbai');
INSERT INTO student VALUES (3, 'Karan', 'A', 64, 'Delhi');
INSERT INTO student VALUES (4, 'Neha', 'B', 91, 'Pune');
INSERT INTO student VALUES (5, 'Ravi', 'A', 72, 'Mumbai');
INSERT INTO student VALUES (6, 'Sonia', 'B', 85, 'Delhi');
SELECT * FROM student;Output:
Query OK, 0 rows affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
+---------+-------+---------+-------+--------+
| roll_no | name | section | marks | city |
+---------+-------+---------+-------+--------+
| 1 | Amit | A | 78 | Delhi |
| 2 | Riya | B | 85 | Mumbai |
| 3 | Karan | A | 64 | Delhi |
| 4 | Neha | B | 91 | Pune |
| 5 | Ravi | A | 72 | Mumbai |
| 6 | Sonia | B | 85 | Delhi |
+---------+-------+---------+-------+--------+
6 rows in set (0.00 sec)
| Type of command | Commands | Works on |
|---|---|---|
| DDL (Data Definition Language) | CREATE, ALTER, DROP | The structure of the table |
| DML (Data Manipulation Language) | INSERT, UPDATE, DELETE | The data (rows) |
| Query | SELECT with ORDER BY, GROUP BY | Shows data, changes nothing |
ALTER TABLE: Add, Modify and Drop a Column
ALTER TABLE changes the structure of a table. The rows are kept.
1. Add a new attribute
Syntax: ALTER TABLE table_name ADD column_name datatype; The new column is added at the end, and every existing row gets NULL (no value) in it. DESC shows the structure of the table.
ALTER TABLE student ADD age INT;
DESC student;
SELECT * FROM student;Output:
Query OK, 6 rows affected
Records: 6 Duplicates: 0 Warnings: 0
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| roll_no | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| section | char(1) | YES | | NULL | |
| marks | int | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
+---------+-------+---------+-------+--------+------+
| roll_no | name | section | marks | city | age |
+---------+-------+---------+-------+--------+------+
| 1 | Amit | A | 78 | Delhi | NULL |
| 2 | Riya | B | 85 | Mumbai | NULL |
| 3 | Karan | A | 64 | Delhi | NULL |
| 4 | Neha | B | 91 | Pune | NULL |
| 5 | Ravi | A | 72 | Mumbai | NULL |
| 6 | Sonia | B | 85 | Delhi | NULL |
+---------+-------+---------+-------+--------+------+
6 rows in set (0.00 sec)
Another example, adding a grade column of one character:
ALTER TABLE student ADD grade CHAR(1);
DESC student;
SELECT * FROM student;Output:
+---------+-------+---------+-------+--------+-------+
| roll_no | name | section | marks | city | grade |
+---------+-------+---------+-------+--------+-------+
| 1 | Amit | A | 78 | Delhi | NULL |
| 2 | Riya | B | 85 | Mumbai | NULL |
| 3 | Karan | A | 64 | Delhi | NULL |
| 4 | Neha | B | 91 | Pune | NULL |
| 5 | Ravi | A | 72 | Mumbai | NULL |
| 6 | Sonia | B | 85 | Delhi | NULL |
+---------+-------+---------+-------+--------+-------+
6 rows in set (0.00 sec)
2. Modify the data type of an attribute
Syntax: ALTER TABLE table_name MODIFY column_name new_datatype; Here the names can now be up to 30 characters long:
ALTER TABLE student MODIFY name VARCHAR(30);
DESC student;
SELECT * FROM student;Output of DESC:
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| roll_no | int | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| section | char(1) | YES | | NULL | |
| marks | int | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Changing marks to DECIMAL(5,1) makes it a decimal number with one digit after the point, so every mark gets .0:
ALTER TABLE student MODIFY marks DECIMAL(5,1);
DESC student;
SELECT * FROM student;Output:
+---------+-------+---------+-------+--------+
| roll_no | name | section | marks | city |
+---------+-------+---------+-------+--------+
| 1 | Amit | A | 78.0 | Delhi |
| 2 | Riya | B | 85.0 | Mumbai |
| 3 | Karan | A | 64.0 | Delhi |
| 4 | Neha | B | 91.0 | Pune |
| 5 | Ravi | A | 72.0 | Mumbai |
| 6 | Sonia | B | 85.0 | Delhi |
+---------+-------+---------+-------+--------+
6 rows in set (0.00 sec)
3. Drop an attribute
Syntax: ALTER TABLE table_name DROP COLUMN column_name; The column and its data are removed from every row.
ALTER TABLE student DROP COLUMN city;
DESC student;
SELECT * FROM student;Output:
+---------+-------+---------+-------+
| roll_no | name | section | marks |
+---------+-------+---------+-------+
| 1 | Amit | A | 78 |
| 2 | Riya | B | 85 |
| 3 | Karan | A | 64 |
| 4 | Neha | B | 91 |
| 5 | Ravi | A | 72 |
| 6 | Sonia | B | 85 |
+---------+-------+---------+-------+
6 rows in set (0.00 sec)
UPDATE: Change Data
Syntax: UPDATE table_name SET column = value WHERE condition; UPDATE visits every row and changes only the rows for which the WHERE condition is true.
Change the marks of one student
UPDATE student
SET marks = 90
WHERE roll_no = 3;
SELECT * FROM student;Output:
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
+---------+-------+---------+-------+--------+
| roll_no | name | section | marks | city |
+---------+-------+---------+-------+--------+
| 1 | Amit | A | 78 | Delhi |
| 2 | Riya | B | 85 | Mumbai |
| 3 | Karan | A | 90 | Delhi |
| 4 | Neha | B | 91 | Pune |
| 5 | Ravi | A | 72 | Mumbai |
| 6 | Sonia | B | 85 | Delhi |
+---------+-------+---------+-------+--------+
6 rows in set (0.00 sec)
Change many rows with one command
Here 5 marks are added to every student of section A. The value on the right side of = can use the old value of the column.
UPDATE student
SET marks = marks + 5
WHERE section = 'A';
SELECT * FROM student;Output:
Query OK, 3 rows affected
Rows matched: 3 Changed: 3 Warnings: 0
+---------+-------+---------+-------+--------+
| roll_no | name | section | marks | city |
+---------+-------+---------+-------+--------+
| 1 | Amit | A | 83 | Delhi |
| 2 | Riya | B | 85 | Mumbai |
| 3 | Karan | A | 69 | Delhi |
| 4 | Neha | B | 91 | Pune |
| 5 | Ravi | A | 77 | Mumbai |
| 6 | Sonia | B | 85 | Delhi |
+---------+-------+---------+-------+--------+
6 rows in set (0.00 sec)
Change a text value
UPDATE student
SET city = 'Noida'
WHERE city = 'Delhi';
SELECT * FROM student;Output:
Query OK, 3 rows affected
Rows matched: 3 Changed: 3 Warnings: 0
+---------+-------+---------+-------+--------+
| roll_no | name | section | marks | city |
+---------+-------+---------+-------+--------+
| 1 | Amit | A | 78 | Noida |
| 2 | Riya | B | 85 | Mumbai |
| 3 | Karan | A | 64 | Noida |
| 4 | Neha | B | 91 | Pune |
| 5 | Ravi | A | 72 | Mumbai |
| 6 | Sonia | B | 85 | Noida |
+---------+-------+---------+-------+--------+
6 rows in set (0.00 sec)
UPDATE without WHERE
If you leave out WHERE, every row is changed. Look at the first line of the output: all 6 rows were affected.
UPDATE student
SET marks = marks + 2;
SELECT * FROM student;Output:
Query OK, 6 rows affected
Rows matched: 6 Changed: 6 Warnings: 0
+---------+-------+---------+-------+--------+
| roll_no | name | section | marks | city |
+---------+-------+---------+-------+--------+
| 1 | Amit | A | 80 | Delhi |
| 2 | Riya | B | 87 | Mumbai |
| 3 | Karan | A | 66 | Delhi |
| 4 | Neha | B | 93 | Pune |
| 5 | Ravi | A | 74 | Mumbai |
| 6 | Sonia | B | 87 | Delhi |
+---------+-------+---------+-------+--------+
6 rows in set (0.00 sec)
ORDER BY: Ascending and Descending Order
Syntax: SELECT columns FROM table_name ORDER BY column [ASC | DESC]; ASC (ascending) is the default and gives the smallest value first, or A to Z for text. DESC (descending) gives the largest value first. ORDER BY sorts only the result. The table itself is not changed.
Descending order of marks
SELECT * FROM student
ORDER BY marks DESC;Output:
+---------+-------+---------+-------+--------+
| roll_no | name | section | marks | city |
+---------+-------+---------+-------+--------+
| 4 | Neha | B | 91 | Pune |
| 2 | Riya | B | 85 | Mumbai |
| 6 | Sonia | B | 85 | Delhi |
| 1 | Amit | A | 78 | Delhi |
| 5 | Ravi | A | 72 | Mumbai |
| 3 | Karan | A | 64 | Delhi |
+---------+-------+---------+-------+--------+
6 rows in set (0.00 sec)
Ascending order of names
SELECT * FROM student
ORDER BY name ASC;Output:
+---------+-------+---------+-------+--------+
| roll_no | name | section | marks | city |
+---------+-------+---------+-------+--------+
| 1 | Amit | A | 78 | Delhi |
| 3 | Karan | A | 64 | Delhi |
| 4 | Neha | B | 91 | Pune |
| 5 | Ravi | A | 72 | Mumbai |
| 2 | Riya | B | 85 | Mumbai |
| 6 | Sonia | B | 85 | Delhi |
+---------+-------+---------+-------+--------+
6 rows in set (0.00 sec)
Ascending order of marks
SELECT * FROM student
ORDER BY marks ASC;Output:
+---------+-------+---------+-------+--------+
| roll_no | name | section | marks | city |
+---------+-------+---------+-------+--------+
| 3 | Karan | A | 64 | Delhi |
| 5 | Ravi | A | 72 | Mumbai |
| 1 | Amit | A | 78 | Delhi |
| 2 | Riya | B | 85 | Mumbai |
| 6 | Sonia | B | 85 | Delhi |
| 4 | Neha | B | 91 | Pune |
+---------+-------+---------+-------+--------+
6 rows in set (0.00 sec)
Sort by two columns
Riya and Sonia both have 85 marks, and the order of equal values is not guaranteed when you sort by one column. A second column decides it: highest marks first, and for equal marks the names in A to Z order.
SELECT * FROM student
ORDER BY marks DESC, name ASC;Output:
+---------+-------+---------+-------+--------+
| roll_no | name | section | marks | city |
+---------+-------+---------+-------+--------+
| 4 | Neha | B | 91 | Pune |
| 2 | Riya | B | 85 | Mumbai |
| 6 | Sonia | B | 85 | Delhi |
| 1 | Amit | A | 78 | Delhi |
| 5 | Ravi | A | 72 | Mumbai |
| 3 | Karan | A | 64 | Delhi |
+---------+-------+---------+-------+--------+
6 rows in set (0.00 sec)
DELETE: Remove Rows
Syntax: DELETE FROM table_name WHERE condition; DELETE removes the rows (tuples) for which the condition is true. The table and its columns stay.
Delete the students with marks below 70
DELETE FROM student
WHERE marks < 70;
SELECT * FROM student;Output:
Query OK, 1 row affected
+---------+-------+---------+-------+--------+
| roll_no | name | section | marks | city |
+---------+-------+---------+-------+--------+
| 1 | Amit | A | 78 | Delhi |
| 2 | Riya | B | 85 | Mumbai |
| 4 | Neha | B | 91 | Pune |
| 5 | Ravi | A | 72 | Mumbai |
| 6 | Sonia | B | 85 | Delhi |
+---------+-------+---------+-------+--------+
5 rows in set (0.00 sec)
Delete one student by roll number
DELETE FROM student
WHERE roll_no = 6;
SELECT * FROM student;Output:
Query OK, 1 row affected
+---------+-------+---------+-------+--------+
| roll_no | name | section | marks | city |
+---------+-------+---------+-------+--------+
| 1 | Amit | A | 78 | Delhi |
| 2 | Riya | B | 85 | Mumbai |
| 3 | Karan | A | 64 | Delhi |
| 4 | Neha | B | 91 | Pune |
| 5 | Ravi | A | 72 | Mumbai |
+---------+-------+---------+-------+--------+
5 rows in set (0.00 sec)
Delete the students of one city
DELETE FROM student
WHERE city = 'Mumbai';
SELECT * FROM student;Output:
Query OK, 2 rows affected
+---------+-------+---------+-------+-------+
| roll_no | name | section | marks | city |
+---------+-------+---------+-------+-------+
| 1 | Amit | A | 78 | Delhi |
| 3 | Karan | A | 64 | Delhi |
| 4 | Neha | B | 91 | Pune |
| 6 | Sonia | B | 85 | Delhi |
+---------+-------+---------+-------+-------+
4 rows in set (0.00 sec)
DELETE without WHERE
Without WHERE, all rows are deleted. The table stays, but it is empty.
DELETE FROM student;
SELECT * FROM student;Output:
Query OK, 6 rows affected
+---------+------+---------+-------+------+
| roll_no | name | section | marks | city |
+---------+------+---------+-------+------+
+---------+------+---------+-------+------+
Empty set (0.00 sec)
GROUP BY: Min, Max, Sum, Count and Average
Aggregate functions work on many rows and give one value:
| Function | Meaning |
|---|---|
MIN(column) | Smallest value |
MAX(column) | Largest value |
SUM(column) | Total of the values |
COUNT(*) | Number of rows |
AVG(column) | Average (sum divided by count) |
With GROUP BY, the rows that have the same value in the given column form one group, and the functions give one result row for every group.
Group by section
SELECT section, MIN(marks), MAX(marks), SUM(marks), COUNT(*), AVG(marks)
FROM student
GROUP BY section;Output:
+---------+------------+------------+------------+----------+------------+
| section | MIN(marks) | MAX(marks) | SUM(marks) | COUNT(*) | AVG(marks) |
+---------+------------+------------+------------+----------+------------+
| A | 64 | 78 | 214 | 3 | 71.3333 |
| B | 85 | 91 | 261 | 3 | 87.0000 |
+---------+------------+------------+------------+----------+------------+
2 rows in set (0.00 sec)
How it works (dry run):
| Group | Marks in the group | MIN | MAX | SUM | COUNT | AVG |
|---|---|---|---|---|---|---|
| section A | 78, 64, 72 | 64 | 78 | 214 | 3 | 214 / 3 = 71.3333 |
| section B | 85, 91, 85 | 85 | 91 | 261 | 3 | 261 / 3 = 87.0000 |
Group by city
SELECT city, MIN(marks), MAX(marks), SUM(marks), COUNT(*), AVG(marks)
FROM student
GROUP BY city;Output:
+--------+------------+------------+------------+----------+------------+
| city | MIN(marks) | MAX(marks) | SUM(marks) | COUNT(*) | AVG(marks) |
+--------+------------+------------+------------+----------+------------+
| Delhi | 64 | 85 | 227 | 3 | 75.6667 |
| Mumbai | 72 | 85 | 157 | 2 | 78.5000 |
| Pune | 91 | 91 | 91 | 1 | 91.0000 |
+--------+------------+------------+------------+----------+------------+
3 rows in set (0.00 sec)
Without GROUP BY (the whole table is one group)
SELECT MIN(marks), MAX(marks), SUM(marks), COUNT(*), AVG(marks)
FROM student;Output:
+------------+------------+------------+----------+------------+
| MIN(marks) | MAX(marks) | SUM(marks) | COUNT(*) | AVG(marks) |
+------------+------------+------------+----------+------------+
| 64 | 91 | 475 | 6 | 79.1667 |
+------------+------------+------------+----------+------------+
1 row in set (0.00 sec)
One function only
SELECT section, AVG(marks)
FROM student
GROUP BY section;Output:
+---------+------------+
| section | AVG(marks) |
+---------+------------+
| A | 71.3333 |
| B | 87.0000 |
+---------+------------+
2 rows in set (0.00 sec)SELECT city, COUNT(*)
FROM student
GROUP BY city;Output:
+--------+----------+
| city | COUNT(*) |
+--------+----------+
| Delhi | 3 |
| Mumbai | 2 |
| Pune | 1 |
+--------+----------+
3 rows in set (0.00 sec)
Filter the rows before grouping (WHERE) and the groups after grouping (HAVING)
WHERE picks the rows, for example only the students of Delhi:
SELECT COUNT(*), MAX(marks) FROM student WHERE city = 'Delhi';+----------+------------+
| COUNT(*) | MAX(marks) |
+----------+------------+
| 3 | 85 |
+----------+------------+
1 row in set (0.00 sec)HAVING picks the groups. Here only the sections whose average is above 80 are shown, so section A (average 71.3333) is dropped:
SELECT section, AVG(marks) FROM student GROUP BY section HAVING AVG(marks) > 80;+---------+------------+
| section | AVG(marks) |
+---------+------------+
| B | 87.0000 |
+---------+------------+
1 row in set (0.00 sec)
Common Mistakes
- UPDATE or DELETE without WHERE: every row is changed or removed. In our tests,
UPDATE student SET marks = marks + 2;affected all 6 rows andDELETE FROM student;left an empty table. - No quotes around text: write
city = 'Delhi'. Without quotes, SQL thinksDelhiis a column name and gives an error. - Using an aggregate in WHERE:
WHERE AVG(marks) > 80is an error. Use HAVING after GROUP BY. - Selecting a column that is not in GROUP BY: a query such as
SELECT name, AVG(marks) FROM student GROUP BY section;is rejected by MySQL 5.7.5 and later with the default settings, because one section has many names. Select only the GROUP BY column and the aggregate functions. - Forgetting NULL: after adding a column,
COUNT(age)was 0 althoughCOUNT(*)was 6, andAVG(age)was NULL. Aggregates ignore NULL. - Thinking DROP COLUMN can be undone: the column and its data are gone. Take a copy of the table first if the data matters.
- Expecting a fixed order without ORDER BY: SQL does not promise any order of rows. Add ORDER BY when the order matters, also after GROUP BY.
- Forgetting the semicolon at the end of the command.
Practice Questions
- Add a column
feesof typeINT, then set the fees of all students to 5000 with one UPDATE. - Display the students of section B in the descending order of marks.
- Delete the students whose city is Pune.
- Find the highest and lowest marks in every city.
- Display the sections whose total marks are more than 230.
- Display the number of students in every city, with the biggest city first.
Frequently Asked Questions
What is a student table in SQL?
A student table is a table (relation) that stores one row for every student. The columns, also called attributes or fields, hold the data such as roll number, name, section, marks and city. Each row is called a tuple or record.
How do you create a table and insert data in SQL?
Use CREATE TABLE student (...) with the column names and data types, then add rows with INSERT INTO student VALUES (...). Text values go in single quotes and numbers do not.
How do you add a new column to a table?
Use ALTER TABLE student ADD age INT;. The new column is added at the end and every existing row gets NULL in it until you update the values.
How do you change the data type of a column?
In MySQL use ALTER TABLE student MODIFY marks DECIMAL(5,1);. The existing data is converted, so 78 becomes 78.0. Some databases use ALTER COLUMN ... TYPE instead of MODIFY.
How do you delete a column from a table?
Use ALTER TABLE student DROP COLUMN city;. The column and all its data are removed from every row. This cannot be undone with a normal command, so be careful.
What happens if you run UPDATE without WHERE?
Every row is changed. In our test, UPDATE student SET marks = marks + 2; changed all 6 rows. Always check the WHERE condition before you run UPDATE or DELETE.
What is the difference between DELETE, DROP and TRUNCATE?
DELETE removes rows (all or those matching WHERE) and keeps the table. TRUNCATE TABLE removes all rows quickly and keeps the table. DROP TABLE removes the whole table, structure and data.
What is the default order of ORDER BY?
Ascending (ASC), so ORDER BY marks is the same as ORDER BY marks ASC. Use DESC for descending. For text columns ascending means A to Z.
What happens when two rows have the same value in ORDER BY?
The order of those rows is not guaranteed. In our table Riya and Sonia both have 85 marks. Add a second column, such as ORDER BY marks DESC, name ASC, to make the order fixed.
What does GROUP BY do?
It puts the rows that have the same value in the given column into one group, and an aggregate function such as MIN, MAX, SUM, COUNT or AVG then gives one result for each group. GROUP BY section gives one result row for section A and one for section B.
What is the difference between WHERE and HAVING?
WHERE filters rows before they are grouped, so it cannot use aggregate functions like AVG(). HAVING filters the groups after GROUP BY and can use them, for example HAVING AVG(marks) > 80.
Do aggregate functions count NULL values?
COUNT(*) counts all rows. COUNT(column), SUM, MIN, MAX and AVG ignore NULL. After we added the age column, COUNT(*) was 6 but COUNT(age) was 0, and AVG(age) was NULL. After two ages were filled, COUNT(age) was 2 and AVG(age) was 17.5.
Is text comparison in WHERE case sensitive?
In MySQL with the default settings it is not, so WHERE name = 'riya' also finds Riya. Other databases may be case sensitive.