MySQL Maths Functions

 Revision of database concepts and SQL commands covered in class XI 

Scalar Functions - Numeric functions: POWER (), ROUND (), MOD (). 

String /  Text functions: UCASE ()/ UPPER (), LCASE ()/ LOWER (), MID ()/ SUBSTRING () /SUBSTR (), LENGTH (), LEFT (), RIGHT (), INSTR (), LTRIM (), RTRIM (), TRIM (). 

Date Functions: NOW (), DATE (), MONTH (), MONTHNAME (), YEAR (), DAY (), DAYNAME ()

Aggregate Functions - MAX (), MIN (), AVG (), SUM (), COUNT (); using COUNT (*). 

Querying and manipulating data using Group by, Having, Order by. 

Working with two tables using equi-join Querying & SQL ( Continue ) 

Syntax – pre-defined set of rules which tell how to write a command / statement for error free execution. (Every language has its own library where all the keywords – statements / functions / methods / attributes / modules are pre-defined with their structure(syntax). 

So during the translation phase (execution) the translator (assembler/compiler/interpreter) refers these software libraries and check for structure(syntax). If any mismatch in the structure then it shows Syntax Error on the screen) 

What are Functions ?? 

Keywords which returns a value on the basis of input(Parameters / Arguments) given to it. 

What is the identification of a function?? 

A keyword followed by a pair of parenthesis. Create - No /   Insert - No / Round( ) – Yes 

Built-In Functions (Pre-defined in the software library) and User-defined Functions SQL Built-In Functions

.     

        

# Reference table (Relation) is INVENTORY for all the below queries. 

CREATE TABLE INVENTORY(CARID VARCHAR(5), CARTYPE VARCHAR(10), PRICE DECIMAL(9,2), MODEL VARCHAR(10), YEAR INTEGER(5), FUELTYPE VARCHAR(10)); 

INSERT INTO INVENTORY VALUES("D001","Car1",582613.60,"LXI",2017,"Petrol"); 

Write the statement / query to – 

i) To show GST(a new column) amount @12% for each car – 

SELECT Price*12/100 AS GST FROM INVENTORY; 

ii) to show GST amount @12% rounded off up to two decimal places 

SELECT ROUND(Price*12/100, 2) FROM INVENTORY; 

iii) To show the final payable amount as price + GST amount @12% for each car – 

SELECT Price + Price*12/100 AS “Final_Amount” FROM INVENTORY; 

SELECT ROUND(Price + Price*12/100,2) AS “Final_Amount” FROM INVENTORY; 

2. POW(N, M) or POWER(N, M) – returns the numeric value of base N after being raised to the power M. 

N= base 

M= power 

2^2 = 4 

3^2 = 3^1 x 3^1 = 3^ (1+1) = 9 

3. MOD(N, M) - Returns the remainder of one expression by diving by another expression. 

10/2 = 5 (QUOTIENT) REMAINDER = ???? 

10%2 = 0 

11/2=5 

11%2=1 

 Num1 / Num2 = ? 

 10/2 = ? 

SELECT MOD(NUM1,NUM2); = NUM1 % NUM2 (Modulus Operator) = REMAINDER 

MOD( dividend, divider ) 

Reference Table – Inventory 

** Price = On Road Price of Car 

FinalPrice = Price + 12% GST of Price 

EMI = 100000.00 / month 

Total_EMI = EMI*5 (for 5 months) 

Due_Amount = FinalPrice – Total_EMI 

Due_Amount % 2 = 

Write the statement / query to – 

i) To show the car name, FinalPrice, and EMI as 10000.00 for each month 

 SELECT CARNAME, FINALPRICE, 10000.00 AS EMI FROM INVENTORY; 

ii) To show the car name, FinalPrice, and TOTAL_EMI @10000.00 for 5 months 

 SELECT CARNAME, FINALPRICE, 10000.00*5 AS TOTAL_EMI FROM INVENTORY; 

iii) To show the car name, FinalPrice, TOTAL_EMI and the remaining amount as Due_Pay 

 SELECT CARNAME, FINALPRICE, 10000.00*5 AS TOTAL_EMI, FINALPRICE-10000.00*5 AS DUE_PAY FROM INVENTORY; 

 HW 

 iv) car name, Due_pay rounded off up to 2 decimal places 

 mysql> SELECT CarName, ROUND(FinalPrice – 10000.00*5,2) AS Due_pay FROM INVENTORY; 

 v) Remaining amount after dividing the Due_Amount in 2 parts. 

 mysql> SELECT CarName, MOD(FinalPrice-10000.00*5,2) AS Remaining_Amount FROM INVENTORY; 

10%2 = 0 ((FinalPrice-10000.00*5)%2) = MOD(FinalPrice-10000.00*5,2) 100333.00 

 **********