db

in 微语 with 0 comment
create database school;
use school;
create table student(
    sno int primary key,-- 学号
    sname varchar(10),-- 名字
    ssex varchar(3),-- 性别
    sage int ,-- 年龄
    sdept varchar(5)-- 院系
);
create table course(
    cno int primary key,-- 课程号
    cname varchar(8),-- 课程名
    cpno int,-- 先行课
    ccredit int-- 学分
);
create table sc(
    sno int,-- 学号
    cno int,-- 课程号
    grade int-- 成绩
);
insert into student values('10001','叶斌聪','男',21,'CS');
insert into student values('10002','张六伟所','男',19,'MA');
insert into student values('10003','傅贺强','男',22,'CS');
insert into student values('10004','刘伟','男',20,'CS');
insert into student values('10005','张三阳伟','男',20,'MA');
insert into student values('10006','燕琳则','女',20,'CS');
insert into student values('10007','刘琳','女',20,'MA');
insert into student values('10008','苏珊','女',21,'CS');
insert into student values('10009','叶伟','男',22,'MA');
insert into student values('10010','张三','男',21,'CS');
insert into course values(1,'数据库',5,4);
insert into course values(2,'数学','' ,2);
insert into course values(3,'操作系统',6,3);
insert into course values(4,'数据结构',7,4);
insert into course values(5,'数据处理','' ,2);
insert into course values(6,'C语言',6,4);
insert into sc values('10001',1,80);
insert into sc values('10001',2,85);
insert into sc values('10001',3,88);
insert into sc values('10001',4,78);
insert into sc values('10001',5,80);
insert into sc values('10001',6,92);
insert into sc values('10002',1,77);
insert into sc values('10002',2,60);
insert into sc values('10002',3,90);
insert into sc values('10002',4,80);
insert into sc values('10002',5,60);
insert into sc values('10002',6,55);
insert into sc values('10003',1,80);
insert into sc values('10003',2,90);
insert into sc values('10003',3,80);
insert into sc values('10010',1,99);
insert into sc values('10010',2,60);
insert into sc values('10010',3,66);
insert into sc values('10010',4,77);
insert into sc values('10010',5,88);

-- easy query
-- 1
select * from course;
-- 2
select sname,2021-sage Birthday,lower(sdept) Department from student;
-- 3
select distinct cno from sc;
-- 4
select sno,cno,grade from sc where grade>=80 and grade<=100;
-- 5
select sno from student where sdept not in ('CS','MA');
-- 6
select sno,sname from student where sname like '%伟%';
-- 7
select sname  from student where ssex='男' and sname like '__阳%' and sname like '张%' and len(sname)=4;
-- 8
select count(*) from sc;
-- 9
select sno,sum(grade) from sc where sum(grade)<300 group by sno;
-- 10
select count(*) from sc where count(*) >=20 group by cno;
Responses