生产厂家表(manufacturer)
列名 | 注释 |
---|---|
id | 生产厂家id |
name | 生产厂家名称 |
商品表(goods)
列名 | 注释 |
---|---|
id | 商品id |
m_id | 生产厂家id |
name | 商品名称 |
商店表(store)
列名 | 注释 |
---|---|
id | 商店id |
name | 商店名称 |
库存表(stock)
列名 | 注释 |
---|---|
s_id | 商店id |
g_id | 商品id |
price | 售价 |
stock | 库存数量 |
1.查询名称包含笔的商品。
SELECT
*
FROM
goods
WHERE
name LIKE '%笔%';
2.查询 id 为 1 的商店的铅笔售价。
SELECT
st.price
FROM
stock st
LEFT JOIN goods g ON st.g_id = g.id
WHERE
st.s_id = 1
AND g.name = '铅笔';
3.查询有铅笔出售的商店名称,并按售价由高到低排序。
SELECT
s.name
FROM
stock st
LEFT JOIN store s ON st.s_id = s.id
LEFT JOIN goods g ON st.g_id = g.id
WHERE
g.name = '铅笔'
ORDER BY st.price DESC;
4.查询销售商品种类最多的商店名称。
SELECT
s.name
FROM
stock st
LEFT JOIN store s ON st.s_id = s.id
GROUP BY st.s_id
HAVING COUNT(1) = (
SELECT
MAX(g_count)
FROM
(SELECT COUNT(1) g_count FROM stock st GROUP BY st.s_id) st_temp
);
5.查询铅笔的平均售价。
SELECT
AVG(st.price)
FROM
stock st
LEFT JOIN goods g ON st.g_id = g.id
WHERE
g.name = '铅笔';
6.查询每个商店的库存商品总金额。
SELECT
s.name, SUM(st.price * st.stock)
FROM
stock st
LEFT JOIN store s ON st.s_id = s.id
GROUP BY st.s_id;
7.查询铅笔库存大于 100 的商店名称。
SELECT
s.name
FROM
stock st
LEFT JOIN store s ON st.s_id = s.id
LEFT JOIN goods g ON st.g_id = g.id
WHERE
g.name = '铅笔'
AND st.stock > 100;
8.查询不卖铅笔的商店名称。
SELECT
s.name
FROM
store s
WHERE
NOT EXISTS (
SELECT
1
FROM
stock st
LEFT JOIN goods g ON st.g_id = g.id
WHERE
g.name = '铅笔'
AND st.s_id = s.id
);
9.查询所有的生产铅笔的厂家名称。
SELECT
m.name
FROM
goods g
LEFT JOIN manufacturer m ON g.m_id = m.id
WHERE
g.name = '铅笔';
友情提示: 题目来源于各家真实企业,以上回答仅供参考,不能确定是否符合出题人要考查的知识点!