SQL语句的经典语句50例--MySQL版本的答案

--table Student
CREATETABLE Student(
SVARCHAR(10),
SnameVARCHAR(10),
SageDATE,
SsexVARCHAR(10));
--tableStudent: insert rows
INSERTINTO Student (S, Sname, Sage, Ssex) VALUES ('01', 'frank1', '1991-01-01','Male');
INSERTINTO Student VALUES ('02', 'frank2', '1992-07-07', 'Female');
INSERTINTO Student VALUES ('03', 'frank3', '1993-07-07', 'Male');
INSERTINTO Student VALUES ('04', 'frank4', '1994-07-07', 'Male');


--table Course
CREATETABLE Course(
CVARCHAR(10),
CnameVARCHAR(10),
TVARCHAR(10));
--tableCourse: insert rows
INSERTINTO Course VALUES ('01', 'Chinese', '01');
INSERTINTO Course VALUES ('02', 'Math', '02');
INSERTINTO Course VALUES ('03', 'English', 'TeacherC');
UPDATECourse SET T = '03' WHERE Cname = 'English';


--table Teacher
CREATETABLE Teacher(
TVARCHAR(10),
TnameVARCHAR(10));
--tableTeacher: insert rows
INSERTINTO Teacher VALUES ('01', 'TeacherA');
INSERTINTO Teacher VALUES ('02', 'TeacherB');
INSERTINTO Teacher VALUES ('03', 'TeacherC');




--table SC
CREATETABLE SC(
SVARCHAR(10),
CVARCHAR(10),
scoreINT);
--tableSC: insert rows
INSERTINTO SC VALUES ('01', '01', 81);
INSERTINTO SC VALUES ('01', '02', 41);
INSERTINTO SC VALUES ('01', '03', 71);
INSERTINTO SC VALUES ('02', '01', 82);
INSERTINTO SC VALUES ('02', '02', 42);
INSERTINTO SC VALUES ('02', '03', 72);
INSERTINTO SC VALUES ('03', '01', 83);
INSERTINTO SC VALUES ('03', '02', 73);
INSERTINTO SC VALUES ('03', '03', 63);
INSERTINTO SC VALUES ('04', '01', 81);


--1 student and score whose SC 01 > SC 02 
--Onetable can be as two tables      ||              IFNULL
Selecta.*, b.score, c.score from Student a , SC b, SC c where a.S = b.S and c.S = b.Sand b.C = '01' and c.C = '02' and b.score > IFNULL(c.score, 0); 


--3 student whose average score >=60 
--Groupby &#