VR Startup Companies

I noticed there was no topic about this assignment, so I decided to create one for all questions about it so we can help each other out.

I’m having trouble with the last step of the project. Is there an easier way to copy the table of incompatible workers without having it reset to the beginning of the table? Thanks.

Hello! Could you post a link to the exercise, please?

https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-join-data/modules/analyze-data-sql-practice-joins/projects/sql-multiple-tables-with-a-vr-startup

Step 12.

Here is my solution to task 12. My mistake was not to add a comma at the end of line 4.

SELECT employees.first_name,
  employees.last_name,
  employees.personality,
  projects.project_name,
  CASE
    WHEN employees.personality = 'INFP'
    THEN (SELECT COUNT(*)
      FROM employees
      WHERE personality IN ('ISFP', 'ESFP', 'ISTP', 'ESTP', 'ISFJ', 'ESFJ', 'ISTJ', 'ESTJ')
    )
    WHEN employees.personality = 'ENFP'
    THEN (SELECT COUNT(*)
      FROM employees
      WHERE personality IN ('ISFP', 'ESFP', 'ISTP', 'ESTP', 'ISFJ', 'ESFJ', 'ISTJ', 'ESTJ')
    )
    WHEN employees.personality = 'INFJ'
    THEN (SELECT COUNT(*)
      FROM employees
      WHERE personality IN ('ISFP', 'ESFP', 'ISTP', 'ESTP', 'ISFJ', 'ESFJ', 'ISTJ', 'ESTJ')
    )
    WHEN employees.personality = 'ENFJ'
    THEN (SELECT COUNT(*)
      FROM employees
      WHERE personality IN ('ESFP', 'ISTP', 'ESTP', 'ISFJ', 'ESFJ', 'ISTJ', 'ESTJ')
    )
    WHEN employees.personality = 'ISFP'
    THEN (SELECT COUNT(*)
      FROM employees
      WHERE personality IN ('INFP', 'ENFP', 'INFJ')
    )
    WHEN employees.personality = 'ESFP'
    THEN (SELECT COUNT(*)
      FROM employees
      WHERE personality IN ('INFP', 'ENFP', 'INFJ', 'ENFJ')
    )
    WHEN employees.personality = 'ISTP'
    THEN (SELECT COUNT(*)
      FROM employees
      WHERE personality IN ('INFP', 'ENFP', 'INFJ', 'ENFJ')
    )
    WHEN employees.personality = 'ESTP'
    THEN (SELECT COUNT(*)
      FROM employees
      WHERE personality IN ('INFP', 'ENFP', 'INFJ', 'ENFJ')
    )
    WHEN employees.personality = 'ISFJ'
    THEN (SELECT COUNT(*)
      FROM employees
      WHERE personality IN ('INFP', 'ENFP', 'INFJ', 'ENFJ')
    )
    WHEN employees.personality = 'ESFJ'
    THEN (SELECT COUNT(*)
      FROM employees
      WHERE personality IN ('INFP', 'ENFP', 'INFJ', 'ENFJ')
    )
    WHEN employees.personality = 'ISTJ'
    THEN (SELECT COUNT(*)
      FROM employees
      WHERE personality IN ('INFP', 'ENFP', 'INFJ', 'ENFJ')
    )
    WHEN employees.personality = 'ESTJ'
    THEN (SELECT COUNT(*)
      FROM employees
      WHERE personality IN ('INFP', 'ENFP', 'INFJ', 'ENFJ')
    )
    ELSE 0
  END AS 'incompatible_count'
  FROM employees
  LEFT JOIN projects
    ON employees.current_project = projects.project_id;

hi , can you please explain why have you chosen only 8 personality types , when there are 15 to compare?

There are 12 personality types, not 12 and all of them are chosen. Please provide the number of the line you’re talking about if you need further assistance

Hello

For the sake of sharing and exchanging ideas, here’s my solution to question 5:

SELECT project_name
FROM projects
WHERE project_id IS (
SELECT current_project
FROM employees
WHERE current_project IS NOT NULL
GROUP BY current_project
ORDER BY COUNT(*) DESC
LIMIT 1);

1 Like

Thanks!
I had pretty much the same, but couldn’t figure out why mine was wrong.
Turned out that I hade missed to put quotation marks around EACH of the personality types. Obvious when you see it. Not so easy without error messages :slight_smile:

I’d be very happy if this could help you. If you have further questions, please don’t hesitate to ask me. Happy coding :slight_smile:

Hello!

I’m in doubt with question 11. It asks: “Find the personality type most represented by employees with a selected project. What are names of those employees, the personality type, and the names of the project they’ve chosen?”

Using the following query, I see that there are 3 employees with personality ISTJ and 3 with personality ENFJ, so they are both equally represented:

SELECT personality, COUNT (personality)
FROM employees
WHERE current_project IS NOT NULL
GROUP BY personality
ORDER BY COUNT(personality) DESC;

But when I try to use the query in the hint (below), it gives me the results only for the personality tipe ISTJ:

SELECT last_name, first_name, personality, project_name
FROM employees
INNER JOIN projects 
  ON employees.current_project = projects.project_id
WHERE personality = (
   SELECT personality 
   FROM employees
   WHERE current_project IS NOT NULL
   GROUP BY personality
   ORDER BY COUNT(personality)DESC
   LIMIT 1);

Why don’t I see also the data for the personality ENFJ, since both personalities are the most represented?
I thought it was for “LIMIT 1” in the last row, but if I remove it or change it, the results are the same.

Thanks!

1 Like

I was curious about this one too, I believe the hint code you included is slightly deceptive because = is used for the comparison, try using IN instead (as well as altering the limit).

1 Like

Hello! I have a curiosity about exercise 12.
The task says: “For each employee, provide their name, personality, the names of any projects they’ve chosen, and the number of incompatible co-workers.”. The query in the hint is the one pasted below.
But what should I do if, instead of only asking for the number of incompatible co-workers, I would also want to know their names? Would that be possible?
I tried to write “THEN (SELECT first_name, COUNT…” but of course it didn’t work.
Does anyone have an idea on how to do it? Thank you!

SELECT last_name, first_name, personality, project_name,
CASE 
   WHEN personality = 'INFP' 
   THEN (SELECT COUNT(*)
      FROM employees 
      WHERE personality IN ('ISFP', 'ESFP', 'ISTP', 'ESTP', 'ISFJ', 'ESFJ', 'ISTJ', 'ESTJ'))
   WHEN personality = 'ISFP' 
   THEN (SELECT COUNT(*)
      FROM employees 
      WHERE personality IN ('INFP', 'ENTP', 'INFJ'))
   -- ... etc.
   ELSE 0
END AS 'IMCOMPATS'
FROM employees
LEFT JOIN projects on employees.current_project = projects.project_id;

I used Rank() to solve the problem. In the results, you could see ISTJ and ENFJ both.

SELECT last_name, first_name, personality, project_name

FROM employees

INNER JOIN projects

ON employees.current_project = projects.project_id

WHERE personality in (

WITH personality_rank as

(SELECT personality, COUNT(personality), RANK() OVER(ORDER BY COUNT(personality) DESC) AS personality_ranking

FROM employees

WHERE current_project IS NOT NULL

GROUP BY 1)

SELECT personality

FROM personality_rank

WHERE personality_ranking = 1);

For Q12 - I pulled out the paper and pen and mapped out command would work. I figured it would be using case but couldnt quite get the second part within the case statement without looking a the hint

Questions…

  1. is the second part of the case statement (in bold below) is this considered a sub query? or just a statement using an IN clause?

select first_name, last_name, personality, project_name,
CASE
WHEN PERSONALITY = ‘INFP’ THEN
(Select count (*)
FROM employees
WHERE personality IN (‘ISFP’, ‘ESFP’, ‘ISTP’, ‘ESTP’,‘ISFJ’, ‘ESFJ’))

  1. Am I correct in thinking this could be done using OR statement in the CASE statement (for the personality traits) - but using in just makes it quicker?

  2. It still required a lot of manual effort to build each query - any quicker solutions? (more SQL based vs creating vlookups in Excel)

  3. Has anyone found any completely different solutions? Using Cross Joins or creating a new table? Just curious.

I thought this was a tough but really rewarding questions which opened my eyes to how powerful SQL could be

I have a question on Q5… currently the code is:

SELECT project_name

FROM projects

JOIN employees

ON projects.project_id = employees.current_project

WHERE current_project IS NOT NULL

GROUP BY project_name

ORDER BY COUNT(employee_id) DESC;

why is it projects.project_id in the ON statement instead of project_name when that is indeed the primary key?

and then why is it SELECT project_name … I thought it would be – projects.project_name since we’re doing an inner join