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
);
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)
);
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` VARCHAR(15) 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", 20007)
;
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)
);
INSERT INTO `Customers` VALUES
("45", "JMUnionGru", 100, 11, "qwer", "12", "example12", "lol", "kek", "PNH"),
;
SELECT Customers.cust_id, Orders.order_num
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.cust_id=Orders.cust_id;
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL', 'IN', 'MP')
UNION
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name="Fun4All";
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL', 'IN', 'MP')
UNION ALL
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name="Fun4All";
INSERT INTO Customers (`cust_id`, `cust_contact`, `cust_email`, `cust_adress`, `cust_city`, `cust_scate`, `cust_ZIP`, `cust_country`)
SELECT `cust_id`, `cust_contact`, `cust_email`, `cust_adress`, `cust_city`, `cust_scate`, `cust_ZIP`, `cust_country` FROM CustNew
Telegram: DB developers channel