SQL database query build table Insert Collection-Continued update

Create a table

Drop Tablestudent;DROP TableCourse;DROP TableSC;CREATE TABLEStudent (Sidinteger PRIMARY KEYAutoIncrement,--IDSnamenvarchar( +),--Student NameAgeinteger,--AgeSexnvarchar(8)--Sex);Create TableCourse--Timetable(CIDinteger PRIMARY KEYAutoIncrement,--IDCNamenvarchar( +)--Course Name);Create TableSc--Score Table(SCIDinteger PRIMARY KEYAutoIncrement,--IDSidinteger,--Student Table IDCidinteger,--Curriculum IDCoreinteger--Achievements);INSERT  intoStudent (Sname,age,sex)VALUES ('Fong Fong', +,'female'),('Tingting', +,'female'),('Rui Bao', +,'female'),('not known', +,'female');Insert  intoCourse (CNAME)VALUES('language'),('Mathematics');INSERT  intoSC (sid,cid,core)VALUES(1,1, the),(1,2, -),(2,1, About),(2,2, About);

Various inserts

INSERT  into Student (Sid,sname,age,sex) VALUES (6,' disorder ',+,' female ')

Various inquiries

--query for IDs that are disjoint from the increment IDSelectSid from(SelectSid fromStudentOrder  bySidASC) swhere  not exists(Select 1  fromStudentwhereSid=S.sid-1);--connection query, Fong Fong's math scoreSelectS.sname,sc.core fromStudent S Left JOINSc onS.sid=Sc.sid Left JOINCourse C onC.cid=Sc.cidWHERES.sname= 'Fong Fong';--Fuzzy Query the student whose name is TingSelect *  fromStudentWHERESname like '% ting%';--query without connection query Tingting's math scoreSelectS.sid,s.sname,scc.core from(SELECTSid,sname fromStudent) s, (SELECTSid,core,cid fromSC) SCCWHERESname= 'Tingting'  andScc.sid=S.sid andScc.cid= 2 ; --Easy multi-table query language scores of students outside Fong FongSelectS.sname,c.cname,sc.core fromStudent S,sc,course CWHERES.sname is  not 'Fong Fong'  andS.sid=Sc.sid andSc.cid=C.cid andC.cname= 'language' GROUP  bySc.scid; --students who have higher mathematics than ChineseSELECTS.sid,s.sname,sc1.core from(SELECTSid,core fromScWHERECid= '2') SC2, (SELECTSid,core fromScWHERECid= '1') SC1, (SELECTSid,sname fromStudent) sWHERES.sid=Sc1.sid andSc1.sid=Sc2.sid andSc1.core<Sc2.core;--other students who are checking score 100Select *  fromStudentWHERESname not inch(SELECTSname fromStudent S,SCWHERES.sid=Sc.sid andSc.core=  -);--number of students who have scored more than 60Select Count(distinct(sname)) fromStudent S,SCWHERES.sid=Sc.sid andSc.core> -;--query for students with average value greater than 70SelectS.sname,avg(Sc.core) fromStudent S,SCWHERES.sid=Sc.sidGROUP  bySc.sid having avg(Sc.core)> -;

SQL database query build table Insert Collection-Continued update