<embed src=”http://embedit.in/EXj9LW8bre.swf” height=”400″ width=”466″ type=”application/x-shockwave-flash” allowFullScreen=”true”>
Given the following table called StudentScore:
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)
- 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.
set rank = seq
from studentscore ss
(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
- 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:
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