取得用於 ADO.NET 程式代碼範例的 SQL Server 資料庫 - ADO.NET | Microsoft Learn
因為拿到的題目不是我寫的，所以不放哈哈
練習答案沒有用AI,可能會錯
--
select * from Products;
--
select * from Products order by ProductName;
--
select ProductID, ProductName, UnitPrice from Products;
--
select ProductID AS [產品編號], ProductName AS [產品名稱], UnitPrice AS [單價] from Products;
--
select ProductID AS [品編號], ProductName AS [品名], UnitPrice AS [單價], UnitsInStock AS [庫存], UnitsOnOrder AS [在途數], ReorderLevel AS [再訂購量] from Products
where UnitsInStock < ReorderLevel
--
select ProductID AS [品編號], ProductName AS [品名], UnitPrice AS [單價], UnitsInStock AS [庫存], UnitsOnOrder AS [在途數], ReorderLevel AS [再訂購量] from Products
where (UnitsInStock+UnitsOnOrder) < ReorderLevel
--
select ProductName AS [品名] from Products
where ProductName like'%ku%'
--
select CategoryID AS [類別],ProductName AS [品名] from Products
-- where CategoryID = 1 or CategoryID =4 or CategoryID =8
where CategoryID in (1,4,8)
ORDER BY CategoryID,ProductName
--
select UnitPrice AS [單價],ProductName AS [品名] from Products
--
where UnitPrice >= 15 and UnitPrice<=20
ORDER BY UnitPrice DESC
--
--
select * from Products
--
select top 3 with ties ProductID AS [品編號],UnitPrice AS [單價],ProductName AS [品名] from Products
ORDER BY UnitPrice DESC
--
select avg(UnitPrice) AS [平均單價] from Products
--
select avg(UnitPrice) AS [指定類別平均單價] from Products
where CategoryID in (1,4,8)
--
select Products.CategoryID AS [類編號],CategoryName AS [類別],avg(UnitPrice) AS [各類平均單價] from Products
left join Categories on Products.CategoryID = Categories.CategoryID
GROUP BY Products.CategoryID,CategoryName
ORDER BY Products.CategoryID ASC
--
select top 3 with ties Products.CategoryID AS [類編號],CategoryName AS [類別],avg(UnitPrice) AS [平均單價] from Products
left join Categories on Products.CategoryID = Categories.CategoryID
GROUP BY Products.CategoryID,CategoryName
ORDER BY [平均單價] DESC
--
select top 1 with ties ProductID AS [品編號], ProductName AS [品名],UnitPrice AS [價格最高] from Products
ORDER BY [價格最高] DESC
--
select top 1 with ties ProductID AS [品編號], ProductName AS [品名],UnitPrice AS [價格最低] from Products
ORDER BY [價格最低] ASC
--
select ProductID AS [品編號], ProductName AS [品名], UnitPrice AS [價格最低]
from Products
where UnitPrice = (select min(UnitPrice) from Products);
-- select ProductID AS [品編號],
-- ProductName AS [品名],
-- Products.UnitPrice AS [價格最低]
-- from Products
-- join (
-- select min(UnitPrice) as MinPrice
-- from Products
-- ) m on Products.UnitPrice = m.MinPrice;
--
select * from products
-- ProductID AS '產品ID'
-- ProductName AS '產品名稱'
-- SupplierID AS '供應商ID'
-- CategoryID AS '類別ID'
-- QuantityPerUnit AS '單位數量'
-- UnitPrice AS '單價'
-- UnitsInStock AS '庫存數量'
-- UnitsOnOrder AS '在途訂單數量'
-- ReorderLevel AS '再訂購量'
-- Discontinued AS '已停產'
select * from Suppliers
-- SupplierID AS 供應商ID,
-- CompanyName AS 公司名稱,
-- ContactName AS 聯絡人姓名,
-- ContactTitle AS 聯絡人職稱,
-- Address AS 地址,
-- City AS 城市,
-- Region AS 地區,
-- PostalCode AS 郵遞區號,
-- Country AS 國家,
-- Phone AS 電話,
-- Fax AS 傳真,
-- HomePage AS 首頁
select * from Orders
-- OrderID AS 訂單編號,
-- CustomerID AS 客戶號,
-- EmployeeID AS 員工號,
-- OrderDate AS 訂單日期,
-- RequiredDate AS 所需日期,
-- ShippedDate AS 出貨日期,
-- ShipVia AS 出貨方式,
-- Freight AS 貨運,
-- ShipName AS 收貨人姓名,
-- ShipAddress AS 收貨地址,
-- ShipCity AS 收貨城市,
-- ShipRegion AS 收貨地區,
-- ShipPostalCode AS 收貨郵遞區號,
-- ShipCountry AS 收貨國家
select * from OrderDetails
-- 310 列出 以下欄位 ProductID, ProductName, SupplierID
SELECT ProductID AS [品編號], ProductName AS [品名], SupplierID AS [業務編號] from products
--
-- 左邊products有77行 右邊Suppliers有29行 保留左表所有行
SELECT ProductID AS [品編號], ProductName AS [品名], CompanyName AS [供應商],Phone AS [電話],ContactName AS [聯絡人],Suppliers.SupplierID AS [業務編號] from products left join Suppliers on
Suppliers.SupplierID = products.SupplierID
order by CompanyName
--
SELECT CompanyName AS [供應商],
Phone AS [電話],
ContactName AS [聯絡人],
ProductID AS [品編號],
ProductName AS [品名],
Suppliers.SupplierID AS [業務編號],
UnitsInStock AS [庫存量],
ReorderLevel AS [再訂購量],
CASE WHEN UnitsInStock < ReorderLevel THEN '需補貨' ELSE '庫存充足' END AS [庫存狀態]
from products left join Suppliers on
Suppliers.SupplierID = products.SupplierID
where UnitsInStock < ReorderLevel
order by CompanyName
--
--我先根據訂單細節表單,找出產品編號和訂單編號
--查詢訂單有多少產品,折扣,數量
--Products表應該是產品庫存所以不找他
-- 單價*數量*(1 - 折扣)=總金額
-- UnitPrice*Quantity*(1 - Discount)=total
--先根據總金額排序,Group by 同編號不同商品算一個訂單
SELECT * from OrderDetails
SELECT TOP 1 WITH TIES od.OrderID,
CAST(SUM(CAST(od.UnitPrice AS decimal(19,4)) * od.Quantity * (1 - od.Discount)) AS decimal(19,4)) AS [總金額已加總同編號不同商品訂單] from OrderDetails AS od
GROUP BY od.OrderID --有將所有訂單編號視為一組
order by [總金額已加總同編號不同商品訂單] DESC
SELECT TOP 1 WITH TIES
T1.OrderID,
T1.[總金額已加總同編號不同商品訂單],
P.ProductName AS [品名],
OD.Quantity AS [訂單數量],
OD.UnitPrice AS [訂單價格],
OD.Discount AS [訂單折扣]
FROM(SELECT
OD.OrderID,
CAST(SUM(CAST(OD.UnitPrice AS decimal(19,4)) * OD.Quantity * (1 - OD.Discount)) AS decimal(19,4)) AS [總金額已加總同編號不同商品訂單]
FROM
OrderDetails AS OD
GROUP BY OD.OrderID) AS T1 -- 總額最高的訂單ID和總額
left JOIN OrderDetails AS OD ON T1.OrderID = OD.OrderID
left JOIN Products AS P ON OD.ProductID = P.ProductID
ORDER BY T1.[總金額已加總同編號不同商品訂單] DESC
--
-- 先找出Orders表中CustomerID為VINET的訂單
-- 再找出這些訂單的EmployeeID(業務) 不需要知道業務名稱
-- select * from Orders
-- where CustomerID = 'VINET'
select count(DISTINCT EmployeeID) AS [總業務經手數],CustomerID AS [客戶編號] from Orders
where CustomerID = 'VINET'
GROUP BY CustomerID
--
--需要Orders表和Employees表
-- select * from Employees
select DISTINCT Orders.CustomerID AS [客戶編號],Orders.EmployeeID AS [業務編號],LastName AS [業務名字],FirstName AS [業務姓氏]
from Orders left join Employees on
Orders.EmployeeID = Employees.EmployeeID
where Orders.CustomerID = 'VINET'
order by Orders.EmployeeID
--
-- select * from Products
-- select * from OrderDetails
-- 需要三個表 根據OrderDetail的fk和找訂單價格,Orders找訂單編號和客戶編號.Products找產品名稱
-- 一個訂單可能有多筆商品
select Orders.CustomerID AS [客戶編號],ProductName AS [品名],od.UnitPrice AS [訂單價格],od.Quantity AS [訂單數量],od.Discount AS [訂單折扣],(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS [小計]
from OrderDetails AS OD
left join Orders on Orders.OrderID = OD.OrderID
left join Products on Products.ProductID = OD.ProductID
where Orders.CustomerID = 'VINET' AND Orders.OrderID = 10274
--
SELECT DB_NAME() AS CurrentDatabaseName;
SELECT @@VERSION AS VERSION;
--
SELECT
Categories.CategoryName AS [類別],
Products.CategoryID AS [類編號],
Products.ProductID AS [品編號],
Products.ProductName AS [品名],
Products.UnitPrice AS [單價],
T1.AvgUnitPrice AS [類別平均單價],--這裡用子查詢根據類別查詢
CAST(Products.UnitPrice - T1.AvgUnitPrice AS DECIMAL(20, 4)) AS [價差]
FROM Products
LEFT JOIN Categories ON Products.CategoryID = Categories.CategoryID
LEFT JOIN
( SELECT CategoryID, AVG(UnitPrice) AS AvgUnitPrice
FROM Products
GROUP BY CategoryID) AS T1 ON Products.CategoryID = T1.CategoryID
ORDER BY
Products.CategoryID,
Products.ProductID ASC;
--
select *
FROM OrderDetails
LEFT JOIN Products on Products.ProductID = OrderDetails.ProductID
LEFT JOIN Orders on Orders.OrderID = OrderDetails.OrderID
-- LEFT JOIN Categories on Products.CategoryID = Categories.CategoryID
WHERE Products.CategoryID = 3
--
select top 3 with ties ProductName AS [品名],SUM(OrderDetails.Quantity) AS [總銷售數量]
FROM OrderDetails
LEFT JOIN Products on Products.ProductID = OrderDetails.ProductID
GROUP by ProductName
Order by [總銷售數量] DESC
--
select ProductName AS [品名],Country AS [廠商國家] from Products
left join Suppliers on Products.SupplierID = Suppliers.SupplierID
where Country = 'Japan'
--
老師不會給答案，所以寫完之後給ＡＩ檢查看有無遺漏
請先 登入 以發表留言。