SQL Interview Topics

Student (s#,sname,sage,ssex) Student table
Course (c#,cname,t#) timetable
SC (s#,c#,score) score table
Teacher (t#,tname) Teacher table

Problem:
1, the inquiry "001" course is higher than "002" of all students of the school number;
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, the query average score is more than 60 points of the student's number and average score;
Select S#,avg (Score)
From SC
GROUP BY s# have AVG (score) >60;

3, inquiry all students of the school number, name, number of courses selected, total;
Select Student.s#,student.sname,count (SC. C #), SUM (score)
From Student to Outer join SC on STUDENT.S#=SC. s#
GROUP BY Student.s#,sname

4, inquire the surname "Li" the number of teachers;
Select COUNT (Distinct (tname))
From Teacher
Where tname like ' li% ';

5, the inquiry did not learn "cotyledons" teacher class students of the school number, name;
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 Teacher.tname= ' cotyledons ');

6, the inquiry learned "001" and also learned the number "002" course of the students ' study number, name;
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′);
7, the inquiry has learned "Cotyledons" the teacher teaches all classes The student's school number, the name;
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= ' cotyledons ' GROUP by s# have count (SC. C #) = (select count (C #) from Course,teacher where teacher.t#=course.t# and Tname= ' cotyledons '));

8, check all the course results are less than 60 points of the student's school number, name;
Select S#,sname
From Student
where s# not in (the Select student.s# from STUDENT,SC where S.S#=SC. s# and score>60);

9, the inquiry did not learn all the class student's school number, the name;
Select Student.s#,student.sname
From STUDENT,SC
where STUDENT.S#=SC. s#
GROUP BY Student.s#,student.sname have Count (C #) < (select count (C #) from Course);

10, the inquiry has at least one course and the school number for "1001" students learn the same student number and name;
Select S#,sname
From STUDENT,SC
where STUDENT.S#=SC. s# and C # in (select C # from SC where s#= ' 1001 ');

11, delete learning "cotyledons" the SC table record of the teacher class;
Delect SC
From course, Teacher
where COURSE.C#=SC. C # and course.t#= teacher.t# and tname= ' cotyledons ';

12. Check the highest and lowest score of each section: show the following form: Course ID, highest score, lowest score
SELECT l.c# course id,l.score highest score, R.score lowest score
From SC L, SC R
WHERE l.c# = r.c#
and
L.score = (SELECT MAX (Il.score)
From SC il,student IM
WHERE IL. C # = l.c# and IM. S#=il. s#
GROUP by IL. C #)
and
R.score = (SELECT MIN (Ir.score)
From SC IR
WHERE IR. C # = r.c#
GROUP by IR. C #);

13, check the average grade of students and their rankings
Select 1+ (select COUNT (distinct average score)
From (SELECT S#,avg (score) Average score
From SC
GROUP by s#) T1
WHERE Average score > T2. Average score) rank, s# student number, average score
From (SELECT S#,avg (score) average score from SC GROUP by s#) T2
ORDER by average grade desc;

14. Check the records of the top three grades of each section: (regardless of the performance of the situation)
SELECT t1. s# as student id,t1. C # as course Id,score as fraction
From SC T1
WHERE score in (SELECT TOP 3 score
From SC
WHERE t1. c#= C #
ORDER by score DESC)
ORDER by T1. C #;

15. Check the top two of the best results for each door
SELECT t1. s# as student id,t1. C # as course Id,score as fraction
From SC T1
WHERE score in (SELECT TOP 2 score
From SC
WHERE t1. c#= C #
ORDER by score DESC)
ORDER by T1. C #;

Add:
Already know the original table
Year Salary
——————
2000 1000
2001 2000
2002 3000
2003 4000

Solution:
Select B.year,sum (a.salary)
From salary A,salary b
where A.year<=b.year
GROUP BY B.year
Order BY B.year;

In the interview process repeatedly encountered a problem of SQL query, query a (id,name) table 31st to 40th record, ID as the primary key may not be continuous growth of the column, the complete query statement as follows:
Method One:
Select Top 10 *
From A
where ID > (select MAX (ID) from (select top with ID from A ORDER by ID) T) the ORDER by ID
Method Two:
Select Top 10 *
From A
where ID not in (select top with ID from A order by ID)
ORDER BY ID

SQL Interview Topics