Warehouse DB

Warehouse DB data.sql

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172
-- phpMyAdmin SQL Dump -- version 5.0.1 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: Mar 03, 2020 at 05:29 AM -- Server version: 10.4.11-MariaDB -- PHP Version: 7.4.2 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */; /*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */; /*!40101 SET @[email protected]@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `warehouse` -- -- -- Dumping data for table `category` -- INSERT INTO `category` (`id`, `name`, `description`, `isDeleted`) VALUES (1, 'Điện thoại', 0, b'0'), (2, 'Laptop', 0, b'0'); -- -- Dumping data for table `customer` -- INSERT INTO `customer` (`id`, `firstName`, `lastName`, `isBanned`) VALUES (1, 'Giàu', 'Trần Văn', b'00000000000'), (2, 'Khoa', 'Võ Viẹt', b'00000000000'); -- -- Dumping data for table `order` -- INSERT INTO `order` (`id`, `customerId`, `note`, `discount`, `createdAt`, `status`) VALUES (1, 1, '', 0, 1583209665, 1), (2, 2, '', 0, 1583209789, 1), (3, 2, '', 0, 1583212345, 1); -- -- Dumping data for table `product` -- INSERT INTO `product` (`id`, `name`, `description`, `price`, `categoryId`) VALUES (1, 'Dell XPS 15 inch', '', 36000000, 2), (2, 'iPhone 7 plus', '', 14000000, 1); -- -- Dumping data for table `orderitem` -- INSERT INTO `orderitem` (`id`, `orderId`, `productId`, `price`) VALUES (1, 1, 1, 36000000), (2, 1, 2, 13000000); COMMIT; /*!40101 SET [email protected]_CHARACTER_SET_CLIENT */; /*!40101 SET [email protected]_CHARACTER_SET_RESULTS */; /*!40101 SET [email protected]_COLLATION_CONNECTION */;

Warehouse DB structure.sql

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191
-- phpMyAdmin SQL Dump -- version 5.0.1 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: Mar 03, 2020 at 05:22 AM -- Server version: 10.4.11-MariaDB -- PHP Version: 7.4.2 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */; /*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */; /*!40101 SET @[email protected]@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `warehouse` -- -- -------------------------------------------------------- -- -- Table structure for table `category` -- CREATE TABLE `category` ( `id` int(11) NOT NULL, `name` text NOT NULL, `description` int(11) NOT NULL, `isDeleted` bit(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -------------------------------------------------------- -- -- Table structure for table `customer` -- CREATE TABLE `customer` ( `id` int(11) NOT NULL, `firstName` text NOT NULL, `lastName` text NOT NULL, `isBanned` bit(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -------------------------------------------------------- -- -- Table structure for table `order` -- CREATE TABLE `order` ( `id` int(11) NOT NULL, `customerId` int(11) NOT NULL, `note` text NOT NULL, `discount` int(11) NOT NULL, `createdAt` bigint(20) NOT NULL, `status` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -------------------------------------------------------- -- -- Table structure for table `orderitem` -- CREATE TABLE `orderitem` ( `id` int(11) NOT NULL, `orderId` int(11) NOT NULL, `productId` int(11) NOT NULL, `price` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -------------------------------------------------------- -- -- Table structure for table `product` -- CREATE TABLE `product` ( `id` int(11) NOT NULL, `name` text NOT NULL, `description` text NOT NULL, `price` int(11) NOT NULL, `categoryId` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Indexes for dumped tables -- -- -- Indexes for table `category` -- ALTER TABLE `category` ADD PRIMARY KEY (`id`); -- -- Indexes for table `customer` -- ALTER TABLE `customer` ADD PRIMARY KEY (`id`); -- -- Indexes for table `order` -- ALTER TABLE `order` ADD PRIMARY KEY (`id`), ADD KEY `customerId` (`customerId`); -- -- Indexes for table `orderitem` -- ALTER TABLE `orderitem` ADD PRIMARY KEY (`id`), ADD KEY `productId` (`productId`), ADD KEY `orderId` (`orderId`); -- -- Indexes for table `product` -- ALTER TABLE `product` ADD PRIMARY KEY (`id`), ADD KEY `categoryId` (`categoryId`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `category` -- ALTER TABLE `category` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `customer` -- ALTER TABLE `customer` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `order` -- ALTER TABLE `order` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `orderitem` -- ALTER TABLE `orderitem` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `product` -- ALTER TABLE `product` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- Constraints for dumped tables -- -- -- Constraints for table `order` -- ALTER TABLE `order` ADD CONSTRAINT `order_ibfk_1` FOREIGN KEY (`customerId`) REFERENCES `customer` (`id`); -- -- Constraints for table `orderitem` -- ALTER TABLE `orderitem` ADD CONSTRAINT `orderitem_ibfk_1` FOREIGN KEY (`productId`) REFERENCES `product` (`id`), ADD CONSTRAINT `orderitem_ibfk_2` FOREIGN KEY (`orderId`) REFERENCES `order` (`id`); -- -- Constraints for table `product` -- ALTER TABLE `product` ADD CONSTRAINT `product_ibfk_1` FOREIGN KEY (`categoryId`) REFERENCES `category` (`id`); COMMIT; /*!40101 SET [email protected]_CHARACTER_SET_CLIENT */; /*!40101 SET [email protected]_CHARACTER_SET_RESULTS */; /*!40101 SET [email protected]_COLLATION_CONNECTION */;

Sub-query

1
SELECT newTable.categoryId, MAX(newTable.sumOfPrice) FROM (SELECT SUM(price) as sumOfPrice, categoryId FROM `product` WHERE 1 GROUP BY categoryId) AS newTable