Creating Tables and Inserting Data in MySQL

1 hour
  • 5 Learning Objectives

About this Hands-on Lab

In this lab, we’ve been tasked with creating two new tables for our company’s corporate database. Once they’re created, we need to insert some data into each of them.

This will give us some hands-on experience in creating tables and inserting data for databases in a MySQL server.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Log into the MySQL Server as `root` and Change to the `corporate` Database

Login to the MySQL server:

# mysql -u root -p

Change to the corporate database:

mysql> USE corporate;

Create a Table Named `products` in the `corporate` Database with the Characteristics Provided in the Instructions

Create the products table with the following command:

mysql> CREATE TABLE products (productID INT PRIMARY KEY, productType VARCHAR(50), operatingSystem VARCHAR(50));

Create a Table Named `orders** in the `corporate` Database with the Characteristics Provided in the Instructions

Create the orders table with the following command:

mysql> CREATE TABLE orders (orderID INT PRIMARY KEY, userName VARCHAR(50) UNIQUE, orderType VARCHAR(255), purchaseDate DATE);

Insert Data into the `products` Table According to the Information Provided in the Instructions

Insert data into the products table with the following command:

mysql> INSERT INTO products (productID,productType,operatingSystem) VALUES (1,'laptop','ubuntu'),(2,'desktop','windows'),(3,'server','rhel');

Insert Data into the `orders` Table According to the Information Provided in the Instructions

Insert data into the orders table with the following command:

mysql> INSERT INTO orders (orderID,userName,orderType,purchaseDate) VALUES (1,'aaron','laptop','2018-02-23'),(2,'stosh','desktop','2018-01-25'),(3,'kenny','server','2019-12-10');

Additional Resources

Our company is developing a new line of products and we've been tasked with creating two new tables in the corporate database. The first table, products, will contain data about the new product line. The second table, orders will contain order information from users.

We'll use the following information to create the products table in the corporate database:

  • The first column should be named productID using the integer (INT) data type and be set as the primary key.
  • The second column should be named productType and use the VARCHAR(50) data type.
  • The third column should be named operatingSystem and use the VARCHAR(50) data type.

This information applies to the creating the orders table in the corporate database:

  • The first column should be named orderID using the integer (INT) data type and be set as the primary key.
  • The second column should be named userName using the VARCHAR(50) data type and be set as unique.
  • The third column should be named orderType and use the VARCHAR(255) data type.
  • The fourth column should be named purchaseDate and use the date data type.

Insert the following data into the products table:

  • productID = 1, productType = laptop, operatingSystem = ubuntu
  • productID = 2, productType = desktop, operatingSystem = windows
  • productID = 3, productType = server, operatingSystem = rhel

Insert the following data into the orders table:

  • orderID = 1, userName = aaron, orderType = laptop, purchaseDate = 2018-02-23
  • orderID = 2, userName = stosh, orderType = desktop, purchaseDate = 2018-01-25
  • orderID = 3, userName = kenny, orderType = server, purchaseDate = 2019-12-10

Additional Information:

  • Tasks in the MySQL database should be performed as the root user.
  • Linux4me! is the root user's MySQL password
  • Do not update the password for the MySQL root user as it is used for grading purposes.

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Get Started
Who’s going to be learning?

How many seats do you need?

  • $499 USD per seat per year
  • Billed Annually
  • Renews in 12 months

Ready to accelerate learning?

For over 25 licenses, a member of our sales team will walk you through a custom tailored solution for your business.


$2,495.00

Checkout
Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!