构成(产品号，零件号，数量) 五、设计题（24分，每小题3分）
1. SELECT Lno,Book FROM Lesson WHERE Lname=’数据库系统’; 2. SELECT Sno,Sname,Student.Cno,C1.Speciality FROM Class AS C1,Student,Class AS C2
WHERE C1.Monitor=Sno AND Student.Cno=C2.Cno; 3. SELECT E1.Tid,E1.LNo
FROM Election AS E1,Election AS E2 WHERE E1.Year=2005 AND E1.Tid=E2.Tid
AND E1.LNo<>E2.Lno AND E2.Year=2005;
4. INSERT INTO Election VALUES(‘030611’,’06022505’,null,2005,’上’);
5. UPDATE Lesson SET Book=’数据库系统基础教程’ WHERE LNo=’0602205’; 6. CREATE VIEW V1 AS
SELECT SNo,Sname,Gender,Cno,Score FROM Student,Grade,Class,Lesson
WHERE Speciality=’计算机系’ AND Student.Cno=Class.Cno
AND Student.SNo=Grade.SNo AND Grade.LNo=Lesson.LNo AND Lname=’数据库系统’;
7.SELECT Teacher.Tno,Tname,COUNT(LNo),SUM(Score) AS ScoreAmount FROM Teacher,Election,Lesson
WHERE Teacher.Tno=Election.Tno AND Election.LNo=Lesson.LNo AND Year=2005 AND Department=’计算机系’
GROUP BY Teacher.Tno,Tname
ORDER BY ScoreAmount; 8.SELECT Class.Cno,Speciality,COUNT(DISTINCT SNo) FROM Class,Student
WHERE Class.Cno=Student.Cno AND Cno IN
(SELECT Cno
FROM Election,Lesson WHERE Lname=’数据库系统’ AND
Election.LNo=Lesson.LNo) AND Cno NOT IN
(SELECT Cno FROM Election,Lesson
WHERE Lname=’数据库系统’ AND Election.LNo=Lesson.LNo) GROUP BY Class.Cno,Speciality;
六、综合题（16分，第1题4分，第2题3分，后面每小题3分） 1．
empididnonamecustidnameprovnameas headerDepartmendeptidworks forSalesmanganderbirthphoneCustomercityphoneunitsales forsigndateordernoSalesorderProductprodidfactorydescpricespeccontainstypelinenosalescostSalesitemquartityunit of
2．数据库模式
Dpartment(id，name，head_id，desc)
Salesman(empid，name，idNo，gender，birthdate，phone，dept_id) Customer(id，name，prov，city，unit_name，phone) Product(manufactory，type，spec，desc)
Sales_order(order#，signdate，sales_id，cust_id)
Sales_item(order#，line#，manufactory，type，spec，quantity，single_price) 外键
Dpartment(head_id) →Salesman(empid) Salesman(dept_id) →Dpartment(dept_id) Sales_order(sales_id) →Salesman(empid) Sales_order(cust_id) →Customer(id)
Sales_item(order#) →Sales_order(order#)
Sales_item(manufactory，type，spec) →Product(manufactory，type，spec) 3. ⑴ 先定义视图V1 create view V1 as
select sales_order.order#,signdate,sum(quantity) as qty,sum(quantity*single_price) as COST
from sales_order key join item
group by sales_order.order#,signdate order by cose desc 再计算
select order# from V1
where cost=(select max(cost) from V1)
⑵ select empid,name,sum(quantity*single_price) as cost
from Salesman, left Outer join(Sales_order,key join Sales_item)
group by empid,name ⑶ select empid,name from Salesman
where empid=(select head_id
from Salesman,department
where dept_id=id and empid=”S0025 )