Common SQL Notes

Student (s#,sname,sage,ssex) Student table
Course (c#,cname,t#) timetable
SC (s#,c#,score) score table
Teacher (t#,tname) Teacher table
Problem:
1, the inquiry "001" course is higher than "002" 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 query average score is more than 60 points of the student's number and average score;
Select S#,avg (Score)
From SC
GROUP BY s# have AVG (score) >60;
3, inquiry all students of the school number, name, number of courses selected, total;
Select Student.s#,student.sname,count (SC. C #), SUM (score)
From Student to Outer join SC on STUDENT.S#=SC. s#
GROUP BY Student.s#,sname
4, inquire the surname "Li" the number of teachers;
Select COUNT (Distinct (tname))
From Teacher
Where tname like ' li% ';
5, the inquiry did not learn "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 inquiry learned "001" and also learned the number "002" course of the students ' study 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 inquiry has learned "Cotyledons" the teacher teaches all classes The student's school number, the 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# have 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 lower than the course number "001" The student's school 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, check all the course results are less than 60 points of the student's school number, name;
Select S#,sname
From Student
where s# not in (the Select student.s# from STUDENT,SC where S.S#=SC. s# and score>60);
10, the inquiry did not learn all the class student's school number, the name;
Select Student.s#,student.sname
From STUDENT,SC
where STUDENT.S#=SC. s# GROUP BY Student.s#,student.sname have Count (C #) < (select
Count (C #) from Course);
11, the inquiry has at least one course and the school number for "1001" students learn the same student number and 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 to learn the number of "001" Students all the other students of the class number and name;
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 in the "cotyledons" teacher taught the results of the course are changed to the average performance of the curriculum;
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, inquires and "1002" number of students to study the course of the same class of other students and the name of the school;
Select s# from SC where C # in (select C # from SC where s#= ' 1002 ')
GROUP BY s# have count (*) = (select count (*) from SC where s#= ' 1002 ');
15, delete learning "cotyledons" the SC table record of the teacher class;
Delect SC
From course, Teacher
where COURSE.C#=SC. C # and course.t#= teacher.t# and tname= ' cotyledons ';
16, insert some records into the SC table, these records require the following conditions: not the same as the number "003" Course
Study number, 2,
The average grade 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 display all students "database", "Enterprise Management", "English" the curriculum achievements,
Displayed as follows: 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 number of courses, AVG (T.score) as average score
From SC as T
GROUP by s#
ORDER by AVG (T.score)
18. Check the highest and lowest score of each section: show the following form: Course ID, highest score, lowest score
SELECT l.c# as id,l.score as highest score, r.score as Min.
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 grades from low to high and the percentage of passing rate from high to low order
SELECT t.c# as course number, max (course. Cname) As course name, ISNULL (AVG (Score), 0) as average score
, IsNull (score,0) >=60 then 1 ELSE 0 END)/count (*) as Pass Hundred
Scores
From SC T,course
where T.c#=course. C#
GROUP by t.c#
ORDER by $ * SUM (case when IsNull (score,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&AMP;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 average score
, * SUM (case when C # = ' 001 ' and score >= 1 ELSE 0 END)/sum (case
When C # = ' 001 ' then 1 ELSE 0 END) as Enterprise management passing percentage
, SUM (case when C # = ' 002 ' then score ELSE 0 END)/sum (Case C # when ' 002 ' Then
1 ELSE 0 END) as Marx's average score
, * SUM (case when C # = ' 002 ' and score >= 1 ELSE 0 END)/sum (case
When C # = ' 002 ' then 1 ELSE 0 END) as Marx passing 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
, * SUM (case when C # = ' 003 ' and score >= 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
, * SUM (case when C # = ' 004 ' and score >= 1 ELSE 0 END)/sum (case
When C # = ' 004 ' then 1 ELSE 0 END) as database percent passed
From SC
21, query different teachers teach different courses average from high to low display
SELECT Max (z.t#) as Teacher Id,max (Z.tname) as faculty name, c.c# as course I
D,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 following course results 3rd to 6th students Transcripts: Enterprise Management (001), Marx (002),
UML (003), Database (004)
[Student id],[student name], Enterprise management, Marx, UML, database, average score
SELECT DISTINCT Top 3
Sc. s# as student school 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 of
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, the statistics print each section result, each score segment number: 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 and 1 ELSE 0 END) as [100-85]
, SUM (case when score between and 1 ELSE 0 END) as [85-70]
, SUM (case when score between and 1 ELSE 0 END) as [70-60]
, SUM (case when score < 1 ELSE 0 END) as [60-]
From Sc,course
where SC. c#=course.c#
GROUP by SC. C#,cname;
24, check the average grade of students and their rankings
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 number, average score
From (SELECT S#,avg (score) Average score
From SC
GROUP by s#
) as T2
ORDER by average grade desc;
25. Check the records of the top three grades of each section: (regardless of the performance of the situation)
SELECT t1. s# as student id,t1. C # as course Id,score as fraction
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. Check the number of students who have been enrolled in each course
Select C#,count (s#) from SC Group by C #;
27. Find out the number and name of all students who have only one course of study
Select SC. S#,student.sname,count (C #) As course number
From SC, Student
where SC. s#=student.s# GROUP by SC. s#, Student.sname having Count (C #) = 1;
28. Query the number of boys and girls
Select count (Ssex) as the number of males from the Student group by Ssex have ssex= ' male ';
Select count (Ssex) as the number of females from the Student group by Ssex have ssex= ' female ';
29. Check the list of students with the surname "Zhang"
SELECT Sname from 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 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 student
where CONVERT (char (one), DATEPART (year,sage)) = ' 1981 ';
32, the average score of each course is queried, the results are ranked in ascending order by average, and the average grade is the same, descending by the course number.
Arranged
Select C#,avg (score) from SC GROUP by C # Order by AVG (score), C # DESC;
33. The number, name and average scores of all students who have average scores greater than 85
Select SNAME,SC. s#, AVG (Score)
From STUDENT,SC
where STUDENT.S#=SC. s# GROUP by SC. S#,sname has avg (score) >85;
34. Check the name and score of the student whose class is "database" with a score below 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 <60;
35. Check 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. Inquire about the name, course name 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, the examination of the course of failure, and according to the course number from large 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 more than 80;
Select SC. S#,student.sname from Sc,student where SC. s#=student.s# and Score>80 and
c#= ' 003 ';
39. Number of students selected for the course
Select COUNT (*) from SC;
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 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. Check each course and the corresponding number of elective
Select COUNT (*) from SC Group by C #;
42. Check the student's number, course number and student achievement of the same students with different course grades.
SELECT DISTINCT A.s#,b.score from SC A, SC B where A.score=b.score and a.c# <>b.c#;
43. Check the top two of the best results for each door
SELECT t1. s# as student id,t1. C # as course Id,score as fraction
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 participants are counted). Required to output the course number and number of elective
Query results in descending order by number of people, the query results in descending order of people, if the number is the same, in ascending order by course number
Select C # as course number, COUNT (*) as number of
From SC
GROUP BY C #
Order BY Count (*) desc,c#
45. Retrieve the student number of at least two elective courses
Select s#
From SC
GROUP BY s#
Having count (*) > = 2
46. Check the course number and course name of all the students who have enrolled in the course
Select C#,cname
From Course
where C # in (select C # from SC Group by C #)
47, inquiry did not learn the "cotyledons" teacher taught the name of any course students
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= ' cotyledons ');
48. Check the number of students with two or more failed courses and their average scores
Select S#,avg (IsNull (score,0)) from SC where s# in (select s# from SC where score <60 GROUP by
s# has count (*) >2) group by s#;
49, search "004" course score is less than 60, in descending order by the number of students
Select s# from SC where c#= ' 004 ' and score <60 ORDER by score Desc;
50, delete the "002" Students of the "001" The results of the course
Delete from Sc where s#= ' 001 ' and c#= ' 001 ';

Common SQL Notes