Functions

IN

SELECT * FROM Products WHERE vendor_id IN ( 'V000001', 'V000010' ) ORDER BY product_price

NOT IN

SELECT product_id, product_name FROM Products WHERE NOT vendor_id IN ('V000001', 'V000002') ORDER BY product_id

BETWEEN

SELECT id, product_id, product_name, product_price FROM Products WHERE id BETWEEN 000005 AND 000034 ORDER BY id

NOT...BETWEEN

SELECT product_id, product_name FROM Products WHERE NOT vendor_id BETWEEN 'V000002' and 'V000005' ORDER BY product_id

IS NULL

SELECT product_name FROM Products WHERE product_price IS NULL

IS NOT NULL

SELECT id, product_name FROM Products WHERE product_price IS NOT NULL ORDER BY id

AND

SELECT * FROM Products WHERE vendor_id = 'V000001' AND product_price <= 4000 ORDER BY product_price

OR

SELECT * FROM Products WHERE vendor_id = 'V000001' OR vendor_id = 'V000009'

NOT

SELECT product_id, product_name FROM Products WHERE NOT vendor_id = 'V000002'

LIKE

SELECT * FROM Products WHERE product_name LIKE 'NAME%' ORDER BY product_name

NOT LIKE

SELECT * FROM Products WHERE product_name NOT LIKE 'NAME%' ORDER BY product_name

CONCAT

SELECT product_id, product_name, Concat( product_id , '(', product_name ,')' ) AS product_test FROM Products ORDER BY product_id

+

SELECT 3 * 2+5-100/50

-

SELECT 3 * 2+5-100/50

*

SELECT order_num, product_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num BETWEEN 000009 AND 000028 ORDER BY order_num

/

SELECT 3 * 2+5-100/50

UPPER

SELECT id, product_id, UPPER(product_name) FROM Products WHERE id > 10 ORDER BY product_id

LOWER

SELECT id, product_id, LOWER(product_name) FROM Products WHERE id <= 10 ORDER BY product_id

SOUNDEX

SELECT * FROM Vendors WHERE SOUNDEX(vendor_name) = SOUNDEX('Huawee') ORDER BY vendor_name

IFNULL

SELECT IFNULL(product_id, 0) FROM Products;

DAY()

SELECT * FROM TAB_DATE WHERE DAY(date)=21

SELECT * FROM TAB_DATE WHERE date='2018-12-21'

INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22')

MONTH()

SELECT * FROM TAB_DATE WHERE MONTH(date)=12

SELECT * FROM TAB_DATE WHERE date='2018-12-21'

INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22')

YEAR()

SELECT * FROM TAB_DATE WHERE YEAR(date)=2018

SELECT * FROM TAB_DATE WHERE date='2018-12-21'

INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22')

DAYOFYEAR()

SELECT * FROM TAB_DATE WHERE DAYOFYEAR(date)=365

SELECT * FROM TAB_DATE WHERE date='2018-12-31'

INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22')

DAYOFWEEK()

SELECT * FROM TAB_DATE WHERE DAYOFWEEK(date)=6

SELECT * FROM TAB_DATE WHERE date='2018-12-21'

INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22')

WEEKOFYEAR()

SELECT * FROM TAB_DATE WHERE WEEKOFYEAR(date)=51

SELECT * FROMTAB_DATE WHERE date='2018-12-21'

INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22')

SQRT()

SELECT id, product_price, SQRT(product_price) AS price_sqrt FROM Products WHERE product_price < 4000 ORDER BY product_price

AVG()

SELECT AVG(product_price) AS avg_product FROM Products

COUNT()

SELECT COUNT(*) AS num_product FROM Products

MAX()

SELECT id, product_id, product_name, MAX(product_price) AS max_price FROM Products ORDER BY id

MIN()

SELECT id, product_id, product_name, MIN(product_price) AS min_price FROM Products ORDER BY id

SUM()

SELECT SUM(product_price) AS sum_product FROM Products

Functions

Function row_count() is not supported.