/*

50 Sql statements involved in a project

Problems and descriptions:

–1.Student list

Student(S#,Sname,Sage,Ssex) –S# Student ID, Sname student name, Sage date of birth, Ssex student gender

–2.Curriculum table

Course(C#,Cname,T#) –C# –Course number, Cname course name, T# teacher number

–3.Teacher’s table

Teacher(T#,Tname) –T# Teacher number, Tname teacher’s name

–4.Achievement table

SC(S#,C#,score) –S# Student number, C# course number, score score

select * from Student

select * from Course

select * from Teacher

select * from SC

*/

–Create test data

create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))

insert into Student values(’01’ , N’Zhao Lei ‘,’1990-01-01′, N’men’)

insert into Student values(’02’ , N’Money ‘,’1990-12-21′, N’men’)

insert into Student values(’03’ , N’Sun Feng ‘,’1990-05-20′, N’men’)

insert into Student values(’04’ , N’Li Yun ‘,’1990-08-06′, N’men’)

insert into Student values(’05’ , N’Zhou Mei ‘,’1991-12-01′, N’girls’)

insert into Student values(’06’ , N’Wu Lan ‘,’1992-03-01′, N’girls’)

insert into Student values(’07’ , N’Zheng Zhu ‘,’1989-07-01′, N’female’)

insert into Student values(’08’ , N’Wang Ju ‘,’1990-01-20′, N’girl’)

create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))

insert into Course values(’01’ , N’Language ‘,’02’ “)

insert into Course values(’02’ , N’Mathematics’,’01’)

insert into Course values(’03’ , N’English ‘,’03’ “)

create table Teacher(T# varchar(10),Tname nvarchar(10))

insert into Teacher values(’01’ , N’Zhang three ‘)

insert into Teacher values(’02’ , N’Li four ‘)

insert into Teacher values(’03’ , N’Wang five ‘)

create table SC(S# varchar(10),C# varchar(10),score decimal(18,1))

insert into SC values(’01’ , ’01’ , 80)

insert into SC values(’01’ , ’02’ , 90)

insert into SC values(’01’ , ’03’ , 99)

insert into SC values(’02’ , ’01’ , 70)

insert into SC values(’02’ , ’02’ , 60)

insert into SC values(’02’ , ’03’ , 80)

insert into SC values(’03’ , ’01’ , 80)

insert into SC values(’03’ , ’02’ , 80)

insert into SC values(’03’ , ’03’ , 80)

insert into SC values(’04’ , ’01’ , 50)

insert into SC values(’04’ , ’02’ , 30)

insert into SC values(’04’ , ’03’ , 20)

insert into SC values(’05’ , ’01’ , 76)

insert into SC values(’05’ , ’02’ , 87)

insert into SC values(’06’ , ’01’ , 31)

insert into SC values(’06’ , ’03’ , 34)

insert into SC values(’07’ , ’02’ , 89)

insert into SC values(’07’ , ’03’ , 98)

go

–1、Information and course scores for students with higher scores in the “01” course than those in the “02” course

–1.1、Query the situation of the “01” and “02” courses at the same time

–1.2、Query the situation of the “01” and “02” courses at the same time, and the existence of the “01” course, but there may not be a “02” course (which is not shown as null). (the following is no longer explained in the same content).

–2、Information and course scores for students with low scores in the “01” course than those in the “02” course

–2.1、Query the situation of the “01” and “02” courses at the same time

–2.2、There are both “01” courses and “02” courses, and there is no “01” course, but there is a “02” course.

–3、Student numbers and student names and average scores for students whose average score is greater than or equal to 60 points is inquired.

–4、Student numbers and student names and average scores for students with an average score of less than 60 points are inquired.

–4.1、Query the SQL statement of student information in SC table.

–4.2、Query the SQL statement of student information that does not exist in the SC table.

–5、Check all students’ student numbers, student names, total number of courses selected, and the total score of all courses.

–5.1、Query all the SQL that have a result.

–5.2、Query all SQL (including achievement and performance).

–6、Inquire about the number of teacher “Li”

–7、Inquired the information of students who were taught by Zhang three.

–8、I didn’t know the information of the students who were taught by Zhang three.

–9、Enquiries have been conducted on students who have numbered “01” and have also learned the courses numbered as “02”.

–10、Enquiry has been numbered as “01” but not learned by students who numbered “02”.

–11、Inquiries about the students who have not learned all the courses

–12、There is at least one course with information from the same students who have learned the same number as “01”.

–13、The information of the other students is the same as that of the “01” class.

–14、I haven’t studied the names of students in any course taught by Zhang three.

–15、For students with two or more failing grades, their student numbers, names and their average scores are given.

–16、Retrieval of “01” course score less than 60, according to the descending order of student information

–17、According to the average scores, from high to low, the results and average scores of all students are shown.

–17.1 SQL 2000 Static state

–17.2 SQL 2000 Dynamics

–17.3 For reference to SQL 2005’s static and dynamic writing, please refer to my article “common rank transformation (version 2)” or “common rank transformation (version 3)”.

–18、The highest score, the lowest score and the average score of the subjects are shown in the following forms: the course ID, the course name, the highest score, the lowest score, the average score, the passing rate, the medium rate, the good rate, the excellent rate.

–The pass was > =60, medium: 70-80, excellent as 80-90: excellence: > =90

–19、Ranking according to the scores of each section and showing the ranking

–19.1 sql 2000Complete with subqueries

–ScoreReserve vacancies in repetition

–ScoreMerge time

–19.2 sql 2005Complete with rank, DENSE_RANK

–ScoreRetention time vacancy (rank completion)

–ScoreRepeat merge time (DENSE_RANK completion)

–20、Inquire the student’s total score and rank

–20.1 Inquire about the students’ total score

–20.2 Check the student’s total score and rank, SQL 2000 is completed by sub query, and there are two kinds of vacancies when the total score is repeated.

–20.3 Inquires the student’s total result and carries on the rank, the SQL 2005 completes with rank, DENSE_RANK, divides the total score to duplicate the time to reserve the place vacancy and does not retain the place vacancy two kinds.

–21、The average scores of different courses taught by different teachers are from high to low.

–22、Information on all courses and information from second to third students.

–22.1 sql 2000Complete with subqueries

–ScoreReserve vacancies in repetition

–ScoreMerge time

–22.2 sql 2005Complete with rank, DENSE_RANK

–ScoreRetention time vacancy (rank completion)

–ScoreRepeat merge time (DENSE_RANK completion)

–23、Statistics on the scores of each score in each section: course number, course name, [100-85], [85-70], [70-60], [0-60] and percentage.

–23.1 Statistics on the scores of each score in each department: course number, course title, [100-85], [85-70], [70-60], [0-60]

–23.2 Statistics on the scores of each score in each section: course number, course name, [100-85], [85-70], [70-60], [<, 60] and percentage.

–24、Inquiring the students’ average achievement and its ranking

–24.1 Check the students’ average scores and rank them. SQL 2000 is completed by sub query. There are two kinds of vacancies in the average score repeat, the vacancy reservation and no reservation.

–24.2 The average scores of students were inquired and ranked, SQL 2005 was completed with rank, DENSE_RANK, and two kinds of vacancies were retained and no vacancies were retained when the average scores were repeated.

–25、Record the top three records of the subjects in each section

–25.1 Reserve vacancies when the score is repeated

–25.2 When the score is repeated, it does not reserve the place vacancy.

–sql 2000Subquery implementation

–sql 2005Implement with DENSE_RANK

–26、Inquiries about the number of students selected for each course

–27、Check out the student numbers and names of all the students who have only two courses.

–28、Inquiries about the number of boys and girls

–29、Query the student information with the word “wind” in the name

–30、Check the list of homosexual students of the same name, and count the number of persons of the same name.

–31、Check the list of students born in 1990 (Note: the type of Sage column in table Student is datetime).

–32、For each course, the average scores were ranked in the descending order of average scores.

–33、The student numbers, names and average scores of all students whose average score is greater than or equal to 85 is querying.

–34、The names and scores of students whose grades are less than 60 are referred to as “Mathematics”.

–35、Inquire about the course and score of all students;

–36、Check the names, course names and scores of any course with a score of 70 or more.

–37、An unqualified course

–38、The student number and name of the student whose course number is 01 and whose course score is above 80 points;

–39、Seeking the number of students in each course

–40、Information and achievements of the students with the highest scores in the course selected by Zhang three teacher

–40.1 When the highest score is only one

–40.2 When the highest score appears more than one

–41、Students’ number, course number and student achievement are also inquired.

–42、Inquire about the top two best achievements

–43、Statistics on the number of electives per course (more than 5 people’s courses are counted). It is required to export the course number and the number of electives. The results will be arranged in descending order according to the number of people. If the number is the same, arrange them in ascending order according to the number of courses.

–44、Search for students at least two courses

–45、Inquiries about students who have selected all the courses

–46、Inquire about the age of each student

–46.1 Only according to the year

–46.2 According to the date of birth, the current month is < the month of birth is less than one month.

–47、Inquire about the birthday of the student this week

–48、Inquire for the student’s birthday next week

–49、Inquiries about the birthday of the student this month

–50、Inquiries for students who spend their birthday next month

Student(Sid,Sname,Sage,Ssex) Student list

select count(*) from sc;

select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# <>B.C# ;

FROM SC t1

WHERE score IN (SELECT TOP 2 score

FROM SC

WHERE t1.C#= C#

ORDER BY score DESC

)

ORDER BY t1.C#;

select S#

from sc

group by s#

having count(*) > = 2

46、The course number and course name of all courses selected by all students are inquired.

select C#,Cname

from Course

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

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=’Leaf flat;

48、Student numbers and average scores of two or more failed courses are inquired.

select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;

49、Search for “004” course score less than 60, according to the descending order of students.

select S# from SC where C#=’004’and score <60 order by score desc;

50、Delete the achievement of “002” students’ “001” course

delete from Sc where S#=’001’and C#=’001′;

Student(S#,Sname,Sage,Ssex) Students

Course(C#,Cname,T#) Curriculum table

SC(S#,C#,score) Achievement table

Teacher(T#,Tname) Teacher’s Watch

Question:

1、To inquire the number of all students in the “001” course that is higher than the “002” course;

select a.S# from (select s#,score from SC where C#=’001′) a,(select s#,score

from SC where C#=’002′) b

where a.score>b.score and a.s#=b.s#;

2、For students with an average score of more than 60 points, the student number and average score.

select S#,avg(score)

from sc

group by S# having avg(score) >60;

3、Check all students’ student numbers, names, course number and total score.

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、The number of teachers who query the name of “Li”;

select count(distinct(Tname))

from Teacher

where Tname like ‘Li% ‘;

5、The student number and name of the students who have not studied the “Ye Ping” teacher’s class have been inquired.

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=’Leaf flat;

6、The student numbers and names of students who have studied “001” and have also learned the numbered “002” course have been querying.

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、The student number and name of all the classes taught by Ye Ping teacher were inquired.

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, from”And Tname=’leaf flat “))

8、The student number “002” is querying the student number and name of all students who are lower than the course number 001.

Select S#,Sname 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 C#=’001′) S_2 where score2 <score;

9、For students who have less than 60 marks in the course, the student numbers and names.

select S#,Sname

from Student

where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

10、The student number and name of all the students in the class were not inquired.

select Student.S#,Student.Sname

from Student,SC

where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

11、There are at least one class and the student number and name of the same students who have learned the same number as “1001”.

select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#=’1001′;

12、At least you have studied the student numbers and names of all the other students in the class “001”.

select distinct SC.S#,Sname

from Student,SC

where Student.S#=SC.S# and C# in (select C# from SC where S#=’001′);

13、Change the grade of “Ye Ping” teacher’s class in the “SC” table to the average score of this course;

update SC set score=(select avg(SC_2.score)

from SC SC_2

where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname=’Leaf flat;

14、Enquiries and students’ learning numbers and numbers of “1002” are the same.

select S# from SC where C# in (select C# from SC where S#=’1002′)

group by S# having count(*)=(select count(*) from SC where S#=’1002′);

15、Delete the SC table record of learning Ye Ping teacher’s lesson.

Delect SC

from course ,Teacher

where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=’Leaf flat;

16、Insert some records into the SC table. These records meet the following requirements: students with no numbered “003” course numbers, 2, and

The average achievement of class number;

Insert SC select S#,’002′,(Select avg(score)

from SC where C#=’002′) from Student where S# not in (Select S# from SC where C#=’002′);

17、The results of the three courses of “database”, “enterprise management” and “English” from high to low, are shown in the following forms: student ID, database, enterprise management, English, effective course number, effective average score

SELECT S# as Student ID

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’004′) AS Database

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’001′) AS Enterprise management

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’006′) AS English language

,COUNT(*) AS The number of effective courses, AVG (t.score) AS average achievement

FROM SC AS t

GROUP BY S#

ORDER BY avg(t.score)

18、The highest and lowest scores in all subjects are shown in the following form: course ID, the highest score, the lowest score.

SELECT L.C# As Course ID, L.score AS highest score, R.score AS lowest score.

FROM SC L ,SC AS R

WHERE L.C# = R.C# and

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

FROM SC AS IL,Student AS IM

WHERE L.C# = IL.C# and IM.S#=IL.S#

GROUP BY IL.C#)

AND

R.Score = (SELECT MIN(IR.score)

FROM SC AS IR

WHERE R.C# = IR.C#

GROUP BY IR.C#

);

19、According to the average scores from low to high and the percentage of passing rates from high to low,

SELECT t.C# AS Course number, max (course.Cname) AS course name, isnull (AVG (score), 0) AS average score.

,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS Pass 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 percentages of the following courses on average and pass rates (with “1 lines”): 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) AS Enterprise management

,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

,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’s passing percentage

,SUM(CASE WHEN C# = ‘003’ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘003’ THEN 1 ELSE 0 END) AS UMLAveraging

,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 UMLPass percentage

,SUM(CASE WHEN C# = ‘004’ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘004’ THEN 1 ELSE 0 END) AS Database averaging

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

FROM SC

21、The average scores of different courses taught by different teachers are 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 title, AVG (AVG) 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、The following is the transcript of the course performance: third to sixth students: business management (001), Marx (002), UML (003), database (004).

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

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 print the results of each branch, the number of each segment: course ID, course name, [100-85], [85-70], [70-60], [< 60]]

SELECT SC.C# as Course ID, Cname as course name

,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 – 85]

,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 – 70]

,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 – 60]

,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]

FROM SC,Course

where SC.C#=Course.C#

GROUP BY SC.C#,Cname;

24、Inquiring the students’ average achievement and its ranking

SELECT 1+(SELECT COUNT( distinct Average achievement)

FROM (SELECT S#,AVG(score) AS Average achievement

FROM SC

GROUP BY S#

) AS T1

WHERE Average achievement > T2. average score) as rank

S# as Student number, average achievement

FROM (SELECT S#,AVG(score) Average achievement

FROM SC

GROUP BY S#

) AS T2

ORDER BY Average achievement DESC;

25、Records of the top three grades: (not considering the results parallel)

SELECT t1.S# as Student ID, t1.C# as course ID, Score as score

FROM SC t1

WHERE score IN (SELECT TOP 3 score

FROM SC

WHERE t1.C#= C#

ORDER BY score DESC

)

ORDER BY t1.C#;

26、Inquiries about the number of students selected for each course

select c#,count(S#) from sc group by C#;

27、Check out the student numbers and names of all the students who have only chosen one course.

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

from SC ,Student

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

28、Inquiries about the number of boys and girls

Select count(Ssex) as The number of boys is from Student group by Ssex having Ssex=’male;

Select count(Ssex) as The number of girls is from Student group by Ssex having Ssex=’female;

29、A list of students named “Zhang”

SELECT Sname FROM Student WHERE Sname like ‘Zhang% ‘;

30、Check the list of homosexual students of the same name, and count the number of persons of the same name.

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

31、1981The list of students born in the year (Note: the type of Sage column in table Student is datetime).

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

from student

where CONVERT(char(11),DATEPART(year,Sage))=’1981′;

32、The average scores of each course are ranked in the ascending order of average scores, and the average scores are in the same order.

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

33、For students who have an average score of more than 85, their student numbers, names and average scores are

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

from Student,SC

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

34、The names and scores of students whose grades are below 60 are inquired.

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 < 60;

35、Inquire about the course selection of all students;

SELECT SC.S#,SC.C#,Sname,Cname

FROM SC,Student,Course

where SC.S#=Student.S# and SC.C#=Course.C# ;

36、Check the names, course names and scores of any course with a score of 70 or more.

SELECT distinct student.S#,student.Sname,SC.C#,SC.score

FROM student,Sc

WHERE SC.score>=70 AND SC.S#=student.S#;

37、Check out the failed courses and arrange them according to the number of courses.

select c# from sc where scor e <60 order by C# ;

38、The student number and name of the student whose course number is 003 and whose course score is above 80 points;

select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#=’003′;

39、The number of students selected for the course

select count(*) from sc;

40、The names and achievements of the students with the highest scores in the course chosen by the “Ye Ping” teacher are inquired.

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 of the various courses and the corresponding elective numbers

select count(*) from sc group by C#;

42、For students whose grades are the same, the student number, course number and student achievement are the same.

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 best achievements

SELECT t1.S# as Student ID, t1.C# as course ID, Score as score

FROM SC t1

WHERE score IN (SELECT TOP 2 score

FROM SC

WHERE t1.C#= C#

ORDER BY score DESC

)

ORDER BY t1.C#;

44、Statistics on the number of electives per course (more than 10 people’s courses are counted). Request the output of the course number and the number of elective, query results in descending order according to the number of people, the results are arranged in descending order according to the number of people, if the number is the same, arrange the sequence according to the course number.

select C# as Course number, count (*) as

from sc

group by C#

order by count(*) desc,c#

45、Search for students at least two courses

select S#

from sc

group by s#

having count(*) > = 2

46、The course number and course name of all courses selected by all students are inquired.

select C#,Cname

from Course

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

47、I haven’t studied the names of students in any course taught by 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=’Leaf flat;

48、Student numbers and average scores of two or more failed courses are inquired.

select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;

49、Search for “004” course score less than 60, according to the descending order of students.

select S# from SC where C#=’004’and score <60 order by score desc;

50、Delete the achievement of “002” students’ “001” course

delete from Sc where S#=’001’and C#=’001′;