前言

深圳大學《系統設計與資料庫系統》第二次實驗記錄。

一、實驗目的與要求：

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	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

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';

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;

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';

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;

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;

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';

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

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

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

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';

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;

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);

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;

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;

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;

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;

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

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;

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;

Which employees were hired in March?

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

Which employees were hired on a Tuesday?

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

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;

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;

Show details of employee hiredates and the date of their first payday. (Paydays occur on the last Friday of each month) (plus their names)
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

List the department number and average salary of each department.

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

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;

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;

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;

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 );

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;

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 );

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;

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.

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' );

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 );

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');

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'
		);

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;

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;

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;

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

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 ) 
)

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 );

Check that you have created 3 new records in Loans

-- QUESTION7.3
SELECT
	* 
FROM
	LOAN;

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 );

Add 10% interest to all M type loans

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

Remove all loans less than £3000.00

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

Change the name of loans table to accounts

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

Change the name of column LNO to LOANNO

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

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;

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'

Create a view which shows summary information for each department.

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

三、資料處理分析

SQL知識點整理：

UNION

在表後連接配接一個新的表
COUNT/MAX/MIN/…

聚合函數，不能用于where中，若要作為分組條件，隻能使用者having中
THETA ALL/SOME

運算符+all/some ===> 大于/小于/… + 部分/全部資料
ROUND

四舍五入函數
FLOOR

去尾法函數
DATE_FORMAT

日期格式定義函數，具體格式見下

【系統設計與資料庫系統】Advanced SQL LAB前言一、實驗目的與要求：二、實驗過程及内容三、資料處理分析
TIMESTAMPDIFF函數

TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)

傳回日期或日期時間表達式datetime_expr1 和datetime_expr2the 之間的整數差。
NOW()函數

擷取系統目前時間
COUNT聚合函數+判斷條件

Count(case when XXXX then 1 else NULL end)

傳回值為1，計數+1，傳回值為NULL，不計數
取資料前n行

Order By

XXXX DESC/ASC

Limit n

【系統設計與資料庫系統】Advanced SQL LAB前言一、實驗目的與要求：二、實驗過程及内容三、資料處理分析

目錄

前言

一、實驗目的與要求：

二、實驗過程及内容

EXERCISES 2 JOINS

EXERCISES 3 FUNCTIONS

EXERCISES 4 DATES

EXERCISES 5 GROUP BY & HAVING

EXERCISES 6 SUB QUERIES.

EXERCISES 7 Data Manipulation

三、資料處理分析

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述