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
, `cust_id` INT NOT NULL
, `prod_id` VARCHAR(10) NOT NULL
);
INSERT INTO `Orders` VALUES
("1", "100", "10", "2000-11-11", 100, "RGAN01"),
("2", "200", "1000", "2011-11-11", 3, "RGAN01"),
("3", "300", "12", "2004-06-17", 2, "e"),
("4", "400", "9999", "2004-04-04", 14, "e"),
("5", "900", "3.49", "2001-01-01", 12, "q")
;
Create table `Customers`
( `cust_num` VARCHAR (3) NOT NULL
, `cust_name` VARCHAR (20) NOT NULL
, `cust_price` DOUBLE NOT NULL
, `cust_id` INT NOT NULL
);
INSERT INTO `Customers` VALUES
("7", "JMUnion", 10, 1, "qwe"),
("2", "Cucumber", 1000, 2, "rty"),
("3", "CornJo", 12, 3, "asd"),
("4", "Slave", 9999, 4, "fgh"),
("1", "~pi", 3.49, 5, "zxc")
;
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", 20007)
;
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >=2;
SELECT order_num, COUNT(*) AS items
FROM OrderItems
WHERE order_num=20007
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
SELECT DISTINCT cust_id
FROM Orders
WHERE order_num IN (
SELECT order_num
FROM OrderItems
WHERE prod_id='RGAN01'
);
SELECT cust_name, cust_state,
(SELECT COUNT(*) FROM Orders WHERE Orders.cust_id =
Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
Telegram: DB developers channel