Skip to content

Instantly share code, notes, and snippets.

@Dharisd

Dharisd/bike_company.sql

Last active May 15, 2022 17:58
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Dharisd/bae6388be2193d2157b74f8876c16c82 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Dharisd/bae6388be2193d2157b74f8876c16c82 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
bike_company.sql
-- phpMyAdmin SQL Dump
-- version 5.1.3
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: May 15, 2022 at 07:36 PM
-- Server version: 10.4.24-MariaDB
-- PHP Version: 7.4.29
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `bikecompany`
--
-- --------------------------------------------------------
--
-- Table structure for table `bike`
--
CREATE TABLE `bike` (
`bike_id` int(11) NOT NULL,
`owner_id` varchar(10) DEFAULT NULL,
`customer_id` varchar(10) DEFAULT NULL,
`model` varchar(20) DEFAULT NULL,
`inspector_id` int(11) DEFAULT NULL,
`returned` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `bike`
--
INSERT INTO `bike` (`bike_id`, `owner_id`, `customer_id`, `model`, `inspector_id`, `returned`) VALUES
(1, 'a275674', 'a2756768', 'mx150', 20, 1),
(2, 'a275675', 'a2756763', 'airblade', 21, 1),
(3, 'a275676', 'a2756767', 'scoopy', 22, 1),
(4, 'a275677', 'a2756762', 'jupiter', 23, 1),
(5, 'a275678', 'a2756760', 'fina', 24, 1);
-- --------------------------------------------------------
--
-- Table structure for table `customer`
--
CREATE TABLE `customer` (
`customer_id` varchar(10) NOT NULL,
`customer_name` varchar(25) DEFAULT NULL,
`damage` int(11) DEFAULT NULL,
`paid` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `customer`
--
INSERT INTO `customer` (`customer_id`, `customer_name`, `damage`, `paid`) VALUES
('a2756760', 'ismail', 0, 1),
('a2756762', 'adam', 100, 0),
('a2756763', 'ahmed', 0, 1),
('a2756767', 'ali', 0, 1),
('a2756768', 'mohamed', 0, 1);
-- --------------------------------------------------------
--
-- Table structure for table `inspector`
--
CREATE TABLE `inspector` (
`inspector_id` int(11) NOT NULL,
`inspector_name` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `inspector`
--
INSERT INTO `inspector` (`inspector_id`, `inspector_name`) VALUES
(20, 'jan'),
(21, 'jenny'),
(22, 'tim'),
(23, 'alfred'),
(24, 'jim');
-- --------------------------------------------------------
--
-- Table structure for table `owner`
--
CREATE TABLE `owner` (
`owner_id` varchar(10) NOT NULL,
`owner_name` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `owner`
--
INSERT INTO `owner` (`owner_id`, `owner_name`) VALUES
('a275674', 'moahamed'),
('a275675', 'idhrees'),
('a275676', 'ilyas'),
('a275677', 'ilham'),
('a275678', 'igdhaan');
-- --------------------------------------------------------
--
-- Table structure for table `price`
--
CREATE TABLE `price` (
`bike_id` int(11) DEFAULT NULL,
`customer_pay` double DEFAULT NULL,
`owner_pay` double DEFAULT NULL,
`total_earn` double DEFAULT NULL,
`final_profit` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `price`
--
INSERT INTO `price` (`bike_id`, `customer_pay`, `owner_pay`, `total_earn`, `final_profit`) VALUES
(1, 3000, 2250, 3000, 750),
(2, 1000, 750, 1000, 250),
(3, 1600, 1200, 1600, 400),
(4, 1600, 11257, 1500, 3847),
(5, 3000, 2250, 3000, 750);
-- --------------------------------------------------------
--
-- Table structure for table `rent`
--
CREATE TABLE `rent` (
`bike_id` int(11) DEFAULT NULL,
`customer_id` varchar(10) DEFAULT NULL,
`rented_from` date DEFAULT NULL,
`rented_days` int(11) DEFAULT NULL,
`rent_price` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `rent`
--
INSERT INTO `rent` (`bike_id`, `customer_id`, `rented_from`, `rented_days`, `rent_price`) VALUES
(1, 'a2756768', '2020-01-01', 30, 3000),
(2, 'a2756763', '2020-02-01', 30, 500),
(3, 'a2756767', '2020-03-01', 30, 400),
(4, 'a2756762', '2020-04-01', 30, 3000),
(5, 'a2756760', '2020-05-01', 30, 567);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `bike`
--
ALTER TABLE `bike`
ADD PRIMARY KEY (`bike_id`);
--
-- Indexes for table `customer`
--
ALTER TABLE `customer`
ADD PRIMARY KEY (`customer_id`);
--
-- Indexes for table `inspector`
--
ALTER TABLE `inspector`
ADD PRIMARY KEY (`inspector_id`);
--
-- Indexes for table `owner`
--
ALTER TABLE `owner`
ADD PRIMARY KEY (`owner_id`);
COMMIT;
/*!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 */;
Raw
db.md

List of new customers since the beginning of this year with the total amount they have spent

SELECT customer.customer_name, rent.rent_price FROM `rent` INNER JOIN customer ON rent.customer_id=customer.customer_id where rent.rented_from > 2020;

Total amount spent on the bikes of each brand by the customers

SELECT bike.model, SUM(rent.rent_price) FROM `rent` INNER JOIN bike ON rent.bike_id=bike.bike_id GROUP By bike.model;

Total amount spent on any given bike for repairs

SELECT customer.damage FROM `bike` INNER JOIN customer ON bike.customer_id=customer.customer_id where bike_id=1;

Income generated by the by the company

SELECT SUM(final_profit) from price;

Top 5 customers who rented most

SELECT customer.customer_name, rent.rent_price FROM `rent` INNER JOIN customer ON rent.customer_id=customer.customer_id ORDER BY rent_price DESC LIMIT 5;

The brand on which each customer made the most payments to

SELECT bike.model, rent.customer_id, SUM(rent.rent_price) AS `amount` FROM rent INNER JOIN bike ON rent.bike_id=bike.bike_id GROUP BY rent.customer_id ORDER BY `amount` DESC;

Total amount paid to each owner

SELECT bike.owner_id, SUM(price.owner_pay) FROM price INNER JOIN bike ON price.bike_id=bike.bike_id GROUP BY bike.bike_id;

Number of customers rented for each bike

SELECT bike_id, COUNT(customer_id) FROM rent GROUP BY customer_id;

Get the time of the year when the most number of renting Occur

SELECT rented_from, COUNT(rented_from) AS `value_occurrence` FROM rent GROUP BY rented_from ORDER BY `value_occurrence` DESC LIMIT 1;

@Dharisd
Copy link
Author

Dharisd commented May 15, 2022 •
edited
Loading

SELECT customer.customer_name, SUM(rent.rent_price) FROM rent
INNER JOIN customer ON rent.customer_id=customer.customer_id
WHERE rent.rented_from > 2020
GROUP BY customer.customer_id

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment