-- 1 列出所有商品的型号(PDT_MODEL),品牌(MANU)和类型(CAT)
use etg;
SELECT PDT_MODEL,MANU,CAT
FROM pdt;
-- 2 列出品牌(MANU)为Asus的所有产品的类型(CAT)和价格(PRICE),列名分别为Category,$
SELECT CAT AS Category, PRICE AS '$'
FROM PDT
WHERE MANU = 1;
-- 3 列出有价格(PRICE)在500元以上的品牌 DISTINCT去重复
select distinct pdt_model
from pdt
where price > 500;
/*4 列出所有特价商品(STATUS=S)的品牌(MANU),类型(CAT),原价(PRICE),优惠价(SPECIAL_PRICE),节省的价格(PRICE - SPECIAL_PRICE),列名分别是Manufacture,Category,Original Price,Special Price,Save*/
SELECT MANU AS Manufacture,
CAT AS Category,
PRICE AS 'Original Price',
SPECIAL_PRICE AS 'Special Price',
PRICE - SPECIAL_PRICE AS Save
FROM PDT
WHERE STATUS = 'S';
-- 5 列出含有特价商品的品牌。
SELECT PDT_MODEL
FROM PDT
WHERE SPECIAL_PRICE IS NOT NULL;
-- 6 列出所有非DDR2内存(DDR2内存即在类型PDT_MODEL里,包含DDR2字样)。
SELECT *
FROM PDT
WHERE PDT_MODEL LIKE '%DDR2%';
-- 7 列出商品类型(PDT_MODEL)以M或者P开头的所有商品信息。
SELECT PDT_MODEL
FROM PDT
WHERE PDT_MODEL LIKE 'M%' OR PDT_MODEL LIKE 'P%';
-- 8 列出所有价格等于499,999,1499的商品
SELECT *
FROM PDT
WHERE PRICE = 499 OR PRICE = 999 OR PRICE = 1499;
-- 9 如果一次性购买3件商品会减10%的折扣,求购买三件Asus型号为P5Q商品的总的价格,列名为Total Prices。
SELECT SUM(PRICE * 3 * 0.9) AS `Total Prices`
FROM PDT
WHERE MANU = 1 AND PDT_MODEL = 'P5Q';
-- 10 以“Benq - G900HD : $899”的模式列出所有显示器的厂商,型号,和价格。
-- || CONCAT(str1,str2,...)
SELECT CONCAT(MANU, " - ", PDT_MODEL, " : $", PRICE)
FROM PDT
-- 11 列出所有Processor新产品,按发布日期从现在到过去的顺序排列
select *
from pdt
where cat = 6
and status = "N"
order by create_dt;
-- 12 列出所有非特价的处理器和显示器
SELECT *
from PDT
WHERE cat ="6"
OR cat ="13"
AND STATUS !="S";
-- 13 列出拥有价格在500-1000之间的商品的品牌
SELECT DISTINCT PDT_MODEL
FROM PDT
WHERE PRICE BETWEEN 500 AND 1000;
-- 14 列出所有非特价商品
SELECT *
FROM PDT
WHERE SPECIAL_PRICE IS NULL;
-- 15 列出产品描述(PDT_DESC)里包含%的所有产品
SELECT *
FROM PDT
WHERE PDT_DESC LIKE '%/%%' ESCAPE '/';
08-04
1070