zhengzhe_exam1.txt

ssd7考试exam1,绝对有用~欢迎下载

1.1
select distinct A 
from R;

1.2
select *
from R
where B = 13;

1.3
select distinct A,B
from R,S
where R.C in (select D from S);

2.1
ename  	project  	dept
Kasper 	Spreadsheet 	Admin
Mohan 	Spreadsheet 	Admin
Lin 	Forecast 	Admin
Lin 	Spreadsheet 	Admin

2.2
This operation cannot be performed because of each UNION query must have the same number of columns, which means they must have union compatiblily.

2.3
dept

(The rusult is a null table)

4.1
alter table ORDERS
	alter Received set not null;

4.2
alter table ORDERS
	add check (Shipped = NULL or Shipped > Received);

5.1
select Pno
from PART
where UnitPrice between 10.00 and 25.00;

5.2
select i.Pno, sum(i.Qty), sum(i.BilledPrice)
from INVOICE i, ORDERS o
where i.Ono = o.Ono 
and o.Received > TO_DATE('01-01-98','DD-MM-YY')
and o.Received < TO_DATE('31-12-98','DD-MM-YY')
group by i.Pno
order by sum(i.BilledPrice);

5.3
select distinct p.*
from PART p, INVOICE i
where p.Pno = i.Pno 
and p.Pno not in(select ii.Pno
		from ORDERS oo, INVOICE ii
		where ii.Ono = oo.Ono 
		and oo.Received > TO_DATE('01-01-98','DD-MM-YY')
		and oo.Received < TO_DATE('31-12-98','DD-MM-YY')
);

6.
alter table EMPLOYEE
	add foreign key (dname) references DEPARTMENT(name);