exam_1.txt

来自「ssd7_exam1」· 文本 代码 · 共 57 行

1.
  1)  SELECT A 
      FROM R
  
  2)  SELECT *
      FROM R
      WHERE B = 13
  
  3)  SELECT A,B
      FROM R,S
      WHERE R.C = D.S

2.
  1)   ename      project         dept
       Mohan      Spreadsheet     Admin
       Lin  	  Forecast  	  Admin
       Lin  	  Spreadsheet  	  Admin

  2) This operation cannot be performed, because the number of columns of R is not equal with S.

  3) This operation cannot be performed, because there are nothing leave after this operation.

4.
  1) ALTER TABLE ORDERS
     ADD CONSTRAINT CONSTRAINT received_not_null
     SET Received NOT NULL

  2) ALTER TABLE ORDERS
     ADD CONSTRAINT CONSTRAINT ship_should_greater
     CHECK ( (Shipped IS NOT NULL) AND (Shipped > Received) ) IS NOT FALSE

5.
  1)  SELECT Pno FROM PART
      WHERE UnitPrice BETWEEN 10 AND 25

  2)  SELECT SUM(BilledPrice) AS total_bill
      FROM ORDERS, INVOICE
      WHERE ( ORDERS.Ono = INVOICE.Ono ) AND 
            ( ORDERS.Received BETWEEN TO_DATE('1998/01/01', 'YYYY/MM/DD') AND TO_DATE('1998/12/31','YYYY/MM/DD'))
      GROUP BY INVOICE.Ono
      ORDER BY total_bill
  
  3)  SELECT PART.*
      FROM PART
      WHERE PART.Pno IN
            (
                SELECT INVOICE.Pno
                FROM INVOICE, ORDERS
                WHERE (ORDERS.Pno = INVOICE.Pno) AND
                      ORDERS.Received < TO_DATE('1998/01/01', 'YYYY/MM/DD') OR
                      ORDERS.Received > TO_DATE('1998/12/31', 'YYYY/MM/DD')
            )

6. 
  ALTER TABLE EMPLOYEE 
  ADD CONSTRAINT CONSTRAINT employee_FK
  FOREIGN KEY (dname) REFERENCES DEPARTMENT(name)