drop table grade;
create table grade(
name varchar2(10),
inTime date,
val varchar2(10)
)
insert into grade(name,inTime,val)
values('Tom','03-4月-08','A');
insert into grade(name,inTime,val)
values('Tom','02-4月-08','A');
insert into grade(name,inTime,val)
values('Tom','01-4月-08','B');
insert into grade(name,inTime,val)
values('Tom','31-3月-08','C');
insert into grade(name,inTime,val)
values('Jone','31-3月-08','A');
insert into grade(name,inTime,val)
values('Jone','01-4月-08','B');
insert into grade(name,inTime,val)
values('Jone','02-4月-08','B');
insert into grade(name,inTime,val)
values('Jone','03-4月-08','C');
--将intime这一列显示为“2008-04-03 16:44:11”
select to_char(inTime,'yyyy-mm-dd hh24:mi:ss')from grade;
--查询数据
select name,
count(val) A,
(select count(val)
from grade
where val = 'B'
and name = 'Tom') B,
(select count(val)
from grade
where val = 'C'
and name = 'Tom') C
from grade
where val = 'A'
and name = 'Tom'
group by name
union
select name,
count(val) A,
(select count(val)
from grade
where val = 'B'
and name = 'Jone') B,
(select count(val)
from grade
where val = 'C'
and name = 'Jone') C
from grade
where val = 'A'
and name = 'Jone'
group by name
--08年4月份得A的次数超过1次的人员姓名
--查询时间格式
select name
from grade
where intime between to_date('2008/4/1', 'yyyy-mm-dd') and
to_date('2008/4/30', 'yyyy-mm-dd')
and val = 'A'
group by name
having count(val) >= 1
--在grade 上建立两个外键
--外键在有主键的表中建立
create table grade(
name varchar2(10),
intinme date,
val varchar2(10),
Stu_id varchar2(10),
Cour_id varchar2(10),
foreign key(Stu_id_fk) reference Student(id),
foreign key(Cour_id_fk) reference Course(id)
)
create table Student(
id varchar2(10) not null primary key,
Stu_id_fk varvhar2(10)
)
create table course(
id varchar2(10) not null paimary key,
Cour_id_fk varchar(10)
)
--5
不会
|