exam1.txt

SSD7 EXAM1 答案 不对的地方请指教 所有的答案

1.
(1)
SELECT A
FROM R

(2)
SELECT *
FROM R
WHERE B = 13

(3)
SELECT A, B
FROM R, S
WHERE R.C = S.D

2.
(1) 	
	ename     project        dept
	Kasper    Spreadsheet    Admin
	Mohan     Spreadsheet    Admin
	Lin       Forecast       Admin
	Lin       Spreadsheet    Admin
(2)
The operation r U s can not be performed, because the columns of table r and s are not equal.

(3)
Πdept(r) - Πdept(s) = NULL

4.
(1) 
  alter table orders
  alter Received set not null;

(2)
  alter table orders
  add check((Shipped set not null) AND ("shipped">"received") Or Shipped set null);

5.
(1)
select pno from part where unitprice between 10 and 25

(2)
select invoice.pno,sum(invoice.qty) as sumqty from invoice,orders
where invoice.ono = orders.ono and orders.received like '%98'
group by invoice.pno
order by sumqty asc;

(3)
select * from part
where pno not in( 	select invoice.pno from orders,invoice 
                where orders.ono = invoice.ono and orders.received  like '%98'
);


6.
create table employee 
       (id      id_dom   not null,
	name    name_dom,
        salary  salary_dom,
        dname   dept_name_dom
       );

	create table department
       (name   dept_name_dom  not null,
        mgrid  id_dom ,
        budget budget_dom
      );

	alter table employee
        add constraint empk primary key(id)
	check(dname in (select name from department));

	alter table department
	add constraint depk primary key(name)
	add constraint depf foreign key(mgrid) references employee(id);