SQL Stats Student score 2

Article Source: http://www.ynpxrz.com/n822738c2024.aspx

T_tudent (sid,sname,sage,ssex,sdept) Student table

T_course (Cid,cname,tid) timetable

T_score (scid,sid,cid,grade) score table

T_teacher (tid,tname) Teacher table

Problem:

1, the inquiry "001" course is higher than "002" of all students of the school number;

SelectT1.sid from(SelectSid,grade fromT_scorewhereCid= '001') T1, (SelectSid,grade fromT_scorewhereCid= '002') T2whereT1.grade>T2.grade andT1.sid=T2.sid;SelectT2.SID,T2.CA,T2.CB (SelectT1.sid,sum(t1.ca) CA,sum(T1.CB) CB from(SelectSID, Case  whenCid= '001'  ThenGradeELSE 0 ENDCA, Case  whenCid= '002'  ThenGradeELSE 0 ENDCB fromt_score) T1GROUP  bySID) T2wheret2.ca>T2.cb

View Code

2, the query average score is more than 60 points of the student's number and average score;

Select Sid,avg (grade) from T_score

GROUP BY Sid

Having avg (grade) >60;

3, inquiry all students of the school number, name, number of courses selected, total;

Select T1.sid,t1.sname,count (t2.cid), sum (grade) from T_student T1

Left JOIN T_score T2

On t1.sid = T2sid

Group BY T1sid,t1.sname;

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

Select COUNT (Distinct (tname)) from T_teacher where tname like ' li% ';

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

Select Sid, Sname from T_student

Where Sid not in

(

SELECT DISTINCT (T1.SID) from T_score t1,t_course t2,t_teacher T3

where t1.cid = t2.cid and T3.tid = T2.tid and t3.tname = ' cotyledons '

);

6, the inquiry learned "001" and also learned the number "002" course of the students ' study number, name;

Select T0.sid,t0.sname from T_student t0, T_score t1

where T0.sid = t1.sid and t1.cid = ' 001 '

and exists

(

SELECT * from T_score t2 where t2.sid = t1.sid and t2.cid = ' 002 '

);

7, the inquiry has learned "Cotyledons" the teacher teaches all classes The student's school number, the name;

Select Sid,sname from T_student

where SID in

(

Select Sid from T_score T1, T_course t2,t_teacher T3

where T1.cid=t2.cid

and T3.tid=t2.tid

and t3.tname = ' cotyledons '

GROUP BY Sid

Having count (t1.cid) = (

Select COUNT (CID) from T_course T1,t_teacher T2

where T2.tid=t1.tid and t2.tname = ' cotyledons '

)

);

8, the query "001" Course than "002" course performance of all students of the school number, name;

Select Sid,sname from

(

Select T1.sid,t1.sname,t2.grade,

(

Select grade from T_score T3

where T3.sid = t1.sid and t3.cid = ' 002 '

) Grade2

From T_student T1, T_score T2

where T1sid = t2.sid and t2.cid = ' 001 '

) s_2

Where Grade 2 < grade;

9, check all the course results are less than 60 points of the student's school number, name;

Select Sid,sname from T_student

Where Sid not in

(

Select T1.sid from t_student T1, T_score T2

where T1.sid = T2.sid and T2.grade >60

);

10, the inquiry did not learn all the class student's school number, the name;

Select T1.sid,t1.sname from Student T1, T_score T2

where T1.sid = T2.sid

GROUP BY T1.sid,t1.sname

Have count (t2.cid) < (select count (CID) from T_course);

11, the inquiry has at least one course and the school number for "1001" students learn the same student number and name;

Select Sid,sname from t_student T1, T_score T2

where T1.sid = T2.sid

and CID in (the Select CID from t_score where sid = ' 1001 ');

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

Select DISTINCT t2.sid,t1.sname from t_student T1, T_score T2

where T1.sid = T2.sid

and CID in (the Select CID from t_score where sid = ' 001 ');

13, the "T_score" table "cotyledons" teacher teaching the results of the course are changed to the average performance of the curriculum;

Update T_score t4 Set grade = (select AVG (t3.grade) from T_score T3 where t3.cid= t4.cid)

From T_course T1,t_teacher T2

where t1.cid = T4.cid

and T1.tid = T2.tid

and t2.tname = ' cotyledons ');

14, inquires and "1002" number of students to study the course of the same class of other students and the name of the school;

Select Sid from T_score

Where CID in (select CID from t_score where sid = ' 1002 ')

GROUP BY Sid

Have count (*) = (select count (*) from t_score where sid = ' 1002 ');

15, delete the learning "cotyledons" Teacher class T_score table records;

Delect T_score from T_course T1, T_teacher T2

Where t1.cid = T2.cid

and T1.tid = T2.tid

and t2.tname = ' cotyledons ';

16, insert some records into the T_score table, these records require the following conditions: No number "003" of the course's student number, 2,

The average grade of the class;

Insert T_score

Select Sid, ' 002 ', (select AVG (grade) from T_score where cid = ' 002 ')

From T_student

Where Sid is not in (the Select SID from T_score WHERE cid = ' 002 ');

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

SELECT SID as Student ID,

(SELECT grade from T_score WHERE Sid=t.sid and cid= ' 004 ') As database,

(SELECT grade from T_score WHERE Sid=t.sid and cid= ' 001 ') As Enterprise management,

(SELECT grade from T_score WHERE Sid=t.sid and cid= ' 006 ') As English,

COUNT (*) as effective number of courses,

AVG (T.grade) as average score

From T_score as T

GROUP by Sid

ORDER by AVG (T.grade)

18. Check the highest and lowest score of each section: show the following form: Course ID, highest score, lowest score

SELECT T1.cid as id,t1.grade as highest score, t2.grade as Min.

From T_score T1, T_score T2

WHERE t1.cid = T2.cid

and T1.grade = (

SELECT MAX (Tt1.grade) from T_score tt1,t_student tt2

WHERE t1.cid = tt1.cid and Tt2.sid = Tt1.sid

GROUP by Tt1.cid

)

and T2.grade = (

SELECT MIN (Tt3.grade) from T_score as Tt3

WHERE t2.cid = Tt3.cid GROUP by tt2.cid

);

19, according to the average grades from low to high and the percentage of passing rate from high to low order

SELECT T.cid as course number, Max (Course.cname) As course name, ISNULL (avg (grade), 0) as average score,

* SUM (case if IsNull (grade, 0) >=60 then 1 ELSE 0 END)/count (*) as passing percentage

From T_score T1,t_course T2

where t1.cid = T2.cid

GROUP by T1.cid

ORDER by $ * SUM (case when IsNull (grade, 0) >=60 then 1 ELSE 0 END)/count (*) DESC

20. Check the percentage of average and pass rate for the following courses (shown with "1 lines"): Enterprise Management (001), Marx (002), OO&UML (003), Database (004)

SELECT

SUM (

case when CID = ' 001 '

Then grade ELSE 0 END)/sum (case CID when ' 001 '

Then 1 ELSE 0 END

As Enterprise management average score,

* SUM (

case when CID = ' 001 ' and grade >= 60

Then 1 ELSE 0 END)/sum (case when cid = ' 001 '

Then 1 ELSE 0 END

As enterprise management passing percentage from T_score

21, query different teachers teach different courses average from high to low display

SELECT Max (T3.tid) as Teacher Id,max (T3.tname) as teacher name,

T2.cid as Course Id,max (t2.cname) As course name, AVG (grade) as average score

From T_score as t1,t_course as T2, t_teacher as T3

where t1.cid = t2.cid and T2.tid = T3.tid

GROUP by T2.cid

ORDER by AVG (grade) DESC;

22, query The following course results 3rd to 6th Students Transcript: Enterprise Management (001), Marx (002), UML (003), Database (004)

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

SELECT DISTINCT Top 3 T_score.sid as student number, student.sname as student name,

T1.grade as Enterprise management, T2.grade as Marx, T3.grade as UML,

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

From Student, T_score

Left JOIN T_score as T1

On t_score.sid = t1.sid and t1.cid = ' 001 '

Left JOIN T_score as T2

On t_score.sid = t2.sid and t2.cid = ' 002 '

Left JOIN T_score as T3

On t_score.sid = t3.sid and t3.cid = ' 003 '

Left JOIN T_score as T4

On t_score.sid = t4.sid and t4.cid = ' 004 '

WHERE student.sid= T_score.sid

and ISNULL (T1.grade, 0) + ISNULL (t2.grade, 0) + ISNULL (t3.grade, 0) + ISNULL (t4.grade, 0)

Not IN (

SELECT DISTINCT TOP with TIES ISNULL (t1.grade, 0) + ISNULL (t2.grade, 0) + ISNULL (t3.grade, 0) + ISNULL (t4.grade, 0)

From T_score

Left JOIN T_score as T1

On t_score.sid = t1.sid and t1.cid = ' K1 '

Left JOIN T_score as T2

On t_score.sid = t2.sid and t2.cid = ' K2 '

Left JOIN T_score as T3

On t_score.sid = t3.sid and t3.cid = ' K3 '

Left JOIN T_score as T4

On t_score.sid = t4.sid and t4.cid = ' K4 '

ORDER by ISNULL (t1.grade, 0) + ISNULL (t2.grade, 0) + ISNULL (t3.grade, 0) + ISNULL (t4.grade, 0) DE T_score

);

23, the statistics print each section result, each score segment number: Course ID, course name, [100-85],[85-70],[70-60],[<60]

SELECT T1.cid as Course ID, t2.cname as course name,

SUM (case when t1.grade between and 1 ELSE 0 END) as [100-85],

SUM (case when t1.grade between-and-then 1 ELSE 0 END) as [85-70],

SUM (case when t1.grade between and 1 ELSE 0 END) as [70-60],

SUM (case when T1.grade < 1 ELSE 0 END) as [60-]

From T_score t1,t_course t2 where t1.cid = T2.cid

GROUP by T1.cid,t2.cname;

24, check the average grade of students and their rankings

SELECT

1+ (

SELECT COUNT (Distinct avggrade)

From (

SELECT Sid,avg (grade) as Avggrade from T_score GROUP by Sid

As T1 where Avggrade > T2.avggrade) as Rank,

Sid as student school number,

Avggrade

From (

SELECT Sid,avg (grade) as Avggrade from T_score GROUP by Sid

) as T2

ORDER by Avggrade Desc;

25. Check the records of the top three grades of each section: (regardless of the performance of the situation)

SELECT T1.sid as Student id,t1.cid as course id,grade as fraction

From T_score T1 WHERE grade in (

SELECT TOP 3 grade from T_score t2 WHERE t1.cid = T2.cid ORDER by grade DESC

)

ORDER by T1.cid;

26. Check the number of students who have been enrolled in each course

Select Cid,count (SID) from the T_score group by CID;

27. Find out the number and name of all students who have only one course of study

Select T1.sid,t2.sname,count (t1.cid) As course number from T_score t1,t_student T2

where T1.sid=t2.sid

Group BY T1.sid, T2.sname

Having count (t1.cid) = 1;

28. Query the number of boys and girls

Select count (ssex) as the number of males from the T_student group by Ssex have ssex= ' male ';

Select count (ssex) as the number of females from the T_student group by Ssex have ssex= ' female ';

29. Check the list of students with the surname "Zhang"

SELECT sname from t_student WHERE sname like ' Zhang% ';

30, check the same-name list of same-sex students, and statistics of the same number

Select Sname,count (*) from T-student GROUP by Sname have Count (*) >1;

31.1981-Born Student list (note: The type of sage column in the student table is datetime)

Select Sname, CONVERT (char (one), DATEPART (Year,sage)) as age

From T_tudent

where CONVERT (char (one), DATEPART (year,sage)) = ' 1981 ';

32, the average score of each course is queried, the results are arranged in ascending order of average grade, and the average result is the same, descending by the course number.

Select Cid,avg (grade) from T_score Group by CID ORDER by AVG (grade), CID DESC

33. The number, name and average scores of all students who have average scores greater than 85

Select T1.sname, T2.sid, avg (t2.grade) from t_student T1, T_score T2

where T1.sid=t2.sid GROUP by T2.sid,t1.sname

Having avg (T2.grade) >85;

34. Check the name and score of the student whose class is "database" with a score below 60

Select t1.sname,isnull (t2.grade, 0) from T_student T1, T_score t2,t_course T3

where T2.sid = T1.sid and t2.cid = T3.cid

and t3.cname= ' database ' and T2.grade <60;

35. Check the course selection of all students;

SELECT T1.sid, t1.cid,t2.sname,t3.cname from T_score t1,t_student t2,t_course T3

where T1.sid = T2.sid and t1.cid = T3.cid;

36. Inquire about the name, course name and score of any course score above 70 points;

SELECT distinct t1.sid,t1.sname,t2.cid, t2.grade from t_student T1, T_score T2

WHERE T2.grade >=70 and t2.sid = T1.sid;

37, the examination of the course of failure, and according to the course number from large to small arrangement

Select CID from T_score where grade < the order by CID;

38. The number and name of the student whose course number is 003 and the course score is more than 80;

Select T1.sid,t2.sname from T_score t1,t_student T2

where T1.sid = T2.sid and T1.grade > and t1.cid = ' 003 ';

39. Number of students selected for the course

Select COUNT (*) from T_score;

40. The name of the student with the highest achievement and the results of the students who have enrolled in the course of the "cotyledons" teacher

Select T1.sname,t2.grade from T_student T1, T_score t2,t_course t3,t_teacher T4

where T1.sid = T2.sid

and t2.cid = T3.cid

and T3.tid = T4.tid

and T4. Tname = ' cotyledons '

and T2.grade = (select Max (grade) from T_score where cid=t3.cid);

41. Check each course and the corresponding number of elective

Select COUNT (*) from T_score GROUP by CID

42. Check the student's number, course number and student achievement of the same students with different course grades.

Select DISTINCT T1.sid,t2.grade from T_score T1, T_score T2

where T1.grade = T2.grade and T1.cid <>t2.cid;

43. Check the top two of the best results for each door

SELECT T1.sid as Student id,t1.cid as course Id,grade as score from T_score T1

WHERE Grade in (

SELECT TOP 2 grade from T_score

WHERE t1.cid = cid ORDER by grade DESC

)

ORDER by T1.cid;

44. The number of students enrolled in each course (more than 10 participants are counted). Require the output of the course number and the number of elective, query results in descending order of numbers, query results in descending order of numbers, if the number is the same, in ascending order by course number

Select CID as course number, COUNT (*) as number from T_score

GROUP BY CID

Order BY Count (*) desc,cid

45. Retrieve the student number of at least two elective courses

Select SID from T_score GROUP by SID has Count (*) > = 2

46. Check the course number and course name of all the students who have enrolled in the course

Select Cid,cname from T_course where CID on (select CID from T_score Group by CID)

47, inquiry did not learn the "cotyledons" teacher taught the name of any course students

Select Sname from T_student

Where Sid not in (

Select Sid from T_course t1,t_teacher T2, T_score T3

where T1.tid = T2.tid

and T3.cid =t 1.cid

and t2.tname = ' cotyledons '

);

48. Check the number of students with two or more failed courses and their average scores

Select Sid,avg (IsNull (grade, 0)) from T_score

where Sid in (

Select Sid from T_score

Where grade <60 GROUP by SID has Count (*) >2

)

Group BY SID;

49, search "004" course score is less than 60, in descending order by the number of students

Select Sid from T_score where cid= ' 004 ' and grade <60 the order by grade Desc;

50, delete the "002" Students of the "001" The results of the course

Delete from T_score where sid= ' 001 ' and cid = ' 001 ';

The article originates from Yixian network http://www.ynpxrz.com/n822738c2024.aspx

SQL Stats Student score 2