SqlServer Interview Queries

<embed src=”http://embedit.in/EXj9LW8bre.swf” height=”400″ width=”466″ type=”application/x-shockwave-flash” allowFullScreen=”true”>

SQL Exercise

Given the following table called StudentScore:

Name Subject Score Rank
Dave C++ 15  
Dave English 67  
Dave Maths 22  
Kathy English 92  
Kathy Maths 67  
Lee C++ 78  
Lee English 82  
Lee Maths 89  
Russell C++ 2  
Russell English 9  
Russell Maths 20  

 

Where Name and subject fields are the unique compound key, and Rank and Score are integers, write the SQL to satisfy the following requests:

1.    Display the name of the person with the lowest total score.

select top 1 Name,Sum(Score) as Total from StudentScore group by Name order by total

2.    Display average scores of each person in the table.

select Name,Avg(Score) as Total from StudentScore group by Name

3.    Delete the five rows in the table with the highest score.

delete from studentscore where score in (

select top 5 score from studentscore order by score desc)

  1. 4.    Update the table such that the Rank field contains descending integers in order of Score. The row with the highest Score should be given a Rank of 1. Make sure no two rows are given the same rank.

 

update studentscore

set rank = seq

from studentscore ss

inner join

(select name,subject,row_number() over (order by score desc) as seq

 from studentscore) s

on ss.name = s.name and ss.subject = s.subject

  1. 5.    Create another table called Person. The table should have the following fields: PersonID, Name, AverageScore, BestSubject. PersonID is an identity field and primary key. Populate the table from the StudentScore information. Remove the Name field from StudentScore table and replace it with a ForeignKey reference field to the Person table.

 

Here is an example of how the data should look after the SQL is run:

PersonID Name BestSubject AverageScore
1 Dave English 34.666667
2 Kathy English 79.5
3 Lee Maths 83
4 Russell Maths 10.333333

 

PersonID Subject Score Rank
1 C++ 15  
1 English 67  
1 Maths 22  
2 English 92  
2 Maths 67  
3 C++ 78  
3 English 82  
3 Maths 89  
4 C++ 2  
4 English 9  
4 Maths 20  

 

insert into person(Name,averagescore,bestsubject)

select s.personid,Avg(s.score) as Topscore,

(select top 1 si.subject from studentscore1 si

 where si.personid = s.personid and Max(s.score) = si.score) as subject

from studentscore1 s

group by s.personid order by s.personid

Advertisements

One Response to SqlServer Interview Queries

  1. David says:

    Hi

    I get an error when i run you code for 1.5, but if I replace the PersonID for .Name it then executes.

    Is there another method to processing this or am I missing something

    regards

    David

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: