第1题
/*
1.数据库操作题
Student(Sno,Sname,Sage,Ssex) 学生表
Sno:学号；Sname：学生姓名；Sage：学生年龄；Ssex：学生性别
Course(Cno,Cname,Tno) 课程表
Cno:课程编号；Cname：课程名字；Tno:教师编号
SC(Sno,Cno,score) 成绩表
Sno：学号；Cno：课程编号；score成绩
Teacher(Tno,Tname) 教师表
Tno：教师编号；Tname：教师名字
（1）查询各科成绩最高和最低的分，以如下形式显示：课程ID，最高分，最低分
（2）查询每门课成绩最好的前两名
（3）查询不同老师所教不同课程平均分从高到低显示
*/
--查询各科成绩最高和最低的分，以如下形式显示：课程ID，最高分，最低分
select cno as课程编号,max(score) as最高分,min(score) 最低分from sc groupby cno
select t1.cno,nvl(最高分,0) 最高分,nvl(最低分,0) 最低分 from course t1 left join (
select cno , max(score) as 最高分, min(score) as 最低分
from sc
group by cno) t2 on t1.cno =t2.cno
--（2）查询每门课成绩最好的前两名学生
select t1.sname,t3.cname,t2.mc,t2.score from student t1 join (
select * from (
select sno,
cno,
score,
dense_rank() over(partitionby cno order by score desc) mc
from sc)
where mc<=2) t2 on t1.sno = t2.sno
join course t3 on t2.cno = t3.cno
order by cno , mc
--（3）查询不同老师所教不同课程平均分从高到低显示
select t1.*,t2.pjf,t3.tname from course t1 join (
select cno,avg(score) pjf from sc group by cno) t2
on t1.cno = t2.cno
join teacher t3 on t1.tno = t3.tno
--学生表
create table Student
(
Sno char(3),
Sname varchar2(10),
Sage number(2),
Ssex char(2)
);
--添加主键约束
alter table Student
add constraint pk_student_Sno primarykey(Sno);
--添加注释
comment on table Student is '学生表';
comment on column Student.Sno is '学号';
comment on column Student.Sname is '学生姓名';
comment on column Student.Sage is '学生年龄';
comment on column Student.Ssex is '学生性别';
--添加信息
insert into Student
values('001','月一',14,'男');
insert into Student
values('002','月三',15,'女');
insert into Student
values('003','月五',13,'男');
insert into Student
values('004','月七',16,'男');
--教师表
create table Teacher
(
Tno char(2),
Tname varchar2(10)
);
--添加主键约束
alter table Teacher
add constraint pk_teacher_Tno primarykey(Tno);
--添加注释
comment on table Teacher is '教师表';
comment on column Teacher.Tno is '教师编号';
comment on column Teacher.Tname is '教师名字';
--添加信息
insert into Teacher
values('01','楚乔');
insert into Teacher
values('02','宇文玥');
insert into Teacher
values('03','燕洵');
--课程表
create table Course
(
Cno char(2),
Cname varchar2(10),
Tno char(2)
);
--添加主键约束
alter table Course
add constraint pk_course_Cno primarykey(Cno);
--添加外键约束
alter table Course
add constraint fk_Course_Teacher_Tnoforeign key(Tno)
references Teacher(Tno);
--添加注释
comment on table Course is '课程表';
comment on column Course.Cno is '课程编号';
comment on column Course.Cname is '课程名字';
comment on column Course.Tno is '教师编号';
--添加信息
insert into Course
values('11','Java','01');
insert into Course
values('12','Oracle','02');
insert into Course
values('13','JavaWeb','03');
--成绩表
create table SC
(
Sno char(3),
Cno char(2),
score number(3)
);
--联合主键
alter table SC
add constraint pk_sc_sno_cno primarykey(Sno,Cno);
--外键约束
alter table SC
add constraint fk_SC_Student_Sno foreignkey(Sno)
references Student(Sno);
alter table SC
add constraint fk_SC_Course_Cno foreignkey(Cno)
references Course(Cno);
--添加注释
comment on table SC is '成绩表';
comment on column SC.Sno is '学号';
comment on column SC.Cno is '课程编号';
comment on column SC.score is '成绩';
--添加数据
insert into SC
values('001','11',99);
insert into SC
values('001','12',50);
insert into SC
values('001','13',100);
insert into SC
values('002','13',99);
insert into SC
values('002','11',89);
insert into SC
values('003','11',99);
insert into SC
values('003','12',99);
insert into SC
values('004','11',99);
insert into SC
values('004','12',99);
insert into SC
values('004','13',99);
第2题
--查询部门总工资在4000以下的部门
select部门,sum(薪资) as总工资from (
select emp.*,dept.部门from dept join emp on dept.员工 = emp.员工) t
groupby部门havingsum(薪资) <4000
--创建dept表
createtable dept
(
员工varchar2(20) primarykey,--设置员工为主键
部门varchar2(20)
);
--创建emp表
createtable emp
(
员工varchar2(20) primarykey,--设置员工为主键
职位varchar2(20),
薪资number(10)
);
--设置emp中员工字段为外键参照于dept表的员工字段
altertable emp addconstraint fk_emp_员工foreignkey(员工) references dept(员工);
--向dept表中插入数据
insertinto dept(部门,员工) values ('营销部','张三');
insertinto dept(部门,员工) values ('营销部','李四');
insertinto dept(部门,员工) values ('业务部','王五');
insertinto dept(部门,员工) values ('业务部','赵六');
--向emp表中插入数据
insertinto emp(职位,员工,薪资) values('销售','张三',1000);
insertinto emp(职位,员工,薪资) values('经理','李四',2000);
insertinto emp(职位,员工,薪资) values('销售','王五',1500);
insertinto emp(职位,员工,薪资) values('经理','赵六',3000);
第3题
答案：
selectnameas姓名, sum(score) as总成绩
from STUSCORE
groupbyname
orderbysum(score) desc
建表数据：
prompt PL/SQLDeveloper import file
prompt Created on2017年7月1日 by lenovo
set feedback off
set define off
prompt DroppingSTUSCORE...
drop tableSTUSCORE cascade constraints;
prompt CreatingSTUSCORE...
create tableSTUSCORE
(
NAME VARCHAR2(10),
SUBJECT VARCHAR2(10),
SCORE NUMBER(3),
STUID NUMBER(2)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
prompt Disablingtriggers for STUSCORE...
alter tableSTUSCORE disable all triggers;
prompt LoadingSTUSCORE...
insert intoSTUSCORE (NAME, SUBJECT, SCORE, STUID)
values ('张三', '数学 ', 89, 1);
insert intoSTUSCORE (NAME, SUBJECT, SCORE, STUID)
values ('张三', '语文', 80, 1);
insert intoSTUSCORE (NAME, SUBJECT, SCORE, STUID)
values ('张三', '英语', 70, 1);
insert intoSTUSCORE (NAME, SUBJECT, SCORE, STUID)
values ('李四', '数学', 90, 2);
insert intoSTUSCORE (NAME, SUBJECT, SCORE, STUID)
values ('李四', '语文', 70, 2);
commit;
prompt 5 recordsloaded
prompt Enablingtriggers for STUSCORE...
alter tableSTUSCORE enable all triggers;
set feedback on
set define on
prompt Done.
第4题
Sql语句题
1）sql语言中，条件“年龄 between 40 and50”表示年龄在40至50 之间，且（A）
A.包括40岁和50岁 B.不包括40岁和50岁
C.包括40岁但不包括50岁 D.包括50岁但不包括40岁
2）模式查找 like ‘_a%’，下面哪个结果是可能的（B）
A.aili B.bai
C.bba D.cca
3)SQL语言中删除一个表的命令是（B）
A.delete B.drop
C.clear D.remove
4)在SQL的查询语句中，order by 选项实现对结果表的（D）功能
A.分组统计 B.求和
C.查找 D.排序
第5题
createtable club
(
idnumber(2) notnullprimarykey,
gender char(2),
age number(3)
);
insertinto club(id,gender,age) values(67,'M',19);
insertinto club(id,gender,age) values(68,'F',30);
insertinto club(id,gender,age) values(69,'F',27);
insertinto club(id,gender,age) values(70,'F',16);
insertinto club(id,gender,age) values(71,'M',32);
createtable student
(
namevarchar2(20),
course varchar2(20),
score number(3)
);
insertinto student(name,course,score)values('张青','语文',72);
insertinto student(name,course,score)values('王华','数学',72);
insertinto student(name,course,score)values('张华','英语',81);
insertinto student(name,course,score)values('张青','物理',67);
insertinto student(name,course,score)values('李立','化学',98);
insertinto student(name,course,score)values('张燕','物理',70);
insertinto student(name,course,score)values('张青','化学',76);
--查找俱乐部中男性会员和女性会员的总数
select gender,count(*) from club groupby gender
--查询张姓学生中平均成绩大于75的学生信息
select * from student wherenamein (
selectname
from student
wherenamelike'张%'
groupbyname
havingavg(score) > 75)
第6题
createtable customer_shopping(
commodity varchar2(2),
customer varchar2(4),
cnumber number(2)
);
insertinto customer_shopping(commodity,customer,cnumber)values('A','甲',2);
insertinto customer_shopping(commodity,customer,cnumber)values('B','乙',4);
insertinto customer_shopping(commodity,customer,cnumber)values('C','丙',1);
insertinto customer_shopping(commodity,customer,cnumber)values('A','丁',2);
insertinto customer_shopping(commodity,customer,cnumber)values('B','丙',5);
--请写SQL查询所有购入商品为两种或两种以上的购物人记录
select * from customer_shopping where customer in (
select customer from customer_shopping groupby customer
havingcount(*)>=2)
createtable team(
IDnumber(2),
namevarchar2(3)
);
insertinto team(ID,name) values(1,'a');
insertinto team(ID,name) values(2,'b');
insertinto team(ID,name) values(3,'b');
insertinto team(ID,name) values(4,'a');
insertinto team(ID,name) values(5,'c');
insertinto team(ID,name) values(6,'c');
/**
请写出SQL语句执行一个删除操作，当Name列上有相同时，只保留ID这列上值最小的记录。
例如：删除后的结果如下：
ID(number) Name(varchar2)
1 a
2 b
*/
deletefrom team whereidnotin (
selectmin(ID) as zx from team groupbyname)
第7题
建表数据：
/*学生表t_student,字段id,name,age;
教师表t_teacher,字段id,name,age,
教师学生表t_tsrel,字段id,tID,sID
请统计每个教师有多少学生，要求教师年龄大于30，学生年龄大于12，
并列出教师姓名、学生人数（需考虑没有学生的教师）
*/
--全选执行就行了
--创建学生表
create table t_student
(
id varchar2(10),
name varchar2(10) not null,
age number(2) not null
);
--设置学生表id为主键
alter table t_student
add constraint pk_t_student_id
primary key (id);
--创建教师表
create table t_teacher
(
id varchar2(10),
name varchar2(10) not null,
age number(2) not null
);
--设置教师表id为主键
alter table t_teacher
add constraint pk_t_teacher_id
primary key (id);
--创建教师学生表
create table t_tsrel
(
id varchar2(10) ,
tID varchar2(10) not null,
sID varchar2(10) not null
);
--设置教师学生表id为主键
alter table t_tsrel
add constraint pk_t_tsrel_id
primary key (id);
--设置教师学生表tID为外键
alter table t_tsrel
add constraint fk_t_tsrel_tID
foreign key (tID) references t_teacher(id);
--设置教师学生表sID为外键
alter table t_tsrel
add constraint fk_t_student_sID
foreign key (sID) references t_student(id);
--学生表插入数据
insert into t_student(id,name,age) values ('14101101','小华',16);
insert into t_student(id,name,age) values('14101102','小张',15);
insert into t_student(id,name,age) values('14101103','小崔',11);
insert into t_student(id,name,age) values('14101104','小丘',10);
insert into t_student(id,name,age) values ('14101105','小苏',13);
insert into t_student(id,name,age) values('14101106','小徐',14);
insert into t_student(id,name,age) values('14101107','小吕',13);
insert into t_student(id,name,age) values('14101108','小王',11);
insert into t_student(id,name,age) values ('14101109','小李',12);
insert into t_student(id,name,age) values('14101110','小花',14);
insert into t_student(id,name,age) values('14101111','小智',15);
insert into t_student(id,name,age) values('14101112','小豪',10);
insert into t_student(id,name,age) values ('14101201','小马',16);
insert into t_student(id,name,age) values('14101202','小于',15);
insert into t_student(id,name,age) values('14101203','小孩',11);
insert into t_student(id,name,age) values('14101204','小杨',10);
insert into t_student(id,name,age) values ('14101205','小赵',13);
insert into t_student(id,name,age) values('14101206','小子',14);
insert into t_student(id,name,age) values('14101207','小张',13);
insert into t_student(id,name,age) values('14101208','小红',11);
insert into t_student(id,name,age) values ('14101209','小岳',12);
insert into t_student(id,name,age) values('14101210','小赑',14);
insert into t_student(id,name,age) values('14101211','小戕',15);
insert into t_student(id,name,age) values('14101212','小珩',10);
insert into t_student(id,name,age) values ('14101213','小峰',10);
insert into t_student(id,name,age) values('14101301','小黑',16);
insert into t_student(id,name,age) values('14101302','小鹏',15);
insert into t_student(id,name,age) values('14101303','小朱',11);
insert into t_student(id,name,age) values ('14101304','小龙',10);
insert into t_student(id,name,age) values('14101305','小三',13);
insert into t_student(id,name,age) values('14101306','小六',14);
insert into t_student(id,name,age) values('14101307','小飞',13);
insert into t_student(id,name,age) values ('14101308','小辉',11);
insert into t_student(id,name,age) values('14101309','小杰',12);
insert into t_student(id,name,age) values('14101310','小旭',14);
insert into t_student(id,name,age) values('14101311','小星',15);
insert into t_student(id,name,age) values ('14101312','小虎',10);
insert into t_student(id,name,age) values('14101313','小凯',10);
insert into t_student(id,name,age) values('14101314','小昌',10);
--教师表插入数据
insert into t_teacher(id,name,age) values('1101','马老师',43);
insert into t_teacher(id,name,age) values('1102','孙老师',32);
insert into t_teacher(id,name,age) values('1103','杨老师',23);
insert into t_teacher(id,name,age) values('1104','牛老师',28);
--教师学生表插入数据
insert into t_tsrel(id,tID,sID) values('001','1101','14101101');
insert into t_tsrel(id,tID,sID) values('002','1101','14101102');
insert into t_tsrel(id,tID,sID) values('003','1101','14101103');
insert into t_tsrel(id,tID,sID) values('004','1101','14101104');
insert into t_tsrel(id,tID,sID) values('005','1101','14101105');
insert into t_tsrel(id,tID,sID) values('006','1101','14101106');
insert into t_tsrel(id,tID,sID) values('007','1101','14101107');
insert into t_tsrel(id,tID,sID) values('008','1101','14101108');
insert into t_tsrel(id,tID,sID) values('009','1101','14101109');
insert into t_tsrel(id,tID,sID) values('010','1101','14101110');
insert into t_tsrel(id,tID,sID) values('011','1101','14101111');
insert into t_tsrel(id,tID,sID) values('012','1101','14101112');
insert into t_tsrel(id,tID,sID) values('013','1102','14101201');
insert into t_tsrel(id,tID,sID) values('014','1102','14101202');
insert into t_tsrel(id,tID,sID) values('015','1102','14101203');
insert into t_tsrel(id,tID,sID) values('016','1102','14101204');
insert into t_tsrel(id,tID,sID) values ('017','1102','14101205');
insert into t_tsrel(id,tID,sID) values('018','1102','14101206');
insert into t_tsrel(id,tID,sID) values('019','1102','14101207');
insert into t_tsrel(id,tID,sID) values('020','1102','14101208');
insert into t_tsrel(id,tID,sID) values('021','1102','14101209');
insert into t_tsrel(id,tID,sID) values('022','1102','14101210');
insert into t_tsrel(id,tID,sID) values('023','1102','14101211');
insert into t_tsrel(id,tID,sID) values('024','1102','14101212');
insert into t_tsrel(id,tID,sID) values('025','1102','14101213');
insert into t_tsrel(id,tID,sID) values('026','1103','14101301');
insert into t_tsrel(id,tID,sID) values('027','1103','14101302');
insert into t_tsrel(id,tID,sID) values('028','1103','14101303');
insert into t_tsrel(id,tID,sID) values('029','1103','14101304');
insert into t_tsrel(id,tID,sID) values('030','1103','14101305');
insert into t_tsrel(id,tID,sID) values('031','1103','14101306');
insert into t_tsrel(id,tID,sID) values('032','1103','14101307');
insert into t_tsrel(id,tID,sID) values('033','1103','14101308');
insert into t_tsrel(id,tID,sID) values('034','1103','14101309');
insert into t_tsrel(id,tID,sID) values('035','1103','14101310');
insert into t_tsrel(id,tID,sID) values('036','1103','14101311');
insert into t_tsrel(id,tID,sID) values('037','1103','14101312');
insert into t_tsrel(id,tID,sID) values('038','1103','14101313');
--删除教师学生表
--drop table t_tsrel
--删除学生表
--drop table t_student
--删除教师表
--drop table t_teacher
--select * from t_student
--select * from t_teacher
--select * from t_tsrel
答案：
/*请统计每个教师有多少学生，要求教师年龄大于30，学生年龄大于12，
并列出教师姓名、学生人数（需考虑没有学生的教师）*/
select name,nvl(rs,0) as rs from t_teachert1 left join
(
select tid,count(*) as rs from t_tsrelwhere sid in (
select id from t_student where age>12)
group by tid) t2
on t1.id = t2.tid
where age>30
第8题
drop table dept
drop table emp;
--部门表
create table dept
(
dept_ID varchar2(20),
dept_NAME varchar2(20)
)
--员工表
create table emp
(
emp_ID varchar2(20),
emp_NAME varchar(20),
emp_age number(3),
DEPTID varchar2(20)
)
--增加主键在部门表
alter table dept add constraintpk_dept_dept_ID primary key(dept_ID);
--增加外键员工表
alter table emp add constraint fk_emp_DEPTID foreign key(DEPTID) referencesdept(dept_ID);
--增加主键员工表
alter table emp add constraintpk_emp_emp_ID primary key(emp_ID);
--插入的部门表信息
insert into dept(dept_ID,dept_NAME) values('001','华山派');
insert into dept(dept_ID,dept_NAME) values('002','昆仑派');
insert into dept(dept_ID,dept_NAME) values('003','峨眉派');
insert into dept(dept_ID,dept_NAME) values('004','少林派');
insert into dept(dept_ID,dept_NAME) values('005','武当派');
insert into dept(dept_ID,dept_NAME) values('006','崆峒派');
insert into dept(dept_ID,dept_NAME) values('007','古墓派');
--插入员工表信息
insert into emp(emp_ID,emp_NAME,emp_age,DEPTID) values('a1','张三丰',101,'005');
insert intoemp(emp_ID,emp_NAME,emp_age,DEPTID) values('a2','殷梨亭',45,'005');
insert intoemp(emp_ID,emp_NAME,emp_age,DEPTID) values('a3','张无忌',21,'005');
insert into emp(emp_ID,emp_NAME,emp_age,DEPTID) values('a4','朱元璋',30,'005');
insert intoemp(emp_ID,emp_NAME,emp_age,DEPTID) values('a5','宋远桥',51,'005');
insert intoemp(emp_ID,emp_NAME,emp_age,DEPTID) values('a6','宋青书',25,'005');
insert intoemp(emp_ID,emp_NAME,emp_age,DEPTID) values('b1','令狐冲',25,'001');
insert intoemp(emp_ID,emp_NAME,emp_age,DEPTID) values('c1','鲜于通',27,'002');
insert intoemp(emp_ID,emp_NAME,emp_age,DEPTID) values('d1','郭襄',35,'003');
insert intoemp(emp_ID,emp_NAME,emp_age,DEPTID) values('e1','空文',78,'004');
insert intoemp(emp_ID,emp_NAME,emp_age,DEPTID) values('f1','鸣人',20,'006');
insert intoemp(emp_ID,emp_NAME,emp_age,DEPTID) values('r1','杨过',25,'007');
--（1）编写SQL查询出每个员工所属的部门的名称
select emp.*,dept.dept_name from emp joindept on emp.deptid = dept.dept_id
--（2）编写SQL查询出员工数量大于5的部门及具体员工数量
select dept.*,rs from dept join (
select deptid,count(*) as rs from emp groupby deptid having count(*)>5) t
on dept.dept_id = t.deptid
第9题:
9、请写一条SQL语句查询出每门课都大于80分的销学生姓名
表名：table 字段及记录如下：
| Name | Kecheng | fengshu |
| 张三 | 语文 | 81 |
| 张三 | 数学 | 75 |
| 李四 | 语文 | 76 |
| 李四 | 数学 | 90 |
| 王五 | 语文 | 81 |
| 王五 | 数学 | 100 |
| 王五 | 英语 | 90 |
自答：
--每门课都大于80分的学生姓名
Select name fromzt9_table where fengshu>80
--创建表 table
create table zt9_table
(
Name varchar2(20),
Kecheng varchar2(20),
Fengshu number(3)
);
--添加数据
Insert into zt9_table values('张三','语文',81);
Insert into zt9_table values('张三','数学',75);
Insert into zt9_table values('李四','语文',76);
Insert into zt9_table values('李四','数学',90);
Insert into zt9_table values('王五','语文',81);
Insert into zt9_table values('王五','数学',100);
Insert into zt9_table values('王五','英语',90);
答案1：
selectname
from zt9_table
groupbyname
havingmin(fengshu)>= 80
第10题:
请写一个SQL语句删除内容相同的学生冗余信息
---创建表table
Create table zt10_table
(
学号varchar2(10) ,
姓名varchar2(20),
课程编号varchar2(10),
课程名称varchar2(10),
分数 number(3)
);
--添加数据
Insert into zt10_table values('2005001','张三','0001','数学',69);
Insert into zt10_table values('2005002','李四','0001','数学',89);
Insert into zt10_table values('2005001','张三','0001','数学',69);
答案:
deletefrom zt10_table whererowidnotin
(
select min(rowid)
from zt10_table
groupby学号||姓名||课程编号||课程名称||分数
)
第11题
第12题
1、
答:
select * from empwhere dept_id = (select id from dept where dept_name=’A’)
2:
答:
update emp setsalary = salary+500 where salary<2000 and dept_id=(select id from dept wheredept_name=’信息中心’)
第13题
createtable right_item
(
right_item_code number(20),
right_item_name char(20)
)
insertintoright_item(right_item_code,right_item_name) values(3,'张a');
insertintoright_item(right_item_code,right_item_name) values(2,'张b');
insertintoright_item(right_item_code,right_item_name) values(15,'张c');
insertintoright_item(right_item_code,right_item_name) values(1,'张d');
insertintoright_item(right_item_code,right_item_name) values(25,'张e');
insertintoright_item(right_item_code,right_item_name) values(16,'张f');
insertintoright_item(right_item_code,right_item_name) values(7,'张g');
insertintoright_item(right_item_code,right_item_name) values(8,'张h');
insertintoright_item(right_item_code,right_item_name) values(19,'张i');
insertintoright_item(right_item_code,right_item_name) values(10,'张j');
insertintoright_item(right_item_code,right_item_name) values(4,'张k');
insertintoright_item(right_item_code,right_item_name) values(22,'张l');
insertintoright_item(right_item_code,right_item_name) values(5,'张m');
insertintoright_item(right_item_code,right_item_name) values(14,'张n');
--有一权限表right_item
--问题：按字段right_item_code(权限代码)的数字顺序查出最大的8条记录
selectright_item_name from (
selectright_item_code,right_item_name ,rank() over (orderby right_item_code asc) t from right_item )where t < 9
第14题
select * from book_a;
select * from book_b;
--删除book_a表和book_b中book_id字段相同的记录
deletefrom book_a
where book_a.book_id in
(select a.book_id
from book_a a
join book_b b on a.book_id =b.book_id)
Deletefrom book_a
where book_a.book_id in
(select book_a. book_id
from book_a
join book_b on book_a. book_id = book_b.book_id)
Createtable book_a
(
Book_id number(20),
Book_name char(20),
Book_code varchar(20)
);
Createtable book_b
(
Book_id number(20),
Book_name char(20),
Book_code varchar(20)
);
insertintobook_a(Book_id,Book_name,Book_code) values(1,'java','bh1');
insertintobook_a(Book_id,Book_name,Book_code) values(2,'数据库','bh2');
insertintobook_a(Book_id,Book_name,Book_code) values(3,'计算机','bh3');
insertintobook_a(Book_id,Book_name,Book_code) values(4,'网络原理','bh4');
insertintobook_a(Book_id,Book_name,Book_code) values(5,'离散','bh5');
insertintobook_a(Book_id,Book_name,Book_code) values(6,'高数','bh6');
insertintobook_b(Book_id,Book_name,Book_code) values(9,'组成原理','bh1');
insertintobook_b(Book_id,Book_name,Book_code) values(7,'应用文','bh2');
insertintobook_b(Book_id,Book_name,Book_code) values(8,'数据结构','bh3');
insertintobook_b(Book_id,Book_name,Book_code) values(4,'网络原理','bh4');
insertintobook_b(Book_id,Book_name,Book_code) values(5,'离散','bh5');
insertintobook_b(Book_id,Book_name,Book_code) values(6,'高数','bh6');
第15题
--表名：高考信息表
--给出高考总分在600分以上的学生准考证号
select * from高考信息表
select 准考证号
from (select准考证号, sum(数学 + 语文 + 英语 + 物理 + 化学) as zf
from高考信息表
groupby准考证号)
where zf > 600
Createtable高考信息表
(
准考证号 number(20),
数学number(3),
语文number(3),
英语number(3),
物理number(3),
化学number(3)
);
Insertinto高考信息表(准考证号, 数学, 语文, 英语,物理,化学)values(2006001,108,119,98,127,136);
Insertinto高考信息表(准考证号, 数学, 语文, 英语,物理,化学)values(2006002,149,105,110,142,129);
第16题
scott.emp
问题1：显示员工姓名，入职时间，工资，和入职的季度
select
ename,hiredate,sal,
case
when to_char(hiredate,'mm')<=3then'第一季度'
when to_char(hiredate,'mm')<=6then'第二季度'
when to_char(hiredate,'mm')<=9then'第三季度'
else
'第四季度'
endas季度
from emp
问题2：统计各个季度入职的人数
方法一:
select
case
when to_char(hiredate,'mm')<=3then'第一季度'
when to_char(hiredate,'mm')<=6then'第二季度'
when to_char(hiredate,'mm')<=9then'第三季度'
else
'第四季度'
endas季度,count(*) as人数
from emp
groupby
case
when to_char(hiredate,'mm')<=3then'第一季度'
when to_char(hiredate,'mm')<=6then'第二季度'
when to_char(hiredate,'mm')<=9then'第三季度'
else
'第四季度'
end
方法2：
select季度,count(*) as人数from (
select
case
when to_char(hiredate,'mm')<=3then'第一季度'
when to_char(hiredate,'mm')<=6then'第二季度'
when to_char(hiredate,'mm')<=9then'第三季度'
else
'第四季度'
endas季度,ename,hiredate,sal
from emp)
groupby季度
第17题
表A2_A、表A2_B结构如下：
表A2_A：基础信息表
| Name | Code | type |
| 用户代码 | usercode | Integer |
| 用户名称 | username | varchar(50) |
| 住址 | address | varchar(100) |
表A2_B：账户信息表
| Name | Code | type |
| 用户代码 | usercode | Integer |
| 帐号 | account | varchar(50) |
| 开户银行 | bank | varchar(10) |
A2_A表与A2_B表为一对多关系，请使用一条SQL语句查询用户代码在10至10000之间的其开户银行信息，要求开户银行信息不可重复。
第18题
表A3_A结构如下：
表A3_A：
| Name | Code | type |
| 用户代码 | usercode | Integer |
| 用户名称 | username | varchar(50) |
| 注册时间 | regtime | Timestamp |
| 住址 | address | varchar(100) |
请使用一个SQL语句查询注册时间为2012年1月1日的用户名称，要求用户名只取前6个字符。
已知公司的
员工表A4_EMP(EID,ENAME,BDATE,SEX,CITY)
部门表A4_DEPT(DID,DNAME,DCITY)
工作表A4_WORK(EID,DID,STARTDATE,SALARY)
各字段说明如下：
EID——员工编号，最多6个字符。例如A00001（主键）
ENAME——员工姓名，最多10个字符。例如SMITH
BDATE——出生日期，日期型
SEX——员工性别，单个字符。F或者M
CITY——员工居住的城市，最多20个字符。例如：上海
DID——部门编号，最多3个字符。例如：A01（主键）
DNAME——部门名称，最多20个字符。例如：研发部门
DCITY——部门所在的城市，最多20个字符。例如：上海
STARTDATE——员工到部门上班的日期，日期型
SALARY——员工的工资。整型
1、创建表A4_EMP, A4_DEPT , A4_WORK
2、向每个表中插入适当的数据。例如：插入三条部门的数据，分别为每个部门插入两条员工数据
3、查询“研发”部门的所有员工的基本信息
4、查询拥有最多的员工的部门的基本信息（要求只取出一个部门的信息），如果有多个部门人数一样，那么取出部门编号最小的那个部门的基本信息
5、显示部门人数大于5的每个部门的编号，名称，人数
6、查询出工资比其所在部门平均工资高的所有职工信息
7、显示部门人数大于5的每个部门的最高工资，最低工资
8、列出员工编号以字母P至S开头的所有员工的基本信息
9、删除年龄超过60岁的员工
10、为工龄超过10年的职工增加10%的工资
第19题
现有3个表：
A1_S(学号(SNO)PK , 姓名(SNAME));
A1_C(编号(CNO)PK , 名称(CNAME) , 教师(CTEACHER));
A1_SC((学号(SNO) PK , 课程编号(CNO)FK) PK , 成绩(SCGRADE));
完成以下查询功能。
1、 找出没有报“李明”老师课程的学生的姓名。
2、 列出有2门以上（含2门）课程不及格的学生的姓名和平均成绩。
3、 列出学过“1”、“2”号课程的所有学生的姓名。
4、 列出1号课程成绩高于2号课程成绩的学生的姓名
5、 列出1号课程成绩高于2号课程成绩的学生的姓名，1号课程成绩，2号课程成绩和平均成绩。
第20题
假设：数据库中存在下面三个表：
1.学生表s（学号s#，姓名sname，年龄age，性别sex）
2.学生和课程表的关系sc（学号S#，课程号c#，成绩grade）
3.课程表c（课程号c#，课程名称cname，老师teacher）
请填写sql语句解决下列问题
1.检索学习课程号为c2的学生学号和姓名
2.检索选修课课程名为maths的学生学号与姓名
3.检索选修课程号为c2或c4的学生学号
4.求男生的总人数和平均年龄
5.统计每一个选修课程的学生人数
6.在s中检索姓名以字符D打头的学生姓名
7.在s中查找尚未填写年龄的学生名称(年龄为空)
8.检索至少不学c2和c4两门课程的学生学号
建表数据：
create table s
(
s# number(10) primary key,
sname varchar2(10),
age number(3),
sex char(4)
);
create table c
(
c# varchar2(10) primary key,
cname varchar2(32),
teacher varchar2(32)
);
create table sc
(
s# number(10),
c# varchar2(10),
grade number(4)
);
alter table sc add constraint pk_s#_c#primary key(c#,s#);
alter table sc add constraint fk_sc_c_sforeign key(s#) references s(s#);
alter table sc add constraint fk_sc_c#foreign key(c#) references c(c#);
insert into s(s#, sname, age, sex)values(1,'D四',23,'男');
insert into s(s#, sname, age, sex)values(2,'张三',28,'男');
insert into s(s#, sname, age, sex)values(3,'小丽',null ,'女');
insert into s(s#, sname, age, sex)values(4,'小爱',18,'女');
insert into c(c#, cname, teacher)values('c1', '英语', '李老师');
insert into c(c#, cname, teacher)values('c2', '化学', '五老师');
insert into c(c#, cname, teacher)values('c3', '语文', '例老师');
insert into c(c#, cname, teacher)values('c4', 'maths', '应老师');
insert into sc(s#, c#, grade)values (1,'c2', 80);
insert into sc(s#, c#, grade)values(1,'c3', 50);
insert into sc(s#, c#, grade)values (2,'c2', 70);
insert into sc(s#, c#, grade)values(3,'c1', 55);
insert into sc(s#, c#, grade)values(4,'c4', 60);
答案：
--1检索学习课程号为001的学生的学号和姓名
法 1：
select s#,sname from student
where s# in (
select s# from sc where c#='001')
法二：
select t1.s#, t1.sname
from s t1
join (select s#, c# from sc_1 where c# = 'c2') t2 on t1.s# = t2.s#
--2检索学习课程名为JAVA的学生的学号和姓名
select s#, sname
from student
where s# in
(select s#
from sc
where c# = (select c# from course where cname = 'JAVA'))
--3查询选修课程为001或002的学号
select s# from sc where c# in ('001','002')
--4查询选择了001同时选择了002课程的学号。
--法1
select t1.s# from (
select * from sc where c#='001') t1
join
(select * from sc where c#='002') t2
on t1.s# = t2.s#
--法2
select s# from sc t1 where c#='001'
and (select count(*) from sc t2 where c#='002' and t2.s#=t1.s#)=1
--法3：集合运算
select s# from sc where c#='001'
intersect
select s# from sc where c#='002'
--法4：
select s# from sc where c# in ('001','002') group by s#
having count(*)=2
--5算出男同学的总数和平均年龄
select count(*) 总数,avg(sage) 平均年龄 from student where ssex='男'
--6统计每一个课程的选课人数和课程名
select t.*,nvl(rs,0) as 选课人数 from courset left join (
select c#,count(*) as rs from sc group by c#) t2
on t.c# = t2.C#
--7查询student中姓张的同学信息
select * from student where sname like '张%'
--8查询学生中没有填写年龄的学生信息
select * from student where sage is null
--9查询既没有学001,也没有学002号课程的学号
--法1:集合运算 差集 A minus B 得到A中与B不相同的信息(差集)
select distinct(s#) as s# from sc
minus
select s# from sc where c# in ('001','002')
--法2
select distinct(s#) as s# from sc
where s# not in (
select s# from sc where c# in('001','002'))
第21题
表student（S#，sname，sage，ssex）
Course（c#，cname，t#）
Sc（s#，c#，score）
Teacher（t#，tname）
问题：
1、查询“001”课程比“002”课程成绩高的所有学生的学号
2、查询平均成绩大于60分的同学的学号和平均成绩
3、删除学习“周杰伦”老师课的sc表记录
--1、
select * from sc where c#='001'
select * from sc where c#='002'
--将以上两表联合产生包含001课程成绩和002课程成绩的结果集
select t1.s#,t1.c# as k1,t1.score ask1cj,t2.c# as k2,t2.score as k2cj from
(select * from sc where c#='001') t1
join
(select * from sc where c#='002') t2
on t1.s# = t2.s#
where t1.score > t2.score
--子查询
select * from sc t1 where c#='001'
and score>(select score from sc t2 wherec#='002' and t2.s# = t1.s#)
--2
select s#, avg(score) as 平均成绩
from sc
group by s#
having avg(score) > 60
--3
delete from sc where c# in (
select c# from course where t# =(
select t# from teacher where tname='张老师'))
扫一扫
1168
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
