A company has several employees and projects. Each employee may be working on several projects. Design a database to hold information about projects and employees, choosing some sensible sample fields and data. Write an SQL query to display the number of women and the number of men working on each project (i.e. two values; women and men).
Provide SQL CREATE TABLE statements showing the structure, and some sample data, as well as the SQL query.
I did this:
CREATE TABLE employee (
process_id INT(10) NOT NULL,
emp_first_name VARCHAR(100) NOT NULL,
emp_last_name VARCHAR(100) NOT NULL,
emp_sex CHAR NOT NULL,
emp_project VARCHAR(100)
)
SELECT DISTINCT emp_project, (SELECT COUNT(emp_sex) FROM emp1 WHERE emp_sex='men') AS "Total Men",(SELECT COUNT(emp_sex) FROM emp1 WHERE emp_sex='women') AS "Total Women" FROM emp1 GROUP BY emp_projectBut it is not nested SELECT so it will always show the same count no.