Create table `Vendors`
( `vend_country` VARCHAR (10) NOT NULL
, `vend_name` VARCHAR (20) NOT NULL
, `vend_state` VARCHAR(15) NOT NULL
, `cust_city` VARCHAR (10) NOT NULL
, `vend_id` INT(3) NOT NULL
);
INSERT INTO `Vendors` VALUES
("USA", "JMUnion", "10", "1", 1),
("SS", "Cucumber", "1000", "2", 2),
("USSR", "CornJo", "12", "3", 3),
("CHINA", "Slave", "9999", "4", 4),
("IRELAND", "~pi", "3.49", "5", 5)
;
Create table `Orders`
( `order_num` INT NOT NULL
, `quantity` VARCHAR (20) NOT NULL
, `item_price` VARCHAR(15) NOT NULL
, `order_date` DATE NOT NULL
, `cust_id` INT NOT NULL
, `prod_id` VARCHAR(10) NOT NULL
, `cust_contact` VARCHAR(10) NOT NULL
);
INSERT INTO `Orders` VALUES
(20007, "100", "10", "2000-11-11", 100, "RGAN01", ""),
(20008, "200", "1000", "2011-11-11", 3, "RGAN01", ""),
(20007, "300", "12", "2004-06-17", 2, "e", ""),
(200007, "400", "9999", "2004-04-04", 14, "e", ""),
(1, "900", "3.49", "2001-01-01", 12, "q", "")
;
Create table `Customers`
( `cust_num` VARCHAR (10) NOT NULL
, `cust_name` VARCHAR (20) NOT NULL
, `cust_price` DOUBLE
, `cust_id` INT NOT NULL
, `cust_state` VARCHAR(10) NOT NULL
, `cust_contact` VARCHAR(10) NOT NULL
, `cust_email` VARCHAR(20) NOT NULL
, `cust_address` VARCHAR(30)
, `cust_scate`VARCHAR(30)
, `cust_ZIP` VARCHAR(30)
, `cust_city` VARCHAR(30)
, `cust_country` VARCHAR(20)
);
INSERT INTO `Customers` VALUES
("7", "JMUnion", 10, 1, "qwe", "1", "example1", "", "", "", "", ""),
("2", "Cucumber", 1000, 2, "rty", "2", "example2", "", "", "", "", ""),
("3", "CornJo", 12, 3, "asd", "3", "example3", "", "", "", "", ""),
("4", "Slave", 9999, 4, "fgh", "4", "example4", "", "", "", "", ""),
("1", "Fun4All", 3.49, 5, "zxc", "Jim Jones", "example5", "", "", "", "", "")
;
Create table `Products`
( `prod_id` VARCHAR (3) NOT NULL
, `prod_name` VARCHAR (20) NOT NULL
, `prod_price` DOUBLE NOT NULL
, `vend_id` INT (3) NOT NULL
);
INSERT INTO `Products` VALUES
("7", "Banana", 10, 1),
("2", "Cucumber", 1000, 2),
("3", "Corn", 12, 3),
("4", "Slave", 9999, 4),
("1", "~pi", 3.49, 5)
;
Create table `OrderItems`
( `prod_id` VARCHAR (10) NOT NULL
, `quantity` INT NOT NULL
, `item_price` INT NOT NULL
, `item_desc` VARCHAR (10) NOT NULL
, `order_num` INT NOT NULL
);
INSERT INTO `OrderItems` VALUES
("RGAN01", 100, 10, "item1", 200005),
("RGAN01", 200, 1000, "item2", 20005),
("RGAN01", 300, 12, "item3", 1000),
("RGAN01", 400, 9999, "item4", 20008)
;
Create table `CustNew`
( `cust_num` VARCHAR (10) NOT NULL
, `cust_name` VARCHAR (20) NOT NULL
, `cust_price` DOUBLE
, `cust_id` INT NOT NULL
, `cust_state` VARCHAR(10) NOT NULL
, `cust_contact` VARCHAR(10) NOT NULL
, `cust_email` VARCHAR(20) NOT NULL
, `cust_address` VARCHAR(30)
, `cust_scate`VARCHAR(30)
, `cust_ZIP` VARCHAR(30)
, `cust_city` VARCHAR(20)
, `cust_country` VARCHAR(20)
);
INSERT INTO `Customers` VALUES
("45", "JMUnionGru", 100, 11, "qwer", "12", "example12", "lol", "kek", "PNH", "IVANOVO", "Russia")
;
SELECT * FROM Products;
ALTER TABLE Vendors
ADD vend_phone INT(20) NULL ;
SELECT * FROM Vendors;
CREATE VIEW ProductCustomer
AS
SELECT Customers.cust_name, Orders.cust_contact, OrderItems.prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id=Orders.cust_id AND OrderItems.order_num=Orders.order_num;
CREATE VIEW CustomerEmailList AS
SELECT cust_id, cust)name, cust_email FROM Customers WHERE cust_email != NULL;
SELECT prod_id, quantity, item_price, quantity * item_price FROM OrderItems WHERE order_num=20008
Telegram: DB developers channel