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
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
Tags:
Phản hồi từ học viên
5
(Dựa trên đánh giá ngày hôm nay)
![Nguyễn Hữu Hiếu [community,C2108L]](https://www.gravatar.com/avatar/a0bf50bf5fc1f4be215c41edd7e659fc.jpg?s=80&d=mm&r=g){kind=avatar}
Nguyễn Hữu Hiếu
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
![Hoàng Thái Sơn [C2010L]](https://www.gravatar.com/avatar/f7030644ed6e4a7a1fd3fd27abf6ff02.jpg?s=80&d=mm&r=g){kind=avatar}
Hoàng Thái Sơn
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)![Võ Như Việt [C2010L]](https://www.gravatar.com/avatar/fb93c99beb23339eb21f4d4ffe8981af.jpg?s=80&d=mm&r=g){kind=avatar}
Võ Như Việt
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)![Trần Việt Đức Anh [C2010L]](https://www.gravatar.com/avatar/caee507e11365ca2cf5068cbea5c740a.jpg?s=80&d=mm&r=g){kind=avatar}
Trần Việt Đức Anh
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![Nguyễn Hùng Phương [community,C2010L]](https://www.gravatar.com/avatar/6732a4cbcdf15169a0f037e93226bc48.jpg?s=80&d=mm&r=g){kind=avatar}
Nguyễn Hùng Phương
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![Vũ Trung Kiên [C2009I]](https://www.gravatar.com/avatar/abf994de48c0204239812f8d9a7dcf8c.jpg?s=80&d=mm&r=g){kind=avatar}
Vũ Trung Kiên
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
