Module 5: Advanced Reporting


1.  

What does the following command return?

 SELECT ep_title AS Album, Band, IF(Release>1964 and Release<=1969, "60's"," ") AS Beatles Era, IF(Release>1979 and Release<1990, "New Wave"," ") AS "Hair Band Era", FROM RecordCollection ORDER BY Band; 

2.  

What is the purpose of the CASE WHEN function?

3.  

What are the four ways to control how a column of fields rounds off numeric content?

4.  

What does the code LPAD sale_price, 5, "$" return, when sale_price contains a string value of 9.95?

5.  

From a table called cust_orders that has the columns cust_num , design_num , design_name , quantity , and price , make a report that shows the total quantity of outfits bought by cust_num 13 .

6.  

What would the following code return?

 concat_ws("   ", "There can be", "Only ONE") 

7.  

When is it required that you use the table_name.field_name syntax in your JOIN clause?

8.  

Can you use only the ORDER BY clause in the SELECT syntax?

9.  

Which function returns the current date and time on the computer on which you are working?

10.  

What does the following command do?

 SELECT Range, AVG(height) FROM Mountains GROUP BY Range HAVING Range="Rockies"; 

Answers

1.  

It returns a report with the headers Album, Band, Beatles Era, and Hair Band Era, arranged by sorting the Band column and putting either 60's or a space in the Beatles Era column and New Wave or a space in the Hair Band Era.

2.  

It controls the flow of data to the resulting output report.

3.  

ROUND , CEILING , FLOOR , and TRUNCATE all allow you to control the way a numeric field is rounded.

4.  

It returns the string $$$$$9.95 .

5.  

The code would look like this:

 select sum(quantity) from cust_orders where cust_num=13; 

6.  

It would return the string There can be Only ONE .

7.  

You must use the table_name.field_name syntax when you are selecting a field that has exactly the same field name from each table.

8.  

No, the GROUP BY clause must always come before ORDER BY clause in the SELECT syntax.

9.  

The NOW function returns the current date and time.

10.  

It returns an average of the height of the peaks listed in the Rockies table.




MySQL(c) Essential Skills
MySQL: Essential Skills
ISBN: 0072255137
EAN: 2147483647
Year: 2006
Pages: 109

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net