MySQL -- sql advanced syntax: description of 50 examples

A topic involves 50 Sql statements --(the structure of the table below is given. You can create a table in the database and add corresponding data. The data should be more comprehensive. In the Student table, SId is the Student ID)

------------------------------------Table structure--------------------------------------
--Student list tblStudent(number StuId,full name StuName,Age StuAge,Gender StuSex)
--Class Schedule Card tblCourse(Course number CourseId,Course name CourseName,Teacher number TeaId)
--Transcript tblScore(Student number StuId,Course number CourseId,achievement Score)
--Teacher table tblTeacher(Teacher number TeaId,full name TeaName)
---------------------------------------------------------------------------------

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

Select StuId From tblStudent s1 
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId='001')>
 (Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId='002')

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

 Select StuId,Avg(Score) as AvgScore From tblScore
  Group By StuId
   Having Avg(Score)>60

--3. Query the student number, name, number of courses and total score of all students;    Select StuId,StuName,   SelCourses=(Select Count(CourseId) From tblScore t1 Where t1.StuId=s1.StuId),   SumScore=(Select Sum(Score) From tblScore t2 Where t2.StuId=s1.StuId)      From tblStudent s1

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

 Select Count(*) From tblTeacher Where TeaName like 'Lee%'

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

 Select StuId,StuName From tblStudent
  Where StuId Not In
   (
  Select StuID From tblScore sc
  Inner Join tblCourse cu ON sc.CourseId=cu.CourseId
     Inner Join tblTeacher tc ON cu.TeaId=tc.TeaId    
      Where tc.TeaName='Ye Ping'
   )

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

 Select StuId,StuName From tblStudent st
  Where (Select Count(*) From tblScore s1 Where s1.StuId=st.StuId And s1.CourseId='001')>0
   And
   (Select Count(*) From tblScore s2 Where s2.StuId=st.StuId And s2.CourseId='002')>0

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

 Select StuId,StuName From tblStudent st Where not exists
  (
   Select CourseID From tblCourse cu Inner Join tblTeacher tc On cu.TeaID=tc.TeaID 
    Where tc.TeaName='Ye Ping' And CourseID not in 
    (Select CourseID From tblScore Where StuID=st.StuID)
  )

--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 StuId,StuName From tblStudent s1 
  Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId='001')>
   (Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId='002')

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

 Select StuId,StuName From tblStudent st
  Where StuId Not IN
   (Select StuId From tblScore sc Where st.StuId=sc.StuId And Score>60)

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

 Select StuId,StuName From tblStudent st
  Where (Select Count(*) From tblScore sc Where st.StuId=sc.StuId)<
   (Select Count(*) From tblCourse)

--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"; ------ using connection query

 Select DistInct st.StuId,StuName From tblStudent st
  Inner Join tblScore sc ON st.StuId=sc.StuId
   Where sc.CourseId IN (Select CourseId From tblScore Where StuId='1001')
 ------nested subqueries 
 Select StuId,StuName From tblStudent
  Where StuId In
  (
   Select Distinct StuId From tblScore Where CourseId In (Select CourseId From tblScore Where StuId='1001')
  )

--12. Query the student numbers and names of other students who have studied at least all courses with student number "1001";  

Select StuId,StuName From tblStudent
  Where StuId In
  (
   Select Distinct StuId From tblScore Where CourseId Not In (Select CourseId From tblScore Where StuId='1001')

--13. Change the grades of the courses taught by "Ye Ping" in the "SC" table to the average grade of this course; (get the table name in the parent query from the sub query, which is OK.)

 --Create test "Software testing knowledge base")surface 
 Select * Into Sc From tblScore
 Go"Go knowledge base")

 Update Sc Set Score=(Select Avg(Score) From tblScore s1 Where s1.CourseId=sc.CourseId) 
  Where CourseId IN
   (Select CourseId From tblCourse cs INNER JOIN tblTeacher tc ON cs.TeaID=tc.TeaID WHERE TeaName ='Ye Ping')

--14. Query the student numbers and names of other students who are exactly the same as the courses studied by students with "1002";

 Select StuID,StuName From tblStudent st
  Where StuId <> '1002' 
   And 
   Not Exists(Select * From tblScore sc Where sc.StuId=st.StuId And CourseId Not In (Select CourseId From tblScore Where StuId='1002')) 
   And
   Not Exists(Select * From tblScore Where StuId='1002' And CourseId Not In (Select CourseId From tblScore sc Where sc.StuId=st.StuId))

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

 Delete From tblScore Where CourseId IN 
  (Select CourseId From tblCourse cs INNER JOIN tblTeacher tc ON cs.TeaId=tc.TeaId Where tc.TeaName='Ye Ping')

--16. Insert some records into the SC table. These records are required to meet the following conditions: the student number of students who have not taken the course numbered "003" and the average score of course '002';

 Insert Into tblScore (StuId,CourseId,Score) 
  Select StuId,'002',(Select Avg(Score) From tblScore Where CourseId='002') From tblScore Where
   StuId Not In (Select StuId From tblScore Where CourseId='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 form: Student ID, database, enterprise management, English, number of effective courses and effective average score

 Select StuId
  ,database=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='database' And sc.StuID=st.StuId)
  ,business management=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='business management' And sc.StuID=st.StuId)
  ,English=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='English' And sc.StuID=st.StuId)
  ,Number of effective courses=(Select Count(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='database' or CourseName='business management' or CourseName='English') And sc.StuID=st.StuId)
  ,Effective mean score=(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='database' or CourseName='business management' or CourseName='English') And sc.StuID=st.StuId)
  From tblStudent st
  Order by Effective mean score Desc

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

 Select CourseId as curriculum ID, Highest score=(Select Max(Score) From tblScore sc Where sc.CourseId=cs.CourseId ),
  Lowest score=(Select Min(Score) From tblScore sc Where sc.CourseId=cs.CourseId )
  From tblCourse cs

--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 (how to format the percentage into two decimal places?) Select course ID, average score, pass rate From

  (Select CourseId as curriculum ID, average=(Select Avg(Score) From tblScore sc Where sc.CourseId=cs.CourseId ),
   pass rate=Convert(varchar(10),((Select Count(*) From tblScore sc Where sc.CourseId=cs.CourseId And sc.Score>=60)*10000/(Select Count(*) From tblScore sc Where sc.CourseId=cs.CourseId))/100)+'%'
   From tblScore cs) as tmp
  Group by curriculum ID,average,pass rate
   Order by average, Convert(float,substring(pass rate,1,len(pass rate)-1)) 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 curriculum ID=sc.CourseId,Course name=cs.CourseName,Average score=Avg(Score)
  ,pass rate =Convert(varchar(10),((Select Count(Score) From tblScore Where CourseId=sc.CourseId And Score>=60)*10000/Count(Score))/100.0)+'%'
  From tblScore sc
  Inner Join tblCourse cs ON sc.CourseId=cs.CourseId
  Where sc.CourseId like '00[1234]'
  Group By sc.CourseId,cs.CourseName

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

 Select curriculum ID=CourseId,Course name=CourseName,Instructor=TeaName,Average score=(Select Avg(Score) From tblScore Where CourseId=cs.CourseId)
  From tblCourse cs
  Inner Join tblTeacher tc ON cs.TeaId=tc.TeaId
  Order by Average 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) format: [student ID], [student name], enterprise management, Marx, UML, database, average score

 Select * From 
  (
   Select Top 6 student ID=StuId,Student name=StuName
    ,business management=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='business management' And sc.StuID=st.StuId)
    ,Marx=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='Marx' And sc.StuID=st.StuId)
    ,UML=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='UML' And sc.StuID=st.StuId)
    ,database=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='database' And sc.StuID=st.StuId)
    ,Average score=(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='database' or CourseName='business management' or CourseName='UML'or CourseName='Marx') And sc.StuID=st.StuId)
    ,ranking=Row_Number() Over(Order by(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='database' or CourseName='business management' or CourseName='UML'or CourseName='Marx') And sc.StuID=st.StuId) DESC)
    From tblStudent st
    Order by ranking
  ) as tmp
  Where ranking between 3 And 6

--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 course ID=CourseId, course name = CourseName

  ,[100-85]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 85 And 100)
  ,[85-70]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 70 And 84)
  ,[70-60]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 60 And 69)
  ,[<60]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score <60)
  From tblCourse cs

--24. Query the average score and ranking of students

 Select Student number=st.StuId, full name=StuName,Average score=sc.AvgScore,Ranking=(Dense_Rank() Over(Order by sc.AvgScore Desc)) From tblStudent st
  Inner Join (Select StuId,Avg(Score) as AvgScore From tblScore Group by StuId) as sc On sc.StuId=st.StuId
  Order by Student number

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

 Select Student number=StuId,Course number=CourseId,fraction=Score
  From 
  (Select Row_Number() Over(order by CourseId,Score Desc) as i,* From tblScore) as tmp --Get a temporary ranking table,among i Indicates the number
   Where i In 
   (
    Select Top 3 i From (Select Row_Number() Over(order by CourseId,Score Desc) as i,* From tblScore) as t1 Where t1.CourseId=tmp.CourseId
   )

--26. Query the number of students selected for each course

 Select curriculum ID=CourseId,Number of electives=(Select Count(*) From (Select Distinct StuId From tblScore Where CourseId=cs.CourseId) as tmp)
  From tblCourse cs

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

 Select Student number=StuId,full name=StuName
  From tblStudent st 
  Where (Select Count(*) From (Select Distinct CourseId From tblScore Where StuId=st.StuId) as tmp)=1

--28. Query the number of boys and girls

 Select Number of boys=(select Count(*) From tblStudent Where StuSex='male'),
   Number of girls=(select Count(*) From tblStudent Where StuSex='female')

--29. Check the list of students surnamed "Zhang"

 Select * From tblStudent Where StuName 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 Distinct Student name=StuName,Number of people with the same name=(Select Count(*) From tblStudent s2 Where s2.StuName=st.StuName) From tblStudent st
  Where (Select Count(*) From tblStudent s2 Where s2.StuName=st.StuName)>=2

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

 Select * From tblStudent Where 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, it is arranged in descending order according to the course number

 Select curriculum ID=CourseId,Course name=CourseName,Average score=(Select Avg(Score) From tblScore Where CourseId=cs.CourseId)
  From tblCourse cs
  Order by Average score,CourseId Desc

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

 Select Student number=StuId,full name=StuName,Average score=(Select Avg(Score) From tblScore Where StuId=st.StuId) From tblStudent st
  Where (Select Avg(Score) From tblScore Where StuId=st.StuId)>85

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

 Select full name=StuName,fraction=Score From tblScore sc
  Inner Join tblStudent st On sc.StuId=st.StuId
  Inner Join tblCourse cs On sc.CourseId=cs.CourseId
  Where CourseName='database' And Score<60

--35. Inquire about the course selection of all students;

 Select Student number=StuId,Number of courses selected=(Select Count(*) From (Select Distinct CourseId From tblScore Where StuId=st.StuId) as tmp)
  From tblStudent st

 Select distinct full name=StuName,Elective courses=CourseName From tblScore sc
  Inner Join tblStudent st On sc.StuId=st.StuId
  Inner Join tblCourse cs On sc.CourseId=cs.CourseId

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

 Select full name=StuName,Course name=CourseName,fraction=Score From tblScore sc
  Inner Join tblStudent st On sc.StuId=st.StuId
  Inner Join tblCourse cs On sc.CourseId=cs.CourseId
  Where Score>=70

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

 Select * From tblScore Where Score<60 order by CourseId Desc

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

 Select StuId,StuName From tblStudent 
  Where StuId in 
   (Select StuId From tblScore Where CourseId='003' And Score>=80) 

--39. Number of students taking courses

 Select Number of students taking courses=Count(*) From tblStudent st Where StuId IN (Select StuID From tblScore) 

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

Select CourseId,CourseName
 ,The highest student in the subject=(Select StuName From tblStudent Where StuId in (Select Top 1 StuID From tblScore Where CourseId=cs.CourseId Order by Score Desc))
 ,achievement=(Select Top 1 Score From tblScore Where CourseId=cs.CourseId Order by Score Desc)
 From tblCourse cs Inner Join tblTeacher tc ON cs.TeaId=tc.TeaId
 Where TeaName='Ye Ping'

--41. Query each course and the corresponding number of electives

 Select curriculum ID=CourseId,Number of electives=(Select Count(*) From (Select Distinct StuId From tblScore Where CourseId=cs.CourseId) as tmp)
  From tblCourse cs

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

 Select Student number=StuId, Course number=CourseId, achievement=Score From tblScore sc 
  Where Exists (Select * From tblScore Where Score=sc.Score And StuId=sc.StuId And CourseId <>sc.CourseId)
  Order by Student number,achievement

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

 Select Course number=CourseId,
  1st place=(Select Top 1 StuId From tblScore Where CourseId=cs.CourseId Order by Score DESC),
  2nd place=(Select Top 1 StuID From (Select Top 2 StuId,Score From tblScore Where CourseId=cs.CourseId Order by Score DESC) as tmp Order by Score)
  From tblCourse cs

--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 curriculum ID=CourseId,Number of electives=(Select Count(*) From (Select Distinct StuId From tblScore Where CourseId=cs.CourseId) as tmp)
  From tblCourse cs 
  Where (Select Count(*) From (Select Distinct StuId From tblScore Where CourseId=cs.CourseId) as tmp)>=10
  Order by Number of electives DESC, curriculum ID  

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

 Select StuId from tblScore Group by Stuid having Count(*)>=2 --This method can be used when there is no duplicate course data

 --Use this method when there are repeated courses(Such as make-up examination)
 Select StuId from tblStudent st Where
  (Select Count(*) From (Select Distinct CourseId From tblScore Where StuId=st.StuId) as tmp)>=2

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

 Select CourseId,CourseName From tblCourse cs
  Where Not Exists
  (
   Select * From tblStudent Where StuId Not In     --Are there any students who have not studied this course
   (Select StuId From tblScore Where CourseId=cs.CourseId)
  )

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

 Select StuId,StuName From tblStudent
  Where StuId Not In
   (
    Select StuID From tblScore sc
     Inner Join tblCourse cu ON sc.CourseId=cu.CourseId
     Inner Join tblTeacher tc ON cu.TeaId=tc.TeaId    
      Where tc.TeaName='Ye Ping'
   ) 

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

 Select StuID as Student number,Avg(Score) as Average score From tblScore sc
  Where (Select Count(*) From tblScore s1 Where s1.StuId=sc.StuId And Score<60)>=2
  Group By StuId

--49. Retrieve the student number (ok) of students whose score of "004" course is less than 60 in descending order of score

 Select StuID,Score From tblScore Where CourseId='004' And Score<60 Order by Score Desc

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

 Delete From SC Where StuId='1002' And CourseId='001'

----------------------SC To delete a temporary data table
Select * INTO SC From tblScore
Select * from sc Where stuId='1018'
Insert Sc(Stuid,courseId,Score) Select StuID,'009',74 From tblStudent

/*********************************  Create database, create table, create constraint, insert test data******************************************/
Use master
go
if db_id('MySchool') is not null
 Drop Database MySchool
Create Database MySchool
go
Use MySchool
go
create table tblStudent
(
 StuId varchar(5) primary key,
 StuName nvarchar(10) not null,
 StuAge int,
 StuSex nchar(1) not null
)
create table tblTeacher
(
 TeaId varchar(3) primary key, 
 TeaName varchar(10) not null
)
create table tblCourse
(
 CourseId varchar(3) primary key,
 CourseName nvarchar(20) not null, 
 TeaId varchar(3) not null foreign key references tblTeacher(teaId)
)
create table tblScore
(
 StuId varchar(5) not null foreign key references tblStudent(stuId),
 CourseId varchar(3) not null foreign key references tblCourse(CourseId),
 Score float
)
----------------------------------Table structure----------------------------------------------------
--Student list tblStudent(number StuId,full name Stuname,Age Stuage,Gender Stusex)
--Class Schedule Card tblCourse(Course number CourseId,Course name CourseName,Teacher number TeaId)
--Transcript tblScore(Student number StuId,Course number CourseId,achievement Score)
--Teacher table tblTeacher(Teacher number TeaId,full name TeaName)
--------------------------------insert data-------------------------------------------------
insert into tblStudent
select '1000','zhang wuji',18,'male' union
select '1001','Zhou Zhiruo',19,'female' union
select '1002','Guo Yang',19,'male' union
select '1003','Zhao Min',18,'female' union
select '1004','little dragon maiden',17,'female' union
select '1005','Zhang Sanfeng',18,'male' union
select '1006','linghu chong',19,'male' union
select '1007','Ren yingying',20,'female' union
select '1008','Yue Lingshan',19,'female' union
select '1009','Wei Xiaobao',18,'male' union
select '1010','Kang min',17,'female' union
select '1011','Xiao Feng',19,'male' union
select '1012','Huang Rong',18,'female' union
select '1013','Guo Jing',19,'male' union
select '1014','Zhou Botong',19,'male' union
select '1015','Yinggu',20,'female' union
select '1016','Li Qiushui',21,'female' union
select '1017','pharmacist hwang',18,'male' union
select '1018','Li Mochou',18,'female' union
select '1019','Feng mofeng',17,'male' union
select '1020','wang chongyang',17,'male' union
select '1021','Guo Xiang',18,'female' 
go

insert  into tblTeacher
select '001','Yao Ming' union
select '002','Ye Ping' union
select '003','Ye Kai' union
select '004','Meng Xinghun' union
select '005','Seek defeat alone' union
select '006','Qiu Qianren' union
select '007','Qiu Qianchi' union
select '008','Zhao Zhijing' union
select '009','A Zi' union
select '010','Guo Furong' union
select '011','Tong Xiangyu' union
select '012','Bai zhantang' union
select '013','LV Qinghou' union
select '014','Li Dazui' union
select '015','Flawless flowers' union
select '016','Jin Buhuan' union
select '017','Jordan'
go

insert into tblCourse
select '001','business management','002' union
select '002','Marx','008' union
select '003','UML','006' union
select '004','database','007' union
select '005','logical circuit','006' union
select '006','English','003' union
select '007','electronic circuit ','005' union
select '008','Introduction to Mao Zedong Thought','004' union
select '009','History of Western Philosophy','012' union
select '010','linear algebra','017' union
select '011','Fundamentals of computer','013' union
select '012','AUTO CAD drafting','015' union
select '013','Graphic Artist Designer','011' union
select '014','Flash comic','001' union
select '015','[Java](http://lib.csdn.net/base/javaee "Java EE knowledge base") development ',' 009 'Union
select '016','C#Basic ',' 002 'Union
select '017','Oracl Database principle','010'
go

insert into tblScore
select '1001','003',90 union
select '1001','002',87 union
select '1001','001',96 union
select '1001','010',85 union
select '1002','003',70 union
select '1002','002',87 union
select '1002','001',42 union
select '1002','010',65 union
select '1003','006',78 union
select '1003','003',70 union
select '1003','005',70 union
select '1003','001',32 union
select '1003','010',85 union
select '1003','011',21 union
select '1004','007',90 union
select '1004','002',87 union
select '1005','001',23 union
select '1006','015',85 union
select '1006','006',46 union
select '1006','003',59 union
select '1006','004',70 union
select '1006','001',99 union
select '1007','011',85 union
select '1007','006',84 union
select '1007','003',72 union
select '1007','002',87 union
select '1008','001',94 union
select '1008','012',85 union
select '1008','006',32 union
select '1009','003',90 union
select '1009','002',82 union
select '1009','001',96 union
select '1009','010',82 union
select '1009','008',92 union
select '1010','003',90 union
select '1010','002',87 union
select '1010','001',96 union

select '1011','009',24 union
select '1011','009',25 union

select '1012','003',30 union
select '1013','002',37 union
select '1013','001',16 union
select '1013','007',55 union
select '1013','006',42 union
select '1013','012',34 union
select '1000','004',16 union
select '1002','004',55 union
select '1004','004',42 union
select '1008','004',34 union
select '1013','016',86 union
select '1013','016',44 union
select '1000','014',75 union
select '1002','016',100 union
select '1004','001',83 union
select '1008','013',97
go

Posted by mshen on Wed, 04 May 2022 15:22:09 +0300