#1
SELECT sno,sname,sex from students where sdept='计算机学院';
#2
SELECT * from students;
#3
SELECT sno,sname,sphone from students where saddress='上海';
#4
SELECT sno,sname,sphone,sbirthday from students;
#5
SELECT DISTINCT cname,credit from courses;
#6
SELECT cname,credit from courses LIMIT 3;
#7
SELECT grade from score WHERE cno='c07';
#8
SELECT sbirthday from students where sbirthday>'1982-08-08';
#9
SELECT cname,cperiod-6 from courses where ctype='必修';
#10
SELECT sname,sbirthday from students where saddress='上海';
#11
SELECT sname,sphone from students where saddress!='上海' && saddress!='北京';
#12
SELECT students.sno,courses.cname from students,courses,score WHERE
score.grade>=80&&score.grade<=90;
#13
SELECT sname,sphone from students WHERE sphoto is NULL && sbirthday BETWEEN '1988-01-01' AND '1990-12-31';
#14
SELECT cname,ctype from courses WHERE credit BETWEEN 2 AND 5;
#15
SELECT * from courses WHERE credit BETWEEN 3 AND 5 && cperiod not BETWEEN 68 AND 102;
#16
SELECT sname from students WHERE sname LIKE('王%');
#17
SELECT sname from students WHERE sname LIKE('%小%');
#18
SELECT sname from students WHERE sname LIKE('李小%') || sname LIKE('李晓%') ;
#19
SELECT sname from students WHERE sphoto is not null && sex='女';
#20
SELECT * from students WHERE sex='男' order by sbirthday ASC;
#21
SELECT grade from score WHERE sno='2015002' ORDER BY grade DESC;
#22
select sname,sbirthday from students ORDER BY sbirthday ASC LIMIT 3;
#21
SELECT cname,cperiod,credit from courses where ctype='必修' ORDER BY credit DESC,cperiod ASC;
#24
select COUNT(*) from students;
#25
SELECT COUNT(*) from courses ORDER BY ctype;
#26
SELECT sex,COUNT(*) from students GROUP BY sex;
#27
SELECT cno,COUNT(*) from score GROUP BY sno ORDER BY cno DESC;
#28
SELECT sno,MAX(grade),MIN(grade),AVG(grade) from score GROUP BY sno ;
#29
SELECT sdept,COUNT(*) from students GROUP BY sdept HAVING COUNT(*)>3;
#30
SELECT sno,AVG(grade) from score WHERE grade > 90 GROUP BY sno;