数据库原理及安全（二）

根据上节课建立的表以及插入的数据来查询相关信息

SELECT * FROM student;
SELECT Sno,Sname FROM student;
SELECT Sno FROM sc;
SELECT DISTINCT Sno FROM sc;
SELECT * FROM student WHERE Sdept='CS';
SELECT * FROM sc WHERE Cno=1;
SELECT Sno,Sname FROM student WHERE Ssex='男';
SELECT Cno FROM sc WHERE Grade <60;
SELECT * FROM sc WHERE Grade BETWEEN 95 AND 99;
SELECT * FROM sc WHERE Grade NOT BETWEEN 95 AND 99;
SELECT  Sname,Ssex FROM student WHERE Sage=18 OR Sage=20 OR Sage=24;
SELECT  Sname,Ssex FROM student WHERE Sage!=18 AND Sage!=20 AND Sage!=24;
SELECT Cno,Cname,Ccredit FROM course WHERE Cname LIKE '_据%';

INSERT course
VALUES('8','A_C','5',2);
SELECT Cno,Ccredit FROM course WHERE Cname LIKE 'A\_C'; 

SELECT Cno,Cname FROM course WHERE Cpno IS NULL;
SELECT Sno,Sname,Sage,Sdept FROM student ORDER BY Sdept desc, Sage asc;
SELECT COUNT(*) FROM course;
SELECT COUNT(DISTINCT Cno) FROM sc;
SELECT MIN(Sage) FROM student;
SELECT MIN(Sage) FROM student WHERE Ssex='男';
SELECT MIN(Sage) FROM student WHERE Ssex='男' AND Sdept='CS';

INSERT student
VALUES('201295001','何止','男',20,'CS');

INSERT sc
VALUES('201295001','1',80);
INSERT sc
VALUES('201295001','2',90);
INSERT sc
VALUES('201295001','3',100);

SELECT AVG(Grade) FROM sc WHERE Sno='201295001';
SELECT MAX(Grade) FROM sc WHERE Sno='201295001';

SELECT Sno,COUNT(Sno) FROM sc GROUP BY Sno;
SELECT * FROM student WHERE Sdept IN("CS","MA") AND SUBSTR(Sname,1,1)="刘";

INSERT sc
VALUES('201295002','5',null);

SELECT Sno,Cno FROM sc WHERE Grade is NULL;