For these exercises, we use the Employees Database presented in the other pictur

Question

For these exercises, we use the Employees Database presented in the other picture. Answer each question with a single SQL statement. Your query must work for any set of data in the Employees Database, not just the set of data we provide.

1. Find the names of all people who work in the Consulting department and who spend more than 20% of their time on the project with ID ADT4MFIA. Solve three ways:

A) using only WHERE-based join (i.e., no INNER/OUTER/CROSS JOIN),

B) using JOINON

C) using NATURAL JOIN whenever possible and JOIN ON otherwise.

2. Find the total percentage of time assigned to employee Abe Advice. Solve it two ways:

A) using only WHERE-based join (i.e., no INNER/OUTER/CROSS JOIN) and 2) using some form of JOIN.

3. Find the descriptions of all projects that require more than 70% of an employee’s time. Solve it two ways:

A) using only WHERE-based join (i.e., no INNER/OUTER/CROSS JOIN)

B) using some form of JOIN.

4. For each employee ID, find the last name of all employees making more money than that employee. Solve it two ways:

A) using only WHERE-based join (i.e., no INNER/OUTER/CROSS JOIN)

B) using some form of JOIN.

5. Rank the projects by revenue. Solve it two ways:

    A) using only WHERE-based join (i.e., no INNER/OUTER/CROSS JOIN)

B) using some form of JOIN.

Tools Donahoo-SQL P. x 24 Chapter 1: Databasics departments code projects projectid deptcodexname description startdate enddate managerid subdeptof T workson projectid revenue employeeidH assignedtime employees employeed firstname astname deplcode X salary Figure 1.5: Schema for the Employees Database

Explanation / Answer

Solution:

The first question is done as per Chegg guidelines, please repost others. ?

1)

A)

SELECT firstname FROM employees. workson, projects, department WHERE (employees.employeeid= workson.employeeid) AND (workson.projectid= projects.projectid) AND (projects.deptcode= department.code) AND assingedtime='20' AND projectid= 'ADT4MFIA'

B)

SELECT firstname FROM employees INNER JOIN workson ON (employees.employeeid= workson.employeeid) INNER JOIN projects ON (projects.deptcode= department.code) INNER JOIN department ON (workson.projectid= projects.projectid) WHERE assingedtime='20' AND projectid= 'ADT4MFIA'

C)

SELECT firstname FROM employees FULL OUTER JOIN workson ON (employees.employeeid= workson.employeeid) FULL OUTER JOIN projects ON (projects.deptcode= department.code) FULL OUTER JOIN department ON (workson.projectid= projects.projectid) WHERE assingedtime='20' AND projectid= 'ADT4MFIA'

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---