<jupyter_start><jupyter_text>Character, Number, Date, and Group functions---  Connecting with the database<jupyter_code>%load_ext sql
# replace with your own credentials
%sql ibm_db_sa://my-username:my-password@dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net:50000/BLUDB<jupyter_output><empty_output><jupyter_text>• GROUP FUNCTIONS `Group functions` (also called `aggregate functions`) are mathematical functions to operate on sets of rows to give one result per set.  1. AVG FunctionIt returns the average of the values in the selected columns.<jupyter_code>%sql select * from STUDENTS
%sql select avg(test_score) from STUDENTS
%sql select avg(test_score) as average_marks from STUDENTS<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>2. MIN FunctionReturns the data from the row in which the specified column contains the minimum value.<jupyter_code>%sql select min(test_score) as min_marks from STUDENTS<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>3. MAX FunctionReturns the data of the row in which the specified column contains the maximum value.<jupyter_code>%sql select max(test_score) as max_marks from STUDENTS<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>4. SUM FunctionReturns the total of all the values in a specified column.<jupyter_code>%sql select sum(test_score) as toal_marks from STUDENTS<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>5. COUNT FunctionIt adds the number of rows that matches the criteria specified in the select statement.<jupyter_code>%sql select count(*) as no_of_rows from STUDENTS<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>• CURRENT DATE AND TIME The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database. As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated.  1. NOWThis function returns the current date and time of the system.<jupyter_code>%sql select now() as current_date_time from STUDENTS where test_score=54<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>2. CURDATE Returns the current date of the system.<jupyter_code>%sql VALUES CURRENT DATE<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>3. CURTIMEReturns the current time of the system.<jupyter_code>%sql VALUES CURRENT TIME<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>• CHARACTER FUNCTIONSCharacter functions accept character inputs and can return either characters or number values as output.  1. ASCIIReturns the ASCII code of the leftmost character of the string passed.<jupyter_code>%sql select first_name, ascii(first_name) as ascii_fname from STUDENTS<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>2. CHARReturns the character of ineteger.<jupyter_code>%sql VALUES chr('23')<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>3. CONCATReturns the string that results from the concatenation of the arguments.<jupyter_code>%sql select concat(first_name, last_name) as name from STUDENTS where test_score=54<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>4. INSTRReturns the location of the specified substring in a string.<jupyter_code>%sql select instr('quadratic', 'dra') as at_index from STUDENTS where test_score=54<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>5. INSERTInserts a substring into a string at the specified position.<jupyter_code>%sql select insert('quadratic',3,4, 'what') from STUDENTS where test_score=54<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>6. LENGTHReturns the length of the string.<jupyter_code>%sql select first_name, length(first_name) as length from STUDENTS<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>7. LOWERConverts all the letters to lower case.<jupyter_code>%sql select lower('HELLO') from STUDENTS where test_score=54<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>8. UPPERConverts all the letters to upper case.<jupyter_code>%sql select upper('hello') from STUDENTS where test_score=54<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>• NUMBER FUNCTIONSSQL numeric functions are used primarily for numeric manipulation and/or mathematical calculations.  1. POWER (X,Y)Returns the value of x raised to the power of y.<jupyter_code>%sql VALUES POWER(5, 2)<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>2. Round (M,N)Returns M round to N places to the right of decimal.<jupyter_code>%sql VALUES ROUND(873.729, 1)<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>3. SQRT (N)Returns the square root of N.<jupyter_code>%sql VALUES SQRT(25)<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>4. EXP (N)<jupyter_code>%sql VALUES EXP(3)<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>5. MOD (N,M)<jupyter_code>%sql VALUES MOD(125, 50)<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>6. FLOOR<jupyter_code>%sql VALUES FLOOR(3.35)<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>7. CEIL<jupyter_code>%sql VALUES Ceiling(3.35)<jupyter_output>* ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB
Done.
StarcoderdataJupyterStructuredCleanDedup