SQL relational database
A relational database contains the following tables:
Employee (EmployeeNo, EmployeeName, DateOfBirth, JobDescription,DeptNo)
Project (ProjectNo, ProjectName, Location)Assignment (EmployeeNo, ProjectNo, Role-in-Project)Formulate the following using SQL:i. Find the names of all employees assigned to a project. List the employee
names in an alphabetical order.
Software/Hardware used:
Software/Hardware used:
ASKED:
March 14, 2009 10:08 PM
UPDATED:
March 17, 2009 2:25 PM
Answer Wiki:
You could do something like this:
SELECT e.EmployeeName
FROM Employee e
INNER JOIN Assignment a ON e.EmployeeNo=a.EMployeeNo
INNER JOIN Project p ON p.ProjectNo=a.ProjectNo
WHERE p.ProjectName='_specific_projectname_'
ORDER BY e.EmployeeName
If you're looking for all employees for all existing projects, then:
SELECT p.ProjectName,e.EmployeeName
FROM Employee e
INNER JOIN Assignment a ON e.EmployeeNo=a.EMployeeNo
INNER JOIN Project p ON p.ProjectNo=a.ProjectNo
WHERE p.ProjectName='_specific_projectname_'
ORDER BY p.ProjectName,e.EmployeeName,
To see all answers submitted to the Answer Wiki: View Answer History.
You need to join the three tables together, on EmployeeNo and ProjectNo, and use an ORDER BY statement to sort the data.
No I won’t give you the SQL answer your to your school assignment.
Moderator Note: Denny is exactly right. IT Knowledge Exchange isn’t a place to have others answer your school assignments. But if you’re studying for a career in IT or development and have run into a problem after attempting to solve it yourself – then post your code or a detailed explanation and maybe someone can point out the problem – or at least point you in the right direction (as Denny did above). I see this is your first question, so welcome to IT Knowledge Exchange. Thanks.