SQL Help for Beginner please

Question

I am learning SQL at university. Stuck one a question and any help would be appreciated.

Question:

Write a SQL statement to retrieve the first and last name as a column “fullname” of the employees which manage 2 or more projects (2pts).

Background info:

EMPLOYEE Table with 2 entries, PROJECT Table with 4 project tables. Last column is ProjectManager which has the Employee ID. Two of the projects are managed by the same employee.

What I have:

 Select EMPLOYEE.FirstName+','+EMPLOYEE.LastName AS FullName
 FROM EMPLOYEE
 WHERE count(PROJECT.ProjectManager==EMPLOYEE.EmployeeID) > 1

EDIT: Sorry for the confusion guys. Its a PROJECT table with 4 records. I needed to find the first and last name of the Employee whose ID was listed under 2 different project records. The employee ID went in the ProjectManager column. The answer Serif Emek gave seems to be what I needed.

`PROJECT Table with 4 project tables` wait - what? Does it mean 4 entries in the project table? Where is the schema for each table? Sounds like you need to join the two tables together. — Bridge, Feb 07 '15 at 00:26
Please [edit] your question title to something that describes the problem you're asking us to help you solve. The title should be something that will convey information to a future reader of this site who finds it in a search result. The fact you need help is clear, because you've asked a question here, and *SQL help for beginner please* has no meaningful information. Explain what *PROJECT table with 4 project tables* means, and explain the problem with what you've posted. Does it work? If not, in what way does it not work? Does it return the wrong content? Does it return no content? — Ken White, Feb 07 '15 at 00:33

score 1 · Accepted Answer · answered Feb 07 '15 at 00:45

1

That may help;

Select E.FirstName+','+E.LastName AS FullName
 FROM EMPLOYEE E, PROJECT P
 WHERE 
E.EmployeeId = P.ProjectManager
GROUP BY E.FirstName,E.LastName, E.EmployeeId 
HAVING COUNT(*) > 1

answered Feb 07 '15 at 00:45

Serif Emek

674
5
13

score 1 · Answer 2 · edited May 23 '17 at 12:20

1

Go with the ANSI standard version

SELECT e.FirstName + ',' + e.LastName AS FullName
  FROM employee AS e
  INNER JOIN project AS p
    OM e.EmployeeId = p.ProjectManager
  GROUP BY e.FirstName, e.LastName, e.EmployeeId
  HAVING COUNT(*) >= 2;

See also: ANSI vs. non-ANSI SQL JOIN syntax

edited May 23 '17 at 12:20

Community

1
1

answered Feb 07 '15 at 03:31

Mike Harris

869
9
21

ANSI SQL has || for concat... And select ... from table1, table2 is ANSI SQL, just an older version of the syntax - but still supported! – jarlh Feb 09 '15 at 08:28

SQL Help for Beginner please

2 Answers2