Database Systems Assignment - SQL
Construction Database -
The construction company database maintains information on employees, departments, projects, categories and employee assignments.
The tblEmployee table maintains information relating to an employee including the department they belong to and their supervisor.
The tblDepartment table stores information relating to full name of each department The tblCategory table lists all the different types of sectors a project can belong to. The tblProject table stores information relating to projects, the employee who is its overall supervisor and the category it belongs to.
The tblAssigned table maintains information relating to employees and the time they have spent on a particular project. It maintains the number of days an employee has worked on a project and their hourly rate for project.
Questions
1. Display all employee details for those employees that receive a salary in the range of 75,000 and 95,000 dollars inclusive. Order the output by Surname in descending order.
2. What is the surname and first name of the employee that receives the highest salary?
3. List the complete details of all the employees that have been assigned to the department called 'Administration'. Order the output by employee surname in ascending order.
4. Display the project number, title and total cost of all projects whose project title starts with the letter 'H'.
5. What is the title of each project that has not started yet?
6. What is the title and total cost of each project that has started but has not been completed?
7. List the full details of all employees that have been assigned to the project with project number 'E11'
8. What are the titles of the projects that employee 'Bill Smith' has worked on?'
9. How many projects are there in each category? Display the category and the count. Rename the count as 'Total Number' Order the output by category in descending order.
10. For each project display the project number, project title and the employee number, first name and surname of the employee who supervises the project.
11. What is the project title, employee number, first name and surname of the employee who has been assigned to a project for the longest duration?
12. Display a employee number, surname and first name of the employees that do not supervise a project.
13. For each employee that supervises a project, display their surname and first name and the number of project they supervise. Order the output by employee surname in descending order.
14. For each employee assigned to a project display their employee number, duration, charge rate and the their total fee. Total fee is a calculation based on the multiplication of an employee's duration time and hourly charge rate for a specific project.. The heading for this calculated field is to be 'Total Fee''
15. Which employees supervises more than 3 projects? Display only the employee number and count. Rename the count to "Projects Supervised"
16. For each employee display their employee number, first name and surname and the employee number first name and surname of their managers.
17. For each project assignment display the project number, project title, employee number, first name, surname, and the full name of the department they belong to.
18. What is the sum of all project total costs? Label the heading as 'Total Costs'
19. What is the first name and surname of the employee with the second lowest salary?
20. List all the details of employees who are not assigned to the Construction department.
21. For each employee display the number of projects they have worked on? Show the employee number and count only.
22. How many hours(duration in total) has employee Anne Smith worked on projects?
23 What are the names of the employees that have worked on the Melbourne Airport project? Display the employee number, surname, first name , project name and start and end dates.
24. Which projects(Project Title) have had more than 5 employees work on it?
25. For each project what is the sum of all employee costs? Display the project name and sum of all employee costs. Rename this field as 'Sum of Costs'.
26. What is the name of the employee that has the highest charge rate for any project?
27 An employee's room is made up of three components Building letter, level and room number. For example K123 refers to building K, level 1 and room 23. Display all the employee details of those employee that have a room on level 3.
28. Which projects were completed in the year 2003?
Attachment:- Assignment Files.rar