1. Lý thuyết

Categories

Cấu trúc bảng

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	Name	nvarchar(50)				UNIQUE
3	Description	nvarchar(500)	yes

Các câu lệnh DML

Insert data

INSERT INTO [dbo].[Categories] ([Name], [Description])
VALUES
  (N'CPU', N'Chip xử lý'),
  (N'Headphone', N'Tai nghe');
GO

Update data

UPDATE [dbo].[Categories]
SET [Description] = N'Điện thoại di động'
WHERE [Id] = 1;
GO

Delete data

DELETE FROM [dbo].[Categories]
WHERE [Id] = 1;
GO

Suppliers

Cấu trúc bảng

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	Name	nvarchar(100)
3	Email	varchar(50)				UNIQUE
4	PhoneNumber	varchar(50)				UNIQUE
5	Address	nvarchar(500)

Các câu lệnh DML

Insert data

INSERT INTO [dbo].[Suppliers] ([Name], [Email], [PhoneNumber], [Address])
VALUES
  (N'SONY', 'contact@sony.com', '0123456788', N'Japan'),
  (N'APPLE', 'contact@apple.com', '0123456789', N'USA'),
  (N'SAMSUNG', 'contact@samsung.com', '321456765', N'Unknown');
GO

Update data

UPDATE [dbo].[Suppliers]
SET [Address] = N'Korea'
WHERE [Name] = N'SAMSUNG';
GO

Delete data

DELETE FROM [dbo].[Suppliers]
WHERE [Name] = N'SAMSUNG';
GO

Customers

Cấu trúc bảng

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	FirstName	nvarchar(50)
3	LastName	nvarchar(50)
4	Email	varchar(50)				UNIQUE
5	PhoneNumber	varchar(50)				UNIQUE
6	Address	nvarchar(500)
7	Birthday	datetime	yes

Các câu lệnh DML

Insert data

INSERT INTO [dbo].[Customers] (
  [FirstName],
  [LastName],
  [Email],
  [PhoneNumber],
  [Address],
  [Birthday]
)
VALUES
  (N'Nguyễn', N'Văn A', 'a@gmail.com', '0123456789', N'Việt Nam', '1990-11-21'),
  (N'Nguyễn', N'Văn B', 'b@gmail.com', '0123456788', N'Việt Nam', '1990-05-18');
GO

Update data

UPDATE [dbo].[Customers]
SET [Address] = N'Korea'
WHERE [FirstName] = N'Nguyễn' AND [LastName] = N'Văn B';
GO

Delete data

DELETE FROM [dbo].[Customers]
WHERE [Email] = 'a@gmail.com'
GO

Employees

Cấu trúc bảng

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	FirstName	nvarchar(50)
3	LastName	nvarchar(50)
4	Email	varchar(50)				UNIQUE
5	PhoneNumber	varchar(50)				UNIQUE
6	Address	nvarchar(500)
7	Birthday	datetime	yes

Các câu lệnh DML

Insert data

INSERT INTO [dbo].[Employees] (
  [FirstName],
  [LastName],
  [Email],
  [PhoneNumber],
  [Address],
  [Birthday]
)
VALUES
  (N'Nguyễn', N'Văn A', 'aa@gmail.com', '0123456333', N'Việt Nam', '1990-11-22'),
  (N'Trần', N'Thị B', 'bb@gmail.com', '0123456000', N'Việt Nam', '1990-10-01')
GO

Update data

UPDATE [dbo].[Employees]
SET [Address] = N'Korea'
WHERE [FirstName] = N'Nguyễn' AND [LastName] = N'Văn A';
GO

Delete data

DELETE FROM [dbo].[Employees]
WHERE [Email] = 'bb@gmail.com';
GO

Products

Cấu trúc bảng

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	Name	nvarchar(100)
3	Price	money				n > 0
4	Discount	decimal(18,2)			0	0 <= n <= 90
5	Stock	decimal(18,2)			0	n >= 0
6	Description	nvarchar(max)	yes
7	CategoryId	int		FK		Refrence to Categories (Id)
8	SupplierId	int		FK		Refrence to Suppliers (Id)

Các câu lệnh DML

Insert data

INSERT INTO [dbo].[Products] (
  [Name],
  [Price],
  [Discount],
  [Stock],
  [Description],
  [CategoryId],
  [SupplierId]
)
VALUES
  (N'Product 1', 100000, 10, 100, N'Product 1', 1, 1),
  (N'Product 2', 200000, 20, 200, N'Product 2', 2, 2),
  (N'Product 3', 300000, 30, 300, N'Product 3', 3, 3),
  (N'Product 4', 400000, 40, 400, N'Product 4', 4, 4)
GO;

Update data

UPDATE [dbo].[Products]
SET [Description] = N'Product 1 updated'
WHERE [Name] = N'Product 1';
GO

Delete data

DELETE FROM [dbo].[Products]
WHERE [Name] = N'Product 1';
GO

Orders

Cấu trúc bảng

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	CreatedDate	datetime			NOW
3	ShippedDate	datetime	yes			n < CreatedDate
4	Status	varchar(50)			WAITING	n in [WAINTING, COMPLETED, CANCELED]
5	Description	nvarchar(max)
6	ShippingAddress	nvarchar(500)	yes
7	ShippingCity	nvarchar(50)
8	PaymentType	varchar(20)			CASH	n in [CASH, CREDIT CARD]
9	CustomerId	int		FK		Refrence to Customers (Id)
10	EmployessId	int		FK		Refrence to Employees (Id)

Các câu lệnh DML

Insert data

INSERT INTO [dbo].[Orders] (
  [CreatedDate],
  [ShippedDate],
  [Status],
  [Description],
  [ShippingAddress],
  [ShippingCity],
  [PaymentType],
  [CustomerId],
  [EmployessId]
)
VALUES
  (GETDATE(), GETDATE(), 'WAITING', N'Order 1', N'Address 1', N'City 1', 'CASH', 1, 1),
  (GETDATE(), GETDATE(), 'WAITING', N'Order 2', N'Address 2', N'City 2', 'CASH', 2, 2),
  (GETDATE(), GETDATE(), 'WAITING', N'Order 3', N'Address 3', N'City 3', 'CASH', 3, 3),
  (GETDATE(), GETDATE(), 'WAITING', N'Order 4', N'Address 4', N'City 4', 'CASH', 4, 4);
GO

Update data

UPDATE [dbo].[Orders]
SET [Description] = N'Order 1 updated'
WHERE [Id] = 1;
GO

Delete data

DELETE FROM [dbo].[Orders]
WHERE [Id] = 1;
GO

Order Details

Cấu trúc bảng

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	OrderId	int		PK + FK		Refrence to Orders (Id)
2	ProductId	int		PK + FK		Refrence to Products (Id)
3	Quantity	decimal(18,2)				n > 0
4	Price	decimal(18,2)				n > 0
5	Discount	decimal(18,2)				0 <= n <=90

Các câu lệnh DML

Insert data

INSERT INTO [dbo].[OrderDetails] (
  [OrderId],
  [ProductId],
  [Quantity],
  [Price],
  [Discount]
)
VALUES
  (4, 4, 4, 1, 30, 5);
GO

Update data

UPDATE [dbo].[OrderDetails]
SET [Quantity] = 2
WHERE [OrderId] = 1 AND [ProductId] = 1;
GO

Delete data

DELETE FROM [dbo].[OrderDetails]
WHERE [OrderId] = 1 AND [ProductId] = 1;
GO