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 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

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

by GokiSoft.com - 21:41 10/01/2022 5,304 Lượt Xem

Bạn được yêu cầu xây dựng database cho hệ thống đưa đón học sinh bằng xe bus cho một trường học có nghiệp vụ như sau

- Bảng học viên gồm các trường : id tự tăng, Tên, địa chỉ, họ tên bố, me, sđt của bố, số điện thoại của mẹ, ngày sinh, giới tính, id địa điểm đón xe.

- Thông tin xe bus : id tự tăng, biển số xe, loại xe, số ghế ngồi, id tài xế

- Tài xế: id tự tăng, tên, sđt, giới tính, địa chỉ

- Lộ trình xe đi : id xe bus, id địa điểm đón

- Địa điểm đón xe : id tự tăng, địa chỉ


Yêu cầu thiết kế hệ thống trên.

Thêm mỗi bảng 5 bản ghi

Tạo Proc xem thông tin lộ trình đi của xe bus : tài xế, biển số xe, địa chỉ đón.

Tạo Proc xem thông tin sinh viên theo biển số xe.

Tao View xem thông tin sinh viên gồm : Tên SV, giới tính, địa chỉ đó

Bình luận



Chia sẻ từ lớp học

Nguyễn Xuân Mai [T2008A]

Ngày viết: 22:21 06/12/2020



create database Hethongduadon
use Hethongduadon

create table student(
	id int primary key identity(1,1),
	name nvarchar(50),
	address nvarchar(100),
	dad_name nvarchar(50),
	mom_name nvarchar(50),
	phone_dad int,
	phone_mom int,
	birthdate date,
	gender nvarchar(20),
	id_pickupad int references pickup(id)
)

insert into student(name, address, dad_name,mom_name,phone_dad,phone_mom,birthdate,gender, id_pickupad)
values 
('Nguyen A', 'Nguyen Chi Thanh', 'Nguyen B', 'Nguyen C', 0901234567, 0811234567,'2001/03/06','Nu',1),
('Tran A', 'Hoang Dao Thuy', 'Tran B', 'Tran C', 0901234678, 0811234678,'2001/12/06','Nam',3),
('Le A', 'Hoang Minh Giam', 'Le B', 'Le C', 0901234234, 0811567567,'2002/06/15','Nu',3),
('Hoang A', 'Lieu Giai', 'Hoang B', 'Hoang C', 0901237767, 0815884567,'2002/12/26','Nu',1),
('Ngo A', 'Giang Vo', 'Ngo B', 'Ngo C', 0811232867, 0811484567,'2003/11/10','Nam',5)

select * from student

create table bus(
	id int primary key identity(1,1),
	bienso nvarchar(20),
	type_of_car nvarchar(30),
	seat_no int,
	id_driver int references driver(id)
)

insert into bus(bienso, type_of_car, seat_no, id_driver)
values
('30A12345', 'Toyota', '48', 5),
('30B34567', 'Hyundai', '47', 3),
('30C23456', 'Ford', '16', 4),
('30D45678', 'Isuzu', '29', 1),
('30E56789', 'Toyota', '16', 2)

select * from bus

create table driver(
	id int primary key identity(1,1),
	name nvarchar(50),
	gender nvarchar(30),
	phone_no int,
	address nvarchar(100)
)

insert into driver(name, gender, phone_no, address)
values 
('Nguyen Van A', 'Nam', 0901231233, 'Ha Dong'),
('Tran Van A', 'Nam', 0901236488, 'My Dinh'),
('Le Van A', 'Nam', 0901234497, 'Hai Ba Trung'),
('Hoang Van A', 'Nam', 0901234977, 'Ly Thuong Kiet'),
('Ngo Van A', 'Nam', 0811232956, 'Doi Can')

select * from driver

create table route(
	id_bus int references bus(id),
	id_pickupad int references pickup(id)
)

insert into route
values 
(3,1),
(4,5),
(1,2),
(2,3),
(5,4)

select * from route

create table pickup(
	id int primary key identity(1,1),
	address nvarchar(100)
)

insert into pickup(address)
values 
('Nguyen Chi Thanh'),
('Tran Hung Dao'),
('Tran Duy Hung'),
('Lang Ha'),
('Le Van Luong')

select * from pickup

CREATE PROC route_info
AS
BEGIN
	select driver.name, bus.bienso, pickup.address
	from driver, bus, pickup, route
	where bus.id_driver=driver.id and bus.id = route.id_bus and route.id_pickupad = pickup.id
	group by driver.name, bus.bienso, pickup.address
END

EXEC route_info

CREATE PROC student_info
	@bienso nvarchar(20)
AS
BEGIN
	select student.*, bus.bienso 
	from student, bus,route
	where student.id_pickupad = route.id_pickupad and route.id_bus = bus.id and bus.bienso = @bienso
END
drop proc student_info
EXEC student_info '30A12345'

create view student_info2
AS
SELECT student.name, student.gender, student.address
FROM student

select * from student_info2

create clustered index ci_dadname on Student (dad_name)

create trigger TG_pickup_delete on pickup
for delete
as
begin
	if (select address from deleted) <> ''
	begin
	print N'Do not delete pickup address'
	rollback transaction 
	end
end


hainguyen [T2008A]

Ngày viết: 14:44 05/12/2020



create database Bus_school

use Bus_school

create table student (
	id int primary key identity (1, 1),
	name nvarchar(50),
	address nvarchar(100),
	father_name nvarchar(50),
	mother_name nvarchar(50),
	f_phonenumber int,
	m_phonenumber int,
	birthday date,
	gender nvarchar(10),
	id_address int
)

create table bus (
	id int primary key identity (1, 1),
	number int,
	type nvarchar(30),
	seat int,
	id_driver int
)

create table driver (
	id int primary key identity (1, 1),
	name nvarchar(50),
	phonenumber int,
	gender nvarchar(10),
	address nvarchar(100)
)

create table road (
	id_bus int primary key identity (1, 1),
	id_address int
)

create table Diemdon (
	id int primary key identity (1, 1),
	address nvarchar(100)
)

alter table road
add constraint AD_address foreign key (id_address) references Diemdon(id)

alter table road
drop AD_address

alter table bus
add constraint PK_driver foreign key (id_driver) references driver(id)

alter table road
drop AD_address

alter table student
add constraint PK_address foreign key (id_address) references Diemdon(id)

alter table student
drop PK_address

alter table bus
add constraint PK_bus_road foreign key (id) references road(id_bus)

alter table road 
add id_address2 int

alter table road
add constraint PK_address_id foreign key (id_address2) references student(id)

insert into student(name, address, father_name, mother_name, f_phonenumber, m_phonenumber, birthday, gender, id_address)
values
('TRAN VAN A', 'HA NOI', 'TRAN VAN AA', 'NGUYEN THI A', 0123456789, 0198765432, '2000-01-01', 'Nam', 1),
('TRAN Thi A', 'HA NOI', 'TRAN VAN AB', 'NGUYEN THI AB', 0123346344, 0198774545, '2000-02-02', 'Nu', 2),
('TRAN VAN B', 'NAM DINH', 'TRAN VAN BB', 'NGUYEN THI BB', 0123534534, 0123545452, '2000-11-11', 'Nam', 3),
('TRAN VAN C', 'NAM DINH', 'TRAN VAN CC', 'NGUYEN THI C', 0124563463, 0123534524, '2001-05-01', 'Nam', 4),
('TRAN THI B', 'HA NOI', 'TRAN VAN BA', 'NGUYEN THI BA', 0123234273, 0198687576, '2000-02-04', 'Nu', 5)

insert into Diemdon(address)
values
('HA NOI'),
('NAM DINH'),
('HA NOI'),
('HA NOI'),
('NAM DINH')

insert into driver(name, phonenumber, gender, address)
values
('NGUYEN VAN A', 0123456789, 'Nam', 'HA NOI'),
('NGUYEN VAN B', 0138452638, 'Nam', 'NAM DINH'),
('NGUYEN THI A', 0124569235, 'Nu', 'HA NOI'),
('NGUYEN VAN C', 0123969989, 'Nam', 'NAM DINH'),
('NGUYEN VAN D', 0182369486, 'Nam', 'HA NOI')

insert into bus(number, type, seat, id_driver)
values
(001, 'TOYOTA', 20, 1),
(002, 'AUDI', 30, 2),
(003, 'TOYOTA', 22, 3),
(004, 'HONDA', 20, 4),
(005, 'TOYOTA', 30, 5)

insert into road(id_address)
values
(1),
(2),
(3),
(4),
(5)

select * from student
select * from bus
select * from driver
select * from road
select * from Diemdon

alter table bus
add constraint LK_address foreign key (id) references road(id_bus)

create proc LoTrinhXeBus 
as
begin
	select driver.name, bus.number, road.id_address
	from bus, driver, road
	where driver.id = bus.id_driver and road.id_bus = bus.id
end

exec LoTrinhXeBus 

create proc ThongTinSv
	@biensoxe int
as
begin
	select student.name, student.address, student.gender, bus.number
	from student, bus, road
	where student.id = road.id_address2 and bus.id = road.id_bus and bus.number = @biensoxe
end

exec ThongTinSv 1

create view Thong_Tin_sv
as
select student.name, student.gender, road.id_address
from student, road
where student.id = road.id_address2

select * from Thong_Tin_sv

create clustered index cr_fat on student (father_name)




vuong huu phu [T2008A]

Ngày viết: 21:29 04/12/2020



create database school_bus_transportation_system
use school_bus_transportation_system

------Bảng học viên 
create table student_bus (
student_id int identity (1,1) primary key,
student_name nvarchar(100),
Date_of_birth date ,
gender nvarchar(20),
student_address nvarchar (200),
student_father_name nvarchar(100),
student_mother_name nvarchar(100),
phone_number_mother nvarchar (20),
phone_number_father nvarchar (20),
id_don_xe int
)
go
-----Bảng Thông tin xe bus
create table Bus (
id int identity(1,1) primary key,
license_plates nvarchar (30),
range_of_vehicle nvarchar(50),
seating int ,
driver_id int 
)
go
-----Bảng Tài xế
create table driver(
driver_id int identity(1,1) primary key, 
driver_name nvarchar(100),
phone_number_driver nvarchar (20),
gender nvarchar(20),
driver_address nvarchar(200)
)
go
-----Bảng Lộ trình xe đi
create table Route_of_the_car(
bus_id int,
location_id int
primary key (bus_id,location_id)
)
go
-----Bảng Địa điểm đón xe 
create table Pick_up_location(
id int identity(1,1) primary key,
address nvarchar(200)
)
go

alter table student_bus 
add constraint fk_đia_diem foreign key (id_don_xe)
references Pick_up_location(id)

alter table Bus 
add constraint fk_tai_xe foreign key (driver_id) 
references driver(driver_id)

alter table Route_of_the_car 
add constraint fk_xe_bus foreign key (bus_id) 
references Bus(id)

alter table Route_of_the_car 
add constraint fk_điaiem_bus_don foreign key (location_id) 
references Pick_up_location(id)

insert into student_bus(student_name,student_address,Date_of_birth,gender,student_father_name,student_mother_name,phone_number_father,phone_number_mother,id_don_xe)
values
('A','Ton That Thuyet - Ha Noi','2001-07-13','Nam','AA','AAA','AA1234567890','AAA1234567890','1'),
('B','Nguyen Chi Thanh - Ha Noi','2001-07-13','Nam','BB','BBB','BB1234567890','BBB1234567890','2'),
('C','Nguyen Thai Hoc - Ha Noi','2001-07-13','Nu','CC','CCC','CC1234567890','CCC1234567890','3'),
('D','Hang cot - Ha Noi','2001-07-13','Nam','DD','DDD','DD1234567890','DDD1234567890','4'),
('E','Ton That Thuyet - Ha Noi','2001-07-13','Nam','EE','EEE','EE1234567890','EEE1234567890','1')

insert into Bus(license_plates,range_of_vehicle,seating,driver_id)
values
('30A-111.11','Honda','4','1'),
('30A-888.88','Honda','4','4'),
('30A-666.66','Honda','4','2'),
('30A-266.66','Honda','4','5'),
('30A-999.99','Honda','4','3')

insert into driver(driver_name , phone_number_driver , gender , driver_address)
values
('driver 1','8987112234','Nam','Bach Mai - Ha Noi'),
('driver 2','7987112234','Nam','Cua Bac - Ha Noi'),
('driver 3','6987112234','Nam','Phan Dinh Phung - Ha Noi'),
('driver 4','0587112234','Nam','Gia Thuy - Ha Noi'),
('driver 5','0487112234','Nam','Ngoc Lam - Ha Noi')

insert into Route_of_the_car (bus_id,location_id)
values
('1','1'),
('2','2'),
('3','3'),
('4','4'),
('5','5')

insert into Pick_up_location (address)
values
('Long Bien - Ha Noi'),
('Hoan Kiem - Ha Noi'),
('Ba Dinh - Ha Noi'),
('My Dinh - Ha Noi'),
('Thanh Xuan - Ha Noi')


select * from Bus
select * from driver
select * from Pick_up_location
select * from Route_of_the_car
 
 ------Tạo Proc xem thông tin lộ trình đi của xe bus : tài xế, biển số xe, địa chỉ đón.
create proc tt_lo_trinh
@id_xe int
as
begin
select driver.driver_name,Bus.license_plates,Pick_up_location.address
from driver,Bus,Pick_up_location,Route_of_the_car
where Bus.driver_id = driver.driver_id and Route_of_the_car.bus_id = Bus.id and Route_of_the_car .location_id = Pick_up_location.id and bus_id = @id_xe
end

exec tt_lo_trinh 1
------Tạo Proc xem thông tin sinh viên theo biển số xe.
alter proc tt_sv_bs_xe
@bsx nvarchar (20)
as
begin 
select student_bus.student_id,student_bus.student_name,student_bus.student_address,student_bus.Date_of_birth,student_bus.gender,student_bus.student_father_name,student_bus.student_mother_name,student_bus.phone_number_father,student_bus.phone_number_mother,student_bus.id_don_xe
from student_bus,driver,Bus,Pick_up_location,Route_of_the_car
where student_bus.id_don_xe = Pick_up_location.id and Bus.driver_id = driver.driver_id and Route_of_the_car.bus_id = Bus.id and Route_of_the_car .location_id = Pick_up_location.id and Bus.license_plates = @bsx
end

exec tt_sv_bs_xe '30A-111.11'

-----Tao View xem thông tin sinh viên
create view tt_sinh_vien
as
select student_bus.student_name ,student_bus.gender,Pick_up_location.address
from student_bus ,Pick_up_location,Bus,driver,Route_of_the_car
where student_bus.id_don_xe = Pick_up_location.id and Bus.driver_id = driver.driver_id and Route_of_the_car.bus_id = Bus.id and Route_of_the_car .location_id = Pick_up_location.id

select * from tt_sinh_vien

create clustered index ci_father
on student_bus(student_father_name)




Trần Văn Lâm [T2008A]

Ngày viết: 12:53 04/12/2020



create database simple_system
use simple_system
create table Student(
	id int primary key identity(1,1),
	name nvarchar(100),
	address nvarchar(200),
	name_father nvarchar(100),
	name_mother nvarchar(100),
	phoneNumer_father nvarchar(20),
	phoneNumber_mother nvarchar(20),
	Date_of_birth datetime,
	gender nvarchar(10),
	id_locationPickup int 
)
create table Bus(
	id int primary key identity(1,1),
	license_plate nvarchar(50),
	range_vehicle nvarchar(50),
	num_desk int,
	id_driver int
)
create table Driver(
	id int primary key identity(1,1),
	name nvarchar(100),
	phoneNumber nvarchar(20),
	gender nvarchar(20),
	address nvarchar(200)
)
create table Rout(
	id_bus int primary key identity(1,1),
	id_locationPickup int
)
create table LocationPickup(
	id int primary key identity(1,1),
	address nvarchar(200)
)

select * from Student
insert into Student(name,address,name_father,name_mother,phoneNumer_father,phoneNumber_mother,Date_of_birth,gender,id_locationPickup)
values
('Nguyen Van A','Linh Dam','Nguyen Tuan A','Tran Thi A','1234567','1234567',2002-05-05,'nam',1),
('Nguyen Van B','Linh Dam','Nguyen Tuan B','Tran Thi B','1234567','1234567',2002-05-05,'nam',3),
('Nguyen Van C','Linh Dam','Nguyen Tuan C','Tran Thi C','1234567','1234567',2002-05-05,'nam',2),
('Nguyen Van D','Linh Dam','Nguyen Tuan D','Tran Thi D','1234567','1234567',2002-05-05,'nam',1),
('Nguyen Van E','Linh Dam','Nguyen Tuan E','Tran Thi E','1234567','1234567',2002-05-05,'nam',5)
select * from Bus
insert into Bus(license_plate,range_vehicle,num_desk,id_driver)
values
('T3409','Mercedes',10,1),
('T3445','Mazda',12,2),
('T6756','Camry',18,4),
('T2123','Vinfast',20,2),
('T4564','Bungati',26,3)
select * from Driver
insert into Driver(name,phoneNumber,gender,address)
values
('Messi','12322','nam','My Dinh'),
('Mordic','12322','nam','My Dinh'),
('Tevez','12322','nam','My Dinh'),
('Neymar','12322','nam','My Dinh'),
('Ronaldo','12322','nam','My Dinh')
select * from Rout
insert into Rout(id_locationPickup)
values
(1),
(2),
(2),
(4),
(3)
insert into LocationPickup(address)
values
('Dong Da'),
('My Dinh'),
('Ton Duc Thang'),
('Dinh Tien Hoang'),
('Ton That Thuyet')
select Driver.name from Driver
select Bus.license_plate from Bus
select LocationPickup.address from LocationPickup
create proc proc_lo_trinh
as
begin
	select Driver.name from Driver
    select Bus.license_plate from Bus
    select LocationPickup.address from LocationPickup
end
exec proc_lo_trinh
create view thong_tin_sinh_vien
as
select Student.name, Student.gender, LocationPickup.address
from Student,LocationPickup
select * from thong_tin_sinh_vien
create clustered index ci_name_father on Student (name_father)


Do Trung Duc [T2008A]

Ngày viết: 10:41 04/12/2020



create database Bus_Symtem_Management
use Bus_Symtem_Management

create table Student(
Id int identity(1,1),
StudentName  nvarchar(100),
Address nvarchar(100),
ParentsName nvarchar(100),
ParentsPhone nvarchar(100),
Bithday date,
Id_TakePlace int,
constraint PK_Student primary key (Id)
)

create table BusInformation(
Id int primary key identity(1,1),
Number nvarchar(20),
Type nvarchar(100),
Seats int,
DriverId int,
)

create table DriverInformation(
DriverId int primary key identity(1,1),
DriverName nvarchar (100),
DriverPhone nvarchar (100),
DriverSex nvarchar (100),
DriverAddress nvarchar (200)
)

create table BusRoad(
id_busroad int primary key identity  (1,1),
AddressPlace nvarchar (200)
)

alter table BusRoad
drop  PK__BusRoad__3AF689CBBB74427A

alter table BusRoad
drop column id_busroad

alter table BusRoad
drop column AddressPlace

alter table BusRoad
add Id_TakePlace int


create table AddressPlaceDetail(
Id_AddressPlace int primary key identity  (1,1),
AddressPlace nvarchar (200)
)

alter table Student 
add constraint FK_Id_TakePlace_Student foreign key (Id_TakePlace) references AddressPlaceDetail(Id_AddressPlace)

alter table BusInformation
add constraint FK_DriverId_BusInformation foreign key (DriverId) references DriverInformation(DriverId)

alter table BusRoad
add constraint FK_id_busroad_BusRoad foreign key (id_busroad) references BusInformation(Id)

alter table BusRoad
drop FK_id_busroad_BusRoad

alter table BusRoad
add Bus_Id int 

alter table BusRoad
add constraint FK_Bus_Id_BusRoad foreign key (Bus_Id) references BusInformation(Id)

alter table BusRoad
add constraint FK_Id_TakePlace_BusRoad foreign key (Id_TakePlace) references AddressPlaceDetail(Id_AddressPlace)

--Them ban ghi
insert into  AddressPlaceDetail(AddressPlace)
values
('So 90 Cau Giay'),
('So 1 Linh Dam'),
('So 2 Hoang Mai'),
('So 9 Dong Da'),
('So 8 Tay Ho')
select *from AddressPlaceDetail

select *from Student
INSERT into Student(StudentName,Address,ParentsName,ParentsPhone,Bithday,Id_TakePlace)
values
('Do Trung Duc','Cau Giay,Ha Noi','Bo cua Duc','123456781','1991-12-20',1),
('Tran Van Diep','Linh Dam,Ha Noi','Bo cua Diep','123456782','1991-12-20',2),
('Nguyen Tuan Anh','Cau Giay,Ha Noi','Bo cua Tuan Anh','123456783','1991-12-20',1),
('Pham Hong Quang','Dong Da,Ha Noi','Bo cua Quang','123456784','1991-12-20',4),
('Le Minh Son','Cau Giay,Ha Noi','Bo cua Son','123456785','1991-12-20',5)


insert into DriverInformation(DriverName,DriverPhone,DriverSex,DriverAddress)
values
('Hoang Van A','123456789','Nam','Hanoi'),
('Hoang Van B','123456789','Nam','Hanoi'),
('Hoang Van B','123456789','Nam','Hanoi'),
('Hoang Van D','123456789','Nam','Hanoi'),
('Hoang Van E','123456789','Nam','Hanoi')

select * from DriverInformation

insert into BusInformation(Number,Type,Seats,DriverId)
values
('30K6523','FordTransit',16,1),
('30K6524','FordTransit',16,2),
('30K6525','Huyndai',29,3),
('30K6123','FordTransit',16,4),
('30K4226','Huyndai',29,5)

select *from BusInformation

insert into BusRoad(Bus_Id,Id_TakePlace)
values
(1,4),
(2,2),
(3,3),
(4,1),
(5,5)

select *from AddressPlaceDetail
select *from Student
select *from DriverInformation
select *from BusInformation
select *from BusRoad

create PROC xemthongtinlotrinhxebus
AS
BEGIN
	select DriverInformation.DriverName, BusInformation.Number, AddressPlaceDetail.AddressPlace
	FROM DriverInformation, BusInformation, AddressPlaceDetail, BusRoad
	where DriverInformation.DriverId = BusInformation.DriverId and BusInformation.Id = BusRoad.Bus_Id and BusRoad.Id_TakePlace = AddressPlaceDetail.Id_AddressPlace
END
exec xemthongtinlotrinhxebus

create Proc xemthongtinsinhvientheobiensoxe
   @biensoxe nvarchar(20)
AS
BEGIN 
	select Student.* , BusInformation.Number
	from Student, BusInformation, BusRoad
	where Student.Id_TakePlace = BusRoad.Id_TakePlace and BusRoad.Bus_Id = BusInformation.Id and BusInformation.Number =  @biensoxe
END

EXEC xemthongtinsinhvientheobiensoxe '30K6523'

--Tao View xem thông tin sinh viên gồm : Tên SV, giới tính, địa chỉ đón

alter table Student
add StudentSex nvarchar(10)

update Student set StudentSex =' Nam'

create view xemthongtinsinhvien 
AS
select Student.StudentName, Student.StudentSex, AddressPlaceDetail.AddressPlace
FROM Student inner join AddressPlaceDetail on Student.Id_TakePlace = AddressPlaceDetail.Id_AddressPlace

select * from  xemthongtinsinhvien 

--Đánh index (clustered index) cho column họ tên bố trên bảng học viên

create clustered Index ci_ParentsName on Student(ParentsName) --HOilaiThay

--Tao trigger khong cho phep xoa dia chi don xe don
create trigger khongxoadiadiemdon on AddressPlaceDetail
for delete
AS
BEGIN
	if (select AddressPlace from deleted) <> ''
	begin
	print N'‘Do not delete AddressPlace'
	rollback transaction 
	end
END

select * from AddressPlaceDetail
DELETE AddressPlace FROM AddressPlaceDetail WHERE Id_AddressPlace = 1 

select * from AddressPlaceDetail

select *from Student

create trigger nhapnamsinh on Student
for insert
as
BEgin
	if (select Bithday from inserted ) > '1990-1-1'
	begin
		print N'Nam sinh khong hop le'
		rollback transaction
		end
End

INSERT into Student(StudentName,Address,ParentsName,ParentsPhone,Bithday,Id_TakePlace)
values
('Testriger1','Cau Giay,Ha Noi','Bo cua triger1','123456781','1989-12-20',1)

INSERT into Student(StudentName,Address,ParentsName,ParentsPhone,Bithday,Id_TakePlace)
values
('Testriger2','Cau Giay,Ha Noi','Bo cua triger2','123456781','1994-12-20',1)






Đã sao chép!!!