1.1

-- add the employee's surname (LASTNAME) from the EMPLOYEE
table
-- to each row of the result.
SELECT SAMP.EMP_ACT.*, LASTNAME
FROM SAMP.EMP_ACT, SAMP.EMPLOYEE
WHERE EMP_ACT.EMPNO = EMPLOYEE.EMPNO
-- Determine the employee number and salary of sales
representatives
-- along with the average salary and head count of their
departments.
-- This query must first create a new-column-name specified
in the AS clause
-- which is outside the fullselect (DINFO)
-- in order to get the AVGSALARY and EMPCOUNT columns,
-- as well as the DEPTNO column that is used in the WHERE
clause
SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY, DINFO.AVGSALARY,
DINFO.EMPCOUNT
FROM EMPLOYEE THIS_EMP,
(SELECT OTHERS.WORKDEPT AS DEPTNO,
AVG(OTHERS.SALARY) AS AVGSALARY,
COUNT(*) AS EMPCOUNT
FROM EMPLOYEE OTHERS
GROUP BY OTHERS.WORKDEPT
)AS DINFO
WHERE THIS_EMP.JOB = 'SALESREP'
AND THIS_EMP.WORKDEPT = DINFO.DEPTNO
SQL Expressions
Expressions can appear in SQL statements and clauses.
Syntax for many statements and expressions includes the term Expression, or a term for a specific kind of
expression such as TableSubquery. Expressions are allowed in these specified places within statements.
Some locations allow only a specific type of expression or one with a specific property.
If not otherwise specified, an expression is permitted anywhere the word Expression appears in the syntax. This
includes:
• ORDER BY Clause
• SelectExpression
• UPDATE
• VALUES Expression
• WHERE Clause
Of course, many other statements include these elements as building blocks, and so allow expressions as part of
these elements.
The following sections list all the possible SQL expressions and indicate where the expressions are allowed.
General expressions
General expressions are expressions that might result in a value of any type.
ExplanationExpression type
A column-Name that references the value of the column made visible
to the expression containing the Column reference.
Column reference
523
SQL Language Reference