Access 期末考範例與解答

Access 期末考範例 (未參加MOS考試者)

Access 練習題

一、請完成下列題目之要求

(1)    建立物料單資料表(Material)，欄位有[物料編號 NO] ]為5個字元，[名稱 Name ]為20個字元，[尺寸(單位為公分) Size ]為整數，和顏色 (Color)為2個字元。

(2)    新增一筆資料到物料資料表，名稱為Screw，尺寸5cm，顏色BK和物料編號A001；
再新增一筆資料到物料資料表，名稱為Nuts，尺寸1cm，顏色RD和物料編號A002
(3)    更新前述資料之尺寸為10cm，顏色為WH

(4)    查詢資料表中尺寸大於等於10cm且顏色為WH的物料

(5)    刪除資料表中尺寸小於10cm的物料

DELETE  *

FROM Material

WHERE Size >=10;

二、有三個Tables 如下，請標明題號寫出下列SQL執行完成的結果；各題獨立。

SP 資料表(Relation)為供應商(S)，產品編號(P)，供應量(QTY)

SS  資料表為供應商(S)，廠商地址(Addr)

PP  資料表為產品編號(P)，成本價格(Cost)

(SP 中的 P 需與 PP 中的 P 具有參考完整性；SP 中的 S 需與 SS 中的 S 具有參考完整性)

1.      SELECT S AS 供應廠商編號, P, QTY FROM SP WHERE S='1';

2.      SELECT S, SUM(Qty) AS Total3 FROM SP GROUP BY S;

3.      SELECT *  FROM (SELECT SP.S, SUM(SP.QTY*PP.Cost) AS Total4

FROM SP, PP

WHERE SP.P = PP.P AND S < ‘2’

GROUP BY SP.S)

4.      SELECT P, QTY  FROM SP  ORDER BY P DESC;

5.      SELECT *  FROM (SELECT SP.S, SUM(SP.QTY*PP.Cost) AS Total6

FROM SP INNER JOIN PP ON SP.P = PP.P

GROUP BY SP.S)

ORDER BY Total6 DESC;

6.      SELECT P, Cost  FROM PP  WHERE P NOT IN

(SELECT DISTINCT P FROM SP);

7.      SELECT SP.S,SUM(Qty*cost) AS Total  FROM SP , PP

WHERE SP.P=PP.P  GROUP BY SP.S  ORDER BY Total;

8.      SELECT S, Addr FROM SS  WHERE S IN (SELECT DISTINCT S FROM SP);

9.      SELECT S, Addr  FROM SS  WHERE S IN (SELECT DISTINCT S FROM SP

WHERE  P<='20');

10.  SELECT P  FROM PP  WHERE P NOT IN (

SELECT DISTINCT P

FROM SP);

三、Model一家小型工廠的庫存系統

至少以下四種entity types: 進貨單、領料單、物料、供應商

進貨單：工廠進原料時須填進貨單。每一張進貨單有進貨單編號和日期，並記載數筆明細，每一明細記載一種物料和其進貨數量。

領料單：工人領料製造時須填領料單。每一張領料單有領料單編號和日期，並記載數筆明細，每一明細記載一種物料和其領料數量。

物料：倉庫裡有數百種物料。每一物料有名稱，尺寸，顏色和物料編號。請注意該公司是一小型工廠，故物料編號乃沿用該物料供應商的物料編號，因此物料編號可能重複。但因每一種物料只由一供應商提供，因此供應商和物料編號的組合便是唯一。

供應商：供應商供應物料。每一供應商有供應商編號，公司名稱，住址，電話，傳真，負責人姓名。請注意一供應商可能不只一個電話號碼。

請以「北風」資料庫為例，以SQL查詢語法寫出以下之查詢(答案請寫在空白處): 
1. 查詢全世界前5大人口最多的首都名字(city.name),國家名字(country.name)及首都人口數(population)。

SELECT c.name, cy.name,cy.population
FROM country c, city cy
where c.capital=cy.id
order by cy.population desc
limit 5;
2. 查詢國家語言(Countrylanguage)中國碼(countrycode)為”SGP”的所有欄位資料。 

SELECT *
FROM countrylanguage
where countrycode = "sgp";
3. 查詢亞洲(asia)及歐洲(europe)GNP的總額，請顯示洲名及將GNP的總額別名為TotalGNP。

SELECT continent, sum(gnp) as TotalGNP
FROM country
where continent = "asia" or continent = "europe"
group by continent;
4. 查詢城市(City)資料中人口(Population)超過9000000的所有欄位資料。 

SELECT *
FROM city
where population >9000000;
5. 單一地顯示出全世界所有的官方語言(isofficial為T)名稱，語言名稱(language)請勿重覆。

…等等 
SELECT distinct language
FROM countrylanguage
where isofficial = "T";
6. 查詢各洲中(continent)的國家的個數, 請顯示洲名及國家的個數。 

SELECT continent, count(*)
FROM country
group by continent;
7. 查詢國碼(countrycode)為USA城市名稱(name)，人口數(population)。 

…等等 
SELECT name, population
FROM city
where countrycode = "usa";
8. 查詢全世界最晚獨立(IndepYear)的國家名稱(Name)及獨立年份(IndepYear)。 

SELECT name , indepyear
FROM country
order by indepyear desc
limit 1;
9. 查詢中東(Middle East)地區(Region)的國家名稱及國家型態(GovernmentForm)。 

…等等 
SELECT name, governmentform
FROM country
where region ="middle east"
10. 查詢中東(Middle East)地區(Region)的地區名稱，及表面積(Surfacearea)之總合(請將其別名為TotalArea)。 

SELECT region, sum(surfacearea) as totalarea
FROM country
where region ="middle east"
group by region;




五、
利用 world 範例資料庫練習設計共計至少20 種 SQL Query 其中包括:

1. 10 種inner join 之多表格結合。
   Ex. 
   所有歐洲的城市名稱, 及城市人口數。
   SELECT c.name, c.population
   FROM city c, country cn
   where c.countrycode = cn.code and cn.continent = "europe";
   Ex.
   顯示出語言中有包含"Chinese"字串的國名(country name), 語言(Language), 是否為官方語(IsOffical), 百分比(Percentage); 依百分比由大 到小排序 。
   SELECT c2.name, c.language, c.IsOfficial, c.Percentage
   FROM countrylanguage c, country c2
   where c.countrycode = c2.code and c.language like "%chinese%"
   order by percentage desc;
2. 8 種聚合函數使用範例。
   Ex.
   各洲人口統計，顯示出洲名及人口數，依洲名排序。
   SELECT continent, sum(population)
   FROM country
   group by continent
   order by continent;
3. 5 種子查詢之運用。
   Ex.
   單一的顯示出在北美洲(North America) 所說的官方(IsOfficial= true)語言名稱(Language)。
   SELECT distinct language
   FROM countrylanguage
   WHERE isofficial = true and countrycode in (SELECT Code
   FROM country c
   WHERE Continent = "North America");

全部共 0則留言
留言...
登入帳號密碼代表遵守學術網路規範