Exercise SQL - Solution

Request for data

  • Write a SQL query for accessing all the elements in the table article. 
    SELECT * FROM article
  • Write a query for displaying the name and the price of all articles and sort them with price (descending) 
    SELECT name, price
FROM `article` 
ORDER BY price DESC
  • Write a query for displaying for each article the name and its category name (from the other table). 
    SELECT article.name AS name, category.name AS category
FROM `category` , `article` 
WHERE category.categoryID = article.categoryID
    Or 
    SELECT a.name AS name, c.name AS category
FROM `category` as c 
INNER JOIN `article` as a 
ON c.categoryID = a.categoryID
  • For each category, display the smallest price, and the number of elements 
    SELECT category.name AS category, 
       min( article.price ) AS price,
       category.categoryID
FROM `category`
INNER JOIN `article` 
ON category.categoryID = article.categoryID
GROUP BY categoryID
  • For each category, display an article having the smallest price 
    CREATE TEMPORARY TABLE resultat(
 SELECT category.name AS category,  
        min( article.price ) AS price, 
        category.categoryID
 FROM `category` , `article` 
 WHERE category.categoryID = article.categoryID
 GROUP BY category.categoryID
);# Affected rows:2

SELECT article.name, resultat.category, resultat.price
   FROM resultat, article
   WHERE article.categoryID = resultat.categoryID
   AND article.price = resultat.price;# Rows: 2
DROP TABLE resultat;# MySQL returned an empty result set (i.e. zero rows).
    This can also be written: 
    SELECT article.name, resultat.category, resultat.price
   FROM (SELECT category.name AS category,  
             min( article.price ) AS price, 
             category.categoryID
         FROM `category` , `article` 
         WHERE category.categoryID = article.categoryID
         GROUP BY category.categoryID
         ) as resultat, 
   article
   WHERE article.categoryID = resultat.categoryID
   AND article.price = resultat.price;
BACK TO TOP © 2014-2021 Emmanuel Benoist