1-- Basic create database
2CREATE DATABASE OnlineStore;
3GO
4
5-- With options
6CREATE DATABASE OnlineStore
7ON PRIMARY (
8    NAME = 'OnlineStore_Data',
9    FILENAME = 'C:\SQLData\OnlineStore_Data.mdf',
10    SIZE = 100MB,
11    MAXSIZE = 1GB,
12    FILEGROWTH = 10%
13)
14LOG ON (
15    NAME = 'OnlineStore_Log',
16    FILENAME = 'C:\SQLData\OnlineStore_Log.ldf',
17    SIZE = 50MB,
18    MAXSIZE = 500MB,
19    FILEGROWTH = 10%
20);
21GO
22
23-- Use database
24USE OnlineStore;
25GO

1-- Schema giúp tổ chức tables theo modules
2-- AdventureWorks có: Sales, Production, HumanResources, Person, Purchasing
3
4-- Create schemas
5CREATE SCHEMA Sales;
6GO
7
8CREATE SCHEMA Inventory;
9GO
10
11CREATE SCHEMA Customer;
12GO
13
14-- Xem schemas trong AdventureWorks
15SELECT DISTINCT 
16    SCHEMA_NAME(schema_id) AS SchemaName
17FROM sys.tables
18ORDER BY SchemaName;

1-- Syntax
2CREATE TABLE SchemaName.TableName (
3    Column1 DataType [Constraints],
4    Column2 DataType [Constraints],
5    ...
6    [Table-level Constraints]
7);
8
9-- Example: Simple table
10CREATE TABLE Customer.Customer (
11    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
12    FirstName NVARCHAR(50) NOT NULL,
13    LastName NVARCHAR(50) NOT NULL,
14    Email VARCHAR(100) NOT NULL UNIQUE,
15    Phone VARCHAR(20),
16    CreatedDate DATETIME2 DEFAULT SYSDATETIME()
17);

1-- IDENTITY(seed, increment)
2CREATE TABLE Product (
3    ProductID INT IDENTITY(1,1) PRIMARY KEY,     -- Starts at 1, increments by 1
4    ProductName NVARCHAR(100)
5);
6
7-- Start at 1000, increment by 10
8CREATE TABLE Order (
9    OrderID INT IDENTITY(1000, 10) PRIMARY KEY,  -- 1000, 1010, 1020...
10    OrderDate DATE
11);
12
13-- Insert và lấy ID mới
14INSERT INTO Product (ProductName) VALUES ('Laptop');
15SELECT SCOPE_IDENTITY() AS NewProductID;
16
17-- Xem giá trị IDENTITY hiện tại
18SELECT IDENT_CURRENT('Product') AS CurrentIdentity;
19
20-- Reset IDENTITY
21DBCC CHECKIDENT ('Product', RESEED, 0);

1-- 1. Category Table
2CREATE TABLE Inventory.Category (
3    CategoryID INT IDENTITY(1,1),
4    CategoryName NVARCHAR(50) NOT NULL,
5    Description NVARCHAR(500),
6    ParentCategoryID INT,
7    IsActive BIT NOT NULL DEFAULT 1,
8    SortOrder SMALLINT DEFAULT 0,
9    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
10    ModifiedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
11    
12    -- Constraints
13    CONSTRAINT PK_Category PRIMARY KEY (CategoryID),
14    CONSTRAINT UQ_Category_Name UNIQUE (CategoryName),
15    CONSTRAINT FK_Category_Parent FOREIGN KEY (ParentCategoryID) 
16        REFERENCES Inventory.Category(CategoryID),
17    CONSTRAINT CK_Category_SortOrder CHECK (SortOrder >= 0)
18);
19GO
20
21-- 2. Product Table
22CREATE TABLE Inventory.Product (
23    ProductID INT IDENTITY(1,1),
24    SKU VARCHAR(20) NOT NULL,
25    ProductName NVARCHAR(200) NOT NULL,
26    Description NVARCHAR(MAX),
27    CategoryID INT NOT NULL,
28    UnitPrice DECIMAL(19, 4) NOT NULL,
29    CostPrice DECIMAL(19, 4),
30    StockQuantity INT NOT NULL DEFAULT 0,
31    ReorderLevel INT DEFAULT 10,
32    Weight DECIMAL(10, 2),
33    ImageURL VARCHAR(500),
34    IsActive BIT NOT NULL DEFAULT 1,
35    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
36    ModifiedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
37    
38    -- Constraints
39    CONSTRAINT PK_Product PRIMARY KEY (ProductID),
40    CONSTRAINT UQ_Product_SKU UNIQUE (SKU),
41    CONSTRAINT FK_Product_Category FOREIGN KEY (CategoryID) 
42        REFERENCES Inventory.Category(CategoryID),
43    CONSTRAINT CK_Product_UnitPrice CHECK (UnitPrice >= 0),
44    CONSTRAINT CK_Product_CostPrice CHECK (CostPrice >= 0),
45    CONSTRAINT CK_Product_Stock CHECK (StockQuantity >= 0)
46);
47GO
48
49-- 3. Customer Table
50CREATE TABLE Customer.Customer (
51    CustomerID INT IDENTITY(1,1),
52    Email VARCHAR(100) NOT NULL,
53    PasswordHash VARBINARY(256) NOT NULL,
54    FirstName NVARCHAR(50) NOT NULL,
55    LastName NVARCHAR(50) NOT NULL,
56    Phone VARCHAR(20),
57    DateOfBirth DATE,
58    Gender CHAR(1),
59    IsEmailVerified BIT NOT NULL DEFAULT 0,
60    IsActive BIT NOT NULL DEFAULT 1,
61    LastLoginDate DATETIME2,
62    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
63    ModifiedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
64    
65    -- Constraints
66    CONSTRAINT PK_Customer PRIMARY KEY (CustomerID),
67    CONSTRAINT UQ_Customer_Email UNIQUE (Email),
68    CONSTRAINT CK_Customer_Email CHECK (Email LIKE '%@%.%'),
69    CONSTRAINT CK_Customer_Gender CHECK (Gender IN ('M', 'F', 'O'))
70);
71GO
72
73-- 4. Address Table (1:N with Customer)
74CREATE TABLE Customer.Address (
75    AddressID INT IDENTITY(1,1),
76    CustomerID INT NOT NULL,
77    AddressType VARCHAR(20) NOT NULL DEFAULT 'Shipping',
78    AddressLine1 NVARCHAR(200) NOT NULL,
79    AddressLine2 NVARCHAR(200),
80    City NVARCHAR(100) NOT NULL,
81    Province NVARCHAR(100) NOT NULL,
82    PostalCode VARCHAR(10),
83    Country NVARCHAR(50) NOT NULL DEFAULT N'Việt Nam',
84    IsDefault BIT NOT NULL DEFAULT 0,
85    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
86    
87    CONSTRAINT PK_Address PRIMARY KEY (AddressID),
88    CONSTRAINT FK_Address_Customer FOREIGN KEY (CustomerID) 
89        REFERENCES Customer.Customer(CustomerID) ON DELETE CASCADE,
90    CONSTRAINT CK_Address_Type CHECK (AddressType IN ('Shipping', 'Billing'))
91);
92GO
93
94-- 5. Order Table
95CREATE TABLE Sales.[Order] (  -- Order là reserved word, cần []
96    OrderID INT IDENTITY(1,1),
97    OrderNumber VARCHAR(20) NOT NULL,
98    CustomerID INT NOT NULL,
99    OrderDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
100    Status VARCHAR(20) NOT NULL DEFAULT 'Pending',
101    ShippingAddressID INT,
102    BillingAddressID INT,
103    SubTotal DECIMAL(19, 4) NOT NULL DEFAULT 0,
104    ShippingFee DECIMAL(19, 4) NOT NULL DEFAULT 0,
105    TaxAmount DECIMAL(19, 4) NOT NULL DEFAULT 0,
106    DiscountAmount DECIMAL(19, 4) NOT NULL DEFAULT 0,
107    TotalAmount AS (SubTotal + ShippingFee + TaxAmount - DiscountAmount) PERSISTED,
108    Notes NVARCHAR(500),
109    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
110    ModifiedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
111    
112    CONSTRAINT PK_Order PRIMARY KEY (OrderID),
113    CONSTRAINT UQ_Order_Number UNIQUE (OrderNumber),
114    CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerID) 
115        REFERENCES Customer.Customer(CustomerID),
116    CONSTRAINT FK_Order_ShippingAddress FOREIGN KEY (ShippingAddressID) 
117        REFERENCES Customer.Address(AddressID),
118    CONSTRAINT FK_Order_BillingAddress FOREIGN KEY (BillingAddressID) 
119        REFERENCES Customer.Address(AddressID),
120    CONSTRAINT CK_Order_Status CHECK (Status IN ('Pending', 'Confirmed', 'Processing', 'Shipped', 'Delivered', 'Cancelled', 'Refunded')),
121    CONSTRAINT CK_Order_SubTotal CHECK (SubTotal >= 0)
122);
123GO
124
125-- 6. OrderDetail Table
126CREATE TABLE Sales.OrderDetail (
127    OrderDetailID INT IDENTITY(1,1),
128    OrderID INT NOT NULL,
129    ProductID INT NOT NULL,
130    Quantity INT NOT NULL,
131    UnitPrice DECIMAL(19, 4) NOT NULL,
132    Discount DECIMAL(4, 2) NOT NULL DEFAULT 0,
133    LineTotal AS (Quantity * UnitPrice * (1 - Discount)) PERSISTED,
134    
135    CONSTRAINT PK_OrderDetail PRIMARY KEY (OrderDetailID),
136    CONSTRAINT FK_OrderDetail_Order FOREIGN KEY (OrderID) 
137        REFERENCES Sales.[Order](OrderID) ON DELETE CASCADE,
138    CONSTRAINT FK_OrderDetail_Product FOREIGN KEY (ProductID) 
139        REFERENCES Inventory.Product(ProductID),
140    CONSTRAINT CK_OrderDetail_Quantity CHECK (Quantity > 0),
141    CONSTRAINT CK_OrderDetail_UnitPrice CHECK (UnitPrice >= 0),
142    CONSTRAINT CK_OrderDetail_Discount CHECK (Discount >= 0 AND Discount <= 1)
143);
144GO

1-- Indexes cho performance (chi tiết ở bài Indexing)
2CREATE INDEX IX_Product_CategoryID ON Inventory.Product(CategoryID);
3CREATE INDEX IX_Order_CustomerID ON Sales.[Order](CustomerID);
4CREATE INDEX IX_Order_OrderDate ON Sales.[Order](OrderDate DESC);
5CREATE INDEX IX_OrderDetail_OrderID ON Sales.OrderDetail(OrderID);
6CREATE INDEX IX_Customer_Email ON Customer.Customer(Email);

1-- Add single column
2ALTER TABLE Customer.Customer
3ADD MiddleName NVARCHAR(50);
4
5-- Add multiple columns
6ALTER TABLE Customer.Customer
7ADD 
8    ReferralCode VARCHAR(20),
9    ReferredBy INT;
10
11-- Add with constraint
12ALTER TABLE Customer.Customer
13ADD LoyaltyPoints INT NOT NULL DEFAULT 0
14    CONSTRAINT CK_Customer_LoyaltyPoints CHECK (LoyaltyPoints >= 0);

1-- Change data type (careful với existing data!)
2ALTER TABLE Customer.Customer
3ALTER COLUMN Phone VARCHAR(30);
4
5-- Change to NOT NULL (phải không có NULL values)
6UPDATE Customer.Customer SET MiddleName = '' WHERE MiddleName IS NULL;
7ALTER TABLE Customer.Customer
8ALTER COLUMN MiddleName NVARCHAR(50) NOT NULL;
9
10-- Change size
11ALTER TABLE Inventory.Product
12ALTER COLUMN Description NVARCHAR(2000);

1-- Drop column
2ALTER TABLE Customer.Customer
3DROP COLUMN MiddleName;
4
5-- Drop column với constraint
6-- Phải drop constraint trước
7ALTER TABLE Customer.Customer
8DROP CONSTRAINT CK_Customer_LoyaltyPoints;
9
10ALTER TABLE Customer.Customer
11DROP COLUMN LoyaltyPoints;

1-- Add constraint
2ALTER TABLE Inventory.Product
3ADD CONSTRAINT CK_Product_Weight CHECK (Weight > 0);
4
5-- Add foreign key
6ALTER TABLE Customer.Customer
7ADD CONSTRAINT FK_Customer_ReferredBy 
8    FOREIGN KEY (ReferredBy) REFERENCES Customer.Customer(CustomerID);
9
10-- Drop constraint
11ALTER TABLE Inventory.Product
12DROP CONSTRAINT CK_Product_Weight;

1-- Drop table (careful!)
2DROP TABLE Sales.OrderDetail;
3DROP TABLE Sales.[Order];
4
5-- Drop if exists (SQL Server 2016+)
6DROP TABLE IF EXISTS Sales.OrderDetail;
7DROP TABLE IF EXISTS Sales.[Order];
8
9-- Drop with dependencies check
10-- Must drop in correct order due to FK constraints!
11-- 1. OrderDetail first (references Order)
12-- 2. Order next (references Customer, Address)
13-- 3. Address, Customer last
14
15-- Hoặc disable FKs temporarily
16EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
17-- Drop tables
18EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL';

1-- TRUNCATE - Remove all rows, reset IDENTITY
2TRUNCATE TABLE Sales.TempLog;
3
4-- DELETE - Remove all rows, keep IDENTITY
5DELETE FROM Sales.TempLog;
6
7-- TRUNCATE với FK (không được!)
8-- Error: Cannot truncate table because it is being referenced by FK
9-- Phải disable FK hoặc dùng DELETE

1-- Table names: PascalCase, singular
2CREATE TABLE Customer (...);      -- ✅
3CREATE TABLE Customers (...);     -- ❌ Avoid plural
4CREATE TABLE customer (...);      -- ❌ Avoid lowercase
5
6-- Column names: PascalCase
7CustomerID, FirstName, CreatedDate
8
9-- Constraint names: Type_Table_Column
10PK_Customer                       -- Primary Key
11FK_Order_Customer                 -- Foreign Key
12UQ_Customer_Email                 -- Unique
13CK_Product_Price                  -- Check
14DF_Order_Status                   -- Default
15IX_Order_CustomerID               -- Index

1-- Audit columns (add to every table)
2CREATE TABLE MyTable (
3    -- Business columns
4    ...
5    
6    -- Audit columns
7    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
8    CreatedBy NVARCHAR(100) DEFAULT SYSTEM_USER,
9    ModifiedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
10    ModifiedBy NVARCHAR(100) DEFAULT SYSTEM_USER,
11    
12    -- Soft delete (optional)
13    IsDeleted BIT NOT NULL DEFAULT 0,
14    DeletedDate DATETIME2
15);

1-- Standard table template
2CREATE TABLE SchemaName.TableName (
3    -- Primary Key
4    TableNameID INT IDENTITY(1,1),
5    
6    -- Business columns
7    Column1 DataType NOT NULL,
8    Column2 DataType,
9    
10    -- Foreign Keys
11    RelatedTableID INT NOT NULL,
12    
13    -- Status/Flag columns
14    IsActive BIT NOT NULL DEFAULT 1,
15    
16    -- Audit columns
17    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
18    ModifiedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
19    
20    -- Constraints
21    CONSTRAINT PK_TableName PRIMARY KEY (TableNameID),
22    CONSTRAINT FK_TableName_RelatedTable FOREIGN KEY (RelatedTableID) 
23        REFERENCES SchemaName.RelatedTable(RelatedTableID)
24);
25GO
26
27-- Index for FK
28CREATE INDEX IX_TableName_RelatedTableID ON SchemaName.TableName(RelatedTableID);
29GO

1-- Your solution here
2-- 1. Create Schema
3CREATE SCHEMA Blog;
4GO
5
6-- 2. Create User table
7CREATE TABLE Blog.[User] (
8    UserID INT IDENTITY(1,1),
9    Username VARCHAR(50) NOT NULL,
10    Email VARCHAR(100) NOT NULL,
11    PasswordHash VARBINARY(256) NOT NULL,
12    DisplayName NVARCHAR(100),
13    Avatar VARCHAR(500),
14    Bio NVARCHAR(1000),
15    IsActive BIT NOT NULL DEFAULT 1,
16    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
17    
18    CONSTRAINT PK_User PRIMARY KEY (UserID),
19    CONSTRAINT UQ_User_Username UNIQUE (Username),
20    CONSTRAINT UQ_User_Email UNIQUE (Email)
21);
22
23-- 3. Create Category table
24CREATE TABLE Blog.Category (
25    CategoryID INT IDENTITY(1,1),
26    CategoryName NVARCHAR(50) NOT NULL,
27    Slug VARCHAR(50) NOT NULL,
28    Description NVARCHAR(500),
29    
30    CONSTRAINT PK_Category PRIMARY KEY (CategoryID),
31    CONSTRAINT UQ_Category_Slug UNIQUE (Slug)
32);
33
34-- 4. Create Tag table
35CREATE TABLE Blog.Tag (
36    TagID INT IDENTITY(1,1),
37    TagName NVARCHAR(30) NOT NULL,
38    Slug VARCHAR(30) NOT NULL,
39    
40    CONSTRAINT PK_Tag PRIMARY KEY (TagID),
41    CONSTRAINT UQ_Tag_Slug UNIQUE (Slug)
42);
43
44-- 5. Create Post table
45CREATE TABLE Blog.Post (
46    PostID INT IDENTITY(1,1),
47    Title NVARCHAR(200) NOT NULL,
48    Slug VARCHAR(200) NOT NULL,
49    Content NVARCHAR(MAX) NOT NULL,
50    Excerpt NVARCHAR(500),
51    AuthorID INT NOT NULL,
52    Status VARCHAR(20) NOT NULL DEFAULT 'Draft',
53    ViewCount INT NOT NULL DEFAULT 0,
54    PublishedDate DATETIME2,
55    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
56    ModifiedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
57    
58    CONSTRAINT PK_Post PRIMARY KEY (PostID),
59    CONSTRAINT UQ_Post_Slug UNIQUE (Slug),
60    CONSTRAINT FK_Post_Author FOREIGN KEY (AuthorID) 
61        REFERENCES Blog.[User](UserID),
62    CONSTRAINT CK_Post_Status CHECK (Status IN ('Draft', 'Published', 'Archived'))
63);
64
65-- 6. Junction: Post_Category (M:N)
66CREATE TABLE Blog.PostCategory (
67    PostID INT NOT NULL,
68    CategoryID INT NOT NULL,
69    
70    CONSTRAINT PK_PostCategory PRIMARY KEY (PostID, CategoryID),
71    CONSTRAINT FK_PostCategory_Post FOREIGN KEY (PostID) 
72        REFERENCES Blog.Post(PostID) ON DELETE CASCADE,
73    CONSTRAINT FK_PostCategory_Category FOREIGN KEY (CategoryID) 
74        REFERENCES Blog.Category(CategoryID) ON DELETE CASCADE
75);
76
77-- 7. Junction: Post_Tag (M:N)
78CREATE TABLE Blog.PostTag (
79    PostID INT NOT NULL,
80    TagID INT NOT NULL,
81    
82    CONSTRAINT PK_PostTag PRIMARY KEY (PostID, TagID),
83    CONSTRAINT FK_PostTag_Post FOREIGN KEY (PostID) 
84        REFERENCES Blog.Post(PostID) ON DELETE CASCADE,
85    CONSTRAINT FK_PostTag_Tag FOREIGN KEY (TagID) 
86        REFERENCES Blog.Tag(TagID) ON DELETE CASCADE
87);
88
89-- 8. Comment table
90CREATE TABLE Blog.Comment (
91    CommentID INT IDENTITY(1,1),
92    PostID INT NOT NULL,
93    UserID INT NOT NULL,
94    ParentCommentID INT,  -- For replies
95    Content NVARCHAR(2000) NOT NULL,
96    IsApproved BIT NOT NULL DEFAULT 0,
97    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
98    
99    CONSTRAINT PK_Comment PRIMARY KEY (CommentID),
100    CONSTRAINT FK_Comment_Post FOREIGN KEY (PostID) 
101        REFERENCES Blog.Post(PostID) ON DELETE CASCADE,
102    CONSTRAINT FK_Comment_User FOREIGN KEY (UserID) 
103        REFERENCES Blog.[User](UserID),
104    CONSTRAINT FK_Comment_Parent FOREIGN KEY (ParentCommentID) 
105        REFERENCES Blog.Comment(CommentID)
106);
107GO
108
109-- Create indexes
110CREATE INDEX IX_Post_AuthorID ON Blog.Post(AuthorID);
111CREATE INDEX IX_Post_Status ON Blog.Post(Status);
112CREATE INDEX IX_Comment_PostID ON Blog.Comment(PostID);
113GO