6. Creating Tables

Download Slide

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

Script T-SQL

Create table [Categories]

CREATE TABLE [dbo].[Categories] (
  [Id] INT IDENTITY(1,1) NOT NULL,
  [Name] NVARCHAR(50) NOT NULL,
  [Description] NVARCHAR(500) NULL,
);
GO

Create PRIMARY KEY

ALTER TABLE [dbo].[Categories]
ADD CONSTRAINT [PK_Categories] PRIMARY KEY ([Id]);
GO

Create UNIQUE KEY (Name))

ALTER TABLE [dbo].[Categories]
ADD CONSTRAINT [UQ_Categories_Name] UNIQUE ([Name]);
GO

Các câu lệnh DML

Insert data

INSERT INTO [dbo].[Categories] ([Name], [Description])
VALUES
  (N'CPU', N'Chip xử lý'),
  (N'HDD', N'Ổ cứng'),
  (N'RAM', N'Bộ nhớ'),
  (N'VGA', N'Card đồ họa'),
  (N'Monitor', N'Màn hình'),
  (N'Keyboard', N'Bàn phím'),
  (N'Mouse', N'Chuột'),
  (N'Printer', N'Máy in'),
  (N'UPS', N'Nguồn'),
  (N'Headphone', N'Tai nghe');
GO

Update data

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

Delete data

DELETE FROM [dbo].[Categories]
WHERE [Name] = N'Điện thoại';
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)

Script T-SQL

Create table [Suppliers]

CREATE TABLE [dbo].[Suppliers] (
  [Id] INT IDENTITY(1,1) NOT NULL,
  [Name] NVARCHAR(100) NOT NULL,
  [Email] VARCHAR(50) NOT NULL,
  [PhoneNumber] VARCHAR(50) NOT NULL,
  [Address] NVARCHAR(500) NOT NULL,
);
GO

Create PRIMARY KEY

ALTER TABLE [dbo].[Suppliers]
ADD CONSTRAINT [PK_Suppliers] PRIMARY KEY ([Id]);
GO

Create UNIQUE KEY (Email)

ALTER TABLE [dbo].[Suppliers]
ADD CONSTRAINT [UQ_Suppliers_Email] UNIQUE ([Email]);
GO

Create UNIQUE KEY (PhoneNumber)

ALTER TABLE [dbo].[Suppliers]
ADD CONSTRAINT [UQ_Suppliers_PhoneNumber] UNIQUE ([PhoneNumber]);
GO

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

Script T-SQL

Create table [Customers]

CREATE TABLE [dbo].[Customers] (
[Id] INT IDENTITY(1,1) NOT NULL,
[FirstName] NVARCHAR(50) NOT NULL,
[LastName] NVARCHAR(50) NOT NULL,
[Email] VARCHAR(50) NOT NULL,
[PhoneNumber] VARCHAR(50) NOT NULL,
[Address] NVARCHAR(500) NOT NULL,
[Birthday] DATETIME NULL,
);
GO

Create PRIMARY KEY

ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT [PK_Customers] PRIMARY KEY ([Id]);
GO

Create UNIQUE KEY (Email)

ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT [UQ_Customers_Email] UNIQUE ([Email]);
GO

Create UNIQUE KEY (PhoneNumber)

ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT [UQ_Customers_PhoneNumber] UNIQUE ([PhoneNumber]);
GO

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

Script T-SQL

Create table [Employees]

CREATE TABLE [dbo].[Employees] (
  [Id] INT IDENTITY(1,1) NOT NULL,
  [FirstName] NVARCHAR(50) NOT NULL,
  [LastName] NVARCHAR(50) NOT NULL,
  [Email] VARCHAR(50) NOT NULL,
  [PhoneNumber] VARCHAR(50) NOT NULL,
  [Address] NVARCHAR(500) NOT NULL,
  [Birthday] DATETIME NULL,
);
GO

Create PRIMARY KEY

ALTER TABLE [dbo].[Employees]
ADD CONSTRAINT [PK_Employees] PRIMARY KEY ([Id]);
GO

Create UNIQUE KEY (Email)

ALTER TABLE [dbo].[Employees]
ADD CONSTRAINT [UQ_Employees_Email] UNIQUE ([Email]);
GO

Create UNIQUE KEY (PhoneNumber)

ALTER TABLE [dbo].[Employees]
ADD CONSTRAINT [UQ_Employees_PhoneNumber] UNIQUE ([PhoneNumber]);
GO

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)

Script T-SQL

Create table [Products]

CREATE TABLE [dbo].[Products] (
  [Id] INT IDENTITY(1,1) NOT NULL,
  [Name] NVARCHAR(100) NOT NULL,
  [Price] MONEY NOT NULL,
  [Discount] DECIMAL(18,2) NOT NULL,
  [Stock] DECIMAL(18,2) NOT NULL,
  [Description] NVARCHAR(MAX) NULL,
  [CategoryId] INT NOT NULL,
  [SupplierId] INT NOT NULL,
);
GO

Create PRIMARY KEY

ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [PK_Products] PRIMARY KEY ([Id]);
GO

Create FOREIGN KEY (CategoryId)

ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [FK_Products_Categories] FOREIGN KEY ([CategoryId]) REFERENCES [dbo].[Categories] ([Id]);
GO

Create FOREIGN KEY (SupplierId)

ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY ([SupplierId]) REFERENCES [dbo].[Suppliers] ([Id]);
GO

Create CHECK (Price > 0)

ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [CK_Products_Price] CHECK ([Price] > 0);
GO

Create CHECK (Discount >= 0 AND Discount <= 90)

ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [CK_Products_Discount] CHECK ([Discount] >= 0 AND [Discount] <= 90);
GO

Create CHECK (Stock >= 0)

ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [CK_Products_Stock] CHECK ([Stock] >= 0);
GO

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)

Script T-SQL

Create table [Orders]

CREATE TABLE [dbo].[Orders] (
  [Id] INT IDENTITY(1,1) NOT NULL,
  [CreatedDate] DATETIME NOT NULL,
  [ShippedDate] DATETIME NULL,
  [Status] VARCHAR(50) NOT NULL,
  [Description] NVARCHAR(MAX) NULL,
  [ShippingAddress] NVARCHAR(500) NULL,
  [ShippingCity] NVARCHAR(50) NOT NULL,
  [PaymentType] VARCHAR(20) NOT NULL,
  [CustomerId] INT NOT NULL,
  [EmployessId] INT NOT NULL,
);

GO

Create PRIMARY KEY

ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [PK_Orders] PRIMARY KEY ([Id]);
GO

Create FOREIGN KEY (CustomerId)

ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customers] ([Id]);
GO

Create FOREIGN KEY (EmployessId)

ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [FK_Orders_Employees] FOREIGN KEY ([EmployessId]) REFERENCES [dbo].[Employees] ([Id]);
GO

Create CHECK (Status in [WAITING, COMPLETED, CANCELED])

ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [CK_Orders_Status] CHECK ([Status] IN ('WAITING', 'COMPLETED', 'CANCELED'));
GO

Create CHECK (PaymentType in [CASH, CREDIT CARD])

ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [CK_Orders_PaymentType] CHECK ([PaymentType] IN ('CASH', 'CREDIT CARD'));
GO

Create CHECK (ShippedDate < CreatedDate)

ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [CK_Orders_ShippedDate] CHECK ([ShippedDate] < [CreatedDate]);
GO

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	Discount	decimal(18,2)			WAITING	0 <= n <=90

Script T-SQL

Create table [OrderDetails]

CREATE TABLE [dbo].[OrderDetails] (
  [OrderId] INT NOT NULL,
  [ProductId] INT NOT NULL,
  [Quantity] DECIMAL(18,2) NOT NULL,
  [Discount] DECIMAL(18,2) NOT NULL,
);
GO

Create PRIMARY KEY

ALTER TABLE [dbo].[OrderDetails]
ADD CONSTRAINT [PK_OrderDetails] PRIMARY KEY ([OrderId], [ProductId]);
GO

Create FOREIGN KEY (OrderId)

ALTER TABLE [dbo].[OrderDetails]
ADD CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY ([OrderId]) REFERENCES [dbo].[Orders] ([Id]);
GO

Create FOREIGN KEY (ProductId)

ALTER TABLE [dbo].[OrderDetails]
ADD CONSTRAINT [FK_OrderDetails_Products] FOREIGN KEY ([ProductId]) REFERENCES [dbo].[Products] ([Id]);
GO

Create CHECK (Quantity >= 0)

ALTER TABLE [dbo].[OrderDetails]
ADD CONSTRAINT [CK_OrderDetails_Quantity] CHECK ([Quantity] >= 0);
GO

Create CHECK (Discount >= 0 AND Discount <= 90)

ALTER TABLE [dbo].[OrderDetails]
ADD CONSTRAINT [CK_OrderDetails_Discount] CHECK ([Discount] >= 0 AND [Discount] <= 90);
GO

Các câu lệnh DML

Insert data

INSERT INTO [dbo].[OrderDetails] (
  [OrderId],
  [ProductId],
  [Quantity],
  [Discount]
)
VALUES
  (1, 1, 1, 0),
  (2, 2, 2, 0),
  (3, 3, 3, 0),
  (4, 4, 4, 0);
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