SQL Cheat Sheet: FUNCTIONS and Implicit JOIN

Command Syntax (MySQL/DB2) Description Example (MySQL/DB2)

SELECT COUNT(column_name) FROM COUNT function returns the number of rows

COUNT SELECT COUNT(dep_id) FROM employees;
table_name WHERE condition; that match a specified criterion.

SELECT AVG(column_name) FROM table_name AVGfunction returns the average value of a

AVG SELECT AVG(salary) FROM employees;
WHERE condition; numeric column.

SELECT SUM(column_name) FROM table_name SUMfunction returns the total sum of a

SUM SELECT SUM(salary) FROM employees;
WHERE condition; numeric column.

SELECT MIN(column_name) FROM table_name MIN function returns the smallest value of
MIN SELECT MIN(salary) FROM employees;
WHERE condition; the SELECTED column.

SELECT MAX(column_name) FROM table_name MAXfunction returns the largest value of the
MAX SELECT MAX(salary) FROM employees;
WHERE condition; SELECTED column.

SELECT ROUND(2number, decimals, ROUND function rounds a number to a

ROUND SELECT ROUND(salary) FROM employees;
operation) AS RoundValue; specified number of decimal places.

LENGTH function returns the length of a

LENGTH SELECT LENGTH(column_name) FROM table; SELECT LENGTH(f_name) FROM employees;
string (in bytes).

UCASE function displays the column name in

UCASE SELECT UCASE(column_name) FROM table; SELECT UCASE(f_name) FROM employees;
each table in uppercase.

LCASE function displays the column name in

LCASE SELECT LCASE(column_name) FROM table; SELECT LCASE(f_name) FROM employees;
each table in lowercase.

DISTINCT function is used to display data SELECT DISTINCT UCASE(f_name) FROM

DISTINCT SELECT DISTINCT column_name FROM table;
without duplicates. employees;

DAY function returns the day of the month SELECT DAY(b_date) FROM employees where
DAY SELECT DAY(column_name) FROM table
for a given date. emp_id = 'E1002';

CURRENT_DATE is used to display the current

CURRENT_DATE SELECT CURRENT_DATE; SELECT CURRENT_DATE;
date.

DATEDIFF() is used to calculate the

difference between two dates or time SELECT DATEDIFF(CURRENT_DATE,
DATEDIFF() SELECT DATEDIFF(date1, date2);
stamps. The default value generated is the date_column) FROM table;
difference in number of days.

FROM_DAYS() is used to convert a given SELECT FROM_DAYS(DATEDIFF(CURRENT_DATE,

FROM_DAYS() SELECT FROM_DAYS(number_of_days);
number of days to YYYY-MM-DD format. date_column)) FROM table;

DATE_ADD() is used to calculate the date

after lapse of mentioned number of units of
date type, i.e. if n=3 and type=DAY, the
DATE_ADD() SELECT DATE_ADD(date, INTERVAL n type); SELECT DATE_ADD(date, INTERVAL 3 DAY);;
result is a date 3 days after what is
mentioned in date column. The type
valiable can also be months or years.

DATE_SUB() is used to calculate the date

prior to the record date by mentioned
number of units of date type, i.e. if n=3 and
DATE_SUB() SELECT DATE_SUB(date, INTERVAL n type); SELECT DATE_SUB(date, INTERVAL 3 DAY);;
type=DAY, the result is a date 3 days before
what is mentioned in date column. The type
valiable can also be months or years.

SELECT emp_id, f_name, l_name, salary

FROM employees
where salary
< (SELECT AVG(salary)
Subquery is a query within another SQL FROM employees);
SELECT column_name [, column_name ] FROM query and embedded within the WHERE
table1 [, table2 ] WHERE column_name clause.
Subquery OPERATOR (SELECT column_name [, SELECT * FROM ( SELECT emp_id, f_name,
column_name ] FROM table1 [, table2 ] A subquery is used to return data that will l_name, dep_id FROM employees) AS
[WHERE]) be used in the main query as a condition to emp4all;
further restrict the data to be retrieved.

SELECT * FROM employees WHERE job_id IN

(SELECT job_ident FROM jobs);

Implicit Inner Join combines two or

SELECT column_name(s) FROM table1,
more records but displays only matching SELECT * FROM employees, jobs where
Implicit Inner Join table2 WHERE table1.column_name =
table2.column_name; values in both tables. Inner join applies employees.job_id = jobs.job_ident;
only the specified columns.

Implicit Cross Join is defined as a

SELECT column_name(s) FROM table1, Cartesian product where the number of
Implicit Cross Join SELECT * FROM employees, jobs;
table2; rows in the first table is multiplied by the
number of rows in the second table.
Author(s)
Lakshmi Holla
Abhishek Gagneja