Managing Databases and

Tables

DDL Commands
Hands-on Exercise
 Index
1 Objectives ..................................................................................................................... 3
2 Overview ....................................................................................................................... 4
3 Prerequisites ................................................................................................................ 5
4 Preliminary Concepts .................................................................................................. 6
4.1 DDL Commands................................................................................................. 6
4.2 Creating a Database .......................................................................................... 6
4.2.1 Guided Exercise – Creating a Database/Schema................................. 7
4.3 Creating a Table................................................................................................. 8
4.3.1 Guided Exercise – Creating Table1....................................................... 9
4.3.2 Guided Exercise – Creating Table2..................................................... 10
4.3.3 Guided Exercise – Creating Table3..................................................... 11
4.3.4 Guided Exercise – Viewing the Created Tables.................................. 12
4.3.5 Guided Exercise – Using the DESCRIBE Command ......................... 13
4.3.6 Guided Exercise – Using the SHOW CREATE TABLE Command .... 13
4.4 Managing Database and Tables...................................................................... 14
4.4.1 Guided Exercise: Adding a Table Column .......................................... 14
4.4.2 Guided Exercise: Modifying a Table Column ...................................... 15
4.4.3 Guided Exercise: Removing a Table Column ..................................... 16
4.4.4 Dropping a Table ................................................................................. 17
4.4.5 Dropping a Database ........................................................................... 17

5 Practice Exercise ................................................................................. 18

Copyright © 2021 Accenture

All rights reserved.
Accenture and its logo are trademarks of Accenture.
1 Objectives
After completing the hands-on exercise, you will be able to:
– Create a database with MySQL Workbench
– Create a table in the database
– Manage the structure of the table according to business requirements
2 Overview
This hands-on document provides scenario-based guided exercises to help you
execute Data Definition Language (DDL) commands for starting off database
development. The practice exercises at the end enable you to assess your
understanding of the concepts.

▪ Tools to perform exercise: MySQL Database Server, MySQL Workbench IDE

▪ Estimated time needed: 30 minutes
3 Prerequisites
1. Install and configure the MySQL Server database
2. Access the database through MySQL Workbench’s SQL Worksheets
4 Preliminary Concepts

4.1 DDL Commands

Scenario

An organization has decided to use an HR application having MySQL database for

maintaining and accessing details of all employees in the organization.

The HR manager will use this application to:

• Add departments, jobs, and employee records in the system
• Modify records, such as when an employee changes job profile
• Delete records of employees who have resigned
• Search for an employee’s details to generate various reports

The organization has hired you to manage the database for this application.

4.2 Creating a Database

Before creating the tables for the application, you need to create the database in
which they will be created.

To create a database in MySQL server, use CREATE DATABASE or CREATE

SCHEMA command.

The syntax for creating a database in MySQL is shown below.

Note: In MySQL, the terms database and schema are synonymous to each other.
Database will only be created if it does not exist from before. If it does, then the
optional IF NOT EXISTS keyword throws an error while executing the command.

Guided Exercise – Creating a

Database/Schema

The HR Manager would like to have a database/schema to store and process the
tables required for their department.

i. Create a database/schema

a. Open the already installed MySQL Workbench

b. Open a new SQL Worksheet by connecting to the server and execute
the below command

c. To execute the command, select it and click the Execute icon or

press Ctrl+Enter

You should be able to see the below output in the MySQL Workbench
Output pane
Output

To use the database, execute the given command

You should be able to see the below output in the MySQL Workbench
Output pane

Output
 4.3 Creating a Table

Now let us examine how to create tables in the MySQL database.

i. Syntax to Create a Table

• The command is CREATE TABLE

• It will be followed by the table name
• The table structure i.e., the columns in it begins with a "(" and ends with ")"
• A table can have one or more attributes/columns separated by a “,” (comma)
• Each column needs to be assigned with a datatype
• You can define the column options as well, for example NOT NULL will not allow
you to leave the field as blank
• Finally, you can define the primary key options for the table
• “;” terminates the MySQL CREATE TABLE statement

ii. Choosing Datatypes for Columns

• As the name suggests, a column’s datatype dictates what type and size of data
can be stored in it
• For the column, you can select a datatype based on your requirements. A well-
designed database uses the most suitable data types for each column
• When you are choosing a data type for a column, consider the following things:
o the type/kind of data the column needs to store. For example, if you need
to store pin code of locations, which is a number, use a numeric datatype
rather than a character datatype
 o The amount (size) of the data per value being stored. For example, pin
code needs 6 digits to be stored

iii. Datatypes – Overview

• Numeric data types store data like employee’s salary

o Integer, floating-point (for approximate rounded-off data) and fixed-point
(for exact-values) data
• Temporal data types store data and time information like birthday and hire date
o Date and time data
• String data types store data like names, email, titles
o Character data

Guided Exercise – Creating Table1

Scenario - The HR Manager would like to input department’s data (ID and Name) in
a table. Create the necessary department table with DepartmentID as the primary
key.

Execute the below command in MySQL Workbench’s worksheet to

create department table

The output pane should confirm the table creation. Ignore the warnings

Output

Here is the breakdown of various components you have used to create a table.
• The column named DepartmentID is assigned the datatype of INT with a
maximum possibility of 3 digits, and no null values are allowed
• The column named DepartmentName is assigned the VARCHAR data type, a
length of 20 characters, and no null values are allowed
• Here, both the DepartmentID and DepartmentName are mandatory columns
since we specified NOT NULL as the column option
• We made DepartmentID as the primary key for the table

Guided Exercise – Creating Table2

The HR Manager would like to store the Job’s data in a table. Create the Job table
with JobID as CHAR of 7 and JobTitle as VARCHAR of 30 with both being NOT
NULL columns and the JobID being the primary key.

Execute below command in MySQL Workbench’s worksheet to create

Job table

The output pane should confirm the table creation

Output
 Guided Exercise – Creating Table3

The HR Manager would like to store employees’ data in a table. Create the
employee table to store employees’ information.

Execute the below command in MySQL Workbench’s worksheet to

create employee table

The output pane should confirm the table creation. Ignore the warnings

Output

• As you can see, we made use of ENUM and various data types to meet the
requirements of storing employees’ data
• The foreign keys on the DepartmentID and JobID maintain referential integrity by
enforcing a link between the data in the Department, Job and Employee’s tables
• A foreign key in the child table (Employee) references the primary (or unique) key
in the parent table (Department and Job), and it prevents actions that could
cause inconsistency between the child and parent table
• So, for example, it prevents a row from Department table, a parent record, from
being deleted when there are Employee records for it
• It also prevents data from being inserted into the foreign key column of the child
table (Employee) that has no matching primary value in the parent table
(Department or Job)
• In other words, Department or Job records cannot be deleted when there are
employees working in that Department or Job, and Employees cannot be
registered with Department and Jobs which are not recorded in those respective
tables

Guided Exercise – Viewing the Created

Tables
To confirm whether the tables are available in the MySQL Workbench on the
Schemas tab, expand the empmanagement database and then the tables to view
the tables in the database.

Alternatively, execute the below command in the SQL worksheet of the

MySQL Workbench

The output pane should display the tables in the database

Output
 Guided Exercise – Using the DESCRIBE
Command
After creating the tables, if you need to see its structure, use the DESCRIBE
command.

Execute the below command in the SQL worksheet of the MySQL

Workbench

The output pane should display the structure of the table

Output

Guided Exercise – Using the SHOW CREATE

TABLE Command
To view the command, which was used to create the table, use the SHOW CREATE
TABLE <tableName> command.

Execute the below command in the SQL worksheet of the MySQL

Workbench
 The command used to create the table will be displayed as a result

Output

4.4 Managing Database and Tables

Guided Exercise: Adding a Table Column

To add or include a new attribute/column to the table, which is already created, use
the ALTER TABLE command with ADD COLUMN option.

Syntax:

Steps

The HR Manager asked you to include the ManagerID column to the employee’s
table.

In this case, you can make use of the ALTER TABLE command on the employee
table with the ADD COLUMN option to include the ManagerID column.
 Execute below command in the SQL Worksheet of the MySQL
Workbench

The output pane should confirm the command execution

Output

There can be various reasons for modifying a table structure, for instance:

• You omitted a column

• The column definition is registered incorrectly
• An unnecessary column is included

Guided Exercise: Modifying a Table Column

To modify/change a column (column properties) of an existing table, you can use the
ALTER TABLE statement with MODIFY COLUMN.

Steps

The HR Manager asked you to increase the Salary column’s size from precision 7 to
9.
 Execute the below command in the SQL Worksheet of the MySQL
Workbench

The output pane should confirm the command execution.

Ignore the warnings

Output

Guided Exercise: Removing a Table Column

For removing/dropping a column from a table, you can use the ALTER TABLE
command with DROP COLUMN option.

Steps

Assume that the HR Manager decides a particular column might not be necessary
for the employee’s table anymore and asks you to remove ManagerID from the
same.

Execute the below command in the SQL Worksheet of the MySQL

Workbench
 The output pane should confirm the command execution. Ignore the
warnings

Output

Dropping a Table
With the DROP command, you can remove one or more tables at once.

• The IF EXISTS clause is optional

• You need to use this command with caution as it also removes the data of the
table and this operation cannot be reversed

Dropping a Database
To remove an existing database and the tables contained in it, execute the DROP
DATABASE command.

Use this command with caution as it removes the tables included in the database
with their data and this command cannot be reversed.
5 Practice Exercise
Create the necessary tables for the below requirements.

A school management needs to maintain their data in the MySQL database. They
need the following tables with necessary attributes and constraints:

• Courses
(CourseID will be a unique number, Course Name like Java, C, SQL, Python,
etc.)
• Students
(StudentID will be a unique number, Student Name, Contact Number, Joining
Date)
• Assessments
(StudentID must be a valid one from the student table, CourseID must be a valid
one from the course table, Marks)
Here, StudentID and CourseID combination must be unique across the table.

You have completed DDL Commands hands-on exercises in Managing Databases

and Tables.
 Copyright © 2021 Accenture
All rights reserved.
Accenture and its logo are trademarks of Accenture.