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,
Final review of database : Summary of comprehensive application questions Laplance operator ( Second derivative ) Simple learning of computer composition principle pyqt Button call python program _PyQt: Link button to function in program How much can you go up once you change jobs ? Today, I saw the ceiling of job hopping python in str Function usage _python in str Usage Summary of built-in functions MySQL trigger web The server nginx---linux Installation and deployment C++ Chapter V polymorphism exercises :( It's coming to an end )python Check built-in functions , How to check python Built in function