API tools faq
paste
Advertisement
Guest User

SQL

a guest
May 1st, 2018
216
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 6.25 KB | None | 0 0
raw download clone embed print report
  1. CREATE TABLE `users` (
  2.     `id` INT(10) NOT NULL AUTO_INCREMENT UNIQUE,
  3.     `name` VARCHAR(16) NOT NULL,
  4.     `password` VARCHAR(15) NOT NULL,
  5.     `email` VARCHAR(255) NOT NULL UNIQUE,
  6.     `balance` DECIMAL(10,2),
  7.     `created_at` TIMESTAMP NOT NULL,
  8.     PRIMARY KEY (`id`)
  9. );
  10.  
  11. CREATE TABLE `user_deposits` (
  12.     `id` INT(10) NOT NULL AUTO_INCREMENT,
  13.     `user_id` INT NOT NULL,
  14.     `value` DECIMAL(10,2) NOT NULL,
  15.     `created_at` DATETIME NOT NULL,
  16.     PRIMARY KEY (`id`)
  17. );
  18.  
  19. CREATE TABLE `withdrawals` (
  20.     `id` INT(10) NOT NULL AUTO_INCREMENT,
  21.     `user_id` INT NOT NULL,
  22.     `value` DECIMAL(10,2) NOT NULL,
  23.     `created_at` DATETIME NOT NULL,
  24.     PRIMARY KEY (`id`)
  25. );
  26.  
  27. CREATE TABLE `transfers` (
  28.     `id` INT(10) NOT NULL AUTO_INCREMENT,
  29.     `value` DECIMAL(10,2) NOT NULL,
  30.     `created_at` TIMESTAMP NOT NULL,
  31.     `user_made` INT NOT NULL,
  32.     `user_receive` INT NOT NULL,
  33.     PRIMARY KEY (`id`)
  34. );
  35.  
  36. ALTER TABLE `user_deposits` ADD CONSTRAINT `user_deposits_fk0` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`);
  37.  
  38. ALTER TABLE `withdrawals` ADD CONSTRAINT `withdrawals_fk0` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`);
  39.  
  40. ALTER TABLE `transfers` ADD CONSTRAINT `transfers_fk0` FOREIGN KEY (`user_made`) REFERENCES `users`(`id`);
  41.  
  42. ALTER TABLE `transfers` ADD CONSTRAINT `transfers_fk1` FOREIGN KEY (`user_receive`) REFERENCES `users`(`id`);
  43.  
  44.  
  45. INSERT INTO users (name, password, email, balance, created_at) VALUES ('Rodrigo', 123456, 'rods.eduardo@gmail.com', 500.00, 01-05-2018);
  46. INSERT INTO users (name, password, email, balance, created_at) VALUES ('Eduardo', 123456, 'eduardo@gmail.com', 400.00, 02-05-2018);
  47. INSERT INTO users (name, password, email, balance, created_at) VALUES ('Mendes', 123456, 'mendes@gmail.com', 300.00, 03-05-2018);
  48. INSERT INTO users (name, password, email, balance, created_at) VALUES ('Paulo', 123456, 'paulo@gmail.com', 200.00, 04-05-2018);
  49. INSERT INTO users (name, password, email, balance, created_at) VALUES ('Sergio', 123456, 'sergio@gmail.com', 100.00, 05-05-2018);
  50.  
  51. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (1, 200.00, 01-04-2018);
  52. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (1, 150.00, 01-04-2018);
  53. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (1, 180.00, 01-04-2018);
  54. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (1, 210.00, 01-04-2018);
  55. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (2, 179.00, 02-04-2018);
  56. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (2, 321.00, 02-04-2018);
  57. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (2, 111.00, 02-04-2018);
  58. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (2, 34.00, 02-04-2018);
  59. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (3, 345.00, 03-04-2018);
  60. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (3, 98.00, 03-04-2018);
  61. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (3, 165.00, 03-04-2018);
  62. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (3, 76.00, 03-04-2018);
  63. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (4, 12.00, 04-04-2018);
  64. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (4, 34.00, 04-04-2018);
  65. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (4, 54.00, 04-04-2018);
  66. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (4, 66.00, 04-04-2018);
  67. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (5, 34.00, 05-04-2018);
  68. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (5, 52.00, 05-04-2018);
  69. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (5, 65.00, 05-04-2018);
  70. INSERT INTO user_deposits (user_id, VALUE, created_at) VALUES (5, 45.00, 05-04-2018);
  71.  
  72. INSERT INTO withdrawals (user_id, VALUE, created_at) VALUES (1, 100.00, 05-04-2018);
  73. INSERT INTO withdrawals (user_id, VALUE, created_at) VALUES (1, 145.00, 05-04-2018);
  74. INSERT INTO withdrawals (user_id, VALUE, created_at) VALUES (2, 245.00, 05-04-2018);
  75. INSERT INTO withdrawals (user_id, VALUE, created_at) VALUES (2, 125.00, 05-04-2018);
  76. INSERT INTO withdrawals (user_id, VALUE, created_at) VALUES (3, 135.00, 05-04-2018);
  77. INSERT INTO withdrawals (user_id, VALUE, created_at) VALUES (3, 145.00, 05-04-2018);
  78. INSERT INTO withdrawals (user_id, VALUE, created_at) VALUES (4, 175.00, 05-04-2018);
  79. INSERT INTO withdrawals (user_id, VALUE, created_at) VALUES (4, 125.00, 05-04-2018);
  80. INSERT INTO withdrawals (user_id, VALUE, created_at) VALUES (5, 76.00, 05-04-2018);
  81. INSERT INTO withdrawals (user_id, VALUE, created_at) VALUES (5, 87.00, 05-04-2018);
  82.  
  83. INSERT INTO transfers (VALUE, created_at, user_made, user_receive) VALUES (87.00, 04-04-2018, 1, 2);
  84. INSERT INTO transfers (VALUE, created_at, user_made, user_receive) VALUES (88.00, 03-04-2018, 1, 3);
  85. INSERT INTO transfers (VALUE, created_at, user_made, user_receive) VALUES (89.00, 03-04-2018, 2, 1);
  86. INSERT INTO transfers (VALUE, created_at, user_made, user_receive) VALUES (82.00, 04-04-2018, 2, 4);
  87. INSERT INTO transfers (VALUE, created_at, user_made, user_receive) VALUES (83.00, 03-04-2018, 3, 5);
  88. INSERT INTO transfers (VALUE, created_at, user_made, user_receive) VALUES (77.00, 04-04-2018, 3, 4);
  89. INSERT INTO transfers (VALUE, created_at, user_made, user_receive) VALUES (67.00, 05-04-2018, 4, 1);
  90. INSERT INTO transfers (VALUE, created_at, user_made, user_receive) VALUES (87.00, 05-04-2018, 4, 2);
  91. INSERT INTO transfers (VALUE, created_at, user_made, user_receive) VALUES (47.00, 05-04-2018, 5, 3);
  92. INSERT INTO transfers (VALUE, created_at, user_made, user_receive) VALUES (23.00, 05-04-2018, 5, 2);
  93.  
  94.  
  95. UPDATE users SET users.balance = balance + (SELECT COUNT(balance) FROM user_deposits WHERE id = 1) WHERE id = 1;
  96. UPDATE users SET users.balance = balance + (SELECT COUNT(balance) FROM withdrawals WHERE id = 1) WHERE id = 1;
  97. UPDATE users SET users.balance = balance + (SELECT COUNT(balance) FROM transfers WHERE id = 1) WHERE id = 1;
  98.  
  99. (SELECT COUNT(balance) FROM user_deposits WHERE id = 1 + SELECT COUNT(balance) FROM withdrawals WHERE id = 1 + SELECT COUNT(balance) FROM transfers WHERE id = 1) = SELECT balance FROM users WHERE id = 1;
  100.  
  101. SELECT MAX(VALUE) AS BiggerValueD FROM user_deposits;
  102. SELECT MAX(VALUE) AS BiggerValueW FROM withdrawals;
  103. SELECT MAX(VALUE) AS BiggerValueT FROM transfers;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!