SQL - 基础知识(1)

--不加分组函数修饰的列必定要出现在GROUP BY里
SELECT title, SUM(salary) PAYROLL
FROM s_emp
WHERE title NOT LIKE ’VP%’
GROUP BY title;

--就是先按照DEPT_ID分组，当DEPT_ID相同的时候，再按TITLE分组，而COUNT(*)以合成的组计数。顺序对结果有决定性的影响。
SELECT dept_id, title, COUNT(*)
FROM s_emp
GROUP BY dept_id, title;

-- 应在GROUP BY 后面加上HAVING AVG(salary) > 2000；因为是用来限制组的返回
SELECT dept_id, AVG(salary)
FROM s_emp
GROUP BY dept_id;
HAVING AVG(salary) > 2000;

-- 子查询中的GROUP 函数的应用
SELECT last_name, title, salary
FROM s_emp
WHERE salary < (SELECT AVG(salary) FROM s_emp);

-- 选择出工资最高的员工的家庭住址
SELECT emp_addr 
FROM employees 
WHERE salary = (SELECT MAX(salary) FROM employees);

-- 子查询被多次执行，因为它出现在HAVING 子句中
SELECT dept_id, AVG(salary)
FROM s_emp
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM s_emp WHERE dept_id = 32);

SELECT title, AVG(salary)
FROM s_emp
GROUP BY title
HAVING AVG(salary) = (SELECT MIN(AVG(salary)) FROM s_emp GROUP BY title);

SQL> define lname = aaa
SQL> define lname
DEFINE LNAME           = "aaa" (CHAR)
SQL> select * from s_emp
  2  where last_name = '&lname';
原值    2: where last_name = '&lname'
新值    2: where last_name = 'aaa'

        ID FIRST_NAME LAST_NAME      SALARY
---------- ---------- ---------- ----------
         1 aaa        aaa               100

SQL> define fname
SP2-0135: 符号 fname 未定义

-- SEQUENCE - 不能修改START　WITH，如果变，则要RE－CREATE
CREATE SEQUENCE s_dept_id
INCREMENT BY 1
START WITH 51
MAXVALUE 9999999
NOCACHE
NOCYCLE;