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

前言

深圳大学《系统设计与数据库系统》第二次实验记录。

一、实验目的与要求：

• Please show all work for these problems.
  Just writing down the answer will not get full credit.

• Answers to the following questions must include:
  1. SQL Query command ( 60 Points)
  2. Screenshot of your SQL command result (30 Points)
     Note: Oral Question in LAB ( 10 points)

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

• EMP Table:

EMPNO	PRIMARY KEY NOT NULL IF PRIMARY KEY MODIFED THEN CHILD MUST BE UPDATED	NUMBER(4)
ENAME	NOT NULL	VARCHAR2(10)
JOB	NOT NULL	VARCHAR2(9)
MGR	REFERENCES EMP(EMPNO)	NUMBER(4)
HIREDATE	NOT NULL CANNOT BE GREATER THAN TODAYS DATE	DATE
SAL	NOT NULL AND MORE THAN 5000	NUMBER(7,2)
COMM		NUMBER(7,2)
DEPTNO	REFERENCES DEPT TABLE DEFAULT 10	NUMBER(2)

• DEPT TABLE:

DEPTNO	PRIMARY KEY NOT NULL DON’T ALLOW PRIMARY KEY TO BE MODIFIED IF CHILD RECORD EXIST	NUMBER(2)
DNAME		CHAR(10)
LOC		CHAR(10)

• INSERT TUPLES FOR EMP TABLE:

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-DEC-90	13750	NULL	20
7499	ALLEN	SALESMAN	7698	20-FEB-89	19000	6400	30
7521	WARD	SALESMAN	7698	22-FEB-93	18500	4250	30
7566	JONES	MANAGER	7839	02-APR-89	26850		20
7654	MARTIN	SALESMAN	7698	28-SEP-97	15675	3500	30
7698	BLAKE	MANAGER	7839	01-MAY-90	24000		30
7782	CLARK	MANAGER	7839	09-JUN-88	27500		10
7788	SCOTT	ANALYST	7566	19-APR-87	19500		20
7839	KING	PRESIDENT		17-NOV-83	82500		10
7844	TURNER	SALESMAN	7698	08-SEP-92	18500	6250	30
7876	ADAMS	CLERK	7788	23-MAY-96	11900		20
7900	JAMES	CLERK	7698	03-DEC-95	12500		30
7902	FORD	ANALYST	7566	03-DEC-91	21500		20
7934	MILLER	CLERK	7782	23-JAN-95	13250		10
3258	GREEN	SALESMAN	4422	24-JUL-95	18500	2750	50
4422	STEVENS	MANAGER	7839	14-JAN-94	24750		50
6548	BARNES	CLERK	4422	16-JAN-95	11950		50
7500	CAMPBELL	ANALYST	7566	30-OCT-92	24500	0	40

• INSERT TUPLES FOR DEPT TABLE:

DEPTNO	DNAME	LOC
10	ACCOUNTING	LONDON
30	SALES	LIVERPOOL
40	OPERATIONS	STAFFORD
50	MARKETING	LUTON
20	RESEARCH	PRESTON

二、实验过程及内容

EXERCISES 2 JOINS

1. Find the name and salary of employees in Luton.

-- QUESTION2.1
SELECT
	ENAME,
	SAL 
FROM
	EMP AS T1,
	DEPT AS T2 
WHERE
	T1.DEPTNO = T2.DEPTNO 
	AND T2.LOC = 'LUTON';

2. Join the DEPT table to the EMP table and show in department number order.

-- QUESTION2.2
SELECT
	T1.*,T2.DNAME,T2.DEPTNO 
FROM
	EMP AS T1,
	DEPT AS T2 
WHERE
	T1.DEPTNO = T2.DEPTNO 
ORDER BY
	T1.DEPTNO;

3. List the names of all salesmen who work in SALES

-- QUESTION2.3
SELECT
	ENAME 
FROM
	EMP AS T1,
	DEPT AS T2 
WHERE
	T1.DEPTNO = T2.DEPTNO 
	AND T1.JOB = 'SALESMAN' 
	AND T2.DNAME = 'SALES';

4. List all departments that do not have any employees.

-- QUESTION2.4
SELECT
	DNAME 
FROM
	EMP AS T1,
	DEPT AS T2 
WHERE
	T1.DEPTNO = T2.DEPTNO 
GROUP BY
	T1.DEPTNO
HAVING
	COUNT( T1.DEPTNO )=0;

5. For each employee whose salary exceeds his manager’s salary, list the employee’s name and salary and the manager’s name and salary.

-- QUESTION2.5
SELECT
	T1.ENAME,
	T1.SAL,
	T2.ENAME,
	T2.SAL 
FROM
	EMP AS T1,
	EMP AS T2 
WHERE
	T1.MGR = T2.EMPNO 
	AND T1.SAL > T2.SAL;

6. List the employees who have BLAKE as their manager.

-- QUESTION2.6
SELECT
	T1.ENAME 
FROM
	EMP AS T1,
	EMP AS T2 
WHERE
	T1.MGR = T2.EMPNO 
	AND T2.ENAME = 'BLAKE';

7. List all the employee Name and his Manager’s name, even if that employee doesn’t have a manager

-- QUESTION2.7
SELECT
	T1.ENAME AS EMPLOYEE_NAME,
	T2.ENAME AS MANAGER_NAME 
FROM
	EMP AS T1,
	EMP AS T2 
WHERE
	T1.MGR = T2.EMPNO UNION
SELECT
	ENAME,
	MGR 
FROM
	EMP 
WHERE
	MGR IS NULL;

EXERCISES 3 FUNCTIONS

1. Find how many employees have a title of manager without listing them.

-- QUESTION3.1
SELECT
	COUNT(*) 
FROM
	EMP 
WHERE
	JOB = 'MANAGER';

2. Compute the average annual salary plus commission for all salesmen

-- QUESTION3.2
SELECT
	AVG( SAL + COMM )* 12 AS ANNUAL_AVG 
FROM
	EMP 
WHERE
	JOB = 'SALESMAN';

3. Find the highest and lowest salaries and the difference between them (single SELECT statement)

-- QUESTION3.3
SELECT
	MAX( SAL ) AS HIGHEST_SAL,
	MIN( SAL ) AS LOWEST_SAL,
	MAX( SAL )- MIN( SAL ) AS DIFERENCE 
FROM
	EMP;

4. Find the number of characters in the longest department name

-- QUESTION3.4
SELECT
	DNAME,
	COUNT(*) 
FROM
	EMP AS T1,
	DEPT AS T2 
WHERE
	T1.DEPTNO = T2.DEPTNO 
GROUP BY
	DNAME 
HAVING
	LENGTH( DNAME )>= ALL (
	SELECT
		LENGTH( DNAME ) 
	FROM
	DEPT);

5. Count the number of people in department 30 who receive a salary and the number of people who receive a commission (single statement).

-- QUESTION3.5
SELECT
	COUNT( SAL ),
	COUNT( COMM ) 
FROM
	EMP 
WHERE
	DEPTNO = 30;

6. List the average commission of employees who receive a commission, and the average commission of all employees (assume employees who do not receive a commission attract zero commission)

-- QUESTION3.6
SELECT
	AVG( COMM ) AS AVG1,
	SUM( COMM )/ COUNT(*) AS AVG2
FROM
	EMP;

7. List the average salary of employees that receive a salary, the average commission of employees that receive a commission, the average salary plus commission of only those employees that receive a commission and the average salary plus commission of all employees including those that do not receive a commission. (single statement)

-- QUESTION3.7
SELECT
	AVG( SAL ) AS AVG1,
	AVG( COMM ) AS AVG2,
	AVG( SAL + COMM ) AS AVG3,
	SUM( SAL + COMM )/ COUNT(*) AS AVG4 
FROM
	EMP;

8. Compute the daily and hourly salary for employees in department 30, round to the nearest penny. Assume there are 22 working days in a month and 8 working hours in a day.

-- QUESTION3.8
SELECT
	ROUND( AVG( SAL )/ 22 ) AS DAILY_SAL,
	ROUND( AVG( SAL )/ ( 22 * 8 )) AS HOURLY_SAL 
FROM
	EMP 
WHERE
	DEPTNO = 30;

9. Issue the same query as the previous one except that this time truncate (TRUNC) to the nearest penny rather than round.

-- QUESTION3.9
SELECT
	FLOOR( AVG( SAL )/ 22 ) AS DAILY_SAL,
	FLOOR( AVG( SAL )/ ( 22 * 8 )) AS HOURLY_SAL 
FROM
	EMP;

EXERCISES 4 DATES

1. Select the name, job, and date of hire of the employees in department 20. (Format the hiredate column using a picture MM/DD/YY)

-- QUESTION4.1
SELECT
	ENAME,
	JOB,
	DATE_FORMAT( HIREDATE, '%D/%M/%Y' ) 
FROM
	EMP;

2. Use a picture to format hiredate as DAY(day of the week), MONTH (name of the month, ) DD (day of the month) and YYYY(year)

-- QUESTION4.2
SELECT
	ENAME,
	JOB,
	DATE_FORMAT( HIREDATE, '%W %M %d %Y' ) 
FROM
	EMP;

3. Which employees were hired in March?

-- QUESTION4.3
SELECT
	* 
FROM
	EMP 
WHERE
	MONTH ( HIREDATE )=3;

4. Which employees were hired on a Tuesday?

-- QUESTION4.4
SELECT
	* 
FROM
	EMP 
WHERE
	DAYOFWEEK( HIREDATE )=3;

5. Are there any employees who have worked more than 16 years for the company?

-- QUESTION4.5
SELECT
	* 
FROM
	EMP 
WHERE
	TIMESTAMPDIFF(
		YEAR,
		HIREDATE,
	NOW())>= 16;

6. Show the weekday of the first day of the month in which each employee was hired. (plus their names)

-- QUESTION4.6
SELECT
	ENAME,
	DATE_FORMAT( HIREDATE, '%W' ) 
FROM
	EMP;

7. Show details of employee hiredates and the date of their first payday. (Paydays occur on the last Friday of each month) (plus their names)
8. Refine your answer to 7 such that it works even if an employee is hired after the last Friday of the month (cf Martin)
   这两题第一次做不会做，有会的欢迎留言，等我学会了再回来更

EXERCISES 5 GROUP BY & HAVING

1. List the department number and average salary of each department.

-- QUESTION5.1
SELECT
	DEPTNO,
	AVG( SAL ) 
FROM
	EMP 
GROUP BY
	DEPTNO;

2. Divide all employees into groups by department and by job within department. Count the employees in each group and compute each group’s average annual salary.

-- QUESTION5.2
SELECT
	JOB,
	DEPTNO,
	AVG( SAL ) * 12 AS ANNUAL_SAL
FROM
	EMP 
GROUP BY
	DEPTNO,
	JOB 
ORDER BY
	DEPTNO ASC;

3. Issue the same query as above except list the department name rather than the department number.

-- QUESTION5.3
SELECT
	JOB,
	DNAME,
	AVG( SAL ) * 12 AS ANNUAL_SAL 
FROM
	EMP AS T1,
	DEPT AS T2 
WHERE
	T1.DEPTNO = T2.DEPTNO 
GROUP BY
	T1.DEPTNO,
	JOB
ORDER BY
	DNAME ASC;

4. List the average annual salary for all job groups having more than 2 employees in the group.

-- QUESTION5.4
SELECT
	JOB,
	AVG( SAL )* 12 
FROM
	EMP 
GROUP BY
	JOB 
HAVING
	COUNT(*)>2;

5. Find all departments with an average commission greater than 25% of average salary.

-- QUESTION5.5
SELECT
	DEPTNO 
FROM
	EMP 
GROUP BY
	DEPTNO 
HAVING
	AVG( COMM )> 0.25 * AVG( SAL );

6. Find each department’s average annual salary for all its employees except the managers and the president.

-- QUESTION5.6
SELECT
	DEPTNO,
	AVG( SAL )* 12 
FROM
	EMP 
WHERE
	JOB != 'PRESIDENT' 
	OR JOB != 'MANAGER' 
GROUP BY
	DEPTNO 
ORDER BY
	DEPTNO ASC;

7. List the Department ID and Name where there are at least one Manager and two clerk

-- QUESTION5.7.1
SELECT
	T1.DEPTNO,
	DNAME 
FROM
	EMP AS T1,
	DEPT AS T2 
WHERE
	T1.DEPTNO = T2.DEPTNO 
GROUP BY
	T1.DEPTNO 
HAVING
	COUNT( CASE WHEN JOB = 'CLERK' THEN 1 ELSE NULL END )>= 2 
	AND COUNT( CASE WHEN JOB = 'MANAGER' THEN 1 ELSE NULL END )>=1;

• List the Department ID and Name where there are at least one Manager and two clerk and whose average salary is greater that the company’s average salary.

-- QUESTION5.7.2
SELECT
	T1.DEPTNO,
	DNAME 
FROM
	EMP AS T1,
	DEPT AS T2 
WHERE
	T1.DEPTNO = T2.DEPTNO 
GROUP BY
	T1.DEPTNO 
HAVING
	COUNT( CASE WHEN JOB = 'CLERK' THEN 1 ELSE NULL END )>= 2 
	AND COUNT( CASE WHEN JOB = 'MANAGER' THEN 1 ELSE NULL END )>= 1 
	AND AVG( SAL )>= ( SELECT AVG( SAL ) FROM EMP );

8. List the name of the Manager who manages most employee

-- QUESTION5.8
SELECT
	T2.ENAME,COUNT(*) AS EMP_NUM
FROM
	EMP AS T1,
	EMP AS T2 
WHERE
	T1.MGR = T2.EMPNO 
	AND (T2.JOB = 'MANAGER' OR T2.JOB = 'PRESIDENT')
GROUP BY
	T2.ENAME 
ORDER BY
	COUNT(*) DESC 
	LIMIT 1;

9. List the name of all the Manager who manages atleast 2 employees

-- QUESTION5.9
SELECT
	T2.ENAME,COUNT(*)
FROM
	EMP AS T1,
	EMP AS T2 
WHERE
	T1.MGR = T2.EMPNO 
	AND (T2.JOB = 'MANAGER' OR T2.JOB = 'PRESIDENT') 
GROUP BY
	T2.ENAME 
	HAVING
	COUNT(*)>=2
ORDER BY
	COUNT(*) DESC;

EXERCISES 6 SUB QUERIES.

1. List the name and job of employees who have the same job as Jones.

-- QUESTION6.1
SELECT
	ENAME,
	JOB 
FROM
	EMP 
WHERE
	JOB IN ( SELECT JOB FROM EMP WHERE ENAME = 'JONES' );

2. Find all the employees in Department 10 that have a job that is the same as anyone in department 30.

-- QUESTION6.2
SELECT
	ENAME 
FROM
	EMP 
WHERE
	DEPTNO = 10 
	AND JOB = SOME ( SELECT JOB FROM EMP WHERE DEPTNO = 30 );

3. List the name, job, and department of employees who have the same job as Jones or a salary greater than or equal to Ford.

-- QUESTION6.3
SELECT
	ENAME,
	JOB,
	DEPTNO 
FROM
	EMP 
WHERE
	JOB =(SELECT JOB FROM EMP	WHERE ENAME = 'JONES' ) 
	OR SAL >=(SELECT SAL FROM EMP WHERE	ENAME = 'FORD');

4. Find all employees in department 10 that have a job that is the same as anyone in the Sales department

-- QUESTION6.4
SELECT
	ENAME 
FROM
	EMP 
WHERE
	DEPTNO = 10 
	AND JOB = SOME ( 
		SELECT 
			JOB 
		FROM 
			EMP AS T1, DEPT AS T2 
		WHERE 
			T1.DEPTNO = T2.DEPTNO AND T2.DNAME = 'SALES'
		);

5. Find the employees located in Liverpool who have the same job as Allen. Return the results in alphabetical order by employee name.

-- QUESTION6.5
SELECT
	ENAME 
FROM
	EMP AS T1,
	DEPT AS T2 
WHERE
	T1.DEPTNO = T2.DEPTNO 
	AND T2.LOC = 'LIVERPOOL' 
	AND T1.JOB =(
	SELECT
		JOB 
	FROM
		EMP 
	WHERE
		ENAME = 'ALLEN' 
	) 
ORDER BY
	ENAME ASC;

6. Find all the employees that earn more than the average salary of employees in their department.

-- QUESTION6.6
SELECT 
	ENAME 
FROM 
	EMP AS T1,
	( SELECT DEPTNO, AVG( SAL ) AS AVG FROM EMP GROUP BY DEPTNO ) AS T2 
WHERE
	T1.SAL > T2.AVG
	AND T1.DEPTNO = T2.DEPTNO;

7. Find all the employees that earn more than JONES, using temporary labels to abbreviate table names.

-- QUESTION6.7
SELECT
	T1.ENAME 
FROM
	EMP AS T1,
	EMP AS T2 
WHERE
	T2.ENAME = 'JONES' 
	AND T1.SAL > T2.SAL;

8. List the Name of all employees who earn Highest salary and Second Highest salary.

-- QUESTION6.8
SELECT
	ENAME 
FROM
	EMP 
ORDER BY
	SAL DESC
	LIMIT 2;

EXERCISES 7 Data Manipulation

1. Create a new table called loans with columns named LNO NUMBER (3), EMPNO NUMBER (4), TYPE CHAR(1), AMNT NUMBER (8,2), Create all constraints, such as Primary Key, Foreign Key, Check

-- QUESTION7.1
CREATE TABLE `loan` (
	`LNO` DECIMAL ( 3, 0 ) NOT NULL,
	`EMPNO` INT ( 4 ) DEFAULT NULL,
	`TYPE` CHAR ( 1 ) DEFAULT NULL,
	`AMNT` DECIMAL ( 8, 2 ) DEFAULT NULL,
	PRIMARY KEY ( `LNO` ),
	FOREIGN KEY ( `EMPNO` ) REFERENCES EMP ( EMPNO ) 
)

2. Insert the following data

LNO	EMPNO	TYPE	AMNT
23	7499	M	20000.00
42	7499	C	2000.00
65	7844	M	3564.00

-- QUESTION7.2
INSERT INTO LOAN
VALUES
	( 23, 7499, 'M', 20000 );
	
INSERT INTO LOAN
VALUES
	( 42, 7499, 'C', 2000 );
	
INSERT INTO LOAN
VALUES
	( 65, 7844, 'M', 3564 );

3. Check that you have created 3 new records in Loans

-- QUESTION7.3
SELECT
	* 
FROM
	LOAN;

4. The Loans table must be altered to include another column OUTST NUMBER(8,2)

-- QUESTION7.4
ALTER TABLE LOAN ADD COLUMN OUST NUMERIC ( 8, 2 );

5. Add 10% interest to all M type loans

-- QUESTION7.5
UPDATE LOAN 
SET AMNT = AMNT * 1.1 
WHERE
	TYPE = 'M';

6. Remove all loans less than £3000.00

-- QUESTION7.6
DELETE 
FROM
	LOAN 
WHERE
	AMNT < 3000;

7. Change the name of loans table to accounts

-- QUESTION7.7
ALTER TABLE LOAN RENAME TO ACCOUNTS;

8. Change the name of column LNO to LOANNO

-- QUESTION7.8
ALTER TABLE ACCOUNTS CHANGE LNO LOANNO NUMERIC ( 3, 0 );

9. Create a view for use by personnel in department 30 showing employee name, number, job and hiredate

-- QUESTION7.9
CREATE VIEW VIEW1 AS SELECT
ENAME,
EMPNO,
JOB,
HIREDATE 
FROM
	EMP 
WHERE
	DEPTNO = 30;

10. Use the view to show employees in department 30 having jobs which are not salesman

-- QUESTION7.10
SELECT
	* 
FROM
	view1 
WHERE
	JOB IS NOT NULL 
	AND JOB != 'SALESMAN'

11. Create a view which shows summary information for each department.

-- QUESTION7.11
CREATE VIEW VIEW2 AS SELECT
* 
FROM
	DEPT

三、数据处理分析

SQL知识点整理：

1. UNION
   在表后连接一个新的表

2. COUNT/MAX/MIN/…
   聚合函数，不能用于where中，若要作为分组条件，只能用户having中

3. THETA ALL/SOME
   运算符+all/some ===> 大于/小于/… + 部分/全部数据

4. ROUND
   四舍五入函数

5. FLOOR
   去尾法函数

6. DATE_FORMAT
   日期格式定义函数，具体格式见下
   

7. TIMESTAMPDIFF函数
   TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
   返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。

8. NOW()函数
   获取系统当前时间

9. COUNT聚合函数+判断条件
   Count(case when XXXX then 1 else NULL end)
   返回值为1，计数+1，返回值为NULL，不计数

10. 取数据前n行
    Order By
    XXXX DESC/ASC
    Limit n