Project 5 - 1 Use MySQL Functions to Create Reports

Project 5 “1 Use MySQL Functions to Create Reports

This project gives you an overview of using MySQL functions and shows how some of them might be used in concert to produce more sophisticated output in your reports. To make use of some of the mathematical functions, you will begin by building a table that has more numeric content.

Step by Step

Create a table called duck_sales, using the following command

 USE duckwear; CREATE TABLE duck_sales( design_num MEDIUMINT NOT NULL AUTO_INCREMENT, design_name CHAR(20), winter_sales INT, spring_sales INT, summer_sales INT, fall_sales INT, design_category CHAR(13), primary key(design_num) )type=MyISAM;

Insert the data shown in Table 5-4 into the duck_sales table, using the following INSERT command as a template.

Table 5-4: Data for the duck_sales Table
Name	Winter	Spring	Summer	Fall	Category
Santa_Duck	1067	200	150	267	Holiday
Dr_Duck	970	770	531	486	Profession
Duckula	53	13	21	856	Literary
Fire_Duck	782	357	168	250	Profession
Bunny_Duck	589	795	367	284	Holiday
Duckspeare	953	582	336	489	Literary
Sherlock_Duck	752	657	259	478	Literary
Duck_O_Lantern	67	23	83	543	Holiday
Rodeo_Duck	673	48	625	52	Profession

 INSERT INTO duck_salesVALUES(NULL, 'Santa_Duck', 1067, 200, 150, 0, 'Holiday');

Use the following code to make a report that lists only the categories Profession and Holiday.

 SELECT design_name AS Name, design_category AS Category FROM duck_sales WHERE design_category IN("Profession", "Holiday");

Use an IF function to evaluate which designs sell over 500 units during the winter sales quarter, using the following command to make a report that has name, category, and sales trend.
```
 SELECT design_name AS Name, design_category AS Category, IF(winter_sales>500, "Sells", "Slow") AS Trend FROM duck_sales; 
```

Use a CASE WHEN function to indicate that the different categories have different sales trends, using the following command.

 SELECT design_name AS Name, CASE design_category WHEN "Holiday" THEN "Seasonal" WHEN "Profession" THEN "Bi_annual" WHEN "Literary" THEN "Random" END AS "Pattern" FROM duck_sales;

Using the SUM function, make a report that shows the total sales numbers in each quarter.

 SELECT SUM(winter_sales) AS "winter total", SUM(spring_sales) AS "spring total", SUM(summer_sales) AS "summer total", SUM(fall_sales) AS "fall total" FROM duck_sales;

Use the same format, but find the average sales amount per sales quarter.

 SELECT AVG(winter_sales) AS "winter avg ", AVG (spring_sales) AS "spring avg ", AVG (summer_sales) AS "summer avg ", AVG (fall_sales) AS "fall avg " FROM duck_sales;

In order to alter the display of design_name without altering how it is stored in the table, use the REPLACE command to remove the underscores and insert spaces in their place.
```
 SELECT REPLACE(design_name, "_", " ") FROM duck_sales; 
```
Notice that the change was in the output, not in the record fields themselves .
Use the following command to produce a report called Sales Plan that replaces the underscores with spaces and concatenates the word Sale onto the names of the DuckWear outfits in the Literary and Holiday categories.
```
 SELECT CONCAT((REPLACE(design_name, "_", " ")), " ", "Sale") AS "Sales Plan"   FROM duck_sales WHERE design_category!="Profession"; 
```

Enter the following command, which uses the basic mathematical operators to display a yearly sales total for each of the DuckWear outfits in the duck_sales table.

 SELECT REPLACE(design_name, "_", " ") AS Outfit, winter_sales+spring_sales+summer_sales+fall_sales AS "Yearly Sales", design_category AS Category FROM duck_sales;

Note	The second example in the illustration shows the same command entered without the line breaks. Notice that the result is the same. The line-break version of the code simply makes it easier to read, which makes it easier to comprehend and easier to debug if the command returns errors.

Enter the following DATE_FORMAT commands to experiment with the variety of results you can achieve.

 SELECT DATE_FORMAT("2078-08-30 21:19:58", "%W %M %d %Y"); SELECT DATE_FORMAT("2078-08-30 21:19:58", "%a. %b %e, '%y"); SELECT DATE_FORMAT("2078-08-30 21:19:58", "%m-%e-%Y %l:%i%p"); SELECT DATE_FORMAT("2078-08-30 21:19:58", "%m-%e-%Y %h:%i%s%p %W"); SELECT DATE_FORMAT("2078-08-30 21:19:58", "%M %D,%Y %k:%i CST %W");

Project Summary

In this project, you applied some MySQL functions to a table of data. These examples gave you an idea of how the functions can be combined and layered to produce ever-increasingly complex and specific reports. Most of the time, the required syntax for the functions is straightforward, and when it is not, a small amount of experimentation allows you to refine it until the results match your requirements.

This project covered the use of the IN, IF, CASE WHEN, SUM, AVG, REPLACE, CONCAT, and DATE_FORMAT functions. You used commands with more than one function nested inside them. You saw how you can make your output look the way you want it to, without changing the format in which your data is stored. The next skill will extend your knowledge in this area ever further.