标准SQL查询 总结练习

16、学生选课表中，找出没有选过“黎明”老师课程的所有学生姓名：

可能你会想，我可以先选出老师名不是黎明的课程编号。然后跟sc连接，求出没选这个编号的学生。列出如下语句：SQL>select s.sno,sname,c.cteacher from s,c,sc where c.cno=sc.cno and sc.sno=s.s

no and c.cteacher<> 'liming';

      SNO SNAME                CTEACHER

---------- ----------------------------------------

        2 lisi                 chenzhong

        2 lisi                 yanghua

        3 wangwu               yanghua

        3 wangwu               wanghui

        4 huliu                wanghui

        4 huliu                chenzhong

但是，我们这时候，会想，有学生即选了黎明老师的，也选了别人的（其中还有些学生就选了一门课，黎明老师的，那样的话上面的结果是对的）那上面会把那种学生照样选了出来。

我们要查的是，没有选过黎明老师课的学生，那我们可以找出选了黎明老师课的学生的学号啊。。

这样：select snofrom sc where cno = (select cno from c where cteacher='liming');

然后我们求出学号不在这里面的学生的姓名：下面才是正确结果哦。。。。。。。。

SQL> Select sname from s where sno notin (select sno from sc where cno = (selec

t cno from c where cteacher='liming'));

SNAME

--------------------

dingqi

17、列出两门以上（含两门）不及格的学生的姓名及平均成绩：

二话不说，先求出两门（含两门）不及格的学生的学生编号，以及不及格科目数：

  select sno,count(*) cnt  from sc where scorade <60 group by snohaving count(*) >=2

      SNO        CNT

---------- ----------

3                                  2

看来只有3号学生的不及格科目大于等于2.。。别忘了求出每个学生的平均成绩。。

SQL> select avg(scorade) avg_sc ,snofrom sc group by sno;

   AVG_SC        SNO

---------- ----------

       78          1

72.3333333          2

52.3333333          3

       77          4

     72.5          5

这就是5名同学的平均成绩咯。。现在看来，我们要求上面这俩结果表的连接。。只要sno相同，就是我们要求的学生的结果，恩，最后看结果：

  SQL> select t1.avg_sc,t2.sno,t2.cnt from

    (select avg(scorade) avg_sc ,sno from sc group by sno) t1,

    (select sno,count(*) cnt  from scwhere scorade <60 group by sno having co

unt(*) >=2 ) t2

  where t1.sno=t2.sno

AVG_SC        SNO        CNT

---------- ---------- ----------

52.3333333          3          2

平均成绩会有的。。学生号也会有的。。。还赠送了个不及格科目统计。。。。啊哈哈。。。。

18、列出即学过1号课程，又学过2号课程的学生编号：

SQL> select sno from sc where cno=1 andsno in(select sno from sc where cno=2);

       SNO

----------

        3

        4

这个简单点，可以用两个相同的查询做等值连接sno。。。也可以像答案这样。比较好理解。。

19、40道经典练习题（需要的表的网址：http://download.csdn.net/detail/changyanmanman/5870379）

--2.  Show the last name,  job, salary, and commission of those employees who earn commission. Sort the data by the salary in descending order.

SELECT last_name, job_id, salary, commission_pct
FROM   employees
WHERE  commission_pct IS NOT NULL
ORDER BY salary DESC

--3.  Show the employees that have no commission with a 10% raise in their salary (round off the salaries).
select 'the salary of '||last_name||'after a 10% raise is '||round(salary*(1+0.1),0) from employees where commission_pct is null;

--4.  Show the last names of all employees together with the number of years and the number of completed months that they have been employed.
 SELECT last_name,
       TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) YEARS,
       TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, hire_date), 12)) MONTHS
FROM  employees 

--需要注意，上面写法是标准答案，但是如果我用下面的写法，extract命令，得到的结果会小于正确结果，因为其算法不正确。

select last_name, extract(year from to_date(sysdate))-extract(year from to_date(hire_date)) as years,
    mod(trunc(months_between(sysdate,hire_date),0),12) months from employees

--5.  Show those employees that have a name starting with J, K, L, or M.
select last_name from employees where last_name like '%J%'
 OR last_name like '%K%' 
 OR last_name like '%L%' 
 OR last_name like '%M%'
union
select last_name from employees where first_name like '%J%'
 OR last_name like '%K%' 
 OR last_name like '%L%' 
 OR last_name like '%M%'

上面属于非常笨的方法了，下面是用一个非常简单的办法：

SELECT last_name
FROM   employees
WHERE  SUBSTR(last_name, 1,1) IN ('J', 'K', 'L', 'M')

--6.  Show all employees, and indicate with “Yes” or “No” whether they receive a commission.
select last_name,salary ,nvl2(commission_pct,'YES','NO') COM
from employees

上面是我做的，我觉得我的答案也很好啊，下面是标准答案：

SELECT last_name, salary,
       decode(commission_pct, NULL, 'No', 'Yes') commission
FROM   employees

--7.  Show the department names, locations, names, job titles, and salaries of employees who work in location 1800.
SELECT d.department_name, d.location_id,
       e.last_name, e.job_id, e.salary
FROM   employees e, departments d
WHERE   e.department_id = d.department_id
AND     d.location_id = 1800

--8.   How many employees have a name that ends with an n? Create two possible solutions
select count(*) from employees where last_name like '%n'
select (select count(*) from employees) -(select  count(*) from employees where last_name not like '%n') "COUNT(*)" from dual;

SELECT COUNT(*)
FROM   employees
WHERE  SUBSTR(last_name, -1) = 'n'
/

--9.   Show the names and locations for all departments, and the number of employees working in each department. 
--Make sure that departments without employees are included as well.

select t.department_id,m.department_name,m.location_id,t.COUN "COUNT(E.EMPLOYEE_ID)" from
(select b.department_id department_id, count(a.employee_id) "COUN"
from employees a right join departments b  on(a.department_id=b.department_id)
group by b.department_id) t , departments m 
where m.department_id=t.department_id;

上面是我写的，下面是答案

SELECT d.department_id, d.department_name,
       d.location_id,   COUNT(e.employee_id)
FROM   employees e, departments d
WHERE   e.department_id(+) = d.department_id
GROUP BY d.department_id, d.department_name, d.location_id

--10.  Which jobs are found in departments 10 and 20?
select job_id,department_id from employees  where department_id in(10,20)

--11.  Which jobs are found in the Administration and Executive departments, 
--and how many employees do these jobs? Show the job with the highest frequency first.

SELECT e.job_id, count(e.job_id) FREQUENCY
FROM    employees e, departments d
WHERE  e.department_id = d.department_id
AND    d.department_name IN ('Administration', 'Executive')
GROUP BY e.job_id
ORDER BY FREQUENCY DESC

 --12.  Show all employees who were hired in the first half of the month (before the 16th of the month).
select last_name,hire_date from employees a, 
 (select employee_id ,extract(day from to_date(hire_date)) day from employees where extract(day from to_date(hire_date))<16) b
 where a.employee_id=b.employee_id 

上面我写的查询很麻烦了，答案很好，用了'DD' 日期格式：

SELECT last_name, hire_date
FROM   employees
WHERE  TO_CHAR(hire_date, 'DD') < 16

--13.  Show the names, salaries, and the number of dollars (in thousands) that all employees earn
SELECT last_name, salary, TRUNC(salary, -3)/1000 Thousands
FROM   employees

--14.  Show all employees who have managers with a salary higher than $15,000. 
--Show the following data: employee name, manager name, manager salary, and salary grade of the manager.
查询经理薪水高于15000的， 员工的姓名、经理姓名、经理薪水、经理的工资等级

select t.employee,t.manager,t.salary,s.grade_level gra from 
(select a.last_name employee,b.last_name manager ,b.salary salary from employees a,employees b 
where a.manager_id=b.employee_id and b.salary>15000) t ,job_grades s
where (t.salary  between s.lowest_sal and s.highest_sal); 

我用了一个子查询，不过答案很简单，三个表直接连接。

SELECT e.last_name, m.last_name manager, m.salary,
       j.grade_level
FROM   employees e, employees m, job_grades j
WHERE  e.manager_id = m.employee_id
AND    m.salary BETWEEN j.lowest_sal AND j.highest_sal
AND    m.salary > 15000

--15.  Show the department number, name, number of employees, and average salary of all departments, 
--together with the names, salaries, and jobs of the employees working in each department
--查询所有部门的id，名字，员工数量，平均薪水，还有在各个部门工作的员工的信息。

select d.department_id,
       d.avg_sal,
       d.emp_count,
       c.last_name,
       c.salary,
       c.job_id
  from employees c,
       (select b.department_id department_id,
               round(nvl(avg(salary), 0), 2) avg_sal,
               count(a.employee_id) emp_count
          from employees a
         right join (select department_id from departments) b
            on (a.department_id = b.department_id)
         group by b.department_id) d
 where c.department_id = d.department_id

我按照上面这样写的，思路虽然清晰，但是不够简洁，因为多使用了一层子查询。其实把里面那个

(select b.department_id department_id,
               round(nvl(avg(salary), 0), 2) avg_sal,
               count(a.employee_id) emp_count
          from employees a
         right join (select department_id from departments) b
            on (a.department_id = b.department_id)
         group by b.department_id) d

可以合并成对employees 和 departments两个表的一个查询就行。

--16.  Show the department number and the lowest salary of the department with the highest average salary.
SELECT department_id, MIN(salary)
FROM   employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT MAX(AVG(salary))
                      FROM   employees
                      GROUP BY department_id)

--17.  Show the department numbers, names, and locations of the departments where no sales representatives work.

SELECT *
FROM   departments
WHERE  department_id NOT IN(SELECT department_id
                             FROM employees
                             WHERE job_id = 'SA_REP'
                             AND department_id IS NOT NULL)

--18.  Show the department number, department name, and the number of employees working in each department that:
--a.  Includes fewer than 3 employees:
--b.  Has the highest number of employees
--c.  Has the lowest  number of employees: 
a/

select b.department_id,a.department_name ,b.count_employee from departments a ,
(select department_id,count(employee_id) count_employee from employees group by department_id) b
where a.department_id=b.department_id and b.count_employee < 3
答案写法：

SELECT d.department_id, d.department_name, COUNT(*)
FROM   departments d, employees e
WHERE  d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(*) < 3

b/ 
select d.department_id,a.department_name ,d.count_employee from departments a ,
   (  select c.department_id,c.count_employee from 
      (select department_id,count(employee_id) count_employee from employees group by department_id) c 
       where c.count_employee = 
      (select  max(b.count_employee) from (select department_id,count(employee_id) count_employee from employees group by department_id) b)
   )d
 where a.department_id=d.department_id 
 答案写法：

SELECT d.department_id, d.department_name, COUNT(*)
FROM   departments d, employees e
WHERE  d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
                   FROM   employees
                   GROUP BY department_id)

c/

select d.department_id,a.department_name ,d.count_employee from departments a ,
   (  select c.department_id,c.count_employee from 
      (select department_id,count(employee_id) count_employee from employees group by department_id) c 
       where c.count_employee = 
      (select  min(b.count_employee) from (select department_id,count(employee_id) count_employee from employees group by department_id) b)
   )d
 where a.department_id=d.department_id 
答案写法：

SELECT d.department_id, d.department_name, COUNT(*)
FROM   departments d, employees e
WHERE  d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(*) = (SELECT MIN(COUNT(*))
                   FROM   employees
                   GROUP BY department_id)

-- 19.  Show the employee number, last name, salary, department number, and the average salary in their department for all employees.

select a.employee_id,a.last_name,a.salary,a.department_id, round(b.avg_salary,2) avg_sal from employees a,
(select avg(salary ) avg_salary ,department_id from employees group by department_id) b
where a.department_id=b.department_id

答案写法：

SELECT e.employee_id, e.last_name,
       e.department_id,   AVG(s.salary)
FROM   employees e, employees s
WHERE   e.department_id = s.department_id
GROUP BY e.employee_id, e.last_name, e.department_id

--20.  Show all employees who were hired on the day of the week on which the highest number of employees were hired.
SELECT last_name, TO_CHAR(hire_date, 'DAY') day
FROM   employees
WHERE  TO_CHAR(hire_date, 'Day') =
       (SELECT TO_CHAR(hire_date, 'Day')
        FROM   employees
        GROUP BY TO_CHAR(hire_date, 'Day')
        HAVING COUNT(*) = (SELECT MAX(COUNT(*))
                           FROM   employees
                           GROUP BY TO_CHAR(hire_date, 'Day')))


--21.  Create an anniversary overview based on the hire date of the employees. Sort the anniversaries in ascending order.
select last_name, to_char(hire_date, 'MM-DD') anniversary from employees
order by to_char(hire_date, 'MM-DD')

SELECT last_name, TO_CHAR(hire_date, 'Month DD') BIRTHDAY
FROM   employees
ORDER BY TO_CHAR(hire_date, 'DDD')

--22. Find the job that was filled in the first half of 1990 and the same job that was filled during the same period in 1991.
select job_id from employees 
where hire_date between to_date('1990-01-01','YYYY-MM-DD') and to_date('1990-06-30','YYYY-MM-DD')
and job_id in(
select job_id from employees 
where hire_date between to_date('1991-01-01','YYYY-MM-DD') and to_date('1991-06-30','YYYY-MM-DD')
)


--23. Write a compound query to produce a list of employees showing raise percentages, employee IDs, 
--and old salary and new salary increase. Employees in departments 10, 50, and 110 are
--given a 5% raise, employees in department 60 are given a 10% raise, employees in
--departments 20 and 80 are given a  15% raise, and employees in department 90 are not givena raise.
select 
case 
  when department_id in(10,50,110) then '5% raise'
  when department_id in(60) then '10% raise'
  when department_id in(20,80) then '15% raise'
  when department_id in(90) then 'no raise'
   else 'others'
end "RAISE",
employee_id,salary,
case 
  when department_id in(10,50,110) then 0.05*salary
  when department_id in(60) then 0.1*salary
  when department_id in(20,80) then 0.15*salary
  when department_id in(90) then 0
   else salary
end "NEW_SALARY"
from employees;

答案：

SELECT '05% raise' raise, employee_id, salary, 
salary *.05 new_salary
FROM   employees
WHERE  department_id IN (10,50, 110)
UNION
SELECT '10% raise', employee_id, salary, salary * .10
FROM   employees
WHERE  department_id = 60
UNION
SELECT '15% raise', employee_id, salary, salary * .15 
FROM   employees
WHERE  department_id IN (20, 80)
UNION
SELECT 'no raise', employee_id, salary, salary
FROM   employees
WHERE  department_id = 90;

--25. a. Write queries to display the time zone offsets (TZ_OFFSET) for the following time zones.
-- b. Alter the session to set the TIME_ZONE parameter value to the time zone offset ofAustralia/Sydney.
  --   c. Display the SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, andLOCALTIMESTAMP for this session. 
--   Note: The output might be different based on the date when the command is executed.
-- d. Alter the session to set the TIME_ZONE parameter value to the time zone offset ofChile/Easter Island.
  --   e. Display the SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, andLOCALTIMESTAMP for this session. 
  --f. Alter the session to set the NLS_DATE_FORMAT to  DD-MON-YYYY.

SELECT TZ_OFFSET ('Australia/Sydney') from dual; 


SELECT TZ_OFFSET ('Chile/EasterIsland') from dual;


ALTER SESSION SET TIME_ZONE = '+10:00';


SELECT SYSDATE,CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; 


ALTER SESSION SET TIME_ZONE = '-06:00';


SELECT SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

--26. Write a query to display the last names, month of the date of join, and hire date of those  
-- employees who have joined in the month of January, irrespective of the year of join.
select last_name,EXTRACT(month FROM hire_date) ,hire_date from employees where EXTRACT(month FROM hire_date) =1

--27. Write a query to display the following for those departments whose department ID is greater  than 80:
--The total salary for every job within a department 

一个部门的每个工作的所有薪水
--The total salary  

全部薪水总和
--The total salary for those cities in which the departments are located

每个部门所在城市的薪水的总和
--The total salary for every job, irrespective of the department 

每个工作的薪水总和，不考虑部门
--The total salary for every department irrespective of the city

每个部门的薪水总和，不考虑城市
--The total salary of the cities in which the departments are located

每个部门所在的城市的薪水的总和
--Total salary for the departments, irrespective of  job titles and cities 

每个部门的薪水总和，不考虑工资和城市


SELECT   l.city,d.department_name, e.job_id, SUM(e.salary)
FROM     locations l,employees e,departments d
WHERE    d.location_id = l.location_id
AND      e.department_id = d.department_id
AND      e.department_id > 80
GROUP    BY CUBE( l.city,d.department_name, e.job_id);



--28. Write a query to display the following groupings:
--Department ID, Job ID

部门id，工作id
--Job ID, Manager ID 

工作id，经理的id，

--    The query should calculate the maximum and minimum salaries for each of these groups.

此查询必须计算这些组的最大和最小工资。

select Department_id, Job_id,Manager_ID,max(salary),min(salary)
FROM employees 
GROUP BY GROUPING SETS
((department_id,job_id), (job_id,manager_id))

--31. Write a query to delete the oldest JOB_HISTORY row of  an employee by looking up theJOB_HISTORY table 
--for the MIN(START_DATE) for the employee. Delete the records of only those employees who have changed at least two jobs. 
 --If your query executes correctly, you will get the feedback:
--Hint: Use a correlated DELETE command.


DELETE FROM job_history JH
 WHERE employee_id =
      (SELECT employee_id
         FROM employees E
         WHERE JH.employee_id = E.employee_id
   AND start_date =
(SELECT MIN(start_date)
      FROM job_history JH
       WHERE JH.employee_id = E.employee_id)
        AND 3 > (SELECT COUNT(*)
                  FROM job_history JH
                  WHERE JH.employee_id = E.employee_id
                  GROUP BY employee_id

                  HAVING COUNT(*) >= 2
));



--33. Write a query to display the job IDs of those jobs whose maximum salary is above half the
--maximum salary in the whole company. Use the WITH clause to write this query. Name thequery MAX_SAL_CALC.
with  MAX_SAL_CALC as (select distinct job_id  from employees where salary>
(select max(salary)/2 from employees))
select e.job_id,max(e.salary) from employees e ,MAX_SAL_CALC where e.job_id=MAX_SAL_CALC.job_id
group by e.job_id

--34. Write a SQL statement to display employee number, last name, start date, and salary,showing:
 -- a. De Haan’s direct reports
 -- b. The organization tree under De Haan (employee number 102)
SELECT employee_id, last_name, hire_date, salary
FROM   employees
WHERE  manager_id = (SELECT employee_id
           FROM   employees
          WHERE last_name = 'De Haan');

SELECT employee_id, last_name, hire_date, salary
FROM   employees
WHERE  employee_id != 102
CONNECT BY manager_id = PRIOR employee_id
START WITH employee_id = 102;

--35. Write a hierarchical query to display the employee number, manager number, and employee  
--last name for all employees who are two levels below employee De Haan (employeenumber 102). 
--Also display the level of the employee.

SELECT employee_id, manager_id, level, last_name
FROM   employees
WHERE LEVEL = 3
CONNECT BY manager_id = PRIOR employee_id
START WITH employee_id= 102;

--36. Produce a hierarchical report to display the employee number, manager number, the LEVEL
--  pseudocolumn, and employee last name. For every row in the EMPLOYEES table, you
  -- should print a tree structure showing the employee, the employee’s manager, then the
  -- manager’s manager, and so on. Use indentations for the NAME column.
COLUMN name FORMAT A25
SELECT  employee_id, manager_id, LEVEL,
LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_')  LAST_NAME        
FROM    employees
CONNECT BY employee_id = PRIOR manager_id;
COLUMN name CLEAR

-- 41.Write a SQL script file to drop all objects (tables, views, indexes, sequences,synonyms, and  soon) that you own.

Note: The output shown is only a guideline.

     SELECT   'DROP ' || object_type || ' ' || object_name || ';'
     FROM     user_objects
     ORDER BY object_type
     /

     SET HEADING ON ECHO ON FEEDBACK ON 
     SET PAGESIZE 24     

20、with 语句使用

很多时候，我们都希望能够重用一些已经获得的结果集。即使有内嵌视图这个灵活的工具，SQL语句有时也会显得非常繁琐。

【示例18-3】 对于表salary来说，其中存储了员工领取过的工资信息，那么我们可以利用如下SQL语句来获得员工的平均工资水平。

1. SQL> select employee_id, avg(salary) avg_salary from salary  
2.   2  group by employee_id  
3.   3  /  
4.  
5. EMPLOYEE_ID   AVG_SALARY  
6. -----------  ------------  
7.      1             8500  
8.      2             6800  
9.      3             4000  
10.      4             4000  
11.      5             4500 

在该示例中，我们将工资信息按照employee_id进行分组，并获得每个分组的平均值，从而获得每位员工的平均工资。

那么，为了获得所有员工的平均工资可以利用如下SQL语句。

1. SQL> select avg(avg_salary) from (  
2.   2  select employee_id, avg(salary) avg_salary from salary  
3.   3  group by employee_id)  
4.   4  /  
5.  
6. AVG(AVG_SALARY)  
7. ----------------------------  
8.       5560 

在该SQL语句中，仍然使用到了单个员工平均工资这一信息。如果需求变更为获得工资大于平均工资的员工和工资信息，那么可以利用如下SQL语句：

1. SQL> select * from (  
2.   2  select employee_id, avg(salary) avg_salary from salary  
3.   3  group by employee_id  
4.   4  ) t  
5.   5  where t.avg_salary >(  
6.   6  select avg(avg_salary) from (  
7.   7  select employee_id, avg(salary) avg_salary from salary  
8.   8  group by employee_id));  
9.  
10. EMPLOYEE_ID   AVG_SALARY  
11. -----------  -------------  
12.      1             8500  
13.      2             6800 

很明显，子查询select employee_id, avg(salary) avg_salary from salary group by employee_id被重复执行。通过with子句，可以将该子查询独立出来，并重用其查询结果，如下所示。

1. SQL> with employee_avg_salary as (  
2.   2  select employee_id, avg(salary) avg_salary from salary  
3.   3  group by employee_id)  
4.   4  select * from employee_avg_salary t  
5.   5  where t.avg_salary>(select avg(avg_salary)
   from employee_avg_salary)  
6.   6  /  
7.  
8. EMPLOYEE_ID   AVG_SALARY  
9. -----------  ------------  
10.      1             8500  
11.      2             6800 

with employee_avg_salary as (select employee_id, avg(salary) avg_salary from salary group by employee_id)将子查询select employee_id, avg(salary) avg_salary from salary group by employee_id的查询结果作为临时变量存储起来；在其后的查询中，可以直接使用employee_avg_salary，如同employee_avg_salary是一个真实存在的数据表一样。