deyanmalinov

07-mysql

Sep 20th, 2020
1,410
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE `employees` (
  2. `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  3. `first_name` VARCHAR(45) NOT NULL,
  4. `last_name` VARCHAR(45) NOT NULL,
  5. `title` VARCHAR(45) NOT NULL,
  6. `notes` VARCHAR(45) NULL);
  7.  
  8. CREATE TABLE `customers` (
  9. `account_number` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  10. `first_name` VARCHAR(45) NOT NULL,
  11. `last_name` VARCHAR(45) NOT NULL,
  12. `phone_number` VARCHAR(45) NOT NULL,
  13. `emergency_name` VARCHAR(45) NOT NULL,
  14. `emergency_number` INT NOT NULL,
  15. `notes` VARCHAR(45) NULL);
  16.  
  17. CREATE TABLE `room_status` (
  18. `room_status` TINYINT PRIMARY KEY NOT NULL,
  19. `notes` VARCHAR(45) NULL);
  20.  
  21. CREATE TABLE `room_types` (
  22. `room_type` VARCHAR(20) PRIMARY KEY NOT NULL,
  23. `notes` VARCHAR(45) NULL);
  24.  
  25. CREATE TABLE `bed_types` (
  26. `bed_type` VARCHAR(20) PRIMARY KEY NOT NULL,
  27. `notes` VARCHAR(45) NULL);
  28.  
  29. CREATE TABLE `rooms` (
  30. `room_number` INT PRIMARY KEY NOT NULL,
  31. `room_type` VARCHAR(45) NOT NULL,
  32. `bed_type` VARCHAR(45) NOT NULL,
  33. `rate` DECIMAL NOT NULL,
  34. `room_status` TINYINT NOT NULL,
  35. `note` VARCHAR(45) NULL);
  36.  
  37. CREATE TABLE `payments` (
  38. `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  39. `employee_id` INT NOT NULL,
  40. `payment_date` DATE NOT NULL,
  41. `account_number` INT NOT NULL,
  42. `first_date_occupied` DATE NOT NULL,
  43. `last_date_occupied` DATE NOT NULL,
  44. `total_days` INT NOT NULL,
  45. `amount_charged` DECIMAL NOT NULL,
  46. `tax_rate` DECIMAL NOT NULL,
  47. `tax_amount` DECIMAL NOT NULL,
  48. `payment_total` DECIMAL NOT NULL,
  49. `notes` VARCHAR(45) NULL);
  50.  
  51. CREATE TABLE `occupancies` (
  52. `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  53. `employee_id` INT NOT NULL,
  54. `date_occupied` DATE NOT NULL,
  55. `account_number` INT NOT NULL,
  56. `room_number` INT NOT NULL,
  57. `rate_applied` DECIMAL NOT NULL,
  58. `phone_charge` DECIMAL NOT NULL,
  59. `notes` VARCHAR(45) NULL);
  60.  
  61. INSERT INTO `employees` (`id`, `first_name`, `last_name`, `title`) VALUES
  62. ('1', 'ab', 'ab', 'ab'),
  63. ('2', 'ab', 'ab', 'ab'),
  64. ('3', 'ab', 'ab', 'ab');
  65.  
  66. INSERT INTO `customers` (`account_number`, `first_name`, `last_name`, `phone_number`, `emergency_name`, `emergency_number`) VALUES
  67. ('1', 'ab', 'ab', 'ab', 'ab', '12'),
  68. ('2', 'ab', 'ab', 'ab', 'ab', '12'),
  69. ('3', 'ab', 'ab', 'ab', 'ab', '12');
  70.  
  71. INSERT INTO `room_status` (`room_status`) VALUES
  72. ('1'),
  73. ('0');
  74.  
  75. INSERT INTO `room_types` (`room_type`) VALUES
  76. ('ab'),
  77. ('bc');
  78.  
  79. INSERT INTO `bed_types` (`bed_type`, `notes`) VALUES
  80. ('ab', 'ab'),
  81. ('bc', 'ab');
  82.  
  83. INSERT INTO `rooms` (`room_number`, `room_type`, `bed_type`, `rate`, `room_status`) VALUES
  84. ('1', 'ab', 'ab', '1.2', '1'),
  85. ('2', 'ab', 'ab', '1.2', '1'),
  86. ('3', 'ab', 'ab', '1.2', '1');
  87.  
  88. INSERT INTO `payments` (`id`, `employee_id`, `payment_date`, `account_number`, `first_date_occupied`, `last_date_occupied`, `total_days`, `amount_charged`, `tax_rate`, `tax_amount`, `payment_total`) VALUES
  89. ('1', '12', '1999-1-1', '12', '1999-1-1', '1999-1-1', '12', '1.2', '1.2', '1.2', '1.2'),
  90. ('2', '12', '1999-1-1', '12', '1999-1-1', '1999-1-1', '12', '1.2', '1.2', '1.2', '1.2'),
  91. ('3', '12', '1999-1-1', '12', '1999-1-1', '1999-1-1', '12', '1.2', '1.2', '1.2', '1.2');
  92.  
  93. INSERT INTO `occupancies` (`id`, `employee_id`, `date_occupied`, `account_number`, `room_number`, `rate_applied`, `phone_charge`) VALUES
  94. ('1', '12', '1999-1-1', '12', '12', '1.2', '1.2'),
  95. ('2', '13', '1999-1-1', '12', '12', '1.2', '1.2'),
  96. ('3', '14', '1999-1-1', '12', '12', '1.2', '1.2');
RAW Paste Data