2015 Software College of Tianjin University------database

The first question ( - minutes) Database , write the answer directly on the test paper

The following three tables are available:

Table I: SC (sid,cid,score) score table

SID: Study number; CID, course number; Score: score (the default value is 0)

Table II: Teacher (tid,tname) Teachers table

TID: Teacher number; Tname: Teacher's name

Table III: Course (CID,CNAME) timetable

Please write the following SQL statement:

Test Data: Score table

Test Data: Course table

(1). Check average student scores and their rankings

Mysql> Select Temp.sid,temp.avg, @rank: = @rank + 1 as rank from (select SID,AVG (Score) as Avg. score GROUP BY Sid O Rder by avg desc) as temp, (select @rank: =0) as R;

(2). Statistics and printing of the results of each section, the number of points, the query results include the following columns:

Course ID, course name, [100-85] number, [85-70] people, [70-60] number, [<60] Person

Select Temp1.cid as ' Course ID ', c.cname as ' course name ', Ifnull (temp2.num,0) as ' [100-85] ', Ifnull (temp3.num,0) as ' [85-70] ', ifnull ( temp4.num,0) as ' [70-60] ', Ifnull (temp5.num,0) as ' [<60] ' from (select distinct CID from score) as Temp1 left join cours  E c on c.cid = Temp1.cid left JOIN (select CID, COUNT (*) as Num from score where score between and the group by CID) as Temp2 on temp1.cid = Temp2.cid left join (select Cid,count (*) as Num from score where score between all and all group by CI D) as Temp3 on temp3.cid = Temp1.cid left join (select Cid,count (*) as Num from score where score between by CID) as temp4 to Temp4.cid = Temp1.cid left join (select Cid,count (*) as Num from score where score <) as Temp5 O n Temp5.cid = temp1.cid ORDER by temp1.cid ASC;

SQL Execution Process:

(3). Check the student's number and average score of two or more failed courses

Mysql> Select S.sid,avg (s.score) from score s where S.sid in (select Temp.sid from (select S2.sid, COUNT (*) as Num from Score S2 where S2.score < GROUP by S2.SID have Num >= 2) as temp) group by S.SID;

Execution process:

Or

Mysql> Select S.sid, avg (S.score) from score S, (select S2.sid,count (*) as num from score S2 where S2.score < Grou P by S2.sid have num >= 2) as temp where s.sid = Temp.sid GROUP by S.SID;

Execution process:

Forgive me, write such a poor SQL bar ...

2015 Software College of Tianjin University------database