Create table structure according to ER diagram
/*
Navicat Oracle Data Transfer
Oracle Client Version : 10.2.0.5.0
Source Server : MyOracle
Source Server Version : 110200
Source Host : localhost:1521
Source Schema : KAIFAMIAO
Target Server Type : ORACLE
Target Server Version : 110200
File Encoding : 65001
Date: 2021-10-19 17:16:36
*/
-- ----------------------------
-- Table structure for DEPT
-- ----------------------------
DROP TABLE "KAIFAMIAO"."DEPT";
CREATE TABLE "KAIFAMIAO"."DEPT" (
"ID" NUMBER NOT NULL ,
"DNAME" VARCHAR2(20 BYTE) NULL ,
"LOC" VARCHAR2(100 BYTE) NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of DEPT
-- ----------------------------
INSERT INTO "KAIFAMIAO"."DEPT" VALUES ('1001', 'Development Department', 'Xi'an');
INSERT INTO "KAIFAMIAO"."DEPT" VALUES ('1002', 'Marketing Department', 'Lanzhou');
INSERT INTO "KAIFAMIAO"."DEPT" VALUES ('1003', 'Sales Department', 'Beijing');
-- ----------------------------
-- Table structure for EMP
-- ----------------------------
DROP TABLE "KAIFAMIAO"."EMP";
CREATE TABLE "KAIFAMIAO"."EMP" (
"ID" NUMBER NOT NULL ,
"ENAME" VARCHAR2(50 BYTE) NULL ,
"JOB_ID" NUMBER NULL ,
"MGR" NUMBER NULL ,
"JOINDATE" DATE NULL ,
"SALARY" NUMBER(7,2) NULL ,
"BONUS" NUMBER(7,2) NULL ,
"DEPT_ID" NUMBER NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of EMP
-- ----------------------------
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10001', 'Zhang Bin', '102', '0', TO_DATE('2019-08-09 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '9500', '8000', null);
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10002', 'Gu Ci', '101', '1', TO_DATE('2019-08-09 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8500', '5000', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10003', 'Ping An Chen', '103', '2', TO_DATE('2019-10-09 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8500', '5000', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10004', 'Lose money', '104', '2', TO_DATE('2019-10-10 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8300', '5000', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10005', 'Zhang Sanfeng', '101', '1', TO_DATE('2019-09-19 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8500', '4000', '1002');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10006', 'Cao Ci', '101', '1', TO_DATE('2019-10-18 18:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8500', '3900', '1003');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10007', 'about', '106', '2', TO_DATE('2019-10-13 15:30:10', 'YYYY-MM-DD HH24:MI:SS'), '4500', '2500', '1003');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10008', 'Bai Ye', '105', '2', TO_DATE('2019-10-01 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '4500', '2000', '1002');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10009', 'Assistant 1', '107', '3', TO_DATE('2020-04-29 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '4000', '0', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('100010', 'Assistant 2', '107', '3', TO_DATE('2021-05-10 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '4000', '1000', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('100011', 'Assistant 3', '107', '3', TO_DATE('2019-04-20 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '3000', '0', '1002');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('100012', 'Assistant 4', '107', '3', TO_DATE('2018-10-29 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '3000', '1000', '1003');
-- ----------------------------
-- Table structure for INFOS
-- ----------------------------
DROP TABLE "KAIFAMIAO"."INFOS";
CREATE TABLE "KAIFAMIAO"."INFOS" (
"STUID" VARCHAR2(7 BYTE) NOT NULL ,
"STUNAME" VARCHAR2(10 BYTE) NOT NULL ,
"GENDER" VARCHAR2(5 BYTE) NOT NULL ,
"AGE" NUMBER(2) NOT NULL ,
"SEAT" NUMBER(2) NOT NULL ,
"ENROLLDATE" DATE NULL ,
"STUADDRESS" VARCHAR2(50 CHAR) DEFAULT 'The address is unknown' NOT NULL ,
"CLASSNO" VARCHAR2(4 BYTE) NOT NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of INFOS
-- ----------------------------
INSERT INTO "KAIFAMIAO"."INFOS" VALUES ('s100102', 'Lin Chong', 'male', '22', '2', TO_DATE('2021-10-19 14:45:14', 'YYYY-MM-DD HH24:MI:SS'), 'Xi'an', '1001');
INSERT INTO "KAIFAMIAO"."INFOS" VALUES ('s100104', 'Ruan Xiaoer', 'male', '26', '3', TO_DATE('2021-10-18 21:16:10', 'YYYY-MM-DD HH24:MI:SS'), 'The address is unknown', '1001');
-- ----------------------------
-- Table structure for JOB
-- ----------------------------
DROP TABLE "KAIFAMIAO"."JOB";
CREATE TABLE "KAIFAMIAO"."JOB" (
"ID" NUMBER NOT NULL ,
"JNAME" VARCHAR2(30 BYTE) NULL ,
"DESCRIPTION" VARCHAR2(200 BYTE) NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of JOB
-- ----------------------------
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('101', 'division manager', 'Be responsible for the specific work of the whole department and report to the general manager');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('103', 'Development Engineer', 'Be responsible for the specific development of the Department and report to the Department Manager');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('102', 'general manager', 'Responsible for the overall operation of the company');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('104', 'Operation and maintenance test engineer', 'Be responsible for the operation and maintenance test of the products of the development department');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('105', 'Market developer', 'Responsible for the development and expansion of the company's market');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('106', 'salesperson', 'Responsible for selling the company's products');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('107', 'assistant', 'Assist the superior staff to complete the work');
-- ----------------------------
-- Table structure for SALARYGRADE
-- ----------------------------
DROP TABLE "KAIFAMIAO"."SALARYGRADE";
CREATE TABLE "KAIFAMIAO"."SALARYGRADE" (
"GRADE" NUMBER NOT NULL ,
"LOSALARY" NUMBER NULL ,
"HISALARY" NUMBER NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of SALARYGRADE
-- ----------------------------
INSERT INTO "KAIFAMIAO"."SALARYGRADE" VALUES ('1', '9000', '10000');
INSERT INTO "KAIFAMIAO"."SALARYGRADE" VALUES ('2', '8000', '9000');
INSERT INTO "KAIFAMIAO"."SALARYGRADE" VALUES ('3', '5500', '7000');
INSERT INTO "KAIFAMIAO"."SALARYGRADE" VALUES ('4', '3000', '5000');
-- ----------------------------
-- Table structure for SCORES
-- ----------------------------
DROP TABLE "KAIFAMIAO"."SCORES";
CREATE TABLE "KAIFAMIAO"."SCORES" (
"ID" NUMBER NULL ,
"TERM" VARCHAR2(2 BYTE) NULL ,
"STUID" VARCHAR2(7 BYTE) NOT NULL ,
"EXAMNO" VARCHAR2(7 BYTE) NOT NULL ,
"WRITTENSCORE" NUMBER(4,1) NOT NULL ,
"LABSCORE" NUMBER(4,1) NOT NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of SCORES
-- ----------------------------
INSERT INTO "KAIFAMIAO"."SCORES" VALUES ('1001', 'S2', 's100104', '4', '98', '89');
-- ----------------------------
-- Sequence structure for MYSEQ
-- ----------------------------
DROP SEQUENCE "KAIFAMIAO"."MYSEQ";
CREATE SEQUENCE "KAIFAMIAO"."MYSEQ"
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999999999999999999999999999
START WITH 21
CACHE 20;
-- ----------------------------
-- Indexes structure for table DEPT
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table DEPT
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."DEPT" ADD PRIMARY KEY ("ID");
-- ----------------------------
-- Indexes structure for table EMP
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table EMP
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."EMP" ADD PRIMARY KEY ("ID");
-- ----------------------------
-- Uniques structure for table INFOS
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."INFOS" ADD UNIQUE ("STUNAME");
ALTER TABLE "KAIFAMIAO"."INFOS" ADD UNIQUE ("STUID");
-- ----------------------------
-- Checks structure for table INFOS
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK (AGE >=0 AND AGE<=100);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ((CLASSNO >='1001' AND CLASSNO<='1999') OR
(CLASSNO >='2001' AND CLASSNO<='2999'));
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK (GENDER = 'male' OR GENDER = 'female');
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK (SEAT >=0 AND SEAT <=50);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("STUID" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("STUNAME" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("GENDER" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("AGE" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("SEAT" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("STUADDRESS" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("CLASSNO" IS NOT NULL);
-- ----------------------------
-- Indexes structure for table JOB
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table JOB
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."JOB" ADD PRIMARY KEY ("ID");
-- ----------------------------
-- Indexes structure for table SALARYGRADE
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table SALARYGRADE
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."SALARYGRADE" ADD PRIMARY KEY ("GRADE");
-- ----------------------------
-- Checks structure for table SCORES
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK (TERM = 'S1' OR TERM ='S2');
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK ("STUID" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK ("EXAMNO" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK ("WRITTENSCORE" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK ("LABSCORE" IS NOT NULL);
-- ----------------------------
-- Foreign Key structure for table "KAIFAMIAO"."EMP"
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."EMP" ADD FOREIGN KEY ("DEPT_ID") REFERENCES "KAIFAMIAO"."DEPT" ("ID");
ALTER TABLE "KAIFAMIAO"."EMP" ADD FOREIGN KEY ("JOB_ID") REFERENCES "KAIFAMIAO"."JOB" ("ID");
-- ----------------------------
-- Foreign Key structure for table "KAIFAMIAO"."SCORES"
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."SCORES" ADD FOREIGN KEY ("STUID") REFERENCES "KAIFAMIAO"."INFOS" ("STUID");
Use the above table to complete the data query
1. Select all employees in a department
select e.id, e.ename, j.jname, e.salary, e.bonus, d.dname
from job j join emp e on j.id = e.job_id
join dept d on d.id = e.dept_id
where e.dept_id = 1003;
2. List the names, numbers and department numbers of all second level employees
select e.ename, e.id, e.dept_id from emp e where mgr = 2;
3. Find out employees whose bonus is higher than salary
select * from emp where bonus > salary;
4. Find out employees whose bonus is higher than 60% of salary
select * from emp where bonus > salary * 0.6;
5. Find out the details of the manager of a department and all the second level employees of a department [the two departments are different]
select * from emp where dept_id = 1001 and mgr = 1 or mgr = (select mgr from emp where dept_id = 1002 and mgr = 2);
6. Find out the detailed work of the manager of a department and all the second level employees of a department who are neither managers nor clerks but whose salary is less than or equal to 3000
SQL> select ename, j.jname, salary
from emp join job j on emp.job_id = j.id
where (emp.mgr = 1 and emp.dept_id = 1001)
or (emp.mgr = 2 and emp.dept_id = 1002)
or (emp.mgr != 1 and emp.mgr != 2 and emp.salary > 2000);
7. Find out the different jobs of employees receiving bonuses
-- select distinct j.jname from emp e join job j on e.job_id = j.id where e.bonus != 0;
select distinct j.jname from emp e join job j on e.job_id = j.id where e.bonus is not null;
8. Find out employees who do not receive bonus or whose bonus is less than 5000
select distinct j.jname from emp e join job j on e.job_id = j.id where e.bonus = 0 or e.bonus < 5000;
9. Identify all employees employed on the penultimate day of each month
SELECT * FROM EMP WHERE JOINDATE = LAST_DAY(JOINDATE)-2 ;
10. Identify employees employed earlier than 2 years ago
select * from emp where JOINDATE <= add_months(sysdate,-24);
select * from emp where JOINDATE <= JOINDATE + INTERVAL '-2' YEAR;
Practice based on the SCOTT sample database
1. List all departments of at least one employee
-- stay SQL Medium increase HAVING The reason is, WHERE Keywords cannot be used with aggregate functions. HAVING Clause allows us to filter the grouped groups of data.
SELECT E.DEPTNO,D.DNAME,COUNT(E.EMPNO) AS EMPNUM FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
GROUP BY E.DEPTNO,D.DNAME
HAVING COUNT(E.EMPNO) > 1
-- This is not very rigorous. If the number does not correspond to the name, there will be problems, so the above can not check the name, check the number and group it by number
-- If you want to list them all, you can use the word segmentation query results as tables and dept Table connection
-- having write group by After and before
select d.deptno, d.dname, d1.cou from dept d,
(select deptno, count(empno) cou having count(empno) > 1 group by deptno)
where d.deptno = d1.deptno
2. List all employees who earn more than 'SMITH'
SELECT * FROM EMP
WHERE SAL >
(SELECT SAL FROM EMP WHERE ENAME = 'SMITH')
3. List the names of all employees and their direct supervisors
SELECT E.ENAME AS EMPNAME, E1.ENAME AS MGRNAME FROM EMP E
JOIN EMP E1 ON E.MGR = E1.EMPNO
4. List all employees whose employment date is earlier than their direct supervisor
SELECT E.* FROM EMP E
JOIN EMP E1 ON E.MGR = E1.EMPNO
WHERE E.HIREDATE > E1.HIREDATE
5. List the Department name and employee information of these departments, and list those departments without employees
SELECT D.DNAME,D.DEPTNO, E.* FROM EMP E
RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO
-- left
select
d.deptno,d.dname,e.empno,e.ename
from
dept d left join emp e
on
d.deptno=e.deptno
-- Oracle Special Usage +
SELECT D.DNAME,D.DEPTNO, E.* FROM EMP E , DEPT D
WHERE E.DEPTNO (+) = ED.DEPTNO
select
d.deptno,d.dname,e.empno,e.ename
from
dept d ,emp e
where
d.deptno=e.deptno(+)
6. List all kinds of jobs with a salary of more than 1500
SELECT JOB, MIN(SAL) FROM EMP
GROUP BY JOB
HAVING MIN(SAL) > 1500
7. List the names of employees working in the Department 'SALES'
SELECT ENAME FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES')
-- 2
select
e.ename,d.dname
from
emp e,dept d
where
d.dname='SALES'
and
e.deptno=d.deptno
8. List all employees whose salary is higher than the average salary of the company
select ename,sal from emp where sal > (select avg(sal) from emp)
9. List the names and salaries of all employees whose salary is equal to the salary of employees in 30 departments
select ename,sal,deptno from emp where sal in (select sal from emp where deptno = 30)
10. List the names and salaries of employees whose salaries are higher than those of all employees working in department 30
select ename,sal,deptno from emp where sal > ALL(select sal from emp where deptno = 30)
-- 2
select
ename,sal
from
emp
where
sal>(select max(sal) from emp where deptno=30)
11. List the number of employees working in each department, average salary and average service period
SELECT DEPTNO, COUNT(*) AS Number of people, AVG(SAL), AVG(SYSDATE - HIREDATE)
FROM EMP
GROUP BY DEPTNO
select count(empno),round(avg(sal),2),round(avg(sysdate-hiredate)/365,0)
from emp
group by deptno
12. List the names, Department names and wages of all employees
select e.ename,d.dname,e.sal from emp e ,dept d where e.deptno = d.deptno;
13. List the details of all departments and the number of departments
-- ...
SELECT D.DEPTNO,D.DNAME,D.LOC, E1.COU Number of people
FROM DEPT D,(SELECT DEPTNO,COUNT(EMPNO) COU FROM EMP GROUP BY DEPTNO) E1
WHERE D.DEPTNO = E1.DEPTNO
14. List the minimum wage for various jobs
select job, min(sal) from emp group by job;
15. List the minimum salary of 'MANAGER' in each department
select deptno, min(sal) from emp where job = 'MANAGER' group by deptno;
16. List the annual salary of all employees, ranking from low to high
select ename, ((sal+NVL(COMM, 0)) * 12) year_sal from emp order by year_sal;
17. Query the information of all employees employed after 81 years
select * from emp
where to_char(hiredate, 'yyyy') > to_char(to_date('1981', 'yyyy'), 'yyyy');-- Based on 81 years, i.e. 82 years later
-- select * from emp where hiredate > to_date('1981-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); -- Calculated on January 1, 1981
-- select * from emp where to_char(hiredate,'YYYY') > 81;
18. Query the employee named 'SMITH' in the employee table, how many weeks have they worked up to today
select ROUND((sysdate - hiredate) / 7) from emp where ename = 'SMITH';
19. Query all employee numbers who have worked for more than one year
select empno from emp where to_char(sysdate,'YYYY') - to_char(hiredate,'YYYY') > 1 ;
20. Inquire about the date of Monday, the penultimate week of December this year
select next_day((add_months(trunc(sysdate,'year'),12)-1) + (interval '-14' day),2) from dual;