Java face test-database article 11 __c#

Java face test-database article 11

As a Java back-end developer, database knowledge is essential to the database to master the familiarity of the investigation is the person has a solid basic skills. Especially for novice developers, the interview may not ask about framework-related knowledge, but will not go to the database knowledge, here to collect some common types of SQL statements, whether for the normal development or preparation for the interview, will be helpful.

Basic table structure:

Student (sno,sname,sage,ssex) Student table
Course (CNO,CNAME,TNO) timetable
SC (sno,cno,score) score sheet

Teacher (tno,tname) Teacher's Table

101, the study number of all the students who have scored higher in the course 1 than the 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, check the average score is greater than 60 students of the school number and average score
Select A.sno as "school Number", AVG (A.score) as "average score"
From
(select Sno,score from SC) a
GROUP BY SNO has avg (A.score) >60

103, check all students ' study number, name, course number, total score
Select A.sno as number, b.sname as name,
Count (A.CNO) as class number, sum (a.score) as total score
From SC A, student B
where A.sno = B.sno
Group BY A.sno, B.sname

Or:

Selectstudent.sno as number, student.sname as name,
Count (SC.CNO) as class number, SUM (score) as total score
From student left Outer join SC on student.sno = Sc.sno
Group BY Student.sno, sname

104, the number of the teacher who inquires the surname "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

105, the query has not learned the "John" teacher class student number, name
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= ' John ')

106, query the students who have studied 1 and 2 of the class 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, the query learned the "Dick" teacher taught all the courses of the student number, name
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 = ' dick ')

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 = ' dick ') E
where A.sno = B.sno and B.cno = E.cno

108, inquires the course number 1 The result is higher than the course number 2 The student number, the 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, check all the course score is less than 60 students student number, name
Select Sno,sname from Student
Where Sno not in (SELECT DISTINCT Sno from SC where score > 60)

110, check the number and name of the students who have at least one course with the same class as the student with number 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)

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

Java Companion public number of major companies to organize a number of commonly used interview pen questions, for everyone in the spare time to learn some of the topics, accumulated over time, wait for the interview, everything is ripe, the interview will naturally be easier.