Table of Contents

    Assignment Solution: Working with Tables

    Rumman Ansari July 05, 2026 12 views Subject Details
    Assignment Solution

    4.7 Assignment Solution: Working with Tables

    Review the complete solution for working with MySQL tables, including creating tables, altering table structure, renaming tables, dropping tables, truncating tables, and understanding basic table relationships.

    Solution Purpose: This solution guide helps students understand how to manage table structures in MySQL using practical SQL commands. It covers all important operations from Chapter 4: Create Table, Alter Table, Rename Table, Drop Table, Truncate Table, and Table Relationships.

    Learning Review

    In this assignment solution, students will review how to create a table, modify an existing table, rename a table, remove table data, delete a table permanently, and understand how multiple tables can be connected using relationships.

    After reviewing this solution, students should clearly understand:

    • How to create a table using CREATE TABLE.
    • How to add a new column using ALTER TABLE ADD.
    • How to modify a column using ALTER TABLE MODIFY.
    • How to rename a column using ALTER TABLE RENAME COLUMN.
    • How to drop a column using ALTER TABLE DROP COLUMN.
    • How to rename a table using RENAME TABLE.
    • Difference between DROP TABLE and TRUNCATE TABLE.
    • How basic table relationships work in relational databases.

    Prerequisite Setup

    Before solving table-related tasks, students should create and select a practice database. This keeps all table operations organized inside one database.

    Create and Select Database

    CREATE DATABASE table_practice_db;

USE table_practice_db;

    Explanation

    The first command creates a database named table_practice_db. The second command selects that database so that all new tables will be created inside it.

    Common Error If students do not run USE table_practice_db; before creating a table, MySQL may show a No database selected error.

    Assignment Scenario

    A small company wants to create a simple employee management database. The company needs to store department details and employee details. Later, the company wants to modify the employee table, rename it, test delete operations, and understand how employees are connected with departments.

    Based on this scenario, the following solution covers all major table operations.

    Question 1 Solution: Create a Departments Table

    The first task is to create a table named departments. This table will store department information.

    Correct SQL Command

    CREATE TABLE departments (
    department_id INT,
    department_name VARCHAR(100),
    location VARCHAR(100)
);

    Explanation of Columns

    Column Name Data Type Purpose
    department_id INT Stores the numeric ID of each department.
    department_name VARCHAR(100) Stores the name of the department, such as IT, HR, or Finance.
    location VARCHAR(100) Stores the office location of the department.
    Best Answer Point This is a simple beginner-level table. In later chapters, students can improve it using PRIMARY KEY, NOT NULL, and other constraints.

    Question 2 Solution: Create an Employees Table

    The second task is to create a table named employees. This table will store employee details.

    Correct SQL Command

    CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(15),
    salary DECIMAL(10,2),
    joining_date DATE,
    department_id INT
);

    Explanation of Columns

    Column Name Data Type Reason for Using This Data Type
    employee_id INT Used to store employee ID numbers.
    employee_name VARCHAR(100) Used to store employee names with flexible length.
    email VARCHAR(100) Used to store employee email addresses.
    phone VARCHAR(15) Phone numbers may contain country code, plus sign, or leading zeros, so VARCHAR is better than INT.
    salary DECIMAL(10,2) Used for money values because it stores fixed decimal numbers accurately.
    joining_date DATE Used to store the employee joining date.
    department_id INT Used to connect employees with their departments.
    Important Note: The department_id column in the employees table helps us understand table relationships. It can be connected with the department_id column of the departments table.

    Question 3 Solution: View Tables and Table Structure

    After creating tables, students should verify whether the tables were created successfully.

    Show All Tables

    SHOW TABLES;

    Expected Output

    +-----------------------------+
| Tables_in_table_practice_db |
+-----------------------------+
| departments                 |
| employees                   |
+-----------------------------+

    Check Departments Table Structure

    DESC departments;

    Check Employees Table Structure

    DESC employees;

    Explanation

    The SHOW TABLES command displays all tables inside the selected database. The DESC command shows the structure of a table, including column names, data types, null values, keys, default values, and extra properties.

    Best Practice After every CREATE TABLE operation, students should use DESC table_name; to verify the table structure.

    Question 4 Solution: Add a New Column Using ALTER TABLE

    The task is to add a new column named job_title to the employees table.

    Correct SQL Command

    ALTER TABLE employees
ADD job_title VARCHAR(100);

    Explanation

    The ALTER TABLE command is used to modify an existing table structure. The ADD keyword adds a new column to the table.

    Verify the Change

    DESC employees;
    Syntax Pattern
    ALTER TABLE table_name ADD column_name data_type;

    Question 5 Solution: Modify an Existing Column

    The task is to modify the phone column size from VARCHAR(15) to VARCHAR(20).

    Correct SQL Command

    ALTER TABLE employees
MODIFY phone VARCHAR(20);

    Explanation

    The MODIFY keyword is used to change the data type or size of an existing column. Here, the phone column is changed to allow up to 20 characters.

    Verify the Change

    DESC employees;
    Best Answer Point Phone numbers should usually be stored as VARCHAR, not INT, because phone numbers may contain leading zeros, spaces, hyphens, or country codes.

    Question 6 Solution: Rename a Column

    The task is to rename the column employee_name to full_name.

    Correct SQL Command

    ALTER TABLE employees
RENAME COLUMN employee_name TO full_name;

    Explanation

    The RENAME COLUMN clause is used to change the name of an existing column. The column data type remains the same; only the column name changes.

    Verify the Change

    DESC employees;
    Common Mistake After renaming a column, students should use the new column name in future queries. Using the old column name may cause an error.

    Question 7 Solution: Drop a Column

    The task is to remove the job_title column from the employees table.

    Correct SQL Command

    ALTER TABLE employees
DROP COLUMN job_title;

    Explanation

    The DROP COLUMN command removes a column from an existing table. This operation should be used carefully because the data stored in that column will also be removed.

    Verify the Change

    DESC employees;
    Important Warning Before dropping a column in a real project, always check whether the column contains important data or is used by reports, applications, or relationships.

    Question 8 Solution: Rename a Table

    The task is to rename the table employees to company_employees.

    Correct SQL Command

    RENAME TABLE employees TO company_employees;

    Explanation

    The RENAME TABLE command changes the name of a table. After this command, the table should be accessed using the new name company_employees.

    Verify the Table Rename

    SHOW TABLES;

    Expected Output

    +-----------------------------+
| Tables_in_table_practice_db |
+-----------------------------+
| company_employees           |
| departments                 |
+-----------------------------+
    Important Note: After renaming the table, commands like DESC employees; will not work because the old table name no longer exists. Use DESC company_employees; instead.

    Question 9 Solution: Truncate a Table

    The task is to remove all records from a table while keeping the table structure. For this, we use TRUNCATE TABLE.

    Correct SQL Command

    TRUNCATE TABLE company_employees;

    Explanation

    The TRUNCATE TABLE command removes all rows from a table, but the table structure remains available. This means the columns and table definition are not deleted.

    Check Table Structure After TRUNCATE

    DESC company_employees;

    The table structure will still exist after truncation.

    Memory Rule
    TRUNCATE = Delete All Rows + Keep Table Structure
    Important Warning TRUNCATE TABLE removes all records quickly. Students should not use it on important tables unless they are sure.

    Question 10 Solution: Drop a Table

    The task is to permanently delete a table named old_projects.

    Create Test Table First

    CREATE TABLE old_projects (
    project_id INT,
    project_name VARCHAR(100)
);

    Correct DROP TABLE Command

    DROP TABLE old_projects;

    Explanation

    The DROP TABLE command permanently removes the table structure and all data inside the table. After dropping a table, it will no longer appear in SHOW TABLES.

    Verify the Table Was Deleted

    SHOW TABLES;
    Danger Zone DROP TABLE is more destructive than TRUNCATE TABLE because it removes the complete table, not only the data.

    Question 11 Solution: Difference Between DROP TABLE and TRUNCATE TABLE

    Students must clearly understand the difference between DROP TABLE and TRUNCATE TABLE.

    Point of Difference TRUNCATE TABLE DROP TABLE
    Main Purpose Removes all rows from a table. Deletes the complete table.
    Table Structure Structure remains available. Structure is removed.
    Data All data is removed. All data is removed with the table.
    Use Case When you want to empty a table but use it again. When you no longer need the table.
    Example TRUNCATE TABLE company_employees; DROP TABLE old_projects;

    Wrong Understanding

    • Thinking TRUNCATE and DROP are exactly the same.
    • Using DROP when only data should be removed.
    • Using TRUNCATE when the table should be deleted permanently.

    Correct Understanding

    • Use TRUNCATE to empty the table.
    • Use DROP to remove the table completely.
    • Always confirm before using either command.

    Question 12 Solution: Basic Table Relationships

    Table relationships allow us to connect data from one table with data from another table. In this example, one department can have many employees.

    Relationship Type
    One Department → Many Employees

    Departments Table

    CREATE TABLE departments (
    department_id INT,
    department_name VARCHAR(100),
    location VARCHAR(100)
);

    Employees Table with Department Column

    CREATE TABLE employees_relationship_demo (
    employee_id INT,
    employee_name VARCHAR(100),
    email VARCHAR(100),
    department_id INT
);

    Explanation

    The department_id column exists in both tables. In the departments table, it identifies each department. In the employees_relationship_demo table, it shows which department an employee belongs to.

    Table Name Important Column Role in Relationship
    departments department_id Identifies each department.
    employees_relationship_demo department_id Connects each employee to a department.

    Relationship with Foreign Key Preview

    Although foreign keys will be explained more deeply in the constraints chapter, students can see a simple preview below. 

    CREATE TABLE departments_fk_demo (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100),
    location VARCHAR(100)
);

CREATE TABLE employees_fk_demo (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    email VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments_fk_demo(department_id)
);

    Explanation

    In this example, department_id in the employees_fk_demo table refers to department_id in the departments_fk_demo table. This creates a relationship between employees and departments.

    Best Answer Point A relationship is created when one table uses a column to refer to related data in another table.

    Complete SQL Script for Full Practice

    Students can use the following complete script to practice all major table operations from this assignment. 

    CREATE DATABASE table_practice_db;

USE table_practice_db;

CREATE TABLE departments (
    department_id INT,
    department_name VARCHAR(100),
    location VARCHAR(100)
);

CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(15),
    salary DECIMAL(10,2),
    joining_date DATE,
    department_id INT
);

SHOW TABLES;

DESC departments;

DESC employees;

ALTER TABLE employees
ADD job_title VARCHAR(100);

ALTER TABLE employees
MODIFY phone VARCHAR(20);

ALTER TABLE employees
RENAME COLUMN employee_name TO full_name;

ALTER TABLE employees
DROP COLUMN job_title;

RENAME TABLE employees TO company_employees;

SHOW TABLES;

DESC company_employees;

TRUNCATE TABLE company_employees;

CREATE TABLE old_projects (
    project_id INT,
    project_name VARCHAR(100)
);

DROP TABLE old_projects;

SHOW TABLES;

    Expected Final Table List

    After running the full script, the final table list should contain the active tables that were not dropped. 

    +-----------------------------+
| Tables_in_table_practice_db |
+-----------------------------+
| company_employees           |
| departments                 |
+-----------------------------+
    Output Note: If students also created relationship demo tables, the final table list may include additional demo tables such as departments_fk_demo, employees_fk_demo, or employees_relationship_demo.

    Suggested Marking Scheme

    The assignment can be evaluated using the following marking scheme.

    Criteria Marks Expected Quality
    Create Table Commands 20 Correct creation of departments and employees tables with suitable columns and data types.
    ALTER TABLE Operations 25 Correct use of ADD, MODIFY, RENAME COLUMN, and DROP COLUMN.
    Rename Table Operation 10 Correct use of RENAME TABLE and updated table name in later commands.
    DROP and TRUNCATE Understanding 15 Clear difference between deleting rows and deleting the complete table.
    Table Relationship Understanding 15 Correct explanation of how department_id connects employees and departments.
    Verification Commands 10 Correct use of SHOW TABLES and DESC.
    Presentation and Clarity 5 Clean formatting, readable SQL, and organized explanation.
    Total 100 Final assignment score.

    Common Mistakes and Corrections

    Common Mistakes

    • Creating tables without selecting a database using USE.
    • Forgetting semicolons after SQL statements.
    • Using INT for phone numbers.
    • Using FLOAT for salary instead of DECIMAL.
    • Trying to use the old table name after renaming it.
    • Confusing TRUNCATE TABLE with DROP TABLE.
    • Dropping a column or table without understanding data loss.
    • Not checking table structure after changes.

    Correct Approach

    • Always select the database before creating tables.
    • Use meaningful table and column names.
    • Use VARCHAR for phone numbers.
    • Use DECIMAL(10,2) for salary and money values.
    • Use the new table name after table rename.
    • Use TRUNCATE only when table structure should remain.
    • Use DROP only when the table is no longer needed.
    • Verify every structural change using DESC.

    Bonus Challenge Solution

    The bonus task is to create a small project table and then modify it using table operations.

    Step 1: Create Projects Table

    CREATE TABLE projects (
    project_id INT,
    project_name VARCHAR(100),
    start_date DATE
);

    Step 2: Add Project Budget Column

    ALTER TABLE projects
ADD project_budget DECIMAL(12,2);

    Step 3: Modify Project Name Size

    ALTER TABLE projects
MODIFY project_name VARCHAR(150);

    Step 4: Rename Table

    RENAME TABLE projects TO company_projects;

    Step 5: Check Final Structure

    DESC company_projects;

    Expected Structure

    +----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| project_id     | int           | YES  |     | NULL    |       |
| project_name   | varchar(150)  | YES  |     | NULL    |       |
| start_date     | date          | YES  |     | NULL    |       |
| project_budget | decimal(12,2) | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
    Bonus Best Practice This bonus task is useful because it combines CREATE TABLE, ALTER TABLE ADD, ALTER TABLE MODIFY, RENAME TABLE, and DESC in one practical flow.

    Sample Complete Student Answer

    Below is a short sample answer students can use for self-checking. 

    Assignment Title: Working with Tables

Question 1:
CREATE TABLE departments (
    department_id INT,
    department_name VARCHAR(100),
    location VARCHAR(100)
);

Question 2:
CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(15),
    salary DECIMAL(10,2),
    joining_date DATE,
    department_id INT
);

Question 3:
SHOW TABLES;
DESC departments;
DESC employees;

Question 4:
ALTER TABLE employees
ADD job_title VARCHAR(100);

Question 5:
ALTER TABLE employees
MODIFY phone VARCHAR(20);

Question 6:
ALTER TABLE employees
RENAME COLUMN employee_name TO full_name;

Question 7:
ALTER TABLE employees
DROP COLUMN job_title;

Question 8:
RENAME TABLE employees TO company_employees;

Question 9:
TRUNCATE TABLE company_employees;

Question 10:
DROP TABLE old_projects;

Question 11:
TRUNCATE removes all data but keeps the table structure.
DROP removes the complete table with its structure and data.

Question 12:
Table relationships connect related data between two tables.
For example, department_id in employees can connect employees with departments.

    Teacher Notes

    How to Evaluate Student Answers

    • Check whether students created tables with correct names and meaningful columns.
    • Check whether students used suitable data types for salary, phone, and dates.
    • Check whether students used ALTER TABLE correctly for adding, modifying, renaming, and dropping columns.
    • Check whether students understand the difference between table data and table structure.
    • Check whether students can clearly explain DROP TABLE versus TRUNCATE TABLE.
    • Check whether students understand table relationships at a basic level.
    • Give bonus marks if students include verification commands like SHOW TABLES and DESC.

    Final Answer Summary

    Topic Command Main Purpose
    Create Table CREATE TABLE Creates a new table structure.
    Add Column ALTER TABLE ADD Adds a new column to an existing table.
    Modify Column ALTER TABLE MODIFY Changes a column data type or size.
    Rename Column ALTER TABLE RENAME COLUMN Changes the name of an existing column.
    Drop Column ALTER TABLE DROP COLUMN Removes a column from a table.
    Rename Table RENAME TABLE Changes the name of a table.
    Truncate Table TRUNCATE TABLE Removes all records but keeps table structure.
    Drop Table DROP TABLE Deletes the complete table with structure and data.
    Table Relationship department_id connection Connects employees with departments.

    Final Learning Outcome

    After reviewing this solution, students should confidently understand how to create, modify, rename, empty, delete, and connect tables in MySQL. These table management skills are essential before learning constraints, joins, CRUD operations, and real-world database projects.

    Complete MySQL Database Mastery Course | Chapter 4 Assignment Solution