data sheet USERS,DEPT, The data are as follows :

USERS surface :

DEPT surface :

1, Query all users and display their department name , The results are as follows :

Department name full name
Archives Center user 1
… …
 

sql:

select a.DEPTNAME as Department name ,b.USERNAME as user from USERS a inner join DEPT b on
a.ID=b.DEPT_ID;

2, Name the user department as " President's Office " The sex of " female " Employee transfer in " Archives Center "

sql:update DEPT set DEPT_ID=1 where DEPT_ID=4 and SEX=" female ";

3, It is found that all department personnel exceed 1 Person's department and display the number of people in the Department

Department name Number of people
Archives Center 2
… …
 

having :HAVING Equivalent to conditional screening , But it's not WHERE Screening is different ,HAVING It's for GROUP BY Object to filter

4, Count the number of male and female employees in each department , The results are as follows

Department name male female
Archives Center 2 2
… … …
 

sql : select a.DEPTNAME as department ,sum(case when b.SEX=' male ' then 1 else 0 end ) as
male ,sum(case when SEX=' female ' then 1 else 0 end ) female from USERS a inner join DEPT b on
a.ID=b.DEPT_ID group by a.DEPTNAME;

Technology
©2019-2020 Toolsou All rights reserved,
Share has surpassed Ningde Era !LG Chemical confirmation to spin off battery business unit Summary of artificial intelligence algorithm ( Essence )2020 year 6 month 26 day C# Class library Exception handling help class ( Essence )2020 year 7 month 13 day Wechat applet Inter communication page ( Essence )2020 year 8 month 2 day TypeScript The use of generics ( Essence 2020 year 6 month 2 Daily update ) TypeScript Function explanation Unity3D Input Key system Shortest path Dijkstra (Python3)Element-UI Implementation of secondary packaging TreeSelect Tree drop-down selection component SpringMVC Frame in controller Layer gets the property value of the custom configuration file