沙文_exam1.txt

吐血推荐 自己做的 SSD7 EXAM1 满分 呵呵 随便下了

1: (1)select A 
      from R

   (2)select *
      from R
      where B = 13

   (3)select R.A, R.B
      from R, S
      where R.C = S.D 


4:

(1) ename       project         dept     
    Kasper      Spreadsheet     Admin     
    Mohan       Spreadsheet     Admin
    Lin         Forecast        Admin    
    Lin         Spreadsheet     Admin

(2)  ERROR:  r U s query must have the same number of columns.The table r and s has           different number of columns.

(3)  return null


9: 

(1) alter table orders
    alter received set not null;

(2) alter table orders
    add constraint c check(shipped=null or shipped>received);

 

10:

(1) select pno
    from part
    where unitprice between 10.00 and 25.00;

(2) select part.pno,part.pname,part.unitprice,sum(invoice.qty) as qty,sum(invoice.billedprice)            as billedprice
    from part natural join orders natural join invoice
    where orders.received like '%98'
    group by part.pno,part.pname,part.unitprice
    order by sum(invoice.billedprice) asc;

(3) select part.pno,part.pname,part.unitprice
    from part 
    where part.pno not in
          (
            select invoice.pno
            from  orders natural join invoice
            where orders.received like '%98'
          );


11:

  alter table employee
  alter dname set not null;

  alter table employee
  add foreign key(dname) references department(name);
 
  ****************************************
  创建表employee和department代码如下：
  	create table EMPLOYEE 
	  (id      int primary key ,
	   name    varchar(10),
	   salary  int,
	   dname   varchar(10)
	   );
	
	create table DEPARTMENT
	   (
            name   varchar(10) primary key ,
            mgrid  int,
	    budget int
	    );
 ******************************************