QuestionQuestion

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.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

-- 1. Retrieve the birthdate address of the employee whose name is 'John B. Smith'.

SELECT e.BDATE, e.ADDRESS FROM employee e
where e.FNAME = 'John' and e.LNAME = 'Smith' and e.MINIT = 'B';

-- 2. Retrieve the name and address of all employees who work for the 'Research' department.

SELECT
CONCAT(CONCAT(e.FNAME, CONCAT(' ',
          CONCAT(e.MINIT, '. ')
          )), e.LNAME ) as "NAME" ,
e.ADDRESS
FROM EMPLOYEE e INNER JOIN DEPARTMENT d
on e.DNO = d.DNUMBER
where d.DNAME = 'Research';

-- 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.

SELECT s.PNUMBER, s.DNUM,
e.LNAME as "MGR_NAME", e.ADDRESS, e.BDATE
FROM EMPLOYEE e inner join
( SELECT p.PNUMBER, p.DNUM, d.MGRSSN
      FROM PROJECT p INNER JOIN DEPARTMENT d
      on p.DNUM = d.DNUMBER
      WHERE p.PLOCATION = 'Stafford'
   ) s
   on e.SSN = s.MGRSSN
    ;...
$100.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats