﻿ 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:

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