M

Lab 1: MySQL and Workbench Environment

Instructor: KASHMALA CHAUDHRY

Prof. Kashmala Chaudhry

 Lab 1: MySQL and Workbench Environment

Introduction
Data is a collection of raw facts and figures. It is being processed to obtain useful information to
assist organization in taking better decisions. Database is an organized collection of related data.
In order to manage the databases, Database Management Systems (DBMS) or Database Systems
offer sets of program and tools to efficiently access and manage the databases.
Relational data model is used to model the databases in Database Systems in the form of relation
(table) comprising of tuples (rows) and attributes (columns). Primary key is the unique identifier
for the relation and foreign key is used for referential purposes by the relation to include or refer
to other relations data.
Structured Query Language (SQL) is used to access and modify the relational database. It is the
most widely used query language supported by modern database management systems. SQL is a
nonprocedural language.

Objectives
• After performing this lab the students should be
• familiarized with MySQL and Workbench environment
• Load or unload a database in MySQL through MySQL Workbench
• Explore structure of tables in a database
• Create a test database and tables

Tools/Software Requirement

MySQL:: Download MySQL Installer

FREE available software to download from the above link, this link works for both 32-bit and 64-bit
processing systems.

Downloading Process:

1) Download the 331.3 M version from the above link

2) Skip the Login part and simply go on the option below to download.

Prof. Kashmala Chaudhry

 3) Install the downloaded application on your PC

4) The application will open up and you can see an option of “choosing a setup type ” on the
left side of the software panel. Choose the “custom” option for setup type →Click next

5) Now to select the products →expand the tree list on the left side one by one to select the
options mentioned below and drag them on the right
• MySQL server 8.0.34-X86
• MySQL workbench 8.0.34-X86
• MySQL shell 8.0.34-X86
6) Your screen should look like the figure below(Note few simple steps are not shown for the
sake of students to try easy steps on their own)

Prof. Kashmala Chaudhry

 7) Installation of products gets completed shown below

8) After clicking on next →the next step is product configuration-> this shows your MySQL
version 8.0.34 --> simply do “next” again → new display box of “Type and Networking”
Prof. Kashmala Chaudhry
 panel will be shown on top of the display, keep the options and settings as default click
on Next to set the root password for your workbench. Figures shown below
(Please Note: Root password is for the localhost which means this is your desktop
connection from which every time you connect to the database. You have to type in the
password for security purposes.)

9) Select 1st option for authentication method-> click next

10) Provide a simple but strong password that you can remember for this account e.g
“MySQL123”, repeat the password and click next
Prof. Kashmala Chaudhry
 11) Keep all the settings default for the next steps of window service ->next Server File
permissions -> next Apply configuration click on execute at the bottom and click finish
once done.

12) Note: Haven’t placed screenshots for the two steps mentioned above, so make sure you
are following through.

Prof. Kashmala Chaudhry

 13) After Finish → Apply configuration display will open → simply go next and keep all settings
default until you get another finish button and the cmd console (black window) will
appear along with your MySQL workbench software display. The steps are shown below
for the final installation of the complete software.

Prof. Kashmala Chaudhry

 14) If you click on the localhost you will be asked to enter the password and username
=root. (Make sure you put the right password and username and remember that
password).

Creating a new path for System Variables

The next step is to change your system variables and check your root password on the
command prompt. Why? Because we need to provide a path under computer system variables
for MySQL server to execute a proper function.

1) Go to your C drive→ program files → MySQL→MySQL Server 8.0 →bin .. copy the whole
path from your system.
C:\Program Files\MySQL\MySQL Server 8.0\bin

Prof. Kashmala Chaudhry

 2) Go to start menu of your PC and type environmental variables → open → system
properties dialogue box will open up → click the environmental variables button.

Prof. Kashmala Chaudhry

 3) Double click on the path under system variables -> a new dialogue box will open → click
new →and paste the path copied initially from the drive C of your system. Click OK and
close.

4) Open the command prompt from your PC starting menu.. type in the following
command to verify the version you have installed and press enter. You can see the
following results shown below.

5) Now to verify your password type “ mysql -u root -p” command on the cmd shown
below and if you get the same results as the figure below. You are all set with MySQL
workbench.

Prof. Kashmala Chaudhry

 6) Press Enter and Enter in your password that you have set for the root localhost.

Viola Success !
Prof. Kashmala Chaudhry
PART II
Understanding the Work Bench Environment
MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. You can
find it installed on your workstation. Select Start>Program>MySQL>Workbench to start up. The
home screen is shown in Figure 1:

Figure 1: Workbench Home Window

2. When MySQL Workbench first starts, it presents the Home window, which has three main
sections:
• List of MySQL Connections (help to manage and connect with MySQL server that runs
the database)
• Models (facilitate database design)
• Shortcuts (quick access)

3. MySQL Workbench provides extensive facilities for working directly with SQL code. Before
working directly with a live server, a connection must be created using the MySQL Connections
section on the Home Window. After a connection is established, it is possible to execute SQL
code directly on the server and manipulate the server using SQL code.

Making a connection with MySQL Server

1. To administer your MySQL Server, you must first create a MySQL connection. Creating a
MySQL connection is often the first action performed after installing MySQL Workbench.

Prof. Kashmala Chaudhry

2. To add a connection, click the [+] icon to the right of the MySQL Connections label on the
Home Window. This opens the Setup New Connection form. Fill out the connection detail as
shown in Figure 2:

Figure 2: MySQL Server Connection Setup Window

3. Define the Connection Name value as MyFirstConnection. Click OK. The connection will
appear in the MySQL Connections list on the Home Window.

4. Select the MyFirstConnection from Home Window, this opens the SQL Editor screen as
shown in Figure 3:

Prof. Kashmala Chaudhry

 Figure 3: SQL Editor

5. For querying use the SQL Query menu. It will open the SQL Query Panel as shown in Figure 4:

Prof. Kashmala Chaudhry

 Figure 4: SQL Query Panel

Using the Sakila database

1. Download and unzip the Sakila database. The archive contains three files: sakila-data.sql,
sakila-schema.sql, and sakila. mwb. (Note for the newer version of 8.0.34 the sakila
database is already installed inside workbench, you can simply go on the left side of the
panel and select schema to see sakila database or you can write 😊

“use sakila”
“show tables;”

Prof. Kashmala Chaudhry

2. Now right-click and select drop on an existing database schema named Sakila. You will load it
fresh.

3. From the File menu select Open SQL Script. Select sakila-schema.sql from the file selection
dialog. It would open the file in the query pan. Now select Execute All from the Query menu.
You can alternatively press the Execute All icon from the toolbar. Don’t forget to check the log
pan below the query tab. It mentions all errors, warnings, and messages.

4. Press the refresh icon in the schema section. You must see the Sakila database in the list of
the databases.

5. Repeat the process for sakila-data.sql file. If all goes well, you have successfully loaded the
Sakila database and now you can close the files (but not the Workbench).

6. Expand the Sakila database. Among the listed categories, expand Tables. You will see a list of
tables (relations) in the Sakila database.

7. Right-click on a table and explore the second option Table Inspector. It opens a new tab
showing relation schema, attribute names, and other metadata.
Prof. Kashmala Chaudhry
8. Afterwards right right-click on the same table and explore the first option: Select Rows. It will
open another tab for navigating through the contents of the relation.

9. The data view supports many different options such as sorting a column by selecting its
header, searching contents, deleting a row, adding another, or exporting the contents to an
external file. You can play with these options.

Examples:

SQL is a structure query language that is not a full-featured programming language; it is only a
data sublanguage, which has commands for data definition and processing. Data definition
commands are Data Definition Language while data processing commands are Data
Modification Language. In the first lab we will focus on DML commands for which we will use
Sakila Schema which we have already downloaded and is a sample database. With time we will
be able to design our database.
When we are working on any schema, we need to set that schema as default.

Right-click on Sakila and select the first option, “set as default schema” from the menu.as
shown below.

Figure 5: Setting Default Schema

Expand “Sakila”, there are further tabs as tables, views etc. Expand “Tables”.

Prof. Kashmala Chaudhry

You will find various tables including actor, class, category etc. All these tables have data stored.
You can retrieve or update data using SQL commands.

Explore various tables of the Sakila database and columns that are present in a particular table.
Observe how data is stored in a table.

Retrieving Data from Tables:

When you want to retrieve some data from a table, Select statement will be the most
commonly used statement.
Syntax of the Select statement is

Select Column name From Table name;

You can select multiple columns separated with commas or single columns.

Selecting all the columns

Select * from Customers;
Selecting specific columns
Select customer name, and customer id from Customers;
Example 1:
Write a query in Query 1 tab.

Select actor_id from actor;

It will select the actor Id column from the actor table which is present in Sakila database and
show you the results.

Example 2:
Describe table name; → (describe shows the column names of that table)
Select * → (this means selecting all tables from the databases)
Prof. Kashmala Chaudhry
Select * from film (this means selecting only those columns that are part of table film)

Example 3:
More examples for selected columns but all rows
SELECT title, rating, length FROM film;
Or getting all distinct values of rows from a certain column and table using a distinct clause.
Order by → helps to retrieve information in ascending order
Order by Desc -> helps to retrieve information in descending order.

PRO TIP: Always first observe all columns of all tables before executing your queries, so you may
know how your data looks like.

Prof. Kashmala Chaudhry

Lab Tasks
1. Write a query to Select columns “city_id” and “country_id” from the table “city” which
already exists in the “Sakila” database. (Observe the output when you execute the queries.)
2. Write a query to retrieve the first and last names of actors.
3. How many distinct actors last names are there?
4. Select those films with their titles that has rating “PG” or length < 100.
5. Select those payments where amount is greater then 11.00 which is last updated in
descending order.

Deliverables
Complete your lab tasks and write queries and the steps/screenshots of all the questions
attempted in a Word document and upload them on Blackboard. This is a Graded lab of 10
points (5 for software installation as I need a screenshot of your final software installed on your
PC, also 5 points for the questions with their queries written and outputs shown on the
software. A screenshot of each question is required inside a Word document.)

Submission Time and Date uploaded on BB.

Prof. Kashmala Chaudhry