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
);
INSERT INTO `Vendors` VALUES
("USA", "JMUnion", "10", "1"),
("SS", "Cucumber", "1000", "2"),
("USSR", "CornJo", "12", "3"),
("CHINA", "Slave", "9999", "4"),
("IRELAND", "~pi", "3.49", "5")
;
Create table `Orders`
( `order_num` VARCHAR (10) NOT NULL
, `quantity` VARCHAR (20) NOT NULL
, `item_price` VARCHAR(15) NOT NULL
, `order_date` DATE NOT NULL
);
INSERT INTO `Orders` VALUES
("1", "100", "10", "2000-11-11"),
("2", "200", "1000", "2011-11-11"),
("3", "300", "12", "2004-06-17"),
("4", "400", "9999", "2004-04-04"),
("5", "900", "3.49", "2001-01-01")
;
Create table `Customers`
( `cust_num` VARCHAR (3) NOT NULL
, `cust_name` VARCHAR (20) NOT NULL
, `cust_price` DOUBLE NOT NULL
, `cust_id` VARCHAR (10) NOT NULL
);
INSERT INTO `Customers` VALUES
("7", "JMUnion", 10, "1"),
("2", "Cucumber", 1000, "2"),
("3", "CornJo", 12, "3"),
("4", "Slave", 9999, "4"),
("1", "~pi", 3.49, "5")
;
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
("1", 100, "10", "item1", 200005),
("2", 200, "1000", "item2", 20005),
("3", 300, "12", "item3", 1000),
("4", 400, "9999", "item4", 20005),
;
SELECT MIN(prod_price) AS max_price
FROM Products;
SELECT SUM(quantity) AS item_ordered
FROM `OrderItems`
WHERE `order_num`=20005;
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products;
SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg
FROM Products;
SELECT vend_id, COUNT(vend_id) AS num_prods
FROM Products
GROUP BY vend_id;
Telegram: DB developers channel