API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 24th, 2017
104
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 15.36 KB | None | 0 0
raw download clone embed print report
  1. /*password 12345
  2. show databases;
  3. source D:\jack.sql
  4. */
  5. drop database IF EXISTS jack;
  6.  
  7. create database jack;
  8.  
  9. create table jack.houses
  10. (
  11. id INT NOT NULL AUTO_INCREMENT,
  12. house_number VARCHAR(255) NULL,
  13. street VARCHAR(255) NULL,
  14. build_date DATE NULL,
  15.  
  16. current_sale_amount Decimal(19,2) NULL,
  17. current_rent_month_amount Decimal(10,2) NULL,
  18. current_rent_day_amount Decimal(10,2) NULL,
  19.  
  20. status VARCHAR(30) NOT NULL,
  21.  
  22. PRIMARY KEY (id)
  23. );
  24.  
  25. create table jack.construction_payments
  26. (
  27. id INT NOT NULL AUTO_INCREMENT,
  28. house_id INT NOT NULL,
  29. amount Decimal(19,2) NOT NULL,
  30. payment_comment VARCHAR(255) NULL,
  31.  
  32. PRIMARY KEY (id),
  33. FOREIGN KEY (house_id) references jack.houses (id)
  34. );
  35.  
  36. create table jack.renters
  37. (
  38. id INT NOT NULL AUTO_INCREMENT,
  39. name VARCHAR(255),
  40.  
  41. PRIMARY KEY (id)
  42. );
  43.  
  44. create table jack.rents
  45. (
  46. id INT NOT NULL AUTO_INCREMENT,
  47. house_id INT NOT NULL,
  48. renter_id INT NOT NULL,
  49. amount Decimal(19,2) NOT NULL,
  50. payment_date DATE NOT NULL,
  51. rent_from DATE NOT NULL,
  52. rent_to DATE NOT NULL,
  53. is_payed BIT default 0,
  54.  
  55. PRIMARY KEY (id),
  56. FOREIGN KEY (house_id) references jack.houses (id),
  57. FOREIGN KEY (renter_id) references jack.renters (id)
  58. );
  59.  
  60. /*data*/
  61. INSERT INTO jack.houses (house_number, street, status, build_date, current_sale_amount)
  62. VALUES
  63. (1, "Бейкер стрит", "for sale", "1999/01/01", 10000000),
  64. (2, "Бейкер стрит", "for rent day", "2001/03/06", null),
  65. ("221B", "Бейкер стрит", "for rent month", "2004/07/14", null),
  66. ("L1№4", "Малхолланд Драйв", "for rent month", "1993/02/15", null),
  67. (2, "Малхолланд Драйв", "for rent month", "1999/01/01", null),
  68. (4, "Тисовая улица", "sold", "1980/07/31", 50000000),
  69. (5, "Косой переулок", "for rent month", "2000/03/20", null),
  70. (1, "Улица Вязов", "for rent day", "1984/01/01", null),
  71. (2, "Улица Вязов", "for rent day", "1985/01/01", null),
  72. (3, "Улица Вязов", "for rent day", "1987/01/01", null),
  73. (10, "Улица Вязов", "under construction", null, null);
  74.  
  75. INSERT INTO jack.construction_payments (house_id, amount, payment_comment)
  76. VALUES
  77. (1, 1000000, "покупка земли"),
  78. (2, 2000000, "покупка земли"),
  79. (3, 1000000, "покупка земли"),
  80. (4, 1000000, "покупка земли"),
  81. (5, 1000000, "покупка земли"),
  82. (6, 1000000, "покупка земли"),
  83. (7, 1000000, "покупка земли"),
  84. (1, 5000000, "материалы"),
  85. (2, 4000000, "материалы"),
  86. (3, 5000000, "материалы"),
  87. (4, 4000000, "материалы"),
  88. (5, 4000000, "материалы"),
  89. (6, 3000000, "материалы"),
  90. (7, 3000000, "материалы"),
  91. (1, 5000, "зп строителей"),
  92. (2, 4000, "зп строителей"),
  93. (3, 5000, "зп строителей"),
  94. (4, 4000, "зп строителей"),
  95. (5, 4000, "зп строителей"),
  96. (6, 3000, "зп строителей"),
  97. (7, 3000, "зп строителей"),
  98. (1, 50000, "евроремонт"),
  99. (2, 1000000, "взятки"),
  100. (3, 50000, "евроремонт"),
  101. (4, 1000000, "взятки"),
  102. (5, 40000, "евроремонт"),
  103. (6, 30000, "евроремонт"),
  104. (7, 1000000, "взятки");
  105.  
  106. INSERT INTO jack.renters (name)
  107. VALUES
  108. ("Шерлок Х."),
  109. ("Доктор В."),
  110. ("Девид Л."),
  111. ("Гарри П."),
  112. ("Фредди К.");
  113.  
  114. INSERT INTO jack.rents (house_id, renter_id, amount, payment_date, is_payed, rent_from, rent_to)
  115. VALUES
  116. (2, 2, 800, "2017/02/20", 1, "2017/02/20", "2017/02/20"),
  117. (2, 2, 800, "2017/02/21", 1, "2017/02/21", "2017/02/21"),
  118. (2, 2, 800, "2017/02/22", 1, "2017/02/22", "2017/02/22"),
  119. (2, 2, 900, "2017/02/23", 1, "2017/02/23", "2017/02/23"),
  120. (2, 2, 900, "2017/02/24", 1, "2017/02/24", "2017/02/24"),
  121. (2, 2, 1000, "2017/02/25", 0, "2017/02/25", "2017/02/25"),
  122. (2, 2, 1000, "2017/02/26", 0, "2017/02/26", "2017/02/26"),
  123. (2, 2, 1000, "2017/02/27", 0, "2017/02/27", "2017/02/27"),
  124. (2, 2, 1000, "2017/02/28", 0, "2017/02/28", "2017/02/28"),
  125. (3, 1, 5000, "2016/10/01", 1, "2016/10/01", "2016/10/31"),
  126. (3, 1, 5000, "2016/11/01", 1, "2016/11/01", "2016/11/30"),
  127. (3, 1, 5000, "2016/12/01", 0, "2016/12/01", "2016/12/31"),
  128. (3, 1, 5000, "2017/01/01", 0, "2017/01/01", "2017/01/31"),
  129. (3, 1, 5000, "2017/02/01", 0, "2017/02/01", "2017/02/28"),
  130. (3, 1, 5000, "2017/03/01", 0, "2017/03/01", "2017/03/31"),
  131. (4, 3, 3000, "2016/12/01", 1, "2016/12/01", "2016/12/31"),
  132. (4, 3, 3000, "2017/01/01", 1, "2017/01/01", "2017/01/31"),
  133. (4, 3, 3000, "2017/02/01", 1, "2017/02/01", "2017/02/28"),
  134. (4, 3, 3000, "2017/03/01", 1, "2017/03/01", "2017/03/31"),
  135. (8, 5, 500, "1984/01/02", 0, "1984/01/02", "1984/01/02"),
  136. (9, 5, 500, "1984/01/02", 0, "1984/01/02", "1984/01/02"),
  137. (10, 5, 500, "1984/01/02", 0, "1984/01/02", "1984/01/02"),
  138. (8, 5, 500, "1984/01/03", 0, "1984/01/03", "1984/01/03"),
  139. (9, 5, 500, "1984/01/03", 0, "1984/01/03", "1984/01/03"),
  140. (10, 5, 500, "1984/01/03", 0, "1984/01/03", "1984/01/03"),
  141. (8, 5, 500, "1984/01/04", 0, "1984/01/04", "1984/01/04"),
  142. (9, 5, 500, "1984/01/04", 0, "1984/01/04", "1984/01/04"),
  143. (10, 5, 500, "1984/01/04", 0, "1984/01/04", "1984/01/04"),
  144. (7, 4, 7000, "2017/01/01", 1, "2017/01/01", "2017/01/31"),
  145. (7, 4, 7000, "2017/02/01", 1, "2017/02/01", "2017/02/28"),
  146. (7, 4, 7000, "2017/03/01", 1, "2017/03/01", "2017/03/31"),
  147. (7, 4, 7000, "2017/04/01", 1, "2017/04/01", "2017/04/30"),
  148. (7, 4, 7000, "2017/05/01", 1, "2017/05/01", "2017/05/31"),
  149. (7, 4, 7000, "2017/06/01", 1, "2017/06/01", "2017/06/30");
  150.  
  151. /*queries*/
  152. /*1. Сколько у меня домов в аренде, а сколько на продажу и сколько я уже продал?*/
  153. SELECT status, count(*) as 'count'
  154. from jack.houses GROUP BY status;
  155.  
  156. SELECT 'rent' as 'status', count(*) as 'count'
  157. FROM jack.houses WHERE status like "%rent%";
  158.  
  159. SELECT 'sold' as 'status', count(*) as 'count'
  160. FROM jack.houses WHERE status = "sold";
  161.  
  162. SELECT 'for sale' as 'status', count(*) as 'count'
  163. FROM jack.houses WHERE status = "for sale";
  164.  
  165. /*
  166. 2. Сколько у меня арендаторов и кто, когда и сколько платит?
  167. */
  168. select count(*) as 'Renters count' from jack.renters;
  169.  
  170. select count(distinct(renter_id)) as 'Renters active in 2017'
  171. from jack.rents
  172. where payment_date > '2016/12/31';
  173.  
  174. select name, payment_date, amount, is_payed
  175. from jack.rents, jack.renters
  176. WHERE jack.rents.renter_id = jack.renters.id
  177. order by renter_id, payment_date;
  178.  
  179. /*
  180. 3. Сколько стоит всё моё недвижимое имущество?
  181. */
  182. SELECT sum(amount) as 'Total amount'
  183. FROM jack.construction_payments
  184. WHERE house_id in (select id from jack.houses where status = "sold" );
  185.  
  186. /*
  187. 4. Когда были построены мои дома на Бейкер стрит и Малхолланд Драйв?
  188. */
  189.  
  190. SELECT street, house_number, build_date
  191. FROM jack.houses
  192. WHERE street in ('Бейкер стрит', 'Малхолланд Драйв')
  193. Order by street;
  194.  
  195. /*
  196. 5. Сколько арендаторов не заплатили за последние 3 месяца?
  197. */
  198.  
  199. select distinct(name) as 'Renters who not payed in last 3 months'
  200. from jack.rents, jack.renters
  201. WHERE jack.rents.renter_id = jack.renters.id
  202. and is_payed = 0 and payment_date > DATE_SUB(CURRENT_TIMESTAMP,Interval 3 MONTH);
  203.  
  204. /*
  205. За сколько был построен дом, за сколько продан.
  206. */
  207.  
  208. select street, house_number,
  209. (SELECT sum(amount) FROM jack.construction_payments
  210. WHERE jack.houses.id = house_id) as "build amount",
  211. (IF (status = "sold", current_sale_amount, "not sold")) as "sold amount"
  212. from jack.houses;
  213.  
  214. /*
  215. Кто сейчас живет в арендуемом доме и когда надо взымать арендную плату.
  216. */
  217.  
  218. select house_number, /*street,*/ name,
  219. (select payment_date from jack.rents
  220. where jack.houses.id = jack.rents.house_id
  221. and jack.rents.renter_id = jack.renters.id
  222. and is_payed = 0 order by payment_date limit 1
  223. ) as 'first not payed payment'
  224. from jack.houses, jack.rents, jack.renters
  225. where jack.houses.id = jack.rents.house_id
  226. and jack.rents.renter_id = jack.renters.id
  227. and rent_from <= CURRENT_DATE
  228. and rent_to >= CURRENT_DATE
  229. ;
  230.  
  231. /*
  232. служебные поля:
  233.  
  234. is_deleted BIT default 0,
  235. creation_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  236. modification_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  237. */
  238.  
  239. /*=================RESULT===============*/
  240.  
  241. mysql> source D:\jack.sql
  242. Query OK, 4 rows affected (0.58 sec)
  243.  
  244. Query OK, 1 row affected (0.00 sec)
  245.  
  246. Query OK, 0 rows affected (0.16 sec)
  247.  
  248. Query OK, 0 rows affected (0.21 sec)
  249.  
  250. Query OK, 0 rows affected (0.17 sec)
  251.  
  252. Query OK, 0 rows affected (0.26 sec)
  253.  
  254. Query OK, 11 rows affected (0.03 sec)
  255. Records: 11 Duplicates: 0 Warnings: 0
  256.  
  257. Query OK, 28 rows affected (0.09 sec)
  258. Records: 28 Duplicates: 0 Warnings: 0
  259.  
  260. Query OK, 5 rows affected (0.03 sec)
  261. Records: 5 Duplicates: 0 Warnings: 0
  262.  
  263. Query OK, 34 rows affected (0.09 sec)
  264. Records: 34 Duplicates: 0 Warnings: 0
  265.  
  266. +--------------------+-------+
  267. | status | count |
  268. +--------------------+-------+
  269. | for rent day | 4 |
  270. | for rent month | 4 |
  271. | for sale | 1 |
  272. | sold | 1 |
  273. | under construction | 1 |
  274. +--------------------+-------+
  275. 5 rows in set (0.00 sec)
  276.  
  277. +--------+-------+
  278. | status | count |
  279. +--------+-------+
  280. | rent | 8 |
  281. +--------+-------+
  282. 1 row in set (0.00 sec)
  283.  
  284. +--------+-------+
  285. | status | count |
  286. +--------+-------+
  287. | sold | 1 |
  288. +--------+-------+
  289. 1 row in set (0.00 sec)
  290.  
  291. +----------+-------+
  292. | status | count |
  293. +----------+-------+
  294. | for sale | 1 |
  295. +----------+-------+
  296. 1 row in set (0.00 sec)
  297.  
  298. +---------------+
  299. | Renters count |
  300. +---------------+
  301. | 5 |
  302. +---------------+
  303. 1 row in set (0.00 sec)
  304.  
  305. +------------------------+
  306. | Renters active in 2017 |
  307. +------------------------+
  308. | 4 |
  309. +------------------------+
  310. 1 row in set (0.00 sec)
  311.  
  312. +------------------+--------------+---------+----------+
  313. | name | payment_date | amount | is_payed |
  314. +------------------+--------------+---------+----------+
  315. | Шерлок Х. | 2016-10-01 | 5000.00 | ☺ |
  316. | Шерлок Х. | 2016-11-01 | 5000.00 | ☺ |
  317. | Шерлок Х. | 2016-12-01 | 5000.00 | |
  318. | Шерлок Х. | 2017-01-01 | 5000.00 | |
  319. | Шерлок Х. | 2017-02-01 | 5000.00 | |
  320. | Шерлок Х. | 2017-03-01 | 5000.00 | |
  321. | Доктор В. | 2017-02-20 | 800.00 | ☺ |
  322. | Доктор В. | 2017-02-21 | 800.00 | ☺ |
  323. | Доктор В. | 2017-02-22 | 800.00 | ☺ |
  324. | Доктор В. | 2017-02-23 | 900.00 | ☺ |
  325. | Доктор В. | 2017-02-24 | 900.00 | ☺ |
  326. | Доктор В. | 2017-02-25 | 1000.00 | |
  327. | Доктор В. | 2017-02-26 | 1000.00 | |
  328. | Доктор В. | 2017-02-27 | 1000.00 | |
  329. | Доктор В. | 2017-02-28 | 1000.00 | |
  330. | Девид Л. | 2016-12-01 | 3000.00 | ☺ |
  331. | Девид Л. | 2017-01-01 | 3000.00 | ☺ |
  332. | Девид Л. | 2017-02-01 | 3000.00 | ☺ |
  333. | Девид Л. | 2017-03-01 | 3000.00 | ☺ |
  334. | Гарри П. | 2017-01-01 | 7000.00 | ☺ |
  335. | Гарри П. | 2017-02-01 | 7000.00 | ☺ |
  336. | Гарри П. | 2017-03-01 | 7000.00 | ☺ |
  337. | Гарри П. | 2017-04-01 | 7000.00 | ☺ |
  338. | Гарри П. | 2017-05-01 | 7000.00 | ☺ |
  339. | Гарри П. | 2017-06-01 | 7000.00 | ☺ |
  340. | Фредди К. | 1984-01-02 | 500.00 | |
  341. | Фредди К. | 1984-01-02 | 500.00 | |
  342. | Фредди К. | 1984-01-02 | 500.00 | |
  343. | Фредди К. | 1984-01-03 | 500.00 | |
  344. | Фредди К. | 1984-01-03 | 500.00 | |
  345. | Фредди К. | 1984-01-03 | 500.00 | |
  346. | Фредди К. | 1984-01-04 | 500.00 | |
  347. | Фредди К. | 1984-01-04 | 500.00 | |
  348. | Фредди К. | 1984-01-04 | 500.00 | |
  349. +------------------+--------------+---------+----------+
  350. 34 rows in set (0.00 sec)
  351.  
  352. +--------------+
  353. | Total amount |
  354. +--------------+
  355. | 4033000.00 |
  356. +--------------+
  357. 1 row in set (0.00 sec)
  358.  
  359. +---------------------------------+--------------+------------+
  360. | street | house_number | build_date |
  361. +---------------------------------+--------------+------------+
  362. | Бейкер стрит | 1 | 1999-01-01 |
  363. | Бейкер стрит | 2 | 2001-03-06 |
  364. | Бейкер стрит | 221B | 2004-07-14 |
  365. | Малхолланд Драйв | L1№4 | 1993-02-15 |
  366. | Малхолланд Драйв | 2 | 1999-01-01 |
  367. +---------------------------------+--------------+------------+
  368. 5 rows in set (0.00 sec)
  369.  
  370. +----------------------------------------+
  371. | Renters who not payed in last 3 months |
  372. +----------------------------------------+
  373. | Шерлок Х. |
  374. | Доктор В. |
  375. +----------------------------------------+
  376. 2 rows in set (0.00 sec)
  377.  
  378. +---------------------------------+--------------+--------------+-------------+
  379. | street | house_number | build amount | sold amount |
  380. +---------------------------------+--------------+--------------+-------------+
  381. | Бейкер стрит | 1 | 6055000.00 | not sold |
  382. | Бейкер стрит | 2 | 7004000.00 | not sold |
  383. | Бейкер стрит | 221B | 6055000.00 | not sold |
  384. | Малхолланд Драйв | L1№4 | 6004000.00 | not sold |
  385. | Малхолланд Драйв | 2 | 5044000.00 | not sold |
  386. | Тисовая улица | 4 | 4033000.00 | 50000000.00 |
  387. | Косой переулок | 5 | 5003000.00 | not sold |
  388. | Улица Вязов | 1 | NULL | not sold |
  389. | Улица Вязов | 2 | NULL | not sold |
  390. | Улица Вязов | 3 | NULL | not sold |
  391. | Улица Вязов | 10 | NULL | not sold |
  392. +---------------------------------+--------------+--------------+-------------+
  393. 11 rows in set (0.00 sec)
  394.  
  395. +--------------+------------------+-------------------------+
  396. | house_number | name | first not payed payment |
  397. +--------------+------------------+-------------------------+
  398. | 2 | Доктор В. | 2017-02-25 |
  399. | 221B | Шерлок Х. | 2016-12-01 |
  400. | L1№4 | Девид Л. | NULL |
  401. | 5 | Гарри П. | NULL |
  402. +--------------+------------------+-------------------------+
  403. 4 rows in set (0.00 sec)
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!