query database

mysql query.sql

show tables;
select * from orders;
select * from orders order by orders_id desc limit 5;

-- MySQL dump 10.13  Distrib 5.7.33, for Win64 (x86_64)
--
-- Host: localhost    Database: student
-- ------------------------------------------------------
-- Server version	5.7.33

--
-- Table structure for table `orders`
--

DROP TABLE IF EXISTS `orders`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `orders` (
  `orders_id` int(11) NOT NULL AUTO_INCREMENT,
  `orders_numbers` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `total_tax` decimal(7,2) NOT NULL,
  `date_purchased` datetime DEFAULT NULL,
  `customers_id` int(11) DEFAULT NULL,
  `last_modified` datetime DEFAULT NULL,
  `delivery_time` varchar(191) COLLATE utf8_unicode_ci NOT NULL,
  `delivery_latitude` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `delivery_longitude` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `order_price` double DEFAULT NULL,
  PRIMARY KEY (`orders_id`),
  KEY `idx_orders_customers_id` (`customers_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6816 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `orders`
--

LOCK TABLES `orders` WRITE;
/*!40000 ALTER TABLE `orders` DISABLE KEYS */;
INSERT INTO `orders` VALUES (203,'00000203',146.00,'2012-02-10 00:09:04',292,'2022-02-10 00:09:04','',NULL,NULL,150),(204,'00000204',180.00,'2012-02-10 00:09:04',299,'2022-02-10 00:27:42','',NULL,NULL,250),(205,'00000205',79.00,'2012-02-10 00:09:04',300,'2022-02-10 01:49:36','',NULL,NULL,125.25),(206,'00000206',150.00,'2013-02-10 00:09:04',302,'2022-02-10 03:10:38','',NULL,NULL,450.63),(207,'00000207',94.00,'2013-02-10 00:09:04',305,'2022-02-10 10:08:21','',NULL,NULL,450.63),(208,'00000208',161.00,'2013-02-10 00:09:04',124,'2022-02-10 10:10:44','',NULL,NULL,450.63),(209,'00000209',60.00,'2013-02-10 00:09:04',306,'2022-02-10 11:27:20','',NULL,NULL,450.63),(210,'00000210',288.00,'2014-02-10 00:09:04',308,'2022-02-10 11:51:01','',NULL,NULL,450.63),(211,'00000211',150.00,'2014-02-10 00:09:04',307,'2022-02-10 11:54:55','',NULL,NULL,450.63),(212,'00000212',143.00,'2014-02-10 00:09:04',309,'2022-02-10 12:00:49','',NULL,NULL,450.63),(213,'00000213',139.00,'2014-02-10 00:09:04',310,'2022-02-10 12:05:06','',NULL,NULL,450.63),(214,'00000214',131.00,'2015-02-10 00:09:04',312,'2022-02-10 12:18:08','',NULL,NULL,850.47),(215,'00000215',206.00,'2015-02-10 00:09:04',118,'2022-02-10 12:19:10','',NULL,NULL,114.15),(216,'00000216',64.00,'2015-02-10 00:09:04',118,'2022-02-10 12:44:04','',NULL,NULL,150),(217,'00000217',101.00,'2015-02-10 00:09:04',313,'2022-02-10 12:49:05','',NULL,NULL,850.47),(218,'00000218',143.00,'2015-02-10 00:09:04',314,'2022-02-10 12:55:41','',NULL,NULL,850.47),(219,'00000219',87.00,'2015-02-10 00:09:04',194,'2022-02-10 13:03:18','',NULL,NULL,850.47),(220,'00000220',206.00,'2016-02-10 13:06:03',118,'2022-02-10 13:06:03','',NULL,NULL,850.47),(221,'00000221',72.00,'2015-02-10 00:09:04',316,'2022-02-10 13:07:36','',NULL,NULL,150),(222,'00000222',154.00,'2015-02-10 00:09:04',118,'2022-02-10 13:24:59','',NULL,NULL,150),(223,'00000223',90.00,'2017-02-10 13:06:03',173,'2022-02-10 13:29:29','',NULL,NULL,150),(224,'00000224',57.00,'2017-02-10 13:06:03',118,'2022-02-10 13:51:52','',NULL,NULL,150),(225,'00000225',113.00,'2017-02-10 13:06:03',156,'2022-02-10 14:23:58','',NULL,NULL,48.485),(226,'00000226',49.00,'2018-02-10 13:06:03',320,'2022-02-10 14:32:23','',NULL,NULL,850.47),(227,'00000227',154.00,'2017-02-10 13:06:03',318,'2022-02-10 14:39:38','',NULL,NULL,850.47),(228,'00000228',135.00,'2019-02-10 13:06:03',321,'2022-02-10 15:04:23','',NULL,NULL,850.47),(229,'00000229',154.00,'2019-02-10 13:06:03',322,'2022-02-10 15:55:59','',NULL,NULL,850.47),(230,'00000230',120.00,'2019-02-10 13:06:03',103,'2022-02-10 16:11:55','',NULL,NULL,850.47),(231,'00000231',128.00,'2020-02-10 16:15:29',323,'2022-02-10 16:15:29','',NULL,NULL,850.47),(232,'00000232',206.00,'2019-02-10 13:06:03',179,'2022-02-10 16:25:18','',NULL,NULL,850.47),(233,'00000233',206.00,'2019-02-10 13:06:03',179,'2022-02-10 16:27:21','',NULL,NULL,45.15),(234,'00000234',68.00,'2020-02-10 16:15:29',160,'2022-02-10 16:37:58','',NULL,NULL,NULL),(235,'00000235',64.00,'2021-02-10 16:40:04',324,'2022-02-10 16:40:04','',NULL,NULL,475.45),(236,'00000236',38.00,'2022-02-10 16:49:38',326,'2022-02-10 16:49:38','',NULL,NULL,95.58),(237,'00000237',139.00,'2022-02-10 17:29:04',64,'2022-02-10 17:29:04','',NULL,NULL,95.58);
/*!40000 ALTER TABLE `orders` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-11-02 17:53:09





/** year wise data as count */
select
	year(date_purchased) as year,
	count(*) as total
from
	orders
group by
	date_purchased order by date_purchased  asc;


/** year wise average, total data as count */
select
	year(date_purchased) as year,
	count(*) as Nos,
	sum(order_price) as total,
	avg(order_price) as average
from
	orders
group by
	date_purchased order by date_purchased  asc;


/** year, month wise average, total data as count */
select
	concat(year(date_purchased), '-', monthname(date_purchased)) as ym,  
	count(*) as Nos,
	sum(order_price) as total,
	avg(order_price) as average
from
	orders
group by
	ym;


/** year, month wise average, total data as count by m1, m2 splitting with rounding */
select
	concat(year(date_purchased), '-', monthname(date_purchased),'-',if(day(date_purchased)<=15,'m1','m2')) as ym,  
	count(*) as Nos,
	round(sum(order_price),2) as total,
	round(avg(order_price),1) as average
from
	orders
group by
	ym;


/** year, month wise average, total data as count  fiscal year . Calculation start from year in every month splitting with rounding and  */
select
	case
		when month(date_purchased) >= 4
	then concat(year(date_purchased) , '-', year(date_purchased)+ 1)
		else concat(year(date_purchased)-1 , '-', year(date_purchased))
	end as financial_year,
	count(*) as Nos,
	round(sum(order_price), 2) as total,
	round(avg(order_price), 1) as average
from
	orders
group by
	financial_year;


/** year, month wise average, total data as count  fiscal year . Calculation start from april in every year splitting with rounding and  */
/** date format YYYY-mm-dd - Y*/
select
	case
		when month(date_purchased) >= 4
	then concat(year(date_purchased) , '-', date_format(date_purchased, '%y')+ 1)
		else concat(year(date_purchased)-1 , '-', date_format(date_purchased, '%y'))
	end as financial_year,
	count(*) as Nos,
	round(sum(order_price), 2) as total,
	round(avg(order_price), 1) as average
from
	orders
group by
	financial_year;