WORKSHOP 2: in This Workshop You Will Create a Database Called Company , Define Its Tables

WORKSHOP 2: In this workshop you will create a database called ‘company’, define its tables, populate it with data and learn to use the SELECT statement to display data and the WHERE statement to filter data. Note: SQL statements are case-insensitive. However, many developers use uppercase for all SQL keywords and lowercase for column and table names as this makes code easier to read and debug

First, get a copy of the file ‘creaPopl.sql’ from the module website and save it on your home drive. Open this file from notepad and scan through the code (do not alter any of it!). Some of it should be familiar from the previous workshop, as it creates the tables that we have looked at before, and sets up the primary/foreign key relationships. The rest of the code is a series of INSERT statements to populate the tables with sample data, such as:

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)

VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', '');

This code lists the field names and the data items that should go into them. Rather than enter this code line-by-line, we are going to run all the code at once in MySQL, to create a database called ‘company’ that contains the tables and data described in the SQL code.

a) Highlight all the code in creaPopl.sql and do Edit\Copy

======

b) Log into SQL --- HOW TO LOG INTO SQL

AT THE UNIVERSITY: Start\AllProgs\Departmental Software\Computing\MySQL

AT HOME:

Start up MySQL

Trigger MySQL with root user privileges (allows you to do anything):

Mysql –u root

======

Create the ‘company’ database:

CREATE DATABASE company;

Tell SQL to use this database:

USE company;

Company is an empty database with no tables or entries, we will now populate it with the table structures and sample data from ‘creaPopl.sql’:

c) Click on the mySQL window, right click your mouse and select ‘paste’:

The code from ‘creaPopl.sql’ should now run and add the tables and data to the ‘company’ database

Don't worry if you get a series of bleeps and error messages - in this case it is because the 'company' database has already been created and populated by a previous student on this machine. This will cause no problems - you can use their data.

======

Note - when using SQL, it is often better to type the queries into Notepad first, then copy and paste them into the SQL window. In case you make a typo, this prevents you from having to type in everything again

======

To check that the correct operations have been carried out:

SHOW TABLES;

Do you get the tables you expect?

You can get a description of the ‘customers’ table in the ‘company’ database using:

DESCRIBE customers;

Now we will perform some SQL queries by typing in code (these can also be done graphically using DBdesigner, but as you will have to write SQL code in the exam it is a good idea to do it this way to learn the correct syntax).

1. Use the SELECT statement to retrieve a single column called prod_name from the Products table:

SELECT prod_name

FROM Products; {don’t forget the semicolon}

2. Use SELECT to retrieve 3 columns from the Products table:

SELECT prod_id, prod_name, prod_price

FROM Products;

3. Retrieve all the columns from the Products table:

SELECT *

FROM Products;

4. Use the SELECT statement to retrieve a single column called prod_name from the Products table, but this time SORT the data by product name in DESCENDING order:

SELECT prod_name

FROM Products

ORDER BY prod_name DESC;

5. Retrieve three columns and sorts the results by two of them, first by price and then by name:

SELECT prod_id, prod_name, prod_price

FROM Products

ORDER BY prod_price, prod_name;

6. Use the WHERE clause as a filter/search criterion to find product names of products with a particular price:

SELECT prod_name, prod_price

FROM Products

WHERE prod_price = 3.49;

7. Find all the products in a particular price range:

SELECT prod_name, prod_price

FROM Products

WHERE prod_price BETWEEN 5 AND 10;

8. Find products of less than a particular price from a particular vendor:

SELECT prod_id, prod_price, prod_name

FROM Products

WHERE vend_id = ‘DLL01’ AND prod_price <= 4;

9. Find names and prices of products from vendors in a particular list:

SELECT prod_name, prod_price

FROM Products

WHERE vend_id IN (’DLL01’, ‘BRS01’)

ORDER BY prod_name;

10. Use the ‘LIKE’ operator to find product names that begin with ‘fish’:

SELECT prod_id, prod_name

FROM Products

WHERE prod_name LIKE ‘fish%’;

{notice the % wildcard after ‘fish’}

QUESTION: write your own code that finds teddy bears that cost less than $9

Now we will study CALCULATED FIELDS, ALIASES, AGGREGATE FUNCTIONS and how to GROUP rows and filter GROUPS using the HAVING clause.

11. Here we will use an alias called ‘expanded_price’ to find a particular order, and calculate the cost of each part of the order by multiplying the item price by its quantity:

SELECT prod_id, quantity, item_price,

Quantity*item_price AS expanded_price

FROM OrderItems

WHERE order_num = 20008;

{Note here that expanded_price is an ‘alias’, an alternate name for a field or value. Aliases are assigned using the AS keyword}

12. Find the average price of all the products in the Products table:

SELECT AVG(prod_price) AS avg_price

FROM Products;

13. Count the number of customers in the Customers table:

SELECT COUNT(*) AS num_cust

FROM Customers;

{remember, COUNT(*) will also count NULL entries}

14. Here, we just count those customers with an e-mail address, customers where there is a NULL entry in the ‘cust_email’ column will not be counted:

SELECT COUNT(cust_email) as num_cust

FROM Customers;

15. Find the total order value for a particular order:

SELECT SUM(item_price*quantity) AS total_value

FROM OrderItems

WHERE order_num = 20005;

17. Display the number of items, minimum, maximum and average price of all of our products:

SELECT COUNT(*) as num_items,

MIN(prod_price) as price_min,

MAX(prod_price) as price_max,

AVG(prod_price) as average_price

FROM Products;

18. Here we use GROUP BY to sort the data and group it by ‘vend_id’ – causing ‘num_prods’ to be calculated once per ‘vend_id’ rather than once for the entire table:

SELECT vend_id, COUNT(*) as num_prods

FROM Products

GROUP BY vend_id;

{Note that the GROUP BY clause must come after any WHERE clause and before any ORDER clause}

19. The HAVING clause is very similar to the WHERE clause we have examined earlier. The only difference is that the WHERE clause filters rows and the HAVING clause filters groups. Here we use it to filter the vendor groups, leaving only the vendors who have products priced at 4 or more (filtered by the WHERE clause) and have two or more of these products (filtered using the HAVING clause applied to the previous groups):

SELECT vend_id, COUNT(*) AS num_prods

FROM Products

WHERE prod_price >= 4

GROUP BY vend_id

HAVING COUNT(*) >= 2;

20. Here we retrieve the order number and number of items ordered for all orders containing 3 or more items, and sort the output by the order number and the number of items ordered:

SELECT order_num, COUNT(*) AS items

FROM OrderItems

GROUP BY order_num

HAVING COUNT(*) >= 3

ORDER BY items, order_num;

Question: Can you come up with your own query with a similar structure to 10. above? Do you get what you expect as output?

Log out of MySQL

quit

If you want to save your ‘company’ database on your home space, you will find a folder called 'company' in C:\Apps\MySQL\data' - you can copy and paste this. Even if you forget to do this, in the next workshop you can recreate the 'company' database by following the steps at the beginning of this workshop.

1