Liny_@NotePad

沉迷ACG中

SQL练习

YOYO posted @ 2009年3月15日 22:38 in 【数据库】 with tags 单行函数 多行函数 连接 视图 oracle 练习 , 3637 阅读

【SCOTT/TIGER】

7. 用Decode函数实现横表和竖表的转换

  1. SELECT deptno DEPARTMENT_ID,
  2.        COUNT(*) "部门人数",
  3.        SUM(DECODE(SIGN(sal-9000),-1,0,1)) "超高收入人数（>9000）",
  4.        SUM(DECODE(SIGN(sal-9000),-1,0,( DECODE(SIGN(sal-12000),1,0,1) ) )) "极高收入人数（[9000,12000]）",
  5.        SUM(DECODE(SIGN(sal-6000),-1,0,( DECODE(SIGN(sal-9000),1,0,1) ) )) "高收入（[6000,9000]）",
  6.        SUM(DECODE(SIGN(sal-3000),-1,0,( DECODE(SIGN(sal-6000),1,0,1) ) )) "一般收入（[3000,6000]）",
  7.        SUM(DECODE(SIGN(sal-3000),-1,1,0)) "低收入（[<3000]）"
  8.   FROM emp
  9.  GROUP BY deptno;

【HR/HR】

1. 分组函数: 统计 EMPLOYEES 表中，薪水最高、最低、平均值

  1. SELECT MAX(salary) "薪水最高", MIN(salary) "薪水最低", AVG(salary) "薪水平均"
  2. FROM employees;

2. 多行函数: 按部门和工种统计：薪水最高、最低、平均值

  1. SELECT department_name "部门", job_title "工种", MAX(salary) "薪水最高", MIN(salary) "薪水最低", AVG(salary) "薪水平均"
  2. FROM employees, departments, jobs
  3. WHERE employees.department_id = departments.department_id
  4.         AND employees.job_id = jobs.job_id
  5. GROUP BY department_name, job_title;

3. 统计EMPLOYEES表的两个平均值：有提成员工的平均提成，所有员工计算的平均提成

  1. SELECT AVG(salary*commission_pct) "有提成员工的平均提成", AVG(COALESCE(salary*commission_pct,0)) "所有员工的平均提成"
  2. FROM employees;

4. 查出所有薪水高于平均薪水的员工

  1. SELECT employee_id, first_name, last_name, salary
  2. FROM employees
  3. WHERE salary>(
  4.       SELECT AVG(salary)
  5.       FROM employees);

5. 统计报表：部门员工平均薪水高于公司员工平均薪水的部门，列（部门号，平均薪水）。
  1. SELECT department_name "部门", AVG(salary) "平均工资"
  2. FROM departments, employees
  3. WHERE employees.department_id = departments.department_id
  4. GROUP BY department_name
  5. HAVING AVG(salary)>(   SELECT AVG(salary)
  6.                        FROM employees
  7.                     );

6. 多表连接: 报表：员工信息表，列（员工姓名、岗位名称、部门名称、所在位置、所在国家、所在区域)。

  1. SELECT first_name||' '||last_name "员工姓名", job_title "岗位名称", department_name "部门名称",
  2.        street_address||', '||city||', '||state_province "所在位置",COUNTry_name "所在国家", regions.region_name "所在领域"
  3. FROM employees, jobs, departments, locations, ries, regions
  4. WHERE employees.department_id = departments.department_id AND employees.job_id = jobs.job_id
  5.        AND locations.location_id = departments.location_id AND locations.COUNTry_id = countries.COUNTry_id
  6.        AND countries.region_id = regions.region_id;

7. 用两种方法（Oracle和SQL1999）分别实现:

  按国家和工种统计报表：最高薪水、最低薪水、平均薪水、员工总数、薪水总额

  统计各部门的平均薪水和薪水总数，要求没有员工的部门也要列出

Oracle：按国家和工种统计报表：最高薪水、最低薪水、平均薪水、员工总数、薪水总额Oracle：统计各部门的平均薪水和薪水总数，要求没有员工的部门也要列出

SQL99：统计各部门的平均薪水和薪水总数，要求没有员工的部门也要列出

8. 报表：部门的员工信息，要求用全外连接（没有部门的员工和没有员工的部门，都要列出）.

用SQL99实现用Oracle实现

9.自连接: 报表：每个员工所在的部门和上司名字

  1. SELECT employee.first_name||' '||employee.last_name "员工姓名", department_name "部门", manager.first_name||' '||manager.last_name "上司"
  2. FROM employees employee, departments, employees manager
  3. WHERE employee.department_id = departments.department_id AND employee.manager_id = manager.employee_id ;

9.1.级联查询: 查出employee_id=101员工的所有下属

  1. SELECT employee_id "员工编号", first_name||' '||last_name "姓名", manager_id "上级编号", LEVEL
  2. FROM employees
  3. START WITH employee_id = 101
  4. CONNECT BY PRIOR employee_id = manager_id
  5. ORDER BY LEVEL;

9.2.级联查询: 查出employee_id=206员工的所有上级

  1. SELECT employee_id "员工编号", first_name||' '||last_name "姓名", manager_id "上级编号", LEVEL
  2. FROM employees
  3. START WITH employee_id = 206
  4. CONNECT BY employee_id = PRIOR manager_id
  5. ORDER BY LEVEL DESC;

10. 匿名视图: 查找每个部门薪水最高的员工

  1. SELECT department_name "部门", first_name||' '||last_name "员工姓名", t1.salary "薪水"
  2. FROM departments,employees, (SELECT department_id deptno,MAX(salary) salary
  3.      FROM employees
  4.      GROUP BY department_id
  5.      ) t1
  6. WHERE departments.department_id = t1.deptno AND employees.salary = t1.salary AND employees.department_id = t1.deptno;

11. 实现分页技术：每页20行，分页查询employees表信息(使用Rownum 伪列)，要求起始页和每页行数用&startrownum,&pagerows输入

  1. SELECT *
  2. FROM (SELECT ROWNUM num, emp.*
  3.      FROM employees emp
  4.      WHERE rownum<=&startrownum*&pagerows) t1
  5. WHERE t1.num>(&startrownum-1)*&pagerows;

12. 删除一个表中的重复行

1）、创建一个表test_1(id,val)

  1. CREATE TABLE test_1(
  2.        id NUMBER(4),
  3.        val VARCHAR2(20)
  4. );

2）、为test_1插入一组数据，其中id出现重复

  1. INSERT INTO test_1(id,val)VALUES(1,'asadlkf');
  2. INSERT INTO test_1(id,val) VALUES (1,'aasdf');
  3. INSERT INTO test_1(id,val) VALUES (1,'asadlasdfakf');
  4. INSERT INTO test_1(id,val) VALUES (1,'asasdadlasdkf');
  5. INSERT INTO test_1(id,val) VALUES (2,'asadlkf');
  6. INSERT INTO test_1(id,val) VALUES (32,'asadlkf');
  7. INSERT INTO test_1(id,val) VALUES (25,'asadlkf');
  8. INSERT INTO test_1(id,val) VALUES (2,'asadlkf');
  9. INSERT INTO test_1(id,val) VALUES (8,'asadlkf');

3）、用SELECT distinct查出不重复的记录，作为验证基础
 
-- 查看所有记录-- 找出所有id

4）、用一条 delete 语句删除id重复的记录（保留每个id重复组的rowid最小的记录)
 
-- 删除重复id的记录-- 查看是否有未删除重复列的id-- 查看所有记录5）、验证正确后，DROP该表

 

 

  1. DELETE FROM test_1 a
  2. WHERE rowid>(SELECT MIN(ROWID)
  3.        FROM test_1 b
  4.        WHERE b.id = a.id
  5.        );

 

  1. SELECT id,COUNT(ROWID)
  2. FROM test_1
  3. GROUP BY id
  4. HAVING COUNT(ROWID)>1;

 

  1. SELECT id,val
  2. FROM test_1;

 

  1. DROP TABLE test_1;

 

  1. SELECT id,val
  2. FROM test_1;

 

  1. SELECT DISTINCT id
  2. FROM test_1;

 

  1. SELECT department_name "部门", first_name||' '||last_name "员工"
  2. FROM employees emp FULL OUTER JOIN departments dept
  3. ON emp.department_id = dept.department_id
  4. ORDER BY department_name,first_name;

 

  1. SELECT department_name "部门", first_name||' '||last_name "员工"
  2. FROM employees emp, departments dept
  3. WHERE emp.department_id = dept.department_id (+)
  4. UNION
  5. SELECT department_name "部门", first_name||' '||last_name "员工"
  6. FROM employees emp, departments dept
  7. WHERE emp.department_id(+) = dept.department_id;

 

  1. SELECT department_name "部门", ROUND(COALESCE(AVG(salary),0),2) "平均薪水", COUNT(salary) "薪水种数"
  2. FROM employees
  3. RIGHT JOIN departments
  4. ON employees.department_id = departments.department_id
  5. GROUP BY department_name;

 

  1. SELECT COUNTry_name "国家", job_title "岗位", MAX(salary) "最高薪水", MIN(salary) "最低薪水", COUNT(*) "员工总数", SUM(salary) "薪水总额"
  2. FROM employees, jobs, departments, locations, countries
  3. WHERE employees.job_id = jobs.job_id
  4.       AND departments.department_id = employees.department_id
  5.       AND locations.location_id = departments.location_id
  6.       AND countries.COUNTry_id = locations.COUNTry_id
  7. GROUP BY COUNTry_name, job_title;

 

  1. SELECT department_name "部门", ROUND(COALESCE(AVG(salary),0),2) "平均薪水", COUNT(salary) "薪水种数"
  2. FROM employees, departments
  3. WHERE employees.department_id(+) = departments.department_id
  4. GROUP BY department_name;
Small_feed 评论 (0)

登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter