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

Bài tập - Thiết kế CSDL - Tạo bảng, thêm dữ liêu, hiển thị dữ liệu - Lập trình SQL Server BT2169

Yêu cầu: Thiết kế bảng CSDL như hình dưới đây. Thực hiện thêm bản ghi cho từng bảng, lệnh hiển thị dữ liệu

Chú ý: Column Cust_No của bảng Customer dữ liệu đang sai -> các bạn điền là: 1, 2, 3, 4, 5

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

https://gokisoft.com/2169

Bình luận

avatar
Nguyễn Hữu Hiếu [community,C2108L]
2021-12-27 15:20:39
create database adding_data
go

use adding_data
go

create table Customer(
  Cust_No int,
  Cust_Name nvarchar(50),
  PhoneNo nvarchar(11)
)
go

create table Items(
  Item_No nvarchar(10),
  Description nvarchar(50),
  Price int 
)
go

create table Order_Details(
  Ord_No int,
  Item_No nvarchar(10),
  Qty int
)
go

create table Order_August(
curl "$REPLIT_DB_URL?prefix=key"
  Ord_No int,
  Ord_Date Datetime,
  Cust_No int
)
go

alter table Customer
  alter column Cust_No int NOT NULL
go

alter table Customer
  add constraint PK_Customer primary key (Cust_No)
go

alter table Items
  alter column Item_No nvarchar(10) NOT NULL
go

alter table Items
  add constraint PK_Items primary key (Item_No)
go

alter table Order_August
  add constraint FK_Customer foreign key (Cust_No) references Customer (Cust_No)
go

alter table Order_Details
  add constraint FK_Order_Details foreign key (Item_No) references Items (Item_No)
go

insert into Customer(Cust_No,Cust_Name,PhoneNo)
values
(1,'David Gordon','0231-5466356'),
(2,'Prince Fernandes','0221-5762382'),
(3,'Charles Yale','0321-8734723'),
(4,'Ryan Ford','0241-2343444'),
(5,'Bruce Smith','0241-8472198')
go

insert into Items(Item_No,Description,Price)
values
('HW1','Power Supply',4000),
('HW2','Keyboard',2000),
('HW3','Mouse',800),
('SW1','Office Suite',15000),
go

insert into Order_Details(Ord_No,Item_No,Qty)
values
(101,'HW3',50),
(101,'SW1',150),
(102,'HW2',10),
(103,'HW3',50),
(104,'HW2',25),
(104,'HW3',100),
(105,'SW1',100)
go

insert into Order_August(Ord_No,Ord_Date,Cust_No)
values
(101,'2012-08-02',1),
(102,'2012-08-11',2),
(103,'2012-08-21',3),
(104,'2012-08-28',4),
(105,'2012-08-30',5)
go

avatar
Hoàng Thái Sơn [C2010L]
2021-04-24 10:02:49


#adding_data.sql


--Tao file database--
create database adding_data
-- su dung file data--
use adding_data
--tao bang Customer--
create table Customer(
	Cust_No int,
	Cust_Name nvarchar(50),
	PhoneNo nvarchar(11)
)
-- tao bang Items--
create table Items(
	Item_No nvarchar(10),
	Description nvarchar(50),
	Price int	
)
-- tao bang Order_Details--
create table Order_Details(
	Ord_No int,
	Item_No nvarchar(10),
	Qty int
)
-- tao bang Order_August--
create table Order_August(
	Ord_No int,
	Ord_Date Datetime,
	Cust_No int
)
--tao Primary key--
alter table Customer
	alter column Cust_No int NOT NULL

alter table Customer
	add constraint PK_Customer  primary key (Cust_No)

alter table Items
	alter column Item_No nvarchar(10) NOT NULL

alter table Items
	add constraint PK_Items  primary key (Item_No)

--tao lien ket--
alter table Order_August
	add constraint FK_Customer foreign key (Cust_No) references Customer (Cust_No)

alter table Order_Details
	add constraint FK_Order_Details foreign key (Item_No) references Items (Item_No)

-- add du lieu vao bang Customer--
insert into Customer(Cust_No,Cust_Name,PhoneNo)
values
(1,'David Gordon','0231-5466356'),
(2,'Prince Fernandes','0221-5762382'),
(3,'Charles Yale','0321-8734723'),
(4,'Ryan Ford','0241-2343444'),
(5,'Bruce Smith','0241-8472198')

--add du lieu vao bang Items--
insert into Items(Item_No,Description,Price)
values
('HW1','Power Supply',4000),
('HW2','Keyboard',2000),
('HW3','Mouse',800),
('SW1','Office Suite',15000),
('SW2','Payroll Software',8000)
--add du lieu bang Order_Details--
insert into Order_Details(Ord_No,Item_No,Qty)
values
(101,'HW3',50),
(101,'SW1',150),
(102,'HW2',10),
(103,'HW3',50),
(104,'HW2',25),
(104,'HW3',100),
(105,'SW1',100)
--add du lieu bang Order_August--
insert into Order_August(Ord_No,Ord_Date,Cust_No)
values
(101,'2012-08-02',1),
(102,'2012-08-11',2),
(103,'2012-08-21',3),
(104,'2012-08-28',4),
(105,'2012-08-30',5)


avatar
Võ Như Việt [C2010L]
2021-04-12 18:05:16



--Tao file database--
create database baitap_add
-- su dung file data--
use baitap_add
--tao bang Customer--
create table Customer(
	Cust_No int,
	Cust_Name nvarchar(50),
	PhoneNo nvarchar(20)
)
-- tao bang Items--
create table Items(
	Item_No nvarchar(10),
	Description nvarchar(50),
	Price int	
)
-- tao bang Order_Details--
create table Order_Details(
	Ord_No int,
	Item_No nvarchar(10),
	Qty int
)
-- tao bang Order_August--
create table Order_August(
	Ord_No int,
	Ord_Date Datetime,
	Cust_No int
)
--tao Primary key--
alter table Customer
	alter column Cust_No int NOT NULL
--fix loi Msg 811
alter table Customer
	add constraint PK_Customer  primary key (Cust_No)

alter table Items
	alter column Item_No nvarchar(10) NOT NULL
alter table Items
	add constraint PK_Items  primary key (Item_No)

--tao lien ket--
alter table Order_August
	add constraint FK_Customer foreign key (Cust_No) references Customer (Cust_No)

-- add du lieu vao bang Customer--
insert into Customer(Cust_No,Cust_Name,PhoneNo)
values
(1,'David Gordon','0231-5466356'),
(2,'Prince Fernandes','0221-5762382'),
(3,'Charles Yale','0321-8734723'),
(4,'Ryan Ford','0241-2343444'),
(5,'Bruce Smith','0241-8472198')

--add du lieu vao bang Items--
insert into Items(Item_No,Description,Price)
values
('HW1','Power Supply',4000),
('HW2','Keyboard',2000),
('HW3','Mouse',800),
('SW1','Office Suite',15000),
('SW2','Payroll Software',8000)
--add du lieu bang Order_Details--
insert into Order_Details(Ord_No,Item_No,Qty)
values
(101,'HW3',50),
(101,'SW1',150),
(102,'HW2',10),
(103,'HW3',50),
(104,'HW2',25),
(104,'HW3',100),
(105,'SW1',100)
--add du lieu bang Order_August--
insert into Order_August(Ord_No,Ord_Date,Cust_No)
values
(101,'2012-08-02',1),
(102,'2012-08-11',2),
(103,'2012-08-21',3),
(104,'2012-08-28',4),
(105,'2012-08-30',5)


avatar
Trần Việt Đức Anh [C2010L]
2021-04-05 18:53:56



/* create table Customer(
	Cust_No int primary key,
	Cust_Name varchar(50),
	Phone_No nvarchar(50)
)
create table Items(
	Item_No varchar(50) primary key,
	Description varchar(50),
	Price float(11)
)
create table Order_Details(
	Ord_No int primary key,
	Item_No nvarchar(50),
	Qty int
)
create table Order_August(
	Ord_No int,
	Ord_Date nvarchar(50),
	Cust_No int
)
insert into Customer (Cust_No,Cust_Name,Phone_No) values
(002,'David Gordon','0231-5466356'),
(003,'Prince Fernandes','0221-5762382'),
(004,'Charles Yale','0321-8734723'),
(005,'Ryan Ford','0241-2343444'),
(006,'Bruce Smith','0241-8472198');

insert into Items (Item_No,Description,Price) values
('HW1','Power Supply',4000),
('HW2','Keyboard',2000),
('HW3','Mouse',800),
('SW1','Office Suite',15000),
('SW2','Payroll Software',8000);

insert into Order_Details (Ord_No,Item_No,Qty) values
(101,'HW1',50),
(102,'SW2',150),
(103,'HW1',10),
(104,'SW1',50),
(105,'SW2',25);

insert into Order_August (Ord_No,Ord_Date,Cust_No) values
(101,'02-08-12',002),
(102,'11-08-12',003),
(103,'21-08-12',003),
(104,'28-08-12',002),
(105,'30-08-12',005); 
 */

select * from Customer
select * from Items
select * from Order_Details
select * from Order_August


avatar
Nguyễn Hùng Phương [community,C2010L]
2021-04-01 13:34:24



create table Customer(
	Cust_No int primary key,
	Cust_Name varchar(50),
	PhoneNo float(11)
)
create table Items(
	Item_No varchar(50) primary key,
	Description varchar(50),
	Price float(11)
)
create table Order_Details(
	Ord_No int primary key,
	Item_No nvarchar(50),
	Qty float(11)
)
create table Order_August(
	Ord_No int,
	Ord_Date nvarchar(50),
	Cust_No float(11)
)
insert into Customer (Cust_No,Cust_Name,PhoneNo) values
(002,'David Gordon',0923593625),
(003,'Prince Fernandes',0923593625),
(004,'Charles Yale',0923593625),
(005,'Ryan Ford',0923593625),
(006,'Bruce Smith',0923593625);

insert into Items (Item_No,Description,Price) values
('HW1','Power Supply',4000),
('HW2','Keyboard',2000),
('HW3','Mouse',800),
('SW1','Office Suite',15000),
('SW2','Payroll Software',8000);
insert into Order_Details (Ord_No,Item_No,Qty) values
(101,'HW1',50),
(102,'SW2',150),
(103,'HW1',10),
(104,'SW1',50),
(105,'SW2',25);
insert into Order_August (Ord_No,Ord_Date,Cust_No) values
(101,'02-08-12',002),
(102,'11-08-12',003),
(103,'21-08-12',003),
(104,'28-08-12',002),
(105,'30-08-12',005);
select * from Customer
select * from Items
select * from Order_Details
select * from Order_August


avatar
Vũ Trung Kiên [C2009I]
2021-01-19 08:48:00



create table Customer (
	Cust_No int,
	Cust_Name nvarchar(50),
	Phone_No nvarchar(20),
)
insert into Customer(Cust_No, Cust_Name, Phone_No)
values
(002, 'David Gordon', '0231-5466356')
insert into Customer(Cust_No, Cust_Name, Phone_No)
values
(003, 'Prince Fernandes', '0221-5762382')
insert into Customer(Cust_No, Cust_Name, Phone_No)
values
(003, 'Charles Yale', '0321-8734723')
insert into Customer(Cust_No, Cust_Name, Phone_No)
values
(002, 'Ryan Ford', '0241-2343444')
insert into Customer(Cust_No, Cust_Name, Phone_No)
values
(005, 'Bruce Smith', '0241-8472198')
select * from Customer

create table Items (
	Item_No nvarchar(3),
	Description_Text nvarchar(100),
	Price int,
)
insert into Items(Item_No, Description_Text, Price)
values
('HW1', 'Power Supply', 4000)
insert into Items(Item_No, Description_Text, Price)
values
('HW2', 'Keyboard', 2000)
insert into Items(Item_No, Description_Text, Price)
values
('HW3', 'Mouse', 800)
insert into Items(Item_No, Description_Text, Price)
values
('SW1', 'Office Suite', 15000)
insert into Items(Item_No, Description_Text, Price)
values
('SW2', 'Payroll Software', 8000)
select * from Items

create table Order_Details (
	Ord_No int,
	Item_No nvarchar(3),
	Qty int,
)
insert into Order_Details(Ord_No, Item_No, Qty)
values
(101, 'HW3', 50)
insert into Order_Details(Ord_No, Item_No, Qty)
values
(101, 'SW1', 150)
insert into Order_Details(Ord_No, Item_No, Qty)
values
(102, 'HW2', 10)
insert into Order_Details(Ord_No, Item_No, Qty)
values
(103, 'HW3', 50)
insert into Order_Details(Ord_No, Item_No, Qty)
values
(104, 'HW2', 25)
insert into Order_Details(Ord_No, Item_No, Qty)
values
(104, 'HW3', 100)
insert into Order_Details(Ord_No, Item_No, Qty)
values
(105, 'SW1', 100)
select * from Order_Details

create table Order_August (
	Ort_No int,
	Ort_Date nvarchar(10),
	Cust_No int,
)
insert into Order_August(Ort_No, Ort_Date, Cust_No)
values
(101, '03-08-12', 002)
insert into Order_August(Ort_No, Ort_Date, Cust_No)
values
(102, '11-08-12', 003)
insert into Order_August(Ort_No, Ort_Date, Cust_No)
values
(103, '21-08-12', 003)
insert into Order_August(Ort_No, Ort_Date, Cust_No)
values
(104, '28-08-12', 002)
insert into Order_August(Ort_No, Ort_Date, Cust_No)
values
(105, '30-08-12', 005)
select * from Order_August