數(shù)據(jù)庫系統(tǒng)原理模擬試卷(四)-資料下載頁
2025-03-25 02:58本頁面
【正文】 nt=’計(jì)算機(jī)系’ GROUP BY ,Tname ORDER BY ScoreAmount。 ,Speciality,COUNT(DISTINCT SNo) FROM Class,Student WHERE = AND Cno IN(SELECT Cno FROM Election,Lesson WHERE Lname=’數(shù)據(jù)庫系統(tǒng)’ AND =) AND Cno NOT IN(SELECT Cno FROM Election,Lesson WHERE Lname=’數(shù)據(jù)庫系統(tǒng)’ AND =) GROUP BY ,Speciality。 六、綜合題（16分，第1題4分，第2題3分，后面每小題3分）1． 2．?dāng)?shù)據(jù)庫模式 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 ,signdate,sum(quantity) as qty,sum(quantity*single_price) as COST from sales_order key join item group by ,signdate order by cose desc 再計(jì)算 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 )
畢業(yè)設(shè)計(jì)相關(guān)推薦
鄂ICP備17016276號-1