数据库

sql 多表查询语句练习

位置:首页 > 数据库 > SQL Server,2017-10-06 17:41
显示所有员工的姓名,部门号和部门名称。select e.last_name,d.department_id,d.department_id from emplo
  1. 显示所有员工的姓名,部门号和部门名称。
select e.last_name,d.department_id,d.department_id from employees e,departments d where e.department_id=d.department_id;
  1. 查询90号部门员工的job_id和90号部门的location_id
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;
  1. 选择在Toronto工作的员工的last_name , job_id , department_id , department_name
select e.last_name , e.job_id , d.department_id , d.department_name from employees e,departments d,locations l where e.department_id=d.department_id and d.location_id=l.location_id and l.city='Toronto';
  1. 选择所有员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
select e.last_name "employees",e.employee_id "Emp#",d.last_name "manager",d.manager_id "Mgr#" from employees e,employees d where e.manager_id=d.employee_id(+);
  1. 查询各部门员工姓名和他们的同事姓名,结果类似于下面的格式
Department_id Last_name colleague
20 fay hartstein
select e.department_id "Department_id",e.last_name "Last_name",c.last_name "colleague" from employees e join employees c on(e. department_id =c. department_id) where e.employee_id<>c.employee_id;

TAGS:sql 多表查询

猜你喜欢

NewHot