SQL 问与答4

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products`  (
  `id` int(0) NULL DEFAULT NULL,
  `productname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `category` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `price` int(0) NULL DEFAULT NULL,
  `stock_qty` int(0) NULL DEFAULT NULL,
  `supplier_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES (1, 'Laptop', 'Electronics', 75000, 15, 101);
INSERT INTO `products` VALUES (2, 'Mouse', 'Electronics', 1500, 50, 102);
INSERT INTO `products` VALUES (3, 'Chair', 'Furniture', 8000, 25, 103);
INSERT INTO `products` VALUES (4, 'Keyboard', 'Electronics', 2500, 30, 102);
INSERT INTO `products` VALUES (5, 'Desk', 'Furniture  ', 15000, 10, 103);
INSERT INTO `products` VALUES (6, 'Monitor', 'Electronics', 25000, 20, 101);
INSERT INTO `products` VALUES (7, 'Table', 'Furniture', 5000, 40, 104);
INSERT INTO `products` VALUES (8, 'Webcam', 'Electronics', 3000, 35, 102);

SET FOREIGN_KEY_CHECKS = 1;

 计算每个产品类别的平均价格

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;

查找价格高于其所属类别平均价格的产品

SELECT productname, category, price
FROM products p
WHERE price > (SELECT AVG(price)
FROM products
WHERE category = p.category);

找出每个类别中价格最高的产品

SELECT category, MAX(price) AS max_price
FROM products
GROUP BY category;

查找库存数量少于 20 的产品

SELECT 'OrangeDBM' as AUTHOR, productname, stock_qty
FROM products
WHERE stock_qty < 20;