-- (localdb)\mssqllocaldb
-----------------------------
--select
select * from Customers
select CustomerID, CompanyName, Country from Customers
select CustomerID, CompanyName, Country from Customers
where Country='usa'
-----------------------------
--order by
SELECT ProductID, ProductName, CategoryID ,UnitPrice
FROM products
ORDER BY CategoryID

SELECT ProductID, ProductName, CategoryID ,UnitPrice
FROM products
ORDER BY CategoryID, UnitPrice DESC
----------------------------
--like
SELECT ProductID, ProductName, UnitPrice
FROM Products
where ProductName = 'Chai'


SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE ProductName LIKE '%de'

SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE ProductName LIKE '%de%'
-------------------------------
--in
SELECT ProductID ,ProductName, UnitPrice, CategoryID
FROM Products
--where CategoryID=1 or CategoryID=3 or CategoryID=5
where CategoryID in (1,3,5)
order by CategoryID

-------------------------------
--between and
SELECT ProductID ,ProductName, UnitPrice 
FROM Products
--WHERE UnitPrice >=6 and UnitPrice <=100
where unitprice between 6 and 100
order by unitprice desc

SELECT ProductID ,ProductName, UnitPrice 
FROM Products
--WHERE UnitPrice >=6 and UnitPrice <=100
where unitprice not between 6 and 100
order by unitprice desc
------------------------------
--in
SELECT CompanyName, Country
FROM Suppliers
WHERE country in ('usa','japan', 'uk')


SELECT CompanyName, Country
FROM Suppliers
WHERE country not in ('usa','japan', 'uk')

----------------------------
--null
SELECT EmployeeID, LastName, Region
FROM Employees
where Region is null

SELECT EmployeeID, LastName, Region
FROM Employees
where Region is not null
-----------------------------
--as
SELECT CategoryName AS [產品分類名稱], Description AS [說明]
FROM Categories
-----------------------------
--distinct
select Country from Employees
select distinct Country from Employees
-----------------------------
--top 
select top 5 ProductName, unitprice
from products
order by unitprice desc
--------------------------------------
--fetch
SELECT ProductID, ProductName
FROM Products 
ORDER BY ProductID
OFFSET 10 ROWS

SELECT ProductID, ProductName
FROM Products 
ORDER BY ProductID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
--------------------------------------
--count
select EmployeeID, LastName, ReportsTo
from Employees

select count(*)
from Employees

select count(ReportsTo)
from Employees
--------------------------------------
--aggregate
SELECT max(UnitPrice)
FROM Products

SELECT min(UnitPrice)
FROM Products

SELECT avg(UnitPrice)
FROM Products

SELECT sum(UnitPrice)
FROM Products
--------------------------------------
--group by
select Country, count(*) as Count
from Customers
group by Country

select Country, count(*) as Count
from Customers
group by Country
having COUNT(*)>10
--------------------------------------
--INNER JOIN
SELECT ProductID, ProductName, CategoryID, UnitPrice
FROM Products

SELECT ProductID, ProductName, CategoryName, UnitPrice
FROM Products AS p
INNER JOIN Categories AS c
ON p.CategoryID=c.CategoryID

SELECT ProductID, ProductName,  CategoryName, UnitPrice
FROM Products AS p
INNER JOIN Categories AS c
ON p.CategoryID=c.CategoryID
where p.CategoryID=1
--------------------------------------
USE master
GO
CREATE DATABASE MyDatabase
GO
USE MyDatabase

DROP DATABASE MyDatabase
--------------------------------------
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY IDENTITY,
    CategoryName NVARCHAR(255) NOT NULL
);

--------------------------------------
CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY,
    ProductName NVARCHAR(255) NOT NULL,
    UnitPrice DECIMAL(10, 2) NOT NULL,
    CategoryID INT,
    FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID)
);
----------------------------------
select ProductName from mydemo.dbo.products

INSERT INTO Categories ( CategoryName)
VALUES
    ('Category 1'),
    ('Category 2'),
    ('Category 3'),
    ('Category 4');
go
select * from Categories
-------------------------------
--------------------------------------
select * from Categories
UPDATE Categories 
SET CategoryName='MyCategoryName1'
WHERE CategoryID=1
select * from Categories
--------------------------------
select * from Products
DELETE Products 
WHERE ProductID=12
select * from Products