努力很久只为获得别人尊重的眼光。
		——我是，董宏宇，我为自己代言。
		 技术交流QQ：1358506549（请注明你的来意）



use xsx;
CREATE TABLE Course(
       Cno char(3) NOT NULL ,
       Cname varchar (50)  NULL ,
       Cpno char (3) NULL ,
       Ccredit tinyint NULL
);
CREATE TABLE SC(
       Sno char(5) NOT NULL ,
       Cno char(3)  NOT NULL ,
       Grade tinyint NULL,
       primary key(Sno,Cno)
) ;
CREATE TABLE Student (
       Sno char(5)  NOT NULL primary key,
       Sname char(10)  NULL ,
       Ssex char(2) NULL ,
       Sage int NULL ,
       Sdept char(4) NULL
) ;


Insert  into student   (Sno,Sname,Ssex,Sage,Sdept)      Values('95001','李勇','男',20,'CS');
Insert  into student   (Sno,Sname,Ssex,Sage,Sdept)      Values('95002','刘晨','女',19,'IS');
Insert  into student   (Sno,Sname,Ssex,Sage,Sdept)      Values('95003','王敏','女',18,'MA');
Insert  into student   (Sno,Sname,Ssex,Sage,Sdept)      Values('95004','张立','男',21,'IS');
Insert  into student   (Sno,Sname,Ssex,Sage,Sdept)      Values('95005','林燕芳','女',18,'IS');
Insert  into student   (Sno,Sname,Ssex,Sage,Sdept)      Values('95006','林燕虹','女',17,'IS');
Insert  into student   (Sno,Sname,Ssex,Sage,Sdept)      Values('95007','欧阳锋','男',19,'MA');
Insert  into student   (Sno,Sname,Ssex,Sage,Sdept)      Values('95008','欧阳木兰','女',16,'CS');
 
 
 
Insert  into course   (Cno,Cname,Cpno,Ccredit)      Values('1','数据库','5',4);
Insert  into course   (Cno,Cname,Cpno,Ccredit)      Values('2','数学',NULL,2);
Insert  into course   (Cno,Cname,Cpno,Ccredit)      Values('3','信息系统','1',4);
Insert  into course   (Cno,Cname,Cpno,Ccredit)      Values('4','操作系统','6',3);
Insert  into course   (Cno,Cname,Cpno,Ccredit)      Values('5','数据结构','6',3);
Insert  into course   (Cno,Cname,Cpno,Ccredit)      Values('6','数据处理', '2' ,2);
Insert  into course   (Cno,Cname,Cpno,Ccredit)      Values('7','PASCAL语言','6',4);
Insert  into course   (Cno,Cname,Cpno,Ccredit)      Values('8','DB_DESIGN','6',3);
 
 
Insert  into sc   (Sno,Cno,Grade)      Values('95001','1',92);
Insert  into sc   (Sno,Cno,Grade)      Values('95001','2',85);
Insert  into sc   (Sno,Cno,Grade)      Values('95001','3',88);
Insert  into sc   (Sno,Cno,Grade)      Values('95001','4',23);
Insert  into sc   (Sno,Cno,Grade)      Values('95001','5',34);
Insert  into sc   (Sno,Cno,Grade)      Values('95001','6',56);
Insert  into sc   (Sno,Cno,Grade)      Values('95001','7',86);
Insert  into sc   (Sno,Cno,Grade)      Values('95001','8',88);
Insert  into sc   (Sno,Cno,Grade)      Values('95002','2',90);

Insert  into sc   (Sno,Cno,Grade)      Values('95002','3',80);
Insert  into sc   (Sno,Cno,Grade)      Values('95003','1',50);
Insert  into sc   (Sno,Cno,Grade)      Values('95003','3',98);
Insert  into sc   (Sno,Cno,Grade)      Values('95005','3',null);


//查询全体学生的学号和姓名
select sno,sname from student;

//查询全体学生的姓名、学号和所在系
select sno,sname,sdept from student;

//查询全体学生的详细记录
select * from student;

//查询全体学生的姓名及其出生年份
select sname,sage from student;

//查询全体学生姓名、出生年份和所在系，要求用小写字母表示所有系名
select sname,sage,lower(sdept) from student;

//查询选修了课程的学生学号
select distinct  student.sno from student,sc where student.Sno=sc.Sno;

//查询计算机系(CS)所有学生的名单
select sname from student where sdept="cs";

//查询所有年龄在20以下学生的姓名和年龄
select sname,sage from student where sage<20;

//查询考试成绩有不及格的学生的学号
select sno from sc where grade<60;




// 查询年龄在20-23 （包括20和23）之间的学生的姓名、系别和年龄
select sname,sdept,sage from student where sage<=23 and sage>=20;

//查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别
select sname,ssex from student where sdept="is" or sdept="ma" or sdept='cs';

//查询学号为95001的学生的详细情况
select * from student where sno=95001;

//查询所有姓林的学生的姓名、学号和性别
select sname,sno,ssex from student where sname like "林%";

//查询姓“欧阳”且全名为三个汉字的学生的姓名
select sname from student where sname like "欧阳_";

//查询名字中第二个字为“燕”字的学生姓名和学号
select sname,sno from student where sname like "_燕%";


//查询所有不姓“刘”的学生的姓名
select sname from student where sname not like &qu