High-Performance Development - C#, VB, SQL, ASP.NET

Slogan Text

Word Reversal

In C# or C++, write a function that reverses the order of the words in a string. For example, your function should transform the string "I pledge allegiance to the flag." to "flag. the to allegiance pledge I". Assume that all words are space delimited and treat punctuation the same as letters.

Permutations

In C# or C++, write a function that takes a string, and returns an array with every permutation of the characters in the string. Example: Input: "cat" Output: {cat, cta, tca, tac, act, atc}

Sql SELECT Winners and Runners Up

 

Write a single T-SQL query that returns the name and age of everyone in the Winners and RunnersUp tables:

People
      
Winners
    
RunnersUp
ContestantName Age   ContestnameName   ContestantName
Ed 24   Ed   Parker
Joe 32   Sheila   Betsy
George 25        
Parker 26        
Sain 27        
Sheila 21        
Jim 24        
Betsy 25        
 
Answer 1: Linq

One has to wonder why this question asks for a T-SQL and 

Answer 2: Subquery

SELECT ContestantName, Age

FROM Contestants
WHERE ContestantName IN
(SELECT ContestantName FROM Winners) OR ContestantName IN (SELECT ContestantName FROM RunnersUp)
ORDER BY ContestantName
 
Answer 3: Multi-Table Select

SELECT DISTINCT C.ContestantName, Age

FROM Contestants AS C, Winners AS W, RunnersUp AS R
WHERE C.ContestantName = W.ContestantName OR C.ContestantName = R.ContestantName
ORDER BY C.ContestantName
 
New and Improved

We notice the table design as described in the puzzle is less optimal. While I do understand the objective is to determine the solver's level of SQL expertise let's see if we can improve the table design and in turn the SQL statement to select the winners and runners up.

  • Name
    The table has a column only for a person's first name. 

    A better approach would be to have columns for both first and last names.
     
  • Age
    Storing a person's age is not the best approach since a person's age is changing on a yearly basis.

    A better approach is to store the person's birth date using the DateTime data type.
     
  • Multi-Table
    Store the same information (the contestant's name) in multiple locations is considered bad database design.

    A better approach is to store the contestant's name in one location. 
     
  • No Primary Key
    These tables have no primary key. Although one could argue that the contestant's name is the primary key.

    A better option is to add a primary key. We'll call it ContestantId.
     
  • FinishPosition

    We add a new column named FinishPosition. Possible values are:

    -1 : the contestant did not finish in the winners or runnersup positions. 
    1 : contestant finished a winner.
    2 : contestant finished a runners up.
     
  • Column Order

    You'll notice in our new table design we've ordered the columns in alphabetical order (except for the primary key column). This makes it easier for the database designer to locate columns.
New Table Design: Contestants
ContestantId
  
BirthDate
   
FinishPosition
 
NameFirst
   
NameLast
1   1990-1-1   1   Ed   Smith
2   1985-6-6   -1   Joe   Brown
3   1980-8-13   -1   George   Takei
4   1992-3-5   2   Parker   Hannafin
5   1983-5-8   -1   Sain   Asylum
6   1972-12-4   1   Sheila   Harrington
7   1984-7-19   -1   Jim   Ray
8   1975-9-10   2   Betsy   Ross
New SQL Design

SELECT NameFirst, NameLast, BirthDate FROM Contestants WHERE FinishPosition > 0 ORDER BY FinishPosition