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

Table architecture

Student student table Course course schedule SC score score Teacher teacher table

Construction table sentence

CREATE TABLE student (s # INT, sname nvarchar (32), sage INT, ssex nvarchar (8)) CREATE TABLE course (c # INT, cname nvarchar (32), t # INT) CREATE TABLE sc (s # INT, c # INT, score INT) CREATE TABLE teacher (t # INT, tname nvarchar (16))

test data

Insert into Student select 1 union all select N 'Liu Yi', 18 charge N 'Man' union all select 2 charge N 'money er', 19 charge N 'female' union all select 3 Magi N 'Zhang San', 17 charge N 'male' union all select 4 charge N'Li Si', 18 charge N 'female' union all select 5 charge N 'Wang Wu', 17 charge N 'male' union all select 6 charge N 'Zhao Liu', 19 charge N 'female' insert into Teacher select 1 charge N'Ye Ping 'union all select 2, N' Hegao' union all select 3 N 'Yang Yan' union all select 4pm N 'Zhou Lei' insert into Course select 1P N 'Chinese', 1 union all select 2pm N 'Math', 2 union all select 3pm N' English', 3 union all select 4pm N' Physics', 4 insert into SC select 1MIT 56 union all select 1MIT 78 union all select 1MIT 3MIT 67 union all select 1MIT 4MIT 58 union all select 2MIT 1MIT 79 union all select 2MERN 2MERN 2MH 2MH 2MH 81MH 2MH 2MH 2MH, Math, etc. 88 union all select 3,4,56 union all select 4,2,88 union all select 4,3,90 union all select 4,4,93 union all select 5,1,46 union all select 5,3,78 union all select 5,4,53 union all select 6,1,35 union all select 6,2,68 union all select 6,4,71

problem

1. Look up the student numbers of all the students whose grades in the "001" course are higher than those in the "002" course; select a.S# from (select sprints score from SC where Category scores 001') a, (select slots score from SC where Clippers scores 002') b where a.score > b.score and a. Students b.slots; 2. Look up the student numbers and average scores of the students whose average scores are greater than 60. Select handwriting avg (score) from sc group by S# having avg (score) > 60; 3. Query all students' student numbers, names, number of courses, total grades; select Student.S#,Student.Sname,count (SC.C#), sum (score) from Student left Outer join SC on Student.S#=SC.S# group by Student.S#,Sname 4, query the number of teachers surnamed "Li" Select count (distinct (Tname)) from Teacher where Tname like'Li%'; 5. Query the student numbers and names of the students who have not taken the "Ye Ping" teacher class. Select Student.S#,Student.Sname from Student where S# not in (select distinct (SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=' Ye Ping'); 6. Query the student numbers and names of the students who have studied "001" and have also studied the course number "002". Select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists (Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002'); 7. Query the student numbers and names of all the students who have taken all the lessons taught by teacher Ye Ping. Select Sendry Sname from Student where S# in (select S# from SC, Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=' Ye Ping 'group by S# having count (SC.C#) = (select count (C #) from Course,Teacher where Teacher.T#=Course.T# and Tname=' Ye Ping')). 8. Query the student numbers and names of all students whose scores of course number "002" are lower than that of course number "001". Select where score2 2 where score2 60) 10. Inquire the student numbers and names of the students who have not taken all the courses. Select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# group by Student.S#,Student.Sname having count (C #) = 60 THEN 1 ELSE 0 END) / COUNT (*) AS pass percentage FROM SC T title course where t.C#=course.C# GROUP BY t.C# ORDER BY 100 * SUM (CASE WHEN isnull (score) 0) > = 60 THEN 1 ELSE 0 END) / COUNT (*) DESC 20, query the percentage of the average score and passing rate of the following courses (shown in "1 line"): enterprise Management (001) Marx (002) OO&UML (003) Database (004) SELECT SUM (CASE WHEN C # = '001' THEN score ELSE 0 END) / SUM (CASE C # WHEN' 001' THEN 1 ELSE 0 END) average AS enterprise management score, 100 * SUM (CASE WHEN C # = '001' AND score > = 60 THEN 1 ELSE 0 END) / SUM (CASE WHEN C # =' 001' THEN 1 ELSE 0 END) AS enterprise management pass percentage SUM (CASE WHEN C # = '002' THEN score ELSE 0 END) / SUM (CASE C # WHEN '002' THEN 1 ELSE 0 END) AS Marx average score, 100 * SUM (CASE WHEN C # = '002' AND score > = 60 THEN 1 ELSE 0 END) / SUM (CASE WHEN C # = '002' THEN 1 ELSE 0 END) AS Marx pass percentage SUM (CASE WHEN C # = '003' THEN score ELSE 0 END) / SUM (CASE C # WHEN '003' THEN 1 ELSE 0 END) AS UML average score, 100 * SUM (CASE WHEN C # = '003' AND score > = 60 THEN 1 ELSE 0 END) / SUM (CASE WHEN C # = '003' THEN 1 ELSE 0 END) AS UML pass percentage Average score of SUM (CASE WHEN C # = '004' THEN score ELSE 0 END) / SUM (CASE C # WHEN '004' THEN 1 ELSE 0 END) AS database, 100 * SUM (CASE WHEN C # = '004' AND score > = 60 THEN 1 ELSE 0 END) / SUM (CASE WHEN C # = '004' THEN 1 ELSE 0 END) AS database pass percentage FROM SC 21, query the average score of different courses taught by different teachers from high to low

SELECT max (Z.T#) AS teacher ID,MAX (Z.Tname) AS teacher name, C.C# AS course ID,MAX (C.Cname) AS course name, AVG (Score) AS average score

FROM SC AS T,Course AS C, Teacher AS Z

Where T.C#=C.C# and C.T#=Z.T#

GROUP BY C.C#

ORDER BY AVG (Score) DESC 22, query the transcripts of the 3rd to 6th students in the following courses: business Management (001), Marx (002), UML (003), Database (004)

[student ID], [student name], Business Management, Marx, UML, Database, GPA

SELECT DISTINCT top 3

SC.S# As student number

Student.Sname 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,SC LEFT JOIN SC AS T1

ON SC.S# = T1.S# AND T1.C# = '001'

LEFT JOIN SC AS T2

ON SC.S# = T2.S# AND T2.C# = '002'

LEFT JOIN SC AS T3

ON SC.S# = T3.S# AND T3.C# = '003'

LEFT JOIN SC AS T4

ON SC.S# = T4.S# AND T4.C# = '004'

WHERE student.S#=SC.S# and

ISNULL (T1. Score.0) + ISNULL (T2. Score.0) + ISNULL (T3. Score.0) + ISNULL (T4. Score.0)

NOT IN

(SELECT

DISTINCT

TOP 15 WITH TIES

ISNULL (T1. Score.0) + ISNULL (T2. Score.0) + ISNULL (T3. Score.0) + ISNULL (T4. Score.0)

FROM sc

LEFT JOIN sc AS T1

ON sc.S# = T1.S# AND T1.C# = 'K1'

LEFT JOIN sc AS T2

ON sc.S# = T2.S# AND T2.C# = 'k2'

LEFT JOIN sc AS T3

ON sc.S# = T3.S# AND T3.C# = 'k3'

LEFT JOIN sc AS T4

ON sc.S# = T4.S# AND T4.C# = 'k4'

ORDER BY ISNULL (T1.score.0) + ISNULL (T2.score.0) + ISNULL (T3.score.0) + ISNULL (T4.score.0) DESC).

23. Statistics and print the scores of each subject, the number of people in each score segment: course ID, course name, [100-85], [85-70], [70-60], [T2. Average grade point average) as ranking

S# as student number, grade point average

The average score of FROM (SELECT score)

FROM SC

GROUP BY S#

) AS T2

ORDER BY grade point average desc

25. Inquire about the records of the top three grades in each subject: (regardless of the juxtaposition of scores)

SELECT t1.S# as students' ID,Score as scores for ID,t1.C# as courses

FROM SC t1

WHERE score IN (SELECT TOP 3 score

FROM SC

WHERE t1.Cure = C #

ORDER BY score DESC

)

ORDER BY t1.C#

26. Inquire about the number of students who are enrolled in each course

Select cantilever account (S#) from sc group by C#

27. Find out the student numbers and names of all the students who have taken only one course

Number of courses selected by select SC.S#,Student.Sname,count (C #) AS

From SC, Student

Where SC.S#=Student.S# group by SC.S#, Student.Sname having count (C #) = 1

28. Inquire about the number of boys and girls

Select count (Ssex) as number of boys from Student group by Ssex having Ssex=' male'

Select count (Ssex) as number of girls from Student group by Ssex having Ssex=' girls'

29. Inquire the list of students surnamed "Zhang"

SELECT Sname FROM Student WHERE Sname like 'Zhang%'

30. Query the list of same-sex students with the same name and count the number of students with the same name

Select Sname,count (*) from Student group by Sname having count (*) > 1

31. List of students born in 1981 (Note: the type of Sage column in Student table is datetime)

Select Sname, CONVERT (char (11), DATEPART (year,Sage)) as age

From student

Where CONVERT (char (11), DATEPART (year,Sage)) = '1981'

32. Query the average grade point of each course, and the results are arranged in ascending order. When the average score is the same, it is arranged in descending order of course number.

Select Che Magi Avg (score) from SC group by C # order by Avg (score), C # DESC

33. Inquire about the student number, name and grade point average of all students whose GPA is greater than 85

Select Sname,SC.S#, avg (score)

From Student,SC

Where Student.S#=SC.S# group by SC.S#,Sname having avg (score) > 85

34. Query the names and scores of students whose course name is "Database" and whose score is less than 60

Select Sname,isnull (score,0)

From Student,SC,Course

Where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname=' database 'and score = 70 AND SC.S#=student.S#

37. Query the failed courses and arrange them by course number from big to small.

Select c # from sc where scor e 80 and Clippers

39. The number of students who have taken the course

Select count (*) from sc

40. Inquire about the names and grades of the students with the highest scores among the students taking the courses taught by teacher Ye Ping.

Select Student.Sname,score

From Student,SC,Course C,Teacher

Where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname=' Ye Ping 'and SC.score= (select max (score) from SC where C#=C.C#)

41. Inquire about each course and the corresponding number of electives

Select count (*) from sc group by C#

42. Inquire about the student numbers, course numbers and grades of students with the same scores in different courses

Select distinct A.S#,B.score from SC A, SC B where A.Score=B.Score and A.C# B.C#

43. inquire about the top two with the best scores in each course.

SELECT t1.S# as students' ID,Score as scores for ID,t1.C# as courses

FROM SC t1

WHERE score IN (SELECT TOP 2 score

FROM SC

WHERE t1.Cure = C #

ORDER BY score DESC

)

ORDER BY t1.C#

44. count the number of students taking each course (for courses with more than 10 students). The course number and the number of electives are required to be output. The query results are arranged in descending order. If the number is the same, the course number is arranged in ascending order.

Select C# as course number, number of count (*) as

From sc

Group by C#

Order by count (*) desc,c#

45. Retrieve the student numbers of at least two courses

Select S#

From sc

Group by s#

Having count (*) > = 246. Query the course number and name of the course that all students take.

Select C#,Cname

From Course

Where C # in (select c # from sc group by c #)

47. Inquire the names of students who have not taken any of the courses taught by teacher Ye Ping

Select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname=' Ye Ping')

48. Inquire about the student numbers and average scores of students who fail two or more courses.

Select sparring avg (isnull (score,0)) from SC where S# in (select S# from SC where score 2) group by S#

49. Search the student numbers of "004" whose scores are less than 60 and arranged in descending order.

Select S# from SC where C#='004'and score

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.