【系统设计与数据库系统】Basic SQL command LAB

github项目地址

Basic SQL command LAB

Note: create a Table EMP_your_StudentID and DEPT_your_studentID with all required constraints both Integrity and Referential Integrity Constraint.

EMP Table:

DEPT TABLE:

INSERT TUPLES FOR EMP TABLE:

INSERT TUPLES FOR DEPT TABLE:

实验过程及内容：

0. Create a database

Create two tables

Insert data

1. List all information about the employees.

--question1
SELECT
	* 
FROM
	emp;

2. List all information about the departments

--question2
SELECT
	* 
FROM
	dept

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

--question3
SELECT
	ENAME,
	EMPNO,
	SAL,
	DEPTNO
FROM
	emp;

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

-- question4
SELECT
	* 
FROM
	emp 
WHERE
	`DEPTNO` = 10 
	OR `DEPTNO` = 30;

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

-- question5
SELECT DISTINCT
	JOB 
FROM
	emp

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

-- question6
SELECT
	emp.ENAME 
FROM
	emp 
WHERE
	emp.SAL < 20000

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

-- question7
SELECT
 emp.ENAME, emp.JOB, emp.EMPNO 
FROM
 emp
WHERE
 emp.DEPTNO = 20
 AND emp.SAL > 25000;

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

-- question8
SELECT
	* 
FROM
	emp 
WHERE
	emp.JOB = 'CLERK' 
	OR emp.JOB = 'SALESMAN';

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

-- question9
SELECT
	* 
FROM
	emp 
WHERE
	emp.JOB = 'CLERK' 
	AND emp.DEPTNO != 10;

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

-- question10
SELECT
	* 
FROM
	emp 
WHERE
	emp.JOB = 'SALESMAN' 
	OR ( emp.JOB = 'ANALYST' AND emp.DEPTNO = 20 );

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

-- question11
SELECT
	emp.ENAME,
	emp.DEPTNO,
	emp.SAL 
FROM
	emp 
WHERE
	emp.SAL BETWEEN 15000 
	AND 20000;

12. Find the name of the President.

-- question12
SELECT
	emp.ENAME 
FROM
	emp 
WHERE
	emp.JOB = 'PRESIDENT';

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

-- question13
SELECT
	* 
FROM
	emp 
WHERE
	emp.ENAME LIKE '%S';

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

-- question14
SELECT
	* 
FROM
	emp 
WHERE
	emp.ENAME LIKE '%TH%' 
	OR emp.ENAME LIKE '%LL%';

15. List only those employees who receive commission.

-- question15
SELECT
	* 
FROM
	emp 
WHERE
	emp.COMM IS NOT NULL 
	AND emp.COMM != 0;

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

-- question16
SELECT
	emp.ENAME,
	emp.JOB,
	emp.SAL,
	emp.HIREDATE,
	emp.DEPTNO 
FROM
	emp 
ORDER BY
	emp.ENAME ASC;

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

-- question17
SELECT
	emp.ENAME,
	emp.JOB,
	emp.SAL,
	emp.HIREDATE,
	emp.DEPTNO 
FROM
	emp 
ORDER BY
	emp.SAL ASC;

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

-- question18
SELECT
	* 
FROM
	emp 
WHERE
	emp.JOB = 'SALESMAN' 
ORDER BY
	emp.COMM / emp.SAL DESC;

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

-- question19
SELECT
	* 
FROM
	emp 
WHERE
	emp.DEPTNO = 30 
	AND emp.COMM IS NOT NULL 
	AND emp.COMM != 0 
ORDER BY
	emp.COMM ASC;

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

-- question20
SELECT
	emp.ENAME,
	emp.JOB,
	emp.SAL,
	emp.COMM 
FROM
	emp 
WHERE
	emp.MGR NOT IN ( SELECT emp.EMPNO FROM emp WHERE emp.JOB = 'MANAGER' );


-- question20
SELECT
	e1.ENAME,
	e1.JOB,
	e1.SAL,
	e1.COMM
FROM
	emp as e1,emp as e2
WHERE
	e1.MGR=e2.EMPNO and e2.JOB!='MANAGER';

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

-- question21
SELECT
	* 
FROM
	emp 
WHERE
	emp.DEPTNO = 30 
	AND emp.SAL >= 18000;

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

-- question22
SELECT
	* 
FROM
	emp 
WHERE
	emp.HIREDATE < '1998-1-1' 
	AND ( emp.SAL > 5000 OR emp.SAL < 1000 );

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

-- question23
ALTER TABLE emp ADD PRIMARY KEY ( EMPNO );

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

-- question24
ALTER TABLE emp ADD family_name VARCHAR ( 10 );

25. How to drop primary key constraint for EMPNO

-- question25
ALTER TABLE emp DROP PRIMARY KEY ;

26. rename EMP table to EMPLOYEE

-- question26
RENAME TABLE emp TO EMPLOYEE;

27. rename EMPLOYEE back to EMP

-- question27
RENAME TABLE EMPLOYEE TO emp;

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

-- question28
ALTER TABLE emp DROP emp.family_name;

29. What is the SQL command to copy emp table to employee table

-- question29
CREATE TABLE employee AS SELECT * FROM emp;

30. What is the SQL command to drop employee table

-- question30
DROP TABLE employee;

31. What is the SQL command to display name’s of employee entered interactively from user

-- question31
SELECT
	* 
FROM
	emp 
WHERE
	emp.ENAME LIKE '%用户输入字符串%';

32. What is the SQL command to find the employee whose commission is NULL

-- question32
SELECT
	* 
FROM
	emp
WHERE
	emp.COMM IS NULL;