Databases & SQL · Class 12 · Query Practice
MySQLDDL & DML⏱️ 14 min read
CREATE, ALTER, INSERT, UPDATE & DELETE
Half of the SQL marks are for building and changing tables rather than querying them. This page works through one table from create to drop, so you see the effect of each statement on the one before it.
1CREATE TABLE, with constraints
1
Create a table
3 marksstudent with a primary key rollno, a compulsory name, a stream defaulting to Science, decimal marks, an age that must be between 3 and 20, and an admission date.▶Show the answer
MySQL command line client
mysql> create table student (
-> rollno int primary key,
-> name varchar(25) not null,
-> stream varchar(12) default 'Science',
-> marks decimal(5,2),
-> age int check (age >= 3 and age <= 20),
-> admdate date
-> );
Query OK, 0 rows affected (0.39 sec)
0 rows affected is correct and expected — a create makes a structure, it does not put rows in it.
MySQL command line client
mysql> describe student;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| rollno | int | NO | PRI | NULL | |
| name | varchar(25) | NO | | NULL | |
| stream | varchar(12) | YES | | Science | |
| marks | decimal(5,2) | YES | | NULL | |
| age | int | YES | | NULL | |
| admdate | date | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
describe shows the primary key as PRI with Null: NO, the not null on name, and the default sitting in its own column. Notice that the check constraint does not appear here — it is enforced, but describe has nowhere to show it.
CHECK is not on the IP syllabus
Informatics Practices students are not asked for the
check constraint. Computer Science students are. The rest of this page applies to both.2Three ways to INSERT
Values for every column, in table order:
MySQL command line client
mysql> insert into student values (1, 'Aditi Rai', 'Science', 88.50, 17, '2024-04-01');
Query OK, 1 row affected (0.06 sec)
Or name the columns you are supplying, and let the rest fall back to their defaults:
MySQL command line client
mysql> insert into student (rollno, name, marks, age) values (2, 'Bikash Chettri', 74.25, 16);
Query OK, 1 row affected (0.02 sec)
Or add several rows in one statement:
MySQL command line client
mysql> insert into student values
-> (3, 'Chhering Bhutia', 'Commerce', 91.00, 17, '2024-04-03'),
-> (4, 'Divya Sharma', 'Arts', 67.75, 18, '2024-04-05');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
MySQL command line client
mysql> select * from student;
+--------+-----------------+----------+-------+------+------------+
| rollno | name | stream | marks | age | admdate |
+--------+-----------------+----------+-------+------+------------+
| 1 | Aditi Rai | Science | 88.50 | 17 | 2024-04-01 |
| 2 | Bikash Chettri | Science | 74.25 | 16 | NULL |
| 3 | Chhering Bhutia | Commerce | 91.00 | 17 | 2024-04-03 |
| 4 | Divya Sharma | Arts | 67.75 | 18 | 2024-04-05 |
+--------+-----------------+----------+-------+------+------------+
4 rows in set (0.01 sec)
Default and NULL are not the same thing
Look at row 2.
stream was not supplied and came out as Science — the default did its job. But admdate has no default, so it came out NULL. A column with no default and no value gets NULL, not a blank and not a zero.3The constraints doing their job
A constraint is only visible when it stops something. All three of these were refused:
MySQL command line client
mysql> insert into student values (5, 'Eshan Rai', 'Science', 80.00, 25, '2024-04-06');
ERROR 3819 (HY000): Check constraint 'student_chk_1' is violated.
MySQL command line client
mysql> insert into student values (6, null, 'Science', 80.00, 16, '2024-04-06');
ERROR 1048 (23000): Column 'name' cannot be null
MySQL command line client
mysql> insert into student values (1, 'Repeat Roll', 'Science', 80.00, 16, '2024-04-06');
ERROR 1062 (23000): Duplicate entry '1' for key 'student.PRIMARY'
Age 25 breaks the check; a NULL name breaks not null; and roll number 1 already exists, which breaks the primary key. In each case no row was added — the statement was rejected whole.
4UPDATE — and the WHERE you must not forget
2
Change the marks of roll number
1 mark2 to 90.00.▶Show the answer
MySQL command line client
mysql> update student set marks = 90.00 where rollno = 2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Rows matched: 1 confirms the where picked out exactly one row. Reading that line is the quickest way to catch a mistake.
3
Increase the marks of every Science student by 2.
2 marks▶Show the answer
MySQL command line client
mysql> update student set marks = marks + 2 where stream = 'Science';
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2 Changed: 2 Warnings: 0
set marks = marks + 2 reads the old value and writes the new one, row by row.
MySQL command line client
mysql> select rollno, name, stream, marks from student;
+--------+-----------------+----------+-------+
| rollno | name | stream | marks |
+--------+-----------------+----------+-------+
| 1 | Aditi Rai | Science | 90.50 |
| 2 | Bikash Chettri | Science | 92.00 |
| 3 | Chhering Bhutia | Commerce | 91.00 |
| 4 | Divya Sharma | Arts | 67.75 |
+--------+-----------------+----------+-------+
4 rows in set (0.00 sec)
An UPDATE with no WHERE changes every row
update student set marks = marks + 2; would have added two to all four students. There is no confirmation prompt and no undo. Write the where first, then go back and write the set.5DELETE
MySQL command line client
mysql> delete from student where rollno = 4;
Query OK, 1 row affected (0.03 sec)
MySQL command line client
mysql> select * from student;
+--------+-----------------+----------+-------+------+------------+
| rollno | name | stream | marks | age | admdate |
+--------+-----------------+----------+-------+------+------------+
| 1 | Aditi Rai | Science | 90.50 | 17 | 2024-04-01 |
| 2 | Bikash Chettri | Science | 92.00 | 16 | NULL |
| 3 | Chhering Bhutia | Commerce | 91.00 | 17 | 2024-04-03 |
+--------+-----------------+----------+-------+------+------------+
3 rows in set (0.00 sec)
delete removes rows; the table itself is untouched and still has its six columns. That is the distinction papers test: delete empties, drop destroys.
6ALTER TABLE — adding, changing, removing a column
MySQL command line client
mysql> alter table student add column city varchar(15);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL command line client
mysql> describe student;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| rollno | int | NO | PRI | NULL | |
| name | varchar(25) | NO | | NULL | |
| stream | varchar(12) | YES | | Science | |
| marks | decimal(5,2) | YES | | NULL | |
| age | int | YES | | NULL | |
| admdate | date | YES | | NULL | |
| city | varchar(15) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
The degree went from 6 to 7. The new column is NULL in every existing row — there was nothing to put in it.
MySQL command line client
mysql> alter table student modify column stream varchar(20);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table student drop column city;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL command line client
mysql> describe student;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| rollno | int | NO | PRI | NULL | |
| name | varchar(25) | NO | | NULL | |
| stream | varchar(20) | YES | | NULL | |
| marks | decimal(5,2) | YES | | NULL | |
| age | int | YES | | NULL | |
| admdate | date | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
MODIFY silently threw the default away
Compare the
This was found by running the statements and reading the output, not from a textbook — most of them do not mention it.
stream row with the very first describe on this page. The type widened from varchar(12) to varchar(20) as asked — but the Default column has changed from Science to NULL. modify replaces the whole column definition, so anything you do not restate is lost. To keep it, the statement has to say so: alter table student modify column stream varchar(20) default 'Science';This was found by running the statements and reading the output, not from a textbook — most of them do not mention it.
7DROP TABLE
MySQL command line client
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
mysql> drop table student;
Query OK, 0 rows affected (0.14 sec)
mysql> show tables;
Empty set (0.00 sec)
Three rows existed a moment before, and drop took the rows, the columns and the name together.
Quick Check
A column is declared with DEFAULT 'Science'. A row is inserted without naming that column. What is stored?
Quick Check
After 'alter table student modify column stream varchar(20);', what happens to the column's existing DEFAULT 'Science'?
Quick Check
Which statement removes all the rows but keeps the table structure?