[50 commonly used MySQL statements] if you are not familiar with these 50 sql statements, don't say you will MySQL [suggest collection]

This article mainly introduces 50 SQL query skills and query sentence examples. Taking student table, curriculum table, grade table and teacher table as examples, this paper explains the writing methods of SQL sentences under different needs. Friends in need can refer to it. It is suggested that comrades who often use MySQL collect them.

Student(S#,Sname,Sage,Ssex) student form
Course(C#,Cname,T #) course schedule
SC(S#,C#,score) score sheet
Teacher(T#,Tname) teacher form

catalogue

1. Query the student numbers of all students whose grades in "001" course are higher than those in "002" course;

2. Query the student number and average score of students whose average score is greater than 60;

3. Query the student number, name, number of courses selected and total score of all students;

4. Query the number of teachers surnamed "Li";

5. Query the student number and name of the students who have not studied "Ye Ping" teacher's class;

6. Query the student number and name of students who have studied "001" and also studied the course numbered "002";

7. Query the student number and name of the students who have learned all the lessons taught by "Ye Ping";

8. Query the student numbers and names of all students whose grades of course number "002" are lower than those of course number "001";

9. Query the student number and name of all students whose course scores are less than 60;

10. Query the student number and name of students who have not learned all courses;

11. Query the student number and name of at least one course that is the same as that of the student with student number "1001";

12. Query the student numbers and names of other students who have studied at least one course with student number "001";

13. Change the scores of the courses taught by "Ye Ping" in the "SC" table to the average score of this course;

14. Query the student number and name of other students who are exactly the same as the course of "1002";

15. Delete the SC table record of learning "Ye Ping" teacher's class;

16. Insert some records into the SC table. These records are required to meet the following conditions: the average scores of student number, class 2 and class 3 of students who have not taken the course numbered "003";

17. The course scores of "database", "enterprise management" and "English" of all students are displayed from high to low according to the average score. They are displayed in the following forms: Student ID, database, enterprise management, English, number of effective courses and effective average score

18. Query the highest and lowest scores of each subject: displayed in the following form: course ID, highest score and lowest score

19. According to the order of the average score of each subject from low to high and the percentage of passing rate from high to low

20. Query the percentage of average score and passing rate of the following courses (displayed in "1 line"): Enterprise Management (001), Marx (002), OO & UML (003), database (004)

21. Query the average scores of different courses taught by different teachers from high to low

22. Query the transcripts of students from the 3rd to 6th in the following courses: Enterprise Management (001), Marx (002), UML (003), database (004) [student ID], [student name], enterprise management, Marx, UML, database, average score

23. Count and print the scores of each subject and the number of people in each score segment: course ID, course name, [100-85], [85-70], [70-60], [< 60]

24. Query the average score and ranking of students

25. Query the records of the top three scores of each subject: (regardless of the juxtaposition of scores)

26. Query the number of students selected for each course

27. Find out the student numbers and names of all students who have only taken one course

28. Query the number of boys and girls

29. Query the list of students surnamed "Zhang"

30. Query the list of same-sex students with the same name and count the number of students with the same name

31. List of students born in 1981 (Note: the type of Sage column in the Student table is datetime)

32. Query the average score of each course. The results are arranged in ascending order according to the average score. If the average score is the same, they are arranged in descending order according to the course number

33. Query the student number, name and average score of all students whose average score is greater than 85

34. Query the names and scores of students whose course name is "database" and whose score is lower than 60

35. Query the course selection of all students;

36. Query the name, course name and score of any course with a score of more than 70;

37. Query the failed courses and arrange them according to the course number from large to small

38. Query the student number and name of students whose course number is 003 and whose course score is more than 80;

39. Number of students selected courses

40. Query the names and scores of the students with the highest scores among the students taking the courses taught by "Ye Ping"

41. Query each course and the corresponding number of electives

42. Query the student number, course number and student achievement of students with the same scores in different courses

43. Query the top two with the best performance in each skill

44. Count the number of students taking elective courses in each course (only courses with more than 10 students can be counted). It is required to output the course number and the number of electives. The query results are arranged in descending order by the number of people. If the number of people is the same, they are arranged in ascending order by the course number

45. Search the student numbers of students taking at least two courses

46. Query the course number and course name of the courses that all students take

47. Query the names of students who have not studied any course taught by "Ye Ping"

48. Query the student number and average score of students who have failed more than two courses

49. Retrieve the student numbers of students whose scores of "004" course are less than 60 in descending order

50. Delete the grade of "001" course of "002"

1. Query the student numbers of all students whose grades in "001" course are higher than those in "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. Query the student number and average score of students whose average score is greater than 60;

select S#,avg(score)
from sc
group by S# having avg(score) >60;


3. Query the student number, name, number of courses selected and total score of all students;

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. Query the number of teachers surnamed "Li";

select count(distinct(Tname))
from Teacher
where Tname like 'Lee%';


5. Query the student number and name of the students who have not studied "Ye Ping" teacher's class;

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 = 'Ye Ping');


6. Query the student number and name of students who have studied "001" and also studied the course numbered "002";

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. Query the student number and name of the students who have learned all the lessons taught by "Ye Ping";

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,Teacher where Teacher.T#=Course.T# and Tname = 'Ye Ping');


8. Query the student numbers and names of all students whose grades of course number "002" are lower than those of 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. Query the student number and name of all students whose course scores are less than 60;

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


10. Query the student number and name of students who have not learned all courses;

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. Query the student number and name of at least one course that is the same as that of the student with student number "1001";

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


12. Query the student numbers and names of other students who have studied at least one course with student number "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 scores of the courses taught by "Ye Ping" 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 = 'Ye Ping');


14. Query the student number and name of other students who are exactly the same as the course of "1002";

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

Delect SC from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname = 'Ye Ping';


16. Insert some records into the SC table. These records are required to meet the following conditions: the average scores of student number, class 2 and class 3 of students who have not taken the course numbered "003";

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 course scores of "database", "enterprise management" and "English" of all students are displayed from high to low according to the average score. They are displayed in the following forms: Student ID, database, enterprise management, English, number of effective courses and 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 Number of effective courses, AVG(t.score) AS Average score
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)


18. Query the highest and lowest scores of each subject: displayed in the following form: course ID, highest score and 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 order of the average score of each subject from low to high and the percentage of passing rate 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 percentage of average score and passing rate of the following courses (displayed in "1 line"): 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 average score
,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 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 pass 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
,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 UML pass percentage
,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# When '004' then 1 else 0 end) average score of as database
,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 pass percentage
FROM SC


21. Query the average scores of different courses taught by different teachers 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 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 transcripts of students from the 3rd to 6th in the following courses: 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 number,
Student.Sname AS Student name ,
T1.score AS business 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. Count and print the scores of each subject and the number of people in each score 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. Query the average score and ranking of students

SELECT 1+(SELECT COUNT( distinct Average score)
FROM (SELECT S#, AVG(score) AS
FROM SC
GROUP BY S#
) AS T1
WHERE Average score > T2.Average score) as Ranking,
S# as student number, average score
FROM (SELECT S#, AVG(score)
FROM SC
GROUP BY S#
) AS T2
ORDER BY Average score desc;

25. Query the records of the top three scores of each subject: (regardless of the juxtaposition of scores)

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. Query the number of students selected for each course

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


27. Find out the student numbers and names of all students who have only taken one course

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 Number of boys from Student group by Ssex having Ssex='male';
Select count(Ssex) as Number of girls from Student group by Ssex having Ssex='female';


29. Query the list of students surnamed "Zhang"

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


30. Query the list of same-sex students with the same name and count the number of students with the same name

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


31. List of students born in 1981 (Note: the type of Sage column in the Student table is datetime)

select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age
from student
where CONVERT(char(11),DATEPART(year,Sage))='1981';


32. Query the average score of each course. The results are arranged in ascending order according to the average score. If the average score is the same, they are arranged in descending order according to the course number

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


33. Query the student 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. Query the names and scores of students whose course name is "database" and whose score is lower than 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. Query 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. Query the name, course name and score of any course with a score of more than 70;

SELECT distinct student.S#,student.Sname,SC.C#,SC.score
FROM student,Sc
WHERE SC.score>=70 AND SC.S#=student.S#;


37. Query the failed courses and arrange them according to the course number from large to small

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


38. Query the student number and name of students whose course number is 003 and whose 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 courses

select count(*) from sc;


40. Query the names and scores of the students with the highest scores among the students taking the courses taught by "Ye Ping"

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. Query each course and the corresponding number of electives

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


42. Query the student number, course number and student achievement of students with the same scores 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 top two with the best performance in each skill

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. Count the number of students taking elective courses in each course (only courses with more than 10 students can be counted). It is required to output the course number and the number of electives. The query results are arranged in descending order by the number of people. If the number of people is the same, they are arranged in ascending order by the course number

select C# As course number, count(*) as number of people
from sc
group by C#
order by count(*) desc,c#


45. Search the student numbers of students taking at least two courses

select S#
from sc
group by s#
having count(*) > = 2


46. Query the course number and course name of the courses that all students take

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


47. Query the names of students who have not studied 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 = 'Ye Ping');


48. Query the student number and average score of students who have failed more than two courses

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. Retrieve the student numbers of students whose scores of "004" course are less than 60 in descending order

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


50. Delete the grade of "001" course of "002"

delete from Sc where S#='001'and C#='001';

Tags: Java Database SQL

Posted on Mon, 11 Oct 2021 21:50:53 -0400 by keyurjbhatt