Thứ Tư, 3 tháng 6, 2015

[DBM] Assignment 05

Create Database De05

Go
Use De05

Go
Drop Table Person
Create Table Person
(
ID smallint Constraint PK_Contact Primary Key (ID),
HoVaTen varchar(60) NOT NULL,
NgaySinh date,
DiaChi varchar(120)
)

Go
Drop Table PhoneNumber
Create Table PhoneNumber
(
ID smallint Constraint FK_Contact Foreign Key (ID) References Person(ID),
DienThoai numeric
)

Go
Insert Into Person Values ('101', 'Vu Hoang Nam', '1996-01-08', 'Hoang Dao Thuy, Ha Noi')
Insert Into Person Values ('102', 'Chu Bao Ngoc', '1996-07-11', 'Minh Khai, Ha Noi')
Insert Into Person Values ('103', 'Nguyen Thanh Tung', '1996-07-09', 'Thuy Khue, Ha Noi')
Insert Into Person Values ('104', 'Hoang Dung', '1996-04-20', 'Nguyen Thi Thap, Ha Noi')
Insert Into Person Values ('105', 'Bui Huu Viet Hung', '1996-05-07', NULL)
Insert Into Person Values ('106', 'Vu Hoang Nam', '1996-01-08', 'Hoang Dao Thuy, Ha Noi')

Go
Insert Into PhoneNumber Values ('101', 0917463454)
Insert Into PhoneNumber Values ('102', 01653486187)
Insert Into PhoneNumber Values ('103', 0946510796)
Insert Into PhoneNumber Values ('104', 0984075186)
Insert Into PhoneNumber Values ('105', 01658904232)
Insert Into PhoneNumber Values ('106', 0942998858)

Go
--Liệt kê danh sách những người trong danh bạ
Select * From Person

Go
--Liệt kê danh sách số điện thoại có trong danh bạ
Select * From PhoneNumber

Go
--Liệt kê các số điện thoại của người có tên là Nguyễn Văn An.
Select HoVaTen, DienThoai From Person As A
Inner Join PhoneNumber As B On A.ID = B.ID
Where HoVaTen like 'Chu Bao Ngoc'

Go
--Liệt kê những người có ngày sinh là 12/12/09
Select * From Person
Where NgaySinh = '1996-04-20'

Go
--Liệt kê danh sách người trong danh bạ theo thứ thự alphabet.
Select * From Person
Order By HoVaTen ASC

Go
--Tìm số lượng số điện thoại của mỗi người trong danh bạ.
Select HoVaTen, Count(*) As SoLuongSoDT From PhoneNumber As B
Inner Join Person As A On A.ID = B.ID
Group By HoVaTen

Go
--Tìm tổng số người trong danh bạ sinh vào thang 12.
Select Count(HoVaTen) From Person
Where DatePart(mm, NgaySinh) = 7

Go
--Viết câu lệnh để thay đổi trường ngày sinh là trước ngày hiện tại.
Alter Table Person
Add Constraint dateofbirth Check (NgaySinh < GetDate())

Go
--Viết câu lệnh để xác định các trường khóa chính và khóa ngoại của các bảng.
Alter Table PhoneNumber Drop Constraint FK_Contact
Alter Table Person Drop Constraint PK_Contact

Alter Table Person Add Constraint PK_Contact Primary Key (ID)
Alter Table Person Add Constraint FK_Contact Foreign Key (ID) References Person(ID)

Go
--Viết câu lệnh để thêm trường ngày bắt đầu liên lạc.
Alter Table PhoneNumber
Add NgayBatDauLienLac date

Go
--IX_HoTen: Đặt chỉ mục cho cột Họ và tên
Create NonClustered Index IX_FullName
On Person(HoVaTen)

Go
--IX_SoDienThoai: Đặt chỉ mục cho cột Số điện thoại
Create NonClustered Index IX_PhoneNumber
On PhoneNumber(DienThoai)

Go
--View_SoDienThoai: hiển thị các thông tin gồm Họ tên, Số điện thoại
Create View View_SoDienThoai
As
Select HoVaTen, DienThoai
From Person As A
Inner Join PhoneNumber As B On A.ID = B.ID

Go
Select * From View_SoDienThoai

Go
--View_SinhNhat: Hiển thị những người có sinh nhật trong tháng hiện tại (Họ tên, Ngày sinh, Số điện thoại)
Create View View_SinhNhat
As
Select HoVaTen, NgaySinh, DienThoai
From Person As A
Inner Join PhoneNumber As B On A.ID = B.ID
Where DatePart(mm, NgaySinh) = GetDate()

Go
Select * From View_SinhNhat

Go
--SP_Them_DanhBa: Thêm một người mới vào danh bạ
Create Procedure SP_Them_DanhBa
@ID smallint,
@Name char(60),
@BirthDate date,
@Address char(120)
As
Begin
Insert Into Person (ID, HoVaTen, NgaySinh, DiaChi)
Values (@ID, @Name, @BirthDate, @Address)
End

Go
Execute SP_Them_DanhBa '107', 'Do Huy Phong', '1995-01-25', 'Lang Ha, Ha Noi'
Execute SP_Them_DanhBa '108', 'Trinh Bach', '1995-01-30', NULL

Go
Insert Into PhoneNumber (ID, DienThoai) Values ('107', '0982336668')
Insert Into PhoneNumber (ID, DienThoai) Values ('108', '01697151420')

Go
--SP_Tim_DanhBa: Tìm thông tin liên hệ của một người theo tên (gần đúng)
Create Procedure SP_Tim_DanhBa
@FullName char(60)
As
Select HoVaTen, NgaySinh, DiaChi, DienThoai
From Person As A
Inner Join PhoneNumber As B On A.ID = B.ID
Where HoVaTen = @FullName

Go
Execute SP_Tim_DanhBa 'Trinh Bach'

Go
--Hiển thị toàn bộ thông tin về người, của từng số điện thoại.
Select HoVaTen, NgaySinh, DiaChi, DienThoai
From Person As A
Inner Join PhoneNumber As B On A.ID = B.ID

Go
--Hiển thị toàn bộ thông tin về người, của số điện thoại 123456789.
Declare @Number bigint
Set @Number = '0984075186'
Select HoVaTen, NgaySinh, DiaChi, DienThoai
From Person As A
Inner Join PhoneNumber As B On A.ID = B.ID
Where DienThoai = @Number

Không có nhận xét nào:

Đăng nhận xét