Practice: tables and data types

Optional

45 min · Beginner

A review of this module. Skip it if you feel confident; the next module does not need it.

Fix the mistakes

The slips that cost marks in this module. Spot each one, then check the fix.

Most table bugs come from a small set of choices: a number type for a phone, FLOAT for money, text for dates, a careless MODIFY. Each has a short, reliable rule, and the exercises below put all of them to work.

Choosing types for a real requirement

Choosing a type means asking two questions about each value: what kind of value is it, and what is the largest or longest it can ever be?

Example 1 · A table from a cinema requirement

SQL

1-- Requirement: a cinema stores each booking with a booking number,2-- the customer's mobile, the show date and time, up to 10 seats,3-- the seat class (SILVER, GOLD or RECLINER), the amount paid,4-- whether parking was added, and when the booking was made.5
6CREATE TABLE cinema_ticket_bookings (7  booking_id   INT AUTO_INCREMENT PRIMARY KEY,8  mobile       VARCHAR(15),9  show_at      DATETIME,10  seats        TINYINT UNSIGNED,11  seat_class   ENUM('SILVER', 'GOLD', 'RECLINER'),12  amount_paid  DECIMAL(8,2),13  has_parking     BOOLEAN,14  booked_at    DATETIME DEFAULT CURRENT_TIMESTAMP15);16
17DESCRIBE cinema_ticket_bookings;

Output

+-------------+----------------------------------+------+-----+-------------------+-------------------+
| Field       | Type                             | Null | Key | Default           | Extra             |
+-------------+----------------------------------+------+-----+-------------------+-------------------+
| booking_id  | int                              | NO   | PRI | NULL              | auto_increment    |
| mobile      | varchar(15)                      | YES  |     | NULL              |                   |
| show_at     | datetime                         | YES  |     | NULL              |                   |
| seats       | tinyint unsigned                 | YES  |     | NULL              |                   |
| seat_class  | enum('SILVER','GOLD','RECLINER') | YES  |     | NULL              |                   |
| amount_paid | decimal(8,2)                     | YES  |     | NULL              |                   |
| has_parking | tinyint(1)                       | YES  |     | NULL              |                   |
| booked_at   | datetime                         | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------------+----------------------------------+------+-----+-------------------+-------------------+

What this code does

Each requirement maps to one type: mobile as text, the planned show as DATETIME, a small never-negative count as TINYINT UNSIGNED, the fixed classes as ENUM, money as DECIMAL, parking as BOOLEAN.
-- starts a comment: MySQL ignores the rest of the line. Writing the requirement above the table keeps the reasons next to the design.
DEFAULT_GENERATED under Extra marks a default that comes from a function, here CURRENT_TIMESTAMP.

A quick guide:

Id of the row: INT AUTO_INCREMENT PRIMARY KEY (BIGINT for very fast-growing tables).
Counts and small numbers: TINYINT, SMALLINT or INT by range; UNSIGNED if never negative.
Money: DECIMAL(p, 2), with p from the largest amount.
Phone, PIN, Aadhaar, IFSC: text (VARCHAR or CHAR), never a number type.

And for the rest:

Short text: VARCHAR(n); long free text: TEXT; short fixed list: ENUM.
A day: DATE; a planned date and time: DATETIME; a record time: TIMESTAMP or DATETIME.
Yes/no: BOOLEAN (stored as 0 and 1).

Fixing wrong types

A wrong type works on day one and fails later, when the data grows or someone runs a report. The usual suspects:

FLOAT/DOUBLE for money: totals drift by a few paise.

Example 2 · Text dates sort wrong and cannot be converted

SQL

1CREATE TABLE scholarship_text_dates (2  student     VARCHAR(30),3  awarded_on  VARCHAR(10)4);5
6INSERT INTO scholarship_text_dates VALUES7  ('Asha', '15-08-2025'), ('Ravi', '02-10-2024'), ('Meena', '26-01-2025');8
9SELECT * FROM scholarship_text_dates ORDER BY awarded_on;10
11ALTER TABLE scholarship_text_dates MODIFY awarded_on DATE;

Output

+---------+------------+
| student | awarded_on |
+---------+------------+
| Ravi    | 02-10-2024 |
| Asha    | 15-08-2025 |
| Meena   | 26-01-2025 |
+---------+------------+

ERROR 1292 (22007): Incorrect date value: '15-08-2025' for column 'awarded_on' at row 1

What this code does

Sorted as text, the dates come out by day number: 2 October 2024, then 15 August 2025, then 26 January 2025. The real order is October 2024, January 2025, August 2025.
Changing the column to DATE fails with error 1292, because 15-08-2025 is not in year-first form. The right type at the start would have avoided both problems.

Use DECIMAL.

INT for a mobile number: error 1264, and leading zeros vanish. Use VARCHAR(15).
VARCHAR for dates: no checks, and '02-10-2024' sorts before '15-08-2025' and '26-01-2025' as text, which is the wrong calendar order. Use DATE.
VARCHAR for prices: '120.00' sorts before '45' as text, and maths needs conversions.
TIMESTAMP for far-future dates: refused after January 2038. Use DATETIME.

Many wrong types can be changed in place with ALTER TABLE ... MODIFY, if every stored value can be converted: '45' becomes 45.00. Values that cannot be read in the new type, such as 'Rs 5' as a decimal or '15-08-2025' as a date, make the whole change fail, so you clean the data first.

Find the bug

Find the wrong type

Each table has one column with the wrong type. Find it.

Store the fee a student paid.

Round 1 of 3

Text dates in day-first form must be converted row by row. The Changing Data module shows how with UPDATE and the STR_TO_DATE function.

Changing tables safely with ALTER TABLE

A safe change is one where you know in advance what happens to every existing row. Five checks cover nearly every case:

Read the current definition with SHOW CREATE TABLE before you write the ALTER. 2.

Example 3 · Converting text prices to DECIMAL

SQL

1CREATE TABLE stationery_items (2  item   VARCHAR(30),3  price  VARCHAR(10)4);5
6INSERT INTO stationery_items VALUES ('Pen', '10.50'), ('Notebook', '45'), ('Geometry box', '120.00');7SELECT * FROM stationery_items ORDER BY price;8
9ALTER TABLE stationery_items MODIFY price DECIMAL(7,2) NOT NULL;10SELECT * FROM stationery_items ORDER BY price;11
12INSERT INTO stationery_items VALUES ('Eraser', 'Rs 5');

Output

+--------------+--------+
| item         | price  |
+--------------+--------+
| Pen          | 10.50  |
| Geometry box | 120.00 |
| Notebook     | 45     |
+--------------+--------+
+--------------+--------+
| item         | price  |
+--------------+--------+
| Pen          |  10.50 |
| Notebook     |  45.00 |
| Geometry box | 120.00 |
+--------------+--------+

ERROR 1366 (HY000): Incorrect decimal value: 'Rs 5' for column 'price' at row 1

What this code does

As text, 120.00 sorts before 45, because 1 comes before 4. After MODIFY ... DECIMAL(7,2), every value was converted (45 became 45.00) and the sort is numeric.
Now the column refuses text that is not a number: Rs 5 fails with error 1366. Had that value been stored before the change, the ALTER itself would have failed with the same error.

When adding a required column, give it a DEFAULT, or old rows silently get 0 or an empty text. 3. In MODIFY and CHANGE, write the full definition, including NOT NULL and DEFAULT, or they are dropped. 4. Before shrinking a column, make sure every value fits; otherwise the change fails with 1264 or 1265. 5. Before DROP COLUMN or RENAME, make sure no query or app still uses the old column.

Combine related changes in one ALTER TABLE separated by commas: the table is rebuilt once, and either all of them apply or none.

Mixed review: reading the error codes

An error code points straight at the cause, so learn to read the number first. The ones from this module:

1064: a syntax mistake, such as a comma after the last column or a reserved word used as a name.
1050 / 1051 / 1146: the table already exists / is unknown / does not exist.
1264: a number out of range for its type; 1406: text too long.
1265: a value outside an ENUM, or a value cut while shrinking a column.

And a few more:

1292: an impossible or badly formatted date, or a TIMESTAMP after 2038.
1366: a value that cannot be read as that type ('abc' as a number), or an emoji in a utf8mb3 column.
1075: an AUTO_INCREMENT column that is not a key; 1091: dropping a column that does not exist.
3730: dropping a table that another table points to.

Module quiz

Questions from the whole module. Your first answer is your score.

Problems

Graded easy to hard, using everything from this module.

Try each one yourself first. Use the hint if you get stuck. Mark it as done when it works.

Exercise 1· Easy

Create gym_members for a college gym: an auto id, the member name (up to 60 characters), a 10-digit mobile, the joining date, the monthly fee (up to Rs. 9,999.99) and whether they have a locker. Add ('Kabir Singh', '9876500004', '2025-07-01', 799.00, TRUE) and show the row.

Expected output

+-----------+-------------+------------+------------+-------------+------------+
| member_id | name        | mobile     | joined_on  | monthly_fee | has_locker |
+-----------+-------------+------------+------------+-------------+------------+
|         1 | Kabir Singh | 9876500004 | 2025-07-01 |      799.00 |          1 |
+-----------+-------------+------------+------------+-------------+------------+

Exercise 2· Easy

Add a column discount_pct TINYINT UNSIGNED NOT NULL DEFAULT 0 to the practice table products, right after price, then show DESCRIBE products;.

Expected output

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| product_id   | int              | NO   | PRI | NULL    | auto_increment |
| name         | varchar(80)      | NO   |     | NULL    |                |
| category_id  | int              | YES  | MUL | NULL    |                |
| price        | decimal(10,2)    | NO   |     | NULL    |                |
| discount_pct | tinyint unsigned | NO   |     | 0       |                |
| stock        | int              | NO   |     | 0       |                |
+--------------+------------------+------+-----+---------+----------------+

Exercise 3· Medium

Every type in this IRCTC table is wrong for its data, and the first insert fails. Rewrite the CREATE TABLE with correct types (PNR is a 10-character code, the fare goes up to Rs. 9,999.99, the journey can be years ahead, the class is one of SL, 3A, 2A, 1A) so both rows go in, and show them sorted by journey date.

SQL

1CREATE TABLE irctc_pnr (2  pnr          INT,3  fare         FLOAT,4  journey_on   TIMESTAMP,5  travel_class VARCHAR(2)6);7
8INSERT INTO irctc_pnr VALUES9  ('4521367890', 1245.50, '2040-03-10 06:00:00', 'SL'),10  ('4521367891', 3890.00, '2025-12-24 21:15:00', '3A');11
12SELECT * FROM irctc_pnr ORDER BY journey_on;

Expected output

+------------+---------+---------------------+--------------+
| pnr        | fare    | journey_on          | travel_class |
+------------+---------+---------------------+--------------+
| 4521367891 | 3890.00 | 2025-12-24 21:15:00 | 3A           |
| 4521367890 | 1245.50 | 2040-03-10 06:00:00 | SL           |
+------------+---------+---------------------+--------------+

Exercise 4· Medium

A stationery table stores prices as text. Convert price to DECIMAL(6,2) NOT NULL in place, keeping the rows, then show the items from cheapest to costliest.

SQL

CREATE TABLE book_items (  item   VARCHAR(30),  price  VARCHAR(10));INSERT INTO book_items VALUES ('Pen', '25'), ('Atlas of India', '450.00'), ('Bookmark', '8.5');

Expected output

+----------------+--------+
| item           | price  |
+----------------+--------+
| Bookmark       |   8.50 |
| Pen            |  25.00 |
| Atlas of India | 450.00 |
+----------------+--------+

Exercise 5· Medium

Make the column name of the practice table customers longer (VARCHAR(100)) without losing its NOT NULL rule, and rename joined_on to signup_date keeping its type and rule. Use one ALTER TABLE, then DESCRIBE.

Expected output

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| customer_id | int          | NO   | PRI | NULL    | auto_increment |
| name        | varchar(100) | NO   |     | NULL    |                |
| email       | varchar(100) | NO   | UNI | NULL    |                |
| phone       | varchar(15)  | YES  |     | NULL    |                |
| city        | varchar(40)  | NO   |     | NULL    |                |
| signup_date | date         | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Exercise 6· Medium

Predict, then run: which statement stops this script, and with which error?

SQL

1CREATE TABLE exam_halls_v2 (2  hall_id   TINYINT AUTO_INCREMENT PRIMARY KEY,3  hall_name VARCHAR(10),4  capacity  TINYINT UNSIGNED5);6INSERT INTO exam_halls_v2 (hall_name, capacity) VALUES ('Main Hall', 240);7ALTER TABLE exam_halls_v2 ADD COLUMN floor_no TINYINT NOT NULL DEFAULT 0;8ALTER TABLE exam_halls_v2 MODIFY capacity TINYINT;9SELECT * FROM exam_halls_v2;

Expected output

ERROR 1264 (22003): Out of range value for column 'capacity' at row 1

Exercise 7· Hard

Design hospital_appointments from this requirement: an auto id starting at 50001; patient name; patient mobile; doctor department, one of GENERAL, CARDIO, ORTHO, PAEDIATRICS; appointment date and time (bookings can be made up to a year ahead); consultation fee up to Rs. 2,000.00; a free-text symptoms note; whether it is a follow-up visit; and when the row was created (filled in automatically). Add one appointment and show DESCRIBE and the row's id, patient and department.

Expected output

+----------------+------------------------------------------------+------+-----+-------------------+-------------------+
| Field          | Type                                           | Null | Key | Default           | Extra             |
+----------------+------------------------------------------------+------+-----+-------------------+-------------------+
| appointment_id | int                                            | NO   | PRI | NULL              | auto_increment    |
| patient_name   | varchar(60)                                    | YES  |     | NULL              |                   |
| patient_mobile | varchar(15)                                    | YES  |     | NULL              |                   |
| department     | enum('GENERAL','CARDIO','ORTHO','PAEDIATRICS') | YES  |     | NULL              |                   |
| appointment_at | datetime                                       | YES  |     | NULL              |                   |
| fee            | decimal(6,2)                                   | YES  |     | NULL              |                   |
| symptoms       | text                                           | YES  |     | NULL              |                   |
| is_follow_up   | tinyint(1)                                     | YES  |     | NULL              |                   |
| created_at     | datetime                                       | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+----------------+------------------------------------------------+------+-----+-------------------+-------------------+
+----------------+---------------+------------+
| appointment_id | patient_name  | department |
+----------------+---------------+------------+
|          50001 | Lakshmi Menon | CARDIO     |
+----------------+---------------+------------+

Exercise 8· Hard

A teammate wants to grow the practice table courses: titles up to 120 characters (still required), a new semester TINYINT UNSIGNED NOT NULL DEFAULT 1 after credits, and the code column renamed to course_code (still CHAR(6) NOT NULL). Use SHOW CREATE TABLE courses first to see the current rules, then make all three changes in one ALTER TABLE and show the courses sorted by course_id.

Expected output

+-----------+-------------+-------------------------+---------+----------+
| course_id | course_code | title                   | credits | semester |
+-----------+-------------+-------------------------+---------+----------+
|         1 | CS201       | Data Structures         |       4 |        1 |
|         2 | CS202       | Database Systems        |       4 |        1 |
|         3 | EC301       | Digital Electronics     |       3 |        1 |
|         4 | MA101       | Engineering Mathematics |       4 |        1 |
|         5 | HS101       | Communication Skills    |       2 |        1 |
+-----------+-------------+-------------------------+---------+----------+

Interview questions

Questions interviewers ask on this topic.

Finish up

Mark complete & continue

Next: Mini project: college library tables

Practice: tables and data types

Optional

45 min · Beginner

Need help? Ask a tutor

A review of this module. Skip it if you feel confident; the next module does not need it.

Fix the mistakes

The slips that cost marks in this module. Spot each one, then check the fix.

Choosing types for a real requirement

Choosing a type means asking two questions about each value: what kind of value is it, and what is the largest or longest it can ever be?

Example 1 · A table from a cinema requirement

SQL

1-- Requirement: a cinema stores each booking with a booking number,2-- the customer's mobile, the show date and time, up to 10 seats,3-- the seat class (SILVER, GOLD or RECLINER), the amount paid,4-- whether parking was added, and when the booking was made.5
6CREATE TABLE cinema_ticket_bookings (7  booking_id   INT AUTO_INCREMENT PRIMARY KEY,8  mobile       VARCHAR(15),9  show_at      DATETIME,10  seats        TINYINT UNSIGNED,11  seat_class   ENUM('SILVER', 'GOLD', 'RECLINER'),12  amount_paid  DECIMAL(8,2),13  has_parking     BOOLEAN,14  booked_at    DATETIME DEFAULT CURRENT_TIMESTAMP15);16
17DESCRIBE cinema_ticket_bookings;

Output

+-------------+----------------------------------+------+-----+-------------------+-------------------+
| Field       | Type                             | Null | Key | Default           | Extra             |
+-------------+----------------------------------+------+-----+-------------------+-------------------+
| booking_id  | int                              | NO   | PRI | NULL              | auto_increment    |
| mobile      | varchar(15)                      | YES  |     | NULL              |                   |
| show_at     | datetime                         | YES  |     | NULL              |                   |
| seats       | tinyint unsigned                 | YES  |     | NULL              |                   |
| seat_class  | enum('SILVER','GOLD','RECLINER') | YES  |     | NULL              |                   |
| amount_paid | decimal(8,2)                     | YES  |     | NULL              |                   |
| has_parking | tinyint(1)                       | YES  |     | NULL              |                   |
| booked_at   | datetime                         | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------------+----------------------------------+------+-----+-------------------+-------------------+

What this code does

Each requirement maps to one type: mobile as text, the planned show as DATETIME, a small never-negative count as TINYINT UNSIGNED, the fixed classes as ENUM, money as DECIMAL, parking as BOOLEAN.
-- starts a comment: MySQL ignores the rest of the line. Writing the requirement above the table keeps the reasons next to the design.
DEFAULT_GENERATED under Extra marks a default that comes from a function, here CURRENT_TIMESTAMP.

A quick guide:

Id of the row: INT AUTO_INCREMENT PRIMARY KEY (BIGINT for very fast-growing tables).
Counts and small numbers: TINYINT, SMALLINT or INT by range; UNSIGNED if never negative.
Money: DECIMAL(p, 2), with p from the largest amount.
Phone, PIN, Aadhaar, IFSC: text (VARCHAR or CHAR), never a number type.

And for the rest:

Short text: VARCHAR(n); long free text: TEXT; short fixed list: ENUM.
A day: DATE; a planned date and time: DATETIME; a record time: TIMESTAMP or DATETIME.
Yes/no: BOOLEAN (stored as 0 and 1).

Fixing wrong types

A wrong type works on day one and fails later, when the data grows or someone runs a report. The usual suspects:

FLOAT/DOUBLE for money: totals drift by a few paise.

Example 2 · Text dates sort wrong and cannot be converted

SQL

1CREATE TABLE scholarship_text_dates (2  student     VARCHAR(30),3  awarded_on  VARCHAR(10)4);5
6INSERT INTO scholarship_text_dates VALUES7  ('Asha', '15-08-2025'), ('Ravi', '02-10-2024'), ('Meena', '26-01-2025');8
9SELECT * FROM scholarship_text_dates ORDER BY awarded_on;10
11ALTER TABLE scholarship_text_dates MODIFY awarded_on DATE;

Output

+---------+------------+
| student | awarded_on |
+---------+------------+
| Ravi    | 02-10-2024 |
| Asha    | 15-08-2025 |
| Meena   | 26-01-2025 |
+---------+------------+

ERROR 1292 (22007): Incorrect date value: '15-08-2025' for column 'awarded_on' at row 1

What this code does

Sorted as text, the dates come out by day number: 2 October 2024, then 15 August 2025, then 26 January 2025. The real order is October 2024, January 2025, August 2025.
Changing the column to DATE fails with error 1292, because 15-08-2025 is not in year-first form. The right type at the start would have avoided both problems.

Use DECIMAL.

INT for a mobile number: error 1264, and leading zeros vanish. Use VARCHAR(15).
VARCHAR for dates: no checks, and '02-10-2024' sorts before '15-08-2025' and '26-01-2025' as text, which is the wrong calendar order. Use DATE.
VARCHAR for prices: '120.00' sorts before '45' as text, and maths needs conversions.
TIMESTAMP for far-future dates: refused after January 2038. Use DATETIME.

Find the bug

Find the wrong type

Each table has one column with the wrong type. Find it.

Store the fee a student paid.

Round 1 of 3

Text dates in day-first form must be converted row by row. The Changing Data module shows how with UPDATE and the STR_TO_DATE function.

Changing tables safely with ALTER TABLE

A safe change is one where you know in advance what happens to every existing row. Five checks cover nearly every case:

Read the current definition with SHOW CREATE TABLE before you write the ALTER. 2.

Example 3 · Converting text prices to DECIMAL

SQL

1CREATE TABLE stationery_items (2  item   VARCHAR(30),3  price  VARCHAR(10)4);5
6INSERT INTO stationery_items VALUES ('Pen', '10.50'), ('Notebook', '45'), ('Geometry box', '120.00');7SELECT * FROM stationery_items ORDER BY price;8
9ALTER TABLE stationery_items MODIFY price DECIMAL(7,2) NOT NULL;10SELECT * FROM stationery_items ORDER BY price;11
12INSERT INTO stationery_items VALUES ('Eraser', 'Rs 5');

Output

+--------------+--------+
| item         | price  |
+--------------+--------+
| Pen          | 10.50  |
| Geometry box | 120.00 |
| Notebook     | 45     |
+--------------+--------+
+--------------+--------+
| item         | price  |
+--------------+--------+
| Pen          |  10.50 |
| Notebook     |  45.00 |
| Geometry box | 120.00 |
+--------------+--------+

ERROR 1366 (HY000): Incorrect decimal value: 'Rs 5' for column 'price' at row 1

What this code does

As text, 120.00 sorts before 45, because 1 comes before 4. After MODIFY ... DECIMAL(7,2), every value was converted (45 became 45.00) and the sort is numeric.
Now the column refuses text that is not a number: Rs 5 fails with error 1366. Had that value been stored before the change, the ALTER itself would have failed with the same error.

Combine related changes in one ALTER TABLE separated by commas: the table is rebuilt once, and either all of them apply or none.

Mixed review: reading the error codes

An error code points straight at the cause, so learn to read the number first. The ones from this module:

1064: a syntax mistake, such as a comma after the last column or a reserved word used as a name.
1050 / 1051 / 1146: the table already exists / is unknown / does not exist.
1264: a number out of range for its type; 1406: text too long.
1265: a value outside an ENUM, or a value cut while shrinking a column.

And a few more:

1292: an impossible or badly formatted date, or a TIMESTAMP after 2038.
1366: a value that cannot be read as that type ('abc' as a number), or an emoji in a utf8mb3 column.
1075: an AUTO_INCREMENT column that is not a key; 1091: dropping a column that does not exist.
3730: dropping a table that another table points to.

Module quiz

Questions from the whole module. Your first answer is your score.

Pick an answer to see if it is right. 0/12 answered.

Question 1 of 12

Which type fits an Aadhaar number (12 digits, never used in maths)?

Question 2 of 12

Which column definition is right for a hostel fee of up to Rs. 1,50,000.00?

Question 3 of 12

What does the SELECT return?

Python

CREATE TABLE t (d VARCHAR(10));INSERT INTO t VALUES ('05-01-2025'), ('20-12-2024');SELECT d FROM t ORDER BY d;

Question 4 of 12

Which statement fails?

Python

1CREATE TABLE students_demo (2  roll_no  INT,3  mobile   INT,4  fees     DECIMAL(8,2)5);6INSERT INTO students_demo VALUES (1, 98765, 45000.00);       -- A7INSERT INTO students_demo VALUES (2, 9876500002, 45000.00);  -- B8INSERT INTO students_demo VALUES (3, 98765, 450000.00);      -- C

Question 5 of 12

What does DESCRIBE show for `status` after this change?

Python

CREATE TABLE tickets (status VARCHAR(10) NOT NULL DEFAULT 'BOOKED');ALTER TABLE tickets MODIFY status VARCHAR(20);

Question 6 of 12

What does the SELECT show?

Python

CREATE TABLE p (price VARCHAR(10));INSERT INTO p VALUES ('99'), ('250.5');ALTER TABLE p MODIFY price DECIMAL(6,2);SELECT price FROM p ORDER BY price;

Question 7 of 12

A delivery app adds `rider_rating TINYINT NOT NULL` to a table with 50,000 orders. What do the old orders get?

Question 8 of 12

Which error do you get for `INSERT INTO t (d) VALUES ('2025-06-31')` on a DATE column?

Question 9 of 12

Which statement fails?

Python

1CREATE TABLE fest (2  event_id  INT AUTO_INCREMENT PRIMARY KEY,3  title     VARCHAR(20),4  kind      ENUM('TECH', 'CULTURAL')5);6INSERT INTO fest (title, kind) VALUES ('Code Sprint', 'tech');         -- A7INSERT INTO fest (title, kind) VALUES ('Rangoli', 'ART');              -- B8INSERT INTO fest (title, kind) VALUES ('Battle of the Bands', 'CULTURAL'); -- C

Question 10 of 12

You need ids for a payments table that will get about 5 crore (50 million) rows a month for many years. Which id column fits?

Question 11 of 12

What does the last SELECT return?

Python

CREATE TABLE seats (seat_id INT AUTO_INCREMENT PRIMARY KEY, label CHAR(3));INSERT INTO seats (label) VALUES ('A1'), ('A2'), ('A3');DELETE FROM seats;INSERT INTO seats (label) VALUES ('B1');SELECT seat_id, label FROM seats;

Question 12 of 12

A chat table rejects a message with an emoji with error 1366, and the database is utf8mb4. What is the most likely reason?

Problems

Graded easy to hard, using everything from this module.

Try each one yourself first. Use the hint if you get stuck. Mark it as done when it works.

Exercise 1· Easy

Expected output

+-----------+-------------+------------+------------+-------------+------------+
| member_id | name        | mobile     | joined_on  | monthly_fee | has_locker |
+-----------+-------------+------------+------------+-------------+------------+
|         1 | Kabir Singh | 9876500004 | 2025-07-01 |      799.00 |          1 |
+-----------+-------------+------------+------------+-------------+------------+

Exercise 2· Easy

Add a column discount_pct TINYINT UNSIGNED NOT NULL DEFAULT 0 to the practice table products, right after price, then show DESCRIBE products;.

Expected output

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| product_id   | int              | NO   | PRI | NULL    | auto_increment |
| name         | varchar(80)      | NO   |     | NULL    |                |
| category_id  | int              | YES  | MUL | NULL    |                |
| price        | decimal(10,2)    | NO   |     | NULL    |                |
| discount_pct | tinyint unsigned | NO   |     | 0       |                |
| stock        | int              | NO   |     | 0       |                |
+--------------+------------------+------+-----+---------+----------------+

Exercise 3· Medium

SQL

1CREATE TABLE irctc_pnr (2  pnr          INT,3  fare         FLOAT,4  journey_on   TIMESTAMP,5  travel_class VARCHAR(2)6);7
8INSERT INTO irctc_pnr VALUES9  ('4521367890', 1245.50, '2040-03-10 06:00:00', 'SL'),10  ('4521367891', 3890.00, '2025-12-24 21:15:00', '3A');11
12SELECT * FROM irctc_pnr ORDER BY journey_on;

Expected output

+------------+---------+---------------------+--------------+
| pnr        | fare    | journey_on          | travel_class |
+------------+---------+---------------------+--------------+
| 4521367891 | 3890.00 | 2025-12-24 21:15:00 | 3A           |
| 4521367890 | 1245.50 | 2040-03-10 06:00:00 | SL           |
+------------+---------+---------------------+--------------+

Exercise 4· Medium

A stationery table stores prices as text. Convert price to DECIMAL(6,2) NOT NULL in place, keeping the rows, then show the items from cheapest to costliest.

SQL

CREATE TABLE book_items (  item   VARCHAR(30),  price  VARCHAR(10));INSERT INTO book_items VALUES ('Pen', '25'), ('Atlas of India', '450.00'), ('Bookmark', '8.5');

Expected output

+----------------+--------+
| item           | price  |
+----------------+--------+
| Bookmark       |   8.50 |
| Pen            |  25.00 |
| Atlas of India | 450.00 |
+----------------+--------+

Exercise 5· Medium

Expected output

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| customer_id | int          | NO   | PRI | NULL    | auto_increment |
| name        | varchar(100) | NO   |     | NULL    |                |
| email       | varchar(100) | NO   | UNI | NULL    |                |
| phone       | varchar(15)  | YES  |     | NULL    |                |
| city        | varchar(40)  | NO   |     | NULL    |                |
| signup_date | date         | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Exercise 6· Medium

Predict, then run: which statement stops this script, and with which error?

SQL

1CREATE TABLE exam_halls_v2 (2  hall_id   TINYINT AUTO_INCREMENT PRIMARY KEY,3  hall_name VARCHAR(10),4  capacity  TINYINT UNSIGNED5);6INSERT INTO exam_halls_v2 (hall_name, capacity) VALUES ('Main Hall', 240);7ALTER TABLE exam_halls_v2 ADD COLUMN floor_no TINYINT NOT NULL DEFAULT 0;8ALTER TABLE exam_halls_v2 MODIFY capacity TINYINT;9SELECT * FROM exam_halls_v2;

Expected output

ERROR 1264 (22003): Out of range value for column 'capacity' at row 1

Exercise 7· Hard

Expected output

+----------------+------------------------------------------------+------+-----+-------------------+-------------------+
| Field          | Type                                           | Null | Key | Default           | Extra             |
+----------------+------------------------------------------------+------+-----+-------------------+-------------------+
| appointment_id | int                                            | NO   | PRI | NULL              | auto_increment    |
| patient_name   | varchar(60)                                    | YES  |     | NULL              |                   |
| patient_mobile | varchar(15)                                    | YES  |     | NULL              |                   |
| department     | enum('GENERAL','CARDIO','ORTHO','PAEDIATRICS') | YES  |     | NULL              |                   |
| appointment_at | datetime                                       | YES  |     | NULL              |                   |
| fee            | decimal(6,2)                                   | YES  |     | NULL              |                   |
| symptoms       | text                                           | YES  |     | NULL              |                   |
| is_follow_up   | tinyint(1)                                     | YES  |     | NULL              |                   |
| created_at     | datetime                                       | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+----------------+------------------------------------------------+------+-----+-------------------+-------------------+
+----------------+---------------+------------+
| appointment_id | patient_name  | department |
+----------------+---------------+------------+
|          50001 | Lakshmi Menon | CARDIO     |
+----------------+---------------+------------+

Exercise 8· Hard

Expected output

+-----------+-------------+-------------------------+---------+----------+
| course_id | course_code | title                   | credits | semester |
+-----------+-------------+-------------------------+---------+----------+
|         1 | CS201       | Data Structures         |       4 |        1 |
|         2 | CS202       | Database Systems        |       4 |        1 |
|         3 | EC301       | Digital Electronics     |       3 |        1 |
|         4 | MA101       | Engineering Mathematics |       4 |        1 |
|         5 | HS101       | Communication Skills    |       2 |        1 |
+-----------+-------------+-------------------------+---------+----------+

Interview questions

Questions interviewers ask on this topic.

6 questions. Answer out loud first, then compare.

Finish up

Mark complete & continue

Next: Mini project: college library tables

Choosing types for a real requirement

What this code does

Fixing wrong types

What this code does

Changing tables safely with ALTER TABLE

What this code does

Mixed review: reading the error codes

Exercise 1· Easy

Exercise 2· Easy

Exercise 3· Medium

Exercise 4· Medium

Exercise 5· Medium

Exercise 6· Medium

Exercise 7· Hard

Exercise 8· Hard

Choosing types for a real requirement

What this code does

Fixing wrong types

What this code does

Changing tables safely with ALTER TABLE

What this code does

Mixed review: reading the error codes

Which type fits an Aadhaar number (12 digits, never used in maths)?

Which column definition is right for a hostel fee of up to Rs. 1,50,000.00?

What does the SELECT return?

Which statement fails?

What does DESCRIBE show for status after this change?

What does the SELECT show?

A delivery app adds rider_rating TINYINT NOT NULL to a table with 50,000 orders. What do the old orders get?

Which error do you get for INSERT INTO t (d) VALUES ('2025-06-31') on a DATE column?

Which statement fails?

You need ids for a payments table that will get about 5 crore (50 million) rows a month for many years. Which id column fits?

What does the last SELECT return?

A chat table rejects a message with an emoji with error 1366, and the database is utf8mb4. What is the most likely reason?

Exercise 1· Easy

Exercise 2· Easy

Exercise 3· Medium

Exercise 4· Medium

Exercise 5· Medium

Exercise 6· Medium

Exercise 7· Hard

Exercise 8· Hard

Q1How do you choose a data type for a column?

Q2What problems have you seen from wrong data types?

Q3How would you change a column's type on a table that already has data?

Q4Why is VARCHAR(255) for every text column a bad habit?

Q5A report shows a sale on 05-01-2025 before one on 20-12-2024. What went wrong?

Q6What does "strict mode" protect you from when you create and change tables?

Which type fits an Aadhaar number (12 digits, never used in maths)?

Which column definition is right for a hostel fee of up to Rs. 1,50,000.00?

What does the SELECT return?

Which statement fails?

What does DESCRIBE show for status after this change?

What does the SELECT show?

A delivery app adds rider_rating TINYINT NOT NULL to a table with 50,000 orders. What do the old orders get?

Which error do you get for INSERT INTO t (d) VALUES ('2025-06-31') on a DATE column?

Which statement fails?

You need ids for a payments table that will get about 5 crore (50 million) rows a month for many years. Which id column fits?

What does the last SELECT return?

A chat table rejects a message with an emoji with error 1366, and the database is utf8mb4. What is the most likely reason?

Q1How do you choose a data type for a column?

Q2What problems have you seen from wrong data types?

Q3How would you change a column's type on a table that already has data?

Q4Why is VARCHAR(255) for every text column a bad habit?

Q5A report shows a sale on 05-01-2025 before one on 20-12-2024. What went wrong?

Q6What does "strict mode" protect you from when you create and change tables?

What does DESCRIBE show for `status` after this change?

A delivery app adds `rider_rating TINYINT NOT NULL` to a table with 50,000 orders. What do the old orders get?

Which error do you get for `INSERT INTO t (d) VALUES ('2025-06-31')` on a DATE column?

What does DESCRIBE show for `status` after this change?

A delivery app adds `rider_rating TINYINT NOT NULL` to a table with 50,000 orders. What do the old orders get?

Which error do you get for `INSERT INTO t (d) VALUES ('2025-06-31')` on a DATE column?