Manufacturer table

The column name annotation
id Manufacturer ID
name Manufacturer name

Goods list

The column name annotation
id Product id
m_id Manufacturer ID
name Name of commodity

Store list

The column name annotation
id The shop id
name The shop name

Inventory Statement (STOCK)

The column name annotation
s_id The shop id
g_id Product id
price price
stock Inventory quantity

1. Query the product whose name contains pens.

SELECT
  *
FROM
  goods
WHERE
  name LIKE 'pen % %';
Copy the code

2. Query the price of pencils in the store whose ID is 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 = 'pencil';
Copy the code

3. Look up the stores that sell pencils and sort them by price from highest to lowest.

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 = 'pencil'
ORDER BY st.price DESC;
Copy the code

4. Query the name of the store that sells the most items.

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
);
Copy the code

5. Check the average price of a pencil.

SELECT
  AVG(st.price)
FROM
  stock st
  LEFT JOIN goods g ON st.g_id = g.id
WHERE
  g.name = 'pencil';
Copy the code

6. Check the total amount of goods in stock at each store.

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;
Copy the code

7. Query the name of the store that has more than 100 pencils in stock.

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 = 'pencil'
  AND st.stock > 100;
Copy the code

8. Query the name of the store that does not sell pencils.

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 = 'pencil'
      AND st.s_id = s.id
  );
Copy the code

9. Query the name of all pencil manufacturers.

SELECT
  m.name
FROM
  goods g
  LEFT JOIN manufacturer m ON g.m_id = m.id
WHERE
  g.name = 'pencil';
Copy the code

Friendship tips: the topic comes from each real enterprise, the above answers are for reference only, can not determine whether to meet the topic to examine the knowledge point!