2018-11-28資料庫作業，練習

-- a.請從表中查詢出名稱包含“企鵝”的所有商品
SELECT * FROM product WHERE NAME LIKE '%企鵝%';


-- b.查詢Cash第二貴的物品的Name
 SELECT NAME FROM product  where Cash=(select distinct Cash from product order by Cash desc limit 1,1)

-- c.請插入一條名稱為“角色卡”，Cash“100”的新資料
INSERT INTO product(NAME,Cash) VALUE('角色卡','100');

-- d.請更新“皇帝企鵝2代”的Cash，增加20
UPDATE product SET Cash=Cash+20 WHERE NAME='皇帝企鵝2代'; 

-- 1.用sql語句找出是計算機系並籍貫是北京的學生的所有記錄
條件：
-- 多表關聯
SELECT* FROM Test1
-- 普通欄位過濾 where
WHERE Department = '計算機系'AND place='北京'

答:
SELECT * FROM Test1 WHERE Department = '計算機系'AND place='北京';

• 1.用sql語句把計算機系的學生的Department改為資訊學院
  條件：
  -- 更新哪張表
  UPDATE Test1
  -- 修改那個欄位
  SET Department='資訊學院'
  -- 條件
  WHERE Department='計算機系'

答：
UPDATE Test1 SET Department = '資訊學院' WHERE Department = '計算機系';

-- 3.用sql連線查詢找出成績大於等於75分的學生的No,Name,Grade,Courses欄位的記錄
條件：
-- 多表關聯
SELECT* FROM test1 a JOIN Test2 b ON a.No=b.No
-- 普通欄位過濾
WHERE Grade>=75
-- 展示欄位
a.no,a.Name,b.Grade,b.Courses

答：
SELECT a.no,a.Name,b.Grade,b.Courses
FROM TEST1 a JOIN Test2 b ON a.No=b.No
GROUP BY NAME
WHERE Grade>75

-- 4.用sql連線查詢找出總分最高的學生的No,Name，總分數
條件：
-- 多表關聯
SELECT* FROM test1 a JOIN Test2 b ON a.No=b.No
-- 分組group by
GROUP BY a.no
-- 排序
ORDER BY SUM(b.Grade)DESC
-- 使用limit
LIMIT 1
-- 展示欄位
a.no，a.name

答：
SELECT SUM(b.Grade)
FROM test1 a JOIN Test2 b ON a.No=b.No
GROUP BY a.no
ORDER BY SUM(b.Grade)DESC
LIMIT 1;

SELECT a.no,a.name
FROM test1 a JOIN Test2 b ON a.No=b.No
GROUP BY a.no
HAVING SUM(b.Grade)

-- 4.1根據商品價格正序/倒序排列
SELECT prod_price FROM products ORDER BY prod_price ;
SELECT prod_price FROM products ORDER BY prod_price DESC;

-- 4.2選擇商品價格最高的商品prod_id及vend_id
SELECT MAX(prod_price) FROM products;
SELECT prod_id,vend_id FROM products WHERE prod_price IN (SELECT MAX(prod_price) FROM products);

-- 4.3統計每個供應商的商品總價大於20的資料，並倒序排列
SELECT vend_id,SUM(prod_price)FROM products GROUP BY vend_id HAVING SUM(prod_price)>20

-- 4.4根據vent_id關聯兩張表，選出每個供應商的商品價格最高的資料vent_id，vend_name，prod_id，prode_name，prod_price
SELECT vend_id,MAX(prod_price)FROM products GROUP BY vend_id;
SELECT (a.vend_id,b.vend_name,a.prod_id,prod_name,prod_price)
FROM products a JOIN vendors b ON a.vend_id=b.vend_id
GROUP BY a.vend_id 
ORDER BY a.prod_price DESC;

1. SELECT * FROM A a LEFT JOIN B b ON a.Num=b.Num;
   SELECT * FROM A a RIGHT JOIN B b ON a.Num=b.Num;
-- 1.查詢Level是L2的所有員工的Name和Department資訊。如果有Grade資訊，把Grade資訊也查詢出來，並按照Department升序排序。
SELECT NAME,Department
FROM Employeeinfo,Department
WHERE ID=EmployeeID,LEVEL='L2'
GROUP BY Department ASC;

-- 多表關聯
SELECT*FROM Employeeinfo a JOIN Department b ON a.eid=b.pid
-- 對欄位過濾
WHERE a.elevel='L2'
-- 排序
ORDER BY a.edepartemt
-- 展示欄位
a.ename,a.edepartment,b.pgrade
-- 拼裝成sql語句
SELECT a.ename,a.edepartment,b.pgrade FROM Employeeinfo a

-- 2.更新所有Department=CS的員工的所有績效成績為D。
條件 :
-- 更新哪張表
performance
-- 更新那個欄位
SET pgrade='D'
-- 過濾條件
WHERE edepartment='CS'
-- 發現不在同一張表中，用巢狀查詢
-- 1.只能根據上述的條件找到查詢出跟另一張表有關係的欄位
SELECT eid FROM Employeeinfo WHERE edepartment='CS'
-- 2.拿出查詢出的結果，根據關聯欄位寫更新條件
WHERE pid IN (SELECT eid FROM Employeeinfo WHERE edepartment='CS')

答：update performance SET pgrade='D'WHERE pid IN (SELECT eid FROM Employeeinfo WHERE edepartment='CS')
-- 3.統計有2次績效成績為A的員工Name和Department資訊
條件：
-- 多表關聯
SELECTFROM Employeeinfo a JOIN Performance b ON a.eid=b.pid
-- 分組，分組欄位name
GROUP BY a.ename
-- 聚合函式過濾
HAVING COUNT()=2
-- 普通欄位篩選
WHERE pgrade='A'
-- 展示欄位Name Department
ename,edepartment
-- 拼裝成sql
SELECT ename,edepartment FROM Employeeinfo a JOIN Performance b ON a.eid=b.pid WHERE pgrade='A'

-- 1.用sql語句找出是計算機系並籍貫是北京的學生的所有記錄
條件：
-- 多表關聯
SELECT* FROM Test1
-- 普通欄位過濾 where
WHERE Department = '計算機系'AND place='北京'

答:
SELECT * FROM Test1 WHERE Department = '計算機系'AND place='北京';

-- 2.用sql語句把計算機系的學生的Department改為資訊學院
條件：
-- 更新哪張表
UPDATE Test1
-- 修改那個欄位
SET Department='資訊學院'
-- 條件
WHERE Department='計算機系'

答：
UPDATE Test1 SET Department = '資訊學院' WHERE Department = '計算機系';

-- 3.用sql連線查詢找出成績大於等於75分的學生的No,Name,Grade,Courses欄位的記錄
條件：
-- 多表關聯
SELECT* FROM test1 a JOIN Test2 b ON a.No=b.No
-- 普通欄位過濾
WHERE Grade>=75
-- 展示欄位
a.no,a.Name,b.Grade,b.Courses

答：
SELECT a.no,a.Name,b.Grade,b.Courses
FROM TEST1 a JOIN Test2 b ON a.No=b.No
GROUP BY NAME
WHERE Grade>75

-- 4.用sql連線查詢找出總分最高的學生的No,Name，總分數
條件：
-- 多表關聯
SELECT* FROM test1 a JOIN Test2 b ON a.No=b.No
-- 分組group by
GROUP BY a.no
-- 排序
ORDER BY SUM(b.Grade)DESC
-- 使用limit
LIMIT 1
-- 展示欄位
a.no，a.name

答：
SELECT SUM(b.Grade)
FROM test1 a JOIN Test2 b ON a.No=b.No
GROUP BY a.no
ORDER BY SUM(b.Grade)DESC
LIMIT 1;

SELECT a.no,a.name
FROM test1 a JOIN Test2 b ON a.No=b.No
GROUP BY a.no
HAVING SUM(b.Grade)

-- 1.查詢書名“B”開頭且2014年之後購入的書籍清單，顯示欄位 barcode,name,amount,date
條件：
-- 多表關聯
SELECT*FROM A JOIN B ON a.Barcode=b.Barcode
-- 普通欄位過濾
WHERE a.name LIKE 'B%'AND DATE>='2014-01-01'
-- 展示欄位
a.barcode,a.name,b.amount,b.date

答：
SELECT a.barcode,a.name,b.amount,b.date FROM A JOIN B ON a.Barcode=b.Barcode WHERE a.name LIKE 'B%'AND DATE>='2014-01-01';

-- 2.統計每部書的總數量，顯示欄位：barcode,name,總量
條件：
-- 多表關聯
SELECT*FROM A JOIN B ON a.Barcode=b.Barcode
-- 分組group by
GROUP BY A.barcode
-- 展示欄位
a.barcode,a.NAME,SUM(Amount)

答：
SELECT SUM(Amount),a.barcode,a.NAME FROM A JOIN B ON a.Barcode=b.Barcode GROUP BY barcode SUM(Amount)

-- 3.統計購入次數大於1次的書，顯示欄位：barcode,name,購入次數
SELECT barcode,NAME
FROM A JOIN B ON a.Barcode=b.Barcode
GROUP BY a.Barcode
HAVING COUNT()>1;
-- 4.查詢表中共有多少條操作記錄
SELECT COUNT() FROM Audit;
-- 5.查詢最近的10條操作記錄的內容
SELECT * FROM Audit ORDER BY CreateTime DESC LIMIT 10;