﻿ SQL Challenges-1: Orders items 5 or more times - 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
1052130juice
1062103cake
108 2125cake
1092139cake
1112116cake
1122128cake
1132146chocolate
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(105,2130,'juice');
INSERT INTO orders VALUES(106,2103,'cake');
INSERT INTO orders VALUES(108,2125,'cake');
INSERT INTO orders VALUES(109,2139,'cake');
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(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: