[SQL] 北風資料庫 練習

1. USE northwind  
2. GO
4. -- 飲料庫存總量
5. SELECT c.CategoryName, SUM(p.UnitsInStock) 庫存總量, avg(p.UnitsInStock) 平均數
6. FROM Categories c
7. INNER JOIN Products p ON p.CategoryID = c.CategoryID
8. WHERE c.CategoryName = 'Beverages'
9. GROUP BY c.CategoryName
10. 
    
11. --各種類的庫存狀況
12. SELECT c.CategoryName [種類], p.ProductName [物品名稱], p.UnitsInStock [庫存]
13. FROM Products p
14. INNER JOIN Categories c ON p.CategoryID = c.CategoryID
15. WHERE c.CategoryName = 'Beverages'
17. --農產品庫存 + 平均庫存 + 最大最小平均差
18. SELECT c.CategoryName, SUM(p.UnitsInStock) AS 庫存, avg(p.UnitsInStock) AS 平均, MAX(p.UnitsInStock) 最大,MIN(p.UnitsInStock) 最小, STDEV(p.UnitsInStock)
19. FROM Categories c
20. INNER JOIN Products p ON c.categoryID = p.categoryID
21. WHERE c.CategoryName = 'Produce'
22. GROUP BY c.CategoryName
24. -- 1996年 銷售總額
25. SELECT c.CategoryName 類別, ROUND(SUM(od.Quantity * (1-od.Discount)* od.UnitPrice),2) 銷售總額
26. FROM Categories c
27. JOIN Products p ON p.CategoryID = c.CategoryID
28. JOIN [ORDER Details] od ON od.ProductID = p.ProductID
29. JOIN Orders o ON o.OrderID = od.OrderID
30. WHERE c.CategoryName = 'Beverages' AND Datepart(YEAR,o.OrderDate)='1996'
31. GROUP BY c.CategoryName
33. -- 展示類別 (用group by)
34. SELECT c.CategoryName
35. FROM Products p
36. JOIN Categories c ON c.CategoryID=p.CategoryID
37. GROUP BY c.CategoryName
39. -- 展示類別 (用distinct)
40. SELECT DISTINCT c.CategoryName
41. FROM Categories c
43. --1996年 超過15比以上訂單 員工
44. SELECT e.EmployeeID [編號], e.LastName [名稱], COUNT(o.CustomerID) [訂單數]
45. FROM orders o
46. JOIN Employees e ON e.EmployeeID = o.EmployeeID
47. WHERE DATEPART(YEAR, o.OrderDate) = '1996'
48. GROUP BY e.EmployeeID, e.LastName
49. HAVING COUNT(o.CustomerID) > 14
50. ORDER BY e.EmployeeID
52. --1996年 超過15比以上訂單 員工
53. SELECT e.EmployeeID [編號], e.LastName [名稱], COUNT(*) [訂單數]
54. FROM orders o
55. JOIN Employees e ON e.EmployeeID = o.EmployeeID
56. WHERE o.OrderDate BETWEEN '1996-01-01' AND '1996-12-31'
57. GROUP BY e.EmployeeID, e.LastName
58. HAVING COUNT(*) > 14
59. ORDER BY e.EmployeeID
61. -- 各類別庫存
62. SELECT c.CategoryName [類別], SUM(p.UnitsInStock) [庫存], MIN(p.UnitsInStock) [最小庫存], MAX(p.UnitsInStock) [最大庫存]
63. FROM Products p
64. INNER JOIN Categories c ON p.CategoryID= c.CategoryID
65. GROUP BY c.CategoryName
67. --加拿大 員工與客戶訂單的統計
68. SELECT EmployeeID, CustomerID,  COUNT(OrderID) 訂單數量
69. FROM [Orders Qry]
70. WHERE country IN('Canada')
71. GROUP BY EmployeeID, CustomerID
72. WITH ROLLUP
74. --加拿大 員工與客戶訂單的統計 家小計總計
75. SELECT isnull(CONVERT(nvarchar,EmployeeID),N'總計') AS EmployeeID, ISNULL(CustomerID, N'小計') AS CustomerID, COUNT(OrderID) 訂單數量
76. FROM [Orders Qry]
77. WHERE country IN('Canada')
78. GROUP BY EmployeeID, CustomerID
79. WITH ROLLUP
81. --加拿大 員工與客戶訂單的統計 家小計總計
82. SELECT isnull(CONVERT(nvarchar,EmployeeID),N'總計') AS EmployeeID, ISNULL(CustomerID, N'小計') AS CustomerID, COUNT(OrderID) 訂單數量
83. FROM [Orders]
84. WHERE shipcountry IN('Canada')
85. GROUP BY EmployeeID, CustomerID
86. WITH ROLLUP
88. -- 員工訂單 + 業績
89. SELECT e.EmployeeID, e.LastName, COUNT(o.OrderID) [訂單],ROUND(SUM( od.Quantity * ( 1 - od.Discount) *od.UnitPrice),0) 業績
90. FROM Employees e
91. JOIN Orders o ON o.EmployeeID=e.EmployeeID
92. JOIN [ORDER Details] od ON od.OrderID = o.OrderID
93. GROUP BY e.EmployeeID, e.LastName

2/8

1. USE northwind
2. GO
3.  
4.  
5. --查詢客戶訂單及供應商的資料，只會顯示有有訂單的客戶
6. SELECT DISTINCT c.CompanyName AS customers, p.ProductName , s.CompanyName 供應商
7. FROM Customers c
8. JOIN Orders o ON c.CustomerID=o.CustomerID
9. JOIN [ORDER Details] od ON o.OrderID = od. OrderID
10. JOIN Products p ON od.ProductID = p. ProductID
11. JOIN Suppliers s ON p.SupplierID=s.SupplierID
12. GROUP BY c.CompanyName, p.ProductName, s.CompanyName
13.  
14. --同上 (錯誤)但包含沒有客戶訂單的資料
15. SELECT c.CompanyName, s.CompanyName
16. FROM Customers c
17. LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
18. JOIN [ORDER Details] od ON od.OrderID = o.OrderID
19. JOIN Products p ON p.ProductID = od.ProductID
20. JOIN Suppliers s ON s.SupplierID = p.SupplierID
21. GROUP BY c.CompanyName,s.CompanyName
22. ORDER BY s.CompanyName
23.  
24. --同上 但包含沒有客戶訂單的資料
25. SELECT c.CompanyName, s.CompanyName
26. FROM Customers c
27. LEFT JOIN ( Orders o
28. JOIN [ORDER Details] od ON od.OrderID = o.OrderID
29. JOIN Products p ON p.ProductID = od.ProductID
30. JOIN Suppliers s ON s.SupplierID = p.SupplierID )
31.  ON o.CustomerID = c.CustomerID
32. GROUP BY c.CompanyName,s.CompanyName
33. ORDER BY s.CompanyName
34.  
35. --連結自身 找出住在相同城市的員工
36. SELECT a.EmployeeID, a.FirstName, a.City, b.EmployeeID, b.FirstName, b.City
37. FROM Employees a
38. JOIN Employees b ON a.City = b.City
39. WHERE a.EmployeeID > b.EmployeeID
40.  
41. --self join 員工與主管關係表 (示範用right 顯示右邊b 資料表基底的全部員工ID，請與下面參照)
42. SELECT a.EmployeeID, a.FirstName, b.EmployeeID [主管ID], b.FirstName
43. FROM Employees a
44. RIGHT JOIN Employees b ON  b .EmployeeID = a.ReportsTo
45.  
46. --self join 員工與主管關係表 (正確)
47. SELECT a.EmployeeID, a.FirstName, b.EmployeeID [主管ID], b.FirstName
48. FROM Employees a
49. LEFT JOIN Employees b ON  b .EmployeeID = a.ReportsTo
50.  
51. SELECT MAX(OrderDate) FROM Orders
52. SELECT MIN(OrderDate) FROM Orders
53.  
54.  
55. --最早的第一筆訂單產品 與最新的一筆訂單產品 (訂單編號 + 訂單日期 + 產品)
56. SELECT
57. o.OrderID 訂單編號,
58. CONVERT(VARCHAR, o.OrderDate, 111) 訂單日期,
59. p.ProductName 產品
60. FROM Orders o
61. JOIN [ORDER Details] od ON o.OrderID = od.OrderID
62. JOIN Products p ON p.ProductID = od.ProductID
63. WHERE o.OrderDate = (SELECT MAX(OrderDate) FROM Orders)
64. OR  o.OrderDate = (SELECT MIN(OrderDate) FROM Orders)
65. ORDER BY o.OrderDate

給新人的一些話~

來源 : mark shu 版主

實際去應徵或去工作最實在，當然自己平日空檔時間要把基礎不斷加強，但要加強到百分之百，要等到何年何月，從工作上去學習實務是最快的，還有一點很重要，很多東西是在工作上遇到才去學的，學習要變成一種習慣，即使進入職場再多年也是一樣，
技術的範圍很廣，新的技術也不會停止出現，要等到自己認為可以了，可能永遠也不會有那一天，因為新的技術會一直出現，所以心態準備好了，就可以準備上戰場了，剛開始務必從個踏實的新兵做起，沒什麼好等待的.

[C#] 不重複的52個數字

說明 :
利用IndexOf() 方法 確認容器裡面不要有相同的數值
然後Add() 加到最後一個位置
ps. 後面p1 ...p4 就是分配給四個人(有點像發牌XD)

如果有更好、更好簡易的算法，請指導一下，謝謝。

1. List<int> ary = new List<int>();
2.             Random rnd = new Random();
3.             int num = 0;
4.             do
5.             {
6.                 num = rnd.Next(52);
7.                 if (ary.IndexOf(num) == -1)
8.                 {
9.                     ary.Add(num);
10.                 }
11.             } while (ary.Count < 52);
13.             List<int> p1 = ary.GetRange(0, 13);
14.             p1.Sort();
15.             List<int> p2= ary.GetRange(13, 13);
16.             p2.Sort();
17.             List<int> p3 = ary.GetRange(26, 13);
18.             p3.Sort();
19.             List<int> p4 = ary.GetRange(39, 13);
20.             p4.Sort();

[C#] Dictionary 練習(判斷文字內有幾個英文單字)

想法 :
1. 利用Dictionary 將英文單字與數量分別用Key,Value表示
2. 判斷字母大小寫
3. 改用SortedDictionary 自動遞增排序

請看範例 :

1. //判斷字串內有幾個單字
2.         static SortedDictionary<char,int> CountWord(string text)
3.         {
4.             SortedDictionary<char, int> Dic = new SortedDictionary<char, int>();
5.             char[] words = text.ToCharArray();
7.             foreach (char word in words)
8.             {
9.                 //去除非字母
10.                 if (char.IsLetter(word))
11.                 {
12.                     //轉小寫
13.                     char w = char.ToLower(word);
14.                     if (Dic.ContainsKey(w))
15.                     {
16.                         Dic[w]++;
17.                     }
18.                     else
19.                     {
20.                         Dic[w] = 1;
21.                     }
22.                 }
23.             }
24.             return Dic;
25.         }

1.  ///Ditionary 練習從片語中判斷出現幾次單字
2.             string text = @"Do you like green eggs and ham?
3.                            i do not like them, Sam-I-AM";
5.             SortedDictionary<char, int> frequency = CountWord(text);
7.             foreach (KeyValuePair<char,int> word in frequency)
8.             {
9.                 char key = word.Key;
10.                 int value = word.Value;
11.                 Console.WriteLine("{0} : {1}個", key, value);
12.             }

參考資料 : 精通C#

[C#] cast vs convert

直接看範例就知道嚕~

1. double d = 25.30;

2.             //無條件捨去 25
3.             int i = (int)(d+0.5);  
4.             //正確的四捨五入 26
5.             int j = Convert.ToInt32(d + 0.5);
6.             Console.WriteLine("i:" + i + " j: " + j);

[C#] ReferenceEquals() 是比較兩個是否為同一個位址

 //因為兩個i都是value type 所以要經過boxing 但是兩個在boxing成為新的獨立個體，如此兩個是分配到不同的heap，所以參考比較時並不指向相同的位址，所以回傳false

1.             i = 10;
2.             bool b = ReferenceEquals(i, i);
3.             Console.WriteLine(b);
4.             //因為兩個i都是value type 所以要經過boxing 但是兩個在boxing成為新的獨立個體，如此兩個是分配到不同的heap，所以參考比較時並不指向相同的位址，所以回傳false

如有錯誤，請指教，謝謝。