数据库查询测试(一)

--查询所有的字段怎么办

--查询所有学生的学号，并在前面加上“lovo”

--查询所有学生的学号、姓名( 显示中文字段:别名)

--查询各个考试日期(不显示时分秒)

--如何按姓名排序(升序，倒序)

--语文分从低到高排序(空值转换函数)

--如何查询没有输入年龄的学生信息(空值判断，不能…不能…)

--如何让年龄按从小到大排序

--如何求每个人的总分(语文+数学)

--查询年龄在20以上,语文在85以上的学生记录(复合条件:and,or)

--所有姓王的学生信息(模糊查询)

--姓名中包含“%%”的学生信息？(了解)
select t.*, t.rowid from t_stu t
where t.stu_name like '%/%%' escape '/'

--已经有考试了哪些科目(重复值的过滤，d后面可以跟多个字段)

--想同时查看两个表所有学生的考试记录
--(表间连接：纵;去除重复数据;字段要对准)

--想知道两个表中一共考试了哪些科目

--查询成绩50-90范围内

--查询成绩不在50-90范围内

--查询1,3,5,9组同学的考试记录

--把表t_exam中成绩大于60分的学生添加到t_exam2

 

相关的两个表：

prompt PL/SQL Developer import file
prompt Created on 2007年6月20日 by Administrator
set feedback off
set define off
prompt Dropping T_STU...
drop table T_STU cascade constraints;
prompt Creating T_STU...
create table T_STU
(
  STU_ID   NUMBER not null,
  STU_NAME VARCHAR2(10),
  STU_SFZ  VARCHAR2(20),
  AGE      NUMBER,
  DATE_REG DATE,
  FEN_YW   NUMBER(5,2),
  FEN_SX   NUMBER(5,2)
)
;
comment on column T_STU.STU_ID
  is '学号';
comment on column T_STU.STU_NAME
  is '姓名';
comment on column T_STU.STU_SFZ
  is '身份证';
comment on column T_STU.AGE
  is '年龄';
comment on column T_STU.DATE_REG
  is '注册日期';
comment on column T_STU.FEN_YW
  is '语文';
comment on column T_STU.FEN_SX
  is '数学';
alter table T_STU
  add constraint PK_STU primary key (STU_ID);
alter table T_STU
  add constraint CHK_1111
  check (age>16);

prompt Disabling triggers for T_STU...
alter table T_STU disable all triggers;
prompt Loading T_STU...
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (107, '王1', '100004', 29, null, 90, null);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (108, '朱', '100005', 39, null, 80, 60);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (109, '王2', '100006', 49, null, 70.5, 80);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (110, '李', '100007', 99, null, 80, 50);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (104, '赵', '100001', null, to_date('14-01-1990 11:12:13', 'dd-mm-yyyy hh24:mi:ss'), 58, null);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (105, '王3', '100002', null, to_date('14-02-1985', 'dd-mm-yyyy'), null, 60);
insert into T_STU (STU_ID, STU_NAME, STU_SFZ, AGE, DATE_REG, FEN_YW, FEN_SX)
values (106, '%孙', '100003', 19, to_date('14-03-1980', 'dd-mm-yyyy'), null, 70);
commit;
prompt 7 records loaded
prompt Enabling triggers for T_STU...
alter table T_STU enable all triggers;
set feedback on
set define on
prompt Done.

prompt PL/SQL Developer import file
prompt Created on 2007年6月20日 by Administrator
set feedback off
set define off
prompt Dropping T_EXAM...
drop table T_EXAM cascade constraints;
prompt Dropping T_EXAM2...
drop table T_EXAM2 cascade constraints;
prompt Creating T_EXAM...
create table T_EXAM
(
  KS_ID      NUMBER not null,
  KS_NAME    VARCHAR2(10),
  KS_DATE    DATE,
  KS_CJ      NUMBER(3),
  STU_ID     NUMBER,
  GROUP_NAME CHAR(4)
)
;
alter table T_EXAM
  add constraint PK_T_KS primary key (KS_ID);
alter table T_EXAM
  add constraint PPP foreign key (STU_ID)
  references T_STU (STU_ID) on delete cascade;

prompt Creating T_EXAM2...
create table T_EXAM2
(
  KS_ID   NUMBER,
  KS_NAME VARCHAR2(10),
  KS_DATE DATE,
  KS_CJ   NUMBER(3),
  STU_ID  NUMBER
)
;

prompt Disabling triggers for T_EXAM...
alter table T_EXAM disable all triggers;
prompt Disabling triggers for T_EXAM2...
alter table T_EXAM2 disable all triggers;
prompt Disabling foreign key constraints for T_EXAM...
alter table T_EXAM disable constraint PPP;
prompt Loading T_EXAM...
insert into T_EXAM (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID, GROUP_NAME)
values (1, '语文', to_date('19-06-2007', 'dd-mm-yyyy'), 20, 105, '1组 ');
insert into T_EXAM (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID, GROUP_NAME)
values (2, '数学', to_date('19-06-2007', 'dd-mm-yyyy'), 30, 105, '3组 ');
insert into T_EXAM (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID, GROUP_NAME)
values (3, '语文', to_date('19-06-2007', 'dd-mm-yyyy'), 40, 106, '4组 ');
insert into T_EXAM (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID, GROUP_NAME)
values (4, '化学', to_date('19-06-2007', 'dd-mm-yyyy'), 50, 106, '2组 ');
insert into T_EXAM (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID, GROUP_NAME)
values (5, '物理', to_date('19-06-2007', 'dd-mm-yyyy'), 60, 107, '3组 ');
insert into T_EXAM (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID, GROUP_NAME)
values (6, '化学', to_date('19-06-2007', 'dd-mm-yyyy'), 70, 107, '2组 ');
insert into T_EXAM (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID, GROUP_NAME)
values (7, '语文', to_date('19-06-2007', 'dd-mm-yyyy'), 80, 108, '1组 ');
insert into T_EXAM (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID, GROUP_NAME)
values (8, '化学', to_date('19-06-2007', 'dd-mm-yyyy'), null, 109, '2组 ');
insert into T_EXAM (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID, GROUP_NAME)
values (9, '化学', to_date('19-06-2007', 'dd-mm-yyyy'), 100, 110, '3组 ');
insert into T_EXAM (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID, GROUP_NAME)
values (10, '语文', null, 60, 110, '8组 ');
insert into T_EXAM (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID, GROUP_NAME)
values (11, '英语', null, 70, 110, '9组 ');
commit;
prompt 11 records loaded
prompt Loading T_EXAM2...
insert into T_EXAM2 (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID)
values (1, '德语', to_date('19-06-2006', 'dd-mm-yyyy'), 20, 105);
insert into T_EXAM2 (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID)
values (2, '法语', to_date('19-06-2006', 'dd-mm-yyyy'), 30, 105);
insert into T_EXAM2 (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID)
values (3, '语文', to_date('19-06-2006', 'dd-mm-yyyy'), 40, 106);
insert into T_EXAM2 (KS_ID, KS_NAME, KS_DATE, KS_CJ, STU_ID)
values (4, '英文', to_date('19-06-2006', 'dd-mm-yyyy'), 50, 106);
commit;
prompt 4 records loaded
prompt Enabling foreign key constraints for T_EXAM...
alter table T_EXAM enable constraint PPP;
prompt Enabling triggers for T_EXAM...
alter table T_EXAM enable all triggers;
prompt Enabling triggers for T_EXAM2...
alter table T_EXAM2 enable all triggers;
set feedback on
set define on
prompt Done.