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

This article introduces the relevant knowledge of "what are the interview questions in the database in java". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Basic table structure:

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

Course (cno,cname,tno) course schedule

Sc (sno,cno,score) score sheet

Teacher (tno,tname) teacher list

111. change the grades taught by the "Wang Wu" in the "sc" table to the average grade point 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=' Wang Wu'

112, inquire about the student number and name of the other students whose courses are exactly the same as those of the students with number 2.

This question is divided into two steps:

one,

Select sno

From sc

Where sno 2

Group by sno

Having sum (cno) = (select sum (cno) from sc where sno = 2)

two,

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)

Delete the sc table records of learning "Wang Wu" teacher's class.

Delete sc from course, teacher

Where course.cno = sc.cno and course.tno = teacher.tno and tname = 'Wang Wu'

Insert records into the sc table that meet the following conditions:

Make up the score of the student without course 3, which is equal to the average score of all students in course 2.

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. the following statistical report shows all students from high to low according to the average score:

-- student number, business management, Marx, UML, database, physics, number of courses, average score

Select sno as student number

, max (case when cno = 1 then score end) AS enterprise management

, max (case when cno = 2 then score end) AS Marx

, max (case when cno = 3 then score end) AS UML

, max (case when cno = 4 then score end) AS database

, max (case when cno = 5 then score end) AS physics

, count (cno) AS courses

, avg (score) AS average score

FROM sc

GROUP by sno

ORDER by avg (score) DESC

116. inquire about the highest and lowest scores of each subject:

Displayed in the following form: course number, highest score, lowest score

Select cno as course number, the highest score for max (score) as and the lowest score for min (score)

From sc group by cno

Select course.cno as' course number'

, MAX (score) as' highest score'

, MIN (score) as' lowest score'

From sc,course

Where sc.cno=course.cno

Group by course.cno

117. According to the average score of each subject from low to high and the percentage of passing rate from high to low.

SELECT t.cno AS course number

Max (course.cname) AS course name

Isnull (AVG (score), 0) AS average score

100 * 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. Inquire about the percentage of the average grade and passing rate of the following courses (shown in "1 line"):

Enterprise Management, Marx, UML, Database

Select

Avg (case when cno = 1 then score end) as average score 1

Avg (case when cno = 2 then score end) as average score 2

Avg (case when cno = 3 then score end) as average score of 3

Avg (case when cno = 4 then score end) as average score 4

100 * sum (casewhen cno = 1 and score > 60 then 1 else 0 end) / sum (casewhen cno = 1 then 1 else 0 end) as pass rate 1

100 * sum (casewhen cno = 2 and score > 60 then 1 else 0 end) / sum (casewhen cno = 2 then 1 else 0 end) as pass rate 2

100 * sum (casewhen cno = 3 and score > 60 then 1 else 0 end) / sum (casewhen cno = 3 then 1 else 0 end) as pass rate 3

100 * sum (casewhen cno = 4 and score > 60 then 1 else 0 end) / sum (casewhen cno = 4 then 1 else 0 end) as pass rate 4

From sc

119. Query the average scores of different courses taught by different teachers, from high to low.

Select max (c.tname) as teacher, max (b.cname) course, avg (a.score) average score

From sc a, course b, teacher c

Where a.cno = b.cno and b.tno = c.tno

Group by a.cno

Average score of order by desc

Or:

Select r.tname as' teacher', r.rname as' course', AVG (score) as' average score'

From sc

(select t.tame.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. enquire about the scores of the following students whose course scores are between No. 3 and No. 6:

-- [student ID], [student name], business management, Marx, UML, database, GPA

Select top 6 max (a.sno) student number, max (b.sname) name

Max (case when cno = 1 then score end) as enterprise management

Max (case when cno = 2 then score end) as Marx

Max (case when cno = 3 then score end) as UML

Max (case when cno = 4 then score end) as database

Average score of avg (score) as

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

This is the end of the content of "what are the interview questions in the database in java"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.