MySQL Workshop 1 - Databases & Data Types

Workshop Contents

Common MySQL Data Types

• INT
• BOOLEAN aka TINYINT(1)
• VARCHAR
• DATETIME
• ENUM

Connecting to MySQL

# Start MySQL Instance
$ mysql-ctl start

# Connect to the MySQL Instance
$ mysql -u<USERNAME> -p<PASSWORD>

Database Statements

• Show Databases
• Select Database
• Show Tables
• Create Database
• Drop Database

Table Statements

• Create Table
• Alter Table
• Truncate Table
• Drop Table
• Describe Table

Workshop Instructions

• Fork this repository
• Create a new Cloud9 Workspace
• For every exercise in this Workshop:
  • Create a new branch off from "master" named "exercise-n"
  • Create a new file named "exercise-n.txt", containing:
    • The SQL Statement used, when applicable
    • The SQL Statement results, when applicable
  • Create a pull request

Exercise 1

• Connect to the MySQL instance within your Workspace using your Cloud9 username

Exercise 2

• Create a database named decodemtl_test within MySQL
• Create a database named decodemtl_addressbook within MySQL

Exercise 3

• Remove database named decodemtl_test from MySQL

Exercise 4

• Return the list of databases within MySQL

Exercise 5

• Create table Account for database decodemtl_addressbook
• Create table AddressBook for database decodemtl_addressbook
• Create table Entry for database decodemtl_addressbook
• Create table Test for database decodemtl_addressbook

Exercise 6

• Remove table Test for database decodemtl_addressbook

Exercise 7

• Return list of tables within database decodemtl_addressbook

Exercise 8

• Reflect the data model shown in schema/addressbook_denormalized.png within database decodemtl_addressbook
  • Account.id is a primary auto-increment key
  • AddressBook.id is a primary auto-increment key
  • Entry.id is a primary auto-increment key
  • Entry.type is an ENUM column permitting home, work and other
  • Entry.subtype is an ENUM column permitting phone, address and email

Exercise 9

• Create a data model representing a Barn with Chickens 🐣
• This model should provide answers to the following questions:
  • How many rooster, hen and chicks existed in the Barn on a specific date
  • How many chicks will come to age on a specific date

Exercise 10 (Workshop Challenge)

• Create a data model representing a Hotel with Floors and Rooms 🏨
• This model should provide answers to the following questions:
  • The list of Rooms available for rent on a specific date
  • The list of Rooms which can be occupied by at least 3 people on a specific date
  • The amount of unrentable Rooms (janitor closets, public laundry room, gym, etc.)
  • The amount of Rooms having a private Kitchen
  • The average amount of windows per Floor
  • The amount of Floors having Rooms with carpets