Java face question-database chapter

As a Java back-end developer, database knowledge is essential, to the database of the familiarity of the investigation is also on whether the person has a solid basic skills. Especially for junior developers, interviews may not ask the framework for knowledge, but they will never go into database knowledge, and here are some common types of SQL statements that can be helpful both for normal development and for preparing interviews.

Basic table structure:

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

Teacher (tno,tname) Teacher table

101, check the number of all students who have a high score of 1 of 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, the number of students with average scores greater than 60 points and average scores
Select A.sno as "academic number", AVG (A.score) as "average score"
From
(select Sno,score from SC) a
GROUP BY SNO have avg (a.score) >60

103, check all students of the student number, name, number of courses selected, total
Select A.sno as study number, b.sname as name,
Count (A.CNO) As course count, sum (a.score) as Total
From SC A, student B
where A.sno = B.sno
Group BY A.sno, B.sname

Or:

Selectstudent.sno as school number, student.sname as name,
Count (SC.CNO) As course count, sum (score) as Total
From student left Outer join SC on student.sno = Sc.sno
Group BY Student.sno, sname

104, the number of teachers to inquire the surname "Zhang"

SelectCount (Distinct (tname)) from teacher where tname like ' Zhang% '
Or:
Select Tname as "name", COUNT (Distinct (tname)) as "number of people"
From teacher
Where Tname like ' Zhang% '
GROUP BY Tname

105, inquiry did not learn the "Zhang San" teacher class students of the school 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= ' Zhang San ')

106, check the number and name of the students who have also studied course 1 and course 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)
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 inquiry has learned the "John Doe" teacher all courses taught by all students of the school 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 = ' John Doe ')

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

108, check the course number 1 of the results of the course number 2 is higher than the number of all students, the name of the student
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 the student's number and name of all the students who have achieved less than 60 points.
Select Sno,sname from Student
Where Sno not in (SELECT DISTINCT Sno from SC where score > 60)

110, check at least one course with the student number 1 students learn the same class number and name of the students
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 bosom friend of the public to organize some of the major companies commonly used to interview pen questions, for everyone in the daily spare time to learn a few of the topics, the cumulative, wait until the interview, all the time, the interview will naturally be at ease.

As a Java back-end developer, database knowledge is essential, to the database of the familiarity of the investigation is also on whether the person has a solid basic skills. Especially for junior developers, interviews may not ask the framework for knowledge, but they will never go into database knowledge, and here are some common types of SQL statements that can be helpful both for normal development and for preparing interviews.

Basic table structure:

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

Teacher (tno,tname) Teacher table

111, the "SC" table "Harry" The results of the lessons are changed to the average performance of this course
Update SC Set score = (select AVG (sc_2.score) from SC sc_2 wheresc_2.cno=sc.cno)
From Course,teacher where Course.cno=sc.cno and Course.tno=teacher.tno andteacher.tname= ' Harry '

112, inquiry and number 2 of the students to study the course of the exact same other student number and name
This topic is divided into two steps:

1,

Select Sno
From SC
Where Sno <> 2
GROUP BY Sno
Having sum (CNO) = (select sum (CNO) from SC where sno = 2)

2,
Select B.sno, B.sname
From SC A, student B
where B.sno <> 2 and A.sno = B.sno
Group BY B.sno, B.sname
Having sum (CNO) = (select sum (CNO) from SC where sno = 2)

113, delete learning "Harry" the SC table record of the teacher class
Delete sc from course, teacher
where course.cno = sc.cno and Course.tno = Teacher.tno and tname = ' Harry '

114, insert some records into the SC table, these records require the following conditions:
The results of the students who do not have 3 grades will be the result of 2 of the average grade of all student's courses .
Insert SC Select Sno, 3, (select AVG (score) from SC where CNO = 2)
From student
Where Sno not in (select Sno from sc where CNO = 3)

115, according to the flat average score from high to low display all students the following statistical report:
--School number, enterprise management, Marx, UML, Database, physics, course number, average score
Select Sno as study number
, Max (case if CNO = 1 then score end) as Enterprise management
, Max (case if CNO = 2 then score end) as Marx
, Max (case if CNO = 3 then score end) as UML
, Max (case if CNO = 4 then score end) as database
, Max (case if CNO = 5 then score end) as physical
, Count (CNO) as course number
, AVG (score) as average score
From SC
GROUP by Sno
ORDER by AVG (score) DESC

116. Check the highest score and the lowest score of each section:

Displayed as follows: Course number, highest score, lowest score
Select CNO as course number, MAX (score) as highest score, min (score) Lowest score
From SC GROUP by CNO

Select Course.cno as ' Course number '
, Max (score) as ' highest score '
, Min (score) as ' minimum score '
From Sc,course
where Sc.cno=course.cno
GROUP BY Course.cno

117, according to the average grades from low to high and the percentage of passing rate from high to low order
SELECT T.cno as course number,
Max (Course.cname) as course name,
IsNull (AVG (Score), 0) as average score,
* SUM (case when IsNull (score,0) >=60 then 1 ELSE 0 END)/count (1) as pass rate
From SC T, course
where t.cno = Course.cno
GROUP by T.cno
ORDER by pass rate desc

118. Check the percentage of average and pass rate for the following courses (shown with "1 lines"):

Enterprise Management (001), Marx (002), UML (003), Database (004)
Select
AVG (case if CNO = 1 then score end) as an average of 1,
AVG (case if CNO = 2 then score end) as an average of 2,
AVG (case If CNO = 3 then score end) as an average of 3,
AVG (case If CNO = 4 then score end) as an average of 4,
* SUM (case when CNO = 1 and score > 1 Else 0 end)/sum (casewhen CNO = 1 then 1 else 0 end) as pass rate 1,
* SUM (case when CNO = 2 and score > 1 Else 0 end)/sum (casewhen CNO = 2 Then 1 else 0 end) as pass rate 2,
* SUM (case when CNO = 3 and score > 1 Else 0 end)/sum (casewhen CNO = 3 then 1 else 0 end) as pass rate 3,
* SUM (case when CNO = 4 and score > 1, Else 0 end)/sum (casewhen CNO = 4 Then 1 else 0 end) as pass rate 4
From SC

119. Check the average of different courses taught by different teachers, from high to low display
Select Max (c.tname) as teacher, Max (b.cname) course, AVG (A.score) average
From SC A, course B, teacher C
where a.cno = b.cno and B.tno = C.tno
GROUP BY A.cno
ORDER BY average Desc
Or:
Select R.tname as ' Teacher ', r.rname as ' Course ', AVG (score) as ' average '
From SC,
(select T.tname,c.cno as rcso,c.cname as Rname
From teacher T, course C
where T.tno=c.tno) r
where SC.CNO=R.RCSO
GROUP BY Sc.cno,r.tname,r.rname
Order by AVG (score) desc

120. Check the results of students who are between 3rd and 6th in the following courses:
--[Student id],[student name], Enterprise management, Marx, UML, database, average score
Select Top 6 max (A.SNO) number, max (b.sname) name,
Max (case if CNO = 1 then score end) as Enterprise management,
Max (case if CNO = 2 then score end) as Marx,
Max (case if CNO = 3 then score end) as UML,
Max (case if CNO = 4 then score end) as database,
AVG (score) as average score
From SC A, student B
Where A.sno not in

(select top 2 sno from SC where CNO = 1 ORDER BY score Desc)
and A.sno not in (select top 2 sno from SC where CNO = 2 ORDER by Scoredesc)
and A.sno not in (select top 2 sno from SC where CNO = 3 ORDER by Scoredesc)
and A.sno not in (select top 2 sno from SC where CNO = 4 ORDER by Scoredesc)
and A.sno = B.sno
GROUP BY A.sno

Java bosom friend of the public to organize some of the major companies commonly used to interview pen questions, for everyone in the daily spare time to learn a few of the topics, the cumulative, wait until the interview, all the time, the interview will naturally be at ease.

Java face question-database chapter