多表查询练习

Posted by
  1. 显示所有员工的姓名,部门号和部门名称。

select * from regions;
select * from countries;
select * from locations;
select * from departments;
select * from jobs;
select * from employees;
select * from job_history;

  1. 查询工资大于12000的员工姓名和工资
    select ‘姓名:’ || first_name || last_name || ‘,工资:’ || salary
       from employees
      where salary > 12000;

  2. 查询员工号为176的员工的姓名和部门号
    select first_name, department_id from employees where employee_id =
    176;

  3. 选择工资不在5000到12000的员工的姓名和工资
    select first_name, salary from employees where salary not between 5000
    and 12000;

select e.last_name,d.department_id,d.department_id from employees e,departments d where e.department_id=d.department_id;

4.
选择雇用时间在1998-02-01到1998-05-01之间的员工姓名,job_id和雇用时间
select first_name, job_id, hire_date
   from employees
  where hire_date between to_date(‘1998-02-01’, ‘yyyy-mm-dd’) and
        to_date(‘1998-05-01’, ‘yyyy-mm-dd’);

  1. 查询90号部门员工的job_id和90号部门的location_id
  1. 选择在20或50号部门工作的员工姓名和部门号
    select first_name, last_name, department_id
       from employees
      where department_id in (20, 50);

  2. 选择在1994年雇用的员工的姓名和雇用时间
    select first_name, last_name, hire_date
       from employees
      where to_char(hire_date, ‘yyyy’) = 1994;

  3. 选择公司中没有管理者的员工姓名及job_id
    select first_name, last_name, job_id
       from employees
      where manager_id is null;

  4. 选择公司中有奖金的员工姓名,工资和奖金级别
    Select last_name||’ ‘||first_name,salary,commission_pct from
    employees where commission_pct is not null;

  5. 选择员工姓名的第三个字母是a的员工姓名
    select first_name, last_name from employees where first_name like
    ‘__a%’;

  6. 选择姓名中有字母a和e的员工姓名
    select first_name || ‘ ‘ || last_name
       from employees
      where first_name || last_name like ‘%a%e’
         or last_name || first_name like
    ‘%e%a’;–为什么firts_name和last_name要倒置写一次??

  7. 显示系统时间
    select sysdate from dual;

  8. 查询员工号,姓名,工资,以及工资提高百分之20%后的结果(new
    salary)
    select * from employees;
    select employee_id, first_name||’ ‘||last_name, salary, salary * 1.2
    as “new salary” from employees;

  9. 将员工的姓名按首字母排序,并写出姓名的长度(length)
    select initcap(concat(first_name,last_name)) “name”,
            length(concat(first_name, last_name)) “length”
       from employees
      order by substr(concat(first_name, last_name), 1, 1);

  10. 查询各员工的姓名,并显示出各员工在公司工作的月份数
    select initcap(concat(first_name, last_name)) “name”,
            round(months_between(sysdate, hire_date)) “month”
       from employees
      order by substr(concat(first_name, last_name), 1, 1);

15.
查询员工的姓名,以及在公司工作的月份数(worked_month),并按月份数降序排列
select initcap(concat(first_name, last_name)) “name”,
        round(months_between(sysdate, hire_date)) “worked_month”
   from employees
  order by “worked_month”;

select e.job_id,d.location_id from employees e,departments d where e.department_id=d.department_id and d.department_id=90;
  1. 选择所有有奖金的员工的select last_name , department_name ,
    location_id , city

select e.last_name,d.department_name,l.location_id,l.city from employees e,departments d,locations l where e.department_id=d.department_id and d.locatiion_id=l.location_id and e.commission_pct is not null;

相关文章

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注