Database query questions that appear frequently in Java interviews

Programmers who have experienced interviews all know that during the interview process, the interviewer may ask you all kinds of strange questions, but they always remain the same. In the end, you have to ask the key points, such as The basic operation steps of a certain job and how to write the code, etc., this article talks about the most classic database query problems.

Basic table structure:

teacher(tno,tname) teacher table

student(sno,sname,sage, ssex) student table

course(cno,cname,tno) course table

sc(sno,cno,score) grade table

NO.1 query course The student numbers of all students whose grades in 1 are higher than those in course 2

select a.sno from(select sno,score from sc where cno=1) a,(select sno,score from sc where cno=2) bwhere a.score>b.score and a.sno=b.sno

NO.2 Query the student numbers and average grades of students whose average grades are greater than 60 points

select a.sno as "学号", avg(a.score) as "平均成绩" from(select sno,score from sc) a group by sno having avg(a.score)>60

NO.2 Query All students’ student numbers, names, number of courses taken, and total grades

select a.sno as 学号, b.sname as 姓名,count(a.cno) as 选课数, sum(a.score) as 总成绩from sc a, student bwhere a.sno = b.snogroup by a.sno, b.sname

or:

selectstudent.sno as 学号, student.sname as 姓名, count(sc.cno) as 选课数, sum(score) as 总成绩from student left Outer join sc on student.sno = sc.snogroup by student.sno, sname

NO.3 Query the number of teachers with the surname “Zhang”

selectcount(distinct(tname)) from teacher where tname like '张%‘

or :

select tname as "姓名", count(distinct(tname)) as "人数" from teacher where tname like'张%'group by tname

NO.4 Query the student number and name of the students who have not studied "Zhang San"'s class

select student.sno,student.sname from student
where sno not in (select distinct(sc.sno) from sc,course,teacher
where sc.cno=course.cno and teacher.tno=course.tno and teacher.tname='张三')

NO.5 Query the students who have studied both course 1 and course 2 Student ID, name

select sno, sname from studentwhere sno in (select sno from sc where sc.cno = 1)and sno in (select sno from sc where sc.cno = 2)

or:

selectc.sno, c.sname from(select sno from sc where sc.cno = 1) a,(select sno from sc where sc.cno = 2) b,student cwhere a.sno = b.sno and a.sno = c.sno

or:

select student.sno,student.sname from student,sc where student.sno=sc.sno and sc.cno=1and exists( select * from sc as sc_2 where sc_2.sno=sc.sno and sc_2.cno=2)

NO.6 Query the student IDs of all students who have studied all courses taught by "Li Si" , name

select a.sno, a.sname from student a, sc bwhere a.sno = b.sno and b.cno in(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四')

or:

select a.sno, a.sname from student a, sc b,(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四') ewhere a.sno = b.sno and b.cno = e.cno

NO.7 Query the student numbers and names of all students whose grades in course number 1 are higher than those in course number 2

<p style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">select a.sno, a.sname from student a,</p><p style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">(select sno, score from sc where cno = 1) b,</p><p style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">(select sno, score from sc where cno = 2) c</p><p style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">where b.score > c.score and b.sno = c.sno and a.sno = b.sno<br/></p>

NO.8 Query the student numbers and names of all students whose grades are less than 60 points

select sno,sname from studentwhere sno not in (select distinct sno from sc where score > 60)

NO.9 Query the courses taken by students with at least one course and student number 1 The student number and name of the same classmate

select distinct a.sno, a.snamefrom student a, sc bwhere a.sno <> 1 and a.sno=b.sno andb.cno in (select cno from sc where sno = 1)

Or:

select s.sno,s.sname from student s,(select sc.sno from scwhere sc.cno in (select sc1.cno from sc sc1 where sc1.sno=1)and sc.sno<>1group by sc.sno)r1where r1.sno=s.sno

The above are the questions you may often encounter in interviews for database-related jobs, hurry up and collect them Get up and take a good look!

【Recommended course: MYSQL learning video】

The above is the detailed content of Database query questions that appear frequently in Java interviews. For more information, please follow other related articles on the PHP Chinese website!