IMG-LOGO
×

Giáo Trình Môn Học

Khoá học lập trình SQL

Kiến Thức Core SQL Server

[Video] Hướng dẫn cách xác định kiểu dữ liệu, primary key, foreign key trong SQL - My SQL - SQL Server [Video] Hướng dẫn thao tác nhiểu bảng - select - multi table - join, inner join trong SQL Server [Video] Hướng dẫn phân tích CSDL - phân tích bảng, column trong bản - Học lập trình SQL, SQL Server [Video] Hướng dẫn tìm hiểu create, alter , insert table và bản chất primary key, foreign key - SQL Server [Video] Hướng dẫn tìm hiểu select, and, in, like, not in,group by .. having, order by asc, desc - SQL Server [Video] Quản lý sinh viên (create, alter, update, insert, select) - Lập trình SQL Server

View SQL Server

[Video] Hướng dẫn tìm hiểu view trong SQL Server Bài Giảng: Hướng dẫn tìm hiểu view + proc trong SQL - Lập trình SQL Server

Store & Procedure

[Video] Hướng dẫn tìm hiểu proc procedure trong SQL Server - Lập trình SQL Server, MySQL Bài Giảng: Xây dự hệ thống đưa đón học sinh bằng xe bus của Trường Học - Lập trình SQL Server [Video] Xây dựng CSDL quản lý điểm danh aptech - Lập trình SQL Server/MySQL

Trigger

[Video] Học Trigger qua ví dụ - quản lý nhà trên phố - Lập trình SQL Server [Video] Hướng dẫn học trigger qua bài quản lý điểm danh Aptech - Lập trình SQL Server [Video] Bài tập - Quản lý nhân khẩu - Lập trình SQL Server - MySQL




Trang Chủ SQL Server/MySQL Bài Giảng: Bài tập - Thiết kế CSDL quản lý sở thú - SQL Server

Bài Giảng: Bài tập - Thiết kế CSDL quản lý sở thú - SQL Server

by GokiSoft.com - 17:37 30/10/2021 1,969 Lượt Xem

Bài tập - kiểm tra 60 phút - Lập trình SQL - Thiết kế CSDL quản lý sở thú


-- Tao CSDL (database)
create database BT2209
go

-- Kich hoat database (CSDL)
use BT2209
go

-- Tao bang (create tables)
create table Room (
	id int primary key identity (1,1),
	name nvarchar(20),
	max int
)
go

create table Animal (
	id int primary key identity (1,1),
	name nvarchar(50),
	age int,
	buy_at date,
	room_id int
)
go

create table FoodType (
	id int primary key identity (1,1),
	name nvarchar(50),
	price float,
	amount int
)
go

create table FoodAnimal (
	food_id int not null,
	animal_id int not null,
	primary key (food_id, animal_id)
)
go


2. Tạo primary key & foreign key cho từng bảng -> Không tạo trong câu lệnh create table



-- Foreign Key
alter table Animal
add constraint fk_animal_room foreign key (room_id) references Room (id)
go

alter table FoodAnimal
add constraint fk_foodanimal_foodtype foreign key (food_id) references FoodType (id)
go

alter table FoodAnimal
add constraint fk_foodanimal_animal foreign key (animal_id) references Animal (id)
go

3. Thêm mỗi bảng 5 bản ghi dữ liệu



-- Thêm mỗi bảng 5 bản ghi dữ liệu
insert into Room (name, max)
values
('R01', 5),
('R02', 2),
('R03', 3),
('R04', 50),
('R05', 10)
go

insert into FoodType (name, amount, price)
values
('Thuc An A', 20, 100000),
('Thuc An B', 10, 200000),
('Thuc An C', 30, 300000),
('Thuc An D', 10, 150000),
('Thuc An E', 10, 120000)
go

insert into Animal (name, age, buy_at, room_id)
values
('Dong Vat A', 2, '2020-02-16', 1),
('Dong Vat B', 1, '2020-02-26', 1),
('Dong Vat C', 3, '2020-02-12', 1),
('Dong Vat D', 5, '2020-02-15', 1),
('Dong Vat E', 2, '2020-06-16', 1),
('Dong Vat F', 1, '2020-02-16', 2),
('Dong Vat G', 1, '2020-02-16', 2),
('Dong Vat H', 6, '2020-02-16', 3),
('Dong Vat I', 4, '2020-02-16', 4),
('Dong Vat J', 8, '2020-02-16', 5)
go

insert into FoodAnimal(animal_id, food_id)
values
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(3, 2),
(4, 2),
(5, 5)
go

TEST

-- TEST
select * from Room
go
select * from FoodType
go
select * from Animal
go
select * from FoodAnimal
go

4. Xem thông tin động vật gồm các trường sau: tên chuồng, tên động vật, tuổi, ngày mua về



-- Xem thông tin động vật gồm các trường sau: tên chuồng, tên động vật, tuổi, ngày mua về
---- Lay du lieu tu bang: Animal, Room
select Room.name 'Room Name', Animal.name 'Animal Name', Animal.age, Animal.buy_at
from Room, Animal
where Room.id = Animal.room_id
go


5. Xem thông tin những chuồng có số động vật đang lưu vượt quá max của chuồng đó


-- Xem thông tin những chuồng có số động vật đang lưu vượt quá max của chuồng đó
---- B1. Xem so dong vat tung room
select Room.name, Room.max, count(Animal.id) 'Count'
from Room left join Animal on Room.id = Animal.room_id
group by Room.name, Room.max
having count(Animal.id) > Room.max
order by Room.name asc
go


Thêm Data Test

---- Insert data de vuot qua max
insert into Animal (name, age, buy_at, room_id)
values
('Dong Vat AA', 2, '2020-02-16', 1),
('Dong Vat BB', 1, '2020-02-26', 1),
('Dong Vat CC', 3, '2020-02-12', 1),
('Dong Vat DD', 5, '2020-02-15', 1),
('Dong Vat EE', 2, '2020-06-16', 1),
('Dong Vat FF', 1, '2020-02-16', 2),
('Dong Vat GG', 1, '2020-02-16', 2),
('Dong Vat HH', 6, '2020-02-16', 3),
('Dong Vat II', 4, '2020-02-16', 4),
('Dong Vat JJ', 8, '2020-02-16', 5)
go

6. Xem thông tin những chuồng còn so khả năng lưu thêm động vật vào

-- Xem thông tin những chuồng còn so khả năng lưu thêm động vật vào
select Room.name, Room.max, count(Animal.id) 'Count'
from Room left join Animal on Room.id = Animal.room_id
group by Room.name, Room.max
having count(Animal.id) < Room.max
order by Room.name asc
go

7. Viết proc có tham số là @animalId -> cho phép xem được thông tin loại thức ăn của động vật này.



-- Viết proc có tham số là @animalId -> cho phép xem được thông tin loại thức ăn của động vật này.
create proc proc_view_food_animal
	@animalId int
as
begin
	select Animal.*, FoodType.name 'Food Name'
	from Animal, FoodAnimal, FoodType
	where Animal.id = FoodAnimal.animal_id
		and FoodAnimal.food_id = FoodType.id
		and Animal.id = @animalId
end
go

---- TEST
exec proc_view_food_animal 1

8. Viết trigger intead of -> cho phép xóa 1 loại thức ăn trong bảng FoodType


-- Viết trigger intead of -> cho phép xóa 1 loại thức ăn trong bảng FoodType
---- TEST
delete from FoodType where id = 1



Cách 1: Fix

---- De thuc hien xoa thanh cong can xoa het foreign key
delete from FoodAnimal where food_id = 1
delete from FoodType where id = 1

Cách 2: Fix (bằng Trigger)

---- Trigger
create trigger trigger_instead_of_delete_foodtype on FoodType
instead of delete
as
begin
	delete from FoodAnimal where food_id in (select id from deleted)
	delete from FoodType where id in (select id from deleted)
end
go

---- TEST lai
delete from FoodType where id = 1


TEST


-- TEST
select * from Room
go
select * from FoodType
go
select * from Animal
go
select * from FoodAnimal
go




Bình luận



Phản Hồi Bởi:

(1) GokiSoft.com [Teacher]

Chia sẻ từ lớp học

GokiSoft.com [Teacher]

Ngày viết: 19:29 20/04/2021



-- Tao CSDL (database)
create database BT2209
go

-- Kich hoat database (CSDL)
use BT2209
go

-- Tao bang (create tables)
create table Room (
	id int primary key identity (1,1),
	name nvarchar(20),
	max int
)
go

create table Animal (
	id int primary key identity (1,1),
	name nvarchar(50),
	age int,
	buy_at date,
	room_id int
)
go

create table FoodType (
	id int primary key identity (1,1),
	name nvarchar(50),
	price float,
	amount int
)
go

create table FoodAnimal (
	food_id int not null,
	animal_id int not null,
	primary key (food_id, animal_id)
)
go

-- Foreign Key
alter table Animal
add constraint fk_animal_room foreign key (room_id) references Room (id)
go

alter table FoodAnimal
add constraint fk_foodanimal_foodtype foreign key (food_id) references FoodType (id)
go

alter table FoodAnimal
add constraint fk_foodanimal_animal foreign key (animal_id) references Animal (id)
go

-- Thêm mỗi bảng 5 bản ghi dữ liệu
insert into Room (name, max)
values
('R01', 5),
('R02', 2),
('R03', 3),
('R04', 50),
('R05', 10)
go

insert into FoodType (name, amount, price)
values
('Thuc An A', 20, 100000),
('Thuc An B', 10, 200000),
('Thuc An C', 30, 300000),
('Thuc An D', 10, 150000),
('Thuc An E', 10, 120000)
go

insert into Animal (name, age, buy_at, room_id)
values
('Dong Vat A', 2, '2020-02-16', 1),
('Dong Vat B', 1, '2020-02-26', 1),
('Dong Vat C', 3, '2020-02-12', 1),
('Dong Vat D', 5, '2020-02-15', 1),
('Dong Vat E', 2, '2020-06-16', 1),
('Dong Vat F', 1, '2020-02-16', 2),
('Dong Vat G', 1, '2020-02-16', 2),
('Dong Vat H', 6, '2020-02-16', 3),
('Dong Vat I', 4, '2020-02-16', 4),
('Dong Vat J', 8, '2020-02-16', 5)
go

insert into FoodAnimal(animal_id, food_id)
values
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(3, 2),
(4, 2),
(5, 5)
go

-- TEST
select * from Room
go
select * from FoodType
go
select * from Animal
go
select * from FoodAnimal
go

-- Xem thông tin động vật gồm các trường sau: tên chuồng, tên động vật, tuổi, ngày mua về
---- Lay du lieu tu bang: Animal, Room
select Room.name 'Room Name', Animal.name 'Animal Name', Animal.age, Animal.buy_at
from Room, Animal
where Room.id = Animal.room_id
go

-- Xem thông tin những chuồng có số động vật đang lưu vượt quá max của chuồng đó
---- B1. Xem so dong vat tung room
select Room.name, Room.max, count(Animal.id) 'Count'
from Room left join Animal on Room.id = Animal.room_id
group by Room.name, Room.max
having count(Animal.id) > Room.max
order by Room.name asc
go

---- Insert data de vuot qua max
insert into Animal (name, age, buy_at, room_id)
values
('Dong Vat AA', 2, '2020-02-16', 1),
('Dong Vat BB', 1, '2020-02-26', 1),
('Dong Vat CC', 3, '2020-02-12', 1),
('Dong Vat DD', 5, '2020-02-15', 1),
('Dong Vat EE', 2, '2020-06-16', 1),
('Dong Vat FF', 1, '2020-02-16', 2),
('Dong Vat GG', 1, '2020-02-16', 2),
('Dong Vat HH', 6, '2020-02-16', 3),
('Dong Vat II', 4, '2020-02-16', 4),
('Dong Vat JJ', 8, '2020-02-16', 5)
go

-- Xem thông tin những chuồng còn so khả năng lưu thêm động vật vào
select Room.name, Room.max, count(Animal.id) 'Count'
from Room left join Animal on Room.id = Animal.room_id
group by Room.name, Room.max
having count(Animal.id) < Room.max
order by Room.name asc
go

-- Viết proc có tham số là @animalId -> cho phép xem được thông tin loại thức ăn của động vật này.
create proc proc_view_food_animal
	@animalId int
as
begin
	select Animal.*, FoodType.name 'Food Name'
	from Animal, FoodAnimal, FoodType
	where Animal.id = FoodAnimal.animal_id
		and FoodAnimal.food_id = FoodType.id
		and Animal.id = @animalId
end
go

---- TEST
exec proc_view_food_animal 1

-- Viết trigger intead of -> cho phép xóa 1 loại thức ăn trong bảng FoodType
---- TEST
delete from FoodType where id = 1

---- De thuc hien xoa thanh cong can xoa het foreign key
delete from FoodAnimal where food_id = 1
delete from FoodType where id = 1

---- Trigger
create trigger trigger_instead_of_delete_foodtype on FoodType
instead of delete
as
begin
	delete from FoodAnimal where food_id in (select id from deleted)
	delete from FoodType where id in (select id from deleted)
end
go

---- TEST lai
delete from FoodType where id = 1

-- TEST
select * from Room
go
select * from FoodType
go
select * from Animal
go
select * from FoodAnimal
go


Đã sao chép!!!