#Written by Jen Chen
#Copyrighted by Jen Chen
#CS 122
#Lecture on 10/18/04
#Topics: Mathematical functions.
#Tables in used: BooksJC, PublishersJC.

Table: BooksJC
	===================================================   
	Field Name		Data Type	
	===================================================   
	ISBN			varchar(15) primary key not null
	Title			varchar(50)
	PubID			varchar(25)
	Price			decimal(10,2)

Table: PublishersJC
	===================================================   
	Field Name		Data Type	
	===================================================   
	PubID			varchar(25) primary key not null
	PubName			varchar(50)
	PubPhone		varchar(15)
	
Contents of BooksJC:

  ISBN          Title   	PubID   Price
  0-103-45678-9	Iliad		1	25
  0-11-345678-9	Moby, Dick	3	49
  0-12-333433-3	On, Liberty	1	25
  0-123-45678-0	Ulysses		2	34
  0-12-345678-9	Jane, Eyre	3	49
  0-321-32132-1	Balloon		3	34
  0-55-123456-9	Main, Street	3	22.95
  0-555-55555-9	Macbeth		2	12
  0-91-045678-5	Hamlet		2	20
  0-91-335678-7	Faire, Queen	1	15
  0-99-777777-7	King, Lear	2	49
  0-99-999999-9	Emma		1	20
  1-1111-1111-1	C++		1	29.95
  1-22-233700-0	Visual, Basic	1	25

	
Contents of PublishersJC:

	+-------+------------------+----------------+
	| PubID | PubName          | PubPhone       |
	+-------+------------------+----------------+
	| 1     | "Big, House"     | 123-456-7890   |
	| 
	  2     | "Alpha, Press"   | 999-999-9999   |
	| 
	  3     | "Small, House"   | 714-000-0000   |
	| 
	  4     | "Another, Press" | 111-222-3333   |
	+-------+------------------+----------------+
	4 rows in set (0.00 sec)
	

	(A) Built-in mathematical functions:
	Note: All mathematical functions return NULL in case of an error. 

	(1) ABS(X): Returns the absolute value of X. 

	 SELECT ABS(2);
        	 2

	 SELECT ABS(-32);
        	 32
	
	(2) ACOS(X): Returns the arc cosine of X, that is, the value whose 
	cosine is X. 
	Returns NULL if X is not in the range -1 to 1. 
	
	 SELECT ACOS(1);
        	 0.000000

	 SELECT ACOS(1.0001);
        	 NULL

	 SELECT ACOS(0);
        	 1.570796

	(3)ASIN(X): Returns the arc sine of X, that is, the value whose 
	sine is X. 
	Returns NULL if X is not in the range -1 to 1. 

	 SELECT ASIN(0.2);
        	 0.201358

	 SELECT ASIN('foo');
        	 0.000000

	(4) ATAN(X): Returns the arc tangent of X, that is, the value whose
	tangent is X. 

	 SELECT ATAN(2);
        	 1.107149

	 SELECT ATAN(-2);
        	 -1.107149

	(5) ATAN(Y,X) , ATAN2(Y,X): Returns the arc tangent of the two variables
	X and Y. It is similar to calculating the arc tangent of Y / X, except 
	that the signs of both arguments are used to determine the quadrant of 
	the result. 

	 SELECT ATAN(-2,2);
        	 -0.785398

	 SELECT ATAN2(PI(),0);
        	 1.570796

	(6) CEILING(X) , CEIL(X): Returns the smallest integer value not less 
	than X. 

	 SELECT CEILING(1.23);
        	 2

	 SELECT CEIL(-1.23);
        	 -1

	select ceiling(5.89);
		6

	Note that the return value is converted to a BIGINT! 
	The CEIL() alias was added in MySQL 4.0.6. 

	(7) COS(X): Returns the cosine of X, where X is given in radians. 

	 SELECT COS(PI());
        	 -1.000000


	(8) COT(X): Returns the cotangent of X. 

	 SELECT COT(12);
        	 -1.57267341

	 SELECT COT(0);
        	 NULL


	(9) CRC32(expr): Computes a cyclic redundancy check value and returns 
	a 32-bit unsigned value. The result is NULL if the argument is NULL. 
	The argument is expected be a string and is treated as one if it is not. 

	 SELECT CRC32('MySQL');
        	 3259397556


	(10) CRC32() is available as of MySQL 4.1.0. 


	(11) DEGREES(X): Returns the argument X, converted from radians to 
	degrees. 

	 SELECT DEGREES(PI());
        	 180.000000


	(12) EXP(X): Returns the value of e (the base of natural logarithms) 
	raised to the power of X. 

	 SELECT EXP(2);
        	 7.389056

	 SELECT EXP(-2);
        	 0.135335


	(13) FLOOR(X): Returns the largest integer value not greater than X. 

	 SELECT FLOOR(1.23);
        	 1

	 SELECT FLOOR(-1.23);
        	 -2

	 select floor(5.67);
	           5 

	Note that the return value is converted to a BIGINT! 


	(14) LN(X): Returns the natural logarithm of X. 

	 SELECT LN(2);
        	 0.693147

	 SELECT LN(-2);
        	 NULL

	This function was added in MySQL 4.0.3. It is synonymous with LOG(X) in MySQL. 


	(15) LOG(X) , LOG(B,X) 

	If called with one parameter, this function returns the natural logarithm of X.

	 SELECT LOG(2);
        	 0.693147

	 SELECT LOG(-2);
        	 NULL

	If called with two parameters, this function returns the logarithm of X 
	for an arbitrary base B. 

	 SELECT LOG(2,65536);
        	 16.000000

	 SELECT LOG(1,100);
        	 NULL

	The arbitrary base option was added in MySQL 4.0.3. LOG(B,X) is equivalent 
	to LOG(X)/LOG(B). 


	(16) LOG2(X): Returns the base-2 logarithm of X. 

	 SELECT LOG2(65536);
        	 16.000000

	 SELECT LOG2(-100);
        	 NULL


	(17) LOG2() is useful for finding out how many bits a number would require for 
	storage. This function was added in MySQL 4.0.3. In earlier versions, you 
	can use LOG(X)/LOG(2) instead. 


	(18) LOG10(X): Returns the base-10 logarithm of X. 

	 SELECT LOG10(2);
        	 0.301030

	 SELECT LOG10(100);
        	 2.000000

	 SELECT LOG10(-100);
        	 NULL


	(19) MOD(N,M) , N % M , N MOD M: Modulo operation. Returns the remainder of N 
	divided by M. 

	 SELECT MOD(234, 10);
        	 4

	 SELECT 253 % 7;
        	 1

	 SELECT MOD(29,9);
        	 2

	 SELECT 29 MOD 9;
        	 2

	This function is safe to use with BIGINT values. The N MOD M syntax works only
	as of MySQL 4.1. 
	As of MySQL 4.1.7, MOD() works on values with a fractional part and returns 
	the exact remainder after division: 

	 SELECT MOD(34.5,3);
        	 1.5

	Before MySQL 4.1.7, MOD() rounds arguments with a fractional value to integers 
	and returns an integer result: 

	  SELECT MOD(34.5,3);
        	 2


	(20) PI(): Returns the value of PI. The default number of decimals displayed 
	is five, but MySQL internally uses the full double-precision value for PI. 

	 SELECT PI();
        	 3.141593

	 SELECT PI()+0.000000000000000000;
        	 3.141592653589793116

	  SELECT FORMAT(PI(), 2)
		3.14


	(21) POW(X,Y) , POWER(X,Y): Returns the value of X raised to the power of Y. 

	 SELECT POW(2,2);
        	 4.000000

	 SELECT POW(2,-2);
        	 0.250000


	(22) RADIANS(X): Returns the argument X, converted from degrees to radians. 

	 SELECT RADIANS(90);
        	 1.570796


	(23) RAND() , RAND(N): Returns a random floating-point value in the range 
	from 0 to 1.0. If an integer argument N is specified, it is used as the 
	seed value (producing a repeatable sequence). 

	 SELECT RAND();
        	 0.9233482386203

	 SELECT RAND(20);
        	 0.15888261251047

	 SELECT RAND(20);
        	 0.15888261251047

	 SELECT RAND();
        	 0.63553050033332

	 SELECT RAND();
        	 0.70100469486881

	select format(RAND()*100,0);
		will give us a random integer from 0 to 99.

	CAUTION: You can't use a column with RAND() values in an ORDER BY clause, 
	because ORDER BY would evaluate the column multiple times. As of MySQL 
	3.23, you can retrieve rows in random order like this: 

	 SELECT * FROM tbl_name ORDER BY RAND();


	NOTE: ORDER BY RAND() combined with LIMIT is useful for selecting a random sample
	of a set of rows: 

	 SELECT * FROM table1, table2 WHERE a=b AND c<d
	     ORDER BY RAND() LIMIT 1000;


	Note that RAND() in a WHERE clause is re-evaluated every time the WHERE 
	is executed. 

	RAND() is not meant to be a perfect random generator, but instead a fast 
	way to generate ad hoc random numbers that is portable between platforms
	for the same MySQL version. 


	(24) ROUND(X) , ROUND(X,D): Returns the argument X, rounded to the nearest
	integer. With two arguments, returns X rounded to D decimals. D can be 
	negative to round D digits left of the decimal point of the value X. 

	 SELECT ROUND(-1.23);
        	 -1

	 SELECT ROUND(-1.58);
	         -2

	 SELECT ROUND(1.58);
        	 2

	 SELECT ROUND(1.298, 1);
        	 1.3

	 SELECT ROUND(1.298, 0);
        	 1

	 SELECT ROUND(23.298, -1);
        	 20


	Before MySQL 5.0.3, the behavior of ROUND() when the argument is halfway
	between two integers depends on the C library implementation. Different 
	implementations round to the nearest even number, always up, always down,
	or always toward zero. If you need one kind of rounding, you should use
	a well-defined function such as TRUNCATE() or FLOOR() instead. 

	As of MySQL 5.0.3, ROUND() uses the precision math library. for exact-value
	arguments: 

	For exact-value numbers, ROUND() uses the "round half up" rule: A value with
	a fractional part of .5 or greater is rounded up to the next integer if 
	positive or down to the next integer if negative. (In other words, it is 
	rounded away from zero.) A value with a fractional part less than .5 is 
	rounded down to the next integer if positive or up to the next integer if 
	negative. 

	For approximate-value numbers, the result depends on the C library. On many
	systems, this means that ROUND() uses the "round to nearest even" rule: A
	value with any fractional part is rounded to the nearest even integer. 

	The following example shows how rounding differs for exact and approximate
	values: 

	 SELECT ROUND(2.5), ROUND(25E-1);
	+------------+--------------+
	| ROUND(2.5) | ROUND(25E-1) |
	+------------+--------------+
	| 3          |            2 |
	+------------+--------------+

	For more information, see Chapter 23, Precision Math. 

	(25) SIGN(X): Returns the sign of the argument as -1, 0, or 1, depending on
	whether X is negative, zero, or positive. 

	 SELECT SIGN(-32);
	         -1

	 SELECT SIGN(0);
        	 0

	 SELECT SIGN(234);
	         1

	(26) SIN(X): Returns the sine of X, where X is given in radians. 

	 SELECT SIN(PI());
        	 0.000000


	(27) SQRT(X): Returns the non-negative square root of X. 

	 SELECT SQRT(4);
        	 2.000000

	 SELECT SQRT(20);
        	 4.472136

	(28) TAN(X): Returns the tangent of X, where X is given in radians. 

	 SELECT TAN(PI()+1);
        	 1.557408


	(29) TRUNCATE(X,D): Returns the number X, truncated to D decimals. If 
	D is 0, the result has no decimal point or fractional part. D can be 
	negative to truncate (make zero) D digits left of the decimal point of
	the value X. 

	 SELECT TRUNCATE(1.223,1);
        	 1.2

	 SELECT TRUNCATE(1.999,1);
        	 1.9

	 SELECT TRUNCATE(1.999,0);
        	 1

	 SELECT TRUNCATE(-1.999,1);
        	 -1.9

	 SELECT TRUNCATE(122,-2);
	        100

	Starting from MySQL 3.23.51, all numbers are rounded toward zero. 
	Note that decimal numbers are normally not stored as exact numbers in
	computers, but as double-precision values, so you may be surprised by
	the following result: 


	 SELECT TRUNCATE(10.28*100,0);
	       	 1027

	This happens because 10.28 is actually stored as something like 
	10.2799999999999999.