Chương I: Tổng quan về Cơ sở dữ liệu và SQL Server 2005
1. Cơ sở dữ liệu
Trong thực tế, khi giải quyết một bài toán với khối lượng dữ liệu lớn cần xử lý, sẽ phát sinh việc quản lý các dữ liệu đó một cách khoa học. Tuy nhiên, việc dùng hệ quản lý tệp với các tệp dữ liệu chứa thông tin và dùng chương trình để thực hiện các thao tác trên dữ liệu đó đã dẫn tới một số nhược điểm không mong muốn như:
- Dư thừa và không nhất quán về dữ liệu
- Khó khăn trong việc truy cập dữ liệu
- Sự cô lập dữ liệu
- Các dị thường truy nhập tương tranh
- Các vấn đề về an toàn, toàn vẹn, độ tin cậy dữ liệu
Vì vậy để khắc phục các nhược điểm trên cũng như phục vụ việc quản lý dữ liệu một cách khoa học đã xuất hiện hệ cơ sở dữ liệu.
Một cơ sở dữ liệu (CSDL) là tập hợp các dữ liệu có liên quan đến nhau chứa thông tin về một tổ chức nào đó, được lưu trữ trên các thiết bị nhớ thứ cấp để đáp ứng nhu cầu khai thác thông tin của nhiều người sử dụng với các mục đích khác nhau. Một CSDL phải thỏa mãn hai tính chất:
- Tính độc lập dữ liệu
- Tính chia sẻ dữ liệu
Hệ quản trị CSDL là một hệ thống phần mềm cho phép tạo lập CSDL và điều khiển mọi truy nhập đối với CSDL đó.
Các thao tác truy nhập chủ yếu:
- Tìm kiếm dữ liệu theo chỉ tiêu nào đó
- Bổ sung dữ liệu vào CSDL
- Loại bỏ dữ liệu khỏi CSDL
- Sửa chữa dữ liệu trong CSDL
Các hệ quản trị CSDL hiện nay đều có đặc điểm chung là sử dụng ngôn ngữ truy vấn có cấu trúc Structured Query Language (SQL). Một số hệ quản trị CSDL thường dùng là:MS Access, VFP, SQL Server, Oracle, DB2, Sybase…
2. SQL Server 2005:
Microsoft SQl Server là một hệ quản trị cơ sở dữ liệu quan hệ (relational database management system – RDBMS) do Microsoft phát triển. SQL Server là một hệ quản trị cơ sở dữ liệu quan hệ mạng máy tính hoạt động theo mô hình khách chủ cho phép đồng thời cùng lúc có nhiều người dùng truy xuất đến dữ liệu, quản lý việc truy nhập hợp lệ và các quyền hạn của từng người dùng trên mạng. Ngôn ngữ truy vấn quan trọng của Microsoft SQL Server là Transact-SQL. Một RDBMS bao gồm databases, database engine và các ứng dụng dùng để quản lý dữ liệu và các bộ phận khác trong RDBMS.
Các phiên bản của SQL Server 2005: Enterprise, Standard, Workgroup, Express.
Các thành phần của SQL Server :
- Đối tượng CSDL:
· master: là CSDL chứa tất cả các bảng của hệ thống dùng để kiểm soát mọi hoạt động của SQL Server
· model: là CSDL chứa các khuôn mẫu cho bất kì một CSDL mới nào được tạo ra
· msdb: là CSDL dùng để lưu các tác vụ xảy ra trong SQL Server
· tempdb: là CSDL chứa các bảng tạm do người sử dụng sinh ra
- Tập tin chuyển tác log: là tập tin ghi lại hoạt động của CSDL theo thòi gian
- Các đối tượng cơ bản của CSDL
· Bảng dữ liệu (Tables)
· Chỉ mục (Indexes)
· Bẫy lỗi (Triggers)
· Ràng buộc (Constraints)
- Tập tin nhóm (Filegroups)
- Lược đồ (Diagrams)
- Khung nhìn (Views)
- Thủ tục nội tại (Stored procedures)
- Hàm do người dùng định nghĩa (User Defined Functions)
- Người sử dụng và vai trò của người sử dụng (Users and Roles)
- Các quy tắc (Rules)
- Giá trị ngầm định (Defaults)
- Kiểu dữ liệu người dùng định nghĩa (User-Defined Data Types)
- Phân loại dữ liệu text (Full-text Catalogs)
- Các kiểu dữ liệu trong SQL Server như: bit, int, smallint, money, float, DateTime, nchar, nvarchar…
3. Một số khái niệm cần lưu ý:
- Các thông tin trong thực tế thường được lưu trữ dưới dạng tập hợp các bảng, mỗi bảng gồm các dòng và các cột. Tên bảng là tên quan hệ. Mỗi dòng của bảng chứa dữ liệu tương ứng với dữ liệu của một thực thể. Có một hoặc nhiều giá trị dùng để phân biệt giữa các dòng. Trên mỗi cột, các giá trị có cùng kiểu dữ liệu.
- Những giá trị không xác định được biểu diễn trong CSDL quan hệ bởi các giá trị Null. Đây là giá trị đặc biệt, đóng vai trò quan trọng trong CSDL. Hầu hết các hệ quản trị CSDL đều hỗ trợ việc sử dụng giá trị này.
- Các ràng buộc dữ liệu: một ràng buộc (Constraint) là một hạn chế việc cập nhật dữ liệu lên CSDL. Ràng buộc có thể được đặt trên một hay nhiều cột hoặc bảng, nó đảm bảo rằng dữ liệu phải đáp ứng một số quy tắc toàn vẹn dữ liệu.
· Ràng buộc PRIMARY KEY là ràng buộc áp lên một cột của bảng dữ liệu. Khi đó, giá trị của cột đối với mỗi hàng (bản ghi) trong bảng dữ liệu là duy nhất. Ràng buộc PRIMARY KEY được sử dụng để định nghĩa khoá chính của bảng. Mỗi một bảng chỉ có thể có duy nhất một khoá chính và bản thân khoá chính không chấp nhận giá trị NULL. Ràng buộc PRIMARY KEY là cơ sở cho việc đảm bảo tính toàn vẹn thực thể cũng như toàn vẹn tham chiếu.
· Ràng buộc FOREIGN KEY (Khóa ngoại) là một cột hay một sự kết hợp của nhiều cột được sử dụng để áp đặt mối liên kết dữ liệu giữa hai bảng dữ liệu. FOREIGN KEY của bảng này sẽ là PRIMARY KEY của một bảng khác. Trong một bảng có thể tạo ra nhiều FOREIGN KEY. Cột FOREIGN KEY có thể chứa giá trị NULL. Ràng buộc FOREIGN KEY để kiểm soát dữ liệu có trong bảng con đồng thời kiểm soát dữ liệu trong bảng cha.
· Ràng buộc UNIQUE là ràng buộc gần giống PRIMARY KEY, nó xác định các hàng trong bảng phải hoàn toàn khác nhau.
· Ràng buộc CHECK được sử dụng nhằm chỉ định điều kiện hợp lệ đối với dữ liệu. Mỗi khi có sự thay đổi dữ liệu trên bảng (INSERT, UPDATE), những ràng buộc này sẽ được sử dụng nhằm kiểm tra xem dữ liệu mới có hợp lệ hay không.
· Ràng buộc DEFAULT áp đặt cho cột của bảng dữ liệu. Khi có dòng mới được thêm vào bảng dữ liệu, nếu không xác định được dữ liệu cho cột thì SQL Server sẽ điền dữ liệu mặc định một cách tự động.
Chương II: Tìm hiểu về Cơ sở dữ liệu mẫu Northwind
Northwind là CSDL mẫu của SQL Server 2000 và những bản trước đó. Trong SQL Server 2005, Microsoft đã thay thế Northwind bằng CSDL AdventureWorks.
CSDL mẫu Northwind có chứa các dữ liệu về việc xuất, nhập khẩu các mặt hàng thực phẩm trên thế giới của một công ty có tên giả định là Northwind.
Các thành phần trong Northwind:
1.Database Diagrams
2.Tables
3.Views
4.Synonyms
5.Programmability
6.Security
1. Database Diagrams:
Đây là nơi mô tả một cách trực quan quan hệ giữa các bảng có trong CSDL.
Ví dụ: ta thể hiện mối quan hệ giữa 4 bảng Products, Order Details, Order, Customers như hình dưới
Ở đây, mỗi bảng là một ô, các cột ở mỗi bảng đều được thể hiện ở các ô này. Ví dụ như bảng Order Details có 5 cột gồm: OrderID, ProductID, UnitPrice, Quantity, Discount. Trong đó ProductID, OrderID là khóa.
2. Các bảng cơ sở dữ liệu - Tables
Tên bảng
Mô tả
Categories
Các loại sản phẩm của Northwind
CustomerCustomerDemo
CustomerDemographics
Customers
Tên của các khách hàng, địa chỉ, số điện thoại
Employees
Tên của các nhân viên, chức vụ và thông tin cá nhân
EmployeeTerritories
Mã nhân viên và mã hạt
Order Details
Các chi tiết về sản phẩm, số lượng và đơn giá cho mỗi đơn đặt hàng trong bảng Orders
Orders
Tên khách hàng, ngày đặt hàng và cước phí vận chuyển cho mỗi đơn hàng
Products
Tên sản phẩm, các nhà cung cấp, giá và đơn vị tồn kho
Region
Mã vùng, tên vùng
Shippers
Tên và số điện thoại của nhà vận chuyển
Suppliers
Tên, địa chỉ, số điện thoại và link trang chủ của nhà cung cấp.
Territories
Mã hạt (thuộc vùng), tên hạt, mã vùng
Lưu ý:
PK: khóa chính
FK: khóa ngoại
Clust: Clustered Index
Non-Clust: Non-Clustered Index
Allow Nulls: có thể nhận giá trị Null
2.1 Bảng Categories
Đây là câu lệnh tạo bảng Categories với khóa chính là CategoryID sắp xếp theo kiểu tăng dần
CREATE TABLE [dbo].[Categories](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Picture] [image] NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Các cột dữ liệu trong bảng:
Tên cột
Kiểu dữ liệu
Allow Nulls
Khóa/Index
Mô tả
CategoryID
Int
PK/Clust
Số được tự động tăng với mỗi loại hàng mới
CategoryName
nvarchar(15)
Non-clust
Tên loại thực phẩm
Description
ntext
ü
Mô tả chi tiết cho loại thực phẩm
Picture
image
ü
Ảnh đại diện cho loại thực phẩm
Các mối quan hệ:
Bảng cha
Khóa chính
Bảng con
Khóa ngoại
Categories
CategoryID
Products
CategoryID
Không có ràng buộc
2.2 Bảng CustomerCustomerDemo
Đây là câu lệnh tạo bảng CustomerCustomerDemo.
CREATE TABLE [dbo].[CustomerCustomerDemo](
[CustomerID] [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomerTypeID] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_CustomerCustomerDemo] PRIMARY KEY NONCLUSTERED
[CustomerID] ASC,
[CustomerTypeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerCustomerDemo] WITH CHECK ADD CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY([CustomerTypeID])
REFERENCES [dbo].[CustomerDemographics] ([CustomerTypeID])
ALTER TABLE [dbo].[CustomerCustomerDemo] CHECK CONSTRAINT [FK_CustomerCustomerDemo]
GO
ALTER TABLE [dbo].[CustomerCustomerDemo] WITH CHECK ADD CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
GO
ALTER TABLE [dbo].[CustomerCustomerDemo] CHECK CONSTRAINT [FK_CustomerCustomerDemo_Customers]
Ở bảng này, CustomerID, CustomerTypeID vừa là khóa chính vừa là khóa ngoại. CustomerTypeID là khóa ngoại sẽ tham chiếu đến bảng CustomerDemoGraphic, CustomerID là khóa ngoại sẽ tham chiếu đến bảng Customers
Các cột dữ liệu
Tên cột
Kiểu dữ liệu
Allow Nulls
Khóa/Index
CustomerID
nchar(5)
PK, FK
CustomerTypeID
nchar(10)
PK, FK
Các quan hệ
Bảng cha
Khóa chính
Bảng con
Khóa ngoại
CustomerCustomerDemo
CustomerTypeID
CustomerDemoGraphic
CustomerTypeID
2.3 Bảng CustomerDemoGraphics
Đây là câu lệnh tạo bảng CustomerDemoGraphics có khóa chính là CustomerTypeID
CREATE TABLE [dbo].[CustomerDemographics](
[CustomerTypeID] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomerDesc] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY NONCLUSTERED
[CustomerTypeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Các cột dữ liệu:
Tên cột
Kiểu dữ liệu
Allow Nulls
Khóa/Index
CustomerTypeID
PK
CustomerDesc
ntext
ü
Các quan hệ
Bảng cha
Khóa chính
Bảng con
Khóa ngoại
CustomerCustomerDemo
CustomerTypeID
CustomerDemoGraphic
CustomerTypeID
2.4 Bảng Customers
Đây là câu lệnh tạo bảng Customers có khóa chính là CustomerID
CREATE TABLE [dbo].[Customers](
[CustomerID] [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CompanyName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContactName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContactTitle] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Region] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostalCode] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Country] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [nvarchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fax] [nvarchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Các cột dữ liệu trong bảng:
Tên cột
Kiểu dữ liệu
Allow Nulls
Khóa/Index
Mô tả
CustomerID
nchar(5)
PK/Clust
Mã hóa 5 kí tự dựa vào tên khách hàng
CompanyName
nvarchar(40)
Non-Clust
Tên công ty khách hàng
ContactName
nvarchar(30)
ü
Tên người giao dịch
ContactTitle
nvarchar(30)
ü
Chức vụ của người giao dịch
Address
nvarchar(60)
ü
Địa chỉ
City
nvarchar(15)
ü
Non-Clust
Tên thành phố
Region
nvarchar(15)
ü
Non-Clust
Tên vùng
PostalCode
nvarchar(10)
ü
Non-Clust
Mã bưu điện
Country
nvarchar(15)
ü
Tên nước khách hàng ở
Phone
nvarchar(24)
ü
Số điện thoại bao gồm cả mã nước hoặc mã vùng
Fax
nvarchar(24)
ü
Số fax bao gồm cả mã nước hoặc mã vùng
Các mối quan hệ
Bảng cha
Khóa chính
Bảng con
Khóa ngoại
Customers
CustomerID
Orders
CustomerID
Không có ràng buộc
2.5 Bảng Employees
Đây là câu lệnh tạo bảng Employees với khóa chính là EmployeeID
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Title] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TitleOfCourtesy] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BirthDate] [datetime] NULL,
[HireDate] [datetime] NULL,
[Address] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Region] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostalCode] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Country] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HomePhone] [nvarchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Extension] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Photo] [image] NULL,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReportsTo] [int] NULL,
[PhotoPath] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Employees] ([EmployeeID])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Employees]
GO
ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD CONSTRAINT [CK_Birthdate] CHECK (([BirthDate] < getdate()))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Birthdate]
Ở bảng này, khóa ngoài là ReportsTo tham chiếu đến cột EmployeeID chính trong bảng Employees.
Cột BirthDate có ràng buộc CHECK: BirthDate<getdate()
Các cột dữ liệu:
Tên cột
Kiểu dữ liệu
Allow Nulls
Khóa/Index
Mô tả
EmployeeID
PK/Clust
Tăng tự động lên 1 với mỗi nhân viên mới
LastName
nvarchar(20)
Non-clust
Họ nhân viên
FirstName
nvarchar(10)
Tên nhân viên
Title
nvarchar(30)
ü
Chức vụ
TitleOfCourtesy
nvarchar(25)
ü
Danh xưng (Ông/Bà)
BirthDate
datetime
ü
Ngày sinh
HireDate
datetime
ü
Ngày vào làm
Address
nvarchar(50)
ü
Địa chỉ
City
nvarchar(15)
ü
Tên thành phố
Region
nvarchar(15)
ü
Tên vùng
PostalCode
nvarchar(10)
ü
Non-clust
Mã bưu điện
Country
nvarchar(15)
ü
Tên nước
HomePhone
nvarchar(24)
ü
Số điện thoại nhà
Extension
nvarchar(4)
ü
Số máy lẻ
Photo
image
ü
Ảnh nhân viên
Notes
ntext
ü
Một vài ghi chú về nhân viên
ReportsTo
ü
FK (Employees)
EmployeeID
Nộp báo cáo cho ai
PhotoPath
nvarchar(255)
ü
Đường dẫn ảnh
Các mối quan hệ:
Bảng cha
Khóa chính
Bảng con
Khóa ngoại
Employees
EmployeeID
Order
EmployeeID
EmployeeTerritories
EmployeeID
Ràng buộc:
Cột BirthDate có ràng buộc CHECK: BirthDate<getdate()
2.6 Bảng EmployeeTerritories
Đây là câu lệnh tạo bảng EmployeeTerritories với khóa chính là EmployeeID và TerritoryID
CREATE TABLE [dbo].[EmployeeTerritories](
[EmployeeID] [int] NOT NULL,
[TerritoryID] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY NONCLUSTERED
[EmployeeID] ASC,
[TerritoryID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[EmployeeTerritories] WITH CHECK ADD CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employees] ([EmployeeID])
GO
ALTER TABLE [dbo].[EmployeeTerritories] CHECK CONSTRAINT [FK_EmployeeTerritories_Employees]
GO
ALTER TABLE [dbo].[EmployeeTerritories] WITH CHECK ADD CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY([TerritoryID])
REFERENCES [dbo].[Territories] ([TerritoryID])
GO
ALTER TABLE [dbo].[EmployeeTerritories] CHECK CONSTRAINT [FK_EmployeeTerritories_Territories]
Bảng này có EmployeeID, TerritoryID sẽ tham chiếu lần lượt đến bảng Employees và bảng Territories.
Các cột:
Tên cột
Kiểu dữ liệu
Allow Nulls
Khóa/Index
EmployeeID
PK, FK
TerritoryID
nvarchar(20)
PK, FK
Các quan hệ:
Bảng cha
Khóa chính
Bảng con
Khóa ngoại
Employees
EmployeeID
EmployeeTerritories
EmployeeID
Territories
TerritoryID
EmployeeTerritories
TerritotyID
Không có ràng buộc
2.7 Bảng Order Details
Đây là câu lệnh tạo bảng Order Details với khóa chính là OrderID va ProductID
CREATE TABLE [dbo].[Order Details](
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT (0),
[Quantity] [smallint] NOT NULL CONSTRAINT [DF_Order_Details_Quantity] DEFAULT (1),
[Discount] [real] NOT NULL CONSTRAINT [DF_Order_Details_Discount] DEFAULT (0),
CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED
[OrderID] ASC,
[ProductID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
GO
ALTER TABLE [dbo].[Order Details] CHECK CONSTRAINT [FK_Order_Details_Orders]
GO
ALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
GO
ALTER TABLE [dbo].[Order Details] CHECK CONSTRAINT [FK_Order_Details_Products]
GO
ALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD CONSTRAINT [CK_Discount] CHECK (([Discount] >= 0 and [Discount] <= 1))
GO
ALTER TABLE [dbo].[Order Details] CHECK CONSTRAINT [CK_Discount]
GO
ALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD CONSTRAINT [CK_Quantity] CHECK (([Quantity] > 0))
GO
ALTER TABLE [dbo].[Order Details] CHECK CONSTRAINT [CK_Quantity]
GO
ALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD CONSTRAINT [CK_UnitPrice] CHECK (([UnitPrice] >= 0))
GO
ALTER TABLE [dbo].[Order Details] CHECK CONSTRAINT [CK_UnitPrice]
Bảng có khóa ngoại là ProductID tham chiếu đến bảng Products và một số ràng buộc CHECK
Các cột dữ liệu:
Tên cột
Kiểu dữ liệu
Allow Nulls
Khóa/Index
Mô tả
OrderID
Int
PK,Clust,FK
giống trong bảng Orders
ProductID
Int
PK,Clust,FK
giống trong bảng Products
UnitPrice
Money
Giá đơn vị
Quantity
Smallint
Số lượng
Discount
Real
Tỉ lệ chiết khấu
Các ràng buộc:
- Cột UnitPrice ràng buộc CHECK (UnitPrice >= 0)
- Cột Quantity ràng buộc CHECK (Quantity > 0)
- Toàn bảng Ràng buộc CHECK (Discount >= 0 and Discount <= 1)
2.8 Bảng Orders
Đây là câu lệnh tạo bảng Orders với khóa chính là CustomerID, khóa ngoại là EmployeeID tham chiếu đến bảng Employees và ShipVia tham chiếu đến cột ShipperID trong bảng Shippers.
CREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmployeeID] [int] NULL,
[OrderDate] [datetime] NULL,
[RequiredDate] [datetime] NULL,
[ShippedDate] [datetime] NULL,
[ShipVia] [int] NULL,
[Freight] [money] NULL CONSTRAINT [DF_Orders_Freight] DEFAULT (0),
[ShipName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShipAddress] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShipCity] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShipRegion] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShipPostalCode] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShipCountry] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
[OrderID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_Employees] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employees] ([EmployeeID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Employees]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY([ShipVia])
REFERENCES [dbo].[Shippers] ([ShipperID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Shippers]
Các cột dữ liệu:
Tên cột
Kiểu dữ liệu
Allow Nulls
Khóa/Index
Mô tả
OrderID
Int
PK, Clust
Tự tăng 1 với mỗi hóa đơn mới
CustomerID
Nchar(5)
ü
FK Customers (CustomerID)
giống trong bảng Customers
EmployeeID
Int
ü
FK Employees (EmployeeID)
giống trong bảng Employees
OrderDate
Datetime
ü
Non-clust
Ngày đặt hàng
RequiredDate
Datetime
ü
Ngày nhận hàng
ShippedDate
Datetime
ü
Non-clust
Ngày chuyển hàng
Shipvia
Int
ü
FK Shippers(ShipperID)
giống ShipperID trong bảng Shippers
Freight
Money
ü
Cước vận chuyển
ShipName
Nvarchar(40)
ü
tên người hoặc công ty nhận xếp hàng hóa
ShipAddress
Nvarchar(60)
ü
Địa chỉ
ShipCity
Nvarchar(15)
ü
Tên thành phố chuyển hàng đến
ShipRegion
Nvarchar(15)
ü
Tên vùng chuyển hàng đến
ShipPostalCode
Nvarchar(10)
ü
Non-clust
Mã bưu điện nơi hàng chuyển đến
ShipCountry
Nvarchar(15)
ü
Tên đất nước nơi hàng chuyển đến
Các mối quan hệ:
Bảng cha
Khóa chính
Bảng con
Khóa ngoài
Customers
CustomerID
Orders
CustomerID
Employees
EmployeeID
Orders
EmployeeID
Shippers
ShipperID
Orders
Shipvia
Orders
OrderID
Order Details
OrderID
Không có ràng buộc
2.9 Bảng Products
Đây là câu lệnh tạo bảng Products, một số cột có thiết lập giá trị mặc định. Khóa chính của bảng ProductID, khóa ngoại là CategoryID và SupplierID tham chiếu lần lượt đến các bảng Categories và Suppliers. Một số ràng buộc CHECK.
CREATE TABLE [dbo].[Products](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SupplierID] [int] NULL,
[CategoryID] [int] NULL,
[QuantityPerUnit] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UnitPrice] [money] NULL CONSTRAINT [DF_Products_UnitPrice] DEFAULT (0),
[UnitsInStock] [smallint] NULL CONSTRAINT [DF_Products_UnitsInStock] DEFAULT (0),
[UnitsOnOrder] [smallint] NULL CONSTRAINT [DF_Products_UnitsOnOrder] DEFAULT (0),
[ReorderLevel] [smallint] NULL CONSTRAINT [DF_Products_ReorderLevel] DEFAULT (0),
[Discontinued] [bit] NOT NULL CONSTRAINT [DF_Products_Discontinued] DEFAULT (0),
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
[ProductID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Products] WITH NOCHECK ADD CONSTRAINT [FK_Products_Categories] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Categories] ([CategoryID])
GO
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Categories]
GO
ALTER TABLE [dbo].[Products] WITH NOCHECK ADD CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY([SupplierID])
REFERENCES [dbo].[Suppliers] ([SupplierID])
GO
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Suppliers]
GO
ALTER TABLE [dbo].[Products] WITH NOCHECK ADD CONSTRAINT [CK_Products_UnitPrice] CHECK (([UnitPrice] >= 0))
GO
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [CK_Products_UnitPrice]
GO
ALTER TABLE [dbo].[Products] WITH NOCHECK ADD CONSTRAINT [CK_ReorderLevel] CHECK (([ReorderLevel] >= 0))
GO
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [CK_ReorderLevel]
GO
ALTER TABLE [dbo].[Products] WITH NOCHECK ADD CONSTRAINT [CK_UnitsInStock] CHECK (([UnitsInStock] >= 0))
GO
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [CK_UnitsInStock]
GO
ALTER TABLE [dbo].[Products] WITH NOCHECK ADD CONSTRAINT [CK_UnitsOnOrder] CHECK (([UnitsOnOrder] >= 0))
GO
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [CK_UnitsOnOrder]
Các cột dữ liệu:
Tên cột
Kiểu dữ liệu
Allow Nulls
Khóa/Index
Mô tả
ProductID
Int
PK,Clust
Số tự động được đánh dấu cho sản phẩm mới
ProductName
Nvarchar(40)
non-clust
Tên sản phẩm
SupplierID
ü
FK Suppliers(SupplierID)
Nhập vào giống bảng Suppliers
CategoryID
Int
ü
FK Categories(CategoryID)
Nhập vào giống bảng Categories
QuantityPerUnit
Nvarchar(20)
ü
Số lượng trên mỗi đơn vị
Unit Price
money
ü
Giá trên mỗi đơn vị
UnitsInStock
Smallint
ü
Số đơn vị trong kho
UnitsOnOrder
Smallint
ü
Số đơn vị trong đơn đặt hàng
ReorderLevel
smallint
ü
Số lần đặt mua bổ sung
Discontinued
Bit
.T. tức là ngừng bán
Các quan hệ :
Bảng cha
Khóa chính
Bảng con
Khóa ngoại
Categories
CategoryID
Products
CategoryID
Suppliers
SupplierID
Products
SupplierID
Products
ProductID
OrderDetails
ProductID
Các ràng buộc:
- Cột UnitPrice ràng buộc CHECK : (UnitPrice >= 0)
- Cột UnitInStock ràng buộc CHECK: (UnitInStock >= 0)
- Cột UnitOnOrder ràng buộc CHECK: (UnitOnOrder >= 0)
- Cột RecordsLevel ràng buộc CHECK: (RecordsLevel >= 0)
2.10 Bảng Region
Đây là câu lệnh tạo bảng Region với khóa chính là RegionID
CREATE TABLE [dbo].[Region](
[RegionID] [int] NOT NULL,
[RegionDescription] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Region] PRIMARY KEY NONCLUSTERED
[RegionID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Các cột dữ liệu:
Name
Kiểu dữ liệu
Allow Nulls
Khóa/Index
Mô tả
RegionID
Int
PK
Số tự động đánh cho khu vực
RegionDescription
Nchar(50)
Mô tả khu vực ( Bắc, Nam, Đông, Tây)
Không có ràng buộc
2.11 Bảng Shippers
Đây là câu lệnh tạo bảng Shippers với khóa chính là ShipperID
CREATE TABLE [dbo].[Shippers](
[ShipperID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Phone] [nvarchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Shippers] PRIMARY KEY CLUSTERED
[ShipperID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Các cột dữ liệu
Name
Kiểu dữ liệu
Allow Nulls
PK/Index
Mô tả
ShipperID
Int
PK
Tự động tăng 1 cho mỗi nhà vận chuyển mới
CompanyName
Nvarchar(40)
Tên công ty vận chuyển
Phone
Nvarchar(24)
ü
Số điện thoại công ty vận chuyển (bao gồm cả mã quốc gia và mã khu vực)
Các quan hệ
Bảng cha
Khóa chính
Bảng con
Khóa ngoại
Shippers
ShipperID
Orders
ShipVia
Không có ràng buộc
2.12 Bảng Suppliers
Đây là câu lệnh tạo bảng Suppliers với khóa chính là SupplierID
CREATE TABLE [dbo].[Suppliers](
[SupplierID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContactName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContactTitle] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Region] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostalCode] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Country] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [nvarchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fax] [nvarchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HomePage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Suppliers] PRIMARY KEY CLUSTERED
[SupplierID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Các cột dữ liệu:
Tên
Kiểu dữ liệu
Allow Nulls
PK/Index
Mô tả
SupplierID
Int
PK, clust
Số được tự động đánh cho nhà cung cấp mới
CompanyName
Nvarchar(40)
non-clust
Tên nhà cung cấp
ContactName
Nvarchar(30)
ü
Tên người đại diện của nhà cung cấp
ContactTitle
Nvarchar(30)
ü
Chức vụ người đại diện của nhà cung cấp
Address
Nvarchar(60)
ü
Tên phố hoặc hộp thư bưu điện
City
Nvarchar(15)
ü
Tên thành phố nơi nhà cung cấp đặt trụ sở
Region
Nvarchar(15)
ü
Tên tiểu bang hoặc tỉnh
PostalCode
Nvarchar(10)
ü
non-clust
Mã bưu chính nơi nhà cung cấp đặt trụ sở
Country
Nvarchar(15)
ü
Tên của quốc gia nơi nhà cung cấp đặt trụ sở
Phone
Nvarchar(24)
ü
Số điện thoại bao gồm mã quốc gia hoặc mã vung
Fax
Nvarchar(24)
ü
Số Fax bao gồm mã quốc gia hoặc mã vùng
HomePage
Ntext
ü
Trang chủ nhà cung cấp trên World Wide Web
Các quan hệ:
Bảng cha
Khóa chính
Bảng con
Khóa ngoại
Suppliers
SupplierID
Products
SupplierID
Không có ràng buộc
2.13 Bảng Territories:
Đây là câu lệnh tạo bảng Territories vói khóa chính là TerritoryID, khóa ngoài là RegionID tham chiếu đến bảng Regions.
CREATE TABLE [dbo].[Territories](
[TerritoryID] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TerritoryDescription] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RegionID] [int] NOT NULL,
CONSTRAINT [PK_Territories] PRIMARY KEY NONCLUSTERED
[TerritoryID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Territories] WITH CHECK ADD CONSTRAINT [FK_Territories_Region] FOREIGN KEY([RegionID])
REFERENCES [dbo].[Region] ([RegionID])
GO
ALTER TABLE [dbo].[Territories] CHECK CONSTRAINT [FK_Territories_Region]
Các cột dữ liệu:
Tên cột
Kiểu dữ liệu
Allow Nulls
Khóa/Index
Mô tả
TerritoryID
Nvarchar(20)
PK
Số của vùng lãnh thổ
TerritoryDescription
Nvarchar(50)
Mô tả vùng lãnh thổ
RegionID
FK
Số của tiểu bang hoặc tỉnh
Các quan hệ
Bảng cha
Khóa chính
Bảng con
Khóa ngoại
Torritories
TerritoryID
EmployeeTerritories
TerritoryID
Region
RegionID
Territories
RegionID
Không có ràng buộc
3. View
Chúng ta sử dụng khung nhìn (View) để tìm kiếm một tập các cột từ 1 hoặc nhiều bảng. View có thể được cho như là một cách khá linh hoạt trong việc xem xét các hàng trong các bảng. Chúng ta có thể tạo mới, xem xét các thuộc tính, truy vấn các hàng thông qua một view.
Ví dụ: Chúng ta muốn tìm kiếm danh sách các sản phẩm theo thứ tự bảng chữ cái, tìm kiếm tên sản phẩm, tên loại sản phẩm. Khí đó, thông tin sẽ được tổng hợp từ bảng Products và bảng Categories.
Sau đây là các View trong Northwind
3.1 Alphabetical list of products
Liệt kê danh sách các sản phẩm theo loại sản phẩm. Dữ liệu được lấy từ bảng Categories và bảng Products với điều kiện là các mặt hàng vẫn còn xuất bán.
SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.SupplierID,dbo.Products.CategoryID,dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice, dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder, dbo.Products.ReorderLevel, dbo.Products.Discontinued, dbo.Categories.CategoryName
FROM dbo.Categories INNER JOIN
dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID
WHERE (dbo.Products.Discontinued = 0)
3.2 Category Sales for 1997
Tính tổng doanh thu bán hàng của các mặt hàng trong năm 1997
SELECT CategoryName, SUM(ProductSales) AS CategorySales FROM dbo.[Product Sales for 1997]
GROUP BY CategoryName
3.3 Current Product List
Danh sách các sản phẩm hiện có, với điều kiện là sản phẩm này vẫn còn được xuất bán
SELECT ProductID, ProductName
FROM dbo.Products AS Product_List
WHERE (Discontinued = 0)
3.4 Customer and Suppliers by City
Danh sách các khách hàng và nhà cung cấp theo từng thành phố
SELECT City, CompanyName, ContactName, 'Customers' AS Relationship
FROM dbo.Customers
UNION
SELECT City, CompanyName, ContactName, 'Suppliers' AS Expr1
FROM dbo.Suppliers
3.5 Invoices
In hóa đơn, các thông tin cần thiết được lấy từ các cột đánh dấu trong 6 bảng dưới đây, có tính toán các khoản chiết khấu
SELECT dbo.Orders.ShipName, dbo.Orders.ShipAddress, dbo.Orders.ShipCity, dbo.Orders.ShipRegion, dbo.Orders.ShipPostalCode, dbo.Orders.ShipCountry,
dbo.Orders.CustomerID, dbo.Customers.CompanyName AS CustomerName, dbo.Customers.Address, dbo.Customers.City, dbo.Customers.Region,
dbo.Customers.PostalCode, dbo.Customers.Country, dbo.Employees.FirstName + ' ' + dbo.Employees.LastName AS Salesperson, dbo.Orders.OrderID,
dbo.Orders.OrderDate, dbo.Orders.RequiredDate, dbo.Orders.ShippedDate, dbo.Shippers.CompanyName AS ShipperName, dbo.[Order Details].ProductID,
dbo.Products.ProductName, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, dbo.[Order Details].Discount, CONVERT(money,
(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount) / 100) * 100 AS ExtendedPrice, dbo.Orders.Freight
FROM dbo.Shippers INNER JOIN
dbo.Products INNER JOIN
dbo.Employees INNER JOIN
dbo.Customers INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID ON dbo.Employees.EmployeeID = dbo.Orders.EmployeeID INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID ON dbo.Products.ProductID = dbo.[Order Details].ProductID ON
dbo.Shippers.ShipperID = dbo.Orders.ShipVia
3.6 Order Details Extended
Với view này, chúng ta nhìn được rộng hơn chi tiết các hóa đơn (ở đây có thêm cột tên sản phẩm) và có tính toán số tiền của mỗi hóa đơn.
SELECT dbo.[Order Details].OrderID, dbo.[Order Details].ProductID, dbo.Products.ProductName, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity,
dbo.[Order Details].Discount, CONVERT(money, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount) / 100)
* 100 AS ExtendedPrice
FROM dbo.Products INNER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID
3.7 Order SubTotals
Tính tổng tiền của mỗi hóa đơn theo từng mã
SELECT OrderID, SUM(CONVERT(money, (UnitPrice * Quantity) * (1 - Discount) / 100) * 100) AS Subtotal
FROM dbo.[Order Details]
GROUP BY OrderID
3.8 Order Qry
Liệt kê đầy đủ các thông tin của từng hóa đơn liên quan đến nhân viên, khách hàng, ngày tháng nhận, giao hàng, cách thức vận chuyển, cước phí vận chuyển…
SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.EmployeeID, dbo.Orders.OrderDate, dbo.Orders.RequiredDate, dbo.Orders.ShippedDate, dbo.Orders.ShipVia,
dbo.Orders.Freight, dbo.Orders.ShipName, dbo.Orders.ShipAddress, dbo.Orders.ShipCity, dbo.Orders.ShipRegion, dbo.Orders.ShipPostalCode,
dbo.Orders.ShipCountry, dbo.Customers.CompanyName, dbo.Customers.Address, dbo.Customers.City, dbo.Customers.Region, dbo.Customers.PostalCode,
dbo.Customers.Country
FROM dbo.Customers INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
3.9 Product Sales for 1997
Doanh thu bán hàng của từng sản phẩm trong năm 1997, đồng thời cũng biết được sản phẩm đó thuộc loại sản phẩm nào.
SELECT dbo.Categories.CategoryName, dbo.Products.ProductName, SUM(CONVERT(money, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity)
* (1 - dbo.[Order Details].Discount) / 100) * 100) AS ProductSales
FROM dbo.Categories INNER JOIN
dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID INNER JOIN
dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID ON dbo.Products.ProductID = dbo.[Order Details].ProductID
WHERE (dbo.Orders.ShippedDate BETWEEN '19970101' AND '19971231')
GROUP BY dbo.Categories.CategoryName, dbo.Products.ProductName
3.10 Products Above Average Price
Cho biết sản phẩm nào có giá đơn vị cao hơn mức giá đơn vị trung bình
SELECT ProductName, UnitPrice
FROM dbo.Products
WHERE (UnitPrice >
(SELECT AVG(UnitPrice) AS Expr1
FROM dbo.Products))
3.11 Products by Category
Danh sách các sản phẩm theo từng loại mà công ty vẫn còn xuất bán
SELECT dbo.Categories.CategoryName, dbo.Products.ProductName, dbo.Products.QuantityPerUnit, dbo.Products.UnitsInStock, dbo.Products.Discontinued
FROM dbo.Categories INNER JOIN
dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID
WHERE (dbo.Products.Discontinued <> 1)
3.12 Quarterly Order
Danh sách khách hàng có ngày đặt hàng trong năm 1997
SELECT DISTINCT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Customers.City, dbo.Customers.Country
FROM dbo.Customers RIGHT OUTER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
WHERE (dbo.Orders.OrderDate BETWEEN '19970101' AND '19971231')
3.13 Sales by Category
Doanh thu bán hàng theo từng loại sản phẩm
SELECT dbo.Categories.CategoryID, dbo.Categories.CategoryName, dbo.Products.ProductName, SUM(dbo.[Order Details Extended].ExtendedPrice) AS ProductSales
FROM dbo.Categories INNER JOIN
dbo.Products INNER JOIN
dbo.Orders INNER JOIN
dbo.[Order Details Extended] ON dbo.Orders.OrderID = dbo.[Order Details Extended].OrderID ON dbo.Products.ProductID = dbo.[Order Details Extended].ProductID ON
dbo.Categories.CategoryID = dbo.Products.CategoryID
WHERE (dbo.Orders.OrderDate BETWEEN '19970101' AND '19971231')
GROUP BY dbo.Categories.CategoryID, dbo.Categories.CategoryName, dbo.Products.ProductName
3.14 Sales Totals by Amount
SELECT dbo.[Order Subtotals].Subtotal AS SaleAmount, dbo.Orders.OrderID, dbo.Customers.CompanyName, dbo.Orders.ShippedDate
FROM dbo.Customers INNER JOIN
dbo.Orders INNER JOIN
dbo.[Order Subtotals] ON dbo.Orders.OrderID = dbo.[Order Subtotals].OrderID ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
WHERE (dbo.[Order Subtotals].Subtotal > 2500) AND (dbo.Orders.ShippedDate BETWEEN '19970101' AND '19971231')
3.15 Summary of Sales by Quarter
SELECT dbo.Orders.ShippedDate, dbo.Orders.OrderID, dbo.[Order Subtotals].Subtotal
FROM dbo.Orders INNER JOIN
dbo.[Order Subtotals] ON dbo.Orders.OrderID = dbo.[Order Subtotals].OrderID
WHERE (dbo.Orders.ShippedDate IS NOT NULL)
3.16 Summary of Sales by Year
SELECT dbo.Orders.ShippedDate, dbo.Orders.OrderID, dbo.[Order Subtotals].Subtotal
FROM dbo.Orders INNER JOIN
dbo.[Order Subtotals] ON dbo.Orders.OrderID = dbo.[Order Subtotals].OrderID
WHERE (dbo.Orders.ShippedDate IS NOT NULL)
4. Synonyms
5. Programmability
5.1 Stored Procedures
Stored Procedure- thủ tục thường trú. Đây là một đối tượng trong CSDL gồm nhiều câu lệnh SQL tập hợp lại với nhau thành một nhóm và tất cả các lệnh này sẽ được thực thi khi thủ tục thường trú được thực thi.
Khi một thủ tục được định nghĩa, nó có thể được gọi thông qua tên.
Bên trong thủ tục thường trú có thể sử dụng các biến nhằm lưu trữ các giá trị tính toán được, các giá trị được truy xuất từ CSDL.
Một thủ tục có thể nhận các tham số truyền vào cũng như có thể trả về các giá trị thông qua các tham số sau khi thực hiện xong.
5.1.1 CustOrderHist
CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
5.1.2 CustOrderDetail
CREATE PROCEDURE [dbo].[CustOrdersDetail] @OrderID int
AS
SELECT ProductName,
UnitPrice=ROUND(Od.UnitPrice, 2),
Quantity,
Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID
5.1.3 CustOrdersOrders
CREATE PROCEDURE [dbo].[CustOrdersOrders] @CustomerID nchar(5)
AS
SELECT OrderID,
OrderDate,
RequiredDate,
ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID
5.1.4 Employee Sales by Country
CREATE PROCEDURE [dbo].[Employee Sales by Country]
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN
(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
5.1.5 Sales by Year
CREATE PROCEDURE [dbo].[Sales by Year]
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
5.1.6 Sales by Category
CREATE PROCEDURE [dbo].[SalesByCategory]
@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
SELECT @OrdYear = '1998'
END
SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
5.1.7 Ten Most Expensive Product
CREATE PROCEDURE [dbo].[Ten Most Expensive Products] AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC
5.2 Functions
5.3 Data Triggers
Trigger là một dạng đặc biệt của thủ tục thường trú, được thực thi một cách tự động khi có sự thay đổi dữ liệu (do tác động của lệnh INSERT, UPDATE, DELETE) trên một bảng nào đó
5.4 Assemblies
5.5 Types
5.6 Rules
5.7 Default
6.Security
6.1 Users
· dbo
· guest
· INFORMATION_SCHEMA
· sys
6.2 Roles
· db_accessadmin
· db_backupoperator
· db_datareader
· db_datawriter
· db_ddladmin
· db_denydatareader
· db_denydatawriter
· db_owner
· db_securityadmin
· public
6.3 Schema
Bạn đang đọc truyện trên: Truyen2U.Pro