w3resource

SQL Challenges-1: Orders items 5 or more times

SQL Challenges-1: Exercise-18 with Solution

From the following table, write a SQL query to find those items, which have ordered 5 or more times. Return item name and number of orders.

Input:

Table: orders

Structure:

FieldTypeNullKeyDefaultExtra
ORDER_IDint(11)NO
CUSTOMER_IDint(11)NO
ITEM_DESCvarchar(30)NO

Data:

ORDER_IDCUSTOMER_IDITEM_DESC
1012109juice
1022139chocolate
1032120juice
1042108 cookies
1052130juice
1062103cake
1072122cookies
108 2125cake
1092139cake
1102141 cookies
1112116cake
1122128cake
1132146chocolate
1142119cookies
1152142cake

Sample Solution:

SQL Code(MySQL):

DROP TABLE  IF EXISTS  orders;
CREATE TABLE orders ( ORDER_ID INTEGER(5) NOT NULL, CUSTOMER_ID INTEGER(4) NOT NULL, ITEM_DESC varchar(30) NOT NULL);


INSERT INTO orders VALUES(101,2109,'juice');
INSERT INTO orders VALUES(102,2139,'chocolate');
INSERT INTO orders VALUES(103,2120,'juice');
INSERT INTO orders VALUES(104,2108,'cookies');
INSERT INTO orders VALUES(105,2130,'juice');
INSERT INTO orders VALUES(106,2103,'cake');
INSERT INTO orders VALUES(107,2122,'cookies');
INSERT INTO orders VALUES(108,2125,'cake');
INSERT INTO orders VALUES(109,2139,'cake');
INSERT INTO orders VALUES(110,2141,'cookies');
INSERT INTO orders VALUES(111,2116,'cake');
INSERT INTO orders VALUES(112,2128,'cake');
INSERT INTO orders VALUES(113,2146,'chocolate');
INSERT INTO orders VALUES(114,2119,'cookies');
INSERT INTO orders VALUES(115,2142,'cake');
SELECT * FROM  orders;

SELECT item_desc,COUNT(item_desc) AS "Number of orders" 
FROM orders 
GROUP BY item_desc
HAVING COUNT(item_desc)>=5;

Sample Output:

item_desc|Number of orders|
---------|----------------|
cake     |               6|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Big Cities.
Next: Overall execution Rate



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8