Project 4 - 1 Create Reports Using SELECT...WHERE to Refine Data

Project 4 “1 Create Reports Using SELECT WHERE to Refine Data

In Project 4-1, you will use the SELECT WHERE command with the LIMIT , DISTINCT , ORDER BY , and LIKE clauses to select and refine data from the duck_cust table as discussed in this module.

Step by Step

Open a command-line window and log in to MySQL, using the \mysql\bin\mysql “u root “p command (Windows) or the mysql “p command (Linux), and then enter your password when prompted.
Type USE duckwear; and press the ENTER key.
Use the following code as an example, select the customer number, first name, and last name , while scrolling through the entire duck_cust table four records at a time:
```
 SELECT cust_num, cust_first, cust_last FROM duck_custLIMIT 0, 4; 
```
The < offset > variable of the LIMIT option is the only one that changes, and each subsequent command displays the next four records in the table until the last command, which displays the only two remaining records.
To test the function of the DISTINCT option, enter the following commands:
```
 SELECT cust_duckname FROM duck_cust; SELECT DISTINCT cust_duckname FROM duck_cust; 
```
You ll see that the DISTINCT option filters out all repetition in the data fields.
Display records where each customer s number, last name, state, and duck name are selected and use the AS option to change the header names , as follows :

Field

Header

cust_num

Cust. #

cust_last

Last Name

cust_state

State

cust_duckname

Duck s Name
```
 SELECT cust_num AS "Cust.#", cust_last AS "Last Name",  cust_state AS State, cust_duckname AS "Ducks Name" FROM duck_cust; 
```
The output has the correct headers, but it still lists every customer in the database as if you had used the wildcard.
Since you know that customer 8 is a repeat of the same duck at a different address (customer 6), you can remove it and get the desired output, using the following code:
```
 SELECT cust_num AS "Cust.#",  cust_last AS "Last Name",    cust_state AS State, cust_duckname AS "Ducks Name" FROM duck_cust    WHERE cust_num!=8; 
```
Use a WHERE clause with numeric operators to list the names of customer numbers 10 and above. Also list customer numbers 3 and under plus those above, but not including, customer number 7. Additionally, use the AS option to change all four headers: cust_num to Number, cust_last to Last Name, cust_city to City, and cust_state to State.
```
 SELECT cust_last, cust_first FROM duck_cust WHERE cust_num>=10;SELECT cust_num AS Number, cust_last AS "Last Name",  cust_city AS City, cust_state AS State FROM duck_cust  WHERE cust_num<=3 OR cust_num>7; 
```

Field	Header
cust_num	Cust. #
cust_last	Last Name
cust_state	State
cust_duckname	Duck s Name

Try changing the AND to an OR in the previous command. Then change the < = to >= and > to < : .

 SELECT cust_num AS number, cust_last AS "last name",  cust_city AS city, cust_state AS state FROM duck_cust  WHERE cust_num<=3 AND cust_num>7;

 SELECT cust_num AS number, cust_last AS "last name",  cust_city AS city, cust_state AS state from duck_cust  WHERE cust_num>=3 AND cust_num<7;

This first command completely changes the values of the output to an empty set. Changing the arithmetic operators gives you the previously missing data except for customer 7, plus customer number 3.

Use the following code with equal to and not equal to operators to sort customers by region:

 SELECT cust_num, cust_city, cust_state FROM duck_cust WHERE cust_state="ok" OR cust_state="ks"; SELECT cust_num, cust_city, cust_state FROM duck_cust WHERE cust_state="ca" OR cust_state="wa" OR cust_state="az"; SELECT cust_num, cust_city, cust_state FROM duck_cust WHERE cust_state!="ca" AND cust_state!="wa" AND cust_state!="az" AND cust_state!="ok" AND cust_state!="ks";

Note	Despite being in all uppercase in the duck_cust table, the two-letter state designations do not need to be in uppercase for the string comparison to match, because the comparison was not designated with the BINARY option.

Check for missing duck birthday data with the following code:
```
 SELECT * FROM duck_cust WHERE cust_duckbday IS NULL; 
```
You can see the customers who need to be sent letters asking for their duck s birth date.

List the customers in alphabetical order and change the headers in the display:

 SELECT cust_last AS Last, cust_first AS First, cust_add1 AS Address,  cust_add2 AS "Address 2", cust_state AS State, cust_zip1 AS Zip FROM duck_cust  WHERE cust_num!=8 ORDER BY Last;

The WHERE cust_num!=8 removes the repeat customer from the result set, and the ORDER BY makes the list appear in ascending alphabetical order.

Note	The ORDER BY default is ascending, but you can force the issue by specifying ASC for ascending or DESC for descending after the field or fields specified as the ORDER BY value or values.

Remove the equal-to selection and order the output by ZIP code in descending order, using the following code:

 SELECT cust_last AS last, cust_first AS first, cust_add1 AS address,    cust_add2 AS "address 2", cust_state AS state, cust_zip1 AS zip    FROM duck_cust ORDER BY Zip DESC;

Make a list of customer names, cities, states, and ZIP codes with the output in ascending order by state and then city, using the following code:

 SELECT cust_last AS last, cust_first AS first, cust_city AS city,    cust_state AS state, cust_num AS number   FROM duck_cust ORDER BY state, city ASC;

Repeat the command in step 13, but leave off the ASC .

You can see that the results are identical, because the ORDER BY default is ascending.
Use the following code to make a list of the last names beginning with the following partial characters :
```
 SELECT cust_last AS LAST, cust_first AS FIRST, cust_duckname AS DUCK,cust_num AS NMBR FROM duck_cust WHERE cust_last LIKE "Dav%"  OR cust_last LIKE "S%"  OR cust_last LIKE "Be%"; 
```
The results are arranged in the order in which the records appear in the duck_cust table, which becomes clear when you look at the NMBR column.
Repeat the command in step 13, but add ORDER BY FIRST ASC to the end.

This listing is arranged in ascending order by first name fields.

Project Summary

In this project, you used a variety of ways to manipulate and display the data in your tables, using the SELECT command in combination with the WHERE clause and a few simple options.

The LIMIT , DISTINCT , and AS options allow you to control how many records are displayed, whether specific fields are repeated in the display, and how the columns in the display table headers are labeled, respectively. The array of arithmetic operators and conditional reserved words ( BETWEEN , LIKE , AND , and OR ) enable you to use the WHERE clause to manipulate the display of table data to suit whatever need you require.

Now that you have had experience with these basic methods of reporting, Module 5 will cover increasingly complex ways to manipulate and present your data.