Interview Questions and Answers in SQL Database (50 Examples)
Student(S#Sname, Sage, Ssex Student Table S#Student ID Sname: Student name Sage: Student age Ssex: Student gender Course(C#,Cname,T#Curriculum schedule C#Course Number Cname: Course title T#Teacher number SC(S#,C#score) S#Student ID C#Course Number score: achievement Teacher(T#Teacher table T#Teacher number: Tname: Teacher's name
Question:
1. Search for the number of all students whose grades in "001" are higher than those in "002"
select a.S# from (select S#,score from SC where C#='001')a, (select s#,score from SC where c#='002')b Where a.score>b.score and a.s# = b.s#;
2. Query the number and average score of students whose average score is more than 60
select S#, avg(score) from sc group by S# having avg(score)>60
3. Query the number, name, number of courses selected and total scores of all students
select student.S#, student.Sname, count(sc.C#), sum(score) from student left outer join SC on student.S# = SC.S# group by Student.S#, Sname
4. Number of teachers whose surname is Li:
select count(distinct(Tname)) from teacher where tname like 'plum%';
5. Search for the student number and name of the classmate who has not learned "Yeping":
select student.S#, student.Sname from Student where S# not in (select distinct(SC.S#) from SC,Course,Teacher where sc.c#=course.c# AND teacher.T#=course.T# AND Teahcer.Tname='Yeping';
6. Inquire the student numbers and names of the students who have learned all the lessons taught by "Yeping":
select S#,Sname from Student where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='Ye Ping' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='Yeping');
7. Search the number and name of the students who have studied "011" and have also studied the course "002":
select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
8. Inquire the number of the course "002" and the names of all the students whose grades are lower than the number of the course "001":
Select S#,Sname from (select Student.S#,Student.Sname,score , (select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2 from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 < score;
9. Inquire the number and name of all the students whose grades are less than 60:
select S#, sname from student where s# not in (select student.s# from student, sc where s.s# = sc.s# and score>60);
10. Search for the student numbers and names of the students who have not studied all the courses:
select student.s#, student.sname from student, sc where student.s#=sc.s# group by student.s#, student.sname having count(c#)<(select count(c#) from course);
11. Search for the number and name of at least one class with the same number as that of "1001" students.
select s#, Sname from Student, SC where student.s# = sc.s# and c# in (select c# from SC where s#='1001');
12. Search for the number and name of other students who have studied at least one course of "001".
select distinct sc.s# , sname from student, sc where student.s#=sc.s# and c# in (select C# from sc where s#='001');
13. Change the grade of "Yeping" in the SC table to the average grade of this course:
Update Sc Set Score=(Select Avg(s2_Score) From sc s2 Where s2.c#=sc.c#) Where c# IN (Select c# From sc cs INNER JOIN Teacher tc ON cs.t#=tc.t# WHERE tname ='Yeping')
14. Inquiry and "1002" students to learn the same course number and name of other students:
select s# from sc where c# in (select c# from sc where s#='1002') group by s# having count(*)= (select count(*) from sc where s#='1002');
15. Delete the record of SC table for learning "Yeping" teacher's lessons:
delect sc from course, Teacher where course.c#=sc.c# and course.t#=teacher.t# and tname='Ye Ping';
16. Insert some records into the SC table. These records meet the following requirements: students who have not attended the course No. 003, the average score of Lesson 002:
Insert SC select S#,'002', (Select avg(score) from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');
17. Show all the students'achievements in the course of "database", "enterprise management" and "English" from high to low according to their average scores. Show them in the following forms: student ID, database, enterprise management, English, number of effective courses, effective average scores:
select s# as student ID, (select score from sc where sc.s#=t.s# and c#='004') as database, (select score from sc where sc.s#=t.s# and c#='001') as Enterprise Management, (select score from sc where sc.s#=t.s# and c#='006') as English, count(*) as Number of effective courses, avg(t.score) as Tie score from sc as t group by s# order by avg(t.score)
18. Query the highest and lowest scores of each subject: show in the following form: course ID, highest score and lowest score
select L.c# As course ID, L.score as the highest score.
R.score as Minimum score
from sc L, sc R
where L.c# = R.c#
and L.score = (select max(IL.score)
from sc IL, student as IM
where L.c#=IL.c# and IM.s#=IL.s#
group by IL.c#)
and R.score = (select min(IR.score)
from sc as IR
where R.c#=IR.c#
group by IR.c#);
19. According to the order from low to high and the percentage of passing rate from high to low:
SELECT t.C# AS course number, max(course.Cname)AS Course name, isnull(AVG(score),0) AS Average score, 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS Passing percentage FROM SC T,Course where t.C#=course.C# GROUP BY t.C# ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
20. Query the percentage of the average grade and passing rate of the following courses (shown in line 1): Business Administration (001), Marx (002), OO&UML (003), Database (004):
21. Query the average scores of different courses taught by different teachers from high to low.
SELECT max(Z.T#) AS Teacher ID, MAX(Z.Tname) AS Teacher's name, C.C# AS course ID. AVG(Score) AS Average score FROM SC AS T,Course AS C ,Teacher AS Z where T.C#=C.C# and C.T#=Z.T# GROUP BY C.C# ORDER BY AVG(Score) DESC
22. Query the student transcripts of the 3rd to 6th places in the following courses: Business Administration (001), Marx (002), UML(003), Database (004):
23. Statistical results of the following subjects, the number of students in each fraction: course ID, course name, [100-85], [85-70], [70-60], [less than 60]:
SELECT SC.C# As course ID, Cname as course name, SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85] , SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70], SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60], SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -] FROM SC,Course where SC.C#=Course.C# GROUP BY SC.C#,Cname;
24. Query students'average scores and their rankings:
SELECT 1+(SELECT COUNT( distinct Average score) FROM (SELECT S#AVG(score) AS Average Achievement FROM SC GROUP BY S# ) as T1 WHERE Average Score > T2. Average Score) as Rank, S# as Student Number, Average Achievement FROM (SELECT S#AVG(score) average FROM SC GROUP BY S# ) AS T2 ORDER BY Average score desc;
25. Query the records of the top three performances of each subject (without considering the juxtaposition of achievements):
SELECT t1.S# as Student ID,t1.C# As course ID,Score as score FROM SC t1 WHERE score IN (SELECT TOP 3 score FROM SC WHERE t1.C#= C# ORDER BY score DESC)
26. Query the number of students selected for each course:
select c#, count(s#) from sc group by c#;
27. Find out the number and name of all the students who take only one course:
select sc.s#, student.sname, count(c#) as elective number from sc,student where sc.s# =student.s# group by sc.s#,Student.sname having count(c#)=1;
28. Query the number of boys and girls:
select count(Ssex) as Number of boys from student group by Ssex having Ssex='male'； select count(Ssex) as Number of female students from student group by Ssex having Ssex='female';
29. Query the list of students surnamed "Zhang":
select sname from student where sname like 'Zhang%';
30. Query the list of students with the same name and name, and count the number of students with the same name:
select sanme,count(*) from student group by sname havang count(*)>1;
31. List of students born in 1981 (Note: the type of sage column in the student table is datetime):
select sname, convert(char(11),DATEPART(year,sage)) as age from student where convert(char(11),DATEPART(year,Sage))='1981';
32. Query the number, name and average score of all students whose average score is more than 85:
select Sname,SC.S# ,avg(score) from Student,SC where Student.S#=SC.S# group by SC.S#,Sname having avg(score)>85;
33. Query the average scores of each course. The results are sorted in ascending order according to the average scores. When the average scores are the same, they are sorted in descending order according to the number of courses:
select C#, avg(score) from sc group by c# order by avg(score), c# desc;
34. Query the name of the course as "database", and the student's name and score below 60:
select sname, isnull(score,0) from student, sc ,course where sc.s#=student.s# and sc.c#=course.c# and course.cname='database'and score < 60;
35. Query all students about their choices of courses:
select sc.s#,sc.c#,sname,cname from sc,student course where sc.s#=student.s# and sc.c#=course.c#;
36. Query the names, titles and scores of any course with scores above 70:
select distinct student.s#,student.sname,sc.c#,sc.score from student,sc where sc.score>=70 and sc.s#=student.s#;
37. Query the failed courses and arrange them according to the number of courses from big to small:
select c# from sc where score<60 order by c#;
38. Query the number and name of the student whose course number is "003" and the course score is above 80:
select sc.s#,student.sname from sc,student where sc.s#=student.s# and score>80 and c#='003';
39. Number of students seeking courses:
select count(*) from sc;
40. Inquire about the names and achievements of the students who have the highest scores in the courses offered by Yeping.
select student.sname,score from student,sc,course c, teacher where student.s#=sc.S# and sc.c#=c.c# and c.T#=teacher.T# and teacher.tname='Ye Ping' and sc.score=(select max(score) from sc where c#=c.c#);
41. Inquire about the number of courses and corresponding electives:
select count(*) from sc group by c#;
42. Query the students and student numbers, course numbers and student scores with the same results in different courses:
select distinct a.s#,b.score from sc a ,sc b where a.score=b.score and a.c#<>b.c#;
43. Search for the top two students in each course:
select t1.s# as Student ID,t1.c# Course ID, Score as Score
from sc t1
where score in (select top 2 score from sc
where t1.c#=c#
order by score desc)
order by t1.c#;
44. Statistics of the number of students taking each course (more than 10 courses only statistics). The output of course number and elective number is required. The inquiry results are sorted in descending order according to the number of students. If the number of students is the same, they are sorted in ascending order according to the number of courses:
select c# As course number, count(*) as number from sc group by c# order by count(*) desc c#;
45. Retrieve the student number of at least two courses:
select s# from sc group by s# having count(*)>=2;
46. Query all students'elective courses and course numbers and course names:
select c# ,cname from course where c# in (select c# from sc group by c#);
47. Query the names of students who have not studied any of the courses taught by Yeping.
select sname from student where s# not in (select s# from course,teacher,sc where course.t#=teacher.t# and sc.c#=course.c# and tname='Ye Ping');
48. Query the number of students who have failed two or more courses and their average scores:
select s#,avg(isnull(score,0)) from sc where s# in (select s# from sc where score<60 group by s# having count(*)>2) group by s#;
49. Search "004" course scores less than 60, according to the descending order of student numbers:
select s# from sc where c#='004' and score<60 order by score desc;
50. Delete the results of "001" course for "002" students:
delect from sc where s#='002' and c#='001';