1,现有一个公司的部门及雇员的数据库,由下面三个表组成:
a 雇员(雇员号,姓名,年龄,地址,薪水)
b 部门(部门号,部门名,部门经理号)
c 工作(雇员号,部门号,工作年限)
用PL/SQL语句查询和其部门经理住址相同的员工的姓名及经理姓名?
[color=blue]雇员
create table employee(
empid char(3) primary key,
empname varchar2(10) not null,--雇员姓名不能为空
age number(2) check (age between 18 and 55),--雇员的年龄介于18到55之间
address varchar2(20),
sal number(5) check (sal between 1000 and 50000) --薪水取1000到50000之间整数
);
--部门
create table dep(
deptno char(3) primary key ,
dname varchar2(20) not null,--部门名不能为空
mgr char(3) references employee(empid)
);
--工作
create table work(
empid char(3) references employee(empid),
deptno char(3) references dep(deptno),
worktime number(1),
primary key (empid,deptno)
);[/color]
[color=red]select d.empname,c.leader from (select deptno,empname as leader,address from employee a,dep b
where a.empid = b.mgr) c,employee d,work e
where c.deptno = e.deptno and e.empid = d.empid
and d.address = c.address and c.leader <> d.empname;[/color]
2,现有关系数据表,其中城市CITIES(ID,NAME,CUNTORY),地区AREAS(ID,NAME,CITY_ID),订单ORDERS(ID,DATE,CUSTOMER_ID,USER_ID,AMOUNT,QUANTITY,AREAS) ,用户USERS(ID,NAME,SEX,ADDRESS,TELEPHONE)请编写一个视图数据为,汇总每个业务,每个地区和每个城市每个月的订单数、订单金额
a 雇员(雇员号,姓名,年龄,地址,薪水)
b 部门(部门号,部门名,部门经理号)
c 工作(雇员号,部门号,工作年限)
用PL/SQL语句查询和其部门经理住址相同的员工的姓名及经理姓名?
[color=blue]雇员
create table employee(
empid char(3) primary key,
empname varchar2(10) not null,--雇员姓名不能为空
age number(2) check (age between 18 and 55),--雇员的年龄介于18到55之间
address varchar2(20),
sal number(5) check (sal between 1000 and 50000) --薪水取1000到50000之间整数
);
--部门
create table dep(
deptno char(3) primary key ,
dname varchar2(20) not null,--部门名不能为空
mgr char(3) references employee(empid)
);
--工作
create table work(
empid char(3) references employee(empid),
deptno char(3) references dep(deptno),
worktime number(1),
primary key (empid,deptno)
);[/color]
[color=red]select d.empname,c.leader from (select deptno,empname as leader,address from employee a,dep b
where a.empid = b.mgr) c,employee d,work e
where c.deptno = e.deptno and e.empid = d.empid
and d.address = c.address and c.leader <> d.empname;[/color]
2,现有关系数据表,其中城市CITIES(ID,NAME,CUNTORY),地区AREAS(ID,NAME,CITY_ID),订单ORDERS(ID,DATE,CUSTOMER_ID,USER_ID,AMOUNT,QUANTITY,AREAS) ,用户USERS(ID,NAME,SEX,ADDRESS,TELEPHONE)请编写一个视图数据为,汇总每个业务,每个地区和每个城市每个月的订单数、订单金额