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

Table Statements

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

philraj / mysql-workshop-1 Goto Github PK

mysql-workshop-1's Introduction

MySQL Workshop 1 - Databases & Data Types

Workshop Contents

Common MySQL Data Types

Connecting to MySQL

Database Statements

Table Statements

Workshop Instructions

Exercise 1

Exercise 2

Exercise 3

Exercise 4

Exercise 5

Exercise 6

Exercise 7

Exercise 8

Exercise 9

Exercise 10 (Workshop Challenge)

mysql-workshop-1's People

Contributors

Watchers

Recommend Projects

Recommend Topics

Recommend Org