Submit query statements and query execution results with screen shoots for each
ID: 3826100 • Letter: S
Question
Submit query statements and query execution results with screen shoots for each question. Setup the database and input the data. You must implement the referential constraints if necessary.: 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.
Explanation / Answer
Solution:
1)
SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME= 'John' AND MNIT= 'B' AND LNAME= 'Smith'
The above query will retreive the birthdate address of the employee whose name is 'John B. Smith'
2)
SELECT FNAME, ADDRESS FROM EMPLOYEE INNER JOIN DEPARTMENT ON DNO= DNUMBER WHERE DNO= '5'
The above query will retrieve the name and address of all employees who work for the 'Research' department.
3)
SELECT PNUMBER, DNUMBER, LNAME, ADDRESS, BDATE FROM EMPLOYEE INNER JOIN DEPARTMENT ON DNO= DNUMBER INNER JOIN PROJECT ON DNUM= DNUMBER INNER JOIN WORKS_ON ON PNUMBER= PNO WHERE DLOCATION= 'Stafford'