CpSc 3220 The Language of SQL

CpSc 3220The Language of SQL Chapters 4-6

Using Functions • Function types • Character (string) • Numeric • Date and Time • Summary (aggregate)

Some Character Functions • concat(str1,str2,…) • format(numstr,numDec) • insert(str,pos,len,insStr) • lcase(str) • ucase(str) • length(str) • substr(str,pos,len)

Some Numeric Functions • abs(x) • acos(x) • ceil(x) • exp(x) • mod(x) • pi(x) • pow(x,y) • round(x,d) • truncate(x,d)

Some Date/Time Functions • curdate() • date_format(date,format) • datedif(exp1,exp2) • dayname(date)

Summary Functions • count(exp) • min(exp) • max(exp) • sum(exp) • avg(exp)

Sorting Data • SQL uses the ORDER BY clause to sort rows

Syntax of the SELECT statement SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]

Column-Based Logic • The select_expr term in the SELECT command can include logical conditions

A SELECT with a CASE Expression SELECT column1, column2, caseExpression FROM table

A Simple Example SELECT CASE categoryCode WHEN ‘F’ THEN ‘Fruit’ WHEN ‘V’ THEN ‘Vegetable’ ELSE ‘Other’ END AS ‘CategoryName’ FROM products;

The Searched Format SELECT CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 . . . [ELSE default] END

A Searched Format Example SELECT CASE WHEN categoryCode = ‘F’ THEN ‘Fruit’ WHEN categoryCode=‘V’ THEN ‘Vegetable’ ELSE ‘Other’ END AS ‘CategoryName’ FROM products;

CpSc 3220 The Language of SQL