MySQL Exercise 02

Staff system:

has a relationship with the staff table (employee number empno, employee name ename, Department number deptno, payroll Sal ) and departmental Table (department number deptno, department name dname, director manager), with SQL statement to complete the following requirements:

CREATE TABLE EMP (

Empno Int (Ten) primary key,

ename varchar (20),

Deptno Int (10),

Sal Decimal (10,2)

);

CREATE TABLE Department (

Deptno Int (Ten) primary key,

Dname varchar (20),

Manager varchar (20)

);

build test data yourself based on the keywords of the topic (more important)

(1) inserting rows into the staff table ('025', ' Wang Fang', ' the ', ')

INSERT into EMP values (' 025 ', ' Wang Fang ', ' N ', ' + ');

(2) Remove all personnel from the staff table

Delete from emp where Deptno = (select Deptno from department where dname = ' personnel office ');

(3)Change employee's salary to "001" to $

Update emp Set sal = ' empno ' where = ' 001 ';

(4) to inquire the personnel department's staff maximum salary

Select Max (SAL) from emp where Deptno = (select Deptno from department where dname = ' personnel office ');

(5) Find the department director of "Wang Fang" department

Select Manager from department where Deptno = (select Deptno from emp where ename = ' Wang Fang ');

(6) inquiries with "Wang Fang" in the same department of other staff information

SELECT * from emp where deptno = (select Deptno from emp where ename = ' Wang Fang ') and ename not to like ' Wang Fang ';

(7) Establish a public view of all departments of the company--view of departmental staff

Create View Dept_sta_view (Empno, ename, Deptno, Sal, Dname, Mgr) as select A.empno, A.ename, A.deptno, A.sal, B.dname, B.M Anager from EMP A, Department b where a.deptno = B.deptno;

(8) from the departmental staff view of the financial department staff wages higher than The staff information

SELECT * from Dept_sta_view where Sal > ' "and dname = ' Finance office ';

MySQL Exercise 02