机试一
1. create table Employee
(ID int,
EmpName varchar(20),
NumChildren int)
2. create table Dependent
(DependentName varchar(20),
Age int)
3.
(1)ID EmpName NumChildren
1 Anupam 3
6 Fernando 2
7 Gorbachev 4
8 Heather 2
9 Inga 8
(2) 进行差运算的属性不同,无法得到结果
(3)两个关系的维数不同,无法进行并运算
4.
(1)create table call
(FromAreaCode int,
FromNum int,
ToAreaCode int,
ToNum int,
CallStartTime TimeStamp,
Duration int,
Rate int,
Status char(1))
(2) create table location
(AreaCode int,
City varchar(30))
6.
(1)关系call中,主键为(FromAreaCode,FromNum,CallStartTime),备用键为(ToAreaCode,ToNum,CallStartTime,Status),区号加电话号唯一,但可以多次呼叫,所以加上时间,同一时刻一个电话号只能呼叫一个电话,也就唯一确定了一个事件。
7.
(1)
select count(call.ToAreaCode , call.ToNum , call.CallStartTime)
from call,location
where call.FromAreaCode=location.AreaCode and location.City='San Francisco'
and extract(year from call.CallStartTime) ='1989'
and extract(month from call.CallStartTime) ='10'
and extract(day from call.CallStartTime) ='17'
and extract(hour from call.CallStartTime) between 12 and 22
group by call.FromAreaCode,call.FromNum
order by count(call.ToAreaCode , call.ToNum , call.CallStartTime)
(2)
select location.AreaCode
from location,call
where location.AreaCode=call.ToAreaCode
and extract(year from call.CallStartTime) ='1989'
and extract(month from call.CallStartTime) ='10'
and extract(day from call.CallStartTime) ='17'
and call.Status='f' and extract(hour from call.CallStartTime) between 18 and 19
group by call.FromAreaCode , call.FromNum
having count(call.ToAreaCode,call.ToNum)>count(call.FromAreaCode , call.FromNum)