Question
Using the JC Consulting database and creating all relationships, run the following 5 SQL Queries by going to Create -> Query Design -> SQL View.
Using the JC Consulting database and creating all relationships, run the following 5 SQL Queries by going to "Create -> Query Design -> SQL View". Once you have run the SQL, paste the output into your Word Doc as I demonstrated in class:
Query 1: SELECT Clients.ClientName, Projects.ProjectNotes FROM Clients INNER JOIN Projects ON Clients.ClientID = Projects.ClientID;
Query 2: SELECT DISTINCT Clients.ClientName, Employees.LastName, Employees.FirstName, Employees.Title FROM Employees INNER JOIN (Clients INNER JOIN Projects ON Clients.ClientID = Projects.ClientID) ON Employees.EmployeeID = Projects.EmployeeID;
Query 3: SELECT DISTINCT Employees.Title, Min(Employees.Salary) AS MinOfSalary, Max(Employees.Salary) AS MaxOfSalary, Avg(Employees.Salary) AS AvgOfSalary FROM Employees GROUP BY Employees.Title;
Query 4: SELECT DISTINCT Employees.Title, Count(Employees.Title) AS CountOfTitle FROM Employees GROUP BY Employees.Title;
Query 5: SELECT DISTINCT Clients.ClientName, Employees.LastName, Employees.FirstName, TaskMasterList.Description FROM TaskMasterList INNER JOIN (Clients INNER JOIN ((Employees INNER JOIN Projects ON Employees.EmployeeID = Projects.EmployeeID) INNER JOIN ProjectLineItems ON Projects.ProjectID = ProjectLineItems.ProjectID) ON Clients.ClientID = Projects.ClientID) ON TaskMasterList.TaskID = ProjectLineItems.TaskID;
JC Consulting Database Please review the image of the JC Consulting Co. ERD below. The database you created, into which you imported Excel spreadsheets should look like this. If your database does not have the same fields or relationships, you may use the ERD as a guide to correct the relationships. Relationships Task MasterList Clients Projects 8 ProjectID ProjectStartDate Clienti Employeeld ProjectNotes ProjectLineItems ProjectLineItemID ProjectID TaskID TaskDate Quantity Factor ProjectLineItem Notes 9 Clienti Categories TaskID Description CategoryID Per Estimate SO 1 ClientName Street Zip Government 8 Category Zips Employees Employeeld LastName FirstName HireDate Title HomeZip Salary City Zip If you have difficulties reconciling your database with the ERD above, you may refer to this database: JCConsulting - Begin.accdb JC Consulting Database Please review the image of the JC Consulting Co. ERD below. The database you created, into which you imported Excel spreadsheets should look like this. If your database does not have the same fields or relationships, you may use the ERD as a guide to correct the relationships. Relationships Task MasterList Clients Projects 8 ProjectID ProjectStartDate Clienti Employeeld ProjectNotes ProjectLineItems ProjectLineItemID ProjectID TaskID TaskDate Quantity Factor ProjectLineItem Notes 9 Clienti Categories TaskID Description CategoryID Per Estimate SO 1 ClientName Street Zip Government 8 Category Zips Employees Employeeld LastName FirstName HireDate Title HomeZip Salary City Zip If you have difficulties reconciling your database with the ERD above, you may refer to this database: JCConsulting - Begin.accdbStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started