deyanmalinov

06-mysql

Sep 20th, 2020
987
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE `categories` (
  2. `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  3. `category` VARCHAR(45) NOT NULL,
  4. `daily_rate` DECIMAL NOT NULL,
  5. `weekly_rate` DECIMAL NOT NULL,
  6. `monthly_rate` DECIMAL NOT NULL,
  7. `weekend_rate` DECIMAL NOT NULL
  8. );
  9.  
  10. CREATE TABLE `cars` (
  11. `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  12. `plate_number` VARCHAR(45) NOT NULL,
  13. `make, model` VARCHAR(45) NOT NULL,
  14. `car_year` INT NOT NULL,
  15. `category_id` INT NOT NULL,
  16. `doors, picture` BLOB NULL,
  17. `car_condition` VARCHAR(45) NOT NULL,
  18. `available` TINYINT NOT NULL
  19. );
  20.  
  21. CREATE TABLE `employees` (
  22. `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  23. `first_name` VARCHAR(45) NOT NULL,
  24. `last_name` VARCHAR(45) NOT NULL,
  25. `title` VARCHAR(45) NOT NULL,
  26. `notes` VARCHAR(45) NULL
  27. );
  28.  
  29. CREATE TABLE `customers` (
  30. `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  31. `driver_licence_number` VARCHAR(45) NOT NULL,
  32. `full_name` VARCHAR(45) NOT NULL,
  33. `address` VARCHAR(45) NOT NULL,
  34. `city` VARCHAR(45) NOT NULL,
  35. `zip_code` INT NOT NULL,
  36. `notes` VARCHAR(200) NULL
  37. );
  38.  
  39. CREATE TABLE `rental_orders` (
  40. `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  41. `employee_id` INT NOT NULL,
  42. `customer_id` INT NOT NULL,
  43. `car_id` INT NOT NULL,
  44. `car_condition` VARCHAR(45) NOT NULL,
  45. `tank_level` VARCHAR(45) NOT NULL,
  46. `kilometrage_start` INT NOT NULL,
  47. `kilometrage_end` INT NOT NULL,
  48. `total_kilometrage` INT NOT NULL,
  49. `start_date` DATE NOT NULL,
  50. `end_date` DATE NOT NULL,
  51. `total_days` INT NOT NULL,
  52. `rate_applied` DOUBLE NOT NULL,
  53. `tax_rate` DOUBLE NOT NULL,
  54. `order_status` TINYINT NOT NULL,
  55. `notes` VARCHAR(200) NULL
  56. );
  57.  
  58. INSERT INTO `categories` (`id`, `category`, `daily_rate`, `weekly_rate`, `monthly_rate`, `weekend_rate`) VALUES
  59. ('1', 'text', '105.2', '105.2', '105.2', '105.2'),
  60. ('2', 'text', '105.2', '105.2', '105.2', '105.2'),
  61. ('3', 'text', '105.2', '105.2', '105.2', '105.2');
  62.  
  63. INSERT INTO `cars` (`id`, `plate_number`, `make, model`, `car_year`, `category_id`, `car_condition`, `available`) VALUES
  64. ('1', 'text', 'text', '1980', '1980', 'ok', '1'),
  65. ('2', 'text', 'text', '1980', '1980', 'ok', '1'),
  66. ('3', 'text', 'text', '1980', '1980', 'ok', '1');
  67.  
  68. INSERT INTO `employees` (`id`, `first_name`, `last_name`, `title`, `notes`) VALUES
  69. ('1', 'text', 'text', 'text', 'text'),
  70. ('2', 'text', 'text', 'text', 'text'),
  71. ('3', 'text', 'text', 'text', 'text');
  72.  
  73. INSERT INTO `customers` (`id`, `driver_licence_number`, `full_name`, `address`, `city`, `zip_code`, `notes`) VALUES
  74. ('1', 'text', 'text', 'text', 'text', '123', 'text'),
  75. ('2', 'text', 'text', 'text', 'text', '123', 'text'),
  76. ('3', 'text', 'text', 'text', 'text', '123', 'text');
  77.  
  78. INSERT INTO `rental_orders` (`id`, `employee_id`, `customer_id`, `car_id`, `car_condition`, `tank_level`, `kilometrage_start`, `kilometrage_end`, `total_kilometrage`, `start_date`, `end_date`, `total_days`, `rate_applied`, `tax_rate`, `order_status`, `notes`) VALUES
  79. ('1', '123', '123', '123', 'text', 'text', '123', '123', '123', '1980-10-10', '1980-10-10', '123', '123.3', '123.3', '1', 'text'),
  80. ('2', '123', '123', '123', 'text', 'text', '123', '123', '123', '1980-10-10', '1980-10-10', '123', '123.3', '123.3', '1', 'text'),
  81. ('3', '123', '123', '123', 'text', 'text', '123', '123', '123', '1980-10-10', '1980-10-10', '123', '123.3', '123.3', '1', 'text');
  82.  
RAW Paste Data