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

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

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:
在这里插入图片描述

实验过程及内容:

  1. Create a database

在这里插入图片描述
在这里插入图片描述
Create two tables
在这里插入图片描述
Insert data
在这里插入图片描述

  1. List all information about the employees.
--question1
SELECT
	* 
FROM
	emp;
  1. List all information about the departments
--question2
SELECT
	* 
FROM
	dept
  1. List only the following information from the EMP table ( Employee name, employee number, salary, department number)
--question3
SELECT
	ENAME,
	EMPNO,
	SAL,
	DEPTNO
FROM
	emp;
  1. List details of employees in departments 10 and 30.
-- question4
SELECT
	* 
FROM
	emp 
WHERE
	`DEPTNO` = 10 
	OR `DEPTNO` = 30;
  1. List all the jobs in the EMP table eliminating duplicates.
-- question5
SELECT DISTINCT
	JOB 
FROM
	emp
  1. What are the names of the employees who earn less than £20,000?
-- question6
SELECT
	emp.ENAME 
FROM
	emp 
WHERE
	emp.SAL < 20000
  1. 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;
  1. Find all employees whose job is either Clerk or Salesman.
-- question8
SELECT
	* 
FROM
	emp 
WHERE
	emp.JOB = 'CLERK' 
	OR emp.JOB = 'SALESMAN';
  1. Find any Clerk who is not in department 10.
-- question9
SELECT
	* 
FROM
	emp 
WHERE
	emp.JOB = 'CLERK' 
	AND emp.DEPTNO != 10;
  1. 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 );
  1. 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;
  1. Find the name of the President.
-- question12
SELECT
	emp.ENAME 
FROM
	emp 
WHERE
	emp.JOB = 'PRESIDENT';
  1. Find all the employees whose last names end with S
-- question13
SELECT
	* 
FROM
	emp 
WHERE
	emp.ENAME LIKE '%S';
  1. 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%';
  1. List only those employees who receive commission.
-- question15
SELECT
	* 
FROM
	emp 
WHERE
	emp.COMM IS NOT NULL 
	AND emp.COMM != 0;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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';
  1. 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;
  1. 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 );
  1. What is the command to add primary key constraint to EMPNO
-- question23
ALTER TABLE emp ADD PRIMARY KEY ( EMPNO );
  1. 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 );
  1. How to drop primary key constraint for EMPNO
-- question25
ALTER TABLE emp DROP PRIMARY KEY ;
  1. rename EMP table to EMPLOYEE
-- question26
RENAME TABLE emp TO EMPLOYEE;
  1. rename EMPLOYEE back to EMP
-- question27
RENAME TABLE EMPLOYEE TO emp;
  1. What is the SQL command to remove column EMP_family_name from EMP table
-- question28
ALTER TABLE emp DROP emp.family_name;
  1. What is the SQL command to copy emp table to employee table
-- question29
CREATE TABLE employee AS SELECT * FROM emp;
  1. What is the SQL command to drop employee table
-- question30
DROP TABLE employee;
  1. What is the SQL command to display name’s of employee entered interactively from user
-- question31
SELECT
	* 
FROM
	emp 
WHERE
	emp.ENAME LIKE '%用户输入字符串%';
  1. What is the SQL command to find the employee whose commission is NULL
-- question32
SELECT
	* 
FROM
	emp
WHERE
	emp.COMM IS NULL;
0

评论区