Java high-frequency basic interview questions——(9)
Sep 14, 2020 am 11:21 AMIf 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 選課數(shù), 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 選課數(shù), 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 "人數(shù)" 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!

Hot AI Tools

Undress AI Tool
Undress images for free

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

There are three common methods to traverse Map in Java: 1. Use entrySet to obtain keys and values at the same time, which is suitable for most scenarios; 2. Use keySet or values to traverse keys or values respectively; 3. Use Java8's forEach to simplify the code structure. entrySet returns a Set set containing all key-value pairs, and each loop gets the Map.Entry object, suitable for frequent access to keys and values; if only keys or values are required, you can call keySet() or values() respectively, or you can get the value through map.get(key) when traversing the keys; Java 8 can use forEach((key,value)->

Optional can clearly express intentions and reduce code noise for null judgments. 1. Optional.ofNullable is a common way to deal with null objects. For example, when taking values ??from maps, orElse can be used to provide default values, so that the logic is clearer and concise; 2. Use chain calls maps to achieve nested values ??to safely avoid NPE, and automatically terminate if any link is null and return the default value; 3. Filter can be used for conditional filtering, and subsequent operations will continue to be performed only if the conditions are met, otherwise it will jump directly to orElse, which is suitable for lightweight business judgment; 4. It is not recommended to overuse Optional, such as basic types or simple logic, which will increase complexity, and some scenarios will directly return to nu.

The core workaround for encountering java.io.NotSerializableException is to ensure that all classes that need to be serialized implement the Serializable interface and check the serialization support of nested objects. 1. Add implementsSerializable to the main class; 2. Ensure that the corresponding classes of custom fields in the class also implement Serializable; 3. Use transient to mark fields that do not need to be serialized; 4. Check the non-serialized types in collections or nested objects; 5. Check which class does not implement the interface; 6. Consider replacement design for classes that cannot be modified, such as saving key data or using serializable intermediate structures; 7. Consider modifying

In Java, Comparable is used to define default sorting rules internally, and Comparator is used to define multiple sorting logic externally. 1.Comparable is an interface implemented by the class itself. It defines the natural order by rewriting the compareTo() method. It is suitable for classes with fixed and most commonly used sorting methods, such as String or Integer. 2. Comparator is an externally defined functional interface, implemented through the compare() method, suitable for situations where multiple sorting methods are required for the same class, the class source code cannot be modified, or the sorting logic is often changed. The difference between the two is that Comparable can only define a sorting logic and needs to modify the class itself, while Compar

Method reference is a way to simplify the writing of Lambda expressions in Java, making the code more concise. It is not a new syntax, but a shortcut to Lambda expressions introduced by Java 8, suitable for the context of functional interfaces. The core is to use existing methods directly as implementations of functional interfaces. For example, System.out::println is equivalent to s->System.out.println(s). There are four main forms of method reference: 1. Static method reference (ClassName::staticMethodName); 2. Instance method reference (binding to a specific object, instance::methodName); 3.

There are three common ways to parse JSON in Java: use Jackson, Gson, or org.json. 1. Jackson is suitable for most projects, with good performance and comprehensive functions, and supports conversion and annotation mapping between objects and JSON strings; 2. Gson is more suitable for Android projects or lightweight needs, and is simple to use but slightly inferior in handling complex structures and high-performance scenarios; 3.org.json is suitable for simple tasks or small scripts, and is not recommended for large projects because of its lack of flexibility and type safety. The choice should be decided based on actual needs.

How to quickly create new emails in Outlook is as follows: 1. The desktop version uses the shortcut key Ctrl Shift M to directly pop up a new email window; 2. The web version can create new emails in one-click by creating a bookmark containing JavaScript (such as javascript:document.querySelector("divrole='button'").click()); 3. Use browser plug-ins (such as Vimium, CrxMouseGestures) to trigger the "New Mail" button; 4. Windows users can also select "New Mail" by right-clicking the Outlook icon of the taskbar

To deal with character encoding problems in Java, the key is to clearly specify the encoding used at each step. 1. Always specify encoding when reading and writing text, use InputStreamReader and OutputStreamWriter and pass in an explicit character set to avoid relying on system default encoding. 2. Make sure both ends are consistent when processing strings on the network boundary, set the correct Content-Type header and explicitly specify the encoding with the library. 3. Use String.getBytes() and newString(byte[]) with caution, and always manually specify StandardCharsets.UTF_8 to avoid data corruption caused by platform differences. In short, by
