数据库第一次作业

CREATE TABLE S(SNO CHAR(8) PRIMARY KEY,SNAME VARCHAR(20) UNIQUE,SSEX CHAR(6),SBIRTHDATE DATE,SMAJOR VARCHAR(40));

insert into S values('20180001','李勇','男',20000308,'信息安全');
insert into S values('20180002','刘晨','女',19990901,'计算机科学与技术');
insert into S values('20180003','王敏','女',20010801,'计算机科学与技术');
insert into S values('20180004','张立','男',20000108,'计算机科学与技术');
insert into S values('20180005','陈新奇','男',20011101,'信息管理与信息系统');
insert into S values('20180006','赵明','男',20000612,'数据科学与大数据技术');
insert into S values('20180007','王佳佳','女',20011207,'数据科学与大数据技术');

CREATE TABLE C(CNO CHAR(5) PRIMARY KEY,CNAME VARCHAR(40) NOT NULL,CCREDIT INTEGER,CPNO CHAR(5));

insert into C values('81001','程序设计基础与c语言',4,'');
insert into C values('81002','数据结构',4,'81001');
insert into C values('81003','数据库系统概论',4,'81002');
insert into C values('81004','信息系统概论',4,'81003');
insert into C values('81005','操作系统',4,'81001');
insert into C values('81006','python语言',3,'81002');
insert into C values('81007','离散数学',4,'');
insert into C values('81008','大数据技术概论',4,'81003');

CREATE TABLE SC(SNO CHAR(8),CNO CHAR(5),GRADE SMALLINT,PRIMARY KEY(SNO,CNO),FOREIGN KEY(SNO) REFERENCES S(SNO),FOREIGN KEY(CNO) REFERENCES C(CNO));

insert into SC values('20180001','81001',85);
insert into SC values('20180001','81002',96);
insert into SC values('20180001','81003',87);
insert into SC values('20180002','81001',80);
insert into SC values('20180002','81002',98);
insert into SC values('20180002','81003',71);
insert into SC values('20180003','81001',81);
insert into SC values('20180003','81002',76);
insert into SC values('20180004','81001',56);
insert into SC values('20180004','81002',97);
insert into SC values('20180005','81003',68);

SELECT * FROM S WHERE SMAJOR = '计算机科学与技术';

SELECT SNO,SNAME,SBIRTHDATE FROM S WHERE SSEX='男'AND SNAME LIKE '陈%';

SELECT CNO, CNAME, CCREDIT FROM C WHERE CPNO IS NOT NULL ORDER BY CCREDIT DESC;

SELECT COUNT(*),MIN(SBIRTHDATE) FROM S WHERE SSEX='男';

SELECT SNO, AVG(GRADE) FROM SC WHERE GRADE < 60 GROUP BY SNO ORDER BY AVG(GRADE) DESC;