VR Startup Companies

gguirao13 · January 10, 2021, 5:56am

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.

gguirao13 · January 10, 2021, 8:26am

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.

codeneutrino · January 10, 2021, 8:52am

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

gguirao13 · January 11, 2021, 7:02am

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.

autobahnmaus · January 26, 2021, 7:58pm

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;

dev3361858438 · February 6, 2021, 10:44am

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

autobahnmaus · February 6, 2021, 6:08pm

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

chip7082973286 · February 8, 2021, 2:53pm

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);

agebrand · February 11, 2021, 9:26am

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

autobahnmaus · February 12, 2021, 11:27am

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

giga1784752554 · March 11, 2021, 7:59am

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!

tgrtim · March 11, 2021, 12:25pm

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).

giga1784752554 · March 16, 2021, 8:19am

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;

redstar_yy · April 8, 2021, 10:55pm

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);

ednavas9655736681 · April 24, 2021, 11:43pm

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…

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’))

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?
It still required a lot of manual effort to build each query - any quicker solutions? (more SQL based vs creating vlookups in Excel)
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

dev1701581888 · April 28, 2021, 8:08pm

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?

dev1701581888 · April 28, 2021, 8:24pm

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

gguirao13 · June 2, 2021, 8:39pm

How do you keep track of the table of personality types? It keeps resetting back to its starting position and reading each line is very difficult.

engima_codewise · June 19, 2021, 9:16am

In answer to question two, I used OR statements for personality types that had the same incompatibility after noticing the repetition.

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', 'ISTJ', 'ESTJ'))
  WHEN personality = 'ENFP' 
  THEN (
    SELECT COUNT(*)
    FROM employees
    WHERE personality IN ('ISFP', 'ESFP', 'ISTP', 'ESTP', 'ISFJ', 'ESFJ', 'ISTJ', 'ESTJ'))
  WHEN personality = 'INFJ' 
  THEN (
    SELECT COUNT(*)
    FROM employees
    WHERE personality IN ('ISFP', 'ESFP', 'ISTP', 'ESTP', 'ISFJ', 'ESFJ', 'ISTJ', 'ESTJ'))
  WHEN personality = 'ENFJ' 
  THEN (
    SELECT COUNT(*)
    FROM employees
    WHERE personality IN ('ESFP', 'ISTP', 'ESTP', 'ISFJ', 'ESFJ', 'ISTJ', 'ESTJ'))
  WHEN personality = 'ISFP' 
  THEN (
    SELECT COUNT(*)
    FROM employees
    WHERE personality IN ('INFP', 'ENFP', 'INFJ')
    )
  WHEN personality = 'ESFP' OR personality = 'ISTP' OR personality = 'ESTP' OR personality = 'ISFJ' OR personality = 'ESFJ' OR personality = 'ISTJ' OR personality = 'ESTJ'
   THEN (
    SELECT COUNT(*)
    FROM employees
    WHERE personality IN ('INFP', 'ENFP', 'INFJ', 'ENFJ'))
  ELSE 0
END AS 'INCOMPATIBLE'
FROM employees
LEFT JOIN projects 
 ON employees.current_project = projects.project_id;

system · July 31, 2021, 1:16am

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.