Database face questions and Answers __ Database

Reproduced from: Database Face questions and answers

Student (stuid,stuname,stuage,stusex) Student table

Stuid: School Number stuname: student name; Stuage: Student age; Stusex: Student gender

Course (Courseid,coursename,teacherid) timetable

CourseID, course number; Coursename: course name; Teacherid: Teacher number

Scores (stuid,courseid,score) score sheet

Stuid: School number; CourseID, course number; Score: Results

Teacher (teacherid,teachername) Teacher's Table

Teacherid: Teacher number; TeacherName: Teacher's name

Problem:

1, Query the "001" Course than "002" course scores of all students of the school number;

Select A.stuid from (select Stuid,score from Scores where courseid= ' 001 ') A, (select Stuid,score

From Scores where courseid= ' 002 ') b

where A.score>b.score and A.stuid=b.stuid;

2, the average score is greater than 60 students of the school number and average score;

Select Stuid,avg (Score)

From Scores

GROUP BY STUID have AVG (score) >60;

3, inquires all the student's study number, the name, the choice course number, the total score;

Select Student.stuid,student.stuname,count (Scores.courseid), SUM (score)

From Student left Outer join Scores on Student.stuid=scores.stuid

GROUP BY Student.stuid,stuname

4, inquires the surname "Li" the number of teachers;

Select COUNT (Distinct (teachername))

From Teacher

Where TeacherName like ' li% ';

5, the query did not learn the "cotyledons" teacher class students of the school number, name;

Select Student.stuid,student.stuname

From Student

where Stuid not in (select DISTINCT (SCORES.STUID) from Scores,course,teacher where Scores.courseid=course.courseid and T Eacher.teacherid=course.teacherid and Teacher.teachername= ' cotyledons ');

6, the query learned "001" and also learned the number "002" course of the student's school number, name;

Select Student.stuid,student.stuname from Student,scores where Student.stuid=scores.stuid and scores.courseid= ' 001 ' and exists (Select * from Scores as scores_2 where Scores_2.stuid=scores.stuid and scores_2.courseid= ' 002 ');

7, the query learned "cotyledons" teacher taught all the students of the class number, name;

Select Stuid,stuname

From Student

where Stuid in (select Stuid from Scores, Course, Teacher where Scores.courseid=course.courseid and Teacher.teacherid=cour Se.teacherid and Teacher.teachername= ' cotyledons ' GROUP by Stuid have count (scores.courseid) = (select count (CourseID) from Course,teacher where Teacher.teacherid=course.teacherid and Teachername= ' cotyledons ');

8, inquires the course number "002" The result is more than the course number "001" Curriculum low all schoolmate's student number, the name;

Select Stuid,stuname from (select Student.stuid,student.stuname,score, select Score from Scores scores_2 where scores_2. Stuid=student.stuid and scores_2.courseid= ' 002 ') Score2

From Student,scores where Student.stuid=scores.stuid and courseid= ' 001 ') s_2 where Score2 <score;

9, query all the course score is less than 60 students of the school number, name;

Select Stuid,stuname

From Student

where Stuid not in (select Student.stuid from Student,scores where S.stuid=scores.stuid and score>60);

10, the query did not learn the whole class of students of the student number, name;

Select Student.stuid,student.stuname

From Student,scores

where Student.stuid=scores.stuid GROUP by Student.stuid,student.stuname have count (CourseID) < (select count ( CourseID) from Course);

11, inquires at least one course and the study number is "1001" schoolmate learns the same schoolmate the student number and the name;

Select Stuid,stuname from Student,scores where Student.stuid=scores.stuid and CourseID into select CourseID from Scores wher E stuid= ' 1001 ';

12, inquiry at least learned the number of "001" Students of all the other students learn number and name of a class;

SELECT DISTINCT Scores.stuid,stuname

From Student,scores

where Student.stuid=scores.stuid and CourseID in (select CourseID from Scores where stuid= ' 001 ');

13, the "Scores" table "cotyledons" teachers to teach the results of the course changes to the average grade;

Update Scores Set score= (select AVG (scores_2.score)

From Scores scores_2

where Scores_2.courseid=scores.courseid) from Course,teacher where Course.courseid=scores.courseid and Course.teacherid=teacher.teacherid and Teacher.teachername= ' cotyledons ');

14, the inquiry and "1002" number of students to learn the same course of the other students to learn number and name;

Select Stuid from Scores where CourseID to (select CourseID from Scores where stuid= ' 1002 ')

GROUP BY Stuid has count (*) = (select count (*) from Scores where stuid= ' 1002 ');

15, delete learning "cotyledons" teacher class scores table record;

Delect Scores

From course, Teacher

where Course.courseid=scores.courseid and course.teacherid= Teacher.teacherid and teachername= ' cotyledons ';

16. Insert some records into the scores table, these records require the following conditions: There is no number of "003" course of the classmate number, 2,

The average score of the class;

Insert Scores Select Stuid, ' 002 ', (select AVG (Score)

From Scores where courseid= ' 002 ' to Student where Stuid not in (Select stuid from Scores where courseid= ' 002 ');

17, according to the average score from high to low show all students of the "database", "Enterprise Management", "English" three-course results, as shown in the following form: Student ID, database, business management, English, effective course number, effective average score

SELECT Stuid as Student ID

, (SELECT score from Scores WHERE Scores.stuid=t.stuid and courseid= ' 004 ') as database

, (SELECT score from Scores WHERE Scores.stuid=t.stuid and courseid= ' 001 ') as Enterprise management

, (SELECT score from Scores WHERE Scores.stuid=t.stuid and courseid= ' 006 ') as English

, COUNT (*) as effective course number, AVG (T.score) as average score

From Scores as T

GROUP by Stuid

ORDER by AVG (T.score)

18, inquires the highest and lowest points of the subjects: in the following form: Course ID, highest score, lowest score

SELECT L.courseid as course Id,l.score as the highest score, R.score as the lowest score

From Scores L, Scores as R

WHERE L.courseid = R.courseid and

L.score = (SELECT MAX (Il.score)

From Scores as il,student as IM

WHERE L.courseid = Il.courseid and Im.stuid=il.stuid

GROUP by Il.courseid)

and

R.score = (SELECT MIN (Ir.score)

From Scores as IR

WHERE R.courseid = Ir.courseid

GROUP by Ir.courseid

);

19. From low to high and the percentage of the passing rate from highest to lowest according to the average grade of each section

SELECT T.courseid as course number, Max (Course.coursename) As course name, ISNULL (AVG (Score), 0) as average score

, SUM (case when IsNull (score,0) >=60 THEN 1 ELSE 0)/count (*) as pass percentage

From Scores T,course

where T.courseid=course.courseid

GROUP by T.courseid

Order from * SUM (case when IsNull (score,0) >=60 THEN 1 ELSE 0-end)/count (*) descores

20. Find out the percentage of average and passing rates for the following courses (shown in "1 lines"): Enterprise Management (001), Marx (002), OO&UML (003), Database (004)

SELECT SUM (case when CourseID = ' 001 ' THEN score Else 0 ")/sum (case CourseID when ' 001 ' THEN 1 ELSE 0-end) as Enterprise management average

, SUM (case when CourseID = ' 001 ' and score >= THEN 1 Else 0 ")/sum (case when CourseID = ' 001 ' THEN 1 Else 0 E ND) as Business Management pass percentage

, SUM (case when CourseID = ' 002 ' THEN score Else 0 ")/sum (case CourseID when ' 002 ' THEN" 1 ELSE 0 end) as Marx average score

, SUM (case when CourseID = ' 002 ' and score >= THEN 1 Else 0 ")/sum (case when CourseID = ' 002 ' THEN 1 Else 0 E ND) as Marx percent of passing

, SUM (case when CourseID = ' 003 ' THEN score Else 0 ")/sum (case CourseID when ' 003 ' THEN 1 Else 0-end) as UML average

, SUM (case when CourseID = ' 003 ' and score >= THEN 1 Else 0 ")/sum (case when CourseID = ' 003 ' THEN 1 Else 0 E ND) as UML pass percentage

, SUM (case when CourseID = ' 004 ' THEN score Else 0 ")/sum (case CourseID when ' 004 ' THEN ' 1 else 0-end) as database average

, SUM (case when CourseID = ' 004 ' and score >= THEN 1 Else 0 ")/sum (case when CourseID = ' 004 ' THEN 1 Else 0 E ND) as database passing percentage

From Scores

21, inquires the different teacher to teach the different course average score from the high to the low display

SELECT Max (Z.teacherid) as Teacher Id,max (Z.teachername) as teacher name, C.courseid as Course Id,max (c.coursename) As course name, AVG (score) as Average score

From Scores as t,course as C, Teacher as Z

where T.courseid=c.courseid and C.teacherid=z.teacherid

GROUP by C.courseid

Order by AVG (score) descores

22, inquires the following course score 3rd to 6th Student Transcript: Enterprise Management (001), Marx (002), UML (003), Database (004)

[Student id],[student name], business management, Marx, UML, database, average score

SELECT DISTINCT Top 3

Scores.stuid as student study number,

Student.stuname as student name,

T1.score as Enterprise management,

T2.score as Marx,

T3.score as UML,

T4.score as Database,

ISNULL (t1.score,0) + ISNULL (t2.score,0) + ISNULL (t3.score,0) + ISNULL (t4.score,0) as total score

From Student,scores left JOIN Scores as T1

On scores.stuid = t1.stuid and T1.courseid = ' 001 '

Left JOIN Scores as T2

On scores.stuid = t2.stuid and T2.courseid = ' 002 '

Left JOIN Scores as T3

On scores.stuid = t3.stuid and T3.courseid = ' 003 '