Shulou(Shulou.com)05/31 Report--

This article mainly introduces "commonly used sql sentence summary". In daily operation, I believe many people have doubts about the commonly used sql sentence summary problem. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "commonly used sql sentence summary"! Next, please follow the editor to study!

Student student table (Scandinavian SageSsex)

The course schedule of Course (CnameMagol T#)

SC (score score)

Teacher (teacher list)

Question:

1. Query the student numbers of all students whose scores in "001" course are higher than those in "002" course.

Select a.S# from (select sprints score from SC where Clippers score 001') a, (select sails score)

From SC where clients 002') b

Where a.score > b.score and a.s#=b.s#

2. Inquire about the student number and average score of the students whose average score is more than 60.

Select sparring avg (score)

From sc

Group by S# having avg (score) > 60

3. Check the student number, name, number of courses and total score of all the students.

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. Inquire the number of teachers surnamed "Li"

Select count (distinct (Tname))

From Teacher

Where Tname like'Li%'

5. Inquire about the student numbers and names of the students who have not studied the teacher Ye Ping.

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. Inquire the student numbers and names of the students who have studied "001" and 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. Inquire about the student numbers and names of the students who have studied all the lessons taught by teacher Ye Ping

Select S#,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 those of course number "001".

Select sparkling MagnameSname from (select Student.S#,Student.Sname,score, (select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2

From Student,SC where Student.S#=SC.S# and where score2 001') Song2)

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 passing percentage

FROM SC T,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 average grade and pass rate of the following courses (shown in "1 line"): business 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) AS average 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 passing percentage of enterprise management

, 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

, SUM (CASE WHEN C # = '004' THEN score ELSE 0 END) / SUM (CASE C # WHEN '004' THEN 1 ELSE 0 END) AS database average score

, 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. Check 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, count (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 "" 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 (*) > = 2

46. Inquire about the course number and name of the courses 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 b.score and a.s#=b.s#

2. Inquire about the student number and average score of the students whose average score is more than 60.

Select sparring avg (score)

From sc

Group by S# having avg (score) > 60

3. Check the student number, name, number of courses and total score of all the students.

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. Inquire the number of teachers surnamed "Li"

Select count (distinct (Tname))

From Teacher

Where Tname like'Li%'

5. Inquire about the student numbers and names of the students who have not studied the teacher Ye Ping.

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. Inquire the student numbers and names of the students who have studied "001" and 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. Inquire about the student numbers and names of the students who have studied all the lessons taught by teacher Ye Ping

Select S#,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 those of course number "001".

Select sparkling MagnameSname from (select Student.S#,Student.Sname,score, (select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2

From Student,SC where Student.S#=SC.S# and where score2 001') Song2)

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 passing percentage

FROM SC T,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 average grade and pass rate of the following courses (shown in "1 line"): business 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) AS average 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 passing percentage of enterprise management

, 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

, SUM (CASE WHEN C # = '004' THEN score ELSE 0 END) / SUM (CASE C # WHEN '004' THEN 1 ELSE 0 END) AS database average score

, 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. Check 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 (*) > = 2

46. Inquire about the course number and name of the courses 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.