Instructions: The following questions are base on the schema. Database Schema: Setup the database and input the data. You must implement the referential constraints if necessary. Complete the following queries: 1. Retrieve the birthdate address of the employee whose name is 'John B. Smith'. 2. Retrieve the name and address of all employees who work for the 'Research' department. 3. For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate. 4. For each employee, retrieve the employee's first and last name and the first and last name of the his or her immediate supervisor. 5. Select all of combinations of EMPLOYEE SSN and DEPARTMENT DNAME in the database. 6. Retrieve the salary of every employee. 7. Make a list of all project numbers for projects that involve an employee whose last name is 'Smith', either as a worker or as a manager of the department that controls the project. 8. Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee. 9. Retrieve the name of employees who have no dependents. 10. List the name of managers who have at least one dependent. 11. Retrieve the names of all employees who do not have supervisors. 12. Find the sum of the salaries of all employees of the 'Research' department, as well as the maximum salary, the minimum salary, and the average salary in this department. 13. Count the number of distinct salary values in the database. 14. Retrieve the names of all employees who have two or more dependents. 15. For each department, retrieve the department number, the number of employees in the department, and their average salary. 16. For each project, retrieve the project number, the project name, the number of employees who work on that project. 17. For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on that project. 18. For each project, retrieve the project number, the project name, and the number of employees from department 5 who work on the project. 19. Retrieve all employees whose address is in Houston, Texas. 20. Find all employees who were born during the 1950s. Submit query statements and query execution results for each question by screen shoots.
- 6 years ago
Purchase the answer to view it