Http://www.ynpxrz.com/n822738c2024.aspx
T_tudent (sid,sname,sage,ssex,sdept) Student table
T_course (Cid,cname,tid) timetable
T_score (scid,sid,cid,grade) score table
T_teacher (tid,tname) Teacher table
Problem:
1, the inquiry "001" course is higher than "002" of all students of the school number;
Select T1.sid from
(select Sid,grade from t_score WHERE cid = ' 001 ') T1,
(select Sid,grade from t_score WHERE cid = ' 002 ') T2
where T1.grade >t2.grade and t1.sid = T2.sid;
Select T2.SID,T2.CA,T2.CB (
Select T1.sid,sum (t1.ca) ca,sum (T1.CB) CB
From
(
Select Sid,
case WHERE cid = ' 001 ' then grade ELSE 0 END CA,
case If CID = ' 002 ' then grade ELSE 0 END CB
From T_score
) T1
GROUP by Sid
) T2
where t2.ca > T2.CB
2, the query average score is more than 60 points of the student's number and average score;
Select Sid,avg (grade) from T_score
GROUP BY Sid
Having avg (grade) >60;
3, inquiry all students of the school number, name, number of courses selected, total;
Select T1.sid,t1.sname,count (t2.cid), sum (grade) from T_student T1
Left JOIN T_score T2
On t1.sid = T2sid
Group BY T1sid,t1.sname;
4, inquire the surname "Li" the number of teachers;
Select COUNT (Distinct (tname)) from T_teacher where tname like ' li% ';
5, the inquiry did not learn "cotyledons" teacher class students of the school number, name;
Select Sid, Sname from T_student
Where Sid not in
(
SELECT DISTINCT (T1.SID) from T_score t1,t_course t2,t_teacher T3
where t1.cid = t2.cid and T3.tid = T2.tid and t3.tname = ' cotyledons '
);
6, the inquiry learned "001" and also learned the number "002" course of the students ' study number, name;
Select T0.sid,t0.sname from T_student t0, T_score t1
where T0.sid = t1.sid and t1.cid = ' 001 '
and exists
(
SELECT * from T_score t2 where t2.sid = t1.sid and t2.cid = ' 002 '
);
7, the inquiry has learned "Cotyledons" the teacher teaches all classes The student's school number, the name;
Select Sid,sname from T_student
where SID in
(
Select Sid from T_score T1, T_course t2,t_teacher T3
where T1.cid=t2.cid
and T3.tid=t2.tid
and t3.tname = ' cotyledons '
GROUP BY Sid
Having count (t1.cid) = (
Select COUNT (CID) from T_course T1,t_teacher T2
where T2.tid=t1.tid and t2.tname = ' cotyledons '
)
);
8, the query "001" Course than "002" course performance of all students of the school number, name;
Select Sid,sname from
(
Select T1.sid,t1.sname,t2.grade,
(
Select grade from T_score T3
where T3.sid = t1.sid and t3.cid = ' 002 '
) Grade2
From T_student T1, T_score T2
where T1sid = t2.sid and t2.cid = ' 001 '
) s_2
Where Grade 2 < grade;
9, check all the course results are less than 60 points of the student's school number, name;
Select Sid,sname from T_student
Where Sid not in
(
Select T1.sid from t_student T1, T_score T2
where T1.sid = T2.sid and T2.grade >60
);
10, the inquiry did not learn all the class student's school number, the name;
Select T1.sid,t1.sname from Student T1, T_score T2
where T1.sid = T2.sid
GROUP BY T1.sid,t1.sname
Have count (t2.cid) < (select count (CID) from T_course);
11, the inquiry has at least one course and the school number for "1001" students learn the same student number and name;
Select Sid,sname from t_student T1, T_score T2
where T1.sid = T2.sid
and CID in (the Select CID from t_score where sid = ' 1001 ');
12, inquiry at least to learn the number of "001" Students all the other students of the class number and name;
Select DISTINCT t2.sid,t1.sname from t_student T1, T_score T2
where T1.sid = T2.sid
and CID in (the Select CID from t_score where sid = ' 001 ');
13, the "T_score" table "cotyledons" teacher teaching the results of the course are changed to the average performance of the curriculum;
Update T_score t4 Set grade = (select AVG (t3.grade) from T_score T3 where t3.cid= t4.cid)
From T_course T1,t_teacher T2
where t1.cid = T4.cid
and T1.tid = T2.tid
and t2.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 Sid from T_score
Where CID in (select CID from t_score where sid = ' 1002 ')
GROUP BY Sid
Have count (*) = (select count (*) from t_score where sid = ' 1002 ');
15, delete the learning "cotyledons" Teacher class T_score table records;
Delect T_score from T_course T1, T_teacher T2
Where t1.cid = T2.cid
and T1.tid = T2.tid
and t2.tname = ' cotyledons ';
16, insert some records into the T_score table, these records require the following conditions: No number "003" of the course's student number, 2,
The average grade of the class;
Insert T_score
Select Sid, ' 002 ', (select AVG (grade) from T_score where cid = ' 002 ')
From T_student
Where Sid is not in (the Select SID from T_score WHERE cid = ' 002 ');
17, according to the average score from high to low display all students "database", "Enterprise Management", "English" the course results, as shown in the following form: Student ID, database, business management, English, effective course number, effective average score
SELECT SID as Student ID,
(SELECT grade from T_score WHERE Sid=t.sid and cid= ' 004 ') As database,
(SELECT grade from T_score WHERE Sid=t.sid and cid= ' 001 ') As Enterprise management,
(SELECT grade from T_score WHERE Sid=t.sid and cid= ' 006 ') As English,
COUNT (*) as effective number of courses,
AVG (T.grade) as average score
From T_score as T
GROUP by Sid
ORDER by AVG (T.grade)
18. Check the highest and lowest score of each section: show the following form: Course ID, highest score, lowest score
SELECT T1.cid as id,t1.grade as highest score, t2.grade as Min.
From T_score T1, T_score T2
WHERE t1.cid = T2.cid
and T1.grade = (
SELECT MAX (Tt1.grade) from T_score tt1,t_student tt2
WHERE t1.cid = tt1.cid and Tt2.sid = Tt1.sid
GROUP by Tt1.cid
)
and T2.grade = (
SELECT MIN (Tt3.grade) from T_score as Tt3
WHERE t2.cid = Tt3.cid GROUP by tt2.cid
);
19, according to the average grades from low to high and the percentage of passing rate from high to low order
SELECT T.cid as course number, Max (Course.cname) As course name, ISNULL (avg (grade), 0) as average score,
* SUM (case if IsNull (grade, 0) >=60 then 1 ELSE 0 END)/count (*) as passing percentage
From T_score T1,t_course T2
where t1.cid = T2.cid
GROUP by T1.cid
ORDER by $ * SUM (case when IsNull (grade, 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&UML (003), Database (004)
SELECT
SUM (
case when CID = ' 001 '
Then grade ELSE 0 END)/sum (case CID when ' 001 '
Then 1 ELSE 0 END
As Enterprise management average score,
* SUM (
case when CID = ' 001 ' and grade >= 60
Then 1 ELSE 0 END)/sum (case when cid = ' 001 '
Then 1 ELSE 0 END
As enterprise management passing percentage from T_score
21, query different teachers teach different courses average from high to low display
SELECT Max (T3.tid) as Teacher Id,max (T3.tname) as teacher name,
T2.cid as Course Id,max (t2.cname) As course name, AVG (grade) as average score
From T_score as t1,t_course as T2, t_teacher as T3
where t1.cid = t2.cid and T2.tid = T3.tid
GROUP by T2.cid
ORDER by AVG (grade) DESC;
22, query The following course results 3rd to 6th Students Transcript: Enterprise Management (001), Marx (002), UML (003), Database (004)
[Student id],[student name], Enterprise management, Marx, UML, database, average score
SELECT DISTINCT Top 3 T_score.sid as student number, student.sname as student name,
T1.grade as Enterprise management, T2.grade as Marx, T3.grade as UML,
T4.grade as Database, ISNULL (t1.grade, 0) + ISNULL (t2.grade, 0) + ISNULL (t3.grade, 0) + ISNULL (t4.grade, 0) as total score
From Student, T_score
Left JOIN T_score as T1
On t_score.sid = t1.sid and t1.cid = ' 001 '
Left JOIN T_score as T2
On t_score.sid = t2.sid and t2.cid = ' 002 '
Left JOIN T_score as T3
On t_score.sid = t3.sid and t3.cid = ' 003 '
Left JOIN T_score as T4
On t_score.sid = t4.sid and t4.cid = ' 004 '
WHERE student.sid= T_score.sid
and ISNULL (T1.grade, 0) + ISNULL (t2.grade, 0) + ISNULL (t3.grade, 0) + ISNULL (t4.grade, 0)
Not IN (
SELECT DISTINCT TOP with TIES ISNULL (t1.grade, 0) + ISNULL (t2.grade, 0) + ISNULL (t3.grade, 0) + ISNULL (t4.grade, 0)
From T_score
Left JOIN T_score as T1
On t_score.sid = t1.sid and t1.cid = ' K1 '
Left JOIN T_score as T2
On t_score.sid = t2.sid and t2.cid = ' K2 '
Left JOIN T_score as T3
On t_score.sid = t3.sid and t3.cid = ' K3 '
Left JOIN T_score as T4
On t_score.sid = t4.sid and t4.cid = ' K4 '
ORDER by ISNULL (t1.grade, 0) + ISNULL (t2.grade, 0) + ISNULL (t3.grade, 0) + ISNULL (t4.grade, 0) DE T_score
);
23, the statistics print each section result, each score segment number: Course ID, course name, [100-85],[85-70],[70-60],[<60]
SELECT T1.cid as Course ID, t2.cname as course name,
SUM (case when t1.grade between and 1 ELSE 0 END) as [100-85],
SUM (case when t1.grade between-and-then 1 ELSE 0 END) as [85-70],
SUM (case when t1.grade between and 1 ELSE 0 END) as [70-60],
SUM (case when T1.grade < 1 ELSE 0 END) as [60-]
From T_score t1,t_course t2 where t1.cid = T2.cid
GROUP by T1.cid,t2.cname;
24, check the average grade of students and their rankings
SELECT
1+ (
SELECT COUNT (Distinct avggrade)
From (
SELECT Sid,avg (grade) as Avggrade from T_score GROUP by Sid
As T1 where Avggrade > T2.avggrade) as Rank,
Sid as student school number,
Avggrade
From (
SELECT Sid,avg (grade) as Avggrade from T_score GROUP by Sid
) as T2
ORDER by Avggrade Desc;
25. Check the records of the top three grades of each section: (regardless of the performance of the situation)
SELECT T1.sid as Student id,t1.cid as course id,grade as fraction
From T_score T1 WHERE grade in (
SELECT TOP 3 grade from T_score t2 WHERE t1.cid = T2.cid ORDER by grade DESC
)
ORDER by T1.cid;
26. Check the number of students who have been enrolled in each course
Select Cid,count (SID) from the T_score group by CID;
27. Find out the number and name of all students who have only one course of study
Select T1.sid,t2.sname,count (t1.cid) As course number from T_score t1,t_student T2
where T1.sid=t2.sid
Group BY T1.sid, T2.sname
Having count (t1.cid) = 1;
28. Query the number of boys and girls
Select count (ssex) as the number of males from the T_student group by Ssex have ssex= ' male ';
Select count (ssex) as the number of females from the T_student group by Ssex have ssex= ' female ';
29. Check the list of students with the surname "Zhang"
SELECT sname from t_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 T-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 T_tudent
where CONVERT (char (one), DATEPART (year,sage)) = ' 1981 ';
32, the average score of each course is queried, the results are arranged in ascending order of average grade, and the average result is the same, descending by the course number.
Select Cid,avg (grade) from T_score Group by CID ORDER by AVG (grade), CID DESC
33. The number, name and average scores of all students who have average scores greater than 85
Select T1.sname, T2.sid, avg (t2.grade) from t_student T1, T_score T2
where T1.sid=t2.sid GROUP by T2.sid,t1.sname
Having avg (T2.grade) >85;
34. Check the name and score of the student whose class is "database" with a score below 60
Select t1.sname,isnull (t2.grade, 0) from T_student T1, T_score t2,t_course T3
where T2.sid = T1.sid and t2.cid = T3.cid
and t3.cname= ' database ' and T2.grade <60;
35. Check the course selection of all students;
SELECT T1.sid, t1.cid,t2.sname,t3.cname from T_score t1,t_student t2,t_course T3
where T1.sid = T2.sid and t1.cid = T3.cid;
36. Inquire about the name, course name and score of any course score above 70 points;
SELECT distinct t1.sid,t1.sname,t2.cid, t2.grade from t_student T1, T_score T2
WHERE T2.grade >=70 and t2.sid = T1.sid;
37, the examination of the course of failure, and according to the course number from large to small arrangement
Select CID from T_score where grade < the order by CID;
38. The number and name of the student whose course number is 003 and the course score is more than 80;
Select T1.sid,t2.sname from T_score t1,t_student T2
where T1.sid = T2.sid and T1.grade > and t1.cid = ' 003 ';
39. Number of students selected for the course
Select COUNT (*) from T_score;
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 T1.sname,t2.grade from T_student T1, T_score t2,t_course t3,t_teacher T4
where T1.sid = T2.sid
and t2.cid = T3.cid
and T3.tid = T4.tid
and T4. Tname = ' cotyledons '
and T2.grade = (select Max (grade) from T_score where cid=t3.cid);
41. Check each course and the corresponding number of elective
Select COUNT (*) from T_score GROUP by CID
42. Check the student's number, course number and student achievement of the same students with different course grades.
Select DISTINCT T1.sid,t2.grade from T_score T1, T_score T2
where T1.grade = T2.grade and T1.cid <>t2.cid;
43. Check the top two of the best results for each door
SELECT T1.sid as Student id,t1.cid as course Id,grade as score from T_score T1
WHERE Grade in (
SELECT TOP 2 grade from T_score
WHERE t1.cid = cid ORDER by grade DESC
)
ORDER by T1.cid;
44. The number of students enrolled in each course (more than 10 participants are counted). Require the output of the course number and the number of elective, query results in descending order of numbers, query results in descending order of numbers, if the number is the same, in ascending order by course number
Select CID as course number, COUNT (*) as number from T_score
GROUP BY CID
Order BY Count (*) desc,cid
45. Retrieve the student number of at least two elective courses
Select SID from T_score GROUP by SID has Count (*) > = 2
46. Check the course number and course name of all the students who have enrolled in the course
Select Cid,cname from T_course where CID on (select CID from T_score Group by CID)
47, inquiry did not learn the "cotyledons" teacher taught the name of any course students
Select Sname from T_student
Where Sid not in (
Select Sid from T_course t1,t_teacher T2, T_score T3
where T1.tid = T2.tid
and T3.cid =t 1.cid
and t2.tname = ' cotyledons '
);
48. Check the number of students with two or more failed courses and their average scores
Select Sid,avg (IsNull (grade, 0)) from T_score
where Sid in (
Select Sid from T_score
Where grade <60 GROUP by SID has Count (*) >2
)
Group BY SID;
49, search "004" course score is less than 60, in descending order by the number of students
Select Sid from T_score where cid= ' 004 ' and grade <60 the order by grade Desc;
50, delete the "002" Students of the "001" The results of the course
Delete from T_score where sid= ' 001 ' and cid = ' 001 ';
SQL advanced Query