SQLize | PHPize | SQLtest

DROP TABLE IF EXISTS Customers; CREATE TABLE Customers ( cust_id char(10) PRIMARY KEY NOT NULL, cust_name char(50) , cust_address char(50) DEFAULT NULL, cust_city char(50) DEFAULT NULL, cust_state char(5) DEFAULT NULL, cust_zip char(10) DEFAULT NULL, cust_country char(50) DEFAULT NULL, cust_contact char(50) DEFAULT NULL, cust_email char(255) DEFAULT NULL ); INSERT INTO Customers VALUES ('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com'), ('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green', NULL), ('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com'), ('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com'), ('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard', NULL); -- Заказы DROP TABLE IF EXISTS Orders; CREATE TABLE Orders ( order_num int(11) PRIMARY KEY NOT NULL, order_date datetime NOT NULL, cust_id CHAR(10) NOT NULL ); INSERT INTO Orders VALUES (20005, '2004-05-01 00:00:00', '1000000001'), (20006, '2004-01-12 00:00:00', '1000000003'), (20007, '2004-01-30 00:00:00', '1000000004'), (20008, '2004-02-03 00:00:00', '1000000005'), (20009, '2004-02-08 00:00:00', '1000000001'); DROP TABLE IF EXISTS CustNew; CREATE TABLE CustNew ( cust_id char(10) PRIMARY KEY NOT NULL, cust_name char(50), cust_address char(50) DEFAULT NULL, cust_city char(50) DEFAULT NULL, cust_state char(5) DEFAULT NULL, cust_zip char(10) DEFAULT NULL, cust_country char(50) DEFAULT NULL, cust_contact char(50) DEFAULT NULL, cust_email char(255) DEFAULT NULL ); CREATE TABLE Products ( prod_id CHAR(10) NOT NULL, vend_id CHAR(10) NOT NULL, prod_name CHAR(254) NOT NULL, prod_price DECIMAL(8, 2) NOT NULL, prod_desc VARCHAR(1000) NULL, PRIMARY KEY (prod_id) ); DROP TABLE IF EXISTS Vendors; CREATE TABLE Vendors ( vend_id VARCHAR(5) PRIMARY KEY NOT NULL, vend_name VARCHAR(50) NOT NULL, vend_address VARCHAR(50) DEFAULT NULL, vend_city VARCHAR(50) DEFAULT NULL, vend_state VARCHAR(5) DEFAULT NULL, vend_zip VARCHAR(10) DEFAULT NULL, vend_country VARCHAR(50) DEFAULT NULL ); INSERT INTO Vendors VALUES ('BRE02', 'Bear Emporium', '500 Park Street', 'Anytown', 'OH', '44333', 'USA'), ('BRS01', 'Bears R Us', '123 Main Street', 'Bear town', 'MI', '44444', 'USA'), ('DLL01', 'Doll House Inc.', '555 High Street', 'Dollsville', 'CA', '99999', 'USA'), ('FNG01', 'Fun and Games', '42 Galaxy Road', 'London', NULL, 'N16 6PS', 'USA'), ('FRB01', 'Furball Inc.', '1000 5th Avenue', 'New York', 'NY', '11111', 'England'), ('JT01', 'Jouets et ours', '1 Rue Amusement', 'Paris', NULL, '45678', 'France'); DROP TABLE IF EXISTS Orders; CREATE TABLE Orders ( order_num int(11) PRIMARY KEY NOT NULL, order_date datetime NOT NULL, cust_id CHAR(10) NOT NULL); INSERT INTO Orders VALUES (20005, '2004-05-01 00:00:00', '1000000001'), (20006, '2004-01-12 00:00:00', '1000000003'), (20007, '2004-01-30 00:00:00', '1000000004'), (20008, '2004-02-03 00:00:00', '1000000005'), (20009, '2004-02-08 00:00:00', '1000000001'); -- Айтемы заказов DROP TABLE IF EXISTS OrderItems; CREATE TABLE OrderItems ( order_num int(11) NOT NULL, order_item int(11) NOT NULL, prod_id char(10) NOT NULL, quantity int(11) NOT NULL, item_price decimal(8,2) NOT NULL ); INSERT INTO OrderItems VALUES (20005, 1, 'BR01', 100, 5.49), (20005, 2, 'BR03', 100, 10.99), (20006, 1, 'BR01', 20, 5.99), (20006, 2, 'BR02', 10, 8.99), (20006, 3, 'BR03', 10, 11.99), (20007, 1, 'BR03', 50, 11.49), (20007, 2, 'BNBG01', 100, 2.99), (20007, 3, 'BNBG02', 100, 2.99), (20007, 4, 'BNBG03', 100, 2.99), (20007, 5, 'RGAN01', 50, 4.49), (20008, 1, 'RGAN01', 5, 4.99), (20008, 2, 'BR03', 5, 11.99), (20008, 3, 'BNBG01', 10, 3.49), (20008, 4, 'BNBG02', 10, 3.49), (20008, 5, 'BNBG03', 10, 3.49), (20009, 1, 'BNBG01', 250, 2.49), (20009, 2, 'BNBG02', 250, 2.49), (20009, 3, 'BNBG03', 250, 2.49); CREATE VIEW ProductCustomers AS SELECT C.cust_name, C.cust_contact, OI.prod_id FROM Customers C JOIN Orders O ON C.cust_id = O.cust_id JOIN OrderItems OI ON O.order_num = OI.order_num; SELECT * FROM ProductCustomers