Java high-frequency basic interview questions——(9)

If you want to become a qualified Java back-end developer, database knowledge is essential. The examination of familiarity with the database is also an examination of whether the person has solid basic skills.

(Recommendations for more related interview questions: java interview questions and answers)

Especially for junior developers, the interview may not ask about framework-related knowledge, but it will definitely There is no way to fail to examine database knowledge. Here we collect some common types of SQL statements, which will be helpful whether for daily development or preparing for interviews.

Basic table structure:

 student(sno,sname,sage,ssex)学生表        
 course(cno,cname,tno) 课程表        
 sc(sno,cno,score) 成绩表
 teacher(tno,tname) 教师表

101, query the student numbers of all students whose grades in course 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) b
where a.score>b.score and a.sno=b.sno

102, query the average grade greater than 60 The student number and average grade of the students who scored

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

103, query the student number, name, number of courses taken, and total grade of all students

select a.sno as 学号, b.sname as 姓名,
count(a.cno) as 选课数, sum(a.score) as 总成绩
from sc a, student b
where a.sno = b.sno
group 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.sno
group by student.sno, sname

104, 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

105, query the number of teachers who have not studied with "Zhang San" The student number and name of the student

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='张三')

(recommended study: java course)

106, query the students who have studied both course 1 and course 2 Student number, name

select sno, sname from student
where 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 c
where 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=1
and exists( select * from sc as sc_2 where sc_2.sno=sc.sno and sc_2.cno=2)

107, check the teacher who has studied "Li Si" The student numbers and names of all students in all courses are

select a.sno, a.sname from student a, sc b
where 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 = '李四') e
where a.sno = b.sno and b.cno = e.cno

108. Query the student numbers of all students whose scores in course number 1 are higher than those in course number 2. , Name

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

109, query the student ID of all students whose scores are less than 60 points, name

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

110, query at least one course with a student ID of 1 The student number and name of the classmates who are studying the same course

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

or:

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

Related recommendations: java introductory tutorial

The above is the detailed content of Java high-frequency basic interview questions——(9). For more information, please follow other related articles on the PHP Chinese website!