--第一部分oracle
--1.登陆sys账户的语句。
sqlplus system/workit@oracle10
--创建一个新的用户epointexam/123456。
--登录密码全是数字的话，会报错误，加双引号就不会出现问题了
create user epointexam
identified by "123456"
default tablespace users
temporary tablespace temp;
--更改epointexam的密码为exam123
alter user epointexam
identified by exam123;
--提交
commit;
--解锁scott用户
alter user scott account unlock;
--切换到用户scott
conn scott/tiger@oracle10;
-- mysql部分
-- 表一：学生表
create table student(
stuname varchar(50) not null comment '姓名',
pinyin varchar(50) not null comment '拼音',
sex varchar(50) not null comment '性别',
birthday date not null comment '生日',
location varchar(50) not null comment '户籍',
email varchar(100) not null comment '电子邮箱',
score int(5) not null comment '考试分数',
collageid varchar(50) not null comment '录取院校'
);
-- 表二：学校表
create table collage(
collageid varchar(50) not null comment '学校编号',
collagename varchar(50) not null comment '学校编号',
location varchar(50) not null comment '所在地',
allowance varchar(50) not null comment '学校补助',
campus int(5) not null comment '校区数量',
professor int(8) not null comment '教授数量'
);
-- 表三：学科表
create table major(
collageid varchar(50) not null comment '学校编号',
major varchar(50) not null comment '学校编号',
majorcategory varchar(50) not null comment '学科类别',
plannedpeople int(8) not null comment '计划人数',
years int(5) not null comment '学制',
keymajor varchar(50) not null comment '重点学科'
);
-- 表四：批次表
create table batchinfo(
batchnum int(8) not null comment '录取批次',
lowscore int(5) not null comment '最低分',
highscore int(5) not null comment '最高分'
);
-- 查询语句
-- 7.查询学生信息表，列出拼音中至少包含2个字母a的学生信息。
select *from student
where pinyin like '%a%a%';
-- 8.查询所有姓名为三个汉字的学生信息。
-- 一个汉字是三个字节
select *from student
where length(stuname)=9;
-- 9.查询考试成绩介于(570,600)∪[620,638]的所有学生信息。
select *from student
where score > 570 and score <600
or
score >= 620 and score <= 638;
-- 10.查询所有在1999年出生的学生信息，按考试分数逆序排列。
select *from student
where year(birthday)='1999'
order by score desc;
-- 11.查询拼音以c开头g结尾并且长度大于10的学生信息。
select *from student
where pinyin like 'c%g' and length(pinyin) >10;
-- 12.查询与周兵同一天过生日的学生信息。
-- 第一步：先查出周兵的生日
select birthday from student
where stuname = '周兵';
-- 第二步：
select *from student
where birthday = (
select birthday from student
where stuname = '周兵'
) and stuname != '周兵';
-- 13.查询同年同月同日出生的所有学生的信息。
select a.*,b.* from student a,student b
where a.birthday = b.birthday and a.stuname != b.stuname;
-- 14.按户籍分组统计学生信息，列出学生户籍及对应学生数量，并按照数量逆序排列。
select location,count(stuname) num
from student
group by location
order by num desc;
-- 15.查询年龄超过23岁的学生信息，列出学生姓名、生日、年龄。
select stuname,birthday,round(datediff(now(),birthday)/365) as age
from student
where datediff(now(),birthday)/365 >= 23;
-- 16.查询有谁在自己的电子邮箱中加入了自己的出生年月日。
-- 1、先把日期转化一下格式
select DATE_FORMAT(birthday,'%Y%m%d')
from student;
-- 2、实现
select *from student
where email like concat('%',DATE_FORMAT(birthday,'%Y%m%d'),'%');
-- 17.查询考试分数从高到低排第3到第9的学生信息。
select *from student
order by score desc
limit 3,7;
-- 18.写出上题的oracle语句。
-- 学生表oracle版本
create table student(
stuname varchar(50) not null,
pinyin varchar(50) not null,
sex varchar(50) not null,
birthday date not null,
location varchar(50) not null,
email varchar(100) not null,
score number(5) not null,
collageid varchar(50) not null
);
-- oracle版本的插入语句
insert into student values ('周东宇','zhoudongyu','男',to_date('1999-09-09','yyyy-MM-dd'),'安徽','zhou0909@sina.cn',609,1890);
insert into student values ('蔡飞','caifei','女',to_date('2000-08-12','yyyy-MM-dd'),'浙江','caifei@163.com',610,1890);
insert into student values ('王帅','wangshuai','男',to_date('1999-04-30','yyyy-MM-dd'),'安徽','wangshuai@hotmail.com',612,1765);
insert into student values ('谢素珍','xiesuzheng','女',to_date('1999-11-11','yyyy-MM-dd'),'广东','xiesuzheng@qq.com',616,1765);
insert into student values ('陆江','lujiang','男',to_date('1998-11-10','yyyy-MM-dd'),'江苏','lj19981110@hotmail.com',638,1765);
insert into student values ('王宇哲','wangyuzhe','男',to_date('1998-12-22','yyyy-MM-dd'),'江苏','wangyuzhe@qq.com',620,1660);
insert into student values ('孙虎','sunhu','男',to_date('1998-07-12','yyyy-MM-dd'),'江苏','sunhu@qqaa.com',622,1643);
insert into student values ('周兵','zhoubing','男',to_date('1999-07-12','yyyy-MM-dd'),'安徽','zhoubing@qq.com',600,1392);
insert into student values ('张明','zhangming','男',to_date('1999-02-01','yyyy-MM-dd'),'江苏','happy163@qq.com',635,1333);
insert into student values ('王华成','wanghuacheng','男',to_date('2000-03-16','yyyy-MM-dd'),'浙江','whc20000316@hotmail.com',620,1333);
insert into student values ('陈小明','chenxiaoming','男',to_date('1998-03-15','yyyy-MM-dd'),'江苏','cxm19980315@qq.com',640,1111);
insert into student values ('李东','lidong','男',to_date('1998-05-24','yyyy-MM-dd'),'江苏','lidong@163.com',642,1111);
insert into student values ('周美娟','zhoumeijuan','女',to_date('1999-12-01','yyyy-MM-dd'),'河北','zhoumeijuan@126.com',625,1049);
insert into student values ('夏小天','xiaxiaotian','女',to_date('1999-06-27','yyyy-MM-dd'),'浙江','xxt0627@163.com',604,1049);
insert into student values ('丁海良','dinghailiang','男',to_date('1998-09-08','yyyy-MM-dd'),'浙江','ding19980908@hotmail.com',618,1049);
insert into student values ('丁丽丽','dinglili','女',to_date('1998-09-08','yyyy-MM-dd'),'山东','dinglili@qq.com',633,1029);
insert into student values ('陈梁','chenliang','男',to_date('1999-11-12','yyyy-MM-dd'),'江苏','chenliang@qqbb.com',615,1023);
insert into student values ('李哲','lizhe','男',to_date('1999-01-02','yyyy-MM-dd'),'山东','lizhe@sina.cn',617,1023);
insert into student values ('李华','lihua','男',to_date('1998-03-06','yyyy-MM-dd'),'浙江','huali@163.com',582,1005);
INSERT INTO student values ('王冬梅','wangdongmei','女',to_date('1998-09-20','yyyy-MM-dd'),'浙江','wdm@qq.com',578,1005);
-- 查询语句
select *from
(
select s.*,row_number() over(order by s.score desc) as rank from student s
)where rank > =3 and rank <=9;
-- 19.已知邮箱格式为“邮箱名称@域名.com/.cn”，请查询邮箱名称的字符长度（重命名为len），电子邮箱（要求全部转为大写字母）。
-- 定位@的位置，要用到locate(str,substr):返回子串substr在字符串str中的第一个出现的位置。
select upper(email),locate('@',email)-1 as enamelength from student;
-- 20.查询所有学校编号、学校名称，同时将结果里学校名称中的南京替换为北京。
-- replace(s，s1，s2) 使⽤字符串s2替换字符串s中所有的字符串s1。
select collageid,replace(collagename,'南京','北京') from collage;
-- 21.查询拥有分校区数量最多的学校信息。
-- 只有一条记录
select *from collage
order by campus desc
limit 1;
-- 有多条记录(最佳的)
select *from collage
where campus = (
select max(campus)
from collage
);
-- 22.查询所有考入本省份学校的学生姓名、考试成绩、学校名称、所在地，并按照分数升序排列。
select s.stuname,s.score,c.collagename,c.location
from student s,collage c
where s.collageid = c.collageid and s.location = c.location
order by score asc;
-- 23.查询哪一个学校拥有2人及以上个同学能拿到学费补助，列出学生数量（stucount）、学校名称。
-- 1、先查出有学校有补助的记录
select *from collage
where allowance = '有';
-- 2、实现
select collagename,count(stuname) num
from student s,collage c
where s.collageid = c.collageid and allowance = '有'
group by collagename
having count(stuname) >= 2;
-- 24.查询各学校每个校区可以平均分得多少计划招生人数，列出学校名称、校区数量、平均计划招生人数（avgplanned）（取小于等于结果值的最大整数）。
select c.collagename collagename, c.campus, round(sum(m.plannedpeople)/c.campus) avgplanned
from collage c,major m
where c.collageid = m.collageid
group by collagename,campus;
-- 25.查询各个地区的地区名称、考生总数（stucount）、平均分数（avgscore）（精确到2位小数）、最高分（maxscore）、学校数量（ccount）、教授总数（pcount）。
-- 1、对学生表查出各个地区的考生总数、平均分、最高分
select location,count(stuname) num,round(sum(score)/count(stuname),2) avgscore,max(score) maxscore
from student
group by location;
-- 2、查出各个地区高校的数量以及教授的人数
select location,count(collageid) ccount,sum(professor) pcount
from collage
group by location;
-- 3、实现
-- 要实现把以上的所有查询结果汇总到一起
-- 要用到union,必须两个表的列数是要一样的
-- 目前无法完成该任务啊。
-- 26.查询有哪些学校将“计算机”这门学科作为了重点学科，列出学校名称、计划人数、学校所在地。
-- 1.先查出有计算机学科的学校
select c.collagename,m.plannedpeople,c.location
from major m,collage c
where m.keymajor='是' and m.major = '计算机'
and c.collageid = m.collageid;
-- 27.查询非重点学科中，有哪些学科的学制超过了3年，列出学科名称、学制。
select major,years
from major
where major.keymajor = '否' and major.years > 3;
-- 28.查询各学科类别下各学科的招生总人数，列出学科类别、学科名称、招生总人数。
select majorcategory,major,sum(plannedpeople)
from major
group by majorcategory,major;
-- 29.查询学科名称不包含“数学”的所有理学类的学科名称。
select major
from major
where majorcategory = '理学' and major not like '%数学%';
-- 30.查询有哪些省份开设了“化学”相关学科，列出所在地、学科名称。
select m.major ,c.location
from major m,collage c
where m.major like '%化学%' and m.collageid = c.collageid;
-- 31.根据计划招生人数查询当今哪一门学科是最热门的，列出学科名称。
-- 1、先按降序排列
select major,sum(plannedpeople)
from major
group by major
order by plannedpeople desc;
-- 2、实现
select a.major
from (
select major,sum(plannedpeople)
from major
group by major
order by plannedpeople desc
) a
limit 1;
-- 32.查询有哪些学科是江苏未曾开设的学科，列出学科名称、所在地。
-- 1、先找出江苏已经开设的学科
select distinct m.major
from major m,collage c
where c.collageid = m.collageid and c.location = '江苏';
-- 2、找出总的学科
select distinct major from major;
-- 3、实现
select distinct major
from major
where major not in (
select distinct m.major
from major m,collage c
where c.collageid = m.collageid and c.location = '江苏'
);
-- 33.查询重点学科数量>=2的所有学校，列出学校名称、重点学科数量（keycount）及对应的招生总人数（sumplanned）。
select c.collagename,count(m.major) keycount,sum(plannedpeople) sumplanned
from collage c,major m
where c.collageid = m.collageid and m.keymajor = '是'
group by collagename
having keycount >= 2;
-- 34.查询哪个学校开设有学制最短的学科，列出学校名称、学科名称、学制。
select c.collagename,m.major,m.years
from collage c,major m
where c.collageid = m.collageid and
m.years = (
select min(years) from major
)
order by years asc;
-- 35.查询学校信息表，输出如下格式的信息：“南通大学在江苏，有5个校区、40名教授。”。
select concat(collagename,'在',location,'有',campus,'个校区、',professor,'名教授。')
from collage;
-- 36.写出上题的oracle语句。
-- 学校表
create table collage(
collageid varchar(50) not null,
collagename varchar(50) not null,
location varchar(50) not null,
allowance varchar(50) not null,
campus number(5) not null,
professor number(8) not null);
-- 插入数据
insert into collage values (1023,'南通大学','江苏','无',5,40);
insert into collage values (1392,'东南大学','江苏','有',3,80);
insert into collage values (1409,'华东师范大学','上海','有',3,60);
insert into collage values (1765,'苏州大学','江苏','无',5,55);
insert into collage values (1005,'浙江城市学院','浙江','有',2,12);
insert into collage values (1111,'宁波大学','浙江','有',3,30);
insert into collage values (1029,'青岛大学','山东','有',6,30);
insert into collage values (1333,'济南大学','山东','无',4,35);
insert into collage values (1643,'南京大学','江苏','有',6,90);
insert into collage values (1660,'南京中医药大学','江苏','有',5,55);
insert into collage values (1890,'南京艺术学院','江苏','无',3,40);
commit;
-- 实现
select collagename ||'在'||location||'有'||campus||'个校区、'||professor||'名教授。' as sentence
from collage;
-- 37.查询学校信息表中各省份的学校已录取总人数占该省份计划总人数的比例，列出所在地、比例（percent）（精确到2位小数，用%表示，如2.15%）。
-- 1、先查出来学校总共招多少人数
select c.collagename,c.location,sum(m.plannedpeople) sumpeople
from collage c,major m
where c.collageid = m.collageid
group by collagename, location;
-- 2、再查出学校已经录取了多少学生
select c.collagename,count(s.stuname) stucount
from collage c,student s
where c.collageid = s.collageid
group by collagename;
-- 3、实现
select a.collagename,a.location,concat(round(b.stucount/a.sumpeople,2)*100,'%')
from (
select c.collagename,c.location,sum(m.plannedpeople) sumpeople
from collage c,major m
where c.collageid = m.collageid
group by collagename, location
)a,
(
select c.collagename,count(s.stuname) stucount
from collage c,student s
where c.collageid = s.collageid
group by collagename
)b
where a.collagename = b.collagename;
-- 38.查询各批次录取的所有学生数量，列出批次、学生数量（stucount）、该批次中考生的最高分（maxscore）。
-- 1、先查出每个批次号的最高分和最低分
select *from batchinfo b
where b.batchnum in (
select batchnum from batchinfo
);
-- 2、先查出每个批次号下面录取的学生信息
select s.stuname,s.score,b.batchnum
from student s,
(
select *from batchinfo b
where b.batchnum in (
select batchnum from batchinfo
)
)b
where s.score>= b.lowscore && s.score <= b.highscore;
-- 3、实现
select b.batchnum,count(b.stuname) stucount,max(b.score) maxscore
from (
select s.stuname,s.score,b.batchnum
from student s,
(
select *from batchinfo b
where b.batchnum in (
select batchnum from batchinfo
)
)b
where s.score>= b.lowscore && s.score <= b.highscore
) b
group by batchnum;
-- 39.查询第2批次录取的学生中男、女生人数分别是多少。
-- 1、先查出第二批录取学生的信息
select s.stuname,s.sex,b.batchnum
from student s,
(
select *from batchinfo b
where b.batchnum = 2
)b;
-- 2、先查出第二批录取男、女学生
-- 男学生数量
select b.batchnum,count(s.stuname) stucount
from student s,
(
select *from batchinfo b
where b.batchnum = 2
)b
where s.sex='男'
group by b.batchnum;
-- 女学生数量
select b.batchnum,count(s.stuname) stucount
from student s,
(
select *from batchinfo b
where b.batchnum = 2
)b
where s.sex='女'
group by b.batchnum;
-- 3、实现
select a.batchnum,a.stucount boycount,b.stucount girlcount
from (
select b.batchnum,count(s.stuname) stucount
from student s,
(
select *from batchinfo b
where b.batchnum = 2
)b
where s.sex='男'
group by b.batchnum
)a,
(
select b.batchnum,count(s.stuname) stucount
from student s,
(
select *from batchinfo b
where b.batchnum = 2
)b
where s.sex='女'
group by b.batchnum
)b
where a.batchnum = b.batchnum;
-- 40.查询第1批次录取的学生都去了哪些学校，列出学校编号、学校名称。
-- 1、查出第一批学生的学校信息
select s.collageid,s.stuname
from student s,(
select *from batchinfo b
where b.batchnum = 1
)b
where s.score>= b.lowscore && s.score <= b.highscore;
-- 2、实现
select c.collageid,b.stuname,c.collagename
from collage c,
(
select s.collageid,s.stuname
from student s,(
select *from batchinfo b
where b.batchnum = 1
)b
where s.score>= b.lowscore && s.score <= b.highscore
)b
where c.collageid = b.collageid;
9307
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
