CREATE TABLE Shangpin ("Product name" VARCHAR2 (30),
"Product category" VARCHAR2 (30),
"Commodity price" number (30)
)
INSERT into Shangpin values (' Mobile ', ' electronics ', 6600)
INSERT into Shangpin values (' apple ', ' fruit ', 5)
INSERT into shangpin values (' pepper ', ' spices ', 1)
INSERT into shangpin values (' Beef ', ' meat ', 6600)
INSERT into shangpin values (' Potato chips ', ' snacks ', 6600)
INSERT into shangpin values (' mop ', ' daily necessities ', 15)
INSERT into shangpin values (' wok ', ' cookware ', 50)
INSERT into Shangpin values (' short sleeves ', ' costumes ', 90)
INSERT into Shangpin values (' Shoes ', ' footwear ', 240)
SELECT * FROM Shangpin
--Create a table
CREATE TABLE Gouwucar ("Customer" VARCHAR2 (30),
"Buy goods" varchar2 (30),
"Purchase Quantity" number (30)
)
--Inserting data
INSERT into Gouwucar values (' Tom ', ' phone ', 1)
INSERT into Gouwucar values (' Jick ', ' Apple ', 10)
INSERT into Gouwucar values (' Lili ', ' mop ', 1)
INSERT into Gouwucar values (' Lilei ', ' Wok ', 1)
INSERT into Gouwucar values (' Joce ', ' short sleeves ', 3)
INSERT into Gouwucar values (' Halen ', ' Shoes ', 2)
INSERT into Gouwucar values (' Zhang San ', ' null ', NULL)
INSERT into Gouwucar values (' John Doe ', ' null ', NULL)
INSERT into Gouwucar values (' Harry ', ' Potato chips ', 5)
SELECT * FROM Gouwucar
--Create a table
CREATE TABLE Jiesuan ("Goods" VARCHAR2 (30), "Quantity" number (30), "Total Price" (30))
--Inserting data
INSERT into Jiesuan values (' mobile ', 1,6600)
INSERT into Jiesuan values (' Shoes ', 1,240)
INSERT into Jiesuan values (' wok ', 1,50)
INSERT into Jiesuan values (' short sleeves ', 1,15)
INSERT into Jiesuan values (' mop ', 1,90)
INSERT into Jiesuan values (' Pepper ', 1, 1)
INSERT into Jiesuan values (' Apple ', 1,5)
INSERT into Jiesuan values (' Potato chips ', 1,6)
SELECT * from Jiesuan;
--select customer, purchase commodity, commodity type, commodity price, purchase quantity from Shangpin,gouwucar
--where shangpin. Commodity name =gouwucar. Purchase goods
--related tables
Select Customers, buy items, commodity categories, price of goods, purchase quantity, Total price from Shangpin,gouwucar,jiesuan
where (shangpin. Commodity name =gouwucar. Purchase goods) and (gouwucar. Purchase of goods =jiesuan. Products)
Database creation table, inserting data exercise