Multi table query - case exercise

Multi-table query —— Case practice

Environmental preparation ：

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;


--  Departmental table 
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, --  department id
  dname VARCHAR(50), --  Department name 
  loc VARCHAR(50) --  Department location 
);



--  Job list , Job title , Job description 
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

--  The employee table 
CREATE TABLE emp (
  id INT PRIMARY KEY, --  staff id
  ename VARCHAR(50), --  Employee name 
  job_id INT, --  position id
  mgr INT , --  Superior leaders 
  joindate DATE, --  Date of entry 
  salary DECIMAL(7,2), --  Wages 
  bonus DECIMAL(7,2), --  Bonus 
  dept_id INT, --  Department number 
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
--  Pay scale 
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   --  Level 
  losalary INT,  --  minimum wage 
  hisalary INT --  Maximum wage 
);
				
--  add to 4 Departments 
INSERT INTO dept(id,dname,loc) VALUES 
(10,' Teaching and Research Department ',' Beijing '),
(20,' Department of science and Engineering ',' Shanghai '),
(30,' The sales department ',' Guangzhou '),
(40,' Finance Department ',' Shenzhen ');

--  add to 4 Positions 
INSERT INTO job (id, jname, description) VALUES
(1, ' Chairman of the board of directors ', ' Managing the whole company , order '),
(2, ' The manager ', ' Management staff '),
(3, ' Salesperson ', ' Sell products to customers '),
(4, ' Clerk ', ' Using office software ');


--  Add employees 
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,' The Monkey King ',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,' Jun-yi lu ',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,' Lin Chong ',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,' Tang's monk ',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,' Li Kui ',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,' Song Jiang ',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,' Liu bei ',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,' Pig eight quit ',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,' Luo Guanzhong ',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,' Wu Yong ',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,' Monk sha ',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,' Li Kui ',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,' Small white dragon ',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,' Guan yu ',4,1007,'2002-01-23','13000.00',NULL,10);


--  add to 5 A salary scale 
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);



-- 1. Query all employee information . Enquiry of employee number , Employee name , Wages , Job title , Job description 

-- 2. Enquiry of employee number , Employee name , Wages , Job title , Job description , Department name , Department position 

-- 3. Check the name of the employee , Wages , Pay scale 

-- 4. Check the name of the employee , Wages , Job title , Job description , Department name , Department position , Pay scale 

-- 5. Find out the department number 、 Department name 、 Department position 、 Number of departments 

Table relations ：

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-0XS2kxpA-1657338411931)(C:\Users\10307\Desktop\md picture \2.png)]

practice ：

1、

-- Query all employee information . Enquiry of employee number , Employee name , Wages , Job title , Job description 
--  analysis ： Employee number , Employee name , Wages in emp in 
--  analysis ： Job title , The job description is in job in 
-- job and emp It's a one-to-many relationship ！（ Two meters should be connected （ Internal connection ））

--  Mode one  ： Implicit inner join 
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description
FROM
	emp,
	job
WHERE
	emp.job_id = job.id;

--  Mode two  ： Explicit inner connection 
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description 
FROM
	emp
	INNER JOIN job ON emp.job_id = job.id;

2、

-- 2. Enquiry of employee number , Employee name , Wages , Job title , Job description , Department name , Department position 

--  analysis ： Employee number , Employee name , Wages in emp in 
--  analysis ： Job title , The job description is in job in 
--  analysis ： Department name , Department location is dept in 
-- job and emp It's a one-to-many relationship ！（ Two meters should be connected （ Internal connection ））emp.job_id = job.id
-- dept and emp It's a one-to-many relationship ！（ Two meters should be connected （ Internal connection ））dept.id = emp.dept_id
--  Three meter connection 

--  Mode one  ： Implicit inner join 
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc 
FROM
	emp,
	job,
	dept 
WHERE
	emp.job_id = job.id 
	AND emp.dept_id = dept.id;
	
--  Mode two  ： Explicit inner connection 
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc 
FROM
	emp
	INNER JOIN job ON emp.job_id = job.id
	INNER JOIN dept ON emp.dept_id = dept.id;

3、

	-- 3. Check the name of the employee , Wages , Pay scale 
	--  analysis ： Employee name , Wages in emp in 
	--  analysis ： Pay scale   The information in  salarygrade  In the salary scale 
	--  How to determine the grade ：emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
	--  The two tables are connected 
SELECT
	emp.ename,
	emp.salary,
	t2.* 
FROM
	emp,
	salarygrade t2 
WHERE
	emp.salary >= t2.losalary 
	AND emp.salary <= t2.hisalary;
----

SELECT emp.ename, emp.salary, t2.* FROM emp INNER JOIN salarygrade t2 ON emp.salary >= t2.losalary 
AND emp.salary <= t2.hisalary;

4、

-- 4. Check the name of the employee , Wages , Job title , Job description , Department name , Department position , Pay scale 
--  analysis ： Employee number , Employee name , Wages in emp in 
--  analysis ： Job title , The job description is in job in 
--  analysis ： Department name , Department location is dept in 
--  analysis ： Pay scale   The information in  salarygrade  In the salary scale 

-- job and emp It's a one-to-many relationship ！（ Two meters should be connected （ Internal connection ））emp.job_id = job.id
-- dept and emp It's a one-to-many relationship ！（ Two meters should be connected （ Internal connection ））dept.id = emp.dept_id
--  How to determine the grade ：emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
--  Four meter connection 

SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc,
	t2.* 
FROM
	emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON emp.dept_id = dept.id
INNER JOIN salarygrade t2 ON emp.salary >= t2.losalary 
AND emp.salary <= t2.hisalary;

5、

---- 5. Find out the department number 、 Department name 、 Department position 、 Number of departments 
--  analysis ： Department number 、 Department name 、 Department location is dept In the table 
--  analysis ： How to determine the number of departments ——-（ Multiple departments have corresponding numbers , It is not difficult to think of grouping , Then count ）
--  Number of departments ： stay emp In the list of employees , according to dept_id Grouping , then count(*) Count 
--  According to the Department id Query each department in groups id And number of employees , Subquery 

-- select dept_id, count(*) from emp GROUP BY dept_id;  A virtual table with multiple rows and columns 
SELECT
	dept.id,
	dept.dname,
	dept.loc,
	t1.count 
FROM
	( SELECT dept_id, count(*) count FROM emp GROUP BY dept_id ) t1
	INNER JOIN dept ON dept.id = t1.dept_id;
	

Reference learning ： The latest version of dark horse programmer JavaWeb Basic course ,Java web From introduction to enterprise actual combat full version _ Bili, Bili _bilibili