MySQL Lab Guide - Cengage EMEA

MySQL Lab Guide

A supplement to: Database Systems: Design, Implementation and Management

(International Edition)

Rob, Coronel & Crockett (ISBN: 9781844807321)

MySQL Lab Guide

Table of Contents

Lab

Title

Page

1

Introduction to MySQL

4

2

Building a database: Table by Table

15

3

Data Manipulation Commands

36

4

Basic SELECT statements

47

5

Advanced SELECT statements

63

6

Joining Tables

75

7

SQL functions

90

8

Subqueries

111

9

Views

123

Keeley Crockett

2

MySQL Lab Guide

Introduction to the MySQL Lab Guide

This lab guide is designed to provide examples and exercises in the fundamentals of SQL

within the MySQL environment. The objective is not to develop full blown applications

but to illustrate the concepts of SQL using simple examples. The lab guide has been

divided up into 9 sessions. Each one comprises of examples, tasks and exercises about a

particular concept in SQL and how it is implemented in MySQL.

On completion of this 9 week lab guide you will be able to:

?

Create a simple relational database in MySQL.

?

Insert, update and delete data the tables.

?

Create queries using basic and advanced SELECT statements

?

Perform join operations on relational tables

?

Use aggregate functions in SQL

?

Write subqueries

?

Create views of the database

This lab guide assumes that you know how to perform basic operations in the Microsoft

Windows environment. Therefore, you should know what a folder is, how to maximize or

minimize a folder, how to create a folder, how to select a file, how you maximize and

minimize windows, what clicking and double-clicking indicate, how you drag, how to

use drag and drop, how you save a file, and so on.

MySQL, is one of the most popular Open Source SQL database management systems.

The lab guide has been designed on MySQL version 5.0.45 running on Windows XP

Keeley Crockett

3

MySQL Lab Guide

Professional. The MySQL Web site () provides the latest

information about MySQL database management system.

It is important to note that MySQL is an open source database and is continually under

development. Each version and sub-version may implement SQL syntax differently and

changes are being made constantly. There are also problems with upward compatibility

between different versions. For example some SQL operations that work in versions 3.0

and 4.0 do not work in version 5.0. Furthermore, different variants of a version are

released in response to bugs that have been found by database developers who are using

the latest versions in their work. If an SQL command does not work as expected or

shown in this guide, please consult the MySQL web site for more information.

Keeley Crockett

4

MySQL Lab Guide

Lab 1: Starting MySQL

The learning objectives of this lab are to

?

Learn how to start MySQL

?

Learn how to use the MySQL command line client window

?

Obtain help in MySQL

1.1 Starting MySQL

Before starting this guide, you must obtain a user ID and a password created by your

database administrator in order to log on to the MySQL RDBMS. How you connect to

the MySQL database depends on how the MySQL software was installed on your server

and on the access paths and methods defined and managed by the database administrator.

You may therefore need to follow specific instructions provided by your instructor,

College or University. This section will describe how to start MySQL from a Windows

XP installation of MySQL 5.0.45.

To start MySQL you would:

1. Select the Start button

2. Select All Programs and then MySQL

3. Select MySQL Server 5.0

4. Click on the MySQL Command line client

Keeley Crockett

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download