<h1>
期中考1
</h1>
<h2>
第一題
</h2>
<pre>
<strong>
1. 請找出 skill_ test 資料庫中，Students 表共有幾個欄位及各個欄位名（10%）
（1）共X個欄位，欄位名（手打）（10%）
</strong>
</pre>

select * from skill_test.students
共10欄位
學號 班級座號 姓名 出生年月日 身分證號碼 住址 家長 電話 科別 畢業國中

<hr>
<h2>
第三題
</h2>
<pre>
<strong>
3.資料庫northwind，資料表 orders，找出付款方式（payment_type）是 Credit Card
列出收件人（ship_name），收件地址（ship_address）及訂購日期（order_date）（10%）
</strong>
</pre>

select ship_name , ship_address , order_date 
from northwind.orders where payment_type = 'Credit Card'

<hr>
<h2>
第四題
</h2>
<pre>
<strong>
4.資料庫 skill_ test，資料表 students，找出畢業國中是中山國中且是男生的學生，
列出"學號"，"姓名"，"身份證"，"畢業國中"（15%）
</strong>
</pre>

select * from skill_test.students 
where 畢業國中 like '%中山國中%' and 身分證號碼 like '_1%'

<hr>
<h2>
第五題
</h2>
<pre>
<strong>
5.資料庫 skill_test， 資料表 sales，交易時間88年4~10月間交易，
交易數量為前5名的資料，列出所有欄位（15%）
</strong>
</pre>

select * from skill_test.sales 
where 交易年= 88 and 交易月 between 4 and 10 order by 數量 desc limit 5;

<hr>
<h2>
第六題
</h2>
<pre>
<strong>
6.資料庫 skill_test，資料表 students，列出全校所有科別（不重複），
統計出科別數（10%） *做2個sql
</strong>
</pre>

select distinct 科別 from skill_test.students;
select count(distinct 科別) from skill_test.students; 

<hr>
<h2>
第七題
</h2>
<pre>
<strong>
7.資料庫 skill_test，資料表 records，統計有請假的學生，各假別的總和，
並列出請公假最多的前5名學生
列出所有欄位（20%）
</strong>
</pre>

select 班級座號, 年月日, sum(公假), sum(事假), sum(病假) 
from skill_test.records 
group by 班級座號 having  (sum(公假)+sum(事假)+sum(病假)) > 0
order by sum(公假) desc limit 5;

<br>
<h1>
期末考
</h1>
<h2>
第一題
</h2>

<pre>
<strong>
01.乙檢-查詢業務部門有哪些員工，及主管是誰
1.使用 where（10%）
2.使用 inner join （10%）
</strong>
</pre>
<span style='color: tomato'>資料表：員工資料表（employee）&部門資料表（dept）</span>
<span style='color: green'>顯示欄位：姓名，部門名稱，主管姓名</span>
<span style='color: blue'>Hint：上課題目，只多了"業務部門”條件限制一使用like</span>
<span>使用where</span>

select e.姓名 , d.部門名稱 , d.主管姓名 
from employee e , dept d 
where e.部門代號 = d.部門代號 and d.部門名稱 like '業務%'

<span>使用inner join</span>

select e.姓名 , d.部門名稱 , d.主管姓名 
from employee e 
join dept d on e.部門代號 = d.部門代號 and d.部門名稱 like '業務%'

<hr>
<h2>
第二題
</h2>

<pre>
<strong>
02.乙檢-查詢業務部門各部門在90年業績總額（銷售總額）
40%
</strong>
</pre>
<span style='color: tomato'>資料表：員工資料表（employee）&部門資料表（dept）&銷售資料表（sales）&產品資料表（product）</span>
<span style='color: green'>顯示欄位：部門名稱，產品名稱，銷售額</span>
<span style='color: blue'>Hint：員工資料表和部門資料表先join，銷售資料表和產品資料表join 再將2個結果join.要 group by，要 sum</span>

select ed.部門名稱, p.產品名稱 , sum(s.數量 * p.單價) as 銷售額 
from sales s join product p on s.產品代號 = p.產品代號
join (select e.姓名 , e.部門代號 , d.部門名稱, d.主管姓名  from employee e join dept d on e.部門代號 = d.部門代號) as ed on ed.姓名 = s.業務姓名 
where 交易年 = 90 group by 部門名稱;

<hr>
<h2>
第三題
</h2>

<pre>
<strong>
03. 乙檢-查詢業務在91年整年度的對各個不同的客戶的業續總額，一個客戶一筆統計資料，
最後先按業務姓名排序，再按鎖售總額排序
1.使用 where （15%）
2.使用 innerjoin （15%）
</strong>
</pre>
<span style='color: tomato'>資料表：銷售資料表（sales1）&產品資料表（product） &客戶資料表（customer）</span>
<span style='color: green'>顯示欄位：寶號，業務姓名，產品名稱，銷售額</span>
<span>使用where</span>

select c.寶號,s1.業務姓名 , p.產品名稱  ,s1.數量_91年 * p.單價 as 銷售額
from customer c, sales1 s1, product p
where c.客戶代號 = s1.客戶代號 and p.產品代號 = s1.產品代號
order by 業務姓名, 銷售額;

<span>使用inner join</span>

select c.寶號 , s1.業務姓名, p.產品名稱 , s1.數量_91年 * p.單價 as 銷售額
from customer c 
join sales1 s1 on c.客戶代號 = s1.客戶代號 
join product p on p.產品代號 = s1.產品代號 
order by 業務姓名, 銷售額;

<hr>
<h2>
第四題
</h2>

<pre>
<strong>
04.乙檢-查詢某班級，學生整學期缺曠總數大於10節
座號尾數1號找101 班，2號找102 班，依此類推，33號找103班，10,20,30號為110班
對缺曠總數排序（+5%）
</strong>
</pre>
<span style='color: tomato'>資料表：請假記錄資料表（records）&學生基本資料表（students）</span>
<span style='color: green'>顯示欄位：班級座號，姓名，曠課總數，家長，住址</span>
<span style='color: blue'>Hint：上課題目，過濾使用 like，總數是用 group by 出來的，所以過濾條件使用 having
（20%）</span>
<span>注意班級 這裡使用110班作為舉例</span>

select r.班級座號 , s.姓名 , sum(r.曠課) as 曠課總數, s.家長 , s.住址 
from records r , students s where r.班級座號 = s.班級座號 
group by 班級座號 having 班級座號 like '110%' and 曠課總數 > 10
order by 曠課總數 desc ;

<hr>
<h2>
第五題
</h2>

<pre>
<strong>
05.乙檢-查詢業務在90年各產品的交易數量、交易額及賣給那位客戶，交易額由大到小排序
1.使用 where（10%）
2.使用 inner join （10%）
</strong>
</pre>
<span style='color: tomato'>資料表：交易資料表（sales）&產品資料表（product）&客戶資料表（customer）</span>
<span style='color: green'>顯示欄位：業務姓名，寶號，產品名稱，數量，交易額</span>
<span style='color: blue'>Hint：上課題目，新增了排序</span>
<span>使用where</span>

select s.業務姓名 , c.寶號 , p.產品名稱 , s.數量 , s.數量 * p.單價 as 交易額 
from sales s , product p , customer c 
where s.產品代號 = p.產品代號 and s.客戶代號 = c.客戶代號 and s.交易年 = 90
order by 交易額 desc ;

<span>使用inner join</span>

select s.業務姓名 , c.寶號 , p.產品名稱 , s.數量 , s.數量 * p.單價 as 交易額 
from sales s 
join product p on s.產品代號 = p.產品代號 and s.交易年 = 90
join customer c on s.客戶代號 = c.客戶代號 
order by 交易額 desc ;