By GokiSoft.com| 20:45 11/12/2023|
SQL Server/MySQL

Thiết kế database - bãi đỗ xe - Lập trình SQL Server BT1795

Tạo 3 bảng sau

1. BaiDoXe gồm các column sau

- Tên bãi: nvarchar(50)

- mã bãi đỗ xe: int ->primary key -> identity(1,1)

- địa chỉ: nvarchar(200)

2. ThongTinGui gồm các column sau

- id: int -> primary key -> identity(1,1)

- tên xe: nvarchar(50)

- biển số xe: nvarchar(20)

- mã bãi đỗ xe: int -> foreign key -> liên kết với bảng BaiDoXe

- id chủ sở hữu: int -> foreign key -> liên kết với bảng chủ sở hữu

3. Bảng chủ sở hữu

- id chủ sở hữu: int -> primary key -> identity(1,1)

- tên: nvarchar(50)

- số cmtnd: nvarchar(20)

- địa chỉ: nvarchar(200)

Yêu cầu :

- Tạo bản trên

- chèn mỗi bảng 5 bản nghỉ

- Hiển thị thông tin người gửi xe (số cmtnd, tên, tên bãi đỗ xe, biển số xe)

- Hiển thị số lần gửi xe của tất cả các Chủ Sở Hữu >> Tên bãi đỗ xe, biển số xe, Chủ Sở Hữu, Số CMTND, Số Lần Gửi

Liên kết rút gọn:

https://gokisoft.com/1795

Bình luận

avatar
Hoàng Anh [C2010G]
2021-12-31 10:24:04



--tao database--
create database Quan_ly_bai_do_xe
--use database--
use Quan_ly_bai_do_xe
--tao bang--
create table BaiDoXe(
	TenBD nvarchar(50) not null,
	MaBD int primary key identity (1,1),
	DiaChi nvarchar(200) not null
)
create table ThongTinGui(
	id int primary key identity (1,1),
	TenXe nvarchar(50) not null,
	BienKS nvarchar(20) not null,
	MaBD int not null,
	idChuXe int not null
)
create table BangChuSoHuu(
	idChuXe int primary key identity (1,1),
	TenCX nvarchar(50),
	SoCMND nvarchar(20),
	DiaChi nvarchar(200)
)
--Add foreign key--
alter table ThongTinGui
add constraint fk_MaD foreign key (MaBD) references BaiDoXe(MaBD)

alter table ThongTinGui
add constraint fk_IdChuXe foreign key (idChuXe) references BangChuSoHuu(idChuXe)

--check--
alter table ThongTinGui
add constraint unique_BienKS unique (BienKS) 

alter table BangChuSoHuu
add constraint unique_SoCMND unique (SoCMND) 


--Chen du lieu--
insert into BaiDoXe(TenBD,DiaChi)
values
('Bai A','Ha Noi'),
('Bai B','Nam Dinh'),
('Bai C','Hai Phong'),
('Bai D','Son La'),
('Bai E','Ha Tay')
insert into ThongTinGui(TenXe,BienKS,MaBD,idChuXe)
values
('vios','29C-1133',1,1),
('toyota','29A-2133',2,2),
('honda','29C-1642',5,3),
('mitsubishi','29C-3263',1,4),
('audi','29C-5543',4,5)
insert into BangChuSoHuu(TenCX,SoCMND,DiaChi)
values
('Nguyen Van A','1123344321','Ha Noi'),
('Le Thi B','324344321','Ha Noi'),
('Nguyen Van C','25434321','Cao Bang'),
('Nguyen Van D','577244321','Lang Son'),
('Le Thi E','981244321','Quang Ninh')

-- Hiển thị thông tin người gửi xe (số cmtnd, tên, tên bãi đỗ xe, biển số xe)--
select BangChuSoHuu.SoCMND,BangChuSoHuu.TenCX 'Ten',BaiDoXe.TenBD,ThongTinGui.BienKS 'Bien So Xe'
from BangChuSoHuu, BaiDoXe, ThongTinGui
where ThongTinGui.MaBD = BaiDoXe.MaBD and ThongTinGui.idChuXe = BangChuSoHuu.idChuXe

--Hiển thị số lần gửi xe của tất cả các Chủ Sở Hữu >> Tên bãi đỗ xe, biển số xe, Chủ Sở Hữu, Số CMTND, Số Lần Gửi
select BaiDoXe.TenBD, ThongTinGui.BienKS 'Bien So Xe' , BangChuSoHuu.TenCX 'Chu So Huu', BangChuSoHuu.SoCMND 'So CMTND', BangChuSoHuu.idChuXe, count(BangChuSoHuu.idChuXe) as SoLanGui
from BaiDoXe, ThongTinGui, BangChuSoHuu
where ThongTinGui.MaBD = BaiDoXe.MaBD and ThongTinGui.idChuXe = BangChuSoHuu.idChuXe
group by BaiDoXe.TenBD, ThongTinGui.BienKS, BangChuSoHuu.TenCX, BangChuSoHuu.SoCMND, BangChuSoHuu.idChuXe


avatar
Hoàng Thái Sơn [C2010L]
2021-04-18 04:49:12


#testting03-04-2021-ex2.sql


Create database BaiDoXe;
Use BaiDoXe;

Create table BaiDoXe (
	park_name nvarchar(50),
	id int primary key identity(1,1),
	park_address nvarchar(200)
);
Create table ThongTinGui (
	id int primary key identity(1,1),
	car_name nvarchar(50),
	car_num nvarchar(20),
	park_id int FOREIGN KEY REFERENCES BaiDoXe(id),
	owner_id int FOREIGN KEY REFERENCES ChuSoHuu(id)
);
Create table ChuSoHuu (
	id int primary key identity(1,1),
	owner_name nvarchar(50),
	owner_num nvarchar(20),
	owner_address nvarchar(200)
);
insert into BaiDoXe(park_name, park_address)
values
('ciputra', 'HCM'),
('ciputr', 'HN'),
('ciput', 'L.A'),
('cipu', 'NewYork'),
('cip', 'LONDON')

select * from BaiDoXe

insert into ChuSoHuu(owner_name, owner_num, owner_address)
values
('John','1111','Binh Duong - HCM'),
('Smith','2222','Cau Giay - Ha Noi'),
('David','3333','Hoan Kiem - Ha Noi'),
('jack','4444','Dong Thap - HCM'),
('dog','5555','LongAn');

select * from ChuSoHuu

insert ThongTinGui (owner_id, park_id, car_name, car_num)
values
(1, 1, 'Honda','29X3-77676'),
(2, 3, 'Mercedes','29X3-45454'),
(3, 2, 'Civic','29X3-75645'),
(4, 3, 'BMW','29X3-53232'),
(5, 3, 'Vios','29X3-97867');

insert ThongTinGui (owner_id, park_id, car_name, car_num)
values
(1, 1, 'Honda','29X3-77676'),
(2, 3, 'Mercedes','29X3-45454'),
(3, 2, 'Civic','29X3-75645')

select * from ThongTinGui

select ChuSoHuu.owner_name, ChuSoHuu.owner_num, BaiDoXe.park_name, BaiDoXe.park_address, ThongTinGui.car_num
from ChuSoHuu, ThongTinGui, BaiDoXe
where BaiDoXe.id = ThongTinGui.park_id
	and ThongTinGui.owner_id = ChuSoHuu.id

select ChuSoHuu.owner_name, ChuSoHuu.owner_num, BaiDoXe.park_name, ThongTinGui.car_num, count(ThongTinGui.owner_id) 'Số lần gửi'
from ChuSoHuu, ThongTinGui, BaiDoXe
where BaiDoXe.id = ThongTinGui.park_id and ThongTinGui.owner_id = ChuSoHuu.id
group by ChuSoHuu.owner_name, ChuSoHuu.owner_num, BaiDoXe.park_name, ThongTinGui.car_num


avatar
Hoàng Thái Sơn [C2010L]
2021-04-17 12:47:53


#testing03-04-2021.sql


create database quanLyBaiDoXe
use quanLyBaiDoXe

create table baiDoXe (
	id int primary key identity(1,1),
	name nvarchar(100) not null,
	address nvarchar(250)
)

create table customer(
	id int primary key identity(1,1),
	name_customer nvarchar(50),
	number_card nvarchar(25),
	address_customer nvarchar(250)
)
create table info (
	id_info int primary key identity(1,1),
	id_customer int references customer(id),
	id_baiDoXe int references baiDoXe(id),
	name_car nvarchar(50),
	number_car nvarchar(20)
)

insert into baiDoXe(name, address)
values
('Chuong Duong Do', 'Chuong Duong - Hoan Kiem - HN'),
('Vinh Yen', 'Vinh Yen - Vinh Phuc'),
('Cau Giay 2', 'Trung Hoa - Cau Giay - HN'),
('Thanh Xuan 3', 'Nga Tu So - Thanh Xuan - HN'),
('Long Bien 1', 'Nguyen Van Cu - Long Bien - HN')

select * from baiDoXe

insert into customer(name_customer, number_card, address_customer)
values
('Nguyen A','0142323','Thanh Xuan - Ha Noi'),
('Nguyen B','02534867','Cau Giay - Ha Noi'),
('Nguyen C','05342756','Hoan Kiem - Ha Noi'),
('Nguyen D','06473','Tu Liem - Ha Noi'),
('Nguyen E','075656','Nam Dinh')

select * from customer

insert info (id_customer, id_baiDoXe, name_car, number_car)
values
(2, 1, 'Honda','29X3-77676'),
(3, 3, 'Mercedes','29X3-45454'),
(2, 2, 'Civic','29X3-75645'),
(3, 3, 'BMW','29X3-53232'),
(5, 3, 'Vios','29X3-97867')

insert info (id_customer, id_baiDoXe, name_car, number_car)
values
(2, 1, 'Honda','29X3-77676'),
(2, 1, 'Honda','343535'),
(3, 3, 'Mercedes','29X3-45454')


select * from baiDoXe
select * from customer
select * from info
--Hien thi thong tin nguoi gui xe
select customer.number_card, customer.name_customer, baiDoXe.name, info.number_car
from baiDoXe, customer, info
where customer.id = info.id_customer and baiDoXe.id = info.id_baiDoXe
--Hien thi so lan gui xe cua tat ca chu so huu (Tên bãi đỗ xe, biển số xe, Chủ Sở Hữu, Số CMTND, Số Lần Gửi)
select customer.name_customer, baiDoXe.name, info.number_car, customer.number_card, count(info.id_customer) 'So lan gui'
from baiDoXe, customer, info
where customer.id = info.id_customer and baiDoXe.id = info.id_baiDoXe
group by customer.name_customer, baiDoXe.name, info.number_car, customer.number_card







avatar
Nguyễn Hoàng Hiệp [community,C2010G]
2021-04-05 03:42:00
select BangChuSoHuu.CMTNN, BangChuSoHuu.TenChuXe, BaiDoXe.TenBai, ThongTinGui.BienSoXe
	from BangChuSoHuu, ThongTinGui, BaiDoXe
		where BaiDoXe.MaBaiDoXe = ThongTinGui.MaBaiDoXe
			and ThongTinGui.idChuXe = BangChuSoHuu.idChuXe
avatar
Vũ Trung Kiên [C2009I]
2021-01-23 09:56:38


#CarPaking.sql


create table BaiDoXe(
	TenBai nvarchar(50),
	MaBaiDoXe nvarchar(50) primary key,
	DiaChi nvarchar(50)
)

insert into BaiDoXe(TenBai, MaBaiDoXe, DiaChi)
values
('a', 'a1', 'ha noi'),
('b', 'a2', 'ha noi'),
('c', 'a3', 'ha noi'),
('d', 'a4', 'ha noi'),
('e', 'a5', 'ha noi')

create table ThongTinGui(
	TenXe nvarchar(50),
	BienSoXe nvarchar(50),
	MaBaiDoXe nvarchar(50) primary key,
	idChuXe nvarchar(50)
)
insert into ThongTinGui(TenXe, BienSoXe, MaBaiDoXe, idChuXe)
values
('honda', '18-G1 45678', 'a1', '123456789'),
('honda', '1r9-G1 45678', 'a2', '1234567891'),
('yamaha', '10-G1 45678', 'a3', '1234567892'),
('kawazaki', '34-G1 45678', 'a4', '1234567893'),
('ducati', '35-G1 45678', 'a5', '1234567894')

create table BangChuSoHuu(
	idChuXe nvarchar(50) primary key,
	TenChuXe nvarchar(50),
	CMTNN bigint,
	DiaChiChuXe nvarchar(50)
)

insert into BangChuSoHuu(idChuXe, TenChuXe, CMTNN, DiaChiChuXe)
values
('123456789', 'A', '3247239872', 'ha noi'),
('1234567891', 'B', '0980978458201', 'ha noi'),
('1234567892', 'C', '0980978458202', 'ha noi'),
('1234567893', 'D', '0980978458203', 'ha noi'),
('1234567894', 'E', '0980978458204', 'ha noi')

alter table BaiDoXe
add constraint fk_MaBaiDoXe foreign key (MaBaiDoXe) references ThongTinGui (MaBaiDoXe)

alter table ThongTinGui
add constraint fk_idChuXe foreign key (idChuXe) references BangChuSoHuu (idChuXe)

select BangChuSoHuu.CMTNN, BangChuSoHuu.TenChuXe, BaiDoXe.TenBai, ThongTinGui.BienSoXe
	from BangChuSoHuu, ThongTinGui, BaiDoXe
		where BaiDoXe.MaBaiDoXe = ThongTinGui.MaBaiDoXe
			and ThongTinGui.idChuXe = BangChuSoHuu.idChuXe


avatar
Nguyễn đình quân [T2008A]
2021-01-22 07:54:37



drop table baidoxe
drop table thongtingui
drop table bangchusohuus
create table baidoxe (
	ten_bai nvarchar(50),
	ma_bai_bai_do nvarchar(50),
	dia_chi nvarchar(50)
)
create table thongtingui (
	ten_xe nvarchar(50),
	bien_so_xe nvarchar(50),
	ma_bai_do_xe nvarchar(50),
	id_chu_so_huu int
)
create table bangchusohuus (
	id_chu_so_huu int,
	ten nvarchar(50),
	so_cmnd nvarchar(50),
	dia_chi nvarchar(50)
)
insert into baidoxe(ten_bai, ma_bai_bai_do, dia_chi)
values
('a', 'a1', 'ha noi'),
('b', 'a2', 'ha noi'),
('c', 'a3', 'ha noi'),
('d', 'a4', 'ha noi'),
('e', 'a5', 'ha noi')
select * from baidoxe
insert into thongtingui(ten_xe, bien_so_xe, ma_bai_do_xe, id_chu_so_huu)
values
('honda', '18-G1 45678', 'a1', '123456789'),
('honda', '19-G1 45678', 'a2', '1234567891'),
('yamaha', '10-G1 45678', 'a3', '1234567892'),
('kawazaki', '34-G1 45678', 'a4', '1234567893'),
('ducati', '35-G1 45678', 'a5', '1234567894')
select * from thongtingui
insert into bangchusohuus(id_chu_so_huu, ten, so_cmnd, dia_chi)
values
('123456789', 'A', '3247239872', 'ha noi'),
('1234567891', 'B', '0980978458201', 'ha noi'),
('1234567892', 'C', '0980978458202', 'ha noi'),
('1234567893', 'D', '0980978458203', 'ha noi'),
('1234567894', 'E', '0980978458204', 'ha noi')
select * from bangchusohuus


avatar
Lê Trọng Nghĩa [community,C2009I]
2021-01-21 09:38:17



drop table baidoxe
drop table thongtingui
drop table bangchusohuus
create table baidoxe (
	ten_bai nvarchar(50),
	ma_bai_bai_do nvarchar(50),
	dia_chi nvarchar(50)
)
create table thongtingui (
	ten_xe nvarchar(50),
	bien_so_xe nvarchar(50),
	ma_bai_do_xe nvarchar(50),
	id_chu_so_huu int
)
create table bangchusohuus (
	id_chu_so_huu int,
	ten nvarchar(50),
	so_cmnd nvarchar(50),
	dia_chi nvarchar(50)
)
insert into baidoxe(ten_bai, ma_bai_bai_do, dia_chi)
values
('a', 'a1', 'ha noi'),
('b', 'a2', 'ha noi'),
('c', 'a3', 'ha noi'),
('d', 'a4', 'ha noi'),
('e', 'a5', 'ha noi')
select * from baidoxe
insert into thongtingui(ten_xe, bien_so_xe, ma_bai_do_xe, id_chu_so_huu)
values
('honda', '18-G1 45678', 'a1', '123456789'),
('honda', '19-G1 45678', 'a2', '1234567891'),
('yamaha', '10-G1 45678', 'a3', '1234567892'),
('kawazaki', '34-G1 45678', 'a4', '1234567893'),
('ducati', '35-G1 45678', 'a5', '1234567894')
select * from thongtingui
insert into bangchusohuus(id_chu_so_huu, ten, so_cmnd, dia_chi)
values
('123456789', 'A', '3247239872', 'ha noi'),
('1234567891', 'B', '0980978458201', 'ha noi'),
('1234567892', 'C', '0980978458202', 'ha noi'),
('1234567893', 'D', '0980978458203', 'ha noi'),
('1234567894', 'E', '0980978458204', 'ha noi')
select * from bangchusohuus


avatar
Hiếu Vũ [community,C2009I]
2021-01-21 09:33:30



create table BaiDoXe (
	Ten_bai nvarchar(50) unique,
	Ma_bai_do_xe nvarchar(50) primary key,
	Dia_chi nvarchar(200) unique
)

create table Thong_Tin_Gui (
	Ten_Xe nvarchar(50),
	Bien_So_Xe nvarchar(50) primary key,
	Ma_Bai_Do_Xe nvarchar(50) unique,
	Id_Chu_So_Huu nvarchar(100) unique
)

create table Bang_Chu_So_Huu (
	Id_Chu_So_Huu nvarchar(100) primary key,
	Ten nvarchar(50),
	So_cmtnd nvarchar(50) unique,
	Dia_Chi nvarchar(200) unique
)

insert into BaiDoXe (Ten_bai, Ma_bai_do_xe, Dia_chi)
values
('Hoang Hoa Tham 01', 'HHT01', '247 Hoang Hoa Tham'),
('Thai Ha 07', 'TH07', '225 Thai Ha'),
('Vincom Parkinglot','VC09','556 Pham Van Dong'),
('Giai Phong 04', 'GP04', '878 Giai Phong'),
('Cau Dien 14', 'CD14', '334 Cau Dien')

select * from BaiDoXe

insert into Thong_Tin_Gui (Ten_Xe, Bien_So_Xe, Ma_Bai_Do_Xe, Id_Chu_So_Huu)
values
('Mercedes GT 53 4 Matic', '16A-09999','HHT01', '15453A'),
('Audi Q7', '29A-15543','TH07', '767678B'),
('BMW i8', '15A-87787', 'VC09', '998989D'),
('Mercedes AMG E200', '67A-859688', 'GP04', '98898F'),
('Toyota Vios','27A-23432', 'CD14', '123123G')

select * from Thong_Tin_Gui

insert into Bang_Chu_So_Huu (Id_Chu_So_Huu, Ten, So_cmtnd, Dia_Chi)
values
('15453A','Chu Viet Dung', '04508573842','S2.11 Vinhome Ocean Park'),
('767678B', 'Bui Thanh Do', '909097842', 'Park 4 Times City'),
('998989D', 'Phung Thai Linh', '9829489', '564 Nguyen Trai'),
('98898F', 'Nguyen Anh Dung', '24323415', '5/54 Dang Van Ngu'),
('123123G', 'Phan Anh Vu', '6345345', '65 Nguyen Xien')

select * from Bang_Chu_So_Huu

select * from BaiDoXe, Bang_Chu_So_Huu, Thong_Tin_Gui
	where BaiDoXe.Ma_bai_do_xe = Thong_Tin_Gui.Ma_Bai_Do_Xe and Thong_Tin_Gui.Id_Chu_So_Huu = Bang_Chu_So_Huu.Id_Chu_So_Huu


avatar
Nguyễn Hữu Hiếu [T2008A]
2020-12-15 06:02:07



create database quanLyBaiDoXe
use quanLyBaiDoXe

create table baiDoXe (
	id int primary key identity(1,1),
	name nvarchar(100) not null,
	address nvarchar(250)
)

create table customer(
	id int primary key identity(1,1),
	name_customer nvarchar(50),
	number_card nvarchar(25),
	address_customer nvarchar(250)
)
create table info (
	id_info int primary key identity(1,1),
	id_customer int references customer(id),
	id_baiDoXe int references baiDoXe(id),
	name_car nvarchar(50),
	number_car nvarchar(20)
)

insert into baiDoXe(name, address)
values
('Chuong Duong Do', 'Chuong Duong - Hoan Kiem - HN'),
('Vinh Yen', 'Vinh Yen - Vinh Phuc'),
('Cau Giay 2', 'Trung Hoa - Cau Giay - HN'),
('Thanh Xuan 3', 'Nga Tu So - Thanh Xuan - HN'),
('Long Bien 1', 'Nguyen Van Cu - Long Bien - HN')

select * from baiDoXe

insert into customer(name_customer, number_card, address_customer)
values
('Nguyen A','0142323','Thanh Xuan - Ha Noi'),
('Nguyen B','02534867','Cau Giay - Ha Noi'),
('Nguyen C','05342756','Hoan Kiem - Ha Noi'),
('Nguyen D','06473','Tu Liem - Ha Noi'),
('Nguyen E','075656','Nam Dinh')

select * from customer

insert info (id_customer, id_baiDoXe, name_car, number_car)
values
(2, 1, 'Honda','29X3-77676'),
(3, 3, 'Mercedes','29X3-45454'),
(2, 2, 'Civic','29X3-75645'),
(3, 3, 'BMW','29X3-53232'),
(5, 3, 'Vios','29X3-97867')

insert info (id_customer, id_baiDoXe, name_car, number_car)
values
(2, 1, 'Honda','29X3-77676'),
(2, 1, 'Honda','343535'),
(3, 3, 'Mercedes','29X3-45454')


select * from baiDoXe
select * from customer
select * from info
--Hien thi thong tin nguoi gui xe
select customer.number_card, customer.name_customer, baiDoXe.name, info.number_car
from baiDoXe, customer, info
where customer.id = info.id_customer and baiDoXe.id = info.id_baiDoXe
--Hien thi so lan gui xe cua tat ca chu so huu (Tên bãi đỗ xe, biển số xe, Chủ Sở Hữu, Số CMTND, Số Lần Gửi)
select customer.name_customer, baiDoXe.name, info.number_car, customer.number_card, count(info.id_customer) 'So lan gui'
from baiDoXe, customer, info
where customer.id = info.id_customer and baiDoXe.id = info.id_baiDoXe
group by customer.name_customer, baiDoXe.name, info.number_car, customer.number_card