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