Need SQL queries that provide the data for (DB is called JCData):

1. Project list(It includes the project name, the project's color (used to flag the project's paperwork), the beginning date of the project, the projected end of the project, the number of remaining days for the project (relative to the current date). 2. Project Assignment list(This is a report to for employees who have been assigned to more than one project. The query lists the project name, the first and last names of project team members, their project begin and end dates as well as their position (role) on the project. ***A view must be used for this query. ) 3. Office Phone and Email list (sorted by phone number) for the first 15 in the list.(This printed list includes last name, first name, and 4?digit phone number for each employee. Updates are triggered by turnover of personnel, name changes, and office relocation (phone numbers are tied to offices). JC would like this list expanded to list each person's email address) 4. Project Members Phone Contact list for Far West project only.(Employees may work from home up to 15 hours per week. Each project leader needs a list of current means of contacting each member of the project team: home phone and home FAX number. To contact employees "on the road" the project leaders also need to know cell phone numbers. Currently it is the responsibility of the project leader to obtain this information from the team members and to keep it current. JC wishes the responsibility for this information to be given to YK. A master list of all contact numbers for all employees should be available for reference by JC and YK. ) 5. Names and addresses for mailing labels for employees living in North Las Vegas with 'street' or'road' in the address. (Occasionally, YK sends correspondence to employees' homes through the Postal Service. Presently, addresses are maintained in a text file which "feeds" the mail?merge feature of a word processor. YK would like to have mailing labels as part of the new information system. You can make your own assumptions on what fields are required for the employee mailing labels. )

The tables are on the screenshot:

CoursHeroTranscribedText

Transcribed Image Text:

0 (+0 JCData 0 Database Diagrams Tables + + + + + + + + + 0 System Tables File Tables External Tables Graph Tables dbo.EmployeeProject Columns * EmployeeID (PK, FK, int, not null) To ProjectID (PK, FK, int, not null) PositionOnProject (varchar(50), not null) StartOnProject (datetime, null) FinishOnProject (datetime, null) Keys Constraints Triggers Indexes Statistics dbo.Employees Columns * EmployeelD (PK, int, not null) LastName (varchar(50), not null) FirstName (varchar(50), not null) OfficePhoneNo (varchar(50), not null) EmailAddress (varchar(50), not null) HomePhone (varchar(50), not null) HomeFax (varchar(50), null) CellPhone (varchar(50), null) Job Title (varchar(50), not null) StreetAddress (varchar(50), not null) City (varchar(50), not null) State (varchar(50), not null) Zip (varchar(50), not null) Keys Constraints Triggers Indexes Statistics dbo.Project Columns ProjectID (PK, int, not null) Description (varchar(50), not null) ProjectColor (varchar(50), not null) BeginDate (datetime, null) Expected EndDate (datetime, null) 0 (+0 JCData 0 Database Diagrams Tables + + + + + + + + + 0 System Tables File Tables External Tables Graph Tables dbo.EmployeeProject Columns * EmployeeID (PK, FK, int, not null) To ProjectID (PK, FK, int, not null) PositionOnProject (varchar(50), not null) StartOnProject (datetime, null) FinishOnProject (datetime, null) Keys Constraints Triggers Indexes Statistics dbo.Employees Columns * EmployeelD (PK, int, not null) LastName (varchar(50), not null) FirstName (varchar(50), not null) OfficePhoneNo (varchar(50), not null) EmailAddress (varchar(50), not null) HomePhone (varchar(50), not null) HomeFax (varchar(50), null) CellPhone (varchar(50), null) Job Title (varchar(50), not null) StreetAddress (varchar(50), not null) City (varchar(50), not null) State (varchar(50), not null) Zip (varchar(50), not null) Keys Constraints Triggers Indexes Statistics dbo.Project Columns ProjectID (PK, int, not null) Description (varchar(50), not null) ProjectColor (varchar(50), not null) BeginDate (datetime, null) Expected EndDate (datetime, null)