而今来看一些 SQL 函数的例子。第一个例子从一个表中选择全数书的题目和代价。若是该书的代价为 NULL,则将其代价发挥阐发为 0.00。
SELECT TITLE, COALESCE(PRICE, 0.00) AS PRICE
FROM TITLES;
接上去的例子前去的效果是公司的称谓以及公司称谓中所含的字符数:
SELECT COMPANYNAME, LENGTH(COMPANYNAME)
FROM CUSTOMERS
而今看看如何前去每个作者的名字(first name)最右边的 5 个字符:
SELECT RIGHT(AU_FNAME, 5)
FROM AUTHORS
下一个例子运用 project 表,将宿主变量 AVERAGE (decimal(5,2)) 设置为名为 D11 的部门(DEPTNO)中项目标匀称人为程度(PRSTAFF)。
SELECT AVG(PRSTAFF)
INTO :AVERAGE
FROM PROJECT
WHERE DEPTNO = 'D11'
群众表表达式 是一个外地临时表,可以在一条 SQL 语句中引用多次。这个临时表只能存在于界说它的 SQL 语句的生命周期内。每次群众表表达式被引用时,其效果都是不异的。临时表是在 SQL 语句顶用 WITH 子句界说的。下面是具体的语法:
WITH <COMMON NAME1> AS ( <SELECT EXPRESSION>), <COMMON NAME2>
AS (<SELECT EXPRESSION), & SELECT <COLUMN> FROM <TABLE_NAME> <WHERE_CLAUSE>
<table_name> 是数据库中的一个表,也可以是由一个包孕 WITH 子句的 SQL 语句界说的 <Common name>。 下面是一个例子:
WITH PROD_QUANTITY AS
(SELECT PRODUCT_ID, SUM (QUANTITY) AS QUANTITY
FROM CUSTOMER_ORDER_ITEM
GROUP BY PRODUCT_ID),
TOTALS AS
(SELECT -1 AS PRODUCT_ID, SUM(QUANTITY) AS TOTAL)
SELECT PRODUCT_ID, QUANTITY
FROM PROD_QUANTITY
UNION
SELECT PRODUCT_ID, TOTALS
FROM TOTALS
ORDER BY 1 DESC
下面看另一个例子:
WITH
PAYLEVEL AS
(SELECT EMPNO, EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,
SALARY BONUS COMM AS TOTAL_PAY
FROM EMPLOYEE
WHERE EDLEVEL > 16),
PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS
(SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)
FROM PAYLEVEL
GROUP BY EDLEVEL, HIREYEAR)
SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, DECIMAL(AVG_TOTAL_PAY,7,2)
FROM PAYLEVEL, PAYBYED
WHERE EDLEVEL = EDUC_LEVEL
AND HIREYEAR= YEAR_OF_HIRE
AND TOTAL_PAY < AVG_TOTAL_PAY