Category:php
Article From:https://www.cnblogs.com/haohaoyuan/p/9124595.html

/*
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], [&lt, 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

Course(Cid,Cname,Tid) Class Schedule Card
SC(Sid,Cid,score) Achievement table
Teacher(Tid,Tname) Teacher’s Watch
 
Exercise content:
1.To inquire the student number of “a 1” course is higher than that of a “2” course.
SELECT a.sid FROM (SELECT sid,score FROM SC WHERE cid=1) a,(SELECT sid,score FROM SC WHERE cid=3) b WHERE a.score>b.score AND a.sid=b.sid;
This knowledge is nested and the table to be checked is aliased.
2.For students whose average score is greater than 60, the student number and the average score are inquired.
SELECT sid,avg(score)  FROM sc  GROUP BY sid having avg(score) >60;
In this case, the GROUP BY statement is used to group a result set according to one or more columns in conjunction with the aggregate function. Where can not be followed by group by, having instead of where.
3.Check all students’ student numbers, names, course number and total score.
SELECT Student.sid,Student.Sname,count(SC.cid),sum(score)FROM Student left Outer JOIN SC on Student.sid=SC.cid GROUP BY Student.sid,Sname
4.The number of teachers who inquire about “Li”;
select count(teacher.tid)from teacher where teacher.tname like’Li% ”
5.I did not know the student numbers and names of the students in the “Ye Ping” teacher’s class.
SELECT Student.sid,Student.Sname FROM Student WHERE sid not in (SELECT distinct( SC.sid) FROM SC,Course,Teacher WHERE  SC.cid=Course.cid AND Teacher.id=Course.tid AND Teacher.Tname=’Leaf leveling.
 The point of knowledge, distinct is the role of deweighting
6.The student number and name of the students who have studied “` ` ` and have also learned the numbered ` ` ` course” have been inquired.
select a.SID,a.SNAME from (select student.SNAME,student.SID from student,course,sc where cname=’c++’and sc.sid=student.sid and sc.cid=course.cid) a,
(select student.SNAME,student.SID from student,course,sc where cname=’english’and sc.sid=student.sid and sc.cid=course.cid) b where a.sid=b.sid;
Standard answer (but it doesn’t seem to be good enough) 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′)
Knowledge of this problem, exists is collecting data in the collection, as is an alias.
7.The student number and name of all the classes taught by Ye Ping teacher were inquired.
select a.sid,a.sname from (select student.sid,student.sname from student,teacher,course,sc 
where teacher.TNAME=’Yang Weiwei ‘and teacher.tid=course.tid and course.cid=sc.cid and student.sid=sc.sid) a
Standard answer: SELECT SID, Sname FROM Student WHERE Sid in (SELECT Sid FROM FROM, *, “Urse.cid AND Teacher.tid=Course.tid AND Teacher.Tname=’Yang Weiwei’ GROUP BY Sid having count (SC.cid) = (SC.cid)T count (CID) FROM Course, Teacher WHERE WHERE Teacher.tid=Course.tid AND Tname=’Yang Wei Wei)
8.The student number and the name of all students who inquired the course number “lower than the course number”;
select a.sid,a.sname from(select student.SID,student.sname,sc.SCORE  from student,sc where student.sid=sc.sid and sc.cid=1) a,
(select student.SID,student.sname,sc.score from student,sc where student.sid=sc.sid and sc.cid=2) b where a.score<b.score and a.sid=b.sid
Standard answer: SELECT SID, Sname FROM (SELECT Student.sid, Student.Sname, score,
(SELECT score FROM SC SC_2 WHERE SC_2.sid=Student.sid AND SC_2.cid=1) score2 FROM Student,SC
WHERE Student.sid=SC.sid AND cid=1) S_2 WHERE score2 <score;
9.Check the student numbers and names of all the students whose grades are below the score.
SELECT sid,Sname FROM Student WHERE sid not in (SELECT Student.sid FROM Student,SC WHERE Student.sid=SC.sid AND score>60); 
First, find out more than 60 points, then not in is less than 60 points.
10.The students did not learn the names and names of all the students.
SELECT Student.sid,Student.Sname  FROM Student,SC  
WHERE Student.sid=SC.sid GROUP BY  Student.sid,Student.Sname having count(cid) <(SELECT count(cid) FROM Course); 
11.The student numbers and names of the same students who have learned at least one course with the student number “
12.At least you have studied the student numbers and names of other students in all courses.
SELECT student.sid,student.Sname FROM Student,SC WHERE Student.sid=SC.sid AND cid in (SELECT cid FROM SC WHERE sid=1)
The knowledge of this topic is SELECT SID, Sname FROM Student, SC WHERE Student.sid=SC.sid AND CID in (CID)Sid=1) it is wrong to write this way, because from is followed by two tables, and it can not be clear which table is Sid and sname, so the error is “unknown definite column”.
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(score) from sc,course,teacher where course.cid=sc.cid and course.tid=teacher.tid and teacher.tname=’Yang Weiwei ‘)
14.Students and students in the same course have the same student numbers and names.
SELECT sid FROM SC WHERE cid in (SELECT cid FROM SC WHERE sid=6) GROUP BY sid having count(*)=(SELECT count(*) FROM SC WHERE sid=6); 
The point of knowledge is judged by quantity.
15.Delete the SC table record of learning Ye Ping teacher’s lesson.
delete from sc s where s.cid in (select c.cid from teacher t,course c where t.tid = c.tid and tname=’Li Zi ‘)
In this case, nested queries can be distributed and considered. First, find out what class ID has been handed in, and then delete those values of ID.
16.To insert some records into the SC table, these records meet the following requirements: the average number of student numbers and courses that have not been numbered “Courses”;
Insert into SC SELECT sid,2,(SELECT avg(score) FROM SC WHERE cid=2) FROM Student WHERE sid not in (SELECT sid FROM SC WHERE cid=2); 
17.The results of the three courses of “database”, “enterprise management” and “English” from high to low, are shown in the following form: student ID, database, enterprise management, English, effective courses, effective average score; (not done)
18.The highest and lowest scores were found in all subjects: ID, the highest score and the lowest score.
select cid as Course number, max (score) as highest score, min (score) as lowest score from SC group by CID
Standard answer (but not running well) SELECT L.cid As course ID, L.score AS highest score, R.score AS lowest score.
FROM SC L ,SC AS R  
WHERE L.cid = R.cid AND  
L.score = (SELECT MAX(IL.score)  
FROM SC AS IL,Student AS IM  
WHERE L.cid = IL.cid AND IM.sid=IL.sid  
GROUP BY IL.cid)  
AND  R.Score = (SELECT MIN(IR.score) FROM SC AS IR WHERE R.cid = IR.cid  GROUP BY IR.cid ); 
19.According to the average scores from low to high, and the percentage of passing rates from high to low.
26.Inquiries about the number of students selected for each course
select sc.cid,count(sc.sid) from sc,course where sc.cid=course.cid group by sc.cid
27.Check out the student numbers and names of all the students who have only chosen one course.
SELECT SC.sid,Student.Sname,count(cid) AS The number of selected classes FROM SC, Student
WHERE SC.sid=Student.sid GROUP BY SC.sid ,Student.Sname having count(cid)=1;
32.The average scores of each course are ranked in the ascending order of the average scores, and the average scores are in the same order.
SELECT Cid,Avg(score) FROM SC GROUP BY cid ORDER BY Avg(score),cid DESC ;
37.Check out the failed courses and arrange them according to the number of courses.
SELECT cid,sid FROM sc WHERE score <60 ORDER BY cid 
38.The student number and name of the student whose course number is above the score is above.
select student.sid,student.sname from sc,student where sc.cid=1 and sc.score>60 and sc.sid=student.sid
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 Ye Ping teachers are inquired.
select student.sname,sc.score from sc,student,teacher,course c where teacher.tname=’Plums’
and teacher.tid=c.tid and c.cid=sc.cid and sc.sid=student.sid and sc.score=(select max(score)from sc where sc.cid=c.cid)
41.Inquiries for each course and the number of electives
select sc.cid ,count(sc.sid)from sc,student where sc.sid=student.sid group by sc.cid 
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.Search the top two best grades for each door
 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 the number of elective students in each course. Require the output of the course number and the number of elective, query results in descending order according to the number of people, query results in descending order according to the number of people, if the number is the same, according to the course number of ascending order
select sc.cid,count(sc.cid)from sc,course where sc.cid=course.cid group by sc.cid  order by sc.cid desc
45.Search for student number of at least two courses
SELECT sid FROM  sc group  by  sid having  count(*)  >  =  2  
 
rownumUsage
Check all grades from second to fourth
select * from (select rownum p,t.score from(SELECT s.score score FROM sc s ORDER BY score desc)t )tt where tt.p>1 and tt.p<5
 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′;
 

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

Link of this Article: SQL query 50 questions

Leave a Reply

Your email address will not be published. Required fields are marked *