UNIT 2 Database Query and SQL Math Functions + POW() or POWER() POWER( A, B) or POW( A, B) returns the number A raised to the power of another number B.Here the number A is the base and the number B is the exponent. Needs two numbers as parameters. SYNTAX: SELECT POW(A, B); Examples: 1) mysql> select power(2,3); + + | power(2,3) | + + een + + 1 row in set (0.05 sec) 2) ~mysql>select pow(2,3); + + | pow(2,3) | + + 1 8 1 + + 1 row in set (0.00 sec) mysqi>select pow(2.0 , 3.0); + | pow(2.0,3.0) | + + | 8 | to 1 row in set (0.00 sec) ROUND( ) This function is used to round the number to the specified number of decimal places. Parameters required: the number to be rounded and the number of decimal places required. If the number of decimal places required is not mentioned, then the result will not have decimal places SYNTAX: SELECT ROUND(NUMBER, NUMBER OF DECIMAL PLACES) Examples 1) mysql>select round(2.25); + + | round(2:25) | + + I 2 + 1 row in set (0.01 sec)mysq[>select round(2.25, 1); + + | round(2:35, 1) | + + | 23 | + + 1 row in set (0.00 sec) mysq[>select round(2.25, 2); + + | round(2.2, 2) | + + ) 25 | + + 1 row in set (0.00 sec) mysql>select round (2.26, 0); + + | round(2:26, 0) | + + | 2 1 + + 1 row in set (0.00 sec) mysqi>select round(135.43, + + | round(438.43; 0) | + + I 135 | + + mysqi>select round(135.53, Facccssmnseenseonsnmsens | round(438.53, 0) | + + | 136 | + + 1 row in set (0.00 sec) mysq[>select round(135.55, + + | round(135.55, 1) | + + | 135.6 | + + 1 row in set (0.00 sec) mysq[>select round(135.55, + + | round(135.55, -1) | + + | 40 |at 1 (€.00 see) 9) mysq>select round(134.45, -1); + + | round(134.45,-4) | Fccesemnsennsesnseennenes | 130° | + + 1 row in set (0.00 sec) 10)mysql>select round(134.45, -2); + + | round(134:48,2) | + + | 400 | + + 1 row in set (0.00 sec) 14)mysql>select round(154.45, -2); + + | round(¥54.45, 2) | + + | 200°” | + + 1'row in set (0.00 sec) 12)mysql>select round(1454.45, -2); + + | round(1454.45, -2) | + + 1800 Foo sosengeennen 1 row in set (0.60 sec) 13)mysql>select round(1444.45, -2); + + | round(i444:45, “2) | + + 4400 | | + z 1 row in set (0.00 sec) 14)mysq|>select round(1444.45, -3); + + | round(1444.45, -3) | + + | 1000 | + +1 row in set (0.00 sec) B15)mysql>select round(1544.45, -3); + + | round(1844:45,~) | This function can be used to find modulus (remainder) when one number is divided by another. Examples: mysql>select mod(5,3) + + | mod(5,3) | + + 12 1 + + 1 row in set (0.00 sec) 1) mysql> select mod(5,4); + + |mod(6,4) | + + I ot ot + + 1 row in set (0.00 sec) 2) mysql>select mod(4,2); + + | mod(4,2) | + + | 90 | + + 1'row in set (0.00 sec) Text/String/Character Functions: + UCASE()/UPPER() Used to convert a character or text to uppercase. Examples: 1) mysql>SELECT UCASE('hello’); | UCASE (hetio') | | HELLS i 1 row in set (0.00 sec)2) mysq>SELECT Upper(‘hello’); + + | Upper(heiio’ | + + jHetto™ | Faccsemnseenseennet 1 row in set (0.00 sec) + LCASE()/LOWER() : Used to convert a character or text to lowercase. Examples 1) mysql>select Icase(‘HELLO'); + + Jicase(HELLOY | + + | helio | + + 1 row in set (0,00 sec) mysq[>select LOWER(HELLO’); + + | LOWER(HELLOY | + + | hello | + + 1'row in set (0.00 sec) MID( ) : To extract a specified number of characters from the string First parameter is the text/string. Second parameter is the starting index and the thirdparameter is the number of characters required. (Note: index starts with 1 and not 0.) Examples: 1) mysql>SELECT MID(ABCDEFGHIJKLMNOP', 1,4); | MID(ABCDEFGHIJKiMNOP", 1,4) | | ABCD I 1 row in set (0.00 sec) 2) mysql>SELECT MID(‘ABCDEFGHIJKLMNOP’, 1); + + | MID(ABCDEFGHIJKLMNOP’, 1) | + + | ABCDEFGHIJKLMINOP | +1 row in set (0.00 sec)3) mysql> SELECT MID(ABCDEFGHIJKLMNOP*, -2,-1 + + | MID(ABCDEFGHIJKLMNOP*, -2,-1) | + + | I +1 row in set (0.00 sec) 4) mysql>SELECT MID(ABCDEFGHIJKLMNOP", 0,4); + + | MID(ABCDEFGHIJKLMNOP'’, 0,4) | + + | | 1 row in set (0.00 sec) (Please note the output of example 3, 4) 5) mysqleSELECT MID(ABCDEFGHVJKLMNOP’, 3.4); i MID(ABCDEFGHIJKLMNOP", 3 34) I i CDEF 1 +1 row in set (0.00 sec) mysql>SELECT MID((ABCDEFGHIJKLMNOP’, -4,2); + + | MID(ABCDEFGHIJKLMNOP*, -4,2) | + + | MN | + +1 row in set (0.00 sec) + SUBSTRING() ? Same as MID() function To extract a specified number of characters from thestring. Examples: 1) mysql>SELECT SUBSTRING(‘ABCDEFGHIJKLMNOP’, 3,4); + + \ SUBSTRING(‘ABCDEFGHIJKLMNOP’, i 4) | leeEe™ nnn “4 +1 row in set (0.00 sec) 2) mysql>SELECT SUBSTRING(‘ABCDEFGHIJKLMNOP', 0,4); + + | SUBSTRING(ABCDEFGHIJKLMNOP’, 0,4) | + + +1 row in set (0.00 sec)3) mysql>SELECT SUBSTRING(‘ABCDEFGHIJKLMNOP", 1,4); + + | SUBSTRING (ABCDEFGHIIKLNINOP® 4,4) | + + | ABCD | +1 row in set (0.00 sec) mysql>SELECT SUBSTRING('ABCDEFGHIJKLMNOP", 4,2); + + | SUBSTRING(‘ABCDEFGHIJKLMNOP’, 4,2) | + + |DE | +1 row in set (0.00 sec) mysql>SELECT SUBSTRING('ABCDEFGHIJKLMNOP', -4,2); + + | SUBSTRING(‘ABCDEFGHIJKLMNOP’, -4,2) | + + | MN | +1 row in set (0.00 sec) + SUBSTR() : Same as that of MID( ) and SUBSTRING( ) Examples: 1) mysql>SELECT SUBSTR(‘ABCDEFGHIJKLMNOP, -4,3); + + | SUBSTR(/ABCDEFGHISKLMNOP*, -4,3) | + + | MNO I +1 row in set (0.00 sec) mysql>SELECT SUBSTR(‘ABCDEFGHIJKLMNOP’, 1,3); + + | SUBSTR(‘ABCDEFGHIJKLMNOP’, 1,3) | + + | ABC | +1 row in set (0.00 sec) mysql>SELECT SUBSTR(‘ABCDEFGHIJKLMNOP’, 4,3); + + | SUBSTR(ABCDEFGHIUKLMNOP®, 4,3) | + + | DEF | _.#1 row in set (0.00 sec) * LENGTH() : This function returns the number of characters in the given text. Examples: 1) mysql> SELECT LENGTH('HELLO WORLD); + + | LENGTH(HELLO WORLD | + + | "1 |+1 row in set (0.00 sec) mysql> SELECT LENGTH( "); + + |LENGTH(' ')| + + | 3 I +1 row in set (0.00 sec) mysq> SELECT LENGTH(''); + + |LENGTHE) | + + | 1 I + + 1 row in set (0.00 sec) LEFT( ) :Returns the specified number of characters including space starting from the leftmost characters. Parameters required : text, number of characters to be extracted. Examples: mysqi>SELECT LEFT(‘ABCDEFGHIJKLMNOP",1); + + | LEFT(‘(ABCDEFGHIJKLMNOP",1) | + + IA | +1 row in set (0.00 sec) mysql> SELECT LEFT(‘ABCDEFGHIJKLMNOP" 2); + + | LEFT(ABCDEFGHIJKLMNOP* 2) | + + |AB | +1 row in set (0.00 sec) mysql> SELECT LEFT(‘(ABCDEFGHIJKLMNOP" 3); + + | LEFT(ABCDEFGHIJKLMNOP:,3) | + + | ABC | +1 row in set (0.00 sec) mysql> SELECT LEFT(‘ABCDEFGHIJKLMNOP",-1); + + | LEFT(ABCDEFGHIZKLMNOP® 1) | + + +i rowin set (0.00 sec) (Note : In the above example , the number of characters to be selected is -1 and hence characters are notextracted)RIGHT( ) :Returns the specified number of characters including space starting from the right of the text. Parameters required : text, number of characters to be extracted. Examples: mysql> SELECT RIGHT((ABCDEFGHIJKLMNOP*, 1); + + | RIGHT(ABCDEFGHISKLMNGP* 1) | + + \P I +1 row in set (0.00 sec) (Extracting 1 character) mysql> SELECT RIGHT((ABCDEFGHIJKLMNOP’,2); + + | RIGHT(‘ABCDEFGHIJKLMNOP',2) | + + |p \ +1 row in set (0.00 sec) (Extracting 2 characters) mysql> SELECT RIGHT(‘ABCDEFGHIJKLMNOP’,3); + + | RIGHT(‘ABCDEFGHIJKLMNOP*,3) | + + | NOP | +1 row in set (0.00 sec) mysql> SELECT RIGHT(‘ABCDEFGHIJKLMNOP',4); + + | RIGHT(ABCDEFGHIJKLMNOP,4) | + + | MNOP | +1 row in set (0.00 sec) mysql> SELECT RIGHT(‘ABCDEFGHIJKLMNOP",-1); + + | RIGHT(ABCDEFGHIJKLMNOP*,-1) | + + INSTR() : Checks whether the second string/text is present in the first string. If present itretums the starting index.Otherwise returns 0. Examples: mysql> SELECT INSTR(‘ABCDEFGHIJKLMNOP" ABC’); + + | INSTR(ABCDEFGHIJKLMNOP"’ABC’) | + + | 1 | +1 row in set (0.00 sec)mysql> SELECT INSTR(‘ABCDEFGHIJKLMNOP",'BC’); + + | INSTR(ABCDEFGHIJKLMNOP* BC’) | + + | 2 | +1 row in set (0.00 sec) mysqi> SELECT INSTR(‘ABCDEFGHUKLMNOP' 'EFG'); + + | INSTR(ABCDEFGHIJKLMNOP"'EFG’) | + + | 5 | +1 row in set (0.00 sec) mysql> SELECT INSTR(ABCDEFGHIJKLMNOP"'QRST)); + + | INSTR(ABCDEFGHIJKLMNOP''GRST) | + + | 0 I +1 row in set (0.00 sec) LTRIM() :To trim the spaces, if any, from the beginning of the text. Examples: mysql> SELECT LTRIM(’ HELLO’); + + (LTRIMG HELLO) | + + | HELLO I + RTRIM( ) : To trim the spaces, if any, from the end of the text. Examples: mysql> SELECT RTRIM(HELLO’); + + | RTRIMCHELLO 4) | + + | HELLO | +1 row in set (0.00 sec) mysql> SELECT CONCAT(RTRIM(HELLO’), 'WORLD’); + + | CONCAT(RTRIM(HELLO 7}; WORLD | + + | HELLOWORLD | + + 1'row in set (0.00 sec) TRIM(): To trim the spaces, if any, from the beginning and end of the text. Examples: 801) mysql> SELECT CONCAT(TRIM(HELLO’), WORLD’); + + | CONCAT(TRIMCHELLS , WORLDS | + + | HELLOWORLD | + _.#1 row in set (0.00 sec) Note: CONCAT( ) combines two strings/texts 2) mysq> SELECT TRIM) HELLO + + | TRIM( HELLO) + + | HELLO | + + 1 row in set (0.00 sec) Try yourself: Give the output of the following: 1. SELECT POWER(3,3); . SELECT POW(3,2); SELECT ROUND(123.45,1); SELECT ROUND(123.45,-1); . SELECT ROUND(123.45,0); SELECT ROUND(153.45,2); SELECT ROUND(155.45,0); . SELECT ROUND(245,-2); SELECT ROUND(255,- 2); 10, SELECT ROUND(897,3); 11, SELECT ROUND(457,-3); 12. SELECT ROUND(1567,-3); 13. SELECT RIGHT(‘MORNING,, 2); 44.SELECT MID( TRIM(‘GOOD ‘'), 4, 4); 15, SELECT INSTR( ‘GOOD MORNING’ , ‘GOOD’ ); Answers 41) 27, 2)9, 3/1235 4/120, 5)123 6)200, 7}155, 8)200, 9)300, 10)1000, 110, 12)2000, 13)NG , 14)G00D, 15)1Date Functions NOW0:- Function that returns the current date and time. Err PE CCnrO neDAYNAMEO:- Returns the weekday name of a date. mysql> select dayname('2022/08/2 oneal “Aggregate functions:- It perform calculation on multiple values and retum a single value. Itis also know as Multi-row functions List of aggregate functions MAX(),MINQ,AVGQ),SUMQ),COUNT() Aggregate function will not consider NULL values for calculation. Conside the following table named student, SC aT cram Sees aetna eT oreshysql> select count (ete) in set (0.26 sec) ime) will count the number of values(excluding NULL) present in the dataset. errors eee errs) set (0.00 sec) Here answer 4 is displyed even though 5 rows are present in the student table because one NULL value is present in the column named mark. Group By clause:- The GROUP BY statement groups rows that have the same values into summary rows. The GROUP BY statement is often used with aggregate functions ( COUNT(), MAX(), MIN() , SUM() , AVG() ) to group the result-set by one or more columns Consider the following table named teacher. | Physic | cae See | Chemistry | | Chemistry | | Physics | Write SQL statement to display number of Teacher teahing each subject. Select count(*) from teacher group by subject, count (*)The WHERE clause in MySQL is used with SELECT, INSERT, UPDATE, and DELETE queries to filter rows from the table or relation. It describes a specific condition when retrieving records from tables. Display number of employees in each subject whose experience is more than 2, select count(*) from teacher where experience>2 group by subject; WHERE clause is coming before group by. Having Clause The HAVING clause is often used with the GROUP BY clause to filter rows based on a specified condition. If we omit the GROUP BY clause, the HAVING clause behaves like the WHERE clause. Display the subject and number of teachers in each subject where group count is more than 1 select subject, count(*) from teacher group by subject having count(*)>1; HAVING clause is coming after group by Display details of teacher whose tid more than 3 select * from teacher having tid>3; OR select * from teacher where tid>3; HAVING clause is behaving like WHERE clause in this example. ‘ORDER BY Order by clause is used to arrange the rows in Ascending or Descending order of values in a Column, Display the details of teachers ascending of experience * from teacher order by experience; hny mete mopestes mc ane eereres Display the teachers details in the Descending order of experience. select * from teacher order by experience DI oe ooQuestions 1. Which of the following would arrange the rows in ascending order in SQL. a,SORTBY —_b. ALIGN BY c. GROUP BY d. ORDER BY Prachi has given the following command to obtain the highest marksSelect max(marks) from student where group by class; but she is not getting the desired result, Help her by writing the correct command. a, Select max(marks) from student where group by class; b, Select class, max(marks) from student group by marks; c. Select class, max(marks) group by class from student; d. Select class, max(marks) from student group by class; Help Ritesh to write the command to display the name of the youngest student? a, select name,min(DOB) from student ; b, select name,max(D0B) from student ; ¢, select name,min(DO8) from student group by name ; d. select name,maximum(DOB) from student; Arelation ‘Vehicles’ is given below : Vino [Type [Company [Price ‘AWI25__ [Wagon _|Maruti | 250000 30083 [Jeep | Mahindra | 4000000 9090 | SUV Mitsubishi | 2500000 M0892 [Mini van | Datsun 1500000 wa760 [SUV Maruti 7500000 2409 [Mini van [Mahindra | 350000 Write SQlcommands to: a. Display the average price of each type of vehicle having quant 'b, Count the type of vehicles manufactured by each company. c. Display the total price of all the types of vehicles. 5. Consider the table ‘FANS’ and answer the following. FANID_[FANNAME [FAN city [FAN DOB | FAN MODE Foor, SUSHANT | MUMBAl__| 2998-20-02 __| MAIL Foce RIVA MUMBA [19971212 __ | LETTER F003, ‘ANIKA DELHI 2001-06-30 | BLOG Food RUDRA AIMER | 2005-08-22 | MAIL F006, MIARA KOLKATTA [1996-01 __| BLOG Write MySQL queries for the following: a. To display the details of fans in descending order of their DOB . To count the total number of fans of each fan mode ¢. iv, To display the dob of the youngest fan,6. Write commands in SQL. for (i) and (ii) and output for (ii) Storeld Name [Location (ciy ]NoOfEmp] DateOpen | SalesAmt Sio1 Planet Fashion |eanda jMumbal] 7 [2018-10-16] 40000 S102 |vogue IKarolBagh [bain & | 2zo1s07-1a | 120000 S103 [Trends lPowal iMumbal 2015-0624 | 30000 Si04 [Super Fashion [Thane (Mumbai 2015-02-08 | 45000 $105 [Annabete [Southexn Delhi 2015-04-09 | 60000 5106 Rage lDefence Colony|beint 2015-03-01 | 20000 (i) To display the details of the store in alphabetical order of name. (ii) To display the City and the number of stores located in that City, only if the number of stores ismore than 2. (ili) SELECT COUNT(STOREID), NOOFEMP FROM STORE GROUP BY NOOFEMP HAVING MAX(SALESAMT)<60000; Answers a, select Type, avg(Price) from Vehicle group by Type having Qty>20; &. select Company, count(distinct Type) from Vehicle group by Company; c. Select Type, sum(Price* Qty) from Vehicle group by Type; i, SELECT * FROM FANS ORDER BY FAN_DOB DESC; ii, SELECT FAN_MODE, COUNT(*) FROM FANS GROUP BY FAN_MODE; SELECT MAX(FAN_DOB) FROM FANS; (i) SELECT * FROM STORE ORDER BY NAME; (ii) SELECT CITY, COUNT(*) FROM STORE GROUP BY STORE HAVING COUNT(*)>2; (iti) Count(Storeld) | NoofEmp | + +. + | 10 1 1 in 1 1Worksheet 1 A. Give output of the following: 1. Select Round(546.345, -2); 2. Select Round(546.345, -3); 3. Select Truncate(124.56, 1); 4, Select Truncate(124.56, -2); 5. Select SIGN(-341.45); 6. Select SIGN(0); 7. Select MOD(34,5); 8, Select MOD(6,8); 9. Select MOD(12.6, 8); 10. Select Pow(2,4); 11, Select Char(78,65,77,65,78); 12, Select Char(76.5,65.3,'77.6',65,78); 13, Select Substr('JS09876/XII-H/12'-8,7); 14. Select Trim(Leading 'Pp' from 'PppProgram Features’); 15, Select Instr(COORDINATION COMMITTEE ORDER'/OR’); 16, Select left(COORDINATION COMMITTEE ORDER’, length(‘committee’)); 17, Select right((COORDINATION COMMITTEE ORDER, length(‘committee order’)); B. Write MySQL statements for the following: |. Display first letter of the provided string in CAPITAL letter and rest all in small letters. Il, Display the phone number 07762227042 in the format (07762)-22-7042. Worksheet 2 select Char(70,65,67,69); select Char(65, 67.3, '69.3'); SELECT CONCAT(NAME, dept) AS 'NAME DEPT’ FROM Employee; SELECT LOWER(NAME) FROM Employee select substr(‘ABCDEFGH’, 3,4); select substr‘ABCDEFGH’, -3,4); SELECT UCASE(‘parxyz’); SELECT RTRIM(abcdefgh '); select trim(’ pgr Periodic Test 1 is over par"); }0.select instr(’O P JINDAL SCHOOL, KHARSIA ROAD RAIGARH, JINDAL STEEL AND POWER LIMITED’, JINDAL!); 11. SELECT LENGTH('O P JINDAL SCHOOL, RAIGARH’); 12. SELECT LENGTH(12345 ) 13. select left('JS12345/OPJS/XII/A’,7); 14, select substr('JS12345/0PJS/XIVA',9,4); 15. select right('JS12345/OPJS/XINA',5); 1 2 3. 4. 5. 6. 7. 8. 9. 1 38816. select MOD(23,5); 17. select power(3,4); 18. select ROUND(20.392, 1); 19. select sign(-945); 20.select sqrt(25) 21.select truncate(129.345,1); Worksheet 3 select pow(2,3), power(-2,3), pow(3,4); select round(12345.789,2), round(1434.56,-1); select round(62.789),round(6.89,0); select truncate(466.789,1), truncate(645.56,-1); select sqri(81)+20 from dual; select mod(23,2), mod(78,4); select sign(15,-15), sign(-25), sign(70); select char(65,67,69) from dual; select concat(“info’,"rmatics” 10. select concat("ISM -”,concatt“xii",""")); 11. select lower(“INFORM”),Icase(“Class XII"); 12. select upper("Class xii"), ucase(“informatics’); 13. select substring(“India is the Best’,3,2),substr(“Indian’ -2,1); 14. select length(trim(" abede defe “)); 15. select instr(“Informatics’,"r’); 16. select length(‘ab cde fge"); 17. select left("Informatics’,4) from dual; 18. select right(“Informatics” 6); 19. select mid(‘Indian School Muscat’,8,6); 20.Select curdate(), current_date(); 21.Select date(now()); 22. Select month(now()); 23.Select year(“2012-02-21"); 24, Select dayname(now()); 25. Select dayofmonth(“2011-03-23"); 26. Select dayofweek(now()); 27. Select dayofyear("2016-02-04"); 28. Select dayofyear(“2012-02-02"); 29. Select NOW(),SLEEP(3),SYSDATE();