Rumah Java JavaSoalan temu bual java高频率基础面试题——(九)

java高频率基础面试题——(九)

Sep 14, 2020 am 11:21 AM
java Soalan temu bual

java高频率基础面试题——(九)

想要成为一名合格的Java后端开发者,数据库知识必不可少,对数据库的掌握熟悉度的考察也是对这个人是否有扎实基本功的考察。

(更多相关面试题推荐:java面试题及答案

特别对于初级开发者,面试可能不会去问框架相关知识,但是绝对不会不去考察数据库知识,这里收集一些常见类型的SQL语句,无论对于平常开发还是准备面试,都会有助益。

基本表结构:

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

101,查询课程1的成绩比课程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
Salin selepas log masuk

102,查询平均成绩大于60分的同学的学号和平均成绩

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

103,查询所有同学的学号、姓名、选课数、总成绩

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
Salin selepas log masuk

或者:

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
Salin selepas log masuk

104,查询姓“张”的老师的个数

selectcount(distinct(tname)) from teacher where tname like '张%‘
Salin selepas log masuk

或者:

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

105,查询没学过“张三”老师课的同学的学号、姓名

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='张三')
Salin selepas log masuk

(推荐学习:java课程

106,查询同时学过课程1和课程2的同学的学号、姓名

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)
Salin selepas log masuk

或者:

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
Salin selepas log masuk

或者:

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)
Salin selepas log masuk

107,查询学过“李四”老师所教所有课程的所有同学的学号、姓名

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 = '李四')
Salin selepas log masuk

或者:

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
Salin selepas log masuk

108,查询课程编号1的成绩比课程编号2的成绩高的所有同学的学号、姓名

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
Salin selepas log masuk

109,查询所有课程成绩小于60分的同学的学号、姓名

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

110,查询至少有一门课程与学号为1的同学所学课程相同的同学的学号和姓名

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)
Salin selepas log masuk

或者:

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
Salin selepas log masuk

相关推荐:java入门教程

Atas ialah kandungan terperinci java高频率基础面试题——(九). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!

Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn

Tag artikel panas

Notepad++7.3.1

Notepad++7.3.1

Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina

SublimeText3 versi Cina

Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1

Hantar Studio 13.0.1

Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6

Dreamweaver CS6

Alat pembangunan web visual

SublimeText3 versi Mac

SublimeText3 versi Mac

Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

Akar Kuasa Dua di Jawa Akar Kuasa Dua di Jawa Aug 30, 2024 pm 04:26 PM

Akar Kuasa Dua di Jawa

Nombor Sempurna di Jawa Nombor Sempurna di Jawa Aug 30, 2024 pm 04:28 PM

Nombor Sempurna di Jawa

Penjana Nombor Rawak di Jawa Penjana Nombor Rawak di Jawa Aug 30, 2024 pm 04:27 PM

Penjana Nombor Rawak di Jawa

Nombor Armstrong di Jawa Nombor Armstrong di Jawa Aug 30, 2024 pm 04:26 PM

Nombor Armstrong di Jawa

Weka di Jawa Weka di Jawa Aug 30, 2024 pm 04:28 PM

Weka di Jawa

Nombor Smith di Jawa Nombor Smith di Jawa Aug 30, 2024 pm 04:28 PM

Nombor Smith di Jawa

Soalan Temuduga Java Spring Soalan Temuduga Java Spring Aug 30, 2024 pm 04:29 PM

Soalan Temuduga Java Spring

Cuti atau kembali dari Java 8 Stream Foreach? Cuti atau kembali dari Java 8 Stream Foreach? Feb 07, 2025 pm 12:09 PM

Cuti atau kembali dari Java 8 Stream Foreach?

See all articles