Bài tập - 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

-- Tao database BT1844
create database BT1844
go

-- Kich hoat database
use BT1844
go

-- Tao tables
create table Student (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	address nvarchar(200),
	father_name nvarchar(50),
	father_phone nvarchar(20),
	mother_name nvarchar(50),
	mother_phone nvarchar(20),
	birthday date,
	gender nvarchar(16),
	position_id int
)
go

create table Bus (
	id int primary key identity(1,1),
	bus_no nvarchar(20),
	type nvarchar(20),
	seats int,
	driver_id int
)
go

create table Driver (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	phone_number nvarchar(20),
	gender nvarchar(16),
	address nvarchar(200)
)
go

create table position (
	id int primary key identity(1,1),
	address nvarchar(200)
)
go

create table routes (
	bus_id int not null,
	position_id int not null,
	primary key (bus_id, position_id)
)
go

-- foreign key
alter table Student
add constraint fk_student_postion foreign key (position_id) references position (id)
go

alter table Bus
add constraint fk_bus_driver foreign key (driver_id) references Driver (id)
go

alter table routes
add constraint fk_routes_bus foreign key (bus_id) references Bus(id)
go

alter table routes
add constraint fk_routes_position foreign key (position_id) references position(id)
go

-- insert data
insert into position (address)
values
('Address 01'),
('Address 02'),
('Address 03'),
('Address 04'),
('Address 05')
go

insert into Driver (fullname, gender, phone_number, address)
values
('Driver 01', 'Nam', '123123', 'Ha Noi'),
('Driver 02', 'Nam', '345345', 'Ha Noi'),
('Driver 03', 'Nam', '546546', 'Ha Noi')
go

insert into Student (fullname, gender, birthday, address, father_name, father_phone, mother_name, mother_phone, position_id)
values
('HV 1', 'Nam', '2016-02-19', 'Ha Noi', 'F1', '1', 'M1', '2', 1),
('HV 2', 'Nu', '2017-03-29', 'Ha Noi', 'F2', '1', 'M2', '2', 2),
('HV 3', 'Nu', '2018-05-19', 'Ha Noi', 'F3', '1', 'M3', '2', 3),
('HV 4', 'Nam', '2015-06-19', 'Ha Noi', 'F4', '1', 'M4', '2', 4),
('HV 5', 'Nam', '2016-02-19', 'Ha Noi', 'F5', '1', 'M5', '2', 2)
go

insert into Bus (bus_no, driver_id, seats, type)
values
('No01', 1, 4, 'Type01'),
('No02', 2, 5, 'Type01'),
('No03', 3, 7, 'Type01')
go

insert into routes(bus_id, position_id)
values
(1, 1),
(1, 3),
(1, 4),
(2, 2),
(2, 3),
(2, 5)
go

-- tài xế (Driver), biển số xe (Bus), địa chỉ đón (position)
select Driver.fullname 'Ten Lai Xe', Bus.bus_no, position.address
from Driver, Bus, position, routes
where Driver.id = Bus.driver_id
	and Bus.id = routes.bus_id
	and routes.position_id = position.id
go

create view view_routing
as
select Driver.fullname 'Ten Lai Xe', Bus.bus_no, position.address
from Driver, Bus, position, routes
where Driver.id = Bus.driver_id
	and Bus.id = routes.bus_id
	and routes.position_id = position.id
go

select * from view_routing

-- Tạo Proc xem thông tin sinh viên theo biển số xe.
-- Ten Hoc Vien, Gioi Tinh, Vi Tri Don, Bien So Xe
select Student.fullname, Student.gender, position.address 'Dia Chi Don', Bus.bus_no 'Bien So Xe'
from Student, position, Bus, routes
where Student.position_id = position.id --foreign & primary
	and position.id = routes.position_id
	and routes.bus_id = Bus.id
go

create proc proce_find
	@busNo nvarchar(20)
as
begin
	select Student.fullname, Student.gender, position.address 'Dia Chi Don', Bus.bus_no 'Bien So Xe'
	from Student, position, Bus, routes
	where Student.position_id = position.id --foreign & primary
		and position.id = routes.position_id
		and routes.bus_id = Bus.id
		and bus.bus_no = @busNo
end

exec proce_find 'No01'

-- Tên SV (Student), giới tính (Student), địa chỉ đó (position)
select Student.fullname, Student.gender, position.address
from Student, position
where Student.position_id = position.id
go

create view view_studenti_position
as
select Student.fullname, Student.gender, position.address
from Student, position
where Student.position_id = position.id
go

select * from view_studenti_position