1.6 LAB - MySQL Workbench review (Sakila)

Introduction

The purpose of this lab is to gain familiarity with MySQL Workbench. The lab also ensures the correct version of the Sakila sample database is installed on your computer, for use in other zyLabs.

This lab has three parts:

Install the Sakila database.

Run a simple query.

Recreate a Sakila table in the zyLab environment.

Only the third part is graded.

Install the Sakila database

This lab requires access to MySQL Server via MySQL Workbench. Most students install and access MySQL Server and MySQL Workbench on their personal computer. Installation instructions are available at MySQL Server installation and MySQL Workbench installation.

To create Sakila tables in MySQL, download the Sakila schema file, open MySQL Workbench, and click the following menu commands:

Click 'File' > 'Open SQL Script' and open the Sakila schema file.

Click 'Query' > ''Execute (All or Selection)'.

To load sample data to the Sakila tables, download the Sakila data file and repeat steps 1 and 2 with this file.

Run a simple query

Refer to the following MySQL Workbench screenshot, taken from a Mac computer. Workbench looks slightly different on Windows.

If 'sakila' does not appear under 'Schemas', click the refresh icon, in the red circle above. If 'sakila' still does not appear, repeat the installation process or request assistance.

Depending on Workbench configuration, a different Limit may appear after 'Select Rows'.

When 'sakila' appears under 'Schemas':

Click > to expand 'sakila'.

Click > to expand 'Tables'.

Right-click 'film'.

Click 'Select Rows - Limit 1000'.

MySQL Workbench executes SELECT * FROM film; and displays 1000 films:

Recreate a Sakila table in the zyLab environment

To recreate the actor table in the zyLab environment:

Right-click 'actor'.

Select 'Copy to Clipboard' > 'Create Statement' to copy the CREATE TABLE statement to your clipboard.

Paste the CREATE TABLE statement into the zyLab Main.sql box.

Delete the following characters for compatibility with the zyLab environment:

COLLATE=utf8mb4_0900_ai_ci

all apostrophes (`)

The CREATE TABLE statement creates actor columns, keys, and indexes. No result is displayed in Develop mode. The tests in Submit mode verify that the zyLab and Sakila actor tables are identical.

MySQL Workbench Local instance 3306 SCHEMAS Limit to 1000 rows MySQL Workbench