ssd7_exam1.txt

ssd7卡耐基教程

1:
   <1>.  ΠA(R)

	SELECT A
	FROM R;

   <2>. σB = 13(R)
	
	SELECT *
	FROM R
	WHERE B=13;
	
   <3>. ΠA,B(R C = D S)

	SELECT R.A, R.B
	FROM R, S
	WHERE R.C = S.D;

2:
   <1>.  σdept = 'Admin' AND project <> 'Audit'(r)

	ename  	project  	dept

	Kasper  Spreadsheet  	Admin
	Mohan  	Spreadsheet  	Admin
	Lin  	Forecast  	Admin
	Lin  	Spreadsheet  	Admin

   <2>. r U s

	This function(r U s) can't be done, because these two relations don't have the same columns, relation r has 3 columns, while relation s has 2 columns.

   <3>. Πdept(r) - Πdept(s)

	dept

	NULL

4:
   <1>.   Received date cannot be undefined and that
	
	ALTER TABLE ORDERS
	ALTER Received SET NOT NULL;

   <2>. the Shipped date, if it is not NULL, should be greater than the Received date.
	
	ALTER TABLE ORDERS
	ADD CONSTRAINT orders_bigger CHECK ((Shipped > Received)  OR  (Shipped IS NULL));

5:
   <1>.  Get the part number of parts that cost between 10 and 25 dollars.

	SELECT Pno
	FROM PART
	WHERE UnitPrice BETWEEN 10.00 AND 25.00;

   <2>. For each part sold in 1998, list the total quantity sold in 1998. Sort your results in ascending order by the total billed price for each part for that year. Parts are considered sold when an order is received. The BilledPrice column in the Invoice table indicates the total price billed for that part (i.e., quantity sold multiplied by unit price minus discount, if any).
	
	SELECT Pno,SUM(Qty),SUM(illedPrice)
	FROM INVOICE
	WHERE Ono IN(SELECT Ono
	FROM ORDERS
	WHERE ORDERS.Received BETWEEN TO_DATE('1998/01/01', 'YYYY/MM/DD') AND TO_DATE('1998/12/31','YYYY/MM/DD'))
	GROUP BY Pno
	ORDER BY SUM(BilledPrice) ASC;
	
   <3>. Get those parts that were not sold in 1998. A part is considered sold when an order is received.

	SELECT DISTINCT Pno, Pname
	FROM PART
	WHERE Pno NOT IN(SELECT Pno 
                 FROM PART NATURAL JOIN INVOICE NATURAL JOIN ORDERS 
                 WHERE ORDERS.Received BETWEEN TO_DATE('1998/01/01', 'YYYY/MM/DD') AND TO_DATE('1998/12/31','YYYY/MM/DD'));

6:

	ALTER  TABLE EMPLOYEE
	ADD CONSTRAINT employeeKey Foreign KEY(dname) REFERENCES DEPARTMENT(name);