Mysql Functions with examples CBSE INDIA 11th class NCERT

Mysql Functions with examples CBSE INDIA 11th class NCERT

• 1.
  CREATE TABLE Employee( id int, first_name VARCHAR(15), last_name VARCHAR(15), date_join DATE, salary DECIMAL (8,2) city VARCHAR(10) ); The rows in Employee Table are as follows: A) Numeric functions: MYSQL numeric functions perform operations on numeric vlaues and return numeric values. The following table tells us about the numeric functions of MYSQL and what they do. Sn o Name & Syntax Description Example 1. POWER (X,Y) Or POW(X,Y) Returns the value of X raised to the power of Y. a) mysql > SELECT POW(2,4); Result: 16 b) mysql > SELECT POW(2, -2); Result : 0.25 c) mysql > SELECT POW(-2, 3); Result : -8 d) mysql > SELECT id, salary, POWER(salary , 2) FROM employee;
• 2.
  Result: 2. ROUND(X,D) ROUND(X) a) Roundsthe argument X to D decimal places. b) if number of decimal places is not specified or is zero the number rounds to the nearest integer OR (0 decimal places). c) if negative value is specified for precision, it counts off that value left from the decimal point. d) If positive value is specified for precision, it counts off that value right from the decimal point. a) mysql > SELECT ROUND(-1, 23); Result : -1 b) mysql > SELECT ROUND (-1, 58); Result : -2 c) mysql >SELECT ROUND (1.43); Result : 1 d) mysql > SELECT ROUND (6.298, 1); Result : 6.3 e) mysql > SELECT ROUND (6.235, 0); Result : 6 f) mysql > SELECT ROUND (56.235, -1); Result : 60 g) mysql > SELECT id, ROUND (salary , 0) FROM employee: result: 3. TRUNCATE (X,D) Returns the number X, truncated to D decimal places. If D is 0, the result has no decimal point or fractional part. If D is negative, It causes D digit left of the decimal point of the value X to become zero. NOTE: TRUNCATE does not round a number. It simply chops off digits from a number. a) mysql > SELECT TRUNCATE (7.543, 1); Result : 7.5 b) mysql > SELECT TRUNCATE (4.567, 0); Result : 4 c) mysql > SELECT TRUNCATE (-7.45, 1); Result : -7.4 d) mysql > SELECT TRUNCATE (346,-2); Result : 399 e) mysql > SELECT id,TRUNCATE (salary,0)from employee; Result:
• 3.
  b) String (Character)Functions String functions operate on character type data. String functions are used to extract, change, format or alter Character strings. They return either character or numeric values. The following table tells us about the Character functions of MYSQL and what they do. Sno Name & Syntax Description Example 1. LENGTH(str) Returns the length of a column or a strin g in bytes. a) mysql > SELECT LENGTH (‘Informatics’); Result : 11 b) mysql > SELECT LENGTH (First_name) FROM Employee; Result: 2. CONCAT(str1, str2,…..) Returns the string that result s from concatenating the arguments. May have one or more arguments. a) mysql > SELECT CONCAT(‘My’ , ‘S’, ‘QL’); Result :’MySQL’ b) mysql > SELECT CONCAT(‘Class’ , ‘NULL’, ‘XI’ ’); Result :NULL c) mysql > SELECT CONCAT(First_Name,’’, Last_Name) FROM Employee; Result: 3. INSTR(str,substr) Returns the a) mysql > SELECT INSTR(‘Informatics’ ,
• 4.
  position of the first occurenceof substring substr in string str. ’for’); Result :3 b) mysql > SELECT INSTR(‘Computer’ , ’pet’); Result :0 c) mysql > SELECT INSTR(First_Name , ’Kiran’) FROM Employee; Result: 4. LOWER(str) Or LCASE(str) Returns the argument (str) in lowercase i.e. it changes all the characters of the passed string to lowercase. a) mysql > SELECT LOWER (‘INFORMATICS’ ); Result :informatics b) mysql > SELECT LOWER (Last_Name) FROM Employee; Result: 5. UPPER(str) Or UCASE(str) Returns the argument in uppercase.i.e. it changes all the characters of the passed string to uppercase. a) mysql > SELECT UPPER (‘Informatics’); Result :’INFORMATICS’ b) mysql > SELECT UPPER (Last_Name)From Employee; Result: 6. LEFT(str,n) Returns the specified number of characters (n) a) mysql > SELECT LEFT (‘Informatics’ , 3); Result :’Inf’ b) mysql > SELECT LEFT (first_name, 3) FROM Employee;
• 5.
  from the left sideof string str Result: 7. RIGHT(str,n) Returns the specified number of characters(n) from the right side of string str. a) mysql > SELECT RIGHT (‘Informatics’ , 4); Result : ‘tics’ b) mysql > SELECT RIGHT (first_name, 3)FROM Employee; Result: 8. RTRIM(str) Removes trailing spaces i.e. removes spaces from the right side of the string str. a) mysql > SELECT RTRIM (‘Informatics ‘); Result : ‘Informatics’ b) mysql > SELECT RTRIM (First_Name)FROM Employee; Result: 9. LTRIM(str) Removes leading spaces i.e. removes spaces from the left side of the string str. a) mysql > SELECT LTRIM (‘Informatics ‘); Result : ‘Informatics’ b) mysql > SELECT LTRIM (First_Name)FROM Employee; Result:
• 6.
  10. TRIM(str) Removesboh leading and trailing spaces from the string str. a) mysql > SELECT TRIM (‘ Informatics ‘); Result : ‘Informatics’ b) mysql > SELECT TRIM (First_Name)FROM Employee; Result: 11. SUBSTRING(str,m, n) Or MID(str,m,n) Returns the specified number of characters from the middle of the string. There are 3 arguments. The first argument is the source string. The second argument is the position of first character to be displayed. The third argument is the number of characters to be displayed. If the third argument is missing, then starting from the position specified, the rest of the string is returned. It is also a) mysql > SELECT SUBSTRING(‘ Informatics’ , 3); Result : ‘formatics’ b) mysql > SELECT SUBSTRING(‘Informatics’ FROM 4); c) mysql > SELECT SUBSTRING (‘Informatics’ ,3,4); Result: ‘form’ d) mysql > SELECT SUBSTRING (‘Computers’, -3); Result: ‘ers’ e) mysql > SELECT SUBSTRING (‘Computers’,-5 , 3); Result: ‘ute’ f) mysql > SELECT SUBSTRING (‘Computers’ FROM -4 FOR 2); Result: ‘te’ g) mysql > SELECT MID (‘Informatics’, 3 , 4); Result: ‘form’ h) mysql > SELECT MID(first_name, 3, 2)FROM Employee; Result:
• 7.
  possible to use anegative value for the second argument ie. Position (pos).In such a case, the beginning of the substring is pos characters from the end of the string. Note: SUBSTR is the same as SUBSTRING 12. ASCII(str) Return the ASCII value of the leftmost character of the string str. Returns 0 if str is an empty string. Returns NULL if str is NULL. a) mysql > SELECT ASCII(‘ 2’) Result : 50 (ASCII value of character ‘2’) b) mysql > SELECT ASCII(‘dx’); Result: 100 c) mysql > SELECT ASCII(‘A’); Result:65 (ASCII value of ‘A’) C) Date and Time Functions Date nd Time functions allow us to perform many types of tasks on data type data. The default date format in MySQL is YYYY-MM-DD. Sno Name & Syntax Description Example 1. CURDATE() Returns the current date in YYYY-MM-DD format or YYYYMMDD format, depending on whether the function is used in a string or numeric context. a) mysql > SELECT CURDATE(); Result : ‘2012-02-26’ 2. NOW() Rerurns the current date and time in ‘YYYY- a) mysql > SELECT NOW(); Result : ‘2012-02-26 21:30:26’
• 8.
  MM-DD HH:MM:SS’ or YYYYMMDDHHM MSS.uuuuuu format, depending on whetherthe sunction is used in a string or numeric context. 3. SYSDATE() Returns the current date and time in ‘YYYY- MM-DD HH:MM:SS’ or YYYYMMDDHHM MSS.uuuuuu format, depending on whether the function is used in a string or numeric context. Note: SYSDATE() returns the time at which the function executes. SYSDATE() differs from NOW() which returns a constant time that indicates the time at which the statement began to execute. *For difference between SYSDATE() and NOW() refers to NOTE at the end of this table a) mysql > SELECT SYSDATE(); Result : ‘2010-02-26 21:30:26’ b) mysql > SELECT SYSDATE() + 0; Result : 20100226213026.000000 4. DATE(epr) Extracts the date part of a date or a) mysql > SELECT DATE(‘2010-02-26 01:02:03’); Result : ‘2010-02-26’
• 9.
  datetime expression. b) mysql >SELECT DATE(‘2009-10-6 01:02:03’) Result : ‘2009-10-16’ 5. MONTH(date) Returns the numeric month from the date passed, in the range 0 to 12. It returns 0 for date such as ‘0000-00-00’ or ‘2010-00-00’ that have a zero month part. a) mysql > SELECT MONTH(‘2010-02-26’); Result : 2 b) mysql > SELECT id, date_join, month(date_join) from employee; Result: 6. YEAR(date) Returns the year for date passed in the range 0 to 9999. Returns values like 1998, 2010, 1996 and so on. a) mysql > SELECT YEAR(‘2010-02-26’); Result : 2010 b) mysql > SELECT id, date_join, year(date_join) from employee; Result: 7. DAYNAME(date ) If you want to know which day you were born on. Was it is Monday or a Friday? Use DAYNAME function. It returns the name of the weekday for the date passed. a) mysql > SELECT YEAR(‘2009-07-21’); Result : ‘Tuesday’ b) mysql > SELECT id, date_join,daynamer(date_join) from employee; Result: 8. DAYOFMONTH( date) Returns the day of the month in the range 0 to 31 a) mysql > SELECT DAYOFMONTH(‘2009-07-21’); Result : 21 b) mysql > SELECT id, date_join,dayofmonth(date_join) from employee; Result:
• 10.
  9. DAYOFWEEK(da te) Returns theday of week in number as 1 for Sunday, 2 for Monday and so on. a) mysql > SELECT DAYOFWEEK(‘2009-07-21’); Result : 3 b) mysql > SELECT id, date_join,dayofweek(date_join) from employee; Result: 10. DAYOFYEAR(da te) Return the day of the year for the given date in numeric format in the range 1 to 366. a) mysql > SELECT DAYOFYEAR(‘2009-07-21’); Result : 202 b) mysql > SELECT DAYOFYEAR(‘2009-01-01’); Result : 1 c) mysql > SELECT id, date_join,dayofyear(date_join) from employee; Result: