SQL Interview Questions and Answers

Source: Internet
Author: User

SQL interview questions and answers by Lee- Last updated:Friday, May Leave a Comment

Student score Table (Stuscore):
Name: Name course: Subject Score: Score No.: Stuid
Zhang San Mathematics 89 1
Zhang San Languages 80 1
Zhang San English 70 1
John Doe Mathematics 90 2
John Doe Languages 70 2
John Doe English 80 2

1. Calculate the total number of each person and rank (requires display field: Name, overall scores)

Answer: Select Name,sum (Score) as Allscore from Stuscore Group by name ORDER by Allscore

2. Calculate each person's total and rank (required field: number, name, overall)

Answer: SELECT DISTINCT t1.name,t1.stuid,t2.allscore from Stuscore T1, (select Stuid,sum (Score) as Allscore from Stuscore g Roup by Stuid) T2 where t1.stuid=t2.stuidorder by t2.allscore desc

3. Calculate the highest score for each individual (required field: School number, name, course, highest score)

Answer: Select T1.stuid,t1.name,t1.subject,t1.score from Stuscore T1, (select Stuid,max (Score) as Maxscore from Stuscore Group by Stuid) T2 where T1.stuid=t2.stuid and T1.score=t2.maxscore

4. Calculate each person's average score (requires display field: study number, name, average score)

Answer: SELECT DISTINCT t1.stuid,t1.name,t2.avgscore from Stuscore T1, (select Stuid,avg (Score) as Avgscore from Stuscore Group by Stuid) T2 where T1.stuid=t2.stuid

5. List the students who have the best grades in each course (Required fields: Student number, name, subject, score)

Answer: Select T1.stuid,t1.name,t1.subject,t2.maxscore from Stuscore T1, (select Subject,max (Score) as Maxscore from Stuscore Group by subject) T2 where T1.subject=t2.subject and T1.score=t2.maxscore

6. List of the two students who have the best results in each course (Required fields: Student number, name, subject, score)

Answer: SELECT DISTINCT t1.* from Stuscore T1 where t1.id in (select top 2 stuscore.id from stuscore where subject = T1.subjec T ORDER BY score DESC) Order by T1.subject

7. Statistics are as follows: School Number name Chinese mathematics English score average

Answer: Select Stuid as number, name as name, sum (case when subject= ' language ' then score else 0 end) as language, sum (case when subject= ' math ' then s Core else 0 end) as math, sum (case when subject= ' English ' then score else 0 end) as English, sum (score) as Total, (SUM (Score)/count (*)) as flat Avg. from Stuscoregroup by stuid,name ORDER BY total desc

8. List the average scores for each course (Required fields: course, average)

Answer: Select Subject,avg (Score) as Avgscore from Stuscore Group by subject

9. List the ranking of math scores (required fields: School number, name, score, rank)

Answer:

Declare @tmp table (PM int,name varchar (), score Int,stuid int)
Insert INTO @tmp select Null,name,score,stuid from Stuscore where subject= ' math ' ORDER BY score Desc
DECLARE @id int
Set @id = 0;
Update @tmp set @[email Protected]+1,[email protected]
SELECT * FROM @tmp

Oracle
Select Dense_rank () over (order BY score Desc) as Row,name,subject,score,stuid from Stuscore where subject= ' math ' ORDER by S Core DESC
MS SQL (best choice)
Select (select COUNT (*) from Stuscore t1 where subject = ' math ' and T1.score>t2.score) +1 as row, Stuid,name,score from Stu Score t2 where subject = ' math ' ORDER BY score Desc

10. List of students with a mathematical score of 2-3 (Requirements display field: Student number, name, subject, score)

Answer: Select t3.* from (select top 2 t2.* from (select Top 3 name,subject,score,stuid from Stuscore where subject= ' math ' order by score Desc) T2 ORDER by T2.score) T3 ORDER BY T3.score Desc

11. Find out the ranking of John Doe's mathematical achievements

Answer:

Declare @tmp table (PM int,name varchar (), score int,stuid int) insert INTO @tmp select Null,name,score,stuid from Stuscore where subject= ' math ' ORDER by score Descdeclare @id intset @id =0;update @tmp set @[email protected]+1,[email protected] * F Rom @tmp where name= ' John Doe '

12. Statistics are as follows: Failure of course (0-59) Good (60-80) Excellent (81-100)

Answer: Select Subject, (select COUNT (*) from Stuscore where score<60 and Subject=t1.subject) as failed, (select COUNT (*) from S Tuscore where score between and subject=t1.subject) as benign, (select COUNT (*) from Stuscore where score >80 and Subject=t1.subject) as excellent from Stuscore T1 GROUP by subject

13. The statistics are as follows: Mathematics: Zhang San (50 points), John Doe (90 points), Harry (90 points), Zhao Liu (76 points)

Answer:

DECLARE @s varchar (+) Set @s= "Select @s [email protected]+ ', ' +name+ ' (' +convert ' (varchar), score) + ' points) ' from Stuscore where subject= ' math ' Set @s=stuff (@s,1,1, ") print ' math: ' [email protected]

14. Average scores of people who have passed the calculation of the section

Answer: SELECT DISTINCT t1.stuid,t2.avgscore from Stuscore T1, (select Stuid,avg (Score) as Avgscore from Stuscore group by S TUID) T2, (select Stuid from Stuscore where score<60 Group by stuid) T3 where T1.stuid=t2.stuid and T1.stuid!=t3.stuid ;
Select Name,avg (Score) as Avgscore from Stuscore s where (select sum (case i.score>=60 then 1 else 0 end) from Stuscore I where i.name= s.name) =3 GROUP by name

SQL Interview Questions and Answers

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.