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

Alex_Shen
2020-11-07 / 0 评论 / 0 点赞 / 97 阅读 / 11,654 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-04-06,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

前言

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

一、实验目的与要求:

  • 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';
  1. 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;
  1. 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';
  1. 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;
  1. 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;

  1. 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';
  1. 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';
  1. 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';
  1. 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;
  1. 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);
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. Which employees were hired in March?
-- QUESTION4.3
SELECT
	* 
FROM
	EMP 
WHERE
	MONTH ( HIREDATE )=3;
  1. Which employees were hired on a Tuesday?
-- QUESTION4.4
SELECT
	* 
FROM
	EMP 
WHERE
	DAYOFWEEK( HIREDATE )=3;
  1. 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;
  1. 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;
  1. Show details of employee hiredates and the date of their first payday. (Paydays occur on the last Friday of each month) (plus their names)
  2. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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 );
  1. 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;
  1. 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 );
  1. 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;
  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.

  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' );
  1. 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 );
  1. 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');
  1. 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'
		);
  1. 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;
  1. 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;
  1. 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;
  1. 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 ) 
)
  1. 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 );
  1. Check that you have created 3 new records in Loans
-- QUESTION7.3
SELECT
	* 
FROM
	LOAN;
  1. 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 );
  1. Add 10% interest to all M type loans
-- QUESTION7.5
UPDATE LOAN 
SET AMNT = AMNT * 1.1 
WHERE
	TYPE = 'M';
  1. Remove all loans less than £3000.00
-- QUESTION7.6
DELETE 
FROM
	LOAN 
WHERE
	AMNT < 3000;
  1. Change the name of loans table to accounts
-- QUESTION7.7
ALTER TABLE LOAN RENAME TO ACCOUNTS;
  1. Change the name of column LNO to LOANNO
-- QUESTION7.8
ALTER TABLE ACCOUNTS CHANGE LNO LOANNO NUMERIC ( 3, 0 );
  1. 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;
  1. 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'
  1. 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

0

评论区