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 */;

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;

-- 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 */;

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