数据库：SQL的DDL语言和单表查询

数据库系统实验：$SQL$的$DDL$语言和单表查询

创建一个表$EMP$和$DEPT$，其中包含所有必需的约束(完整性和引用完整性约束)。

CREATE TABLE `DEPT` (
    `DEPTNO` SMALLINT PRIMARY KEY NOT NULL,
    `DNAME` CHAR(10),
    `LOC` CHAR(10)
) ENGINE = INNODB;

CREATE TABLE `datebase`.`EMP` (
    `EMPNO` INT NOT NULL PRIMARY KEY,
    `ENAME` VARCHAR(10) NOT NULL,
    `JOB` VARCHAR(9) NOT NULL,
    `MGR` INT,
    `HIREDATE` DATE,
    `SAL` DECIMAL(7, 2) NOT NULL CHACK(SAL > 5000),
    `COMM` DECIMAL(7, 2),
    `DEPTNO` SMALLINT DEFAULT 10,
    FOREIGN KEY (`MGR`) REFERENCES `datebase`.`EMP`(`EMPNO`),
    FOREIGN KEY (`DEPTNO`) REFERENCES `DEPT`(`DEPTNO`)
) ENGINE = InnoDB;

这里提供另外一种给数据加限制的方式：

DELIMITER //
CREATE TRIGGER check_sal
BEFORE INSERT ON `EMP`
FOR EACH ROW
BEGIN
    IF NEW.SAL <= 5000 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'SAL must be more than 5000';
    END IF;
END//
DELIMITER ;

由于$EMP$表中包含数据需要参考$DEPT$的数据，故应先添加DEPT中的信息

添加$DEPT$中的信息

INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES
(10, 'ACCOUNTING', 'LONDON'),
(30, 'SALES', 'LIVERPOOL'),
(40, 'OPERATIONS', 'STAFFORD'),
(50, 'MARKETING', 'LUTON'),
(20, 'RESEARCH', 'PRESTON');

由于$MGR$需要参考$EMPNO$数据，故先不添加$MGR$

添加除$MGR$的其他数据

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7369, 'SMITH', 'CLERK', NULL, '1990-12-17', 13750, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', NULL, '1989-02-20', 19000, 6400, 30),
(7521, 'WARD', 'SALESMAN', NULL, '1993-02-22', 18500, 4250, 30),
(7566, 'JONES', 'MANAGER', NULL, '1989-04-02', 26850, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', NULL, '1997-09-28', 15675, 3500, 30),
(7698, 'BLAKE', 'MANAGER', NULL, '1990-05-01', 24000, NULL, 30),
(7782, 'CLARK', 'MANAGER', NULL, '1988-06-09', 27500, NULL, 10),
(7788, 'SCOTT', 'ANALYST', NULL, '1987-04-19', 19500, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1983-11-17', 82500, NULL, 10),
(7844, 'TURNER', 'SALESMAN', NULL, '1992-09-08', 18500, 6250, 30),
(7876, 'ADAMS', 'CLERK', NULL, '1996-05-23', 11900, NULL, 20),
(7900, 'JAMES', 'CLERK', NULL, '1995-12-03', 12500, NULL, 30),
(7902, 'FORD', 'ANALYST', NULL, '1991-12-03', 21500, NULL, 20),
(7934, 'MILLER', 'CLERK', NULL, '1995-01-23', 13250, NULL, 10),
(3258, 'GREEN', 'SALESMAN', NULL, '1995-07-24', 18500, 2750, 50),
(4422, 'STEVENS', 'MANAGER', NULL, '1994-01-14', 24750, NULL, 50),
(6548, 'BARNES', 'CLERK', NULL, '1995-01-16', 11950, NULL, 50),
(7500, 'CAMPBELL', 'ANALYST', NULL, '1992-10-30', 24500, 0, 40);

更新$MGR$的信息

UPDATE EMP SET MGR = 7902 WHERE EMPNO = 7369;
UPDATE EMP SET MGR = 7698 WHERE EMPNO = 7499;
UPDATE EMP SET MGR = 7698 WHERE EMPNO = 7521;
UPDATE EMP SET MGR = 7839 WHERE EMPNO = 7566;
UPDATE EMP SET MGR = 7698 WHERE EMPNO = 7654;
UPDATE EMP SET MGR = 7839 WHERE EMPNO = 7698;
UPDATE EMP SET MGR = 7839 WHERE EMPNO = 7782;
UPDATE EMP SET MGR = 7566 WHERE EMPNO = 7788;
UPDATE EMP SET MGR = NULL WHERE EMPNO = 7839;
UPDATE EMP SET MGR = 7698 WHERE EMPNO = 7844;
UPDATE EMP SET MGR = 7788 WHERE EMPNO = 7876;
UPDATE EMP SET MGR = 7698 WHERE EMPNO = 7900;
UPDATE EMP SET MGR = 7566 WHERE EMPNO = 7902;
UPDATE EMP SET MGR = 7782 WHERE EMPNO = 7934;
UPDATE EMP SET MGR = 4422 WHERE EMPNO = 3258;
UPDATE EMP SET MGR = 7839 WHERE EMPNO = 4422;
UPDATE EMP SET MGR = 4422 WHERE EMPNO = 6548;
UPDATE EMP SET MGR = 7566 WHERE EMPNO = 7500;

1、List all information about the employees.

SELECT * FROM EMP;

2、List all information about the departments.

SELECT * FROM DEPT;

3、List only the following information from the EMP table ( Employee name, employee number, salary, department number).

SELECT ENAME, EMPNO, SAL, DEPTNO FROM EMP;

4、List details of employees in departments 10 and 30.

SELECT * FROM EMP WHERE DEPTNO IN (10, 30);

5、List all the jobs in the EMP table eliminating duplicates.

SELECT DISTINCT JOB FROM EMP;

6、What are the names of the employees who earn less than £20,000?

SELECT ENAME FROM EMP WHERE SAL < 20000;

7、What is the name, job title and employee number of the person in department 20 who earns more than £25000?

SELECT ENAME, JOB, EMPNO 
FROM EMP 
WHERE DEPTNO = 20 
AND SAL > 25000;

8、Find all employees whose job is either Clerk or Salesman.

SELECT * 
FROM EMP 
WHERE JOB IN ('CLERK', 'SALESMAN');

9、Find any Clerk who is not in department 10.

SELECT * 
FROM EMP 
WHERE JOB = 'CLERK' AND DEPTNO != 10;

10、Find everyone whose job is Salesman and all the Analysts in department 20.

SELECT * 
FROM EMP 
WHERE JOB = 'SALESMAN' OR (JOB = 'ANALYST' AND DEPTNO = 20);

11、Find all the employees who earn between £15,000 and £20,000. Show the employee name, department and salary.

SELECT ENAME, DEPTNO, SAL 
FROM EMP 
WHERE SAL BETWEEN 15000 AND 20000;

12、Find the name of the President.

SELECT ENAME 
FROM EMP 
WHERE JOB = 'PRESIDENT';

13、Find all the employees whose last names end with S.

SELECT * 
FROM EMP 
WHERE ENAME LIKE '%S';

14、List the employees whose names have TH or LL in them.

SELECT * 
FROM EMP 
WHERE ENAME LIKE '%TH%' OR ENAME LIKE '%LL%';

15、List only those employees who receive commission.

SELECT * 
FROM EMP 
WHERE COMM IS NOT NULL AND COMM != 0;

16、Find the name, job, salary, hiredate, and department number of all employees by alphabetical order of name.

SELECT ENAME, JOB, SAL, HIREDATE, DEPTNO 
FROM EMP 
ORDER BY ENAME;

17、Find the name, job, salary, hiredate and department number of all employees in ascending order by their salaries.

SELECT ENAME, JOB, SAL, HIREDATE, DEPTNO 
FROM EMP 
ORDER BY SAL ASC;

18、List all salesmen in descending order by commission divided by their salary.

SELECT ENAME, COMM, SAL, (COMM / SAL) AS COMM_SAL_RATIO
FROM EMP 
WHERE JOB = 'SALESMAN' AND COMM IS NOT NULL AND SAL != 0
ORDER BY COMM / SAL DESC;

19、Order employees in department 30 who receive commision, in ascending order by commission.

SELECT * 
FROM EMP 
WHERE DEPTNO = 30 AND COMM IS NOT NULL 
ORDER BY COMM ASC;

20、Find the names, jobs, salaries and commissions of all employees who do not have managers.

SELECT ENAME, JOB, SAL, COMM 
FROM EMP 
WHERE MGR IS NULL;

21、Find all the salesmen in department 30 who have a salary greater than or equal to £18000.

SELECT * 
FROM EMP 
WHERE DEPTNO = 30 AND JOB = 'SALESMAN' AND SAL >= 18000;

22、Find the employees who were hired before 01-Jan-1998 and have salary above 5000 or below 1000.

SELECT * 
FROM EMP 
WHERE HIREDATE < '1998-01-01' AND (SAL > 5000 OR SAL < 1000);

23、What is the command to add primary key constraint to EMPNO.

ALTER TABLE EMP
ADD CONSTRAINT constraint_name PRIMARY KEY (EMPNO);

24、What is the command to add a new column EMP_family_name to existing EMP table?

ALTER TABLE EMP
ADD EMP_family_name VARCHAR(255);

25、How to drop primary key constraint for EMPNO?

ALTER TABLE EMP DROP CONSTRAINT constraint_name;

26、rename EMP table to EMPLOYEE.

ALTER TABLE EMP RENAME TO EMPLOYEE;

27、rename EMPLOYEE back to EMP.

ALTER TABLE EMPLOYEE RENAME TO EMP;

28、What is the SQL command to remove column EMP_family_name from EMP table?

ALTER TABLE EMP DROP COLUMN EMP_family_name;

29、What is the SQL command to copy emp table to employee table?

CREATE TABLE EMPLOYEE AS
SELECT * FROM EMP;

30、What is the SQL command to drop employee table?

DROP TABLE EMPLOYEE;

31、What is the SQL command to find the employee whose commission is NULL?

SELECT * 
FROM EMP 
WHERE COMM IS NULL;