SQL Query Select Statement

This project is to simply write an efficient SQL Query for MS SQL 2005.

PLEASE READ THIS PROJECT DESCRIPTION CAREFULLY. WE WILL NOT RESPOND TO QUESTIONS OR BIDS THAT CLEARLY SHOW THE DESCRIPTION HAS NOT BEEN READ.

WE ARE PROVIDING YOU WILL CLEAR DETAIL AND DATA STRUCTURE SO THAT YOU CAN QUICKLY WRITE YOUR SQL SELECT STATEMENT.

WE WILL EVALUATE IN ADDITION TO THE STATEMENT THE EFFICIENCY OF THE STATEMENT.

We are looking for developers that are capable in Classic ASP, .NET (to eventually convert some of our sites from ASP to .NET) as well as SQL.

This first "pilot" / test is to find a SQL developer who is capable and can write efficient Selects.

PLEASE NOTE. THIS IS A MICRO PROJECT AIMED AT EVALUATING YOUR SKILLS SO THAT THE SUCCESSFUL DATABASE DEVELOPER CAN HAVE FURTHER TALKS WITH US ABOUT MORE DEVELOPMENT.

For This Pilot there will be 3 tables.

EMAILS

EMAILS_THREAD

PROFILES

The database table EMAILS consists of several columns

EmailID Email_To Email_From Email_Subject Email_Registered

1 2 3 Test Subject 2/24/2016 11:00

EmailID is the Primary Key

Email_To and Email_From are both INT values that should be used to JOIN the PROFILES table to get the Profile_User

So for example the PROFILES table

ProfilesID Profiles_User

1 Jack

2 John

3 Dave

Would mean the Email with EmailID 1 was sent by Dave to John.

John replies to Dave.

His entry goes into the table EMAILS_THREAD

EmailThreadID EmailID Email_To Email_From Email_Registered

1 1 3 2 2/24/2016 11:02

Then Dave replies to John so.

EMAILS_THREAD

EmailThreadID EmailID Email_To Email_From Email_Registered

1 1 3 2 2/24/2016 11:02

2 1 2 3 2/24/2016 11:06

John writes a new mail to Jack. So the EMAILS and EMAILS_THREAD tables now look like

EMAILS

EmailID Email_To Email_From Email_Subject Email_Registered

1 2 3 Test Subject 2/24/2016 11:00

2 1 2 Test2 2/24/2016 11:10

EMAILS_THREAD

EmailThreadID EmailID Email_To Email_From Email_Registered

1 1 3 2 2/24/2016 11:02

2 1 2 3 2/24/2016 11:06

Your Select statement should:

1. return all the results for email sent by John

2. where an EmailID is in both EMAILS and EMAILS_THREAD only the latest (last one sent) should be returned in the results.

You will be required to send the MS SQL 2005 select statement to us to test and confirm the Select statement returns the correct results. (We do not need you to send us database tables).

Microsoft SQL Server

ID Proyek: #9750175

Tentang proyek