50 Common SQL statements Popular examples of student elective schedules on the internet _mssql

50 Common SQL statements

Student (s#,sname,sage,ssex) Student table
Course (c#,cname,t#) timetable
SC (s#,c#,score) score sheet
Teacher (t#,tname) Teacher's Table

Problem:
1, Query the "001" Course than "002" course scores of all students of the school number;
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, the average score is greater than 60 students of the school number and average score;
Select S#,avg (Score)
From SC
GROUP BY s# have AVG (score) >60;
3, inquires all the student's study number, the name, the choice course number, the 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, inquires the surname "Li" the number of teachers;
Select COUNT (Distinct (tname))
From Teacher
Where tname like ' li% ';
5, the query did not learn the "cotyledons" teacher class students of the school number, name;
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= ' cotyledons ');
6, the query learned "001" and also learned the number "002" course of the student's school number, name;
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 query learned "cotyledons" teacher taught all the students of the class number, name;
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= ' cotyledons ' GROUP by s# has the having count (SC. C #) = (select count (C #) from Course,teacher where teacher.t#=course.t# and Tname= ' cotyledons '));
8, inquires the course number "002" The result is more than the course number "001" Curriculum low all schoolmate's student number, the name;
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, query all the course score is less than 60 students of the school number, name;
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 query did not learn the whole class of students of the student number, name;
Select Student.s#,student.sname
From STUDENT,SC
where STUDENT.S#=SC. s# GROUP BY Student.s#,student.sname has count (C #) < (select count (C #) from Course);
11, inquires at least one course and the study number is "1001" schoolmate learns the same schoolmate the student number and the name;
Select S#,sname from STUDENT,SC where STUDENT.S#=SC. s# and C # in select C # from SC where s#= ' 1001 ';
12, inquiry at least learned the number of "001" Students of all the other students learn number and name of a class;
SELECT DISTINCT SC. S#,sname
From STUDENT,SC
where STUDENT.S#=SC. s# and C # in (select C # from SC where s#= ' 001 ');
13, the "SC" table "cotyledons" teachers to teach the results of the course changes to the average grade;
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= ' cotyledons ');
14, the inquiry and "1002" number of students to learn the same course of the other students to learn number and name;
Select s# from SC where C # in (select C # from SC where s#= ' 1002 ')
GROUP BY s# has count (*) = (select count (*) from SC where s#= ' 1002 ');
15, delete learning "cotyledons" Teacher class SC table records;
Delect SC
From course, Teacher
where COURSE.C#=SC. C # and course.t#= teacher.t# and tname= ' cotyledons ';
16, to the SC table to insert some records, these records required to meet the following conditions: No number of "003" course of the classmate number, 2,
The average score of the class;
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, according to the average score from high to low show all students of the "database", "Enterprise Management", "English" three-course results, as shown in the following form: Student ID, database, business 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
, COUNT (*) as effective course number, AVG (T.score) as average score
From SC as T
GROUP by s#
ORDER by AVG (T.score)
18, inquires the highest and lowest points of the subjects: in the following form: Course ID, highest score, lowest score
SELECT l.c# as course Id,l.score as the highest score, R.score as the 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. From low to high and the percentage of the passing rate from highest to lowest according to the average grade of each section
SELECT t.c# as course number, max (course. Cname) As course name, ISNULL (AVG (Score), 0) as average score
, SUM (case when IsNull (score,0) >=60 THEN 1 ELSE 0)/count (*) as pass percentage
From SC T,course
where T.c#=course. C#
GROUP by t.c#
Order from * SUM (case when IsNull (score,0) >=60 THEN 1 ELSE 0-end)/count (*) DESC
20. Find out the percentage of average and passing rates for the following courses (shown in "1 lines"): Enterprise Management (001), Marx (002), OO&AMP;UML (003), Database (004)
SELECT SUM (case when C # = ' 001 ' THEN score ELSE 0)/sum (case C # when ' 001 ' THEN 1 ELSE 0-end) as Enterprise management average
, SUM * (case when C # = ' 001 ' and score >= THEN 1 Else 0 end)/sum (case when C # = ' 001 ' THEN 1 ELSE 0 end) as Enterprise management and Percentage of lattice
, SUM (case when C # = ' 002 ' THEN score ELSE 0 end)/sum (Case C # when ' 002 ' THEN 1 ELSE 0-end) as Marx average
, SUM * (case when C # = ' 002 ' and score >= THEN 1 Else 0 end)/sum (case when C # = ' 002 ' THEN 1 ELSE 0 end) as Marx passed Percentage
, SUM (case when C # = ' 003 ' THEN score ELSE 0)/sum (case C # when ' 003 ' THEN 1 ELSE 0-end) as UML average
, SUM * (case when C # = ' 003 ' and score >= 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 ' the 1 ELSE 0 end) as database average
, * SUM (case when C # = ' 004 ' and score >= THEN 1 Else 0 end)/sum (case when C # = ' 004 ' THEN 1 ELSE 0 end) as database passes Percentage
From SC
21, inquires the different teacher to teach the different course average score from the high to the low display
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, inquires the following course score 3rd to 6th Student Transcript: Enterprise 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 study 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 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, Statistical printing of the results of each section, the number of sections: 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 THEN 1 ELSE 0) as [100-85]
, SUM (case when score BETWEEN THEN 1 ELSE 0) as [85-70]
, SUM (case when score BETWEEN THEN 1 ELSE 0) as [70-60]
, SUM (case when score < THEN 1 ELSE 0 end) As [60-]
From Sc,course
where SC. c#=course.c#
GROUP by SC. C#,cname;

24. Check students ' average score and rank
Select 1+ (select COUNT (distinct average score)
From (SELECT S#,avg (score) as average score
From SC
GROUP by s#
) as T1
WHERE Average score > T2. Average score) as rank,
s# as student study number, average score
From (SELECT S#,avg (score) Average score
From SC
GROUP by s#
) as T2
Order by average score desc;

25. Check the records of the top three of the scores of the subjects: (regardless of the performance and situation)
SELECT t1. s# as student id,t1. C # as course Id,score as fractions
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. The number of students enrolled in each course
Select C#,count (s#) from SC Group by C #;
27. Query the student number and name of all the students who took only one course
Select SC. S#,student.sname,count (C #) as selected number of courses
From SC, Student
where SC. s#=student.s# GROUP by SC. s#, Student.sname having Count (C #) = 1;
28, inquires the number of boys and girls
Select count (ssex) as the number of boys from the Student group by the ssex having ssex= ' male ';
Select count (ssex) as the number of girls from the Student group by the Ssex having ssex= ' female ';
29, inquires the surname "Zhang" the student list
SELECT sname from Student WHERE sname like ' Zhang% ';
30, query the same-sex students list, and statistics of the same name number
Select Sname,count (*) from Student GROUP by Sname has count (*) >1;;
31. List of students born in 1981 (note: The type of the sage column in the student table is datetime)
Select Sname, CONVERT (char (one), DATEPART (Year,sage)) as age
From student
where CONVERT (char (one), DATEPART (year,sage)) = ' 1981 ';
32, check the average score of each course, the results are ranked in ascending order of average score, the average score is the same, in descending order by course number
Select C#,avg (score) from SC GROUP by C # Order by AVG (score), C # DESC;
33. Number, name and average score of all students whose average score 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, the inquiry course name is "the database", and the score below 60 student name and the score
Select Sname,isnull (score,0)
From Student,sc,course
where SC. s#=student.s# and SC. c#=course.c# and course.cname= ' databases ' and score <60;
35. Check all students ' elective course;
SELECT SC. S#,sc. C#,sname,cname
From Sc,student,course
where SC. s#=student.s# and SC. c#=course.c#;
36. Inquire the name, course title and score of any course score above 70 points;
SELECT distinct student. S#,student. Sname,sc. C#,sc.score
From STUDENT,SC
WHERE sc.score>=70 and SC. S#=student. s#;
37, check the course, and according to the course number from big to small arrangement
Select C # from SC where Scor e <60 order by C #;
38. The number and name of the student whose course number is 003 and the course score is above 80;
Select SC. S#,student.sname from Sc,student where SC. s#=student.s# and Score>80 and c#= ' 003 ';
39. Number of students who have chosen the course
Select COUNT (*) from SC;
40, the students who choose "cotyledons" Teachers to teach the course of the students, the highest performance of the student names and their results
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= ' cotyledons ' and sc.score= (select Max (score) from SC where c#=c.c#);
41, inquires the various courses and the corresponding elective number
Select COUNT (*) from SC Group by C #;
42. Check the students ' number, course number and grade of students with the same grades 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, query the best results of each of the top two
SELECT t1. s# as student id,t1. C # as course Id,score as fractions
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. The number of students enrolled in each course (more than 10 courses are counted). Request the output of course number and number of students, the results of the query in descending order, the results are in descending order, if the number is the same, in ascending order by course number
Select C # as course number, COUNT (*) as number
From SC
GROUP BY C #
Order BY Count (*) desc,c#
45, to retrieve at least two courses of students study number
Select s#
From SC
GROUP BY s#
Having count (*) > = 2
46. Check the course number and the course name of all the students ' elective courses
Select C#,cname
From Course
where C # in (select C # from SC Group by C #)
47, the query did not learn the "cotyledons" teacher taught by any one of the subjects of the name of the students
Select Sname from Student where s# is not in (select s# from COURSE,TEACHER,SC where course.t#=teacher.t# and SC. C#=course. C # and Tname= ' cotyledons ');
48. Check the students ' school numbers and their average grades of two or more failing courses
Select S#,avg (IsNull (score,0)) from SC where s# in (select s# to SC where score <60 GROUP by s# have Count (*) >2) Group BY s#;
49, the search "004" course score is less than 60, sorted by the score descending order of the student school number
Select s# from SC where c#= ' 004 ' and score <60 ORDER by score Desc;
50, delete the "002" Students "001" Course results
Delete from Sc where s#= ' 001 ' and c#= ' 001 ';

The author does not often