Need to write a SQL Query F,G,and H in a SQL Assignment This is a simplified ver

Question

Need to write a SQL Query F,G,and H in a SQL Assignment

This is a simplified version of the Access Workbench Exercise in the textbook. Follow along in the textbook for more detailed descriptions and pictures, but only the columns and data shown in this assignment are needed.

Use your WPC database to create, run, and save queries using Microsoft Access SQL

SQL Queries: Using Microsoft Access SQL, create and run queries to answer the questions that follow. Save each query using the query name format SQLQuery-#, where the # sign is replaced by the letter of the question.

Hint: If the same column name is contained in two different tables, Microsoft Access requires the table name to be specified with the column name, for example: EMPLOYEE.EmployeeNumber

F. Who are the employees assigned to each project? Show ProjectID, ProjectName, Department (from the PROJECT table), EmployeeNumber, LastName, FirstName, and Phone. Create, run, and save this SQL query as SQLQuery-F.

G. Who are the employees assigned to each project? Show ProjectID, ProjectName, Department (from the PROJECT table), Department Phone, EmployeeNumber, LastName, FirstName, and Employee Phone. Sort by ProjectID, in ascending order. Create, run, and save this SQL query as SQLQuery-G.

H. Who are the employees assigned to projects run by the marketing department? Show ProjectID, ProjectName, Department (from the PROJECT table), Department Phone, EmployeeNumber, LastName, FirstName, and Employee Phone. Sort by ProjectID, in ascending order. Create, run, and save this SQL query as SQLQuery-H.

PROJECT ProjectID Name Department MaxHours StartDate EndDate DEPARTMENT ASSIGNMENT DepartmentName BudgetCode OfficeNumber Phone ProjectID EmployeeNumber HoursWorked EMPLOYEE EmployeeNumber FirstName LastName Department Phone Email Edit Relationships Table/Query: Related Table/Query: OK PROJECT ASSIGNMENT Cancel ProjectID Project!D Join Type Create New Enforce Referential Integrity Cascade Update Related Fields Cascade Delete Related Records Relationship Type: One-To-Many

Explanation / Answer

Below are the SQL Queries for F,G,H questions. Copy the given queries in you Access sql editor and test.

F. Who are the employees assigned to each project?
Show ProjectID, ProjectName, Department (from the PROJECT table), EmployeeNumber, LastName, FirstName, and Phone.
Create, run, and save this SQL query as SQLQuery-F.

Answer :
select p.ProjectID,p.Name as 'ProjectName',p.Department,e.EmployeeNumber,e.LastName,e.FirstName,e.Phone
from PROJECT p, EMPLOYEE e, ASSIGNMENT a
where a.ProjectID = p.ProjectID AND a.EmployeeNumber = e.EmployeeNumber;

G. Who are the employees assigned to each project?
Show ProjectID, ProjectName, Department (from the PROJECT table),
Department Phone, EmployeeNumber, LastName, FirstName, and Employee Phone.
Sort by ProjectID, in ascending order.
Create, run, and save this SQL query as SQLQuery-G.

Answer :
select p.ProjectID,p.Name as 'ProjectName' ,p.Department,e.EmployeeNumber,e.LastName,e.FirstName,e.Phone
from PROJECT p, EMPLOYEE e, ASSIGNMENT a
where a.ProjectID = p.ProjectID AND a.EmployeeNumber = e.EmployeeNumber
ORDER BY p.ProjectID ASC;

H. Who are the employees assigned to projects run by the marketing department?

Show ProjectID, ProjectName, Department (from the PROJECT table), Department Phone,
EmployeeNumber, LastName, FirstName, and Employee Phone. Sort by ProjectID, in ascending order.
Create, run, and save this SQL query as SQLQuery-H.

Answer :
select p.ProjectID,p.Name as 'ProjectName',p.Department,d.Phone as 'DepartmentPhone',e.EmployeeNumber,e.LastName,e.FirstName,e.Phone as 'EmployeePhone'
from PROJECT p, EMPLOYEE e, ASSIGNMENT a, DEPARTMENT d
where a.ProjectID = p.ProjectID AND e.EmployeeNumber = a.EmployeeNumber AND p.Department = d.DepartmentName
AND d.DepartmentName = 'Marketing'
ORDER BY p.ProjectID ASC;
  

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.