exam1.txt

CMU_SSD7_Exam1_答案_by_郜珐理

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

Question 2.
1).	ename	project		dept
	Kasper	Spreadsheet	Admin
	Mohan	Spreadsheet	Admin
	Lin	Forecast	Admin
	Lin	Spreadsheet	Admin
2). The operation union can't perform because two tables are different in column.
3). The operation can be perform but no result return.

Question 4.
	CREAT TBBLE ORDERS
	(
	Ono INT NOT NULL,
	CId INT,
	Received DATE NOT NULL,
	Shipped DATE
		CONSTRAINT ShippedDate CHECK((Shipped > Received) IS NOT NULL),
	Primary Key (Ono)
	);

Question 5.
1).	SELECT PNo
	FROM PART
	WHERE UnitPrice BETWEEN(10, 25);
2).	SELECT SUM(INVOICE.Qty)
	FROM INVOICE, ORDERS
	WHERE INVOICE.Ono = ORDERs.Ono AND (ORDERS.Received >= TO_DATE('1998/1/1', 'YYYY/MM/DD') AND (ORDERS.Received <= TO_DATE'1998/12/31', 'YYYY/MM/DD')
	GROUP BY INVOICE, ORDERS
	ORDER BY SUM(INVOICE.BillPrice) ASC;
3).	SELECT PART.PNo, PART.Pname, PART.UnitPrice
	FROM PART, ORDERS, INVOICE
	WHERE PART.PNo = INVOICE.PNo AND ORDERS.Ono = INVOICE.Ono AND
	ORDERS.Received NOT IN	(SELECT ORDERS.Received
				FROM ORDERS
				WHERE Received BETWEEN TO_DATE('1997/12/31', 'YYYY/MM/DD') AND TO_DATE('1998/12/31', 'YYYY/MM/DD')
				);

Question 6.
ALTER TABLE EMPLOYEE
ADD dname SET NOT NULL,
ADD FORERGN KEY(dname) REFERENCES DEPARTMENT(name);