You were tasked to create an Oracle database for a company to track their employ

Question

You were tasked to create an Oracle database for a company to track their employees and projects. After speaking with the customer, you have determined the following as the user’s needs


The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department.

A department may have several locations.

A department controls a number of projects, each of which has a unique name, a unique number, and a single location
They store each employee’s name, social security number, address, salary, sex, and birth date. An employee is assigned to one department but may work on several projects, which are not necessarily controlled by the same department. We keep track of the number of hours per week than an employee works on each project. We also keep track of the direct supervisor of each employee.
They want to keep track of the dependents of each employee for insurance purposes. We keep each dependent’s first name, sex, birth date, and relationship to the employee.


Question: Create the following the following queries:


List all the employees (Name, SSN) and their dependents (Name, Birthdate). (Make sure you list Employees who don’t have dependents).
List the employees (Names) and the projects that they work on.
List the employees (Name, SSN) and their Manager (Names, SSN)
List the employee Name of who makes the most by department.
List the project Name which has the most hours logged.
List the employee Name who has the most dependents
List the employees, their departments, and their projects.
List the total number of hours worked per department. Show department Name in your results.

Explanation / Answer

       SQL> Select e_name,SSN_number,name as manager_name
                  From employee,manager;

4.List the project Name which has the most hours logged.

      SQL> Select P_name,MAX(WORK_hour) as Most_hours

     SQL> Select e_name,dept_name,P_name
                From employee e,department d,project p
                where e.SSN_number=d.SSN_number and d.SSN_number==p.SSN_number ;

6.List the total number of hours worked per department. Show department Name in your results.

     SQL> Select dept_name,sum(work_hours) as number_of_hours
              From department
               order by dept_name;

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.