Objectives

In this lab we will become familiar with using MySQL Workbench.

What is SQL?

SQL (sometimes pronounced SEQUEL) stands for Structured Query Language. SQL is used to communicate with a database.

Table Basics

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are uniquely identified by their name and are comprised of columns and rows. Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns. Here is a sample table called "weather". The values city, state, high, and low are the columns. The rows contain the data for this table:

Our example - Suppliers and Products

We have built a Suppliers and Products database with the following tables: supplier and product.

The tables are populated and we will use this data in this lab. The schema is stored in supplierproducts.sql.

• Open MySQL Workbench and log in using password root.

To run the script:

• Under the File menu, choose Run SQL Script ….
• When the dialog box opens, select supplierproducts.sql.
• Click the button Open and on the next dialog box, click Run.
• When the Operation has run successfully, click Close.

Alternatively:

• Choose Open SQL Script ….
• When the dialog box opens, select supplierproducts.sql.
• Click the button Open. This will open the script in a new tab.
• To execute the script, choose the third option on the window.

Getting Started

First, we will open a new tab. Select File and then New Query Tab.

To execute a single command, choose the fourth option on the window.

Enter (and execute) the following command to select the database that we want to work with:

 use mysupplierdb;

To see what tables exist in the database, enter (and execute) the following command:

 show tables;

To see the defintion (layout) of each table, use the DESC or DESCRIBE command. So to see the defintion of the supplier table, enter (and execute) the following command:

 desc supplier;

To do

Output the definition of the product table.

Simple Selection

To see all the rows (records) and columns (data fields) from the supplier table, enter (and execute) the following command:

 select * from supplier;

To select a subset of the columns, you need to specify the columns by name. For example, to return the columns (fname and lname) from the supplier table, enter (and execute) the following command:

 select fname, lname from supplier;

To select a subset of rows (records), you need to include a WHERE clause with some condition(s). For example, to return all suppliers form Dublin. The condition would be location = 'Dublin'. Enter (and execute) the following command:

 select * from supplier where location = 'dublin';

To do

1. Return all rows and columns from the product table.
2. Return the name and description of all products.
3. Return all product details for the product identified by productCode 'p13111';
4. Return the fname and lname for all suppliers from London.
5. Return the name and description for the product identified by productCode 'p13111';

The WHERE clause

As we saw already, conditions in the WHERE clause can contain the = operator, as in the following command:

 select * from product where price = 70;

To return all product details for products that are price at 70 euro or more, enter (and execute) the following command:

 select * from product where price >= 70;

To return all product details for products that are in the price range (70 - 90 inclusive), enter (and execute) the following command:

 select * from product where price between 70 and 90;

You could also use the following command where you have 2 conditions and both need to be truem, so you use AND:

 select * from product where price >= 70 and price <= 90;

In a query like this, using BETWEEN ... AND is the better option.

To do

1. Return all product details for products that cost 50 euro or less.
2. Return all product details for products that have a stock quantity in the range 100 - 400 inclusive.

More on the WHERE clause

To return all red products, enter (and execute) the following command:

 select * from product where colour = 'red';

To return all red, grey or white products, enter (and execute) the following command:

 select * from product where colour = 'red' or colour = 'grey' or colour ='white';

A shorter (and better) way of writing this statement would be:

 select * from product where colour in('red','grey','white');

Enter (and execute) the following command to return all products whose name includes the term Skechers:

 select name from product where name = 'Skechers';

The above statement returns no records as there is no exact match with the term Skechers. To return all products whose name begins with the term Skechers, we need to use LIKE for pattern matching. The % wildcard can appear before and/or after the term.

Enter (and execute) the following command:

 select name from product where name like 'Skechers%';

To do

1. Return all suppliers whose last name (lname) is Cooney, Jones or Lacey.
2. Return all products whose name includes the term Munster.

The JOIN clause

If we want to return data from both tables we will need to join the tables together using the JOIN clause. The join we will use is the Natural Join. This joins tables where there is a match between the primary key value of one table and the foreign key value of the other.

In the following example we join the tables together and return all columns. Enter (and execute) the following command:

 select * from supplier natural join product;

The above statement will work if the primary key and foreign key values have the same name. If they do not, you could use the following:

 select * from supplier join product on supplier.supplierId = product.supplierId;

This will return all columns and the common column is duplicated.

Another example that returns the product name and the supplier name that supplies the product, enter (and execute) the following command:

 select name, fname, lname from supplier natural join product;

To do

Return the name and description of all products supplied by all suppliers from London.

Exercises

1. Try to think of other queries that you would like to execute and write the SQL for them.
2. Have a look at the following SQL Tutorial.