Table of Contents

    Practice Assignment: Working with Tables

    Rumman Ansari July 05, 2026 8 views Subject Details
    Practice Assignment

    4.8 Practice Assignment: Working with Tables

    Practice essential MySQL table operations including creating tables, altering table structure, renaming tables, dropping tables, truncating tables, and understanding basic table relationships.

    Assignment Purpose: This assignment is designed to help students apply all important concepts from the Working with Tables chapter through practical MySQL tasks and real-world table management scenarios.

    Learning Objectives

    After completing this assignment, students should be able to create tables, change existing table structures, rename tables, delete table data, remove tables permanently, and understand how tables can be connected in a relational database.

    By the end of this assignment, students will be able to:

    • Create tables using CREATE TABLE.
    • Choose suitable data types for table columns.
    • Add new columns using ALTER TABLE ADD.
    • Modify existing columns using ALTER TABLE MODIFY.
    • Rename columns using ALTER TABLE RENAME COLUMN.
    • Drop columns using ALTER TABLE DROP COLUMN.
    • Rename tables using RENAME TABLE.
    • Differentiate between DROP TABLE and TRUNCATE TABLE.
    • Understand simple table relationships using common columns.

    Prerequisites

    Before starting this assignment, students should complete the following lessons from Chapter 4:

    Lesson No. Lesson Name Required Understanding
    4.1 Create Table How to create a new table with columns and data types.
    4.2 Alter Table How to change an existing table structure.
    4.3 Rename Table How to change the name of an existing table.
    4.4 Drop Table How to permanently delete a table.
    4.5 Truncate Table How to remove all records while keeping table structure.
    4.6 Table Relationships Basic understanding of how tables can be connected using common columns.

    Assignment Overview

    In this assignment, students will build a small employee management database. They will create tables, modify table structures, rename columns and tables, compare destructive table commands, and identify basic relationships between tables.

    1

    Assignment Title

    Working with Tables: Create, Modify, Rename, Delete, and Relate Tables

    This assignment focuses on table management skills that are required before students move into CRUD operations, constraints, joins, and real-world database projects.

    2

    Difficulty Level

    Beginner to Intermediate

    Students will write practical SQL commands and explain the purpose of each operation in simple words.

    3

    Estimated Completion

    Medium practical assignment

    Students should complete this assignment after finishing all lessons from Chapter 4.

    Database Setup Requirement

    Students should first create a practice database for this assignment and select it before creating any table.

    Required Database Name

    table_practice_db
    Hint: Students should use CREATE DATABASE and USE commands before starting table operations.

    Assignment Scenario

    A small company wants to manage employee and department information using MySQL. The company needs two tables: one for departments and one for employees. Later, the database structure needs to be updated by adding, modifying, renaming, and deleting columns. The company also wants to understand the difference between deleting table data and deleting the complete table.

    Based on the above scenario, complete all tasks given below.

    Assignment Questions

    Question 1: Create and Select a Practice Database

    Write SQL commands to create a database named table_practice_db and select it for use.

    Required Commands
    CREATE DATABASE USE

    Question 2: Create a Departments Table

    Create a table named departments with the following columns:

    Column Name Suggested Data Type Description
    department_id INT Stores unique department ID.
    department_name VARCHAR(100) Stores department name such as IT, HR, or Finance.
    location VARCHAR(100) Stores department location.

    Students should write a complete CREATE TABLE statement for the above structure.

    Question 3: Create an Employees Table

    Create a table named employees with the following columns:

    Column Name Suggested Data Type Description
    employee_id INT Stores unique employee ID.
    employee_name VARCHAR(100) Stores employee full name.
    email VARCHAR(100) Stores employee email address.
    phone VARCHAR(15) Stores employee phone number.
    salary DECIMAL(10,2) Stores employee salary.
    joining_date DATE Stores employee joining date.
    department_id INT Stores department ID to connect employee with department.

    Students should write a complete CREATE TABLE statement for the above structure.

    Question 4: View Tables and Table Structure

    Write SQL commands for the following:

    Required Commands

    • Show all tables inside table_practice_db.
    • Check the structure of the departments table.
    • Check the structure of the employees table.

    Question 5: Add a New Column

    Add a new column named job_title to the employees table.

    Column Name Data Type Purpose
    job_title VARCHAR(100) Stores employee job role such as Developer, Manager, or Accountant.

    Students should use ALTER TABLE with ADD.

    Question 6: Modify an Existing Column

    Modify the phone column in the employees table from VARCHAR(15) to VARCHAR(20).

    Hint: Use ALTER TABLE with MODIFY to change the size or data type of an existing column.

    Question 7: Rename a Column

    Rename the column employee_name to full_name in the employees table.

    Important Note After renaming a column, the old column name should not be used in later queries.

    Question 8: Drop a Column

    Remove the job_title column from the employees table.

    Hint: Use ALTER TABLE with DROP COLUMN.

    Question 9: Rename a Table

    Rename the table employees to company_employees.

    Required Operation
    employees → company_employees

    Question 10: Truncate a Table

    Write an SQL command to remove all records from company_employees while keeping the table structure.

    Expected Understanding TRUNCATE TABLE removes all rows but keeps the table structure.

    Question 11: Drop a Table

    Create a temporary table named old_projects and then write the SQL command to permanently delete it.

    Temporary Table Columns Final Task
    old_projects project_id, project_name Delete this table permanently using DROP TABLE.
    Important Warning DROP TABLE deletes the complete table structure and data permanently.

    Question 12: Difference Between DROP TABLE and TRUNCATE TABLE

    Create a comparison table between DROP TABLE and TRUNCATE TABLE.

    Point of Difference TRUNCATE TABLE DROP TABLE
    Main Purpose Write answer here Write answer here
    Effect on Data Write answer here Write answer here
    Effect on Structure Write answer here Write answer here
    Use Case Write answer here Write answer here
    Example Command Write example here Write example here

    Question 13: Basic Table Relationship

    Explain how the departments table and employees table can be related using the department_id column.

    Relationship Concept
    One Department Many Employees

    Students should write a short explanation of how one department can have many employees and how department_id can connect both tables.

    Question 14: Foreign Key Preview

    This is an optional advanced question. Write a table structure where department_id in an employee table refers to department_id in a department table.

    Note: Foreign keys will be explained in detail in the constraints chapter. Here, students only need to understand the basic idea.

    Optional Practical Task

    If students have MySQL Server and MySQL Workbench installed, they should run the following operations step by step and take screenshots.

    Step 1: Create and Select Database

    CREATE DATABASE table_practice_db;

USE table_practice_db;

    Step 2: Create Departments Table

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

    Step 3: Create Employees Table

    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
);

    Step 4: View Tables

    SHOW TABLES;

    Step 5: Check Table Structures

    DESC departments;

DESC employees;

    Step 6: Apply ALTER TABLE Operations

    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;

    Step 7: Rename the Table

    RENAME TABLE employees TO company_employees;

    Step 8: Verify Final Table Structure

    SHOW TABLES;

DESC company_employees;
    Screenshot Requirement: Students should take screenshots after creating tables, after modifying the table, and after renaming the table.

    Submission Guidelines

    Students should submit their assignment in a clean and organized format. SQL commands should be written properly, and screenshots should be attached if practical execution is completed.

    Submission Item Required? Description
    Written Answers Yes Answers for comparison, relationship, and concept-based questions.
    SQL Commands Yes All commands for creating, altering, renaming, truncating, and dropping tables.
    Table Structure Screenshots Optional Screenshots of DESC departments and DESC employees or DESC company_employees.
    Final Table List Screenshot Optional Screenshot after running SHOW TABLES.
    File Format Yes Submit as PDF, DOCX, SQL file, or handwritten scanned copy if allowed by the instructor.

    Evaluation Criteria

    The assignment can be evaluated using the following marking scheme.

    Criteria Marks What Will Be Checked?
    Database Setup 10 Correct database creation and selection using CREATE DATABASE and USE.
    Create Table Commands 20 Correct creation of departments and employees tables.
    ALTER TABLE Operations 25 Correct use of ADD, MODIFY, RENAME COLUMN, and DROP COLUMN.
    Rename Table Operation 10 Correct renaming from employees to company_employees.
    DROP and TRUNCATE Understanding 15 Clear difference between removing all data and deleting the complete table.
    Table Relationship Understanding 10 Correct explanation of relationship between departments and employees.
    Presentation and Clarity 10 Readable SQL, proper formatting, and organized answers.
    Total 100 Final assignment score.

    Hints for Students

    Useful Hints

    • Use CREATE TABLE to create a new table.
    • Use ALTER TABLE when you need to change table structure.
    • Use ADD to add a new column.
    • Use MODIFY to change a column data type or size.
    • Use RENAME COLUMN to rename an existing column.
    • Use DROP COLUMN to remove a column.
    • Use RENAME TABLE to rename a table.
    • Use TRUNCATE TABLE to remove all rows but keep table structure.
    • Use DROP TABLE only when the table is no longer needed.
    • Use DESC table_name; to check table structure after changes.

    Common Mistakes to Avoid

    Avoid These Mistakes

    • Creating tables without selecting the database.
    • Forgetting semicolons after SQL statements.
    • Using spaces in table or column names.
    • Using INT for phone numbers.
    • Using FLOAT for salary instead of DECIMAL.
    • Using the old table name after renaming it.
    • Confusing DROP TABLE with TRUNCATE TABLE.
    • Dropping columns without understanding data loss.

    Best Practices

    • Use lowercase table names with underscores.
    • Use meaningful names such as employees and departments.
    • Use VARCHAR for names, email, phone, and location.
    • Use DECIMAL for salary or money values.
    • Verify every table change using DESC.
    • Take backup before using destructive commands.
    • Use TRUNCATE only when you want to empty a table.
    • Use DROP only when you want to delete the full table.

    Bonus Challenge

    Create a new table named projects, modify it, and then rename it. This bonus task will help students practice multiple table operations together.

    Step Task Required SQL Concept
    Step 1 Create a table named projects. CREATE TABLE
    Step 2 Add a column named project_budget. ALTER TABLE ADD
    Step 3 Modify project_name from VARCHAR(100) to VARCHAR(150). ALTER TABLE MODIFY
    Step 4 Rename projects to company_projects. RENAME TABLE
    Step 5 Check final table structure. DESC
    Bonus Focus
    Create Alter Modify Rename Verify

    Student Answer Template

    Students can use the following template to organize their answers. 

    Assignment Title: Working with Tables

Student Name:
Course Name:
Chapter:
Submission Date:

Question 1 Answer:

Question 2 Answer:

Question 3 Answer:

Question 4 Answer:

Question 5 Answer:

Question 6 Answer:

Question 7 Answer:

Question 8 Answer:

Question 9 Answer:

Question 10 Answer:

Question 11 Answer:

Question 12 Answer:

Question 13 Answer:

Question 14 Answer:

Optional Practical Task Screenshot/Observation:

Bonus Challenge Answer:

    Final Learning Outcome

    After completing this assignment, students will gain practical confidence in creating, modifying, renaming, emptying, deleting, and relating MySQL tables. These skills are essential for CRUD operations, constraints, joins, and real-world database projects.

    Complete MySQL Database Mastery Course | Chapter 4 Practice Assignment