当前位置:Gxlcms > 数据库问题 > mysql练习02

mysql练习02

时间:2021-07-01 10:21:17 帮助过:23人阅读

empno,职工名ename,部门号deptno,工资sal)和部门表(部门号deptno,部门名dname,主任manager),用SQL语句完成下列要求:

create table emp (

empno int(10) primary key,

ename varchar(20),

deptno int(10),

sal decimal(10,2)

);

 

create table department (

deptno int(10) primary key,

dname varchar(20),

manager varchar(20)

);

 

 

根据题目的关键字自行构建测试数据(比较重要)

 

  1)向职工表中插入行(‘025’,‘王芳’,‘03’,1000

insert into emp values(‘025‘, ‘王芳‘, ‘03‘, ‘1000‘);

  2)从职工表中删除人事处的所有员工

delete from emp where deptno = (select deptno from department where dname = ‘人事处‘);

  3)将职工号为‘001’的员工工资改为700元钱

update emp set sal = ‘700‘ where empno = ‘001‘;

  4)查询人事处的员工最高工资

select max(sal) from emp where deptno = (select deptno from department where dname = ‘人事处‘);

  5)查询“王芳”所在部门的部门主任

select manager from department where deptno = (select deptno from emp where ename = ‘王芳‘);

6)查询与“王芳”在同一部门的其它员工信息

select * from emp where deptno = (select deptno from emp where ename = ‘王芳‘) and ename not like ‘王芳‘;

  7)建立公司所有部门的公共视图——部门职工视图

create view dept_sta_view(empno, ename, deptno, sal, dname, mgr) as select a.empno, a.ename, a.deptno, a.sal, b.dname, b.manager from emp a, department b where a.deptno = b.deptno;

  8)从部门职工视图中查询财务处员工工资高于800元的员工信息

select * from dept_sta_view where sal > ‘800‘ and dname = ‘财务处‘;

 

mysql练习02

标签:art   dna   视图   style   view   span   系统   table   max   

人气教程排行