The following is a description of the structure of the Employee Information Sheet (EMP) :

The field names Field to explain The field type The length of the field The constraint
empno Employee number character 8 PK
ename Employee name character 50 NOT NULL
job work character 20 NOT NULL
sal wage The numerical 6 NOT NULL
deptno Unit no. character 4 NOT NULL

The structure of the dept table is as follows:

The field names Field to explain The field type The length of the field The constraint
deptno Unit no. character 4 PK
dname Department of character 50 NOT NULL

1. Write the construction sentences of the EMP table according to the content in the table above.

CREATE TABLE emp (
    empno varchar(8) PRIMARY KEY COMMENT 'Employee No.',
    ename varchar(50) NOT NULL COMMENT 'Name of Employee',
    job varchar(20) NOT NULL COMMENT 'work',
    sal int(6) NOT NULL COMMENT 'wages',
    deptno varchar(4) NOT NULL COMMENT 'Department Number'
) COMMENT = 'Staff Information Sheet';
Copy the code

2. List the lowest and highest salaries of employees whose jobs are Engineer in each department in the EMP table. Write the corresponding SQL statements.

SELECT
  deptno, MIN(sal), MAX(sal)
FROM
  emp
WHERE
  job = 'Engineer'
GROUP BY deptno;
Copy the code

3. For the department whose minimum wage is less than 1000 in emP, list the department name, minimum wage, and maximum wage of the Engineer employee, and write the corresponding SQL statement.

SELECT
  d.dname, MIN(e.sal), MAX(e.sal)
FROM
  emp e, dept d
WHERE
  e.deptno = d.deptno
  AND e.deptno IN (
    SELECT
      deptno
    FROM
      emp
    GROUP BY deptno
    HAVING MIN(sal) < = 1000
  )
  AND e.job = 'Engineer'
GROUP BY e.deptno;
Copy the code

Friendship tips: the topic comes from each real enterprise, the above answers are for reference only, can not determine whether to meet the topic to examine the knowledge point!