Reference no: EM132171791

Using MySQL and the Company schema.

1. Using the Company schema, provide a query that lists all employees along with any dependents. Include employees without dependents in the result set.

List the employee first name, last name, ssn, and the dependent's name and relationship if a dependent exists.

Use the explicit joins.

Expected Result Set Size: 47

2. Using the Company schema, select the unique project numbers and project names where either the project department's manager is ‘Freed' or an employee's last name is ‘Freed'.

Use explicit join notation where necessary.

Nested selects may be needed in this case.

Expected Result Set Size: 2

3. Using the Company schema, list every project name and average employee salary for employees in the project.

Use explicit joins.

Note that the avg salary for project 61 (OperatingSystems) is 42,166.

Expected Result Set Size: 11 i.e. the number of projects.

Schema Name: Company

Table Name: employee

Column Names: fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno

Table Name: dependent

Column Names: essn, dependent_name, sex, bdate, relationship

Table Name: dept_locations;

Column Names: dnumber, dlocation

Table Name: department

Column Names: dnumber, dname, mgrssn, mgrstartdate

Table Name: project

Column Names: pname, pnumber, plocation, dnum

Table Name: works_on

Column Names: essn, pno, hours