【SQL开源代码栏目提醒】:网学会员鉴于大家对SQL开源代码十分关注,论文会员在此为大家搜集整理了“inventory_access.sql”一文,供大家参考学习
// Modify this script as needed for your database.
CREATE TABLE Categories(CategoryID NUMBER(6,0), CategoryName VARCHAR(50))
INSERT INTO Categories VALUES(1,"Beverages")
INSERT INTO Categories VALUES(2,"Condiments")
INSERT INTO Categories VALUES(3,"Confections")
INSERT INTO Categories VALUES(4,"Dairy")
INSERT INTO Categories VALUES(5,"Grains/Cereals")
INSERT INTO Categories VALUES(6,"Meat/Poultry")
INSERT INTO Categories VALUES(7,"Produce")
INSERT INTO Categories VALUES(8,"Seafood")
CREATE TABLE Inventory(ProductID NUMBER(6,0), ProductName VARCHAR(30), ProductDescription VARCHAR(50), CategoryID NUMBER(6,0), Price float, ReorderLevel NUMBER(3,0), Discontinued boolean, LeadTime VARCHAR(30), Quantity(4,0))
INSERT INTO Inventory (2,"Tibetan Barley Beer","Unique flavor",1,$4.00,10,0,"30 Days",100)
INSERT INTO Inventory (5,"Chef Anton's Gumbo Mix","Gumbo mix",8,$55.00,10,0,"10 Days",3)
INSERT INTO Inventory (15,"Blue potato chips","nice texture",5,$12.00,5,0,"5 Days",7)
INSERT INTO Inventory (17,"Wax lips","tasty treat",3,$2.00,10,1,"10 Days",0)
INSERT INTO Inventory (19,"Pita pieces",,5,$15.00,20,0,"1 Day",4)
INSERT INTO Inventory (20,"Tomato Jelly","yummy",2,$1.00,100,0," 2 Days",20)
INSERT INTO Inventory (21,"Fishheads",,8,$12.00,50,0,"10 Days",0)
INSERT INTO Inventory (24,"Jam Juice",,1,$3.00,10,0,"1 Day",5)
CREATE TABLE ProductSuppliers (SupplierID NUMBER(6,0), ProductID NUMBER(6,0), Price float)
INSERT INTO ProductSuppliers (1,2,5.75)
INSERT INTO ProductSuppliers (4,2,7.00)
INSERT INTO ProductSuppliers (3,5,2.25)
INSERT INTO ProductSuppliers (1,15,7.75)
INSERT INTO ProductSuppliers (2,21,25.00)
INSERT INTO ProductSuppliers (4,17,1.00)
INSERT INTO ProductSuppliers (3,15,5.00)
INSERT INTO ProductSuppliers (5,19,5.00)
INSERT INTO ProductSuppliers (4,21,12.00)
INSERT INTO ProductSuppliers (1,24,3.00)
INSERT INTO ProductSuppliers (2,24,4.00)
CREATE TABLE Suppliers (SupplierID NUMBER(6,0), SupplierName VARCHAR(50), ContactName VARCHAR(50), Address VARCHAR(30), City VARCHAR(20), PostalCode VARCHAR(10), StateOrProvince VARCHAR(10), Country VARCHAR(10), PhoneNumber VARCHAR(18), FaxNumber VARCHAR(18), PaymentTerms VARCHAR(10), EmailAddress VARCHAR(30), Notes VARCHAR(50))
INSERT INTO Suppliers (1,"Exotic Liquids","Charlotte Cooper","Purchasing Manager","49 Gilbert St.","London","EC1 4SD",,"UK","( 71) 555-2222",,"Net 30 days",,)
INSERT INTO Suppliers (2,"New Orleans Cajun Delights","Shelley Burke","Order Administrator","P.O. Box 78934","New Orleans","70117-",,"USA","(100) 555-4822",,"Net 60 days",,)
INSERT INTO Suppliers (3,"Grandma Kelly's Homestead","Regina Murphy","Sales Representative","707 Oxford Rd.","Ann Arbor","48104-",,"USA","(313) 555-5735","(313) 555-3349","Net 30 days",,)
INSERT INTO Suppliers (4,"Tokyo Traders","Yoshi Nagase","Marketing Manager","9-8 Sekimai
Musashino-shi","Tokyo","100 -",,"Japan","(03) 3555-5011",,"Net 30 days",,)
INSERT INTO Suppliers (5,"Cooperativa de Quesos 'Las Cabras'","Antonio del Valle Saavedra ","Export Administrator","Calle del Rosal 4","Oviedo","33007-",,"Spain","(98) 598 76 54",,"Net 30 days",,)
CREATE VIEW ProductSuppliersView on (SELECT DISTINCT Suppliers.SupplierName, Inventory.ProductName, Categories.CategoryName, ProductSuppliers.Price
FROM Suppliers WHERE (Categories.CategoryID = Inventory.CategoryID) and (Inventory.ProductID = ProductSuppliers.ProductID) and Suppliers.SupplierID = ProductSuppliers.SupplierID))
;