1. Create a new database companyx, all the existing the relations implied from tables shown and populate it. 2. From the Wamp, export the PDF that shows all tables and their relations. 4 4 EMPLOYEE Fname Minit Lname San Bdate Address Sex Salary Super_ssn Dno John B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Franklin I Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 8886655555 Alicia Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 5 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 5 Ahmad v Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 4 4 DEPARTMENT Dname Research Administration Headquarters Dnumber 5 4 Mgr_ssn 333445555 987654321 888665555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 DEPT_LOCATIONS Dnumber Dlocation 1 Houston 4 Stafford 5 Bellaire Sugarland 5 Houston 1 5 Pno Hours Pnumber Dnum 1 32.5 1 5 WORKS ON Essn 123456789 123456789 666884444 453453453 453453453 2 2 5 PROJECT Pname ProductX ProductY Productz Computerization Reorganization Newbenefits 7.5 40.0 20.0 ooom 3 Plocation Bellaire Sugarland Houston Stafford Houston Stafford 3 5 1 10 4 2 20.0 20 1 333445555 2 10.0 30 4 3 10.0 333445555 333445555 10 10.0 333445555 20 10.0 30 30.0 F 999887777 999887777 10 10.0 987987987 10 35.0 DEPENDENT Essn 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Dependent_name Sex Alice Theodore M Joy F Abner M Michael M Alice Elizabeth F 987987987 30 5.0 Bdate Relationship 1986-04-05 Daughter 1983-10-25 Son 1958-05-03 Spouse 1942-02-28 Spouse 1988-01-04 Son 1988-12-30 Daughter 1967-05-05 Spouse 30 20.0 987654321 987654321 20 15.0 TITIS 888665555 20 NULL 2. Implement the following queries: 1. Find the son and the last name for all employees who work in the same project as employee with ssn=123456789. The employee with ssn 123456789 should be excluded from the results. Write 2 queries, one query with JOIN. 2. same as 1 but without JOIN. 3. For each project that employees work on, get the project number and the employee last name. Use a left outer join with the left table being the works_on. Reproduce the results with a query that is not using the keyword JOIN. 4. Do number 3 but with right outer join, instead or left outer. 5. Create a cross product of employee and works on using the keyword CROSS. 6. Crete a NATURAL join of employee and works_on 7 How many employees work in a department, on average? 8. For each department, get the department number, and the number of employees that work for that department. 9. For each employee, get the ssn, last name, count of all projects that he is working, and count of dependents he may have. 10. For each department having a manager who lives in Houston, get the department number and the number of employees who are working in at least one project that belongs to that department. 11. For each employee, get the ssn, last name, and difference between the year in which he or she was born and the average year of all birthdays. 12. For each employee, get the ssn, last name name, and difference between the year in which he or she was born and the average year of all birthdays for employees who live in the city of Houston. 13. For each department find the sum of all of its salaries the average salary of the department and the percentage of the departmental total salary compared to all salaries. DNO SUM AVG TOTAL PERCENTAGE 1 $100,000 $33,000 $1,000,000 10% 14. What managers have more than 2 employees working for them? 15. Get the ssn for each employee who works for than 25 hours total. 16. Get the first name, last name and the number of projects an employee works-on for employees who work in two projects or more. 1. Create a new database companyx, all the existing the relations implied from tables shown and populate it. 2. From the Wamp, export the PDF that shows all tables and their relations. 4 4 EMPLOYEE Fname Minit Lname San Bdate Address Sex Salary Super_ssn Dno John B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Franklin I Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 8886655555 Alicia Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 5 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 5 Ahmad v Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 4 4 DEPARTMENT Dname Research Administration Headquarters Dnumber 5 4 Mgr_ssn 333445555 987654321 888665555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 DEPT_LOCATIONS Dnumber Dlocation 1 Houston 4 Stafford 5 Bellaire Sugarland 5 Houston 1 5 Pno Hours Pnumber Dnum 1 32.5 1 5 WORKS ON Essn 123456789 123456789 666884444 453453453 453453453 2 2 5 PROJECT Pname ProductX ProductY Productz Computerization Reorganization Newbenefits 7.5 40.0 20.0 ooom 3 Plocation Bellaire Sugarland Houston Stafford Houston Stafford 3 5 1 10 4 2 20.0 20 1 333445555 2 10.0 30 4 3 10.0 333445555 333445555 10 10.0 333445555 20 10.0 30 30.0 F 999887777 999887777 10 10.0 987987987 10 35.0 DEPENDENT Essn 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Dependent_name Sex Alice Theodore M Joy F Abner M Michael M Alice Elizabeth F 987987987 30 5.0 Bdate Relationship 1986-04-05 Daughter 1983-10-25 Son 1958-05-03 Spouse 1942-02-28 Spouse 1988-01-04 Son 1988-12-30 Daughter 1967-05-05 Spouse 30 20.0 987654321 987654321 20 15.0 TITIS 888665555 20 NULL 2. Implement the following queries: 1. Find the son and the last name for all employees who work in the same project as employee with ssn=123456789. The employee with ssn 123456789 should be excluded from the results. Write 2 queries, one query with JOIN. 2. same as 1 but without JOIN. 3. For each project that employees work on, get the project number and the employee last name. Use a left outer join with the left table being the works_on. Reproduce the results with a query that is not using the keyword JOIN. 4. Do number 3 but with right outer join, instead or left outer. 5. Create a cross product of employee and works on using the keyword CROSS. 6. Crete a NATURAL join of employee and works_on 7 How many employees work in a department, on average? 8. For each department, get the department number, and the number of employees that work for that department. 9. For each employee, get the ssn, last name, count of all projects that he is working, and count of dependents he may have. 10. For each department having a manager who lives in Houston, get the department number and the number of employees who are working in at least one project that belongs to that department. 11. For each employee, get the ssn, last name, and difference between the year in which he or she was born and the average year of all birthdays. 12. For each employee, get the ssn, last name name, and difference between the year in which he or she was born and the average year of all birthdays for employees who live in the city of Houston. 13. For each department find the sum of all of its salaries the average salary of the department and the percentage of the departmental total salary compared to all salaries. DNO SUM AVG TOTAL PERCENTAGE 1 $100,000 $33,000 $1,000,000 10% 14. What managers have more than 2 employees working for them? 15. Get the ssn for each employee who works for than 25 hours total. 16. Get the first name, last name and the number of projects an employee works-on for employees who work in two projects or more