【数据库练习】2.多表查询

-- 1查询员⼯的姓名及其所在部⻔的名字和城市
select e.ename,d.dname,d.loc from dept d,emp e where d.deptno=e.deptno;

-- 2. 查询员⼯的姓名和他的管理者的姓名
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

-- 3.查询员⼯的编号、姓名、部⻔编码、部⻔名称以及部⻔所在城市。要求：把没有部⻔的员⼯也查出来
select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e left outer join dept d on d.deptno = e.deptno;

-- 4.查询员⼯的信息及其所在部⻔的信息。要求：把没有员⼯的部⻔也查出来
select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e right outer join dept d on d.deptno = e.deptno;

-- 5.查询员⼯的信息及其所在部⻔的信息。要求：只查询没有员⼯的部⻔
select d.deptno,d.dname,d.loc from emp e right outer join dept d on e.deptno = d.deptno where ename is null;

-- 6. 查询并显示SALES部⻔的职位
select DISTINCT e.job from emp e join dept d on d.deptno = e.deptno where d.dname = 'sales';

-- 7. 查询所有部⻔的名称、所在地、员⼯数量以及平均⼯资
select d.dname,d.loc,count(e.deptno),avg(e.sal) from emp e join dept d on d.deptno = e.deptno group by d.dname;

-- 8. 执⾏下⾯两条查询语句，并⽐较查询结果
select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;
select e1.ename, e2.ename from emp e1, emp e2 where e1.empno = e2.mgr;

-- 9.假设员⼯表中，员⼯和管理者中间只有⼀个层级，也就是说，每个员⼯最多只有⼀个上级，作为管理
-- 者的员⼯不再有上级管理者，并且，上级管理者相同的员⼯，他们属于同⼀个部⻔。找出EMP中那些
-- ⼯资⾼于他们所在部⻔的管理者⼯资的员⼯。
select e1.ename from emp e1 , emp e2 where e1.sal>e2.sal and e1.mgr=e2.empno ;

-- 10.

create table userlist(
  telephone varchar(10) primary key ,
  account varchar(10),
  rent numeric(10,2)
);
create table chaege(
                        telephone varchar(10) primary key ,
                        fee01 double,
                        fee02 double,
                        fee03 double,
                        fee04 double
);
insert into userlist values (4210001,'AAAA',19.50),
                            (4210002,'AAAA',20.50),
                            (4210003,'BBBB',100.00),
                            (4210004,'CCCC',250.00);
insert into chaege values (4210001,11.00,12.00,13.00,14.00),
                          (4210002,21.00,22.00,23.00,24.00),
                          (4210003,31.00,32.00,33.00,34.00);
select account account,count(account)users,sum(rent)rent,sum(fee01)FEE01,sum(fee02)FEE02,sum(fee03)FEE03,sum(fee04)FEE04
FROM userlist u1 left join chaege c
on u1.telephone=c.telephone
group by account;

-- 11

 

 1.select stu.sno,stu.sname
from student stu,sc s,course c
where s.sno=stu.sno and s.cno=c.cno and c.cname='计算机原理'
-- 2.
select c.cname from student stu join sc s on s.sno=stu.sno,course c join cs s on c.cno = s.sno
where s.sname='周星驰'

-- 12. 

 

select t1.id,t1.name from test t1,test t2 where t2.id=t1.manager and t1.age>t2.age

 

-- 13. select c.cityNo,c.cityname,c.stateno,s.statename from city c join state s on c.stateno = s.stateno order by c.cityno asc;