Shulou(Shulou.com)06/01 Report--

This article will explain in detail the database query questions that have a high rate of occurrence in the Java interview. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Basic table structure:

Teacher (tno,tname) teacher list

Student (sno,sname,sage,ssex) student form

Course (cno,cname,tno) course schedule

Sc (sno,cno,score) score sheet

NO.1 queries 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) bwhere a.score > b.score and a.sno=b.sno

NO.2 inquires the student number and grade point average of students whose GPA is greater than 60.

Select a.sno as "student number", avg (a.score) as "grade point average" from (select sno,score from sc) a group by sno having avg (a.score) > 60

NO.2 queries all students' student numbers, names, number of courses, and total grades.

Select a.sno as student number, b.sname as name, count (a.cno) as elective number, sum (a.score) as total score from sc a, student bwhere a.sno = b.snogroup by a.sno, b.sname

Or:

Selectstudent.sno as student number, student.sname as name, count (sc.cno) as elective number, sum (score) as total score from student left Outer join sc on student.sno = sc.snogroup by student.sno, sname

NO.3 queries the number of teachers surnamed "Zhang"

Selectcount (distinct (tname)) from teacher where tname like 'Zhang%'

Or:

Select tname as "name", count (distinct (tname)) as "number" from teacher where tname like' Zhang 'group by tname

NO.4 inquires the student numbers and names of students who have not learned "Zhang San" teacher class.

Select student.sno,student.sname from studentwhere sno not in (select distinct (sc.sno) from sc,course,teacherwhere sc.cno=course.cno and teacher.tno=course.tno and teacher.tname=' Zhang San')

NO.5 queries the student numbers and names of students who have taken both courses 1 and 2.

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) bJournal 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 inquires the student numbers and names of all the students who have studied all the courses taught by teacher Li Si.

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 ='Li Si')

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 ='Li Si') ewhere a.sno = b.sno and b.cno = e.cno

NO.7 queries the student numbers and names of all students whose scores of course number 1 are higher than those of course number 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

NO.8 inquires the student numbers and names of all students whose course scores are less than 60 points.

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

NO.9 queries the student numbers and names of students who have at least one course that is the same as that of students with student number 1.

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.snore.sname from student s, (select sc.sno from scwhere sc.cno in (select sc1.cno from sc sc1 where sc1.sno=1) and sc.sno1group by sc.sno) r1where r1.sno=s.sno about the database query questions with a high rate of occurrence in the Java interview, I hope that the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.