In this lab we will become familiar with using MySQL Workbench.
SQL (sometimes pronounced SEQUEL) stands for Structured Query Language. SQL is used to communicate with a database.
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:
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.
To run the script:
Alternatively:
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;
Output the definition of the product table.
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';
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 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%';
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;
Return the name and description of all products supplied by all suppliers from London.